Failiure joining S3 csv file with mysql table

1

Internal error: Cannot find common type for join keys $0 (type INTEGER) and $0 (type VARCHAR(65536))
Following query which is for S3 join with a mysql table fails because of datatype. Should I have to force convert the S3 datatype?

SELECT *
FROM “src_01”.“dwh-dstest-hautelook-net”.dremtest.“test_table_01.csv” c
join src_02.test.test_table_01 b on c.item_id = b.item_id
where c.item_id = 51551446

2

So, after conversion it works. But, is there any auto conversion or type setting that I can do for S3 files? or Any schema detection/menu available?
SELECT *
FROM “src_01”.“dwh-dstest-hautelook-net”.dremtest.“test_table_01.csv” c
join src_02.test.test_table_01 b on CAST(c.item_id AS integer) = b.item_id
where c.item_id = 51551446

3

@HLNA we don’t yet support type detection for CSV files, but it’s on the roadmap. For now, you can:

  • Navigate to that file (or set of files) in Dremio UI.
  • Covert the data types once and save as a virtual dataset by clicking “Save As”
  • Query that virtual dataset instead. This will apply all transformations on the fly without you having to specify them in your query.
4

@can Sure, understand. But, in prod scenario we’re expecting lots of file and are we suppose to do this for each file/table-file?

Anyhow, I am finding it difficult to do it manually rather than doing it through rest api calls.

The rest API page that I looked does not have enough information/example for virtual dataset conversion… (I think I did not even find an example on adding a source ).

Will be helpful any direction on resolving the virtual dataset and any programming interface (or) rest api references. Appreciate!

5

You can do the conversion for a directory of files as a single operation instead of one file at a time, see the details here: https://docs.dremio.com/data-sources/files-and-directories.html