Charts (ggsql)
Render charts directly from SQL by appending VISUALIZE, DRAW, LABEL, SCALE, and PLACE clauses to any BigQuery query. Charts render client-side in a Chart tab next to the data grid.
ggsql support in Querylab.io is experimental, and the ggsql standard itself is still evolving. Grammar, defaults, and rendering behavior may change in future releases.
What Are Charts in SQL?
ggsql is an open-source SQL extension (ggsql.org) that adds chart-rendering clauses to SQL. Querylab.io ports the grammar to BigQuery. Aggregation (binning, regression, quantiles) runs as part of the BigQuery query; the chart renders in the browser with no second round-trip.
The chart clauses are stripped before BigQuery executes. The returned rows drive both the data grid and the chart. No extra query is issued.
Quick Start
SELECT year, SUM(number) AS births
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE name = 'Alice'
GROUP BY year
ORDER BY year
VISUALIZE year AS x, births AS y
DRAW line
LABEL title => 'Births named Alice, 1910–present'
Run this query. The result panel shows a Chart tab with a line chart, and a Data tab with the raw rows. Both are populated from the same result.
Surface Forms
Two equivalent ways to write ggsql clauses.
Trailing form
Clauses appear after the SELECT body. Upstream-compatible; matches ggsql.org examples.
SELECT date, revenue
FROM `bigquery-public-data.samples.gsod`
VISUALIZE date AS x, revenue AS y
DRAW bar
Pipe form
Clauses are a terminal |> operator. Querylab.io extension; recommended for new queries.
FROM `bigquery-public-data.samples.gsod`
|> SELECT date, revenue
|> ORDER BY date
|> VISUALIZE date AS x, revenue AS y DRAW bar
Both forms produce identical charts. Pipe form combines with |> WHERE, |> ORDER BY, and other pipe operators, so the data steps appear before the chart clause. See Pipe Syntax for the full pipe operator reference.
Trailing form matches community examples and ggsql.org documentation; paste them in unchanged.
VISUALIZE
VISUALIZE maps columns to visual channels (aesthetics).
VISUALIZE date AS x, revenue AS y, region AS color
| Aesthetic | Visual channel |
|---|---|
x | Horizontal axis position |
y | Vertical axis position |
color | Point/line/border color (by category) |
colour | Same as color — both accepted |
fill | Fill color for bars and areas |
size | Point diameter or line width |
shape | Point marker shape |
alpha | Opacity (0–1) |
group | Groups rows without visual encoding |
At minimum, specify x and y for most geoms. DRAW histogram and count-mode DRAW bar need only x.
DRAW
DRAW selects the chart type. Nine geoms are supported:
point
Scatter plot — one mark per row.
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
Use shape or color mapped to a categorical column to distinguish groups. Renders raw rows. Works best up to a few thousand points before overplotting.
line
Line chart. Connects rows in x order. No explicit ORDER BY needed; the chart orders by x automatically.
SELECT CAST(year AS INT64) AS 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
Map a categorical column to color to draw multiple series.
bar
Bar chart. Two modes:
- y mapped — renders a bar per row, height from
y. Use after aggregation. - count mode — map only
x, omity: BigQuery rewrites the query toCOUNT(*) GROUP BY x.
-- After aggregation (y mapped)
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
LABEL title => 'Average birth weight by state, 2005'
-- Count mode (no y — BigQuery counts for you)
SELECT state
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = 2000
VISUALIZE state AS x
DRAW bar
area
Area chart. Like line, but fills the region below the line. Suitable for cumulative or part-of-whole series.
SELECT CAST(year AS INT64) AS year, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY year
ORDER BY year
VISUALIZE year AS x, total AS y
DRAW area
Stack multiple series by mapping a column to fill.
path
Connects rows in dataset order (not sorted by x). Use for trajectory data or when row sequence matters more than x-axis order.
SELECT start_station_longitude AS lon, start_station_latitude AS lat
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
WHERE start_station_id = 2575
ORDER BY start_time
LIMIT 100
VISUALIZE lon AS x, lat AS y
DRAW path
histogram
Frequency histogram. BigQuery pre-aggregates into bins using RANGE_BUCKET, so this works on any table size, not just the rendered page.
SELECT weight_pounds
FROM `bigquery-public-data.samples.natality`
WHERE weight_pounds IS NOT NULL
VISUALIZE weight_pounds AS x
DRAW histogram
Map only x (the continuous variable to bin). Default bin count is 30. Bin boundaries are computed server-side; the chart renders the aggregated counts.
smooth
Scatter plot with an OLS regression line overlay. BigQuery computes regression coefficients server-side so the fit reflects the full result, not just the visible page.
SELECT mother_age, weight_pounds
FROM `bigquery-public-data.samples.natality`
WHERE weight_pounds IS NOT NULL
VISUALIZE mother_age AS x, weight_pounds AS y
DRAW smooth
The underlying points are rendered alongside the regression line. Map color to a categorical column to fit separate regression lines per group.
boxplot
Box-and-whisker plot. BigQuery computes quartiles and whisker bounds using APPROX_QUANTILES server-side, so this scales to arbitrarily large result sets.
SELECT state, weight_pounds
FROM `bigquery-public-data.samples.natality`
WHERE weight_pounds IS NOT NULL
VISUALIZE state AS x, weight_pounds AS y
DRAW boxplot
density
Kernel density estimate. BigQuery computes a Gaussian KDE server-side and returns the density curve as pre-aggregated (x, y) pairs.
SELECT weight_pounds
FROM `bigquery-public-data.samples.natality`
WHERE weight_pounds IS NOT NULL
VISUALIZE weight_pounds AS x
DRAW density
Map color to a categorical column for overlapping density curves by group.
LABEL
Sets chart text annotations. All three keys are optional and can appear in any order.
LABEL title => 'US births by year',
subtitle => 'Source: bigquery-public-data.usa_names',
caption => 'Filtered to years 1960–2020'
| Key | Renders as |
|---|---|
title | Large text above the chart |
subtitle | Smaller text below the title |
caption | Small text below the chart |
Values are string literals enclosed in single quotes.
SCALE
Controls how data values map to visual values for a given aesthetic. SCALE is optional — omit it for automatic scaling.
SCALE x VIA log
SCALE y FROM [0, null]
SCALE color TO ['#e41a1c', '#377eb8', '#4daf4a']
Supported transforms (VIA):
| Transform | Effect |
|---|---|
VIA log | Base-10 logarithmic axis |
VIA log2 | Base-2 logarithmic axis |
VIA ln | Natural logarithm axis |
VIA sqrt | Square-root axis |
VIA square | Square (x²) axis |
VIA date | Force temporal type (date only) |
VIA datetime | Force temporal type (date + time) |
VIA time | Force temporal type (time only) |
Domain and range:
FROM [lo, hi]— fix the domain. Usenullfor auto on one end:FROM [0, null]forces the axis to start at 0.TO ['val1', 'val2', ...]— custom palette forcolororfillaesthetics.SETTING reverse => true— reverse scale direction.
-- Log scale on y, fixed minimum of 0
SELECT year, count
FROM my_table
VISUALIZE year AS x, count AS y
DRAW line
SCALE y VIA log FROM [1, null]
-- Custom color palette
SCALE color TO ['#2166ac', '#d6604d', '#4dac26']
-- Reverse x axis
SCALE x SETTING reverse => true
Some grammar features (CONTINUOUS, DISCRETE, BINNED, ORDINAL, IDENTITY, RENAMING, VIA linear, VIA exp) parse without error but are not yet rendered. They surface as info-level diagnostics (blue underline) in the editor. See Code Quality for diagnostic severity levels.
PLACE
Adds annotation layers on top of the chart using literal values (not column references). Two layer types are currently rendered:
rule
A reference line at a fixed position.
-- Horizontal threshold line
PLACE rule SETTING y => 0.5, colour => 'red', linetype => 'dashed'
-- Vertical marker on a categorical axis
PLACE rule SETTING x => '2020-01-01', stroke => '#888888', linewidth => 2
| Setting | Type | Effect |
|---|---|---|
x | number or string | Vertical reference line position |
y | number or string | Horizontal reference line position |
colour | string | Line color |
stroke | string | Alias for colour |
linewidth | number | Line thickness in pixels |
linetype | string | 'dashed' or 'dotted' |
text
A text annotation at a fixed coordinate.
PLACE text SETTING x => 2015, y => 1200, label => 'Policy change',
fill => '#333333', fontsize => 12, fontweight => 'bold'
| Setting | Type | Effect |
|---|---|---|
x | number or string | Horizontal position |
y | number or string | Vertical position |
label | string | Text to display |
fill | string | Text color |
colour | string | Alias for fill |
stroke | string | Stroke color of glyphs |
fontsize | number | Font size in points |
fontweight | string or number | 'bold' or a numeric weight (700) |
italic | boolean | true for italic text |
Other layer types (point, bar, area, polygon, ribbon) parse without error and surface info diagnostics — they are not yet rendered.
Editor Features
Hover docs
Hover any ggsql keyword, geom name, or aesthetic name to see a description in the editor tooltip. Works for VISUALIZE, DRAW, LABEL, SCALE, PLACE, all geom names (line, histogram, etc.), and all aesthetic names (x, y, color, etc.).
Completions
The editor provides context-aware completions at every point in a ggsql clause:
- After
VISUALIZE— column names available in the query's scope (FROM-side columns and SELECT-list aliases) - After
AS— aesthetic names (x,y,color,fill, …) - After
DRAW— geom names (point,line,bar,area,path,histogram,smooth,boxplot,density) - After
LABEL— label keys (title,subtitle,caption)
See Auto-completions for general completion behavior.
Diagnostics
Real-time squiggles catch mistakes before you run:
| Diagnostic | Meaning |
|---|---|
Unknown geom (e.g. DRAW gizmo) | Geom name not recognized — check spelling |
| Unknown aesthetic name | Aesthetic not in supported list |
| Unrecognized SCALE transform (info) | Transform parses but isn't rendered yet |
| Unsupported PLACE layer type (info) | Layer type parses but isn't rendered yet |
Info-level diagnostics (blue underline) mean the clause is valid syntax but the feature is not yet rendered. The query still runs and the chart still renders from supported clauses.
Formatter
The SQL formatter (SQL Formatting) round-trips ggsql clauses unchanged. Keyword casing, indentation, and clause order are preserved.
Compiled view
When a query contains ggsql clauses, a ggsql badge appears in the editor status bar. Click it to see the actual SQL Querylab.io sends to BigQuery: chart clauses stripped, aggregation rewrites applied (e.g. histogram binning, smooth regression). Click again to restore the source view.
Chart Panel
The Chart tab appears next to the Data tab in the result panel whenever a ggsql query runs successfully.
- Max rows selector — controls how many result rows feed the chart renderer: 1k / 5k / 10k (default) / 50k / 100k. The chart caption shows "Chart: N rows" or "Chart: N of M rows — truncated for rendering" when the result exceeds the selected cap. The data grid is unaffected.
- PNG download — a download button in the chart's footer bar, alongside the row-count caption and max-rows selector, saves the rendered chart as a PNG image.
- The chart is responsive: width and height adapt to the panel. Axes auto-pad based on data range.
Settings
ggsql charts are opt-in per account. Enable them at Settings → SQL Frameworks → Enable ggsql charts.
When disabled, queries with chart clauses still run — the clauses are stripped before execution and no chart tab appears.
Limitations
- Charts cap at 10k rows by default. Increase using the max-rows selector in the chart panel (up to 100k). For larger datasets, aggregate before charting.
histogram,smooth,boxplot, anddensitypre-aggregate server-side and work at any scale. - A handful of advanced grammar features (extra SCALE transforms, additional PLACE layer types, extra LABEL keys) parse but aren't yet rendered. They show as info diagnostics — the rest of the chart still renders.
- ggsql clauses are terminal: no pipe operators may follow
|> VISUALIZE. Order any data before VISUALIZE. - PNG export runs in the browser. No server-side image export.
Further Reading
For the full ggsql language specification — including advanced SCALE options, multi-layer composition, and grammar extensions not covered here — see the upstream documentation at ggsql.org.