COPY FROM LOCAL with DELIMITED file

[ramvertica]

I am using Vertica 6 in an AIX machine. I'm connecting to vertica using VSQL and I use COPY command to load data from my local directory to vertica DB. I load both FIXED LENGTH as well as DELMITED file.

Fixed length :

Code: Select all

cat filename.txt | vsql -h myverticanode -u myuser -w passwd -c "set role all; copy schema.tablename from stdin FIXEDWIDTH COLSIZES(3,4,2,4) ABORT ON ERROR DIRECT"

Delimited :

Code: Select all

cat filename.txt | vsql -h myverticanode -u myuser -w passwd -c "set role all; copy schema.tablename from stdin delimiter '|' NULL '?' TRAILING NULLCOLS ABORT ON ERROR DIRECT"

The above statement has executed well. I want to have exception and rejection files in my calling client in case of any bad records. So I used COPY FROM LOCAL as below:

Fixed length :

Code: Select all

cat filename.txt | vsql -h myverticanode -u myuser -w passwd -c "set role all; copy schema.tablename from local stdin FIXEDWIDTH COLSIZES(3,4,2,4) REJECTED DATA '/mypath/filename.rejections' EXCEPTIONS '/mypath/filename.exceptions' ABORT ON ERROR DIRECT"

Delimited :

Code: Select all

cat filename.txt | vsql -h myverticanode -u myuser -w passwd -c "set role all; copy schema.tablename from local stdin delimiter '|' NULL '?' REJECTED DATA '/mypath/filename.rejections' EXCEPTIONS '/mypath/filename.exceptions' TRAILING NULLCOLS ABORT ON ERROR DIRECT"

[/color]

When I execute the above cmd for delimited text, it throws error :
ERROR 0: client connection I/O error.
Whereas it works for a fixed length file. If I do not use COPY FROM LOCAL then I cannot create EXCEPTION and REJECTION file.

Can you please help in solving the above issue?

[id10t]

Hi!

Perhaps you have no permissions to write in "/mypath". Check it by

Code: Select all

touch /mypath/whatever.txt && rm -rf /mypath/whatever.txt

FYI:
"vsql -c" can execute only one command at once

Code: Select all

daniel@synapse:~$ /opt/vertica/bin/vsql -c "select 1; select 'foo';"
 ?column? 
----------
 foo
(1 row)

So "set role all;" - not executed

[ssrao]

Input file is test.log
100.2.46.244|-|101.4.28.244|-|-|22/May/2010:15:52:12|GET|/I/0/_15449/QA_B101136Z13679366060.mp4|HTTP/1.1|-|206|104857600|104857600|0|1|Telcom/0.1|

Output
--------
Ip1 100.2.46.244
ip2 101.4.28.244
Datetime 22/May/2010:15:52:12
verb GET
DT I
DF 0
SID _15449
FileName QA_B101136Z13679366060.mp4
AF QA_B101136Z13679366060.mp4
Version 1.1
statuscode 206
CL 104857600
BT 104857600
Dur 0
cache 1
UA Telcom-0.1

How to insert this data into table using copy command?

Can we write conditions inside copy command using case statements?
1.when DT is I and status is 300 then sid is Null

thanks in advance.