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")
