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 […]

Expanding array to multiple rows – Athena

Posted 1 CommentPosted in AWS, AWS Athena

A single row in Athena table is stored as — select id, course, date from demo.course_tab where id=’1234567892′ id course date 1234567892 [95c3c1bc5873, 2e345b2eb678, 027b02599f4a, 8695a580520b, 5d453355d415, cdcc7682070b] 2019-06-13 The datatype for course column is array(string). Now, how can you get the output in below format – id course date 1 1234567892 95c3c1bc5873 2019-06-13 2 […]