Search code examples
cratecratedb

Crate.io Copy From 0 rows affected

I have a Crate.io database, running CrateDB version 3.2.7, under Windows Server 2012. (I know it's not the best, it's only for testing purposes right now and will not be the final setup.)

I have create the table dbo.snapshots enter image description here

I exported the data from SQL Server to a CSV file via the BCP command.

bcp DatabaseName.dbo.Snapshots out F:\Path\dbo_OldSnapshots.dat -S ServerName -U UserName -P Password -a65535 -c -C 65001 -t ,

Then I tried to import the data into CrateDb with the "COPY FROM" command.

COPY dbo.snapshots FROM 'file:///F:/Path/dbo_OldSnapshots.dat';

The file is about 11go big. I know it found the file, as I could see the I/O on the drive in Task Manager.

It ran for about 13 minutes, and then said "0 rows affected". I have no idea why it didn't work, I didn't get any errors.

enter image description here Any idea what I can do to make it work?

********************************* EDITED ADDED ADDITIONAL INFO ****************************
Ok, so I've found out that you can specify the "RETURN SUMMARY" clause at the end of the COPY command. I tested it with a smaller file.

With that, I got an error that says that the primary key cannot be NULL. I know that it's not NULL in the data that I extract, so I'm gonna have to find out why it says that my primary key is NULL.

So I changed the BCP delimiter to be a comma, since the CSV file for CrateDB must be comma separated, and I manually edited the file to add the column headers since CrateDB asks for a header.

I also edited the file in Notepadd++ to save it in UTF-8 encoding, to make sure it was the right encoding.

But even with all that, I still get the error saying that the primary key value must not be NULL.

enter image description here

Solution

  • Ok so I managed to make it work. Here is what you need to check if you try to Export data from SQL or another dbms to CrateDB :

    - File encoding in UTF-8

    - Comma separated file

    - The first line needs to be a header with all the columns be carefull, the names are case sensitive so if the column is "MyColumn" in SQL Server, but "mycolumn" in CrateDB, it must be in lower case in the header or else CrateDb won't be able to find it correctly

    - If you have DateTime types, it must be between "" in the file (ex: 1,0,"2019-05-10T16:40:00",0,0)

    - If you have DateTime types, please note that you need to have T between the date and time part. So "2019-05-10T16:40:00" instead of "2019-05-10 16:40:00"

    With all of that in check, I was able to import a sample data in my CrateDB database.