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)
