Visual Studio Code extension providing BigQuery SQL syntax checking
The BigQuery SQL Formatter extension for Visual Studio Code provides syntax checking and formatting for BigQuery SQL
queries. This extension aims to enhance the development experience for users working with BigQuery by offering features
such as:
- Syntax Checking: Automatically checks the syntax of BigQuery SQL queries to ensure they are correct and conform to
best practices.
- Formatting: Provides tools to format SQL queries according to predefined styles, making the code more readable and
maintainable.
- Language Server Integration: Utilizes a language server to offer real-time feedback and suggestions as users write SQL
queries.
- GoogleSQL Support: Checks BigQuery's SQL dialect, including BigQuery-specific query features.
- Best Practices Enforcement: Encourages the use of best practices in SQL query writing, such as preferring LEFT JOIN
over RIGHT JOIN for better readability and maintainability.
This extension is particularly useful for developers and data analysts who frequently write and maintain BigQuery SQL
queries, providing them with tools to improve code quality and productivity.
Diagnostics update as you type, including after edits to multiline strings and
comments. Rules ignore SQL-like text inside strings, quoted identifiers, and
comments. Style and best-practice diagnostics default to warnings. Quick fixes
and formatting preserve your file's line endings; linting does not rewrite files.
Installation
Install
BigQuery SQL Formatter
from the Visual Studio Code Marketplace.
Requires Visual Studio Code 1.91 or newer. Open a BigQuery-specific file or
select GoogleSQL as the document language to start linting.
Rules
See the rule reference
for all aliasing, ambiguous, capitalisation, convention, layout, references, and structure rules.
The references (RF) group checks identifier naming and usage. RF02 requires
source-qualified columns in multi-source queries, including STRUCT field paths.
Its ignore_words and subqueries_ignore_external_references options apply in
editor and project settings. RF03 enforces STRUCT-aware single-source consistency
with quick fixes, Fix All and formatting; single_table_references accepts
consistent (default), qualified or unqualified. RF04 is enabled by default
and warns about keywords used as unquoted aliases. Quoted names are accepted by
default; identifier policies and ignore_words are configurable.
RF05 checks quoted identifiers for special characters, with exceptions for
dashed project IDs and INFORMATION_SCHEMA region qualifiers. RF06 removes
unnecessary backticks from columns, aliases and CTE names. Both are enabled by
default; RF06 leaves table paths quoted unless check_table_paths is enabled.
The 0.4.0 rules add explicit UNION modifiers and INNER JOINs, consistent
GROUP BY/ORDER BY references, matching set-query column counts, statement
terminators, and CTE layout.
UNION modifier checks now use AM02 instead of CV12. Move any UNION-specific
CV12 settings and noqa suppressions to AM02. CV12 continues checking EXCEPT and INTERSECT.
Pipe and temporal queries
Pipe queries (including FROM-only inputs) and FOR SYSTEM_TIME AS OF support
RF01-RF06, AL01, AL02, AL04, AL05, AL06, AL08, AL09, AM04, AM05, AM06 and AM07.
Checks follow each stage's visible table aliases, output aliases and JOIN USING
columns. RF01 remains disabled by default. Quick fixes, Fix All and formatting
preserve pipe placement and line endings.
MATCH_RECOGNIZE, GRAPH_TABLE and unmodelled pipe operators (such as pipe WITH)
still skip structural checks in their own block and descendants; enclosing
queries and sibling blocks continue to be checked. CP01-CP05, CV01, CV02, CV04,
CV05, CV13, LT01, LT12 and LT13 remain active throughout. Other layout and
structure rules do not yet apply inside pipe queries.
Rule configuration
Formatting and Fix All retry without rules whose fixes cycle, grow text for ten
consecutive passes, or exceed 100 passes, preserving the fixes from the remaining rules. A warning and the
language server output list the skipped rule codes; this does not change your
rule configuration or suppress their diagnostics. Cycles skip all participating
rules; growth/pass limits skip rules repeatedly applying fixes in the last ten passes.
Configure rules in your Visual Studio Code user, workspace, or workspace-folder settings:
{
"bigquerySQLFormatter.rules": {
"CV05": { "enabled": true, "severity": "error" },
"LT02": { "enabled": false },
"exclude": ["CV01"],
"groups": { "capitalisation": false }
}
}
Rules default to enabled with warning severity unless documented otherwise.
An opt-in rule with a disabled default requires an explicit "enabled": true;
setting a severity, options, or enabling its group is not sufficient. RF01 is
Disabled by default because schema-free checking cannot distinguish unknown
table qualifiers from STRUCT field access. Enable it with "RF01": { "enabled": true }.
Supported severities are
error, warning, information, hint, and off. Disabling a rule removes
its diagnostics and fixes, including fix-on-save. Exclusions, disabled groups,
and off take precedence over an individual enabled: true setting.
Group names are aliasing, ambiguous, capitalisation, convention, layout,
references, and structure. Use uppercase rule codes in configuration,
including the RF prefix for references rules.
Rules with declared style options accept an options object. For example:
{
"bigquerySQLFormatter.rules": {
"CP04": { "options": { "capitalisation_policy": "upper" } }
}
}
CP01 (keywords), CP02 (unquoted identifiers), CP03 (functions), CP04 (literals),
and CP05 (data types) accept
lower (the default), upper, or
consistent. consistent follows
the first matching token in the file; a mixed-case first token selects
lowercase. Strings, quoted identifiers, and comments are ignored. Group settings
remain boolean toggles, not shared option defaults.
Option declarations also support lists of strings, including RF04's
case-insensitive ignore_words. Lists must contain only strings; an empty list is valid. Project
lists replace the corresponding editor list rather than being concatenated.
For shared project settings, create .bqsqlformatter.json:
{
"maxNumberOfProblems": 100,
"rules": {
"CV05": { "severity": "error" },
"LT02": { "enabled": false }
}
}
The nearest config between the SQL file and its workspace-folder root overrides
Visual Studio Code settings field by field. Only that config is used; parent project configs
are not merged. Exclusion arrays replace rather than extend the editor list.
Multi-root workspaces remain isolated. Without a workspace, only the SQL file's
directory is checked. Settings and project-file changes revalidate open documents
automatically. Options use the same rules.CODE.options shape in project files,
and project values override individual option fields, retaining omitted editor
values. Invalid configuration produces a visible error. Unknown options or
invalid option values reset that rule's entire options object to its declared
defaults, without disabling other settings or rules.
SQL comments can suppress a line (-- noqa or -- noqa: CV05, LT02) or a
file/region (-- noqa: disable=CV05, LT02 followed by -- noqa: enable=CV05).
Put a disable directive at the top for file-wide suppression; use all for all
rules. Enable directives cannot override configured-disabled rules.
See Configuration
for precedence and examples.
Rule-specific style options and .sqlfluff compatibility are not yet supported.
Configuration
File associations
GoogleSQL activates automatically for .bqsql, .bqddl, and .bqdml files.
Generic .sql, .ddl, and .dml files are left to your other SQL extensions.
To enable BigQuery linting for them, select GoogleSQL from the language mode
picker or add explicit associations to your workspace's .vscode/settings.json:
{
"files.associations": {
"*.sql": "googlesql",
"*.ddl": "googlesql",
"*.dml": "googlesql"
}
}
Only documents in GoogleSQL language mode are linted. Existing associations to
the legacy sql-bigquery ID should be changed to googlesql.
bigquerySQLFormatter.maxNumberOfProblems defaults to 100 and limits published
diagnostics on both opening and editing a document, after noqa suppression.
Set it to 0 to hide diagnostics. Settings are resolved per document, including
for code actions, and changing configuration revalidates open documents.
Use Format Document or Format Selection in GoogleSQL language mode.
This extension is the default formatter for GoogleSQL unless you have chosen
another formatter. To enable formatting on save:
{
"[googlesql]": {
"editor.defaultFormatter": "sean-conkie.bigquerysqlformatter",
"editor.formatOnSave": true
}
}
Formatting applies enabled, automatically fixable rules repeatedly until the
result is stable. It respects editor/project rule settings and noqa directives,
preserves line endings, and is not limited by maxNumberOfProblems.
Selection formatting only applies fixes wholly contained in the selection;
text outside it is unchanged. Rules without automatic fixes remain diagnostics.
Formatting uses the rules' existing styles and configured capitalisation policies;
custom indentation and comma-position options are not yet supported. Cycling fixes or more than
100 passes report an error without applying partial formatting.
Fix All and fix on save
Fix All combines fixes across the whole document into one action, even when
only some diagnostics are visible. Like formatting, it repeats safe,
non-overlapping fixes until stable, including issues exposed by earlier fixes.
Individual quick fixes remain available.
As an alternative to formatting on save, add this to your settings.json:
{
"[googlesql]": {
"editor.codeActionsOnSave": {
"source.fixAll.bigquerySQLFormatter": "explicit"
}
}
}
The generic "source.fixAll": "explicit" setting also includes this extension.
Disable Rules
In-line directive
To disable all rules for a line add comment -- noqa.
Directives are case-insensitive and accept zero or more spaces after --
(for example --NOQA or -- noqa). Only actual line comments are directives,
not text inside strings or block comments.
select case when cc.enddate is null then 1 else null end as is_current -- noqa
from dataset.table cc;
To disable specific rules for a line add comment -- noqa followed by a comma separated list of rules.
Rule codes are matched exactly, ignoring case: -- noqa: LT01, CV05 disables
only those rules, while -- noqa: LT0 does not disable LT01.
select case when cc.enddate is null then 1 else null end as is_current -- noqa: ST01
from dataset.table cc;
Contributing
For development, testing, adding rules, and release guidance, see
Contributing.
