How to create simple Athena table from simple JSON data?

0

I have simple JSON log data like:

{ 
  "referer":"https://example.org",
  "data": {
    "datum1": "value1",
    "datum2":"value2",
    "ts":2381738917239
  }
}

How do I get that stuff easily in the Athena so I can type:
select * from mytable where data.ts > '2022-06-01 and datum1 = 'filter'

The documention is really comprehensive (spent a couple of hours browsing it), but I am really not that interesting about knowing all the SerDe details, I just need to get this simple thing going and preferrably fast. Like in Snowflake (took a minute or two to do it).

BR, Joni

J_N__
已提問 1 年前檢視次數 254 次
1 個回答
1

I was able to get a crawler to crawl the data and write a quick query in Athena to be able to accomplish your use case effectively. Please take a look and try it out. If you have any questions, please let me know.

Step 1: I mocked up data for 10 different dates and moved the data in S3 Enter image description here Step 2: I created a crawler to crawl the S3 location and ran the crawler. It created a table with 2 columns referer and data. Step 3: I ran the athena query below to get the dates > 9/4/2022

SELECT referer, data.datum2, data.ts, data.datum1, date_format(from_unixtime(data.ts),'%Y-%m-%d') FROM "raw_db"."nested_json" 
where date_format(from_unixtime(data.ts),'%Y-%m-%d') > '2022-09-04'
limit 10;

Enter image description here

profile pictureAWS
已回答 1 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南