BigQuery 中的 SQL 简介

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

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

• 查询语句（也称为“数据查询语言 (DQL)”语句）是在 BigQuery 中分析数据的主要方法。它们会扫描一个或多个表或表达式，并返回计算的结果行。查询语句可以包含管道语法。
• 过程语言语句是 GoogleSQL 的过程扩展，允许您在一个请求中执行多个 SQL 语句。过程语句可以使用变量和控制流语句，可能会产生副作用。

• 借助数据定义语言 (DDL) 语句，您可以创建和修改以下对象：

  • 数据集
  • 表，包括其架构和列类型
  • 表克隆和快照
  • 查看次数
  • 函数
  • 索引
  • 容量承诺、预留和分配
  • 行级访问权限政策

• 借助数据操纵语言 (DML) 语句，您可以在 BigQuery 表中更新、插入和删除数据。

• 借助数据控制语言 (DCL) 语句，您可以控制 BigQuery 系统资源，例如访问权限和容量。

• 借助事务控制语言 (TCL) 语句，您可以管理数据修改的事务。

• 加载语句和导出语句，用于管理进出 BigQuery 的数据。

BigQuery SQL 方言

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

更改默认方言

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

控制台

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

1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

   转到 BigQuery

2. 在查询编辑器中，点击更多 > 查询设置按钮。

3. 在高级选项部分中，对于 SQL 方言，点击旧版，然后点击保存。这将为此查询设置旧版 SQL 选项。点击 add_box SQL 查询以创建新查询时，您必须再次选择旧版 SQL 选项。

SQL

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

1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

   转到 BigQuery

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

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

3. 点击 运行。

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

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，请执行以下操作：

1. 在文本编辑器中打开 .bigqueryrc。默认情况下，.bigqueryrc 应位于您的用户目录中，例如 $HOME/.bigqueryrc。

2. 将以下文本添加到该文件中。本示例将 GoogleSQL 设置为查询和 mk 命令（用于创建视图）的默认语法。如果您已为 query 或 mk 命令标志配置默认值，则无需重新添加 [query] 或 [mk]。

   [query]
   --use_legacy_sql=false
   [mk]
   --use_legacy_sql=false
   

3. 保存并关闭文件。

4. 如果您使用的是交互式 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 语法，请参阅查询语法。
• 如需详细了解如何在查询中使用管道语法，请参阅管道语法。