python to_sql with method='multi' error

davds

I try to use lib: sqlalchemy_vertica to_sql function load data into Vertica, but found out performance is slow.

I tried to use the option: method='multi',chunksize=1000 to do bulk insert, but I got error.

Does Vertica support this option? any suggestion to speed up this insert process?

thanks
David

Bryan_H

What version of Vertica are you running? Multi-row INSERT as shown is support in v11.1.1 and later. Upgrade Vertica, or try it without the "multi" keyword.

Bryan_H

Vertica 9 is end of life according to https://www.microfocus.com/productlifecycle/
The current released Python driver is compatible with version 9.x to current, so VerticaPy should also work with Vertica 9.

Bryan_H

Which Python package did you install? sqlalchemy_vertica appears older, maybe try https://pypi.org/project/sqlalchemy-vertica-python/
Also, what type of object are you trying to load? Vertica supported tools like the natvie Python driver or VerticaPy may provide better functionality and performance.

davds

PYTHON 3.10

I try to load data frame to vertica, if without "method='multi', chunksize=100" work fine, but it was slow.
but after I add these two options, I got syntax error.

here is example how to use to_sql in the other database:
https://stephenallwright.com/pandas-to_sql/

---

Python code:

df.to_sql('test', schema='sandbox'
, con=engine, if_exists='replace', index=False

, method='multi', chunksize=10

ERROR:

[SQL: INSERT INTO test (NAME, DEPT, num_list, min_date, max_date, cnt)
VALUES ('A','HR','1,2,3','01/01','02/01',10), ('B','Sale','1,2,3','01/01','02/01',10)

sqlalchemy.exc.ProgrammingError: (vertica_python.errors.VerticaSyntaxError) Severity: ERROR, Message: Syntax error at or near ",",

davds

OK, got it. our Vertica version is not support and we need upgrade Vertica first.

thanks!

Bryan_H

Another option is to import VerticaPy and use the pandas_to_vertica function:
https://www.vertica.com/python/documentation_last/utilities/pandas_to_vertica/index.php

davds

our current Vertica is: Vertica Analytic Database v9.2.1-28

does this still is in Vertica support version? has the end-of-life date for this version?

thanks

VValdar

Hi @davds ,

9.2 is no longer on committed support meaning we won't backport new features or bug resolutions on this version.
But you can still open cases and customer support will provide answers, as long as you have a valid support contract.

Even if we encourage our customers to upgrade to latest version when possible we don't force anyone.
Of course, not upgrading prevents the access to the most recent features and bug fixes.

davds

still got error said : table not found.

Connecting to the Database

vp.connect("MyVerticaConnection")

from verticapy.utilities import *

vp.pandas_to_vertica( df = df
, name= "tmp_vertica_tst_ds2"
, schema= "sandbox"
, insert = "True"
)

Traceback (most recent call last):
File "C:\Users\PycharmProjects\pythonProject1\vertica_read_sql_build_pattern_onfly.py", line 150, in
vp.pandas_to_vertica( df = df
File "C:\Users\PycharmProjects\pythonProject1\venv\lib\site-packages\verticapy\utilities.py", line 1128, in pandas_to_vertica
vdf = vDataFrame(name, schema=schema)
File "C:\Users\PycharmProjects\pythonProject1\venv\lib\site-packages\verticapy\vdataframe.py", line 350, in init
assert columns != [], MissingRelation(
AssertionError: No table or views 'tmp_vertica_tst_ds2' found.

but I tried to new table, got table not exist:

vertica_python.errors.MissingRelation: Severity: ERROR, Message: Relation "sandbox.tmp_vertica_tst_ds2" does not exist,