biggerquery 0.2.2

write-append与write-append的区别在于write-append不会在写入新数据之前从给定的表中删除数据。

临时写入

有时创建一个额外的表来存储一些中间结果是很有用的。 write-tmp函数允许从查询结果创建表（write-truncate和write-append只能写入已经存在的表）。

您可以使用write\u tmp函数重构现有代码：

frombiggerqueryimportcreate_dataset_managerPROJECT_ID='your-project-id'USER_TRANSACTION_METRICS_DATASET_NAME='user_transaction_metrics'TRANSACTION_DATASET_NAME='transactions'defsetup_test_transactions_table(project_id,dataset_name):dataset_id,dataset_manager=create_dataset_manager(project_id,'2019-01-01',dataset_name,internal_tables=['transactions'])dataset_manager.create_table("""       CREATE TABLE IF NOT EXISTS transactions (           user_id STRING,           transaction_value FLOAT64,           partition_timestamp TIMESTAMP)       PARTITION BY DATE(partition_timestamp)""")dataset_manager.write_truncate('transactions',"""       SELECT 'john123' as user_id, 800.0 as transaction_value, TIMESTAMP('2019-01-01') as partition_timestamp       """)dataset_manager.write_append('transactions',"""       SELECT 'smith99' as user_id, 10000.0 as transaction_value, TIMESTAMP('2019-01-01') as partition_timestamp       """)dataset_manager.write_append('transactions',"""       SELECT 'smith99' as user_id, 30000.0 as transaction_value, TIMESTAMP('2019-01-01') as partition_timestamp       """)return'{dataset_id}.transactions'.format(dataset_id=dataset_id)# creating source dataset and table- transactionsTRANSACTIONS_TABLE_ID=setup_test_transactions_table(PROJECT_ID,TRANSACTION_DATASET_NAME)# creating processing dataset- user_transaction_metricsuser_transaction_dataset_id,user_transaction_metrics_dataset_manager=create_dataset_manager(project_id=PROJECT_ID,runtime='2019-01-01',dataset_name=USER_TRANSACTION_METRICS_DATASET_NAME,internal_tables=['user_transaction_metrics'],external_tables={'transactions':TRANSACTIONS_TABLE_ID})defcalculate_user_transaction_metrics(dataset_manager):dataset_manager.create_table("""   CREATE TABLE IF NOT EXISTS user_transaction_metrics (       user_id STRING,       metric_value FLOAT64,       metric_type STRING,       partition_timestamp TIMESTAMP)   PARTITION BY DATE(partition_timestamp)   """)dataset_manager.write_tmp('daily_user_transaction_value',"""   SELECT user_id,       sum(transaction_value) as metric_value,       'USER_TRANSACTION_VALUE' as metric_type,       TIMESTAMP('{dt}') as partition_timestamp   FROM `{transactions}`   WHERE DATE(partition_timestamp) = '{dt}'   GROUP BY user_id   """)dataset_manager.write_tmp('daily_user_transaction_count',"""   SELECT user_id,       count(transaction_value) as metric_value,       'USER_TRANSACTION_COUNT' as metric_type,       TIMESTAMP('{dt}') as partition_timestamp   FROM `{transactions}`   WHERE DATE(partition_timestamp) = '{dt}'   GROUP BY user_id   """)dataset_manager.write_truncate('user_transaction_metrics',"""   SELECT * FROM `{daily_user_transaction_value}`   UNION ALL   SELECT * FROM `{daily_user_transaction_count}`   """)calculate_user_transaction_metrics(user_transaction_metrics_dataset_manager)

将一系列相关查询放入一个函数中是一个好的实践，您可以使用指定的数据集管理器对其进行调度、测试或运行。 在本例中，它是用户事务度量函数。通过检查结果步骤，临时表对于调试代码非常有用 循序渐进。将一个大查询分成更小的块也更容易阅读。

收集

您可以使用collect从bigquery中将数据提取到内存中。例如，通过http:

calculate_user_transaction_metrics(user_transaction_metrics_dataset_manager)rows=user_transaction_metrics_dataset_manager.collect("""SELECT * FROM `{user_transaction_metrics}`WHERE DATE(partition_timestamp) = '{dt}'""")importrequestsforrowinrows:requests.post('http://example.com/user-metrics',json={'userMetric':row})

凭证

如果要指定对数据集进行操作的凭据，可以在创建数据集管理器时执行此操作，例如：

mkdir test_biggerquery
cd test_biggerquery
pip install virtualenv
virtualenv -p /usr/bin/python2.7 venv
source venv/bin/activate
pip install biggerquery

mkdir test_biggerquery
cd test_biggerquery
pip install virtualenv
virtualenv -p /usr/bin/python2.7 venv
source venv/bin/activate
pip install biggerquery

external_tables={'transactions':'dataset.id.transactions','some_external_table':'dataset.id2.external_table'}

mkdir test_biggerquery
cd test_biggerquery
pip install virtualenv
virtualenv -p /usr/bin/python2.7 venv
source venv/bin/activate
pip install biggerquery

mkdir test_biggerquery
cd test_biggerquery
pip install virtualenv
virtualenv -p /usr/bin/python2.7 venv
source venv/bin/activate
pip install biggerquery

mkdir test_biggerquery
cd test_biggerquery
pip install virtualenv
virtualenv -p /usr/bin/python2.7 venv
source venv/bin/activate
pip install biggerquery

mkdir test_biggerquery
cd test_biggerquery
pip install virtualenv
virtualenv -p /usr/bin/python2.7 venv
source venv/bin/activate
pip install biggerquery