CODEWITHSUNDEEP
google-cloud-platformgoogle-bigqueryrelational-database
LifeStartsAtHelloWorld
LifeStartsAtHelloWorld

Reputation: 979

Move data from BQ to on-prem SQL Server relationalDB

Can anyone help me out to understand how can we move data from BQ (OLAP) to on-prem SQL Server relationalDB (OLTP)? This would be a one-time backfill process and then a recurring process every month. Before we get into the details, I want to point out, we are not ready to shell out money for CloudSQL or BigData, so we decided the analytical data that exist in BQ can be moved to relationalDB as the public site would read the processed data from relationalDB.

I have two approaches in mind:

  1. Microservice can have a quartz job that reads the data from BQ to add to relationalDB, However, how efficient this would be?
  2. ETL process to read data from BQ and save it to relationalDB.
  3. Save the data into cloud storage from BQ as json file and then read the file and then process and save it in relationalDB (which can be done either through the service or ETL process)

Does anyone have any other approach that they can suggest?

Upvotes: 0

Views: 1772

Answers (1)

guillaume blaquiere
guillaume blaquiere

Reputation: 75715

For efficiency, I can suggest this process

  • Extract the BigQuery data and save them as CSV
    • If the data are too large. Create a temporary table in BigQuery with the data to export and use the BigQuery storage API to export the full table content to Storage in CSV format.
  • On SQL Server, import the CSV in temporary tables (fast and efficient)
  • Perform your ETL and merge-insert with a SQL query between the temporary tables and the real tables
  • Delete the temporary tables

Upvotes: 1

Related Questions