December 19, 2016 at 4:14 pm
Hello SSC,
I have been struggling trying to import data from SQL to Vertica using CMD shell. Does anyone have experience with this? I would like to be able to do this without using a text or CSV file. I would think it is easy enough to copy data from database to database.
Here is the command that I am using from CMD Shell, but this is taken from a CSV. Does anyone know how to do this DB to DB?
VERTICA=> copy VERTICA_TABLE from local 'C:\test.csv' WITH DELIMITER ',';
Hoping for...
VERTICA=> copy VERTICA_TABLE from local SQLSERVER, UNAME, PASS, DB NAME; (I do not know the syntax for this, any help?)
Thanks
The are no problems, only solutions. --John Lennon
December 19, 2016 at 5:07 pm
this isn't a sql server question, but a Vertica question - RTFM is required.
Look at https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/ConnectingToHPVertica/ClientJDBC/UsingVerticaCopyStream.htm as it may either help or guide you on the right direction
December 20, 2016 at 7:51 am
Wow, I didn't expect hostility. I have been "RTFM", that is why I am asking for help. I love this site (until you responded), and I was hoping someone had experience with loading SQL data into Vertica. I am also not a console guy, so this is very challenging.
I would recommend not using profanities on this site. People are trying to help one another and your attitude is distasteful.
The are no problems, only solutions. --John Lennon
December 20, 2016 at 10:37 am
Lord Slaagh (12/19/2016)
Hello SSC,I have been struggling trying to import data from SQL to Vertica using CMD shell. Does anyone have experience with this? I would like to be able to do this without using a text or CSV file. I would think it is easy enough to copy data from database to database.
Here is the command that I am using from CMD Shell, but this is taken from a CSV. Does anyone know how to do this DB to DB?
VERTICA=> copy VERTICA_TABLE from local 'C:\test.csv' WITH DELIMITER ',';
Hoping for...
VERTICA=> copy VERTICA_TABLE from local SQLSERVER, UNAME, PASS, DB NAME; (I do not know the syntax for this, any help?)
Thanks
You might want to investigate whether or not Vertica makes an ODBC driver. If they do, copying the data may be as easy as dealing with a Linked Server.
As to the other poster's reference to RTFM, I suspect that there was no intent to be profane. The IT community in general tends to use acronyms without necessarily stopping to think about them in the level of detail necessary to have some kind of nasty thought associated with it. That particular acronym has been around so long that it's the kind of thing most folks aren't quite so sensitive about, and you might want to consider that since you cannot determine with certainty the intent from a forum post that doesn't specifically specify it, perhaps you can be a little less sensitive... especially since you didn't identify what sources of information you were relying on... And as always, try not to judge an entire forum based on one post from one person...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 20, 2016 at 12:06 pm
Thanks Steve. Indeed no pun intended with RTFM.
David, as per the link I added (from the manuals) there are indeed ways to do it - JDBC from what i could see on a brief reading - but there may be better ways for SQL Server hence the reference to manuals which is where I would refer if looking for info.
From your own reading of the manuals if you have questions around particular issues that relate to sql server connection then we may be of help - but without specifics it will be very hard unless you are lucky on having someone here with hands on experience willing to help.
December 21, 2016 at 10:43 am
Thank you all for your responses. Being in this industry, I have, of course heard the acronym RTFM. It just makes people feel like they haven't done their research, which I have. I am not a command shell guy so this is a bit challenging (damn Microsoft developers :-)).
Thanks for the clarification and I will try to be less sensitive about it. I still love SSC :w00t:
The are no problems, only solutions. --John Lennon
December 21, 2016 at 11:06 am
frederico_fonseca (12/20/2016)
Thanks Steve. Indeed no pun intended with RTFM.David, as per the link I added (from the manuals) there are indeed ways to do it - JDBC from what i could see on a brief reading - but there may be better ways for SQL Server hence the reference to manuals which is where I would refer if looking for info.
From your own reading of the manuals if you have questions around particular issues that relate to sql server connection then we may be of help - but without specifics it will be very hard unless you are lucky on having someone here with hands on experience willing to help.
Maybe what you read just wasn't the right page. I found ODBC drivers for Vertica on my first Google search, located here:
https://my.vertica.com/download/vertica/client-drivers/[/url]
With the ODBC driver installed, SQL Server can then access the Vertica server as a "Linked Server". If you need to filter what data comes across, you'll probably want to use OPENQUERY as opposed to just a four-part name for a table source on Vertica via the Linked Server. You then supply a Vertica formatted SQL query within the OPENQUERY portion of the SQL Server query, and be sure to use a WHERE clause in your Vertica query so as to limit the results BEFORE they traverse the network and waste resources.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 21, 2016 at 12:48 pm
I will check that out and thank you.
The are no problems, only solutions. --John Lennon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply