2

I am trying to export and import data from a cassandra table for changing a timestamp column to unixepoch column ( ie type timestamp to bigint)

I tried exporting data to csv using below command

COPY raw_data(raw_data_field_id, toUnixTimestamp(dt_timestamp), value) TO 'raw_data_3_feb_19.csv' WITH PAGETIMEOUT=40 AND PAGESIZE=20;

but getting error as : Improper COPY command.

How can I fix this issue or is there a better way to achieve this?

from

 raw_data_field_id | dt_timestamp                    | value
-------------------+---------------------------------+-------
                23 | 2018-06-12 07:15:00.656000+0000 | 131.3

to 

 raw_data_field_id | dt_unix_timestamp_epoch         | value
-------------------+---------------------------------+-------
                23 | 1528787700656                   | 131.3
Remis Haroon - رامز
  • 3,304
  • 4
  • 34
  • 62

1 Answers1

3

The COPY command does not support adding extra functions to process the output.

I would say you have several solutions:

  • export the data in csv using COPY, convert the timestamp value (using sh commands or a high level language) and import it to a new table
  • export using echo "select raw_data_field_id, toUnixTimestamp(dt_timestamp), value from raw.raw_data;" | ccm node1 cqlsh > output.csv, change the csv so it has a proper format and import it to a new table (this solution is from here)
  • write your own conversion tool using one of Cassandra drivers (python, java etc).
  • maybe you could try something with a UDF, but I haven't tested this.

You should be aware that COPY FROM supports datasets that have less than 2 milion rows.

Horia
  • 2,942
  • 7
  • 14