CODEWITHSUNDEEP
cassandracqlcqlsh
arthur
arthur

Reputation: 1064

cassandra's copy does not insert a single row

In cassandra ([cqlsh 5.0.1 | Cassandra 2.2.0 | CQL spec 3.3.0 | Native protocol v4]) I have created the following table 

create table flotilla.events1 (
    f1 int,
    f2 int,
    f3 int,
    f4 timestamp,
    f5 text,
    f6 text,
    f7 text,
    f8 int,
    f9 text,
    f10 double,
    f11 int,
    primary key (f2, f1)
);

and trying to insert the following data (/tmp/b.csv):

f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11
1,751168360,0,'2014-04-01T09:56:13.491','a','G','G',296,'G',242,242
2,751168360,0,'2014-04-01T09:56:13.491','a','G','G',296,'G',8,8
3,751168360,0,'2014-04-01T09:56:13.491','a','G','G',296,'G',0,0
4,751168360,0,'2014-04-01T09:56:13.491','a','G','G',296,'G',1,1
5,751168360,0,'2014-04-01T09:56:13.491','a','G','G',296,'G',0,0
6,751168360,0,'2014-04-01T09:56:13.491','a','G','G',296,'G',0,0
7,751168360,0,'2014-04-01T09:56:13.491','a','A','A',279,'P',2.55,255
8,751168360,0,'2014-04-01T09:56:13.491','a','A','A',279,'P',0,0

with the following command:

copy flotilla.events1 (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11) from '/tmp/b.csv' with header=true;

Cassandra says:

8 rows imported in 0.550 seconds.

and when I am checking the table:

SELECT * from flotilla.events1 ;

 f2 | f1 | f10 | f11 | f3 | f4 | f5 | f6 | f7 | f8 | f9
----+----+-----+-----+----+----+----+----+----+----+----

(0 rows)

without a single error. What a hack?

However if I insert the first line with INSERT INTO:

insert into flotilla.events1 (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11) values (1,751168360,0,'2014-04-01T09:56:13.123','a','G','G',296,'G',242,242) ; select * from flotilla.events1;

 f2        | f1 | f10 | f11 | f3 | f4                       | f5 | f6 | f7 | f8  | f9
-----------+----+-----+-----+----+--------------------------+----+----+----+-----+----
 751168360 |  1 | 242 | 242 |  0 | 2014-04-01 09:56:13+0200 |  a |  G |  G | 296 |  G

(1 rows)

What a hack?

Upvotes: 1

Views: 1002

Answers (2)

arthur
arthur

Reputation: 1064

I am posting an additional answer, since the original problem was to import this data (which was residing in a compressed csv.gz File):

f1;f2;f3;f4;f5;f6;f7;f8;f9;f10;f11
1;751168360;0;'2014-04-01T09:56:13.491111';'a';'G';'G';296;'G';242;242
2;751168360;0;'2014-04-01T09:56:13.491111';'a';'G';'G';296;'G';8;8
3;751168360;0;'2014-04-01T09:56:13.491111';'a';'G';'G';296;'G';0;0
4;751168360;0;'2014-04-01T09:56:13.491111';'a';'G';'G';296;'G';1;1
5;751168360;0;'2014-04-01T09:56:13.491111';'a';'G';'G';296;'G';0;0
6;751168360;0;'2014-04-01T09:56:13.491111';'a';'G';'G';296;'G';0;0
7;751168360;0;'2014-04-01T09:56:13.491111';'a';'A';'A';279;'P';2.55;255
8;751168360;0;'2014-04-01T09:56:13.491111';'a';'A';'A';279;'P';0;0

careful selection of the parameters (DELIMITER, QUOTE, HEADER) allows to import the data without errors:

zcat /tmp/a.csv.gz | cqlsh -e "copy flotilla.events1 (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11) from STDIN WITH DELIMITER=';' and QUOTE='''' and header=true;"

however I still believe that microseconds (.491111) causes errors, as cassandra can handle only miliseconds

Upvotes: 1

Aaron
Aaron

Reputation: 57748

Similar to how I answered this question an hour ago, to get this to work I simply removed all single quotes from your CSV file.

f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11
1,751168360,0,2014-04-01T09:56:13.491,a,G,G,296,G,242,242
2,751168360,0,2014-04-01T09:56:13.491,a,G,G,296,G,8,8
3,751168360,0,2014-04-01T09:56:13.491,a,G,G,296,G,0,0
4,751168360,0,2014-04-01T09:56:13.491,a,G,G,296,G,1,1
5,751168360,0,2014-04-01T09:56:13.491,a,G,G,296,G,0,0
6,751168360,0,2014-04-01T09:56:13.491,a,G,G,296,G,0,0
7,751168360,0,2014-04-01T09:56:13.491,a,A,A,279,P,2.55,255
8,751168360,0,2014-04-01T09:56:13.491,a,A,A,279,P,0,0

Then, the COPY command worked just fine:

aploetz@cqlsh:stackoverflow> COPY events1 (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11) FROM '/home/aploetz/cassandra_stack/b.csv' WITH header=true;

8 rows imported in 0.627 seconds.
aploetz@cqlsh:stackoverflow> SELECT * FROM events1 ;

 f2        | f1 | f10  | f11 | f3 | f4                       | f5 | f6 | f7 | f8  | f9
-----------+----+------+-----+----+--------------------------+----+----+----+-----+----
 751168360 |  1 |  242 | 242 |  0 | 2014-04-01 09:56:13-0500 |  a |  G |  G | 296 |  G
 751168360 |  2 |    8 |   8 |  0 | 2014-04-01 09:56:13-0500 |  a |  G |  G | 296 |  G
 751168360 |  3 |    0 |   0 |  0 | 2014-04-01 09:56:13-0500 |  a |  G |  G | 296 |  G
 751168360 |  4 |    1 |   1 |  0 | 2014-04-01 09:56:13-0500 |  a |  G |  G | 296 |  G
 751168360 |  5 |    0 |   0 |  0 | 2014-04-01 09:56:13-0500 |  a |  G |  G | 296 |  G
 751168360 |  6 |    0 |   0 |  0 | 2014-04-01 09:56:13-0500 |  a |  G |  G | 296 |  G
 751168360 |  7 | 2.55 | 255 |  0 | 2014-04-01 09:56:13-0500 |  a |  A |  A | 279 |  P
 751168360 |  8 |    0 |   0 |  0 | 2014-04-01 09:56:13-0500 |  a |  A |  A | 279 |  P

(8 rows)

Remove your single quotes, and then it should work.

Upvotes: 3

Related Questions