创建和使用表

本文档介绍了如何在 BigQuery 中创建和使用标准(内置)表。如需了解如何创建其他表类型,请参阅以下内容:

创建表后,您可执行以下操作:

  • 控制对表数据的访问
  • 获取有关表的信息
  • 列出数据集中的表
  • 获取表元数据

如需详细了解如何管理表(包括更新表属性、复制表和删除表),请参阅管理表

准备工作

在 BigQuery 中创建表之前,请先进行以下操作:

表命名

在 BigQuery 中创建表时,数据集中每个表的名称都必须具有唯一性。表名称可:

  • 包含最多 1024 个字符。
  • 包含类别 L(字母)、M(符号)、N(数字)、Pc(连接符,包括下划线)、Pd(短划线)、Zs(空格)中的 Unicode 字符。如需了解详情,请参阅常规类别

例如,以下都是有效的表名称:table-01ग्राहक00_お客様étudiant

创建表

您可以通过以下方式在 BigQuery 中创建表:

  • 使用 Cloud Console 手动创建或使用 bq 命令行工具的 bq mk 命令创建。
  • 调用 tables.insert API 方法以编程方式创建。
  • 使用客户端库。
  • 使用查询结果。
  • 通过定义引用外部数据源的表。
  • 在加载数据时。
  • 使用 CREATE TABLE DDL 语句创建。

所需权限

如需创建表,您至少必须具有以下权限:

  • bigquery.tables.create:创建表的权限
  • bigquery.tables.updateData:使用加载作业、查询作业或复制作业向表中写入数据
  • bigquery.jobs.create:用于运行向表中写入数据的查询作业、加载作业或复制作业

如需访问向表中写入的数据,可能还需要其他权限,例如 bigquery.tables.getData

以下预定义的 IAM 角色同时包含 bigquery.tables.createbigquery.tables.updateData 权限:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

以下预定义的 IAM 角色包含 bigquery.jobs.create 权限:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

此外,如果用户具有 bigquery.datasets.create 权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner 访问权限。借助 bigquery.dataOwner 访问权限,用户可以在数据集内创建和更新表。

如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅预定义的角色和权限

创建具有架构定义的空表

您可以通过以下方式创建具有架构定义的空表:

  • 使用 Cloud Console 输入架构。
  • 使用 bq 命令行工具以内嵌方式提供架构。
  • 使用 bq 命令行工具提交 JSON 架构文件。
  • 调用 API 的 tables.insert 方法时,在表资源中提供架构。

如需详细了解如何指定表架构,请参阅指定架构

创建表后,您可以向表加载数据,或通过写入查询结果来填充表。

如需创建具有架构定义的空表,请执行以下操作:

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 在导航面板的资源部分中,展开您的项目并选择数据集。

  3. 在窗口右侧的详细信息面板中,点击创建表

    创建表

  4. 创建表页面的来源部分,选择空白表

  5. 创建表页面的目标部分,执行以下操作:

    • 数据集名称部分,选择相应数据集。

      选择数据集

    • 表名称字段中,输入您要在 BigQuery 中创建的表的名称。

    • 确认表类型设置为原生表

  6. 架构部分中,输入架构定义。

    • 通过以下方式,手动输入架构信息:

      • 启用以文本形式修改,并以 JSON 数组格式输入表架构。

      • 使用添加字段手动输入架构。

  7. 对于分区和聚簇设置,保留默认值 No partitioning

  8. 对于高级选项部分的加密,保留默认值 Google-managed key。默认情况下,BigQuery 会对以静态方式存储的客户内容进行加密

  9. 点击创建表

SQL

借助数据定义语言 (DDL) 语句,您可以使用标准 SQL 查询语法创建和修改表和视图。

详细了解如何使用数据定义语言语句

如需使用 DDL 语句在 Cloud Console 中创建表,请执行以下操作:

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 点击编写新查询

  3. 查询编辑器文本区域中,输入您的 CREATE TABLE DDL 语句

    以下查询会创建一个名为 newtable 的表,该表将于 2023 年 1 月 1 日到期。该表的说明是“a table that expires in 2023”(一个将在 2023 年到期的表),该表的标签是 org_unit:development

     CREATE TABLE mydataset.newtable
     (
       x INT64 OPTIONS(description="An optional INTEGER field"),
       y STRUCT<
         a ARRAY<STRING> OPTIONS(description="A repeated STRING field"),
         b BOOL
       >
     )
     OPTIONS(
       expiration_timestamp=TIMESTAMP "2023-01-01 00:00:00 UTC",
       description="a table that expires in 2023",
       labels=[("org_unit", "development")]
     )

  4. (可选)点击展开并选择查询设置查询设置

  5. (可选)对于处理位置,点击自动选择并选择数据的位置。如果您将处理位置保留设置为“未指定”,则系统会自动检测处理位置。查询处理位置

  6. 点击运行。查询完成后,该表将显示在资源窗格中。

bq

使用带有 --table-t 标志的 bq mk 命令。您可以内嵌方式或通过 JSON 架构文件提供表的架构信息。可选参数包括:

  • --expiration
  • --description
  • --time_partitioning_type
  • --destination_kms_key
  • --label

此处未演示 --time_partitioning_type--destination_kms_key。如需详细了解 --time_partitioning_type,请参阅提取时间分区表分区表。如需详细了解 --destination_kms_key,请参阅客户管理的加密密钥

如果您要在非默认项目中创建表,请按以下格式将相应项目 ID 添加到数据集中:project_id:dataset

如需在具有架构定义的现有数据集中创建空表,请输入以下命令:

bq mk \
--table \
--expiration integer \
--description description \
--label key:value, key:value \
project_id:dataset.table \
schema

请替换以下内容:

  • integer 是表的默认生命周期(以秒为单位)。最小值为 3600 秒(一小时)。到期时间以当前世界协调时间 (UTC) 加上这个整数值为准。如果您在创建表时设置了该表的到期时间,则系统会忽略数据集的默认表到期时间设置。
  • description 是加英文引号的表说明。
  • key:value 是代表标签的键值对。您可以使用英文逗号分隔列表输入多个标签。
  • project_id 是您的项目 ID。
  • dataset 是您的项目中的数据集。
  • table 是您要创建的表的名称。
  • schema 是采用 field:data_type,field:data_type 格式的内嵌架构定义,或者是本地机器上 JSON 架构文件的路径。

使用命令行指定架构时,您不能添加 RECORD (STRUCT) 类型和列说明,也不能指定列的模式。所有模式均默认为 NULLABLE。如需添加说明、模式和 RECORD 类型,请改为提供 JSON 架构文件

示例:

输入以下命令,使用内嵌架构定义创建表。该命令会在默认项目的 mydataset 中创建一个名为 mytable 的表。表到期时间设为 3600 秒(1 小时),说明设为 This is my table,标签设为 organization:development。该命令使用 -t 快捷键代替 --table。该架构以内嵌方式指定为:qtr:STRING,sales:FLOAT,year:STRING

bq mk \
-t \
--expiration 3600 \
--description "This is my table" \
--label organization:development \
mydataset.mytable \
qtr:STRING,sales:FLOAT,year:STRING

输入以下命令以使用 JSON 架构文件创建表。该命令会在默认项目的 mydataset 中创建一个名为 mytable 的表。表到期时间设为 3600 秒(1 小时),说明设为 This is my table,标签设为 organization:development。架构文件的路径为 /tmp/myschema.json

bq mk \
--table \
--expiration 3600 \
--description "This is my table" \
--label organization:development \
mydataset.mytable \
/tmp/myschema.json

输入以下命令以使用 JSON 架构文件创建表。该命令会在 myotherprojectmydataset 中创建一个名为 mytable 的表。表到期时间设为 3600 秒(1 小时),说明设为 This is my table,标签设为 organization:development。架构文件的路径为 /tmp/myschema.json

bq mk \
--table \
--expiration 3600 \
--description "This is my table" \
--label organization:development \
myotherproject:mydataset.mytable \
/tmp/myschema.json

创建表后,您可以更新表的到期时间、说明和标签,也可以修改架构定义

API

使用已定义的表资源调用 tables.insert 方法。

C#

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


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

public class BigQueryCreateTable
{
    public BigQueryTable CreateTable(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var dataset = client.GetDataset(datasetId);
        // Create schema for new table.
        var schema = new TableSchemaBuilder
        {
            { "full_name", BigQueryDbType.String },
            { "age", BigQueryDbType.Int64 }
        }.Build();
        // Create the table
        return dataset.CreateTable(tableId: "your_table_id", schema: schema);
    }
}

Go

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

import (
	"context"
	"fmt"
	"time"

	"cloud.google.com/go/bigquery"
)

// createTableExplicitSchema demonstrates creating a new BigQuery table and specifying a schema.
func createTableExplicitSchema(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType},
		{Name: "age", Type: bigquery.IntegerFieldType},
	}

	metaData := &bigquery.TableMetadata{
		Schema:         sampleSchema,
		ExpirationTime: time.Now().AddDate(1, 0, 0), // Table will be automatically deleted in 1 year.
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	return nil
}

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class CreateTable {

  public static void runCreateTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    Schema schema =
        Schema.of(
            Field.of("stringField", StandardSQLTypeName.STRING),
            Field.of("booleanField", StandardSQLTypeName.BOOL));
    createTable(datasetName, tableName, schema);
  }

  public static void createTable(String datasetName, String tableName, Schema schema) {
    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();

      TableId tableId = TableId.of(datasetName, tableName);
      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

Node.js

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

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

async function createTable() {
  // Creates a new table named "my_table" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const schema = 'Name:string, Age:integer, Weight:float, IsMagic:boolean';

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $datasetId = 'The BigQuery dataset ID';
// $tableId = 'The BigQuery table ID';
// $fields = [
//    [
//        'name' => 'field1',
//        'type' => 'string',
//        'mode' => 'required'
//    ],
//    [
//        'name' => 'field2',
//        'type' => 'integer'
//    ],
//];

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$schema = ['fields' => $fields];
$table = $dataset->createTable($tableId, ['schema' => $schema]);
printf('Created table %s' . PHP_EOL, $tableId);

Python

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

from google.cloud import bigquery

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

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name"

schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # Make an API request.
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

Ruby

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

require "google/cloud/bigquery"

def create_table dataset_id = "my_dataset"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  table_id = "my_table"

  table = dataset.create_table table_id do |updater|
    updater.string  "full_name", mode: :required
    updater.integer "age",       mode: :required
  end

  puts "Created table: #{table_id}"
end

创建没有架构定义的空表

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create a table without schema
public class CreateTableWithoutSchema {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createTableWithoutSchema(datasetName, tableName);
  }

  public static void createTableWithoutSchema(String datasetName, String tableName) {
    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();

      TableId tableId = TableId.of(datasetName, tableName);
      TableDefinition tableDefinition = StandardTableDefinition.of(Schema.of());
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

基于查询结果创建表

如需基于查询结果创建表,请将结果写入一个目标表。

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 在导航面板的资源部分中,展开您的项目并选择数据集。

  3. 如果查询编辑器处于隐藏状态,请点击窗口右上角的显示编辑器

  4. 查询编辑器文本区域中输入有效的 SQL 查询。

  5. 点击编辑器下方的更多,然后选择查询设置

    查询设置

  6. 勾选为查询结果设置目标表复选框。

    设置目标位置

  7. 目标位置部分中,选择要在其中创建表的相应项目名称数据集名称,然后选择表名称

  8. 目标表的写入设置部分,选择以下选项之一:

    • 只写入空白表 - 仅在表为空时才将查询结果写入表。
    • 附加到表 - 将查询结果附加到现有表。
    • Overwrite table - 使用查询结果覆盖名称相同的现有表。
  9. (可选)在处理位置部分中,点击自动选择并选择您的位置

  10. 点击运行查询。这会创建一个查询作业,并将查询结果写入您指定的表中。

或者,如果您在运行查询之前忘记指定目标表,可以点击编辑器下方的保存结果按钮,将缓存结果表复制到永久表。

SQL

通过数据定义语言 (DDL) 语句,您可以使用标准 SQL 查询语法创建和修改表。

如需了解详情,请参阅 CREATE TABLE 语句页面和 CREATE TABLE 示例:从现有表创建新表

bq

输入 bq query 命令,并指定 --destination_table 标志以根据查询结果创建永久表。指定 use_legacy_sql=false 标志可使用标准 SQL 语法。如需将查询结果写入非默认项目中的某个表,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

(可选)提供 --location 标志并将其值设置为您的位置

如需控制现有目标表的写入处置方式,请指定以下可选标志之一:

  • --append_table:如果目标表存在,指定该标志可将查询结果附加到该表。
  • --replace:如果目标表存在,指定该标志可使用查询结果覆盖该表。
bq --location=location query \
--destination_table project_id:dataset.table \
--use_legacy_sql=false 'query'

请替换以下内容:

  • location 是用于处理查询的位置的名称。--location 标志是可选的。例如,如果您在东京地区使用 BigQuery,可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置位置的默认值。
  • project_id 是项目 ID。
  • dataset 是数据集名称,该数据集包含您要向其中写入查询结果的表。
  • table 是您要向其中写入查询结果的表的名称。
  • query 是使用标准 SQL 语法的查询。

如果未指定“写入处置方式”标志,则默认行为是仅在表为空时写入结果。如果表已存在且非空,则系统会返回以下错误:`BigQuery error in query operation: Error processing job project_id:bqjob_123abc456789_00000e1234f_1': Already Exists: Table project_id:dataset.table

示例:

输入以下命令可将查询结果写入 mydataset 中名为 mytable 的目标表。该数据集在默认项目中。由于命令中未指定“写入处置方式”标志,因此该表必须为新表或空表。否则,系统将返回 Already exists 错误。该查询从美国名字数据公共数据集中检索数据。

bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=false \
'SELECT
  name,
  number
FROM
  `bigquery-public-data`.usa_names.usa_1910_current
WHERE
  gender = "M"
ORDER BY
  number DESC'

输入以下命令可使用查询结果覆盖 mydataset 中名为 mytable 的目标表。该数据集在默认项目中。该命令使用 --replace 标志覆盖目标表。

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

输入以下命令可将查询结果附加到 mydataset 中名为 mytable 的目标表。该数据集属于 my-other-project,而非默认项目。该命令使用 --append_table 标志将查询结果附加到目标表。

bq query \
--append_table \
--use_legacy_sql=false \
--destination_table my-other-project:mydataset.mytable \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

各示例的输出如下所示。为了方便阅读,部分输出已被截断。

Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE
+---------+--------+
|  name   | number |
+---------+--------+
| Robert  |  10021 |
| John    |   9636 |
| Robert  |   9297 |
| ...              |
+---------+--------+

API

如需将查询结果保存到永久表中,请调用 jobs.insert 方法,配置 query 作业,并添加 destinationTable 属性的值。如需控制现有目标表的写入处置方式,请配置 writeDisposition 属性。

如需控制查询作业的处理位置,请在作业资源jobReference 部分中指定 location 属性。

Go

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

import (
	"context"
	"fmt"
	"io"

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

// queryWithDestination demonstrates saving the results of a query to a specific table by setting the destination
// via the API properties.
func queryWithDestination(w io.Writer, projectID, destDatasetID, destTableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	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 17 as my_col")
	q.Location = "US" // Location must match the dataset(s) referenced in query.
	q.QueryConfig.Dst = client.Dataset(destDatasetID).Table(destTableID)
	// 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

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

如需将查询结果保存到永久表中,请在 QueryJobConfiguration 中将目标表设置为所需的 TableId

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.TableId;

public class SaveQueryToTable {

  public static void runSaveQueryToTable() {
    // TODO(developer): Replace these variables before running the sample.
    String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
    String destinationTable = "MY_TABLE";
    String destinationDataset = "MY_DATASET";

    saveQueryToTable(destinationDataset, destinationTable, query);
  }

  public static void saveQueryToTable(
      String destinationDataset, String destinationTableId, String query) {
    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();

      // Identify the destination table
      TableId destinationTable = TableId.of(destinationDataset, destinationTableId);

      // Build the query job
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query).setDestinationTable(destinationTable).build();

      // Execute the query.
      bigquery.query(queryConfig);

      // The results are now saved in the destination table.

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

Node.js

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

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

async function queryDestinationTable() {
  // Queries the U.S. given names dataset for the state of Texas
  // and saves results to permanent table.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // Create destination table reference
  const dataset = bigquery.dataset(datasetId);
  const destinationTable = dataset.table(tableId);

  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/v2/tables#resource
  const options = {
    query: query,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    destination: destinationTable,
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);

  console.log(`Job ${job.id} started.`);
  console.log(`Query results loaded to table ${destinationTable.id}`);
}

Python

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

如需将查询结果保存到永久表中,请创建 QueryJobConfig 并将目标设置为所需的 TableReference。然后,将作业配置传递给查询方法
from google.cloud import bigquery

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

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.QueryJobConfig(destination=table_id)

sql = """
    SELECT corpus
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY corpus;
"""

# Start the query, passing in the extra configuration.
query_job = client.query(sql, job_config=job_config)  # Make an API request.
query_job.result()  # Wait for the job to complete.

print("Query results loaded to the table {}".format(table_id))

创建引用外部数据源的表

外部数据源(也称为“联合数据源”)是可供直接查询的数据源,即使数据未存储在 BigQuery 中也是如此。您不用加载或流式传输数据,只需创建一个引用外部数据源的表即可。

BigQuery 支持直接查询以下各项中的数据:

支持的格式包括:

  • Avro
  • CSV
  • JSON(仅限换行符分隔格式)
  • ORC
  • Parquet

您可以在支持的外部数据源中查询数据,方法是创建一个临时或永久表,该表引用外部数据源中存储的数据。如需详细了解如何使用外部数据源,请参阅:

在加载数据时创建表

将数据加载到 BigQuery 时,可以将数据加载到新的表或分区中,也可以覆盖或附加到现有的表或分区。无需在加载数据前创建空表。您可以同时创建新表和加载数据。

将数据加载到 BigQuery 时,可以提供表或分区架构;对于支持的数据格式,可以使用架构自动检测

要详细了解如何加载数据,请参阅将数据加载到 BigQuery 简介

控制表的访问权限

如需配置对表和视图的访问权限,您可以在以下级层为实体授予 IAM 角色,这些级层按照允许的资源范围从大到小依次排列:

对受 IAM 保护的任何资源的访问权限具有附加性。例如,如果某个实体没有项目等较高级层的访问权限,您可以在数据集级层向该实体授予访问权限,然后该实体便将有权访问该数据集中的表和视图。同样,如果实体没有较高级层或数据集级层的访问权限,您可以在表或视图级层向该实体授予访问权限。

Google Cloud 资源层次结构中授予更高级层(例如项目、文件夹或组织级层)的 IAM 角色,使实体可访问一组广泛的资源。例如,在项目级为实体授予角色会为该实体提供整个项目中所有数据集的访问权限。

在数据集级层授予角色可指定允许实体对该特定数据集中的表和视图执行的操作,即使实体没有更高级层的访问权限也可执行这些操作。如需详细了解如何配置数据集级层的访问权限控制,请参阅控制对数据集的访问权限

在表或视图级层授予角色可指定允许实体对特定表和视图执行的操作,即使实体没有更高级层的访问权限也可执行这些操作。如需了解如何配置表级层的访问权限控制,请参阅控制对表和视图的访问权限

您还可以创建 IAM 自定义角色。如果创建自定义角色,则您授予的权限取决于您希望实体能够执行的具体操作。

您无法对受 IAM 保护的任何资源设置“拒绝”权限。

如需详细了解角色和权限,请参阅:

使用表

获取表的相关信息

您可以通过以下方式获取表的相关信息或元数据:

  • 使用 Cloud Console。
  • 使用 bq 命令行工具的 bq show 命令。
  • 调用 tables.get API 方法。
  • 使用客户端库。
  • 查询 INFORMATION_SCHEMA 视图(Beta 版)。

所需权限

如需获取有关表的信息,您至少必须获得 bigquery.tables.get 权限。以下预定义的 IAM 角色包含 bigquery.tables.get 权限:

  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

此外,如果用户具有 bigquery.datasets.create 权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner 访问权限。借助 bigquery.dataOwner 访问权限,用户可以检索表元数据。

如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制

获取表信息

如需获取表的相关信息,请执行以下操作:

控制台

  1. 在导航面板的资源部分中,展开您的项目并选择数据集。点击数据集名称,将其展开。此时会显示数据集中的表和视图。

  2. 点击表名称。

  3. 在编辑器下方,点击详细信息。此页面会显示表说明和表信息。

  4. 点击 Schema 标签页,查看表的架构定义。

bq

发出 bq show 命令可显示所有表信息。使用 --schema 标志可仅显示表的架构信息。--format 标志可用于控制输出。

如果您要获取非默认项目中的表信息,请按以下格式将相应项目 ID 添加到数据集:project_id:dataset

bq show \
--schema \
--format=prettyjson \
project_id:dataset.table

其中:

  • project_id 是项目 ID。
  • dataset 是数据集的名称。
  • table 是表的名称。

示例:

输入以下命令可显示 mydataset 中有关 mytable 的所有信息。mydataset 属于默认项目。

bq show --format=prettyjson mydataset.mytable

输入以下命令可显示 mydataset 中有关 mytable 的所有信息。mydataset 属于 myotherproject,而非默认项目。

bq show --format=prettyjson myotherproject:mydataset.mytable

输入以下命令可仅显示 mydataset 中有关 mytable 的架构信息。mydataset 属于 myotherproject,而非默认项目。

bq show --schema --format=prettyjson myotherproject:mydataset.mytable

API

调用 tables.get 方法并提供所有相关参数。

Go

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

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// printTableInfo demonstrates fetching metadata from a table and printing some basic information
// to an io.Writer.
func printTableInfo(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	meta, err := client.Dataset(datasetID).Table(tableID).Metadata(ctx)
	if err != nil {
		return err
	}
	// Print basic information about the table.
	fmt.Fprintf(w, "Schema has %d top-level fields\n", len(meta.Schema))
	fmt.Fprintf(w, "Description: %s\n", meta.Description)
	fmt.Fprintf(w, "Rows in managed storage: %d\n", meta.NumRows)
	return nil
}

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;

public class GetTable {

  public static void runGetTable() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "bigquery_public_data";
    String datasetName = "samples";
    String tableName = "shakespeare";
    getTable(projectId, datasetName, tableName);
  }

  public static void getTable(String projectId, String datasetName, String tableName) {
    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();

      TableId tableId = TableId.of(projectId, datasetName, tableName);
      Table table = bigquery.getTable(tableId);
      System.out.println("Table info: " + table.getDescription());
    } catch (BigQueryException e) {
      System.out.println("Table not retrieved. \n" + e.toString());
    }
  }
}

Node.js

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

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

async function getTable() {
  // Retrieves table named "my_table" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Retrieve table reference
  const dataset = bigquery.dataset(datasetId);
  const [table] = await dataset.table(tableId).get();

  console.log('Table:');
  console.log(table.metadata.tableReference);
}
getTable();

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
//$projectId = 'The Google project ID';
//$datasetId = 'The BigQuery dataset ID';
//$tableId   = 'The BigQuery table ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);

Python

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


from google.cloud import bigquery

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

# TODO(developer): Set table_id to the ID of the model to fetch.
# table_id = 'your-project.your_dataset.your_table'

table = client.get_table(table_id)  # Make an API request.

# View table properties
print(
    "Got table '{}.{}.{}'.".format(table.project, table.dataset_id, table.table_id)
)
print("Table schema: {}".format(table.schema))
print("Table description: {}".format(table.description))
print("Table has {} rows".format(table.num_rows))

使用 INFORMATION_SCHEMA 获取表信息(Beta 版

INFORMATION_SCHEMA 是一系列视图,可让您访问数据集、例程、表、视图、作业、预留和流式数据的相关元数据。

您可以查询 INFORMATION_SCHEMA.TABLESINFORMATION_SCHEMA.TABLE_OPTIONS 视图,检索关于项目中的表和视图的元数据。您还可以查询 INFORMATION_SCHEMA.COLUMNSINFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图,检索关于表中的列(字段)的元数据。

TABLESTABLE_OPTIONS 视图还包含关于视图的概要信息。如需查看详细信息,请改为查询 INFORMATION_SCHEMA.VIEWS 视图。

TABLES 视图

查询 INFORMATION_SCHEMA.TABLES 视图时,查询结果为数据集中的每个表或视图返回一行。

INFORMATION_SCHEMA.TABLES 视图具有如下架构:

列名 数据类型
TABLE_CATALOG STRING 包含数据集的项目的名称
TABLE_SCHEMA STRING 包含表或视图的数据集的名称,也称为 datasetId
TABLE_NAME STRING 表或视图的名称,也称为 tableId
TABLE_TYPE STRING 表类型:
IS_INSERTABLE_INTO STRING YESNO,具体取决于表是否支持 DML INSERT 语句
IS_TYPED STRING 值始终为 NO
CREATION_TIME TIMESTAMP 表的创建时间

示例

示例 1:

以下示例从 INFORMATION_SCHEMA.TABLES 视图中检索所有列,is_typed 除外,该列留待将来使用。系统会返回默认项目 myprojectmydataset 的所有表的元数据。

mydataset 包含以下表:

  • mytable1:标准 BigQuery 表
  • myview1:BigQuery 视图

如需对非默认项目运行查询,请按 `project_id`.dataset.INFORMATION_SCHEMA.view 格式将相应的项目 ID 添加到数据集,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLES

要运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    
  3. 点击运行

bq

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES'

结果应如下所示:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 |
  | myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

示例 2:

以下示例从 INFORMATION_SCHEMA.TABLES 视图中检索类型为 BASE TABLE 的所有表。不包括 is_typed 列。返回的元数据包括默认项目 myprojectmydataset 中的所有表。

如需对非默认项目运行查询,请按 `project_id`.dataset.INFORMATION_SCHEMA.view 格式将相应的项目 ID 添加到数据集,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLES

要运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    WHERE
     table_type="BASE TABLE"
    
  3. 点击运行

bq

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES
 WHERE
   table_type="BASE TABLE"'

结果应如下所示:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | NO                 | 2018-10-31 22:40:05 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

TABLE_OPTIONS 视图

查询 INFORMATION_SCHEMA.TABLE_OPTIONS 视图时,查询结果为数据集中的每个表或视图返回一行。

INFORMATION_SCHEMA.TABLE_OPTIONS 视图具有如下架构:

列名 数据类型
TABLE_CATALOG STRING 包含数据集的项目的名称
TABLE_SCHEMA STRING 包含表或视图的数据集的名称,也称为 datasetId
TABLE_NAME STRING 表或视图的名称,也称为 tableId
OPTION_NAME STRING 选项表中的一个名称值
OPTION_TYPE STRING 选项表中的一个数据类型值
OPTION_VALUE STRING 选项表中的一个值选项
选项表
OPTION_NAME OPTION_TYPE OPTION_VALUE
partition_expiration_days FLOAT64 分区表中所有分区的默认生命周期(以天为单位)
expiration_timestamp FLOAT64 此表的到期时间
kms_key_name STRING 用于加密表的 Cloud KMS 密钥的名称
friendly_name STRING 表的描述性名称
description STRING 表的说明
labels ARRAY<STRUCT<STRING, STRING>> 一组 STRUCT,表示表的标签
require_partition_filter BOOL 对于表执行的查询是否需要分区过滤条件

示例

示例 1:

以下示例通过查询 INFORMATION_SCHEMA.TABLE_OPTIONS 视图来检索默认项目 (myproject) 中的 mydataset 中的所有表的默认表到期时间。

如需对非默认项目运行查询,请按 `project_id`.dataset.INFORMATION_SCHEMA.view 格式将相应的项目 ID 添加到数据集,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS

要运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="expiration_timestamp"
    
  3. 点击运行

bq

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="expiration_timestamp"'

结果应如下所示:

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | table_catalog  | table_schema  | table_name |     option_name      | option_type |             option_value             |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | myproject      | mydataset     | mytable1   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2020-01-16T21:12:28.000Z" |
  | myproject      | mydataset     | mytable2   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2021-01-01T21:12:28.000Z" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

示例 2:

以下示例检索 mydataset 中包含测试数据的所有表的元数据。该查询使用 description 选项中的值查找在说明的任何位置包含“test”的表。mydataset 位于默认项目 myproject 中。

如需对非默认项目运行查询,请按 `project_id`.dataset.INFORMATION_SCHEMA.view 格式将相应的项目 ID 添加到数据集,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS

要运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. 点击运行

bq

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="description" AND option_value LIKE "%test%"'

结果应如下所示:

  +----------------+---------------+------------+-------------+-------------+--------------+
  | table_catalog  | table_schema  | table_name | option_name | option_type | option_value |
  +----------------+---------------+------------+-------------+-------------+--------------+
  | myproject      | mydataset     | mytable1   | description | STRING      | "test data"  |
  | myproject      | mydataset     | mytable2   | description | STRING      | "test data"  |
  +----------------+---------------+------------+-------------+-------------+--------------+
  

COLUMNS 视图

查询 INFORMATION_SCHEMA.COLUMNS 视图时,查询为表中的每一列(字段)返回一行结果。

INFORMATION_SCHEMA.COLUMNS 视图具有如下架构:

列名 数据类型
TABLE_CATALOG STRING 包含数据集的项目的名称
TABLE_SCHEMA STRING 包含表的数据集的名称,也称为 datasetId
TABLE_NAME STRING 表或视图的名称,也称为 tableId
COLUMN_NAME STRING 列的名称
ORDINAL_POSITION INT64 表中列的偏移量,从 1 开始计数;如果列为伪列(例如 _PARTITIONTIME 或 _PARTITIONDATE),则值为 NULL
IS_NULLABLE STRING YESNO,具体取决于列的模式是否允许使用 NULL
DATA_TYPE STRING 列的标准 SQL 数据类型
IS_GENERATED STRING 值始终为 NEVER
GENERATION_EXPRESSION STRING 值始终为 NULL
IS_STORED STRING 值始终为 NULL
IS_HIDDEN STRING YESNO,具体取决于列是否为伪列,例如 _PARTITIONTIME 或 _PARTITIONDATE
IS_UPDATABLE STRING 值始终为 NULL
IS_SYSTEM_DEFINED STRING YESNO,具体取决于列是否为伪列,例如 _PARTITIONTIME 或 _PARTITIONDATE
IS_PARTITIONING_COLUMN STRING YESNO,具体取决于列是否为分区列
CLUSTERING_ORDINAL_POSITION INT64 表的聚簇列中列的偏移量,从 1 开始计数;如果表不是聚簇表,则值为 NULL

示例

以下示例从 census_bureau_usa 数据集内 population_by_zip_2010 表的 INFORMATION_SCHEMA.COLUMNS 视图中检索元数据。此数据集是 BigQuery 公共数据集计划的一部分。

由于您查询的表属于 bigquery-public-data 项目,因此您应按 `project_id`.dataset.INFORMATION_SCHEMA.view 格式将相应项目 ID 添加到数据集;例如 `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES

以下列会从查询结果中排除,因为它们目前预留供将来使用:

  • IS_GENERATED
  • GENERATION_EXPRESSION
  • IS_STORED
  • IS_UPDATABLE

要运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
    FROM
     `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
    WHERE
     table_name="population_by_zip_2010"
    
  3. 点击运行

bq

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
 FROM
   `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
 WHERE
   table_name="population_by_zip_2010"'

结果应如下所示。为确保可读性,table_catalogtable_schema 会从结果中排除:

+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
|       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
  

COLUMN_FIELD_PATHS 视图

当您查询 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图时,查询为嵌套RECORD(或 STRUCT)列中的每一列返回一行结果。

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图具有如下架构:

列名 数据类型
TABLE_CATALOG STRING 包含数据集的项目的名称
TABLE_SCHEMA STRING 包含表的数据集的名称,也称为 datasetId
TABLE_NAME STRING 表或视图的名称,也称为 tableId
COLUMN_NAME STRING 列的名称
FIELD_PATH STRING 嵌套在 `RECORD` 或 `STRUCT` 列中的列的路径
DATA_TYPE STRING 列的标准 SQL 数据类型
DESCRIPTION STRING 列的说明

示例

以下示例从 github_repos 数据集commits 表的 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图中检索元数据。此数据集是 BigQuery 公共数据集计划的一部分。

由于您查询的表属于 bigquery-public-data 项目,因此您应按 `project_id`.dataset.INFORMATION_SCHEMA.view 格式将相应项目 ID 添加到数据集;例如 `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS

commits 表包含以下嵌套列以及嵌套和重复列:

  • author:嵌套的 RECORD
  • committer:嵌套的 RECORD
  • trailer:嵌套且重复的 RECORD
  • difference:嵌套且重复的 RECORD

查询将检索有关 authordifference 列的元数据。

如需运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     *
    FROM
     `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
    WHERE
     table_name="commits"
     AND column_name="author"
     OR column_name="difference"
    
  3. 点击运行

bq

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
 WHERE
   table_name="commits"
   AND column_name="author"
   OR column_name="difference"'

结果应如下所示。为确保可读性,table_catalogtable_schema 会从结果中排除。

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        |
  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        |
  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        |
  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

列出数据集中的表

您可以通过以下方式列出数据集中的表:

  • 使用 Cloud Console。
  • 使用 bq 命令行工具的 bq ls 命令。
  • 调用 tables.list API 方法。
  • 使用客户端库。

所需权限

如需列出数据集中的表,您至少必须具有 bigquery.tables.list 权限。以下预定义的 IAM 角色包含 bigquery.tables.list 权限:

  • bigquery.user
  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制

列出表

如需列出数据集中的表,请执行以下操作:

控制台

  1. 在 Cloud Console 的导航窗格中,点击您的数据集将其展开。此时会显示数据集中的表和视图。

  2. 滚动列表,查看数据集中的表。表和视图由不同的图标进行标识。

bq

发出 bq ls 命令。--format 标志可用于控制输出。如果您要列出非默认项目中的表,请按以下格式将相应项目 ID 添加到数据集中:project_id:dataset

您还可以使用其他标志,包括:

  • --max_results-n:用于表示结果数量上限的整数,默认值为 50
bq ls \
--format=pretty \
--max_results integer \
project_id:dataset

其中:

  • integer 是一个整数,表示要列出的表的数量。
  • project_id 是您的项目 ID。
  • dataset 是数据集的名称。

运行该命令时,Type 字段会显示 TABLEVIEW。例如:

+-------------------------+-------+----------------------+-------------------+
|         tableId         | Type  |        Labels        | Time Partitioning |
+-------------------------+-------+----------------------+-------------------+
| mytable                 | TABLE | department:shipping  |                   |
| myview                  | VIEW  |                      |                   |
+-------------------------+-------+----------------------+-------------------+

示例:

输入以下命令可列出默认项目的数据集 mydataset 中的表。

bq ls --format=pretty mydataset

输入以下命令可从 mydataset 返回超出默认输出数量(50 个)的表。mydataset 位于您的默认项目中。

bq ls --format=pretty --max_results 60 mydataset

输入以下命令可列出 myotherproject 的数据集 mydataset 中的表。

bq ls --format=pretty myotherproject:mydataset

API

如需使用 API 列出表,请调用 tables.list 方法。

C#

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


using Google.Cloud.BigQuery.V2;
using System;
using System.Collections.Generic;
using System.Linq;

public class BigQueryListTables
{
    public void ListTables(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        // Retrieve list of tables in the dataset
        List<BigQueryTable> tables = client.ListTables(datasetId).ToList();
        // Display the results
        if (tables.Count > 0)
        {
            Console.WriteLine($"Tables in dataset {datasetId}:");
            foreach (var table in tables)
            {
                Console.WriteLine($"\t{table.Reference.TableId}");
            }
        }
        else
        {
            Console.WriteLine($"{datasetId} does not contain any tables.");
        }
    }
}

Go

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

import (
	"context"
	"fmt"
	"io"

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

// listTables demonstrates iterating through the collection of tables in a given dataset.
func listTables(w io.Writer, projectID, datasetID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	ts := client.Dataset(datasetID).Tables(ctx)
	for {
		t, err := ts.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "Table: %q\n", t.TableID)
	}
	return nil
}

Java

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

import com.google.api.gax.paging.Page;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQuery.TableListOption;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.DatasetId;
import com.google.cloud.bigquery.Table;

public class ListTables {

  public static void runListTables() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "bigquery-public-data";
    String datasetName = "samples";
    listTables(projectId, datasetName);
  }

  public static void listTables(String projectId, String datasetName) {
    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();

      DatasetId datasetId = DatasetId.of(projectId, datasetName);
      Page<Table> tables = bigquery.listTables(datasetId, TableListOption.pageSize(100));
      tables.iterateAll().forEach(table -> System.out.print(table.getTableId().getTable() + "\n"));

      System.out.println("Tables listed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Tables were not listed. Error occurred: " + e.toString());
    }
  }
}

Node.js

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

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

async function listTables() {
  // Lists tables in 'my_dataset'.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';

  // List all tables in the dataset
  const [tables] = await bigquery.dataset(datasetId).getTables();

  console.log('Tables:');
  tables.forEach(table => console.log(table.id));
}

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId  = 'The Google project ID';
// $datasetId  = 'The BigQuery dataset ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$tables = $dataset->tables();
foreach ($tables as $table) {
    print($table->id() . PHP_EOL);
}

Python

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


from google.cloud import bigquery

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

# TODO(developer): Set dataset_id to the ID of the dataset that contains
#                  the tables you are listing.
# dataset_id = 'your-project.your_dataset'

tables = client.list_tables(dataset_id)  # Make an API request.

print("Tables contained in '{}':".format(dataset_id))
for table in tables:
    print("{}.{}.{}".format(table.project, table.dataset_id, table.table_id))

Ruby

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

require "google/cloud/bigquery"

def list_tables dataset_id = "your_dataset_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id

  puts "Tables in dataset #{dataset_id}:"
  dataset.tables.each do |table|
    puts "\t#{table.table_id}"
  end
end

后续步骤