Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to import data from SQL Server into Vertica

Hi Guys,

Off the top of my head transferring data from SQL Server seems a far fetch for me. Please guide me on how to achieve this. I have nearly 1TB of data sitting in the SQL Server and i want to transfer the schemas, views and tables to Vertica. 

It would help if there is some documentation for doing this.

Thanks,
Anmol

Comments

  • Hi Anmol,

    First of all , Is it a onetime load or a daily process.

    I find it as a one time load considering the amount of data you are planning to load.

    This can be achived two ways
    1. The data extracted from SQL server on windows and loaded to Vertica on linux -- easy way
    2. The data extracted from SQL server and directly loaded to Vertica on Linux -- hard way

    The best approach will be using the official ODBC driver / JDBC driver for SQL Server.

    Following are the steps to load one table form SQL server to vertica (using first option)
    1. Dump the complete catalog from SQL server for a table and change the data types that are not 
       compatible with Vertica in a sql file.
    2. Create the table in Vertica using this sql file.
    3. Dump/Export data from SQL Server table wise in a txt file delimited by '|' on windows.
    4. Now you can directly pick up these files for loading in Vertica using "COPY FROM LOCAL" option

    Following are the steps in brief to load one table from SQL server to Vertica ( this is for second option 
    1. Dump the complete catalog from SQL server for a table and change the data types that are not compatible with Vertica in a sql file.2. Create the table in Vertica using this sql file.
    3. Install / use official ODBC driver from SQL server on Linux and check the proper installation using SQL server utilities.
    4. Now when you have setup the ODBC driver on Linux, you need to install the ODBC loader and parser for Vertica.This is an external package for Vertica and supports different databases.
    5. Once installed, you can use the COPY command with ODBCLoader and ODBCparser components to load data directly into Vertica.

    Brief us more on your requirement and we can help you more precisely.

    Hope this helps

  • Hey Naveen,

    I have some 100 odd data tables in SQL server.
    I have to migrate them to vertica. Right now it is a one time load for data analysis purposes.
    And i m having a hard time doing it.
    1. Am i correct to say that for the copy from local command to work, i need to have a blank table created in vertica which has the same column names and data type properties as the table i want to import?
    2. I have exported some of the tables to CSV but it will be very tedious to manually create a table with 50 odd columns in vertica. How do i load these csv files into vertica?
    3. I Scripted the tables from sql server and i got the table structure in a .sql file but its not working well and throwing errors. How do i load the .sql file?
    4. I tried uploading the CSV in a flex table but i am unable to query the keys.
    Please Guide!

    Regards,
    Anmol
  • Hi anmol,
    1. Am i correct to say that for the copy from local command to work, i need to have a blank table created in vertica which has the same column names and data type properties as the table i want to import?
    Yes, the table has to be present already in Vertica database, but its not mandatory to have the same column names as the data is going to come from Files, as long as each column adheres to its properties( length) it can intake data using COPY FROM LOCAL option.
    Regarding columns, Vertica COPY command gives the flexibility to you for specifying which columns you need to load. this way you can load the column you want from SQL server to Vertica.
    1. I have exported some of the tables to CSV but it will be very tedious to manually create a table with 50 odd columns in vertica. How do i load these csv files into vertica?
    If you don't want to load data into Columnar tables, you can use Flex tables to load CSV without having a proper/strict schema, this will make sure columns are created on the fly in flex tables. Loading CSV files to Flex tables can be done through a a parser called fdelimitedparser
    Refer this 
    Loading Flex tables
    1. I Scripted the tables from sql server and i got the table structure in a .sql file but its not working well and throwing errors. How do i load the .sql file?
    Check you sql file , there will be compatibility issues when it comes to data types between SQL server and Vertica
    For example :
    SQL server - nvarchar
    Vertica - varchar

    If you are facing any other issue other then this while executing the SQL, let us know on this.
    1. I tried uploading the CSV in a flex table but i am unable to query the keys.
    Please paste your example here, so that its easy to understand.

    Hope this helps


  • Thanks a ton for the info Naveen!!

    All the points check out. I was able to load data into the flex tables though i havent tried running the .sql files.

    One point i am stuck on:

    Flex tables compute both the keys and the views. Essentially that means it can divide the data into columns as determined by the parser. Correct?

    So saying i have loaded data into a flex table, queried the flex view for the columns i need. How do i get them into a non flex data table?

    Thanks a ton again!

    Anmol
  • Navin,

    Waiting for your feednack :)

    regards
  • Hi Anmol,

    This can help you understand the flow better.

    How to load CSV files into Flex table

    Hope this helps.
  • Hey Navin,

    The blog post is supremely helpful. Great job putting it together. :)

    While reiterating my problem, You have mentioned that we can load data into vertica from SQL server.

    The extraction and loading of CSV files worked like a charm using the flex tables.

    My second problem. There is another environment where i have a SQL server and a vertica machine, both different machines. How do i use the drivers to fetch data into vertica from SQL server? I hope there should not be any extract or transform involved. Is it possible?

    Best Regards,
    Anmol
  • One more query on that,

    How does this whole sql server -> vertica scenario play out?

    #1: Install odbc drivers on linux and then the parser and loader components are used to fetch data from MSS to vertica

                                                              OR

    #2: Install microsoft connectivity pack on windows and then create a batch file to push data into vertica.

    Are both the assumptions true, considering that i will have to create a table manually in vertica in both scenarios.

    Regards,
    Anmol
  • Hi Anmol,

    Yes both assumptions are true, besides the first method has an advantage over the second method,

    The advantage is after setting up the complete environment, there is no need of extract into a file and then load into vertica. the COPY command takes care of this process using SOURCE and PARSER.

    Regarding the "CREATE TABLE" , yes you have to do that manually on vertica.

    Hope this helps.

  • Hi anmol, Have you abled to import table from sql server to vertica??

  • Hi Navin, I need your help on same problem

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.