Enabling Standard SQL

This topic describes how to enable standard SQL when you query BigQuery data.

To learn how to get started with the BigQuery web UI, see the Quickstart Using the Web UI.

To learn how to get started with the bq command-line tool, see the Quickstart Using the bq Command-Line Tool.

Setting the useLegacySql option

To enable standard SQL for a query:

Web UI

  1. Open the BigQuery web UI.
  2. Click Compose Query.
  3. Click Show Options.
  4. Uncheck the Use Legacy SQL checkbox.

Command-line

Add the --use_legacy_sql=false flag to your command line statement.

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

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

When running a query, standard SQL is used by default.

public BigQueryResults AsyncQuery(string projectId, string datasetId, string tableId,
    string query, BigQueryClient client)
{
    var table = client.GetTable(projectId, datasetId, tableId);
    BigQueryJob job = client.CreateQueryJob(query,
        parameters: null,
        options: new QueryOptions { UseQueryCache = false });

    // Wait for the job to complete.
    job.PollUntilCompleted();

    // Then we can fetch the results, either via the job or by accessing
    // the destination table.
    return client.GetQueryResults(job.Reference.JobId);
}

Using legacy SQL

To use legacy SQL when running queries, set the UseLegacySql parameter to true.

public BigQueryResults LegacySqlAsyncQuery(string projectId, string datasetId,
    string tableId, string query, BigQueryClient client)
{
    var table = client.GetTable(projectId, datasetId, tableId);
    BigQueryJob job = client.CreateQueryJob(query,
        parameters: null,
        options: new QueryOptions { UseLegacySql = true });

    // Wait for the job to complete.
    job.PollUntilCompleted();

    // Then we can fetch the results, either via the job or by accessing
    // the destination table.
    return client.GetQueryResults(job.Reference.JobId);
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

By default, the Go client library uses standard SQL.

q := client.Query(
	"SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` " +
		"WHERE state = \"TX\" " +
		"LIMIT 100")
// Location must match that of the dataset(s) referenced in the query.
q.Location = "US"
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Using legacy SQL

To switch a query back to legacy, leverage the UseLegacySQL property within the query configuration.

q := client.Query(sqlString)
q.UseLegacySQL = true

job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

By default, the Java client library uses standard SQL.

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Using legacy SQL

Set the useLegacySql parameter to true to use legacy SQL syntax in a query job.

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
String query = "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    // To use legacy SQL syntax, set useLegacySql to true.
    QueryJobConfiguration.newBuilder(query).setUseLegacySql(true).build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

By default, the Node.js client library uses standard SQL.

// Imports the Google Cloud client library
const BigQuery = require('@google-cloud/bigquery');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = "your-project-id";
// const sqlQuery = "SELECT * FROM publicdata.samples.natality LIMIT 5;";

// Creates a client
const bigquery = new BigQuery({
  projectId: projectId,
});

// Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
const options = {
  query: sqlQuery,
  useLegacySql: false, // Use standard SQL syntax for queries.
};

let job;

// Runs the query as a job
bigquery
  .createQueryJob(options)
  .then(results => {
    job = results[0];
    console.log(`Job ${job.id} started.`);
    return job.promise();
  })
  .then(() => {
    // Get the job's status
    return job.getMetadata();
  })
  .then(metadata => {
    // Check the job's status for errors
    const errors = metadata[0].status.errors;
    if (errors && errors.length > 0) {
      throw errors;
    }
  })
  .then(() => {
    console.log(`Job ${job.id} completed.`);
    return job.getQueryResults();
  })
  .then(results => {
    const rows = results[0];
    console.log('Rows:');
    rows.forEach(row => console.log(row));
  })
  .catch(err => {
    console.error('ERROR:', err);
  });

Using legacy SQL

Set the useLegacySql parameter to true to use legacy SQL syntax in a query job.

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

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.

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\Core\ExponentialBackoff;

/**
 * Run a BigQuery query as a job.
 * Example:
 * ```
 * $query = 'SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words ' .
 *          'FROM `bigquery-public-data.samples.shakespeare`';
 * run_query_as_job($projectId, $query, false);
 * ```.
 *
 * @param string $projectId The Google project ID.
 * @param string $query     A SQL query to run. *
 * @param bool $useLegacySql Specifies whether to use BigQuery's legacy SQL
 *        syntax or standard SQL syntax for this query.
 */
function run_query_as_job($projectId, $query, $useLegacySql)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $jobConfig = $bigQuery->query($query)->useLegacySql($useLegacySql);
    $job = $bigQuery->startQuery($jobConfig);

    $backoff = new ExponentialBackoff(10);
    $backoff->execute(function () use ($job) {
        print('Waiting for job to complete' . PHP_EOL);
        $job->reload();
        if (!$job->isComplete()) {
            throw new Exception('Job has not yet completed', 500);
        }
    });
    $queryResults = $job->queryResults();

    $i = 0;
    foreach ($queryResults as $row) {
        printf('--- Row %s ---' . PHP_EOL, ++$i);
        foreach ($row as $column => $value) {
            printf('%s: %s' . PHP_EOL, $column, json_encode($value));
        }
    }
    printf('Found %s row(s)' . PHP_EOL, $i);
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

By default, the Python client library uses standard SQL.

# from google.cloud import bigquery
# client = bigquery.Client()

query = (
    'SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` '
    'WHERE state = "TX" '
    'LIMIT 100')
query_job = client.query(
    query,
    # Location must match that of the dataset(s) referenced in the query.
    location='US')  # API request - starts the query

for row in query_job:  # API request - fetches results
    # Row values can be accessed by field name or index
    assert row[0] == row.name == row['name']
    print(row)

Using legacy SQL

Set the use_legacy_sql parameter to True to use legacy SQL syntax in a query job.

# from google.cloud import bigquery
# client = bigquery.Client()

query = (
    'SELECT name FROM [bigquery-public-data:usa_names.usa_1910_2013] '
    'WHERE state = "TX" '
    'LIMIT 100')

# Set use_legacy_sql to True to use legacy SQL syntax.
job_config = bigquery.QueryJobConfig()
job_config.use_legacy_sql = True

query_job = client.query(
    query,
    # Location must match that of the dataset(s) referenced in the query.
    location='US',
    job_config=job_config)  # API request - starts the query

# Print the results.
for row in query_job:  # API request - fetches results
    print(row)

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

By default, the Ruby client library uses standard SQL.

# project_id   = "your google cloud project id"
# query_string = "query string to execute (using bigquery query syntax)"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id

puts "Running query"
query_job = bigquery.query_job query_string

puts "Waiting for query to complete"
query_job.wait_until_done!

puts "Query results:"
query_job.query_results.each do |row|
  puts row.inspect
end

Setting a query prefix

You can set the SQL variant, either legacy SQL or standard SQL, by including a prefix to your query in the web UI, REST API, or when using a client library.

Prefix Description
#legacySQL Runs the query using legacy SQL
#standardSQL Runs the query using standard SQL

For example, if you copy and paste the following query into the web UI, BigQuery runs the query using standard SQL and ignores the default Use Legacy SQL option setting.

#standardSQL
SELECT
  weight_pounds, state, year, gestation_weeks
FROM
  `bigquery-public-data.samples.natality`
ORDER BY weight_pounds DESC
LIMIT 10;

If you explicitly set the useLegacySql option, it must be consistent with the prefix. For example, in the web UI, if you uncheck the Use Legacy SQL option, you cannot use the #legacySQL prefix.

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:

  1. Open .bigqueryrc in a text editor. By default, .bigqueryrc should be in your user directory, for example, $HOME/.bigqueryrc.

  2. 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 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
    
  3. Save and close the file.

  4. 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.

What's next

Was this page helpful? Let us know how we did:

Send feedback about...