Bulk insert into Vertica using Python using Uber's vertica-python package

Question

Question 1 of 2

I'm trying to import data from CSV file to Vertica using Python, using Uber's vertica-python package. The problem is that whitespace-only data elements are being loaded into Vertica as NULLs; I want only empty data elements to be loaded in as NULLs, and non-empty whitespace data elements to be loaded in as whitespace instead.

For example, the following two rows of a CSV file are both loaded into the database as ('1','abc',NULL,NULL), whereas I want the second one to be loaded as ('1','abc',' ',NULL).

1,abc,,^M
1,abc,  ,^M

Here is the code:

# import vertica-python package by Uber
#   source: https://github.com/uber/vertica-python
import vertica_python

# write CSV file
filename = 'temp.csv'
data = <list of lists, e.g. [[1,'abc',None,'def'],[2,'b','c','d']]>
with open(filename, 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f, escapechar='\\', doublequote=False)
        writer.writerows(data)

# define query
q = "copy <table_name> (<column_names>) from stdin "\
    "delimiter ',' "\
    "enclosed by '\"' "\
    "record terminator E'\\r' "

# copy data
conn = vertica_python.connect( host=<host>,
                               port=<port>,
                               user=<user>,
                               password=<password>,
                               database=<database>,
                               charset='utf8' )
cur = conn.cursor()
with open(filename, 'rb') as f:
    cur.copy(q,  f)
conn.close()

Question 2 of 2

Are there any other issues (e.g. character encoding) I have to watch out for using this method of loading data into Vertica? Are there any other mistakes in the code? I'm not 100% convinced it will work on all platforms (currently running on Linux; there may be record terminator issues on other platforms, for example). Any recommendations to make this code more robust would be greatly appreciated.

In addition, are there alternative methods of bulk inserting data into Vertica from Python, such as loading objects directly from Python instead of having to write them to CSV files first, without sacrificing speed? The data volume is large and the insert job as is takes a couple of hours to run.

Thank you in advance for any help you can provide!

Answer 1

The copy statement you have should perform the way you want with regards to the spaces. I tested it using a very similar COPY.

Edit: I missed what you were really asking with the copy, I'll leave this part in because it might still be useful for some people:

To fix the whitespace, you can change your copy statement:

copy <table_name> (FIELD1, FIELD2, MYFIELD3 AS FILLER VARCHAR(50), FIELD4, FIELD3 AS NVL(MYFIELD3,'') ) from stdin

By using filler, it will parse that into something like a variable which you can then assign to your actual table field using AS later in the copy.

As for any gotchas... I do what you have on Solaris often. The only one thing I noticed is you are setting the record terminator, not sure if this is really something you need to do depending on environment or not. I've never had to do it switching between linux, windows and solaris.

Also, one hint, this will return a resultset that will tell you how many rows were loaded. Do a fetchone() and print it out and you'll see it.

The only other thing I can recommend might be to use reject tables in case any rows reject.

You mentioned that it is a large job. You may need to increase your read timeout by adding 'read_timeout': 7200, to your connection or more. I'm not sure if None would disable the read timeout or not.

As for a faster way... if the file is accessible directly on the vertica node itself, you could just reference the file directly in the copy instead of doing a copy from stdin and have the daemon load it directly. It's much faster and has a number of optimizations that you can do. You could then use apportioned load, and if you have multiple files to load you can just reference them all together in a list of files.

It's kind of a long topic, though. If you have any specific questions let me know.