This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Log in to ask a question
Get hands-on experience with 20+ free Google Cloud products and $300 in free credit for new customers.
Log in to ask a question

Connect BigQuery to Microsoft SQL Server on Cloud SQL

Posted on 11-06-2023 10:29 PM
JohnnyChiang
New Member
Reply posted on --/--/---- --:-- AM

Hi everyone,

I'm having a problem connecting BigQuery to a Microsoft SQL Server 2018 instance on Cloud SQL. I created a BigQuery dataset with the source as the SQL Server instance, but I noticed that BigQuery only supports MySQL or PostgreSQL connections.

I'm sure I've enabled the BigQuery API and the BigQuery Connection API. How can I solve this problem?

Thanks for your help.

0 1 1,831
Topic Labels
0 Likes
1 REPLY 1

Posted on --/--/---- --:-- AM
ms4446
Staff
Reply posted on --/--/---- --:-- AM

While Google BigQuery supports native connections to MySQL and PostgreSQL on Cloud SQL, it does not offer the same for SQL Server 2019 instances. To bridge this gap, you can employ one of the following methods:

Method 1: Manual ETL Process

This approach involves a series of steps to export data from SQL Server, upload it to Google Cloud Storage (GCS), and then import it into BigQuery.

Steps:

  1. Export Data from SQL Server: Use SQL Server Management Studio (SSMS), the bcp command-line utility, or SQL Server Integration Services (SSIS) to export data to a BigQuery-compatible file format like CSV or JSON.

  2. Upload to Google Cloud Storage (GCS): Securely upload the exported data files to a GCS bucket using the gsutil command-line tool or through the GCS console with proper access controls.

  3. Load Data into BigQuery from GCS: Use the bq command-line tool to import the data from GCS into BigQuery. During this process, define the table schema or use schema auto-detection for well-structured data.

  4. Schedule Periodic Data Updates: Automate the export, upload, and load operations using Google Cloud Scheduler and Cloud Functions, or a third-party orchestration tool like Apache Airflow.

Considerations:

  • Offers detailed control over the data transfer process.
  • Requires more hands-on management.

Method 2: Cloud Data Fusion

Cloud Data Fusion is a managed data integration service for building ETL/ELT pipelines with minimal coding.

Steps:

  1. Create a Cloud Data Fusion Instance: Set up an instance within GCP.

  2. Configure Cloud Data Fusion: Upload the SQL Server JDBC driver and configure connections to both your SQL Server instance and BigQuery dataset.

  3. Design a Data Pipeline: Construct a pipeline to extract data from SQL Server, apply any necessary transformations, and load it into BigQuery.

  4. Schedule Data Pipeline Execution: Automate pipeline runs to keep SQL Server and BigQuery data in sync.

Considerations:

  • Provides a more automated synchronization process.
  • May require initial setup and configuration.

Additional Considerations:

  • Security and Permissions: Implement encryption for data in transit and at rest, and ensure proper IAM roles and permissions are configured.
  • Data Consistency: Use transactions or database snapshots in SQL Server to maintain a consistent data state for export.
  • Cost and Performance: Assess the implications of data update frequency and volume on the complexity and cost of your chosen method.

Method Selection:

Choose the method that aligns with your technical expertise, data update frequency, volume, and budget. The manual ETL process is suitable for straightforward, occasional data transfers, while Cloud Data Fusion is better for more complex, ongoing synchronization needs.

0 Likes