cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Anonymous
Not applicable

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?

7 REPLIES 7

Hubert-Dudek
Esteemed Contributor III

@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"}}:

image.png 

Anonymous
Not applicable

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
Esteemed Contributor III

@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
Community Manager
Community Manager

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

Anonymous
Not applicable

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
Contributor III

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

jose_gonzalez
Moderator
Moderator

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.

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.