How to export data from SQL Server to Vertica

Moderator: NorbertKrupa

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

How to export data from SQL Server to Vertica

Post by id10t » Mon Sep 30, 2013 7:51 am

Hi!

Nice post about - "How to export data from SQL Server to Vertica"
http://datadug.com/export-data-sql-server-vertica/

doug_harmon
Beginner
Beginner
Posts: 36
Joined: Fri Feb 17, 2012 6:09 pm
Contact:

Re: How to export data from SQL Server to Vertica

Post by doug_harmon » Mon Sep 30, 2013 2:38 pm

Thank you!

darinbob
Newbie
Newbie
Posts: 10
Joined: Thu Mar 27, 2014 2:43 pm

Re: How to export data from SQL Server to Vertica

Post by darinbob » Wed Jul 23, 2014 11:29 pm

Doug - I came across your blog solution also trying to troubleshoot SSIS data type mismatch errors that I couldn't resolve.

I followed your instructions and I learned quite a bit as a newbie from your blog. However - in my case - the result from the sqlcmd/vsql procedure yielded 0 Rows being loaded.

Is there a way then to troubleshoot that? I assume because I'm using the LOCAL STDIN options, the options are few to understand why the data didn't load. I tried to look at the load_streams v_monitor system table and this load attempt doesn't show up there, so I know I didn't even get that far.

I first wrote the results of the sqlcmd to an output file from the SQL Server database just to be sure I had good connections and I was resolving to some data from the .sql input file that I used rather than a stored proc.

That worked fine, so just a nudge of a suggestion may help me to figure out why the COPY command didn't load any data.

doug_harmon
Beginner
Beginner
Posts: 36
Joined: Fri Feb 17, 2012 6:09 pm
Contact:

Re: How to export data from SQL Server to Vertica

Post by doug_harmon » Thu Jul 24, 2014 2:29 pm

As for why it didn't show up in load_streams...did it finish in < 1 second? From the Vertica Docs: "The LOAD_STREAMS system table includes stream names for every COPY statement that takes more than 1-second to run. The 1-second duration includes the time to plan and execute the statement."

The next step would be to modify the COPY statement parameters to log exceptions and rejected records to files on your local box. Full details here: https://my.vertica.com/docs/7.0.x/HTML/ ... Y%7C_____0.

darinbob
Newbie
Newbie
Posts: 10
Joined: Thu Mar 27, 2014 2:43 pm

Re: How to export data from SQL Server to Vertica

Post by darinbob » Thu Jul 24, 2014 3:38 pm

I'm wondering if the results from sqlcmd actually have the string NULL when there are null marks in the table as opposed to consecutive delimiters.

That would cause some loading issues - but modifying the sqlcmd output doesn't seem straightforward.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: How to export data from SQL Server to Vertica

Post by id10t » Thu Jul 24, 2014 4:15 pm

Hi!

[DELETED]
Last edited by id10t on Wed May 06, 2015 5:08 pm, edited 1 time in total.

darinbob
Newbie
Newbie
Posts: 10
Joined: Thu Mar 27, 2014 2:43 pm

Re: How to export data from SQL Server to Vertica

Post by darinbob » Thu Jul 24, 2014 5:55 pm

Thanks - solving the issue of the NULL being populated for null marks in the sqlcmd output is my problem. I confirmed that using Doug's suggestion of writing the rejected data and exceptions to a local file.

I didn't realize the copy local stdin didn't automatically write those to the catalog directory on the initiator node for Vertica - so doing that explicitly helped me figure out the issue.

I'll consider this alternate solution as well to get around the NULL string in the output.

Post Reply

Return to “Vertica Migration”