COPY FROM does not work on all files inside folder about crate HOT 10 OPEN

scala/scala3#10761 (comment)

from crate.

Hi @jphjsoares, thanks for reporting! Looks like Windows specific bug.

from crate.

I forgot to mention it, but I'm also unable to copy all the data from one table to the other by using the good and old: INSERT INTO table2 (SELECT * FROM table1). The command will copy the shards, but Crate reports 0 total records. Does that also have to do with this bug or should I create a separate issue?

from crate.

I forgot to mention it, but I'm also unable to copy all the data from one table to the other by using the good and old: INSERT INTO table2 (SELECT * FROM table1). The command will copy the shards, but Crate reports 0 total records. Does that also have to do with this bug or should I create a separate issue?

Yes please, with details of SHOW CREATE TABLE for both tables and any error you get in the logs.
Keep in mind, that before opening a concrete github issue, you can also use our community portal: https://community.cratedb.com/

Keep in mind we have an open issue to address this scenario of INSERT INTO ... SELECT where no error is returned, just number of inserted rows: #12218

from crate.

Sure, here it is:
You can see that the only difference is in the shards. It was an attempt to migrate to a table with less shards.
The query is executed with success, but 0 records are actually copied. Only shards.

CREATE TABLE IF NOT EXISTS "my_schema"."test" (
   "test_int" INTEGER,
   "test_string" TEXT,
   "timestamp" TIMESTAMP WITH TIME ZONE,
   "edate" TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('week', "timestamp")
)
CLUSTERED INTO 4 SHARDS
PARTITIONED BY ("timestamp")
WITH (
   "allocation.max_retries" = 5,
   "blocks.metadata" = false,
   "blocks.read" = false,
   "blocks.read_only" = false,
   "blocks.read_only_allow_delete" = false,
   "blocks.write" = false,
   codec = 'default',
   column_policy = 'strict',
   "mapping.total_fields.limit" = 1000,
   max_ngram_diff = 1,
   max_shingle_diff = 3,
   number_of_replicas = '0-1',
   "routing.allocation.enable" = 'all',
   "routing.allocation.total_shards_per_node" = -1,
   "store.type" = 'fs',
   "translog.durability" = 'REQUEST',
   "translog.flush_threshold_size" = 536870912,
   "translog.sync_interval" = 5000,
   "unassigned.node_left.delayed_timeout" = 60000,
   "write.wait_for_active_shards" = '1'
)

CREATE TABLE IF NOT EXISTS "my_schema"."test2" (
   "test_int" INTEGER,
   "test_string" TEXT,
   "timestamp" TIMESTAMP WITH TIME ZONE,
   "edate" TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('week', "timestamp")
)
CLUSTERED INTO 1 SHARDS
PARTITIONED BY ("timestamp")
WITH (
   "allocation.max_retries" = 5,
   "blocks.metadata" = false,
   "blocks.read" = false,
   "blocks.read_only" = false,
   "blocks.read_only_allow_delete" = false,
   "blocks.write" = false,
   codec = 'default',
   column_policy = 'strict',
   "mapping.total_fields.limit" = 1000,
   max_ngram_diff = 1,
   max_shingle_diff = 3,
   number_of_replicas = '0-1',
   "routing.allocation.enable" = 'all',
   "routing.allocation.total_shards_per_node" = -1,
   "store.type" = 'fs',
   "translog.durability" = 'REQUEST',
   "translog.flush_threshold_size" = 536870912,
   "translog.sync_interval" = 5000,
   "unassigned.node_left.delayed_timeout" = 60000,
   "write.wait_for_active_shards" = '1'
)

from crate.

@jphjsoares thanks for the additional information.

Since it's not related to COPY FROM, please create a new issue with the table schemas you posted above. Also, please post there a result of select count(*) from my_schema.test2

Asking because not sure what you mean by

The command will copy the shards, but Crate reports 0 total records

So you are saying that INSERT works but reports incorrect number of inserted records or you meant something different? In any case, please elaborate in the new dedicated issue.

Thanks!

from crate.

Just created it here, hopefully I was able to explain it a little bit better there 😄

from crate.

Hi @jphjsoares, I could reproduce on a Windows machine but the problem can be resolved by applying a tip from the docs

Tip
If you are using Microsoft Windows, you must include the drive letter in the file URI.
For example:
file://C:/tmp/import_data/quotes.json

copy quotes from 'file:///C:/full/path/te*.json' ; worked for me locally. Could you please try it?

from crate.

ftr: We do have tests covering such scenario and we even used to run Windows tests on CI in the past.
Tests passes on Windows since we don't use path as a string but compute it like getResource("test_file").toURI()... and this gets resolved to a correct path with drive, as hinted in docs.

from crate.