Loading data into Vertica database from a file

[psunkara]

Hi, I'am new to Vertica , trying to load data into vertica database from a | delimited file but it throws me the error below. I have defined the datatype for trans_gen_key as Bigint(19) but not sure some records are getting loaded fine but some are getting rejected. Can anyone please advise.
Thanks
COPY: Input record 3718 has been rejected (int8 out of range '09224201303800141001' for column 1 (trans_gen_key)). Please see /TRDW/PROD/data/incoming/rejects/testfinREJ.DAT, record 1 for the rejected record. This record was read from testfin.dat

[JimKnicely]

In Vertica an INTEGER is a signed 8-byte (64-bit) data type. Synonyms for INTEGER include INT, BIGINT, INT8, SMALLINT and TINYINT.

The greatest value that can be stored in this data type is 9223372036854775807.

Code: Select all

dbadmin=> create table t (c bigint);
CREATE TABLE

dbadmin=> insert into t values (9223372036854775807);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into t values (9223372036854775808);
ERROR 5411:  Value exceeds range of type numeric(18,0)

Unfortunately, your value of 9224201303800141001 is also too big!

Code: Select all

dbadmin=> insert into t values (9224201303800141001);
ERROR 5411:  Value exceeds range of type numeric(18,0)

I hope this helps!

[ssrao]

Hi,
213.4.28.244 - 95.123.101.114 - - [05/Jun/2013:08:27:47] "GET /X/0/crossdomain.xml HTTP/1.1 http://democdn.biz.tm/player-5.1.897.swf" 200 307 307 0 0 "Mozilla/5.0 (Windows NT 5.1; rv:21.0) Gecko/20100101 Firefox/21.0"

The above record contains many delimiters and not able to write correct copy command.

Can anyone please write copy command for above record.

Advance in thanks

Regards,
ssrao.

[JimKnicely]

Hi!

What do you mean by many delimiters? Can you please describe the table you want to load the data into? That will help us write the COPY command. Or, you can list the data line by line as you want it delimited.

For instance, and this is a guess...

• 1) 213.4.28.244
  2) 95.123.101.114
  3) 05/Jun/2013:08:27:47
  4) "GET /X/0/crossdomain.xml HTTP/1.1 http://democdn.biz.tm/player-5.1.897.swf" 200 307 307 0 0 "Mozilla/5.0 (Windows NT 5.1; rv:21.0) Gecko/20100101 Firefox/21.0"

Thanks!

[ssrao]

Hi,
Please find the below info.
1) 213.4.28.244 -EIP
2) 95.123.101.114 - UIP
3) 05/Jun/2013:08:27:47 -Datetime
4) "GET - verb
5) /Xn -DT
6)/0 -DF
7) /crossdomain.xml - url
8) HTTP/1.1 - version
9)http://democdn.biz.tm/player-5.1.897.swf" -Ref. URL
10)200 - status
11) 307 - CL
12)307 -Bytes
13)0 -dur
14 0-cache
15 "Mozilla/5.0 (Windows NT 5.1; rv:21.0) Gecko/20100101 Firefox/21.0" -UA

I am writing the command like this way
COPY schema.tablename(EIP delimiter '-',cip delimiter '-',Request_Time delimiter '[' ...............) FROM 'path/filename;
Can you please check and give the correct command.

Thanks in advance

[id10t]

Hi!

Vertica provides Apache Log parser as UDF (default location /opt/vertica/sdk/examples/TransformFunctions/ApacheParser.cpp). You can adapt it to UDL function, or use it as shown in example (default location /opt/vertica/sdk/examples/TransformFunctions.sql).

[ssrao]

Hi,

Transform UDF will not help in this case.Plz try to give exact copy command syntax..