How to load data into Vertica from C?

Question

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?

Answer 1

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.

Answer 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!