You can use the CLI or the python module to run a query in BigQuery.
query_to_table dataset table json_key YYYYMMDD query_file -Aquery_arg1=arg -Aquery_arg2=arg
parameters:
- YYYMMMDD: date of the script
- -A: parameter to pass args to the query or the query's file
- json_key: credentials to bigquery service
Use -h to see other parameters options
The CLI default is use the current time.
The CLI allows put some fixed variables in queries:
- previous_date: previous date of declared current date (YYYYMMDD)
- start_date: declared current date (YYYYMMDD)
- next_date: next date of declared current date (YYYYMMDD)
table_to_gcs dataset table bucket cloudstorage_filename json_key YYYYMMDD time_delta export_format compression_format
onde:
- YYYMMMDD: date of the script
- time_delta amount of days before current date to get the table
- json_key credentials to bigquery service
Use -h to see other parameters options
gcs_to_table bucket cloudstorage_filename dataset table json_key YYYYMMDD
onde:
- YYYMMMDD: date of the script
- json_key credentials to bigquery service
Use -h to see other parameters options
from google.cloud import bigquery
from gcloud_utils.bigquery.bigquery import Bigquery
query = "select * from bq_table"
client = bigquery.Client.from_service_account_json(args.gcs_key_json)
bq_client = Bigquery(client)
result = bq_client.query(self, query, **kwargs)
from google.cloud import bigquery
from gcloud_utils.bigquery.bigquery import Bigquery
from gcloud_utils.bigquery.query_builder import QueryBuilder
query = QueryBuilder("select * from ${my_table}")
query.with_vars(my_table="bq_table")
client = bigquery.Client.from_service_account_json(args.gcs_key_json)
bq_client = Bigquery(client)
result = bq_client.query(self, query, **kwargs)
from google.cloud import bigquery
client = bigquery.Client.from_service_account_json(args.gcs_key_json)
bq_client = Bigquery(client)
bq_client.query_to_table(query_or_object, dataset_id, table_id, write_disposition="WRITE_TRUNCATE", job_config=None, **kwargs)
from google.cloud import bigquery
client = bigquery.Client.from_service_account_json(args.gcs_key_json)
bq_client = Bigquery(client)
bq_client.table_to_cloud_storage(dataset_id, table_id, bucket_name, filename, job_config=None, export_format="csv", compression_format="gz", location="US", **kwargs)
from google.cloud import bigquery
client = bigquery.Client.from_service_account_json(args.gcs_key_json)
bq_client = Bigquery(client)
bq_client.cloud_storage_to_table(bucket_name, filename, dataset_id, table_id, job_config=None, location="US", **kwargs)