Can't display a chart

[dhyon]

Hi I'm creating a dashboard from scratch and I've created a widget that contains a chart. The SQL builder allows me to use a custom query to display a text chart but I can't seem to get a visual graph (such as a bar graph) to render at all. Am I missing something here?

Thanks.

[jmuharsky]

Can you provide an example? Depending on the chart, there are requirements about the columns returned.

To make sure your SQL is returning the appropriate data, you can change the chart type to Table.

[jmuharsky]

I'm making some updates to the Chart Gallery dashboard to be more explanatory about which table shaped produce the different charts.

[dhyon]

Hi, this is the table my query renders:

But no other chart type seems to work. Is there something wrong with my data?

[klausw]

For now, please make sure your data doesn't have any extra columns in it. Most visualizations assume a domain in the first column (date, or the label for a bar chart), and at least one data column with a numeric value. You can fix that by adding a wrapping SELECT around your query, for example:

SELECT metric, value
FROM (
  -- your original query goes here
  SELECT ... FROM ...
)

Joe, there does seem to be a bug here. It's supposed to be possible to assign column roles using the "Columns" tab, but I can't get that to work for bar charts.

Here's a test using a (partial) copy of the sample table data:

https://perfkit-explorer.appspot.com/explore?dashboard=5765867027562496

[klausw]

Also, something may be going wrong with your data import or SQL statement - the "owner" column appears to be showing machine types.

[jmuharsky]

There are a couple issues at play here:

1. We're not surfacing chart rendering error messages (which would give a hint to...)
2. The table shape is invalid for a Bar chart. Core Charts (of which Bar Chart is one) require the first column to be a timeseries or category axis, and remaining columns to be arranged into series. In the example you provide, you could use owner, metric or unit as the domain (first) column, and value as the second. You can also use roles to make the additional columns appear as tooltips or annotations. For more help on this:
   • The Chart Gallery contains examples of each chart type, and the table structure that drives them.
   • The Role Gallery contains examples of each column role/style, and explains the table structure required.
   • Klaus created a Repro Dashboard to demonstrate the query and effect you're looking for. Wrt his note about a possible bug, it's important that any "role" columns appear after the data column they're meant to modify.

[dhyon]

Thank you for the prompt feedback everyone. I'll try to apply what you've all responded with and come back with (hopefully) good news.

[dhyon]

SELECT metric,value, FROM (SELECT owner,metric,value,unit FROM results.testrun WHERE test="coremark")

The query above displays a table but cannot render a chart. I can't understand why Klaus's table can render as a bar graph but mine can't other than the fact that Klaus's query is statically defined: select metric, value FROM (select "c3.4xlarge" as owner, "Throughput" as metric, 866 as value, "Mbits/sec" as unit), (select "c3.4xlarge" as owner, "Throughput" as metric, 866 as value, "Mbits/sec" as unit), (select "c3.4xlarge" as owner, "End to End Runtime" as metric, 628.902 as value, "seconds" as unit)

Is this a bug?

[klausw]

This is odd. What kind of chart are you trying to render? Does the input data table have the exact same structure? Any messages in the "LOG" tab (between "SQL" and "Last Query")?

Did you change any settings in the "Query columns and results" tab (pivoting) or "Column styling and order" tab?

You can try pasting the JSON data for the widget here in this bug, that may help see what's going on.

[dhyon]

I just realized I need to have an input data table in the same container as the bar chart. Thanks for clarifying that above. I assumed the bar chart could simply render with the correct query.

[jmuharsky]

You should be able to have a single widget with a bar chart; a second widget with the Table (visualization) isn't necessary, unless you specifically want to see the table of data as well. The following steps should get you to a custom SQL statement-driven bar chart (though a video with be coming soon):

1. Select or create an empty widget.
2. Click the EDIT SQL button on the toolbar.
3. Enter your SQL statement in the footer codebox that appears. Note that driving a chart with categories generally requires rollup to a single value. In this case, that means you'll need to _GROUP BY_ your metric and aggregate the value. The following statement should suffice:

SELECT metric, AVG(value) AS avg FROM results.testrun WHERE test="coremark" GROUP BY metric

4. Click on the Chart tab on the left-hand side (it's one from the bottom, and looks like a small column chart).
5. Under chart type, click the dropdown and choose Bar.

I've created a sample dashboard that demonstrates this with a different test name and table name.

Not that it would change the output, but out of curiousity, why are you wrapping the SQL statement? The SQL statement noted above should work without wrapping in an outer statement, and may yield some small performance benefits.

[jmuharsky]

Upon further investigation, the GROUP BY isn't necessary here. You can see each individual sample plotted with the SQL:

SELECT metric, value FROM results.testrun WHERE test="coremark"

I have updated the sample dashboard to show both behaviors.

[dhyon]

Thanks for the input. I wrapped the SQL statement because Klaus recommended that in his first response to this thread. I've unwrapped that SQL statement now that things are working better.

[jmuharsky]

Sample Dashboard updated one more time to more clearly document the behavior/SQL expectation, and link it back to this issue.

Are you able to create the bar chart now?

[dhyon]

Yes I can create the bar chart now thanks to y'all. Thanks a lot! There was a bit of a learning curve there but I think I've got it working now.

Is there a way to change the numerical range of an axis? I want it to span from 0 to X instead of X-N to X.

[dhyon]

NVM, I was able to set that in the Chart Editor settings. Thanks again!

[jmuharsky]

you're welcome! In the future, we'll support more chart options from the "main" UX, but for now you can use the Chart Dialog for supported features, and the JSON editor for even more customizations.