Export CloudSQL to Bigquery

[Rajesh Golla]

Hi,

I know we can use cloud functions.

But I am new to GCP.

Is there any sample code to schedule data load from cloudsql to BigQuery.

Thanks in advance.

[Alexis (Google Cloud Support)]

Hi,

The easiest method is to use Federated queries[1], which allows you to read data from Cloud SQL as if it were in BigQuery.

The sample code you are looking for is scattered across different documents. For example, as as CSV export from SQL with Cloud Scheduler here[2]. And then load the CSV data from a bucket[3] into Big Query.

Then there's also sample code to write streams using Big Query's write API[4]. More about streaming data into BigQuery here[5].

As you can see, there are a lot of possibilities and all these code samples can be used on Cloud Functions. I think you need to decide first how you will export, plus also in what format and when. However, if appropriate to your workflow, none of this needs to be done if you just use Federated Queries. Federated Queries is the simplest method without impact on performance since BigQuery is an aggregator of sort that uses the infrastructure to run queries[6].

[1] https://cloud.google.com/bigquery/docs/working-with-connections

[2] https://cloud.google.com/sql/docs/mysql/import-export/exporting#automating_export_operations

[3] https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv#loading_csv_data_into_a_table

[4] https://cloud.google.com/bigquery/docs/write-api#write_to_a_stream_in_committed_mode

[5] https://cloud.google.com/bigquery/streaming-data-into-bigquery

[6] https://cloud.google.com/bigquery/docs/introduction