topic Re: COPY INTO command can not recognise MAP type value from JSON file in Data Engineering

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

Anonymous — Thu, 21 Apr 2022 09:27:24 GMT

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?

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

Hubert-Dudek — Thu, 21 Apr 2022 10:12:50 GMT

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

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

Anonymous — Thu, 21 Apr 2022 10:42:19 GMT

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?

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

Hubert-Dudek — Thu, 21 Apr 2022 10:51:39 GMT

@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.

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

Anonymous — Mon, 25 Apr 2022 08:16:00 GMT

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.

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

User16763506477 — Tue, 02 Aug 2022 05:45:55 GMT

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

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

jose_gonzalez — Mon, 29 Aug 2022 22:57:22 GMT

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.

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

Y-I — Sun, 05 Oct 2025 08:50:50 GMT

Use

from_json(to_json({struct column}),{your schema definition})

For example

COPY INTO {table} FROM (select from_json(to_json()), 'MAP<STRING, STRING>' FROM {path}) ...