0

I need to export the data that I have in a Google Cloud SQL Server database to BigQuery in order to make some data analysis and reporting. I found that the recommended way to do that is using BigQuery Cloud SQL federated query but this doesn't support SQL Server database, only MySql and Postgres in Google Cloud SQL service.

I'm looking for a way to do that with the Google Cloud resources, maybe a batch ETL tool or a streaming one. A requirement is that the data in BigQuery should be refreshed in real-time or near to real-time. I'm pretty new to Google Cloud.

I try to use Data Fusion but I can't connect with the GC SQL Server database. Looks like the JDBC installed is for Microsoft SQL Servers, not GC. Even in the Database Driver Class Names for the JDBC GC MySql and Postres are listed but not SQL Server

Dave M
  • 4,514
  • 22
  • 31
  • 30
Avaldes
  • 3
  • 1

1 Answers1

0

In order to use replication in DataFusion for SQL server try to follow-up step by step the documentation provided by Google 1.

Under “Upload the JDBC driver” seccion (link)

  • Download the SQL server JDBC driver mentioned in the documentation.
  • Enter a name, and in the version field keep the default.
  • “ClassName” field enter <com.microsoft.sqlserver.jdbc.SQLServerDriver>

Then, you have to create the pipeline (link), and start the pipeline (link) And finally you will be able to view your data in BigQuery

If you follow-up the full documentation of “Replicating data from SQL Server to BigQuery” you will be able to replicate without any problem. On the other hand, it would be useful if you can describe the step by step you followed to try replicate from Server to BigQuery, as much detailed as you can. Did you follow a guide?

Narda Monserrat
  • 16
  • Hi, thanks for your answer. I did what you say but the problem is that you can't use the jdbc driver to connect directly to GC Sql Sever, right? as it is not an SQL Server instance. What I finally do was create a virtual machine and run the sql proxy and connect the jdbc driver to the proxy in the VM. Do you see any other path? – Avaldes Jul 09 '21 at 23:10
  • In order to create a "Replication" you had to create two instances, (SQL and Cloud Fusion Instance). In the cloud fusion instance, you upload the driver to be able to connect to your database using the JDBC (Check how to use the driver from Cloud fusion)https://cloud.google.com/data-fusion/docs/how-to/using-jdbc-drivers#use_a_jdbc_driver To use the a JBDC driver in the SQL instance, you can use it with connector as well check the following documentation https://cloud.google.com/sql/docs/sqlserver/connect-connectors#setup-and-usage – Narda Monserrat Jul 13 '21 at 17:28