CODEWITHSUNDEEP
google-bigquery
dpick
dpick

Reputation: 35

Upload Google Cloud SQL backup to Bigquery

I have had troubles trying to move a Google Cloud SQL database to BigQuery. I have exported the database backup from Cloud SQL to Cloud Storage, but when trying to import this into BigQuery, I get the error: 'Not found: URI' for gs://bucket-name/file-name

Is what I'm trying to do even possible? I'm hoping to somehow directly upload the Cloud SQL data to BigQuery. It's a large table (>27GB) and I have been having a lot of connection issues with Cloud SQL, so exporting as CSV or JSON isn't the best option.

Upvotes: 1

Views: 3204

Answers (2)

Jian He
Jian He

Reputation: 510

You can use BigQuery Cloud SQL federated query to copy Cloud SQL table into BigQuery. You can do it with one BigQuery SQL statement. For example, following SQL copy MySQL table sales_20191002 to BigQuery table demo.sales_20191002. 

INSERT
   demo.sales_20191002 (column1, column2 etc..)
SELECT
   *
FROM
   EXTERNAL_QUERY(
      "project.us.connection",
      "SELECT * FROM sales_20191002;");

EXTERNAL_QUERY("connection", "foreign SQL") would execute the "foreign SQL" in the Cloud SQL database specified in "connection" and return result back to BigQuery. "foreign SQL" is the source database SQL dialect (MySQL or PostgreSQL).

Before running above SQL query, you need to create a BigQuery connection which point to your Cloud SQL database.

To copy the whole Cloud SQL database, you may want to write a script to iterate all tables and copy them in a loop.

Upvotes: 2

shollyman
shollyman

Reputation: 4384

BigQuery doesn't support the mysql backup format, so the best route forward is to generate csv or json from the cloud sql database and persist those files into cloud storage.

More information on importing data can be found in the BigQuery documentation.

Upvotes: 3

Related Questions