COPY FROM LOCAL does not work?

[jaceksan]

I am not sure, if COPY FROM LOCAL should work, so do not hesitate to close this issue with status "not yet implemented" ;-)

Please, see simple PoC demonstrating the issue here:
https://github.com/jaceksan/vertica-python/blob/copy_local_poc/jacek/copy_local_poc.py

[alexjikim]

ah yes. copy from stdin is the only thing we support at the moment. ill leave this open as a feature request, but i dont think we have any plans to add this at the moment.

[dennisobrien]

Just for comparison to other Vertica native adapter projects:

The Ruby project does not implement COPY FROM LOCAL either:
wvanbergen/vertica#33

And the recently released Python native adapter from HP Vertica supports COPY FROM STDIN but not from a filepath.
https://my.vertica.com/docs/7.2.x/HTML/Content/python_client/loadingdata_copystdin.html

It's great that vertica-python supports COPY FROM STDIN (via the cursor.copy method). Ideally COPY FROM LOCAL could just be done from a cursor.execute call (to make it more portable to pyodbc and jaydebeapi), but it must be tricky to implement since many projects extend the DBAPI2 with cursor.copy. Even psycopg2 has cursor.copy_from and cursor.copy_expert.

[jaceksan]

Denis,
nice to see, that somebody read my request / concern ;-)
Meanwhile things have changed a lot on my side.

1. COPY FROM STDIN can be an alternative to FROM LOCAL in all cases
   so it is not necessary to rely on implementation of COPY FROM LOCAL any more
2. We use COPY ON ANY NODE if possible
   it is incredibly faster and it works using standard cur.execute() method.

[babinu-uthup-4JESUS-zz]

Hi,

There are definitely some use cases where COPY FROM LOCAL works really well, one example being while adding sequence numbers to records while loading them to vertica (see https://community.dev.hpe.com/t5/Vertica-Forum/Add-sequence-numbers-to-a-bulk-load-in-vertica/td-p/237457).

We have tried to do the same thing using COPY FROM STDIN and we see that ordering of sequence numbers is not guaranteed to be the same as the records in the file.

So definitely, COPY FROM LOCAL wins here and it would be great if we could see the support added for the same !

[patuson]

How can we use copy command to load data to vertica using Pycharm in Python ?

Is copy command is working to copy the data from local to vertica in python

[dennisobrien]

@son2 Here's an example function that takes a vertica-python connection to load the data in the dataframe df into the table loading_table. This will also work with pyodbc or turbodbc connections.

def copy_dataframe_to_table(connection, loading_table, df):
    """Use Vertica COPY to load the data in the dataframe `df` into `loading_table`.
    The columns of `df` must match the columns of `loading_table`.

    """
    cursor = connection.cursor()
    csv_buf = io.StringIO()
    df.to_csv(csv_buf, header=False, index=False, encoding='utf-8', quoting=csv.QUOTE_MINIMAL, escapechar='\\')
    query = """
    COPY {loading_table}
    ({column_names})
    FROM stdin
    DELIMITER ',' ABORT ON ERROR NO COMMIT;
    """.format(loading_table=loading_table, column_names=', '.join(df.columns))
    cursor.copy(query, csv_buf)
    connection.commit()
    return len(df)

[patuson]

Thanks Dennis.

…

On Wed, Aug 15, 2018 at 3:51 AM dennisobrien ***@***.***> wrote: @son2 <https://github.com/son2> Here's an example function that takes a vertica-python connection to load the data in the dataframe df into the table loading_table. This will also work with pyodbc or turbodbc connections. def copy_dataframe_to_table(connection, loading_table, df): """Use Vertica COPY to load the data in the dataframe `df` into `loading_table`. The columns of `df` must match the columns of `loading_table`. """ cursor = connection.cursor() csv_buf = io.StringIO() df.to_csv(csv_buf, header=False, index=False, encoding='utf-8', quoting=csv.QUOTE_MINIMAL, escapechar='\\') query = """ COPY {loading_table} ({column_names}) FROM stdin DELIMITER ',' ABORT ON ERROR NO COMMIT; """.format(loading_table=loading_table, column_names=', '.join(df.columns)) cursor.copy(query, csv_buf) connection.commit() return len(df) — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#43 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AcAtQ7onKA6kBh_Pq_whI3vSfnrcKZPSks5uQ01bgaJpZM4DoG5g> .

[arumugaguru]

Hi

I tried the above example but it is not working. I can see the data in the buffer but it is not getting loaded into Vertica

Could someone please help me if i am missing anything in my code.

Thanks

My Code:

import vertica_python
import csv
from datetime import datetime
import pandas as pd

with open('test.csv', newline='') as csvfile:
data = list(csv.reader(csvfile))

Enable using server-side prepared statements at connection level

conn_info = {'host': '127.0.0.1',
'user': 'dbadmin',
'password': '',
'database': 'VMart',
}

connection = vertica_python.connect(**conn_info)
rows = []
for i,row in enumerate(data):
rows.append(row)
cur = connection.cursor()
dfObj = pd.DataFrame(rows)
csv_buf = io.StringIO()
dfObj.to_csv(csv_buf,header=False, index=False, encoding='utf-8', quoting=csv.QUOTE_MINIMAL, escapechar='\')
column_names="id_threatparent,date,ip_source,ip_destination,ip_source_latitude,ip_source_longitude,ip_source_country_name,ip_source_region_name,ip_source_city_name,ip_source_organization_name,ip_destination_latitude,ip_destination_longitude,ip_destination_country_name,ip_destination_region_name,ip_destination_city_name,ip_destination_organization_name,threat_type,threat_severity,threat_name,threat_description,status,ip_destination_isp_name,id_isp_lokasi_perangkat,ta_ip"
query = """ COPY {loading_table} ({column_names}) FROM stdin DELIMITER ',';""".format(loading_table="parent",column_names=column_names)
cur.copy(query, csv_buf)
connection.commit()

[sitingren]

@arumugaguru Can you enclose your code with ``` so that it displays the indentation correctly? Your current code is hard to read. Thanks.

[haishao]

i encountered the same problem as @arumugaguru. it seems that
csv_buf.seek(0)
needs to be added before calling
cur.copy(query, csv_buf)