How to create table with nested map on databricks using sql

user_12004151 0
Viewed 696

I'm trying to replicate this given schema:

+---------------+-----------------------------------------------------------------------+
| column        | type                                                                  |
+---------------+-----------------------------------------------------------------------+
| first_name    | STRING                                                                |
| last_name     | STRING                                                                |
| subscriptions | MAP<STRING, MAP <titles:ARRAY<STRING>, payment_methods:ARRAY<STRING>> |

With this statement:

CREATE TABLE IF NOT EXISTS mydb.map1 (
  first_name STRING,
  last_name STRING,
  subscriptions MAP < MAP < STRING, ARRAY<STRING> >,  MAP < STRING, ARRAY<STRING> >> 
)

I manage to write this schema. But honestly, I'm not 100% sure if it reflects the gives schema. What me confuses so much is this part: "MAP titles:ARRAY<STRING" What is "titles:"? This seems to be the key?

+---------------+----------------------------------------------------------+ 
| col_name      | data_type                                                | 
+---------------+----------------------------------------------------------+
| first_name    | string                                                   | 
| last_name     | string                                                   | 
| subscriptions | map<map<string,array<string>>,map<string,array<string>>> |

# Partitioning
Not partitioned

So I try to test it with this insert statement:

%sql
INSERT INTO
  mydb.map1
VALUES  
  ('String1', 'String2', Map( Map('titles', Array('S1', 'S2', 'S3')), Map('payment_methods', Array('S1', 'S2', 'S3'))))

But this just gives me this error statement and here I'm stuck:

Error in SQL statement: AnalysisException: cannot resolve 'map(map('titles', array('S1', 'S2', 'S3')), map('payment_methods', array('S1', 'S2', 'S3')))' due to data type mismatch: The key of map cannot be/contain map.; line 9 pos 2;

How do I have to write the CREATE statement to get the above given schema?

databricks apache-spark-sql apache-spark arrays sql
1 Answers

Frankly speaking, your create table isn't completely correct. I don't understand how the definition of the subscriptions field MAP<STRING, MAP <titles:ARRAY<STRING>, payment_methods:ARRAY<STRING>> could have named fields in the map - map by definition supports arbitrary keys, not only specific ones.

If you want to achieve that, then it's better to use struct as value of the map, try following:

subscriptions MAP<STRING, STRUCT<titles:ARRAY<STRING>, payment_methods:ARRAY<STRING>>>

The real problem is that you declared a map where keys & values are both maps. If you want still to use maps, then you need to declare it as following:

subscriptions MAP<STRING, MAP<STRING, ARRAY<STRING>>>
Alex_Ott_186270
Related