Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm investigating column oriented databases and came across Vertica.

My need is to feed the Vertica database from C code. I don't succeed in grabbing this information from Vertica: I'm told to use "vsql" and the "copy" command. All I want is issue INSERT statements to my Vertica database.

Can this be done?

For instance, in PostgreSQL you can do "embedded SQL" by linking the Postgres ecpg library to your C binary. I have no idea if such thing exists for Vertica, and I know of no other way.

Any ideas?

share|improve this question
    
You should be able to do that through ODBC. –  a_horse_with_no_name Oct 30 '12 at 10:34

2 Answers 2

You have two options really: You can create a delimited file with your data, like the following:

row1col1data,row1col2data,row1col3data,row1col4data
row2col1data,row2col2data,row2col3data,row2col4data
row3col1data,row3col2data,row3col3data,row3col4data

You can then load that into Vertica using the COPY command and vsql. If you have database superuser rights, you can load the file directly, using:

vsql -U <username> -w <password> YOURDATABASENAME -c "COPY yourtablename (col1name, col2name, col3name, col4name) FROM 'yourdelimitedfile' DELIMITER ','"

Omit -w if you want to enter keep the password out of your bash history and enter it when the program starts. If you don't have database superuser rights, you can still cat the file into vsql and get the data from STDIN - quite why Vertica prevents you from loading data from a file without superuser rights, but will let any user pipe it in via cat, I have no idea, but you can do it as follows:

cat yourdelimitedfile | vsql -U <username> -w <password> YOURDATABASENAME -c "COPY yourtablename (col1name, col2name, col3name, col4name) FROM STDIN DELIMITER ','"

Or, if you have a bunch of individual insert statements in a file, separated by semicolons, you can just run the file through vsql, as follows:

vsql -U <username> -w <password> YOURDATABASENAME -f pathtoyoursqlfile

But if you're doing more than 1000 rows, bulk loading using a delimited file is supposed to be much faster.

Hope that helps!

share|improve this answer

Vertica includes an ODBC driver for C.

Read "Programmer's > Guide Vertica Client Libraries > Programming ODBC Client Applications"

This section details the process for configuring the Vertica ODBC driver. It also explains how to use the ODBC API to connect to Vertica in your own client applications.

share|improve this answer
    
Yes, and use of ODBC is clearly described in the Vertica Programmer's Guide and well documented in both Microsoft's ODBC Programmer's Reference and references on the unixODBC documentation page. –  Danny Whitt Feb 3 '12 at 1:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.