Convert Map in QuickSight

Question

I have a column of type Map string->string in Athena and this is not recognized in AWS QuickSight. I am trying to convert this field to varchar in QuickSight using SQL

SELECT cast(body as varchar) FROM db.events;

But it fails

Cannot cast map(varchar,varchar) to varchar

How can I convert this field correctly so QuickSight can query against it?

I am afraid there is no easy way to convert map to string. Map can have multiple entries. — Dhaval, Oct 20 '17 at 15:59

score 0 · Answer 1 · answered Feb 08 '22 at 17:52

I think there is is no easy way to do that, but maybe there are some workarounds.

If each map has two keys with known names you can create two new columns:

SELECT 
    ELEMENT_AT(map_col,'key1') AS key1_col
    ,ELEMENT_AT(map_col,'key2') AS key2_col
FROM
    (
    SELECT
        MAP(
            ARRAY['key1','key2'], 
            ARRAY['val1','val2']
            ) AS map_col
    )

Which will output:

key1_col	key2_col
val1	val2

If your map column has just one key you can adapt the snippet above and use it or use this one:

SELECT 
    ARRAY_JOIN(MAP_KEYS(map_col), ', ') AS keys
    ,ARRAY_JOIN(MAP_VALUES(map_col), ', ') AS vals
FROM
    (
    SELECT
        MAP(
            ARRAY['key1'], 
            ARRAY['val1']
            ) AS map_col
    )

which will result in:

keys	vals
key1	val1

As said above, there is no correct way, if you have many keys you can try to use the second snippet to create strings to store keys and values, and later use calculated fields (maybe using split) to access them.

Hope it helps (:

Convert Map in QuickSight

1 Answers1