CODEWITHSUNDEEP
google-bigqueryscheduled-tasksgoogle-cloud-storage
RoS
RoS

Reputation: 179

scheduled export from Google BigQuery to Google Cloud Storage

I'm new to Google Cloud and would like to know best use cases on how to schedule queries and export them to Google Cloud Storage. I've seen documentations on how to manually export data but couldn't find anything specific on doing it in an automated way. Is there any best way on how to approach this ?

Thanks

Upvotes: 15

Views: 12570

Answers (2)

p13rr0m
p13rr0m

Reputation: 1297

It is possible to create scheduled export jobs with the scheduled queries feature and EXPORT DATA statement. For example, this script below backups data daily to GCS as Parquet files with SNAPPY compression. Each time the job is executed it takes all the data from the day before.

DECLARE backup_date DATE DEFAULT DATE_SUB(@run_date, INTERVAL 1 day);

EXPORT DATA
  OPTIONS ( uri = CONCAT('gs://my-bucket/', CAST(backup_date AS STRING), '/*.parquet'),
    format='PARQUET',
    compression='SNAPPY',
    overwrite=FALSE ) AS
SELECT
  *
FROM
  `my-project.my-dataset.my-table`
WHERE
  DATE(timestamp) = backup_date

From the BiqQuery UI you can then create a scheduled query and set the trigger frequency and trigger time.

enter image description here

Upvotes: 10

Moe
Moe

Reputation: 131

  1. Implement your table export function [1] using Node.js, python or Go. These languages are supported by Cloud Functions and BigQuery.
  2. Insert the above function in Cloud Function [2] service which is an event driven serverless compute platform.
  3. Trigger the above function using Cloud Scheduler [3] using a schedule interval of your choice. The schedule interval can be specified using a cron expression. The scheduler will trigger the function via a REST call on the function´s endpoint.
  4. Verify the success of the above operation by visiting your bucket and ensuring that the table(s) has been successfully exported.

Upvotes: 5

Related Questions