BigQuery 中的 SQL 简介

本文档简要介绍 BigQuery 中支持的语句和 SQL 语言。

概览

GoogleSQL 是一种符合 ANSI 的结构化查询语言 (SQL)，其中包括以下类型的受支持语句：

查询语句（也称为“数据查询语言 (DQL)”语句）是在 BigQuery 中分析数据的主要方法。它们会扫描一个或多个表或表达式，并返回计算的结果行。
过程语言语句是 GoogleSQL 的过程扩展，允许您在一个请求中执行多个 SQL 语句。过程语句可以使用变量和控制流语句，可能会产生副作用。
借助数据定义语言 (DDL) 语句，您可以创建和修改数据库对象，例如表、视图、函数和行级访问权限政策。
借助数据操纵语言 (DML) 语句，您可以在 BigQuery 表中更新、插入和删除数据。
借助数据控制语言 (DCL) 语句，您可以控制 BigQuery 系统资源，例如访问权限和容量。
借助事务控制语言 (TCL) 语句，您可以管理数据修改的事务。
其他语句提供其他功能，例如导出数据。

BigQuery SQL 方言

BigQuery 支持 GoogleSQL 方言，但也可以使用旧版 SQL 方言。如果您刚开始接触 BigQuery，则应使用 GoogleSQL，因为它支持最广泛的功能。例如，只有在使用 GoogleSQL 时才支持 DDL 和 DML 语句等功能。维护旧版 SQL 是为了保持向后兼容性，如果客户使用旧版 SQL，我们建议进行迁移。

更改默认方言

用于查询数据的界面决定了默认的查询语言。如需切换到其他方言，请执行以下操作：

控制台

Google Cloud 控制台的默认方言是 GoogleSQL。如需将方言更改为旧版 SQL，请执行以下操作：

在 Google Cloud 控制台中，转到 BigQuery 页面。

转到 BigQuery
在查询编辑器中，点击更多 > 查询设置按钮。
在高级选项部分中，对于 SQL 方言，点击旧版，然后点击保存。这将为此查询设置旧版 SQL 选项。点击编写新查询以创建新查询时，您必须再次选择旧版 SQL 选项。

SQL

默认的 SQL 方言是 GoogleSQL。您可以通过在查询中包含 #standardSQL 或 #legacySQL 前缀来设置 SQL 方言。这些查询前缀不区分大小写，必须位于查询之前，并且必须以换行符与查询分隔开。以下示例将方言设置为旧版 SQL 并查询出生率数据集：

在 Google Cloud 控制台中，转到 BigQuery 页面。

转到 BigQuery

在查询编辑器中，输入以下语句：

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

点击运行。

如需详细了解如何运行查询，请参阅运行交互式查询。

bq

bq 命令行工具中的默认查询语言是旧版 SQL。如需切换到 GoogleSQL 方言，请在命令行语句中添加 --use_legacy_sql=false 或 --nouse_legacy_sql 标志。

切换到 GoogleSQL 方言

如需在查询作业中使用 GoogleSQL 语法，请将 use_legacy_sql 参数设置为 false。

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

将 GoogleSQL 设置为默认方言

您可以将 GoogleSQL 设置为命令行工具和交互式 Shell 的默认方言，方法是修改命令行工具的配置文件 .bigqueryrc。

如需详细了解 .bigqueryrc，请参阅为特定于命令的标志设置默认值。

如需在 .bigqueryrc 中设置 --use_legacy_sql=false，请执行以下操作：

在文本编辑器中打开 .bigqueryrc。默认情况下，.bigqueryrc 应位于您的用户目录中，例如 $HOME/.bigqueryrc。
将以下文本添加到该文件中。本示例将 GoogleSQL 设置为查询和 mk 命令（用于创建视图）的默认语法。如果您已为 query 或 mk 命令标志配置默认值，则无需重新添加 [query] 或 [mk]。
```
[query]
--use_legacy_sql=false
[mk]
--use_legacy_sql=false
```
保存并关闭文件。
如果您使用的是交互式 Shell，则必须退出并重启，以使更改生效。

如需了解可用的命令行标志，请参阅 bq 命令行工具参考。

C#

试用此示例之前，请按照 BigQuery 快速入门：使用客户端库中的 C# 设置说明进行操作。如需了解详情，请参阅 BigQuery C# API 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

默认情况下，C# 库使用 GoogleSQL。

切换到旧版 SQL 语言

如需在查询作业中使用旧版 SQL 语法，请将 UseLegacySql 参数设置为 true。


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 = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}");
        }
    }
}

Go

试用此示例之前，请按照 BigQuery 快速入门：使用客户端库中的 Go 设置说明进行操作。如需了解详情，请参阅 BigQuery Go API 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

默认情况下，Go 客户端库使用 GoogleSQL。

切换到旧版 SQL 语言

如需在查询作业中使用旧版 SQL 语法，请将查询配置中的 UseLegacySQL 属性设置为 true。

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: %w", err)
	}
	defer client.Close()

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

	// Run the query and process the returned row iterator.
	it, err := q.Read(ctx)
	if err != nil {
		return fmt.Errorf("query.Read(): %w", err)
	}
	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

试用此示例之前，请按照 BigQuery 快速入门：使用客户端库中的 Java 设置说明进行操作。如需了解详情，请参阅 BigQuery Java API 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

默认情况下，Java 客户端库使用 GoogleSQL。

切换到旧版 SQL 语言

如需在查询作业中使用旧版 SQL 语法，请将 useLegacySql 参数设置为 true。

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 main(String[] args) {
    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

试用此示例之前，请按照 BigQuery 快速入门：使用客户端库中的 Node.js 设置说明进行操作。如需了解详情，请参阅 BigQuery Node.js API 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

默认情况下，Node.js 客户端库使用 GoogleSQL。

切换到旧版 SQL 语言

如需在查询作业中使用旧版 SQL 语法，请将 useLegacySql 参数设置为 true。

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

试用此示例之前，请按照 BigQuery 快速入门：使用客户端库中的 PHP 设置说明进行操作。如需了解详情，请参阅 BigQuery PHP API 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

默认情况下，PHP 客户端库使用 GoogleSQL。

切换到旧版 SQL 语言

如需在查询作业中使用旧版 SQL 语法，请将 useLegacySql 参数设置为 true。

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Query using legacy sql
 *
 * @param string $projectId The project Id of your Google Cloud Project.
 */
function query_legacy(string $projectId): void
{
    $query = 'SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus';

    $bigQuery = new BigQueryClient([
      'projectId' => $projectId,
    ]);
    $jobConfig = $bigQuery->query($query)->useLegacySql(true);

    $queryResults = $bigQuery->runQuery($jobConfig);

    $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

试用此示例之前，请按照 BigQuery 快速入门：使用客户端库中的 Python 设置说明进行操作。如需了解详情，请参阅 BigQuery Python API 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

默认情况下，Python 客户端库使用 GoogleSQL。

切换到旧版 SQL 语言

如需在查询作业中使用旧版 SQL 语法，请将 use_legacy_sql 参数设置为 True。

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 and waits for query job to complete, passing in the extra configuration.
results = client.query_and_wait(
    query, job_config=job_config
)  # Make an API request.

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

Ruby

试用此示例之前，请按照 BigQuery 快速入门：使用客户端库中的 Ruby 设置说明进行操作。如需了解详情，请参阅 BigQuery Ruby API 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

默认情况下，Ruby 客户端库使用 GoogleSQL。

切换到旧版 SQL 语言

如需在查询作业中使用旧版 SQL 语法，请在您的查询中传递 legacy_sql: true 选项。

require "google/cloud/bigquery"

def query_legacy
  bigquery = Google::Cloud::Bigquery.new
  sql = "SELECT name FROM [bigquery-public-data:usa_names.usa_1910_2013] " \
        "WHERE state = 'TX' " \
        "LIMIT 100"

  results = bigquery.query sql, legacy_sql: true do |config|
    # Location must match that of the dataset(s) referenced in the query.
    config.location = "US"
  end

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

后续步骤

如需了解如何在 BigQuery 中运行 SQL 查询，请参阅运行交互式查询作业和批量查询作业。
如需详细了解一般情况下的查询优化，请参阅优化查询性能简介。
如需了解在 BigQuery 中查询数据时使用的 GoogleSQL 语法，请参阅查询语法。