Visualization option doesn't appear with metabase query

markd · March 25, 2021, 5:22pm

Hello, first time user here!

I'm trying to generate a graph from the following query, but the visualization options are dimmed out. I can select a Y axis but not an X axis. It looks like the formatting swallowed an asterisk as the multiply by 100.

Here's the query:

select date, system_name, device_name, valid_read_rate from 
(SELECT  left(from_unixtime(c.capture_time/1000),10) as date,  a.name as 'system_name', b.name as 'device_name', c.read_count, c.valid_object_count, 
if(valid_object_count = 0,'00.00%',concat(format(read_count/valid_object_count*100,2),'%')) as valid_read_rate, read_count/valid_object_count*100 as valid_read_rate_unformatted
FROM  sick_il_services.system_config a
join sick_il_services.system_device b
on a.id = b.system_id
join sick_il_aap.daily_device_read_rate c
on b.id = c.device_id
where 1=1
[[AND c.capture_time >= unix_timestamp(concat({{start_date}}, ' 00:00:00.000')) * 1000 ]]
[[AND c.capture_time <= unix_timestamp(concat({{end_date}}, ' 23:59:59.999')) * 1000]]
[[AND a.name = '{{system_name}}']]
group by c.capture_time,a.name, b.name
order by c.capture_time, a.name, b.name) as sub
where sub.valid_read_rate_unformatted < {{expected_read_rate_percent}}

Any ideas? Thanks in advance!

flamber · March 25, 2021, 5:27pm

Hi @markd
Please post "Diagnostic Info" from Admin > Troubleshooting, and which database you are querying.
A screenshot would be helpful too, to fully understand what is happening.

And you can use the button in the forum editor that looks like </> to enclose your SQL, so it doesn't get formatted incorrectly.

markd · March 25, 2021, 6:43pm

Hi @flamber,

Thanks for the quick response and the tip on formatting!

The database being accessed is sick_il_services joined with a table in sick_il_aap.

Here is a screenshot - as you can see, the visualization options are dimmed out:

I don't see an option for "Diagnostic Info" in the Admin > Troubleshooting section, just "Tasks", "Jobs" and "Logs". Here's the end of the last log:

{:type "native",
:native
{:query
"select date, system_name, device_name, valid_read_rate from \r\n(SELECT left(from_unixtime(c.capture_time/1000),10) as date, a.name as 'system_name', b.name as 'device_name', c.read_count, c.valid_object_count, \r\nif(valid_object_count = 0,'00.00%',concat(format(read_count/valid_object_count100,2),'%')) as valid_read_rate, read_count/valid_object_count100 as valid_read_rate_unformatted\r\nFROM sick_il_services.system_config a\r\njoin sick_il_services.system_device b\r\non a.id = b.system_id\r\njoin sick_il_aap.daily_device_read_rate c\r\non b.id = c.device_id\r\nwhere 1=1\r\n[[AND c.capture_time >= unix_timestamp(concat({{start_date}}, ' 00:00:00.000')) * 1000 ]]\r\n[[AND c.capture_time <= unix_timestamp(concat({{end_date}}, ' 23:59:59.999')) * 1000]]\r\n[[AND a.name = '{{system_name}}']]\r\ngroup by c.capture_time,a.name, b.name\r\norder by c.capture_time, a.name, b.name) as sub\r\nwhere sub.valid_read_rate_unformatted < {{expected_read_rate_percent}}",
:template-tags
{:start_date {:id "c617fe80-ac55-8578-549b-0fd8f43a015a", :name "start_date", :display-name "Start Date", :type "date", :required true},
:end_date {:id "c1e08118-d687-a293-6430-ead9a6fa948f", :name "end_date", :display-name "End Data", :type "date"},
:system_name {:id "b9f0e20e-2ce5-8cc7-9b97-8e463e31a407", :name "system_name", :display-name "System Name", :type "text"},
:expected_read_rate_percent {:id "c7d91d4a-bee2-97b7-30d1-4eca4c9ed6b9", :name "expected_read_rate_percent", :display-name "Expected Read Rate", :type "number", :required true}}},
:parameters [{:type "date/single", :target ["variable" ["template-tag" "start_date"]], :value "2021-03-01"}],
:async? true,
:middleware {:add-default-userland-constraints? true, :userland-query? true},
:info
{:executed-by 1,
:context :ad-hoc,
:card-id nil,
:nested? false,
:query-hash [107, 47, 55, -27, 78, 3, -101, -40, -20, 48, -66, -79, -10, 121, -25, -24, -104, 14, 116, 13, -62, -57, -110, 87, -91, -68, 13, -108, -55, -77, -60, -90]},
:constraints {:max-results 10000, :max-results-bare-rows 2000}}}

[47fd0656-3b0e-4319-9c8d-323b1739d85f] 2021-03-25T14:26:33-04:00 DEBUG metabase.middleware.log POST /api/dataset 200 [ASYNC: completed] 116.5 ms (9 DB calls) Jetty threads: 3/50 (3 idle, 0 queued) (75 total active threads) Queries in flight: 0
[47fd0656-3b0e-4319-9c8d-323b1739d85f] 2021-03-25T14:26:39-04:00 DEBUG metabase.middleware.log POST /api/dataset 200 [ASYNC: completed] 99.6 ms (9 DB calls) Jetty threads: 3/50 (3 idle, 0 queued) (76 total active threads) Queries in flight: 0
[47fd0656-3b0e-4319-9c8d-323b1739d85f] 2021-03-25T14:26:46-04:00 DEBUG metabase.middleware.log GET /api/session/properties 200 2.9 ms (1 DB calls) Jetty threads: 5/50 (2 idle, 0 queued) (76 total active threads) Queries in flight: 0
[47fd0656-3b0e-4319-9c8d-323b1739d85f] 2021-03-25T14:26:46-04:00 DEBUG metabase.middleware.log GET /api/setting 200 702.1 µs (0 DB calls) Jetty threads: 5/50 (2 idle, 0 queued) (77 total active threads) Queries in flight: 0
[47fd0656-3b0e-4319-9c8d-323b1739d85f] 2021-03-25T14:26:46-04:00 DEBUG metabase.middleware.log GET /api/setup/admin_checklist 200 41.3 ms (11 DB calls) Jetty threads: 5/50 (2 idle, 0 queued) (77 total active threads) Queries in flight: 0
[47fd0656-3b0e-4319-9c8d-323b1739d85f] 2021-03-25T14:27:58-04:00 DEBUG metabase.middleware.log GET /api/task 200 48.2 ms (2 DB calls) Jetty threads: 4/50 (3 idle, 0 queued) (75 total active threads) Queries in flight: 0
[47fd0656-3b0e-4319-9c8d-323b1739d85f] 2021-03-25T14:28:49-04:00 DEBUG metabase.middleware.log GET /api/task/info 200 18.3 ms (0 DB calls) Jetty threads: 4/50 (3 idle, 0 queued) (77 total active threads) Queries in flight: 0
[47fd0656-3b0e-4319-9c8d-323b1739d85f] 2021-03-25T14:30:08-04:00 DEBUG metabase.middleware.log GET /api/task/info 200 11.9 ms (0 DB calls) Jetty threads: 4/50 (3 idle, 0 queued) (77 total active threads) Queries in flight: 0

If you can give me some indicator of where the "Diagnoistic Info" link or tab is, I'll send that as well.

Thanks!

markd · March 25, 2021, 6:59pm

I should add that I'm able to create a visualization with another query:

SELECT  left(from_unixtime(c.capture_time/1000),10) as date,  a.name as 'system name', b.name as 'device name', c.read_count, c.valid_object_count, 
if(valid_object_count = 0,'00.00%',concat(format(read_count/valid_object_count*100,2),'%')) as valid_read_rate
FROM  sick_il_services.system_config a
join sick_il_services.system_device b
on a.id = b.system_id
join sick_il_aap.daily_device_read_rate c
on b.id = c.device_id
WHERE 1=1
[[AND c.capture_time >= unix_timestamp(concat({{start_date}}, ' 00:00:00.000')) * 1000 ]]
[[AND c.capture_time <= unix_timestamp(concat({{end_date}}, ' 23:59:59.999')) * 1000]]
[[AND a.name = '{{system_name}}']]
group by a.name, b.name

flamber · March 25, 2021, 7:13pm

@markd I can see what the problem is I think - everything is strings - there are no numerical columns, so that's why you don't get any choices.
You should just return dates and numbers, and then you can use formatting in Metabase to modify them.
Redirecting…

Don't you see this?

markd · March 25, 2021, 7:31pm

Great! I'm sure that's the issue and I will review the link to see how to fix it.

Question, though - the query that does have visualization also returns text. What's the difference? Can't queries that are visualized return text? Or you just need one number or date?

I don't appear to have a help selection on the troubleshooting display:

flamber · March 25, 2021, 7:34pm

@markd The visualizations depend on numeric values to input on graphs. Just like you cannot subtract two cells in Excel if they are strings, since Excel doesn't understand that.

Please click the in upper-right corner > About Metabase - which version are you using?
Latest release is 0.38.2: https://github.com/metabase/metabase/releases/latest

markd · March 25, 2021, 7:36pm

@flamber

Is it too old?

flamber · March 25, 2021, 7:37pm

@markd The date should tell you something - you can use whichever version you prefer, but if you're encountering an issue, then there won't be a fix for your version, only newer versions.

markd · March 25, 2021, 7:45pm

@flamber

Good point, I will bring it up at the next product meeting. Thanks again for the fix!

Completely off-topic, but have you ever run into issues where metabase somehow causes mySql to crash due to (I have no idea). I'm just asking because there was a mySql crash at a beta site we just recently started using metabase at.

Apologies for the vague question, just throwing it out there in case you had ever had any issues.

flamber · March 25, 2021, 7:52pm

@markd Check the logs of both Metabase and MySQL - it should give you a clear hint at what could be the problem. But since you're using an outdated Metabase, then I would think that might also be an outdated MySQL - there's reasons for newer versions, both in terms of security and features, but definitely also bug fixes.
My first guess is that you're running out of memory during Metabase sync/scan process. There has been a lot of changes in later versions of Metabase, so the sync/scan process should be faster and better.