Athena에서 S3 데이터 테이블 생성&쿼리
.
Data_Engineering_TIL(20200614)
[참고사항]
- study program : Fastcampus Data Engineering 온라인
** URL : https://www.fastcampus.co.kr/data_online_engineering
-
‘Spotify 음악데이터 추출, 파케이 변환 및 s3 저장’를 이어서 참고할 것
-
URL : https://minman2115.github.io/DE_TIL93
[학습내용]
- 아래와 같이 Glue에서 데이터베이스를 하나 만들어준다.
- 그리고 아래와 같은 쿼리를 아테나에서 날려서 external 테이블을 만들어본다.
CREATE EXTERNAL TABLE IF NOT EXISTS top_tracks(
id string,
artist_id string,
name string,
popularity int,
external_url string
) PARTITIONED BY (dt string)
STORED AS PARQUET LOCATION 's3://pms-bucket-test/top-tracks/' tblproperties("parquet.compress"="SNAPPY")
위에 그림과 같이 쿼리가 성공하고 좌측에 테이블이 하나 생성된 것이 확인될 것이다.
그러나 쿼리결과를 보면 “테이블에 파티션이 있으면 이러한 파티션을 로드해야 데이터를 쿼리할 수 있다”는 메세지가 보인다. 이게 무슨말이냐면 파티션이 추가로 생성이 되면 아테나 입장에서는 새로 추가가 되었는지 모르니까 사용자가 이 새로 추가된 파티션을 로드를 해줘야 한다는 말이다.
그래서 아래와 같은 쿼리를 실행하면
MSCK REPAIR TABLE top_tracks
쿼리가 성공하고 아래와 같은 쿼리결과 메세지를 보여준다.
Partitions not in metastore: top_tracks:dt=2020-06-14
Repair: Added partition to metastore top_tracks:dt=2020-06-14
글루 메타스토어에 없었던 파티션 정보를 보여주고, 리페어를 해서 새롭게 추가된 파티션 정보를 보여준 것이다.
그런 다음에 select * 쿼리를 날리면 아래 그림과 같이 결과를 확인할 수 있다.
다음과 같이 날짜 파티션을 기준으로 데이터를 가져올 수도 있다.
SELECT * FROM top_tracks WHERE CAST(dt AS date) = CURRENT_DATE LIMIT 10
또는 아래와 같이 기간을 지정해서(최근 7일치만) 가져올 수도 있음
SELECT * FROM top_tracks WHERE CAST(dt AS date) >= CURRENT_DATE - INTERVAL '7' DAY LIMIT 10
audio features 데이터도 테이블을 만들어보자.
아래 쿼리와 같이 데이터의 모든컬럼이 아닌 일부 컬럼만 가져와서 테이블을 만들수도 있다.
CREATE EXTERNAL TABLE IF NOT EXISTS audio_features(
id string,
danceability DOUBLE,
energy DOUBLE,
key int,
loudness DOUBLE,
mode int,
speechiness DOUBLE,
accusticness DOUBLE,
instrumentalness DOUBLE
) PARTITIONED BY (dt string)
STORED AS PARQUET LOCATION 's3://pms-bucket-test/audio-features/' tblproperties("parquet.compress"="SNAPPY")
마찬가지로 해당 테이블이 생성되는 것을 확인할 수 있고, MSCK REPAIR TABLE audio_features
명령어로 파티션 정보를 갱신해보자.
역시 아래와 같은 결과를 확인할 수 있다.
Partitions not in metastore: audio_features:dt=2020-06-14
Repair: Added partition to metastore audio_features:dt=2020-06-14
글루의 데이터베이스 메뉴로 가면 아래 그림과 같이 테이블이 생성된 것을 확인할 수 있다.
그리고 마찬가지로 아래와 같은 쿼리로 데이터를 조회해보자
SELECT * FROM audio_features WHERE CAST(dt AS date) = CURRENT_DATE LIMIT 10
또는 아래와 같이 피쳐의 평균값 등 조회가 가능하다.
## 오늘날짜를 기준으로 danceability, loudness 평균값 구하기
SELECT AVG(danceability), AVG(loudness) FROM audio_features WHERE CAST(dt AS date) = CURRENT_DATE
## 전체날짜를 기준으로 danceability, loudness 평균값 구하기
SELECT AVG(danceability), AVG(loudness) FROM audio_features