CODEWITHSUNDEEP
google-bigquery
Priya Nair
Priya Nair

Reputation: 67

Connecting to Cloud SQL server instance from BigQuery

There is an option to connect a Cloud mySQL instance from BigQuery. I just wanted to know how we can connect a Cloud SQL Server instance to BigQuery.

Upvotes: 2

Views: 2451

Answers (2)

aga
aga

Reputation: 3883

SQL Server:

There are a bunch of third-party extensions/tools that provide this service. One of them is SSIS Data Flow Source & Destination for Google BigQuery, which is Visual Studio extension that connects SQL Server with Google BigQuery data through SSIS Workflows.:

In regards to using SQL Server Integration Services to load the data from the on-premises SQL Server to BigQuery, you can take a look for this site. You can also perform ETL from a relational database into BigQuery using Cloud Dataflow, the official documentation details how it can be done, you might need to use Cloud Storage as an intermediate data sink.

Cloud SQL:

BigQuery allows to query data from Cloud SQL by using federated query. The connection must be created within the same project where your Cloud SQL instance is located. If you want to query your data stored in your Cloud SQL instance from BigQuery located in another project, please follow the steps listed below:

  1. Enable the BigQuery API and the BigQuery connection API within your project.

  2. Create a connection to your Cloud SQL instance within the project by following this documentation.

  3. Once you have created the connection, please locate and select it within BigQuery.

  4. Click on the SHARE CONNECTION button and grant permissions to the users that will be use that connection. Please note that the BigQuery Connection User role is the only needed to use a shared connection.

Additionally, please notice that the "Cloud SQL federated queries" feature is in a Beta stage and might change or have limited support (is no available for certain regions, in which case, it is required to use one the supported options mentioned here). Please remember, that to use Cloud SQL Federated queries in BigQuery, the intances need to have a public IP.

If you are limited e.g. by region, one good option might be exporting the data from CloudSQL to Storage as a CSV, and then load it into BigQuery. If you need, it is possible to automate this process using Cloud Composer, refer to this article.

Other approach is to extract information from Cloud SQL (with exports) and import it into BigQuery through load jobs, or streaming inserts.

I hope you find the above pieces of information useful.

Upvotes: 6

Michael Entin
Michael Entin

Reputation: 7744

It is possible, but be warned the feature is currently Beta

https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries

Upvotes: 0

Related Questions