This topic describes how to set the query dialect when you query BigQuery data. You can use either the standard SQL or legacy SQL dialect.
To learn how to get started querying data by using the Google Cloud Console, see Quickstart using the Cloud Console.
To learn how to get started querying data by using the bq
command-line tool,
see Quickstart using the bq
command-line tool.
Changing from the default dialect
The interface you use to query your data determines which query dialect is the default:
- In the Cloud Console and the client libraries, standard SQL is the default.
- In the
bq
command-line tool and the REST API, legacy SQL is the default.
To switch to a different dialect:
Console
The default dialect for the Cloud Console is standard SQL. To change the dialect to legacy SQL:
If necessary, click Compose new query to open the query editor window.
Below the query editor, click More > Query settings.
Click Advanced options.
In the Additional settings section, for SQL dialect, click Legacy. This sets the legacy SQL option for this query. When you click Compose new query to create a new query, you must select the legacy sql option again.
bq
The default query dialect in the bq
command-line tool is legacy SQL. To
switch to the standard SQL dialect, add the --use_legacy_sql=false
or
--nouse_legacy_sql
flag to your command-line statement.
For example:
bq query \ --use_legacy_sql=false \ 'SELECT word FROM `bigquery-public-data.samples.shakespeare`'
C#
Before trying this sample, follow the C# setup instructions in the
BigQuery Quickstart Using Client Libraries.
For more information, see the
BigQuery C# API reference documentation.
When running a query, standard SQL is used by default.
Using legacy SQL
To use legacy SQL when running queries, set the `UseLegacySql` parameter to `true`.
Go
Before trying this sample, follow the Go setup instructions in the
BigQuery Quickstart Using Client Libraries.
For more information, see the
BigQuery Go API reference documentation.
By default, the Go client library uses standard SQL.
Using legacy SQL
To switch a query back to legacy, leverage the UseLegacySQL
property within the query configuration.
Java
Before trying this sample, follow the Java setup instructions in the
BigQuery Quickstart Using Client Libraries.
For more information, see the
BigQuery Java API reference documentation.
By default, the Java client library uses standard SQL.
Using legacy SQL
Set the useLegacySql
parameter to true
to use legacy SQL syntax in a query
job.
Node.js
Before trying this sample, follow the Node.js setup instructions in the
BigQuery Quickstart Using Client Libraries.
For more information, see the
BigQuery Node.js API reference documentation.
By default, the Node.js client library uses standard SQL.
Using legacy SQL
Set the useLegacySql
parameter to true
to use legacy SQL syntax in a query
job.
PHP
Before trying this sample, follow the PHP setup instructions in the
BigQuery Quickstart Using Client Libraries.
For more information, see the
BigQuery PHP API reference documentation.
By default, the PHP client library uses standard SQL.
Using legacy SQL
Set the useLegacySql
parameter to true
to use legacy SQL syntax in a query
job.
Python
Before trying this sample, follow the Python setup instructions in the
BigQuery Quickstart Using Client Libraries.
For more information, see the
BigQuery Python API reference documentation.
By default, the Python client library uses standard SQL.
Using legacy SQL
Set the use_legacy_sql
parameter to True
to use legacy SQL syntax in a
query job.
Ruby
Before trying this sample, follow the Ruby setup instructions in the
BigQuery Quickstart Using Client Libraries.
For more information, see the
BigQuery Ruby API reference documentation.
By default, the Ruby client library uses standard SQL.
Using a query prefix in the Cloud Console
You can also set the SQL dialect in the Cloud Console by including a SQL dialect prefix as part of your query.
The following prefixes let you set the SQL dialect:
Prefix | Description |
---|---|
#legacySQL |
Runs the query using legacy SQL |
#standardSQL |
Runs the query using standard SQL |
In the Cloud Console, when you use a query prefix, the SQL dialect option is disabled in the Query settings.
For example, if you copy and paste the following query into the Cloud Console, BigQuery runs the query using legacy SQL and ignores the default setting. The default setting for the Cloud Console is standard SQL.
#legacySQL
SELECT
weight_pounds, state, year, gestation_weeks
FROM
[bigquery-public-data:samples.natality]
ORDER BY weight_pounds DESC
LIMIT 10;
The query prefixes #legacySQL
and #standardSQL
:
- Are NOT case-sensitive
- Must precede the query
- Must be separated from the query by a newline character
Some third-party tools might not support this prefix if, for example, they modify the query text before sending it to BigQuery.
Setting standard SQL as the default for the command-line tool
You can set standard SQL as the default syntax for the command-line tool and
the interactive shell by editing the command-line tool's configuration file:
.bigqueryrc
.
For more information on .bigqueryrc
, see
Setting default values for command-specific flags.
To set --use_legacy_sql=false
in .bigqueryrc
:
Open
.bigqueryrc
in a text editor. By default,.bigqueryrc
should be in your user directory, for example,$HOME/.bigqueryrc
.Add the following text to the file. This example sets standard SQL as the default syntax for queries and for the
mk
command (used when you create a view). If you have already configured default values forquery
ormk
command flags, you do not need to add[query]
or[mk]
again.[query] --use_legacy_sql=false [mk] --use_legacy_sql=false
Save and close the file.
If you are using the interactive shell, you must exit and restart for the changes to be applied.
For information on available command-line flags, see bq command-line tool reference.