Copy from Oracle to Vertica via CSV

drno-reg

Hello,

I try to load data from Oracle to Vertica via CSV file

Used python,

wrote this script for create CSV from Oracle

csv_file=open("C:\DataBases\csv\%s_%s.csv" % (FILE_NAME, TABLE_NAME), "a",encoding='utf-8')
for row in cursor:
count_rows+=1
result_inside={}
row_content=[]
for col, val in zip(col_names, row):
result_inside[col] = val
row_content.append(result_inside[col])
result_select_from_oracle.append(result_inside)
file.write(json.dumps(result_inside, default = myconverter))
writer = csv.writer(csv_file, delimiter=';', quoting=csv.QUOTE_ALL)
writer.writerow(row_content)

wrote this script for COPY CSV to Vertica

connection = vertica_python.connect(**conn_info)
cursor = connection.cursor()
with open("C:\DataBases\csv\%s_%s.csv" % (FILE_NAME, TABLE_NAME), "rb") as fs:
record_terminator='\n')" %(SCHEMA_NAME, TABLE_NAME), my_file)
cursor.copy("COPY %s.%s from stdin PARSER fcsvparser(type='traditional', delimiter=';', record_terminator='\n')" %(SCHEMA_NAME, TABLE_NAME), my_file)
connection.commit()
connection.close()

After fineshed operation I had problem
from oracle
Unloaded 40 000 rows
BUT in Vertica 39700 rows.

Where there can be a problem and how to solve it?

peterjansens

It may be that your oracle data isnt fully 'Vertica compatible' and your data gets rejected while attempting the load, have a peek here: https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/BulkLoadCOPY/CapturingLoadExceptionsAndRejections.htm
this section may also be useful:
https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Compatibility/AppendixCompatibilityWithOtherRDBMS.htm

sKwa

Hi!

cursor.copy("COPY %s.%s from stdin PARSER fcsvparser(type='traditional', delimiter=';', record_terminator='\n')" %(SCHEMA_NAME, TABLE_NAME), my_file)

Why do you use fcsvparser ? At least one row lost because of header.

Try a standard Vertica parser

COPY %s.%s FROM STDIN DELIMITER ';'  ...

COPY LOCAL for my opinion is better than COPY ... STDIN
COPY params, take a look on params ABORT ON ERROR, REJECTED DATA and EXCEPTIONS.

---

FYI: http://vertica-forums.com/viewtopic.php?t=2642&p=8851#p8521

drno-reg

Why If I not used option type='traditional' - loading rows = 0?