Loading data

[Mikkis]

Anyone using Microsoft SSIS for loading data to Greenplum.

If yes ODBC is really slow so how do you write in "bulk mode" OleDB or ??

PS Reading data out with OleDB is Ok.

Mikkis

[greg@2ndquadrant.com]

One possibility the speed things up in this sort of situation is to run multiple export/import sessions in parallel, since neither database server is going to be your limiting factor if using one of the direct SSIS connection methods.

Another possibility to look at is using SQL Server "Import and Export Wizard" and dump your database into flat files. Then you can load them into the Greenplum database quite fast using the gpload command. That will be using the SQL Server's bulk writer and Greenplum's preferred bulk reader. You may have to spend more time setting up the configuration files to make that work than you'd expect for this approach, if you're using to using the more GUI interfaces for this sort of work.

[Mikkis]

Thanks Greg
That is just what we do want to avoid. We have been creating Teradata Fastload Scripting Utility that Automatically fastloads Data to database via SSIS. There is ab 450 scripts and to rewrite them via flat file is not what we like to do.

SO If anyone has been using OleDB or any other. I know from java there is possibility to "copy" data in and out from/to greenplum, but just in this case it is not option.

[greg@2ndquadrant.com]

Ah, that clarifies what you're looking for considerably.

As you've picked up already, there's a couple of levels of loading speed you can work at for getting things into the Greenplum database. The gpload utility is the fastest; something using COPY is next; then comes batched INSERT followed by single INSERT as the slowest way.

I believe that ODBC is going to fire as a series of single INSERTs, while the OleDB driver uses batched INSERTs instead. I'm not that familiar with the Windows driver situation to know for sure though, I could be wrong about the details there. Perhaps we'll get someone who is to chime in here about what level those two work at.

[llonergan]

Hi Mikkis!

The PGNP OLEDB Driver has been optimized for very fast (COPY protocol) input and output of data for Greenplum. This work was paid for by Greenplum on behalf of a customer in the Nordics (you know who :-)

I think the performance should be fine - please let us all know how it works out.

- Luke

[Mikkis]

Thanks Luke

[Mikkis]

Luke


OleDb works well, only issue is that SSIS makes it slow to load the data.

We tested one file, with copy it takes ab 10 sec and reading it from DB2 with SSIS and loading it into same GP via OleDB it takes ab 4 minutes. Rading DB2 and SSIS server slows it down.