通过CDATA odbc驱动程序将1百万条记录从SQL Server插入到BigQuery表中 [英] Insert 1 million records from SQL Server to BigQuery table, linked via CDATA odbc driver

问题描述

我需要从SQL Server表中插入一百万(或更多)记录到BigQuery表中，该表通过

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).

另外，源表可能没有列，但行数，ID等.

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

现在，我可以使用以下驱动程序通过此驱动程序将每秒1条记录插入BigQuery:

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

但是对于如此多的记录(如100万或更多)，这是非常低的性能.

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.

推荐答案

创建一个SSIS包以执行从 [natality].[dbo].[natality]表到[GBQ]的快速加载.[CDataGoogleBigQuery].[GoogleBigQuery].[natality]表.简而言之，在ETL过程中使用快速加载来批量加载.

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.

高级操作方法:

1. 在SSIS中，从工具箱"中选择一个数据流任务.

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

单击数据流任务，然后选择编辑.

Click on the data flow task and select edit.

在数据流"页面上，选择工具箱中的ole db源和ole db目标.将两个任务链接在一起.

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

为 [natality].[dbo].[natality] .

为 [GBQ].[CDataGoogleBigQuery].[GoogleBigQuery].[natality] 设置ole db目标.确保选择了快速加载到表"设置.

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

完成后，执行程序包.

这篇关于通过CDATA odbc驱动程序将1百万条记录从SQL Server插入到BigQuery表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！