04-21-2022 02:27 AM
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?
04-21-2022 03:12 AM
04-21-2022 03:42 AM
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?
04-21-2022 03:51 AM
@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.
04-25-2022 01:10 AM
Hi @Alexey Murashev , Please let us know if @Hubert Dudek 's answer helps, or we'll find another answer for you.
04-25-2022 01:16 AM
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.
08-01-2022 10:45 PM
@Alexey Murashev Could you please paste create table (DDL) statement for the hive table?
08-29-2022 03:57 PM
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 a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group