Athena에서 S3 데이터 테이블 생성&쿼리

2020-06-14

.

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에서 데이터베이스를 하나 만들어준다.

3

  • 그리고 아래와 같은 쿼리를 아테나에서 날려서 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")

image

위에 그림과 같이 쿼리가 성공하고 좌측에 테이블이 하나 생성된 것이 확인될 것이다.

그러나 쿼리결과를 보면 “테이블에 파티션이 있으면 이러한 파티션을 로드해야 데이터를 쿼리할 수 있다”는 메세지가 보인다. 이게 무슨말이냐면 파티션이 추가로 생성이 되면 아테나 입장에서는 새로 추가가 되었는지 모르니까 사용자가 이 새로 추가된 파티션을 로드를 해줘야 한다는 말이다.

그래서 아래와 같은 쿼리를 실행하면

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 * 쿼리를 날리면 아래 그림과 같이 결과를 확인할 수 있다.

image

다음과 같이 날짜 파티션을 기준으로 데이터를 가져올 수도 있다.

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

image

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

글루의 데이터베이스 메뉴로 가면 아래 그림과 같이 테이블이 생성된 것을 확인할 수 있다.

image

그리고 마찬가지로 아래와 같은 쿼리로 데이터를 조회해보자

SELECT * FROM audio_features WHERE CAST(dt AS date) = CURRENT_DATE LIMIT 10

image

또는 아래와 같이 피쳐의 평균값 등 조회가 가능하다.

## 오늘날짜를 기준으로 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

image