在架构中指定嵌套和重复的列。
深入探索
如需查看包含此代码示例的详细文档,请参阅以下内容:
代码示例
Go
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
import (
"context"
"fmt"
"io"
"cloud.google.com/go/bigquery"
)
// createTableComplexSchema demonstrates creating a BigQuery table and specifying a complex schema that includes
// an array of Struct types.
func createTableComplexSchema(w io.Writer, 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: %w", err)
}
defer client.Close()
sampleSchema := bigquery.Schema{
{Name: "id", Type: bigquery.StringFieldType},
{Name: "first_name", Type: bigquery.StringFieldType},
{Name: "last_name", Type: bigquery.StringFieldType},
{Name: "dob", Type: bigquery.DateFieldType},
{Name: "addresses",
Type: bigquery.RecordFieldType,
Repeated: true,
Schema: bigquery.Schema{
{Name: "status", Type: bigquery.StringFieldType},
{Name: "address", Type: bigquery.StringFieldType},
{Name: "city", Type: bigquery.StringFieldType},
{Name: "state", Type: bigquery.StringFieldType},
{Name: "zip", Type: bigquery.StringFieldType},
{Name: "numberOfYears", Type: bigquery.StringFieldType},
}},
}
metaData := &bigquery.TableMetadata{
Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
return err
}
fmt.Fprintf(w, "created table %s\n", tableRef.FullyQualifiedName())
return nil
}
Java
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Java 设置说明进行操作。如需了解详情,请参阅 BigQuery Java API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
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.Field.Mode;
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 NestedRepeatedSchema {
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";
createTableWithNestedRepeatedSchema(datasetName, tableName);
}
public static void createTableWithNestedRepeatedSchema(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);
Schema schema =
Schema.of(
Field.of("id", StandardSQLTypeName.STRING),
Field.of("first_name", StandardSQLTypeName.STRING),
Field.of("last_name", StandardSQLTypeName.STRING),
Field.of("dob", StandardSQLTypeName.DATE),
// create the nested and repeated field
Field.newBuilder(
"addresses",
StandardSQLTypeName.STRUCT,
Field.of("status", StandardSQLTypeName.STRING),
Field.of("address", StandardSQLTypeName.STRING),
Field.of("city", StandardSQLTypeName.STRING),
Field.of("state", StandardSQLTypeName.STRING),
Field.of("zip", StandardSQLTypeName.STRING),
Field.of("numberOfYears", StandardSQLTypeName.STRING))
.setMode(Mode.REPEATED)
.build());
TableDefinition tableDefinition = StandardTableDefinition.of(schema);
TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();
bigquery.create(tableInfo);
System.out.println("Table with nested and repeated schema created successfully");
} catch (BigQueryException e) {
System.out.println("Table was not created. \n" + e.toString());
}
}
}
Node.js
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Node.js 设置说明进行操作。如需了解详情,请参阅 BigQuery Node.js API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
async function nestedRepeatedSchema() {
// Creates a new table named "my_table" in "my_dataset"
// with nested and repeated columns in schema.
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const datasetId = "my_dataset";
// const tableId = "my_table";
// const schema = [
// {name: 'Name', type: 'STRING', mode: 'REQUIRED'},
// {
// name: 'Addresses',
// type: 'RECORD',
// mode: 'REPEATED',
// fields: [
// {name: 'Address', type: 'STRING'},
// {name: 'City', type: 'STRING'},
// {name: 'State', type: 'STRING'},
// {name: 'Zip', type: 'STRING'},
// ],
// },
// ];
// 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.`);
}
Python
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
from google.cloud import bigquery
client = bigquery.Client()
# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table_name"
schema = [
bigquery.SchemaField("id", "STRING", mode="NULLABLE"),
bigquery.SchemaField("first_name", "STRING", mode="NULLABLE"),
bigquery.SchemaField("last_name", "STRING", mode="NULLABLE"),
bigquery.SchemaField("dob", "DATE", mode="NULLABLE"),
bigquery.SchemaField(
"addresses",
"RECORD",
mode="REPEATED",
fields=[
bigquery.SchemaField("status", "STRING", mode="NULLABLE"),
bigquery.SchemaField("address", "STRING", mode="NULLABLE"),
bigquery.SchemaField("city", "STRING", mode="NULLABLE"),
bigquery.SchemaField("state", "STRING", mode="NULLABLE"),
bigquery.SchemaField("zip", "STRING", mode="NULLABLE"),
bigquery.SchemaField("numberOfYears", "STRING", mode="NULLABLE"),
],
),
]
table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table) # API request
print(f"Created table {table.project}.{table.dataset_id}.{table.table_id}.")
后续步骤
如需搜索和过滤其他 Google Cloud 产品的代码示例,请参阅 Google Cloud 示例浏览器。