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,请执行以下操作:

  1. 在 Google Cloud 控制台中,转到 BigQuery 页面。

    转到 BigQuery

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

  3. 高级选项部分中,对于 SQL 方言,点击旧版,然后点击保存。这将为此查询设置旧版 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 命令(用于创建视图)的默认语法。如果您已为 querymk 命令标志配置默认值,则无需重新添加 [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

后续步骤