Issues importing data from SQL Server to Vertica using Toad Data Point

DBDave · December 2016

Hello,

I am trying to import a table from SQL Server into Vertica using Toad Data Point. Most of the data loaded but I have several rejected rows. I assume the rows are rejected because of comma delimited lists. Our data has the following delimeters in some of the data... comma, pipe, colon, semicolon, etc. So this import is becoming difficult.

It is my understanding that when you import data into Vertica, Vertica translates the data into a CSV file and then loads the CSV. We are eventually going to use Informatica to import the data, but as of now all I have is Toda DP.

Steps: Used Import/Export wizard from Toad to load data from a query. As I said, most of the data loads properly with the exception of data that has commas.

Any help would be appreciated. Please let me know if I am being clear enough about this topic.

DBDave · December 2016

Thanks for your response. I guess no one is working today. This forum is a joke. It's been several hours since I posted this and no response. Terrible.

ckotsidimos · December 2016

Having done this before, I would suggest to use a delimeter like { or } or [ or ] or.... , something that is not used in your data. It was the only way I found and worked correctly as vertica can understand any character as a delimeter. Furthermore, with the export/import wizzard of sql server you can export the dta directly to csv and then use the copy command toimport it.

That is onllly from my experience

Adrian_Oprea_1 · January 2017

Woww , hold your horses buddy !!!

This is not payed support ! is pure community forum !

Adrian_Oprea_1 · January 2017

Here is simple solution and is free

Use the sqlcmd tool in Sql Server and pipe it into Vertica on csv file transfer etc.. (you need the vertica client installed in Sql Server box).

For the delimiter issue use the \t tab delimited character.

sqlcmd -S sql_server_ip,sql_server_port -d database_name -E -s"\t" -W 
-Q "SELECT * FROM database.dbo.TABLE_NAME"|
 vsql -h 10.1.43.98 -d db_name -U db_user -w passwd 
-c"copy  schema.TABLE_NAME FROM STDIN SKIP 1 DELIMITER E'\t' NULL
 as 'null' DIRECT STREAM NAME 'Loading schema.TABLE_NAME from Sql Server' 
 REJECTED DATA AS TABLE staging.TABLE_NAME_rej;"

Make sure you have it on single line when running it.

Note:

See the rejected table for rejects and once oyu have them in Vertica they are eassy to be treated in cleaned.

Issues importing data from SQL Server to Vertica using Toad Data Point

Comments

Leave a Comment