使用 DDL 查询创建视图。
深入探索
如需查看包含此代码示例的详细文档,请参阅以下内容:
代码示例
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.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.QueryJobConfiguration;
// Sample to create a view using DDL
public class DdlCreateView {
public static void main(String[] args) {
// TODO(developer): Replace these variables before running the sample.
String projectId = "MY_PROJECT_ID";
String datasetId = "MY_DATASET_ID";
String tableId = "MY_VIEW_ID";
String ddl =
"CREATE VIEW "
+ "`"
+ projectId
+ "."
+ datasetId
+ "."
+ tableId
+ "`"
+ " OPTIONS("
+ " expiration_timestamp=TIMESTAMP_ADD("
+ " CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),"
+ " friendly_name=\"new_view\","
+ " description=\"a view that expires in 2 days\","
+ " labels=[(\"org_unit\", \"development\")]"
+ " )"
+ " AS SELECT name, state, year, number"
+ " FROM `bigquery-public-data.usa_names.usa_1910_current`"
+ " WHERE state LIKE 'W%'`";
ddlCreateView(ddl);
}
public static void ddlCreateView(String ddl) {
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();
QueryJobConfiguration config = QueryJobConfiguration.newBuilder(ddl).build();
// create a view using query and it will wait to complete job.
Job job = bigquery.create(JobInfo.of(config));
job = job.waitFor();
if (job.isDone()) {
System.out.println("View created successfully");
} else {
System.out.println("View was not created");
}
} catch (BigQueryException | InterruptedException e) {
System.out.println("View 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 ddlCreateView() {
// Creates a view via a DDL query
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = "my_project"
// const datasetId = "my_dataset"
// const tableId = "my_new_view"
const query = `
CREATE VIEW \`${projectId}.${datasetId}.${tableId}\`
OPTIONS(
expiration_timestamp=TIMESTAMP_ADD(
CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
friendly_name="new_view",
description="a view that expires in 2 days",
labels=[("org_unit", "development")]
)
AS SELECT name, state, year, number
FROM \`bigquery-public-data.usa_names.usa_1910_current\`
WHERE state LIKE 'W%'`;
// For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
const options = {
query: query,
};
// Run the query as a job
const [job] = await bigquery.createQueryJob(options);
job.on('complete', metadata => {
console.log(`Created new view ${tableId} via job ${metadata.id}`);
});
}
Python
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
# from google.cloud import bigquery
# project = 'my-project'
# dataset_id = 'my_dataset'
# table_id = 'new_view'
# client = bigquery.Client(project=project)
sql = """
CREATE VIEW `{}.{}.{}`
OPTIONS(
expiration_timestamp=TIMESTAMP_ADD(
CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
friendly_name="new_view",
description="a view that expires in 2 days",
labels=[("org_unit", "development")]
)
AS SELECT name, state, year, number
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE state LIKE 'W%'
""".format(
project, dataset_id, table_id
)
job = client.query(sql) # API request.
job.result() # Waits for the query to finish.
print(
'Created new view "{}.{}.{}".'.format(
job.destination.project,
job.destination.dataset_id,
job.destination.table_id,
)
)
后续步骤
如需搜索和过滤其他 Google Cloud 产品的代码示例,请参阅 Google Cloud 示例浏览器。