COPY INTO command can not recognise MAP type value from JSON file

[Anonymous]

I have a delta table in Databricks with single column of type map<string, string> and I have a data file in JSON format created by Hive 3 for the table with thecolumn of same type. And I want to load data from file to Databricks's table using COPY INTO command.

I tried this command

COPY INTO db1.table1 FROM (SELECT CAST(map1 AS map<string,string>) AS map1 FROM '/') FILEFORMAT = JSON FILES = ('/mnt/external/repl_test/db1/table1/000000_0')  COPY_OPTIONS ('force'='true');

But got an error

Error in SQL statement: AnalysisException: cannot resolve '`map1`' due to data type mismatch: cannot cast struct<key:string> to map<string,string>; line 1 pos 34;
'Project [cast(map1#6876 as map<string,string>) AS map1#6856]
+- Relation[map1#6876] json

JSON file has the following content

{"map1":{"key":"value"}}

it was generated while inserting data to Hive3 table stored with SerDe org.apache.hadoop.hive.serde2.JsonSerDe.

Is there a proper way to load json file with maps to the delta table?

[Hubert-Dudek]

@Alexey Murashev​ , That struct<key: string> is just a nested column. It is nothing in common with the map, so you can not cast it. Look for the below examples with {"map1":{"key":"value"}}:

 

[Anonymous]

Thanks for the answer!

In my case I just want to migrate data from Hive table to Databricks delta table. Both tables have the same schema with column of type map<string,string>. So I just not understand is there straight and proper way to migrate data using COPY INTO command in case of source table stores data in JSON format?

[Hubert-Dudek]

@Alexey Murashev​  COPY INTO is ok just you need to make some transformation of struct<key:string> as cast will not work in such a case.

[Kaniz]

Hi @Alexey Murashev​ , Please let us know if @Hubert Dudek​ 's answer helps, or we'll find another answer for you.

[Anonymous]

Hi @Kaniz Fatma​ , actually I didn't find the solution for this moment. I also limited to use only SQL in my query, so I can't use Scala or Python.

[User16763506477]

@Alexey Murashev​ Could you please paste create table (DDL) statement for the hive table?

[jose_gonzalez]

Hi Alexey,

Just a friendly follow-up. Did any of the responses help you to resolve your question? if it did, please mark it as best. Otherwise, please let us know if you still need help.