Introduction to SQL in BigQuery (original) (raw)

This document provides an overview of supported statements and SQL dialects in BigQuery.

BigQuery supports the GoogleSQL dialect, but a legacy SQL dialect is also available. If you are new to BigQuery, you should use GoogleSQL as it supports the broadest range of functionality. For example, features such as DDL and DML statements are only supported using GoogleSQL. Legacy SQL is maintained for backward compatibility and we recommend that customersmigrateif they are using legacy SQL.

The interface you use to query your data determines which query dialect is the default. To switch to a different dialect:

Console

The default dialect for the Google Cloud console is GoogleSQL. To change the dialect to legacy SQL:

In the Google Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, click the More > Query settings button.
In the Advanced options section, for SQL dialect, clickLegacy, then click Save. This sets the legacy SQL option for this query. When you click add_box SQL Query to create a new query, you must select the legacy SQL option again.

SQL

The default SQL dialect is GoogleSQL. You can set the SQL dialect by including the prefix#standardSQL or #legacySQL as part of your query. These query prefixes are not case-sensitive, must precede the query, and must be separated from the query by a newline character. The following example sets the dialect to legacy SQL and queries the natality dataset:

In the Google Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, enter the following statement:

#legacySQL
SELECT
weight_pounds, state, year, gestation_weeks
FROM
[bigquery-public-data:samples.natality]
ORDER BY
weight_pounds DESC
LIMIT
10; 3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

The default query dialect in the bq command-line tool is legacy SQL. To switch to the GoogleSQL dialect, add the --use_legacy_sql=false or--nouse_legacy_sql flag to your command-line statement.

Switch to the GoogleSQL dialect

To use GoogleSQL syntax in a query job, set the use_legacy_sqlparameter to false.

  bq query \
  --use_legacy_sql=false \
  'SELECT
    word
  FROM
    `bigquery-public-data.samples.shakespeare`'

Set GoogleSQL as the default dialect

You can set GoogleSQL as the default dialect for the command-line tool and the interactive shell by editing the command-line tool's configuration file:.bigqueryrc.

For more information on .bigqueryrc, seeSetting 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 GoogleSQL as the default syntax for queries and for the mk command (used when you create a view). If you have already configured default values for query or mk 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, seebq command-line tool reference.