from pontoz.bigquery.client import client for pontoz_dataset in client.list_datasets(): pass _transactions_ref = pontoz_dataset.table('transactions') try: transactions_table = client.get_table(_transactions_ref) except NotFound: transactions_table = Table(_transactions_ref) SCHEMA = [ SchemaField('id', 'INT64', 'REQUIRED', None, ()), SchemaField('sale', 'FLOAT64', 'REQUIRED', None, ()), SchemaField('pointz_sale', 'FLOAT64', 'REQUIRED', None, ()), SchemaField('year', 'INT64', 'REQUIRED', None, ()), SchemaField('month', 'INT64', 'REQUIRED', None, ()), SchemaField('day', 'INT64', 'REQUIRED', None, ()), SchemaField('store_name', 'string', 'REQUIRED', None, ()), SchemaField('store_id', 'INT64', 'REQUIRED', None, ()), SchemaField('region_name', 'string', 'REQUIRED', None, ()), SchemaField('region_id', 'INT64', 'REQUIRED', None, ()), SchemaField('client_name', 'string', 'REQUIRED', None, ()), SchemaField('client_id', 'INT64', 'REQUIRED', None, ()), SchemaField('segment_name', 'string', 'REQUIRED', None, ()), ] transactions_table.schema = SCHEMA transactions_table = client.create_table(transactions_table) print(transactions_table)
def bq_insert(rows: List): """ Inserts rows into BigQuery :param rows: list of dictionaries which are representing rows :return: """ from google.cloud import bigquery if not rows: logging.error("no rows to upload") return bq = bigquery.Client(project=GCP_PROJECT) table_ref = TableReference.from_string( f"{GCP_PROJECT}.live.om_state_latencies") schema = [ { "name": "date", "type": "DATE" }, { "name": "sym", "type": "STRING" }, { "name": "from_state", "type": "STRING" }, { "name": "to_state", "type": "STRING" }, { "name": "count", "type": "INTEGER" }, { "name": "average", "type": "FLOAT" }, { "name": "percentile_10", "type": "FLOAT" }, { "name": "percentile_50", "type": "FLOAT" }, { "name": "percentile_90", "type": "FLOAT" }, { "name": "percentile_99", "type": "FLOAT" }, { "name": "percentile_99_99", "type": "FLOAT" }, ] table = Table(table_ref) table.schema = schema table = bq.create_table(table, exists_ok=True) logging.info("inserting {} rows".format(len(rows))) res = bq.insert_rows(table, rows) logging.info(res)