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).

I would probably just export it to csv into GCS, and load it into BigQuery from there. That would be the easiest. https://stackoverflow.com/questions/27784743/upload-google-cloud-sql-backup-to-bigquery — Graham Polley, Dec 04 '17 at 09:32

Jian He · Answer 1 · 2019-09-15T20:43:23.943

13

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;");

edited Sep 15 '19 at 20:43

answered Sep 15 '19 at 19:20

Jian He

460
5
8

1

Note: Federated queries may not perform as well as querying data residing in BigQuery storage. – UsamaAmjad Feb 05 '20 at 15:07
This saves me DAYS of work (instead of implementing some batch or stream approach to loading the data to BigQuery) Thank you so much!!! (And thanks GCP for this feature!!) – Philippe Hebert Aug 10 '22 at 15:14

score 5 · Answer 2 · answered Feb 13 '20 at 14:54

5

After creating a connection to your CloudSQL server, you can use it to create a table from BigQuery in a single query.

CREATE TABLE CUSTOMER AS
SELECT * FROM EXTERNAL_QUERY("<your_connection_id>", "SELECT * FROM CUSTOMER");

answered Feb 13 '20 at 14:54

loicmathieu

5,181
26
31

score 3 · Accepted Answer · answered Dec 06 '17 at 08:30

3

Up to now, there is no automated tool to import data into BigQuery from Cloud SQL, so a procedure you can follow consists in:

Export the data from the table you want in your Cloud SQL instance in CSV format, as explained in the documentation.
Import the CSV data into the BigQuery table you want following the procedure also explained in the documentation.

You are done. If your database is large and has many tables, you may want to do the import programatically, using the API.

answered Dec 06 '17 at 08:30

dsesto

7,864
2
33
50

Note that I get many import errors: "Error while reading data, error message: Error detected while parsing row starting at position: 387. Error: Bad character (ASCII 0) encountered. (error code: invalid)" – Steren Dec 12 '17 at 06:03

score 0 · Answer 4 · answered Apr 28 '21 at 19:07

0

Updated solution: In beta now, you can use Cloud Data Fusion to do this very easily (supporting MySQL and SQL Server for now).

Using Replication
MySQL Tutorial (official docs)
SQL Server Tutorial (official docs)

answered Apr 28 '21 at 19:07

MyNameIsCaleb

4,409
1
13
31

Best way to import Google Cloud SQL data into BigQuery

4 Answers4

Linked