Enabling Standard SQL

This topic describes how to enable standard SQL for use with your BigQuery statements.

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 publicdata.samples.shakespeare"

C#

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

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

public BigQueryResults SyncQuery(string projectId, string datasetId, string tableId,
    string query, double timeoutMs, BigQueryClient client)
{
    var table = client.GetTable(projectId, datasetId, tableId);
    BigQueryJob job = client.CreateQueryJob(query,
        new CreateQueryJobOptions { UseQueryCache = false });
    // Get the query result, waiting for the timespan specified in milliseconds.
    BigQueryResults result = client.GetQueryResults(job.Reference.JobId,
        new GetQueryResultsOptions { Timeout = TimeSpan.FromMilliseconds(timeoutMs) });
    return result;
}

When running an asynchronous 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,
                new CreateQueryJobOptions { 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);
        }

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

public BigQueryResults LegacySqlSyncQuery(string projectId, string datasetId,
    string tableId, string query, double timeoutMs, BigQueryClient client)
{
    var table = client.GetTable(projectId, datasetId, tableId);
    BigQueryJob job = client.CreateQueryJob(query,
        new CreateQueryJobOptions { UseLegacySql = true });
    // Get the query result, waiting for the timespan specified in milliseconds.
    BigQueryResults result = client.GetQueryResults(job.Reference.JobId,
        new GetQueryResultsOptions { Timeout = TimeSpan.FromMilliseconds(timeoutMs) });
    return result;
}

Go

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

When running a synchronous query, set the UseStandard parameter to true to use standard SQL syntax.

// query returns a slice of the results of a query.
func query(proj string) (*bigquery.RowIterator, error) {
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, proj)
	if err != nil {
		return nil, err
	}

	query := client.Query(
		`SELECT
		 APPROX_TOP_COUNT(corpus, 10) as title,
		 COUNT(*) as unique_words
		 FROM ` + "`publicdata.samples.shakespeare`;")
	// Use standard SQL syntax for queries.
	// See: https://cloud.google.com/bigquery/sql-reference/
	query.QueryConfig.UseStandardSQL = true
	return query.Read(ctx)
}

Java

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

When running a synchronous query, set the useLegacySql parameter to false to use standard SQL syntax.

public static void run(
    final PrintStream out,
    final String queryString,
    final long waitTime,
    final boolean useLegacySql) throws IOException {
  BigQuery bigquery =
      new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());

  QueryRequest queryRequest =
      QueryRequest.newBuilder(queryString)
          .setMaxWaitTime(waitTime)
          // Use standard SQL syntax or legacy SQL syntax for queries.
          // See: https://cloud.google.com/bigquery/sql-reference/
          .setUseLegacySql(useLegacySql)
          .build();
  QueryResponse response = bigquery.query(queryRequest);

  if (response.hasErrors()) {
    throw new RuntimeException(
        response
            .getExecutionErrors()
            .stream()
            .<String>map(err -> err.getMessage())
            .collect(Collectors.joining("\n")));
  }

  QueryResult result = response.getResult();
  Iterator<List<FieldValue>> iter = result.iterateAll();
  while (iter.hasNext()) {
    List<FieldValue> row = iter.next();
    out.println(row.stream().map(val -> val.toString()).collect(Collectors.joining(",")));
  }
}

Node.js

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

When running a synchronous query, set the useLegacySql parameter to false to use standard SQL syntax.

function syncQuery (sqlQuery) {
  // Instantiates a client
  const bigquery = BigQuery();

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

  // Runs the query
  return bigquery.query(options)
    .then((results) => {
      const rows = results[0];
      console.log('Rows:');
      rows.forEach((row) => console.log(row));
      return rows;
    });
}

When running an asynchronous query, set the useLegacySql parameter to false to use standard SQL syntax.

function asyncQuery (sqlQuery) {
  // Instantiates a client
  const bigquery = BigQuery();

  // 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
  return bigquery.startQuery(options)
    .then((results) => {
      job = results[0];
      console.log(`Job ${job.id} started.`);
      return job.promise();
    })
    .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));
      return rows;
    });
}

PHP

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

When running a synchronous query, set the useLegacySql parameter to False to use standard SQL syntax.

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Run a BigQuery query.
 * Example:
 * ```
 * $query = 'SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words ' .
 *          'FROM [publicdata:samples.shakespeare]';
 * run_query($projectId, $query, true);
 * ```.
 *
 * @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($projectId, $query, $useLegacySql)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $queryResults = $bigQuery->runQuery(
        $query,
        ['useLegacySql' => $useLegacySql]);

    if ($queryResults->isComplete()) {
        $i = 0;
        $rows = $queryResults->rows();
        foreach ($rows as $row) {
            printf('--- Row %s ---' . PHP_EOL, ++$i);
            foreach ($row as $column => $value) {
                printf('%s: %s' . PHP_EOL, $column, $value);
            }
        }
        printf('Found %s row(s)' . PHP_EOL, $i);
    } else {
        throw new Exception('The query failed to complete');
    }
}

When running an asynchronous query, set the useLegacySql parameter to False to use standard SQL syntax.

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

/**
 * Run a BigQuery query as a job.
 * Example:
 * ```
 * $query = 'SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words ' .
 *          'FROM [publicdata:samples.shakespeare]';
 * run_query_as_job($projectId, $query, true);
 * ```.
 *
 * @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,
    ]);
    $job = $bigQuery->runQueryAsJob(
        $query,
        ['jobConfig' => ['useLegacySql' => $useLegacySql]]);
    $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();

    if ($queryResults->isComplete()) {
        $i = 0;
        $rows = $queryResults->rows();
        foreach ($rows as $row) {
            printf('--- Row %s ---' . PHP_EOL, ++$i);
            foreach ($row as $column => $value) {
                printf('%s: %s' . PHP_EOL, $column, $value);
            }
        }
        printf('Found %s row(s)' . PHP_EOL, $i);
    } else {
        throw new Exception('The query failed to complete');
    }
}

Python

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

When running a synchronous query, set the use_legacy_sql parameter to False to use standard SQL syntax.

def sync_query(query):
    client = bigquery.Client()
    query_results = client.run_sync_query(query)

    # Use standard SQL syntax for queries.
    # See: https://cloud.google.com/bigquery/sql-reference/
    query_results.use_legacy_sql = False

    query_results.run()

    # Drain the query results by requesting a page at a time.
    page_token = None

    while True:
        rows, total_rows, page_token = query_results.fetch_data(
            max_results=10,
            page_token=page_token)

        for row in rows:
            print(row)

        if not page_token:
            break

When running an asynchronous query, set the use_legacy_sql parameter to False to use standard SQL syntax.

def async_query(query):
    client = bigquery.Client()
    query_job = client.run_async_query(str(uuid.uuid4()), query)
    query_job.use_legacy_sql = False
    query_job.begin()

    wait_for_job(query_job)

    # Drain the query results by requesting a page at a time.
    query_results = query_job.results()
    page_token = None

    while True:
        rows, total_rows, page_token = query_results.fetch_data(
            max_results=10,
            page_token=page_token)

        for row in rows:
            print(row)

        if not page_token:
            break
def wait_for_job(job):
    while True:
        job.reload()  # Refreshes the state via a GET request.
        if job.state == 'DONE':
            if job.error_result:
                raise RuntimeError(job.errors)
            return
        time.sleep(1)

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.

What's next

Send feedback about...

This page
Documentation feedback
BigQuery Documentation
Product feedback