AWS Athena – DML Queries

Posted Leave a commentPosted in AWS, AWS Athena

You can learn something new everyday, and today I learned that AWS Athena supports INSERT INTO queries. Lets create table based on marvel_superheroes using CTAS command – Creating the table partition based on “year” failed with : HIVE_COLUMN_ORDER_MISMATCH: Partition keys must be the last columns in the table and in the same order as the […]

Athena: Extracting data from JSON

Posted Leave a commentPosted in AWS, AWS Athena

Suppose you have a table in Athena and its column contain JSON data. How can you extract the individual keys? In the example, the table has column “fixedproperties” which contain JSON data – How can you display the data is below format? select json_extract(fixedproperties, ‘$.objectId’) as object_id, json_extract(fixedproperties, ‘$.custId’) as cust_id, json_extract(fixedproperties, ‘$.score’) as score […]