Insert 1 million records from SQL Server to BigQuery table, linked via CDATA odbc driver

0
votes

I need to insert 1 million (and more) records from a SQL Server table to a BigQuery table, that is present in SQL Server as "linked server" via CDATA odbc driver with remoting daemon in it (documentation).

Also, source table might have no column with number of row, Id etc.

For now, I can insert 1 record per second into BigQuery with this driver, using this query:

INSERT INTO [GBQ].[CDataGoogleBigQuery].[GoogleBigQuery].[natality]
    SELECT * 
    FROM [natality].[dbo].[natality] 
GO

But for such a bunch of records, as 1 Million or more, it's an EXTREMELY LOW performance.

I believe there's a workaround, that can allow me to speed up insertion process. Thanks in advance, comrades.

2
sql-serverdatabasesql-server-2008odbcgoogle-bigquery

2 Answers

1
votes

Create an SSIS package to perform a fast load from the [natality].[dbo].[natality] table to [GBQ].[CDataGoogleBigQuery].[GoogleBigQuery].[natality] table. In short, fast load is used in ETL process to bulk load.

High level how to:

  1. In SSIS, select a data flow task from the Toolbox.

  2. Click on the data flow task and select edit.

  3. On the Data Flow page, select from the tool box your ole db source and ole db destination. Link the 2 tasks together.

  4. Set up your ole db source connection for [natality].[dbo].[natality].

  5. Set up your ole db destination for [GBQ].[CDataGoogleBigQuery].[GoogleBigQuery].[natality]. Be sure the fast load to table setting is selected.

Once your done, execute the package.

0
votes

The link in @Blasterdick comment is no longer available, but the latest build from CData's website works: http://www.cdata.com/drivers/bigquery/ssis

BatchMode is the name of the property that controls Bulk queries, and is enabled by default.