Introduction to SQL in BigQuery
This document provides an overview of supported statements and SQL dialects in BigQuery.
GoogleSQL is an ANSI compliant Structured Query Language (SQL) which includes the following types of supported statements:
- Query statements, also known as Data Query Language (DQL) statements, are the primary method to analyze data in BigQuery. They scan one or more tables or expressions and return the computed result rows. Query statements can include pipe syntax (Preview).
- Procedural language statements are procedural extensions to GoogleSQL that allow you to execute multiple SQL statements in one request. Procedural statements can use variables and control-flow statements, and can have side effects.
- Data Definition Language (DDL) statements let you create and modify database objects such as tables, views, functions, and row-level access policies.
- Data Manipulation Language (DML) statements enable you to update, insert, and delete data from your BigQuery tables.
- Data Control Language (DCL) statements let you control BigQuery system resources such as access and capacity.
- Transaction Control Language (TCL) statements allow you to manage transactions for data modifications.
- Load statements and export statements to manage data coming in and out of BigQuery.
BigQuery SQL dialects
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 customers migrate if they are using legacy SQL.
Changing from the default dialect
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.
In the query editor, click the More > Query settings button.
In the Advanced options section, for SQL dialect, click Legacy, 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.
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;
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_sql
parameter 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
, 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 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 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.
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.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for client libraries.
Switch to the legacy SQL dialect
To use legacy SQL syntax in a query job, 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.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for client libraries.
Switch to the legacy SQL dialect
To use legacy SQL syntax in a query job, set the UseLegacySQL
property
within the query configuration to true
.
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.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for client libraries.
Switch to the legacy SQL dialect
To use legacy SQL syntax in a query job, set the useLegacySql
parameter
to true
.
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.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for client libraries.
Switch to the legacy SQL dialect
To use legacy SQL syntax in a query job, set the useLegacySql
parameter
to true
.
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.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for client libraries.
Switch to the legacy SQL dialect
To use legacy SQL syntax in a query job, set the useLegacySql
parameter
to true
.
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.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for client libraries.
Switch to the legacy SQL dialect
To use legacy SQL syntax in a query job, set the use_legacy_sql
parameter
to True
.
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.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for client libraries.
Switch to the legacy SQL dialect
To use legacy SQL syntax in a query job, pass the option legacy_sql: true
with your query.
What's next
- For information about how to run a SQL query in BigQuery, see Running interactive and batch query jobs.
- For more information about query optimization in general, see Introduction to optimizing query performance.
- To learn about the GoogleSQL syntax used for querying data in BigQuery, see Query syntax.
- To learn more about how to use pipe syntax in your queries, see pipe syntax.