unable to import data from CSV to Cassandra db using cqlsh

Question

I can export the data in CSV format using the COPY command, however, when I try to import the data am see the following error :

COPY loggtest.testdclog from 'G:\logtest\diagnosticlog_table.csv' with header=true;                                                                                                             
Bad Request: unable to coerce '2019-10-14 03:38:30AUS Eastern Daylight Time' to a  formatted date (long)
          Aborting import at record #0 (line 1). Previously-inserted values still present.
0 rows imported in 0.047 seconds.  

Can you let me know if anything missing?

is there any other way to copy data from one Windows server to another Windows server

Answer 1

It is not very clear what version of Cassandra (C*) you're using nor what is the step that failed.

Could you provide the below?

• Version of C*

• Exact COPY command and console output

• Any other system.log related info/error messages

• OSS C* documentation on the cassandra.apache.org is here

• more examples here has more examples on the usage.

---

There is also a free open-source tool that is called DataStax Bulk Loader (aka DSBulk) which can seamlessly perform load/unload/count operations. Check out the below references,

These multi-part DSBulk blog series should help you get going:

• DSBulk intro - https://www.datastax.com/blog/introducing-datastax-bulk-loader
• Intro to DSBulk and Loading - https://www.datastax.com/blog/datastax-bulk-loader-introduction-and-loading
• More Loading - https://www.datastax.com/blog/datastax-bulk-loader-more-loading
• Common settings - https://www.datastax.com/blog/datastax-bulk-loader-common-settings
• Unloading - https://www.datastax.com/blog/datastax-bulk-loader-unloading
• Counting - https://www.datastax.com/blog/datastax-bulk-loader-counting Loading from other locations - https://www.datastax.com/blog/datastax-bulk-loader-examples-loading-other-locations

And you'll do it something like,

~/dsbulk-1.11.0/bin/dsbulk load -k loggtest -t testdclog -url 'G:\logtest\diagnosticlog_table.csv' -header false

Answer 2

So the timestamp format is the issue here:

Bad Request: unable to coerce '2019-10-14 03:38:30AUS Eastern Daylight Time'

Importing with CQLSH requires timestamps to be in this format:

2024-01-06 11:12:09.971-0600

In your case, you should do a search/replace in the diagnosticlog_table.csv file so that the times are formatted like this:

2019-10-14 03:38:30+1100

From a quick Google search, the UTC offset for Eastern Australian Daylight Time was +11. Please adjust if that's not correct.