cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
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.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!