Score:0

Google Cloud SQL Server data to BigQuery

ao flag

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

Score:0
br flag

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?

Avaldes avatar
ao flag
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?
br flag
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
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.