Switching SQL dialects

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 BigQuery web UI, see the Quickstart using the web UI.

To learn how to get started querying data by using the bq command-line tool, see the 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 classic BigQuery web UI, 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:

1. If necessary, click Compose new query to open the query editor window.

2. Below the query editor, click More > Query settings.

3. Click Advanced options.

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

Classic UI

The default dialect for the classic BigQuery web UI is legacy SQL. To change the dialect to standard SQL:

1. In the classic web UI, click Compose Query.

2. Click Show Options.

3. For SQL Dialect, uncheck the Use Legacy SQL box. This sets the standard SQL option while you have the query editor open. If you close the query editor and reopen it, you must deselect 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.

View on GitHub Feedback

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQuery
{
    public void Query(
        string projectId = "your-project-id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        string query = @"
            SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013`
            WHERE state = 'TX'
            LIMIT 100";
        BigQueryJob job = client.CreateQueryJob(
            sql: query,
            parameters: null,
            options: new QueryOptions { UseQueryCache = false });
        // Wait for the job to complete.
        job.PollUntilCompleted();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}");
        }
    }
}

Using legacy SQL

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

View on GitHub Feedback

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQueryLegacy
{
    public void QueryLegacy(
        string projectId = "your-project-id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        string query = @"
            SELECT name FROM [bigquery-public-data:usa_names.usa_1910_2013]
            WHERE state = 'TX'
            LIMIT 100";
        BigQueryJob job = client.CreateQueryJob(
            sql: query,
            parameters: null,
            options: new QueryOptions { UseLegacySql = true });
        // Wait for the job to complete.
        job.PollUntilCompleted();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}");
        }
    }
}

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.

View on GitHub Feedback

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryBasic demonstrates issuing a query and reading results.
func queryBasic(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	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"
	// Run the query and print results when the query job is completed.
	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.Fprintln(w, row)
	}
	return nil
}

Using legacy SQL

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

View on GitHub Feedback

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryLegacy demonstrates running a query using Legacy SQL.
func queryLegacy(w io.Writer, projectID, sqlString string) error {
	// projectID := "my-project-id"
	// sqlString = "SELECT 3 as somenum"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

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

	// Run the query and print results when the query job is completed.
	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.Fprintln(w, row)
	}
	return nil
}

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.

View on GitHub Feedback

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

View on GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class RunLegacyQuery {

  public static void runLegacyQuery() {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

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

      // Execute the query.
      TableResult result = bigquery.query(queryConfig);

      // Print the results.
      result.iterateAll().forEach(rows -> rows.forEach(row -> System.out.println(row.getValue())));

      System.out.println("Legacy query ran successfully");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Legacy query did not run \n" + e.toString());
    }
  }
}

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.

View on GitHub Feedback

// Import the Google Cloud client library using default credentials
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
async function query() {
  // Queries the U.S. given names dataset for the state of Texas.

  const query = `SELECT name
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 100`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

Using legacy SQL

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

View on GitHub Feedback

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryLegacy() {
  // Queries the U.S. given names dataset for the state of Texas using legacy SQL.

  const query =
    'SELECT word FROM [bigquery-public-data:samples.shakespeare] LIMIT 10;';

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    useLegacySql: true,
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

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.

View on GitHub Feedback

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

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $query = 'SELECT id, view_count FROM `bigquery-public-data.stackoverflow.posts_questions`';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$jobConfig = $bigQuery->query($query);
$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);

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.

View on GitHub Feedback

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = """
    SELECT name, SUM(number) as total_people
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE state = 'TX'
    GROUP BY name, state
    ORDER BY total_people DESC
    LIMIT 20
"""
query_job = client.query(query)  # Make an API request.

print("The query data:")
for row in query_job:
    # Row values can be accessed by field name or index.
    print("name={}, count={}".format(row[0], row["total_people"]))

Using legacy SQL

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

View on GitHub Feedback

from google.cloud import bigquery

# Construct a BigQuery client object.
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(use_legacy_sql=True)

# Start the query, passing in the extra configuration.
query_job = client.query(query, job_config=job_config)  # Make an API request.

print("The query data:")
for row in query_job:
    print(row)

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.

View on GitHub Feedback

require "google/cloud/bigquery"

def query
  bigquery = Google::Cloud::Bigquery.new
  sql = "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.
  results = bigquery.query sql do |config|
    config.location = "US"
  end

  results.each do |row|
    puts row.inspect
  end
end

Using a query prefix in the UI

You can also set the SQL dialect in the Cloud Console or the classic web UI 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.

In the classic web UI, when you use a query prefix, the prefix must match the SQL dialect option. For example, in the classic web UI, if you uncheck the Use Legacy SQL option, you cannot use the #legacySQL prefix in your query.

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:

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.