To convert JSON data to Athena data types, use CAST.
Note
In this example, to denote strings as JSON-encoded, start with the
JSON keyword and use single quotes, such as JSON
'12345'
WITH dataset AS (
SELECT
CAST(JSON '"HELLO ATHENA"' AS VARCHAR) AS hello_msg,
CAST(JSON '12345' AS INTEGER) AS some_int,
CAST(JSON '{"a":1,"b":2}' AS MAP(VARCHAR, INTEGER)) AS some_map
)
SELECT * FROM datasetThis query returns:
+-------------------------------------+
| hello_msg | some_int | some_map |
+-------------------------------------+
| HELLO ATHENA | 12345 | {a:1,b:2} |
+-------------------------------------+