# 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.
Source: https://docs.querylab.io/writing-sql/charts/
> **This is not BigQuery syntax.** `VISUALIZE`, `DRAW`, `LABEL`, `SCALE` and `PLACE` come from [ggsql](https://ggsql.org), an open SQL extension that Querylab.io ports to BigQuery. Querylab.io strips these clauses before the query reaches BigQuery; pasted into the BigQuery console they are a syntax error. Experimental.
**Query 1 · Chart tab**
```sql
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'
```
**results → Chart**
Chart: 114 rows · Max rows 10,000 · Download as PNG
## 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, 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:
Your query uses VISUALIZE / DRAW clauses — an
experimental extension that renders inline charts from SQL results.
Enabling it strips those clauses from the SQL sent to BigQuery and
produces a chart tab in the results panel.
The setting lives in Settings → SQL Frameworks → ggsql visualization (experimental). While it's off, queries with chart clauses still run and the result panel shows a _Chart visualization disabled_ notice instead of a chart.
- Enable VISUALIZE / DRAW clauses (selected): Render inline charts from ggsql syntax (e.g. VISUALIZE date AS x, revenue AS y DRAW line). Strips the clauses before sending SQL to BigQuery.
- Match chart theme to app theme (selected): Use dark axes, labels, and gridlines when the app is in dark mode. Turn off to keep charts light (useful for export/screenshots).
## Two places to write it
### Trailing form
Clauses after the statement. This is what ggsql.org examples use; paste them in unchanged.
```sql
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](https://docs.querylab.io/writing-sql/pipe-syntax/). Nothing can follow it; put every data step before it.
```sql
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`.
| Aesthetic | Channel |
| ------------------ | --------------------------------------------------------------------------------- |
| `x`, `y` | Axis position |
| `color` / `colour` | Mark color by category; also splits lines and densities into one series per value |
| `fill` | Fill color for bars and areas; stacks areas |
| `size` | Point diameter or line width |
| `shape` | Point marker shape |
| `alpha` | Opacity, 0–1 |
| `group` | Group rows without a visual encoding |
## 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.
| Geom | Draws | Notes |
| ----------- | ------------------------------------- | ------------------------------------------------------------------------ |
| `point` | One mark per row | Fine up to a few thousand points |
| `line` | Rows connected in `x` order | Sorted by `x` for you; `color` for several series |
| `bar` | One bar per row from `y` | With `x` only, becomes `COUNT(*) GROUP BY x` |
| `area` | `line` with the region below filled | `fill` stacks series |
| `path` | Rows connected in result order | For trajectories where sequence matters more than `x` |
| `histogram` | Frequency of `x` in 30 bins | Bins computed with `RANGE_BUCKET` in BigQuery |
| `smooth` | OLS regression line of `y` on `x` | Two points come back; no scatter overlay, one line regardless of `color` |
| `boxplot` | Quartiles and whiskers of `y` per `x` | `APPROX_QUANTILES` in BigQuery |
| `density` | Gaussian kernel density of `x` | Curve computed in BigQuery; `color` overlays groups |
**scatter with a categorical color**
```sql
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
```
**server-side histogram over the whole table**
```sql
SELECT weight_pounds
FROM `bigquery-public-data.samples.natality`
WHERE weight_pounds IS NOT NULL
VISUALIZE weight_pounds AS x
DRAW histogram
```
BigQuery bins; the browser draws 30 bars
**results → Chart**
The bars are counts per bin; the raw rows never leave BigQuery.
## 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.
```sql
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
```
| Clause | Effect |
| --------------------------------------------- | ----------------------------------------------- |
| `VIA log` / `log2` / `ln` / `sqrt` / `square` | Axis transform |
| `VIA date` / `datetime` / `time` | Force a temporal axis |
| `FROM [lo, hi]` | Fix the domain; `null` leaves one end automatic |
| `TO ['#…', '#…']` | Palette for `color` or `fill` |
| `SETTING reverse => true` | Reverse the axis |
### PLACE
Adds a layer at literal coordinates: a reference line or a text note.
```sql
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`.
> Some scale transforms and layer types aren't rendered yet; the editor marks them.
## Completions, hover, and checks
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.
```sql
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
```
Checks 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](https://docs.querylab.io/writing-sql/sql-formatting/) 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`.
For results above 100k rows, aggregate before charting, or use `histogram`, `smooth`, `boxplot` or `density`, which aggregate in BigQuery and are not capped.