[HIVE] Airflow / Hive를 이용한 데이터 처리

2023.06.02 (Fri) 학습정리
#Hadoop #Hive #AWS #HQL

1. Hive 를 이용한 데이터 처리

1-1 .활용 데이터

서울시 열린데이터 광장의 '서울시 부동산 실거래가 정보' 데이터를 활용하여 서초구 내 실거래가 요약 정보, 건물별 정보를 추출하는 pipeline을 생성

🔗 서울시 열린데이터 광장 '서울시 부동산 실거래가 정보'

열린데이터광장 메인

 

1-2. pipeline

- aws s3에서 데이터를 local에 저장
- hive에 해당 데이터가 존재하면 skip, 존재하지 않으면 전송
- 해당 데이터를 seoul table로 생성
- seoul table에서 서초구 정보 추출 후 조회

 

1-3. hql 문 

📌 seoul.hql > 서울시 부동산 실거래자 정보 테이블 생성

RAW 데이터에서 연도, 자치구코드, 자치구명, 법정동코드, 법정동명, 건물명, 계약일, 물건금액(만원), 건물용도, 주소. 평수만 추출 및 기타 전처리 후 활용

CREATE EXTERNAL TABLE IF NOT EXISTS tb_seoul(
        idx INT,
        year INT,
        guCode INT,
        guNm STRING,
        dongCode INT,
        dongNm STRING,
        houseNm STRING,
        contractDate DATE,
        amount INT,
        houseType STRING,
        address STRING,
        area INT)
        ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
        STORED AS TEXTFILE
        LOCATION '/sub/hive/seoul/' --해당 경로에 있는 파일 가져오기
        tblproperties ("skip.header.line.count"="1");

📌 seocho_desc.hql > 서초구 요약 정보 테이블 생성 : 2023년도 서초구 내 건물유형별 실거래가 및 면적 평균

CREATE TABLE IF NOT EXISTS seocho_describe_2023 AS
SELECT
        guNm,
        dongNm,
        houseType,
        ROUND(AVG(amount),2) amount,
        ROUND(AVG(area),2) area
        FROM tb_seoul
        WHERE guNm = '서초구' AND year = 2023
        GROUP BY guNm, dongNm, houseType
        ORDER BY guNm, dongNm, houseType;

📌 seocho_desc.hql > 서초구 건물유형별 상세 테이블 생성 : 2023년도 서초구 건물유형 내 건물별 실거래가 및 면적 정보

-- 서초구 아파트 정보
CREATE TABLE IF NOT EXISTS seocho_apt_2023 AS
SELECT
        guNm,
        dongNm,
        houseType,
        houseNm,
        address,
        AVG(amount) avg_amount,
        MAX(amount) max_amount,
        MIN(amount) min_amount,
        AVG(area) avg_area
        FROM tb_seoul
        WHERE year = 2023 and guNm = '서초구' and houseType = '아파트'
        GROUP BY guNm, dongNm,houseType, address, houseNm
        ORDER BY avg_amount desc;

-- 서초구 오피스텔 정보
CREATE TABLE IF NOT EXISTS seocho_effapt_2023 AS
SELECT
        guNm,
        dongNm,
        houseType,
        houseNm,
        address,
        AVG(amount) avg_amount,
        MAX(amount) max_amount,
        MIN(amount) min_amount,
        AVG(area) avg_area
        FROM tb_seoul
        WHERE guNm = '서초구' AND year = 2023 AND houseType = '오피스텔'
        GROUP BY guNm, dongNm,houseType, address, houseNm
        ORDER BY avg_amount desc;
   
-- 서초구 연립다세대 정보
CREATE TABLE IF NOT EXISTS seocho_mul_2023 AS
SELECT
        guNm,
        dongNm,
        houseType,
        houseNm,
        address,
        AVG(amount) avg_amount,
        MAX(amount) max_amount,
        MIN(amount) min_amount,
        AVG(area) avg_area
        FROM tb_seoul
        WHERE guNm = '서초구' AND year = 2023 AND houseType = '연립다세대'
        GROUP BY guNm, dongNm,houseType, address, houseNm
        ORDER BY houseType, avg_amount desc;

-- 서초구 단독다가구 정보
CREATE TABLE IF NOT EXISTS seocho_det_2023 AS
SELECT
        guNm,
        dongNm,
        houseType,
        houseNm,
        address,
        AVG(amount) avg_amount,
        MAX(amount) max_amount,
        MIN(amount) min_amount,
        AVG(area) avg_area
        FROM tb_seoul
        WHERE guNm = '서초구' AND year = 2023 AND houseType = '단독다가구'
        GROUP BY guNm, dongNm,houseType, address, houseNm
        ORDER BY houseType, avg_amount desc;

📌 show_seocho_tb.hql > 전체 테이블 내 10건 출력

SELECT * FROM seocho_describe_2023 limit 10;
SELECT * FROM seocho_apt_2023 limit 10;
SELECT * FROM seocho_effapt_2023 limit 10;
SELECT * FROM seocho_mul_2023 limit 10;
SELECT * FROM seocho_det_2023 limit 10;

 

1-4. 출력 결과

조회 쿼리 결과 예시

 

2. 보완점 

• 자치구 정보를 input 값으로 받아서 해당 구의 정보를 테이블로 만들고 출력할 수 있나?
  • airflow UI에서 input 받고 dag로 전달 → input 값을 hql로 전달
• local을 거치지 않고 aws s3 → hadoop 으로 바로 보내기 (시도했지만 계속 오류 발생 😥)