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
add comment

4 Answers

up vote 2 down vote accepted

You can run an INSERT statements from within vsql.

share|improve this answer
    
vsql seems to be a binary. Using something like : exec "vsql 'insert ....'" doesn't seem a good idea to me. Using exec for 20 000 transactions per second doesn't seem realistic. Is a VSQL a plain executable binary ? –  SCO Nov 18 '11 at 21:41
2  
I agree it's not ideal, but i don't believe there is a specific API. What's the performance like? (vsql is a binary) is it actually causing an issue to call vsql to perform inserts. vsql works across a network too (without having to install Vertica, you can simply copy the vsql binary to another system providing the operating system is the same / similar) vsql -h <hostname> –  dba Nov 21 '11 at 12:40
    
@dba I've converted your other answer to a comment as I'm sure that's what you intended - you should be able to comment on your own posts even before 50 rep though? –  Jack Douglas Nov 21 '11 at 15:53
    
I have not tested, but I see several points that make me think that the performance will be wrong : 1/ Using "exec" forces the OS to create a new process. A new process needs memory, kernel objects, time to initialize. This is a big penalty if done thousands of time a second. 2/ Executing vsql means that you open a brand new connection to the database, which is consuming and really really bad for performance. I'm stunned there is no API ! I'll close this as "answered" within a few days if no better answer. Thank you anyway ! –  SCO Nov 21 '11 at 21:49
    
I see that this is the accepted answer but, frankly, that puzzles me because it doesn't answer the question posed. Wouldn't a more appropriate answer be to write a C program that links to native drivers and uses an ODBC connection for general purpose access? 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
add comment

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
add comment

Um, why don't you just use their ODBC driver?

share|improve this answer
add comment

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
add comment

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.