创建和替换BigQuery表 [英] Create and replace BigQuery tables

问题描述

如何创建和替换现有的BigQuery表?我使用datalab定义BigQuery查询，并将结果写入BigQuery表.

How do I create and replace an existing BigQuery table? I use datalab to define BigQuery queries and write the results to BigQuery tables.

我发现最有效的方法是:

The most efficient way I found to do this is:

%%bq query --name helloWorld
Select * from someTable

之后

%%bq execute --table schemaName.destination_table --query helloWorld

但是我每次都必须手动删除表格

However I have to manually drop the table each time

在命令行中，我可以执行以下操作:

From the command line I can execute something like:

bq query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --replace '[QUERY]'

python/Datalab中是否有一些类似的功能?

Is there some similar functionality in python/Datalab?

推荐答案

使用-mode 或 -m 参数.例如:

%% bq执行--table schemaName.destination_table --query helloWorld --mode覆盖

通过运行 %% bq execute --help 查看更多选项:

See more options by running %%bq execute --help:

usage: %bq execute [-h] [-nc] [-b BILLING] [-m {create,append,overwrite}] [-l]
                   -q QUERY [-t TABLE] [--to-dataframe]
                   [--dataframe-start-row DATAFRAME_START_ROW]
                   [--dataframe-max-rows DATAFRAME_MAX_ROWS] [-v]

Execute a BigQuery SQL query and optionally send the results to a named table.
The cell can optionally contain arguments for expanding variables in the
query.

optional arguments:
  -h, --help            show this help message and exit
  -nc, --nocache        Don't use previously cached results
  -b BILLING, --billing BILLING
                        BigQuery billing tier
  -m {create,append,overwrite}, --mode {create,append,overwrite}
                        The table creation mode
  -l, --large           Whether to allow large results
  -q QUERY, --query QUERY
                        The name of query to run
  -t TABLE, --table TABLE
                        Target table name
  --to-dataframe        Convert the result into a dataframe
  --dataframe-start-row DATAFRAME_START_ROW
                        Row of the table to start the dataframe export
  --dataframe-max-rows DATAFRAME_MAX_ROWS
                        Upper limit on number of rows to export to the
                        dataframe
  -v, --verbose         Show the expanded SQL that is being executed

这篇关于创建和替换BigQuery表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！