[memo] Send table information to GCS with BigQuery Schedule and Cloud Functions

TODO

After the schedule set in BigQuery is completed, convert the table information to CSV and send the file to GCS.

• Implementation of Cloud Functions transmission processing triggered by Pub / Sub
• Schedule with BigQuery

Implementation of Cloud Functions transmission processing triggered by Pub / Sub

Pub / Sub settings

1. Enter the Topic ID that triggers Cloud Functions

Example: Set "example-1"

Cloud Functions settings

When the trigger ignites Refer to BQ table in GCS Place a script to send CSV

1. Set the trigger in "Cloud Pub / Sub" Select "example-1" above
2. I like the language, this time inline implementation in Python

REQUIREMENTS.TXT describes necessary modules etc. Describe what actually works with MAIN.PY

The implementation content is such as "Send yesterday's table information to GCS on BQ"

• REQUIREMENTS.TXT

#What is installed with pip
google-cloud-bigquery

• MAIN.PY

from google.cloud import bigquery
from datetime import date, timedelta
def export_table(event, context):
    client = bigquery.Client()
    yesterday = date.today() - timedelta(days=1)
    project = "project_name"
    dataset_id = "data_set_name"
    table_id = "table_name_yyyymmdd_" + yesterday.strftime('%Y%m%d')
    destination_uri = "gs://{}/{}".format("dir_name", yesterday.strftime('%Y%m%d')+".csv")
    dataset_ref = client.dataset(dataset_id, project=project)
    table_ref = dataset_ref.table(table_id)
    extract_job = client.extract_table(
    table_ref,
    destination_uri,
    location="US",
    )
    extract_job.result()

Finally, enter "export_table" as an execution function

Scheduling with BigQuery

There is a "schedule" provided as one of the functions of BigQuery, so use it

Enter a valid query and start by creating a new schedule for it

By setting "example-1" in the Pub / Sub input at the bottom, The trigger will fire when the table is created.

With the above, after the schedule set in BigQuery is completed, the table information will be converted to CSV and the file will be sent to GCS.

Afterword

When migrating Rundeck's on-premise server to the cloud The service account has been switched, and GS execution authority has not been granted. The content itself is such as "Create a table that is completed within BQ" and "Send the table data to GCS" Since it was a fairly small-scale use of functions, I decided to complete it with GCP.

It's not a big deal, so I'll leave a memo implemented here.