Charts

Chart-as-code for BigQuery. A ggsql clause at the end of a query says which columns are the axes and what to draw, so the chart lives in the SQL, versions with it, and is re-created by anyone who runs it. The chart appears in a Chart tab next to the data grid.

SELECT year, SUM(number) AS births
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE name = 'Alice'
GROUP BY year
VISUALIZE year AS x, births AS y
DRAW line
LABEL title => 'Births named Alice, 1910–present'

How it runs

The query above is sent to BigQuery without its last three lines. The rows that come back fill the Data tab as usual and feed the Chart tab in the browser: one query, one bill. For geoms that need aggregation the chart clause is compiled into SQL instead: a histogram becomes RANGE_BUCKET binning, a regression line becomes COVAR_POP / VAR_POP, so the fit or the bins reflect every row in the table, not the rows on the page.

The ggsql badge at the right of the editor’s status bar shows when a query has chart clauses. Click it to see the SQL that actually runs; click again to return to your source.

Enabling it

ggsql is off by default. The first time you run a query with VISUALIZE, a dialog offers to turn it on:

The setting lives in Settings → SQL Frameworks → ggsql visualization (experimental). While it’s off, queries with chart clauses still run (the clauses are still stripped) and the result panel shows a Chart visualization disabled notice instead of a chart.

Two places to write it

Trailing form

Clauses after the statement. This is what ggsql.org examples use; paste them in unchanged.

SELECT state, AVG(weight_pounds) AS avg_weight
FROM `bigquery-public-data.samples.natality`
WHERE year = 2005 AND weight_pounds IS NOT NULL
GROUP BY state
VISUALIZE state AS x, avg_weight AS y
DRAW bar

Pipe form

A terminal |> VISUALIZE step, a Querylab.io extension for pipe syntax. Nothing can follow it; put every data step before it.

FROM `bigquery-public-data.samples.natality`
|> WHERE year = 2005 AND weight_pounds IS NOT NULL
|> AGGREGATE AVG(weight_pounds) AS avg_weight GROUP BY state
|> VISUALIZE state AS x, avg_weight AS y DRAW bar

Mapping columns to channels

VISUALIZE col AS aesthetic, … maps result columns to visual channels. Most geoms need x and y; histogram, density and count-mode bar need only x.

Choosing what to draw

DRAW geom picks one of nine geoms. The first five draw the rows as returned; the last four rewrite the query so BigQuery does the statistics.

SELECT sepal_length, petal_length, species
FROM `bigquery-public-data.ml_datasets.iris`
VISUALIZE sepal_length AS x, petal_length AS y, species AS color
DRAW point

SELECT weight_pounds
FROM `bigquery-public-data.samples.natality`
WHERE weight_pounds IS NOT NULL
VISUALIZE weight_pounds AS x
DRAW histogram

Titles, scales, and annotations

LABEL, SCALE and PLACE are optional and come after DRAW.

LABEL

Sets the text: title, subtitle above the chart, caption below it. Values are single-quoted strings, in any order.

SCALE

Controls how a channel maps values.

SELECT year, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY year
VISUALIZE year AS x, total AS y
DRAW line
SCALE y VIA log FROM [1000, null]
SCALE color TO ['#7586dc', '#4caf50']
SCALE x SETTING reverse => true

PLACE

Adds a layer at literal coordinates: a reference line or a text note.

PLACE rule SETTING y => 7.5, colour => 'red', linetype => 'dashed'
PLACE text SETTING x => 2015, y => 1200, label => 'Policy change', fontsize => 12, fontweight => 'bold'

rule takes x or y, colour (or stroke), linewidth, and linetype ('dashed' or 'dotted'). text takes x, y, label, fill (or colour), stroke, fontsize, fontweight, and italic.

Completions, hover, and diagnostics

Completions follow the clause: columns from the query’s scope after VISUALIZE, aesthetic names after AS, geom names after DRAW, label keys after LABEL. Hover any of the five keywords, a geom or an aesthetic for a one-line description.

SELECT state, COUNT(*) AS n
FROM `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY state
VISUALIZE state AS x, n AS y
DRAW 

Diagnostics are immediate: an unknown geom (DRAW gizmo) or aesthetic is an error; a column in VISUALIZE that the query doesn’t return is an error; unsupported transforms and layers are info. The formatter leaves chart clauses as you wrote them.

Reading the Chart tab

The chart is sized to the panel. Its footer has the LABEL caption if you set one, a row count, a Max rows selector, and a PNG download button.

• The row count reads Chart: 114 rows, or Chart: 10,000 of 48,210 rows — truncated for rendering when the result is larger than the cap.
• Max rows chooses how many rows are fed to the renderer: 1,000, 5,000, 10,000 (the default), 50,000, or 100,000. The data grid is unaffected.
• The download button saves the rendered chart as chart.png, in the browser.

For results above 100k rows, aggregate before charting, or use histogram, smooth, boxplot or density, which aggregate in BigQuery and are not capped.