创建和替换BigQuery表
问题描述：
如何创建和替换现有的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