import json import os from google.oauth2 import service_account from google.cloud import bigquery bq_credentials = service_account.Credentials.from_service_account_info(json.loads(os.environ['DEEPNOTE_BIGQUERY_SERVICE_ACCOUNT'])) bq_client = bigquery.Client(credentials=bq_credentials, project=bq_credentials.project_id)

new_dataset_name = "github_org_webhooks" n_days = 365 view_query_template = """ SELECT * EXCEPT (ROW_NUMBER) FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY loaded_at DESC) ROW_NUMBER FROM `{}.{}.{}` WHERE _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_MICROS(UNIX_MICROS(CURRENT_TIMESTAMP()) - {} * 60 * 60 * 24 * 1000000), DAY, 'UTC') AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'UTC') ) WHERE ROW_NUMBER = 1 """

tables = bq_client.list_tables(new_dataset_name) for table in tables: view_query = view_query_template.format(table.project, table.dataset_id, table.table_id, n_days) view_id = "{}.{}.{}_view".format(table.project, table.dataset_id, table.table_id) view = bigquery.Table(view_id) view.view_query = view_query try: view = bq_client.create_table(view) except Exception as e: print(f"Problem with {table.table_id}: {e}") print(f"Created {view.table_type}: {table.table_id}_view")