Best way to import Google Cloud SQL data into BigQuery

Question

I have a database in a Cloud SQL instance. I would like to copy its content into BigQuery in order to perform analysis. It is not a requirement for me to continuously update the BigQuery dataset. It is OK if the export is done only once.

What is the best way to achieve this?

The 'Create Table' BigQuery UI does not allow me to import from Cloud SQL (only File, Cloud Storage, Drive or BigTable).

Answer 1

BigQuery can directly query Cloud SQL through Cloud SQL federated queries. It introduces a new SQL function called EXTERNAL_QUERY(connection_id, external_sql), which run the external_sql in the Cloud SQL database specified by connection_id.

You need to first create connection in BigQuery, then refer the connection_id in EXTERNAL_QUERY(). Following is a sample query to copy Cloud SQL data to BigQuery.

INSERT
  demo.customers (column1)
SELECT
   * 
FROM
   EXTERNAL_QUERY("project.us.connection",
                  "SELECT column1 FROM mysql_table;");