cassandra-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stefania (JIRA)" <>
Subject [jira] [Commented] (CASSANDRA-12794) COPY FROM with NULL='' fails when inserting empty row in primary key
Date Mon, 17 Oct 2016 01:42:59 GMT


Stefania commented on CASSANDRA-12794:

The idea of CASSANDRA-7792 is that we set NULL to something other than the empty string, something
that is not present in the csv, e.g. {{NULL='\-'}}. The problem is that if we do this in this
example, then it fails to convert the empty strings of the missing {{bigint}} values when
passing them to prepared statements. We could bypass this by disabling prepared statements
with {{PREPAREDSTATEMENTS = False}}, but then it throws another parsing error. The only workaround
at the moment is to insert zeros for the missing {{bigint}} values, and then it can be imported
with something like {{NULL='-'}}. You can also copy only the columns that are present into
the csv by specifying the column names in both the export and import commands.

This specific example with empty strings in composite partition keys of type text, and empty
{{bigint}} fields is not something that we haven't tested so far, and we will need a patch
for it. 

> COPY FROM with NULL='' fails when inserting empty row in primary key 
> ---------------------------------------------------------------------
>                 Key: CASSANDRA-12794
>                 URL:
>             Project: Cassandra
>          Issue Type: Bug
>          Components: CQL
>         Environment: Tested using C* 2.1.15
>            Reporter: Sucwinder Bassi
> Using this table:
> CREATE TABLE testtab (  a_id text,  b_id text,  c_id text,  d_id text,  order_id uuid,
 acc_id bigint,  bucket bigint,  r_id text,  ts bigint,  PRIMARY KEY ((a_id, b_id, c_id, d_id),
> insert one row:
> INSERT INTO testtab (a_id, b_id , c_id , d_id , order_id, r_id ) VALUES ( '', '', '',
'a1', 645e7d3c-aef7-4e3c-b834-24b792cf2e55, 'r1');
> Use COPY to dump the row to temp.csv:
> copy testtab TO 'temp.csv';
> Which creates this file:
> $ cat temp.csv 
> ,,,a1,645e7d3c-aef7-4e3c-b834-24b792cf2e55,,,r1,
> Truncate the testtab table and then use copy from with NULL='' to insert the row:
> cqlsh:sbkeyspace> COPY testtab FROM 'temp.csv' with NULL='';
> Using 1 child processes
> Starting copy of sbkeyspace.testtab with columns ['a_id', 'b_id', 'c_id', 'd_id', 'order_id',
'acc_id', 'bucket', 'r_id', 'ts'].
> Failed to import 1 rows: ParseError - Cannot insert null value for primary key column
'a_id'. If you want to insert empty strings, consider using the WITH NULL=<marker> option
for COPY.,  given up without retries
> Failed to process 1 rows; failed rows written to import_sbkeyspace_testtab.err
> Processed: 1 rows; Rate:       2 rows/s; Avg. rate:       3 rows/s
> 1 rows imported from 1 files in 0.398 seconds (0 skipped).
> It shows 1 rows inserted, but the table is empty:
> select * from testtab ;
>  a_id | b_id | c_id | d_id | order_id | acc_id | bucket | r_id | ts
> ------+------+------+------+----------+--------+--------+------+----
> (0 rows)
> The same error is returned even without the with NULL=''. Is it actually possible for
copy from to insert an empty row into the primary key? The insert command shown above inserts
the empty row for the primary key without any problems.
> Is this related to

This message was sent by Atlassian JIRA

View raw message