해색주의 조용한 공간

 저번 교육때 찾아서 본 책인데 이제서야 시간 날때 보게 되었다. 데이터 분석을 위한 SQL 레시피라는 책인데, 안에 내용도 상당히 좋고 내가 예저에 고민했던 로그 데이터 엔지니어링 관련된 부분이기도 하고 말이지. 문제는 이 책이나 무척이나 불친절하고 실습할 수 있는 환경도 없다는 점이었다. 

 아, 그래서 무료로 Hive Query 공부를 사용했던 사이트를 추천한다.

https://demo.gethue.com/hue/home 

 위의 사이트는 무료이나, 중간중간에 세션이 자주 끊긴다. Visual Code에 코드를 저장하고 여기에서는 실행만 하는 것을 권장합니다. 오라클의 경우 https://livesql.oracle.com/apex/f?p=590:1000 사이트가 있습니다.

 책에서는 Postgre SQL을 비롯한 다양한 SQL을 지원하지만, 제가 실무적으로 많이 사용했던 것은 Hive와 Impala이므로 여기서는 기본적으로 Hive를 기준으로 쿼리를 준비하도록 하겠습니다. 참고로 위의 Hue 사이트의 ID와 Password는 화면에 지원되므로 바로 로그인해서 쿼리 작성이 가능합니다. 책에서는 별도의 쿼리를 제공하지 않으므로 제가 아는 범위 내에서 작성해서 공유하도록 하겠습니다.

01. 로그 데이터 테이블을 생성합니다.

02. 일단 데이터가 제대로 들어갔는지 봐야겠죠?

 데이터가 생각했던데로 들어갔다는 것을 확인이 가능하다.

03. 로그 데이터에서 호스트를 분리해 보자. 

 04. 그러면 이번에는 path와 id를 나눠봅시다.

 오라클에서 할때에는 정규표현식 작성하느라 무척이나 고생했는데, 여기서는 간단하게 함수 하나로 끝나기 때문에 무척이나 편리합니다.

 이전 회사에서는 데이터 엔지니어링 도움을 받을 수 없었기 때문에 분석을 하거나 모델을 만들기 위해서는 개인이 열심히 쿼리를 공부해서 데이터를 정제하고 이를 기반으로 보고서를 만들거나 분석툴을 이용해서 다른 작업을 해야 했습니다. 그러나 새로운 회사에서는 기본적인 쿼리를 작성하고 데이터 엔지니어링 팀을 지원을 요청하면 상당히 많은 부분 도움을 받을 수 있었습니다. 실제로 업무 이외에 이러한 기술 분석을 하기 위한 환경과 데이터가 충분히 존재한다는 것을 감사하고 있다.

 시간 날때마다 쿼리 작성하고 결과 파일 정리해서 올리도록 하겠습니다. Visual Code를 복사해서 붙여넣기 하면 저렇게 예쁘게 나오는 구나. 이런 자료가 다른 사람에게도 도움이 되겠지만, 아마 가장 많은 도움이 되는 것은 내가 아닐까 싶다.

-- 1번. 테이블 삭제

DROP TABLE IF EXISTS KJPARK.FILE_TBL;

-- 2번. '/user/kjpark/file'에 읽어들일 csv 파일을 저장해 놓는다.(단 헤더는 삭제한다.)

-- 3번. 테이블 적재

CREATE TABLE KJPARK.FILE_TBL (COMP_ID string,

                                                             COMP_TYPE string,

COMP_TITLE string,

CUST_TYPE string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

STORED AS TEXTFILE

LOCATION '/user/32200157/file/';

-- 4번. 새로 고침

INVALIDATE METADATA KJPARK.FILE_TBL;

-- 5번. 자료 확인

SELECT *

FROM KJPARK.FILE_TBL

LIMIT 10;

 실제 업무에서 CONNECT BY LEVEL을 쓸 기회가 별로 없는데, 경우의 수를 생성하는데 쓰인다는 것을 오늘 알았다.

덕분에 큰 힘들이지 않고 업무를 빠르게 마무리할 수 있었네.

https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?mod=document&uid=236560

 일련번호로 테이블을 생성하는 방법 가운데 Oracle connect by라는 것이 있다는 것을 알았다.

'YYYYMM' 형태의 월별 자료가 쌓여 있는 테이블에서 자료를 가져오는 가장 빠른 방법이다. 일단은 SUBSTR() 함수는 이런 경우 절대로 피해야 한다는 것을 깨닫게 되기는 했다만.

• 오라클 순위 채번 : https://meyouus.tistory.com/175

• EXISTS 함수를 사용하는 방법 : https://gent.tistory.com/278

 일련번호로 테이블을 생성하는 방법 가운데 Oracle connect by라는 것이 있다는 것을 알았다.

'YYYYMM' 형태의 월별 자료가 쌓여 있는 테이블에서 자료를 가져오는 가장 빠른 방법이다. 일단은 SUBSTR() 함수는 이런 경우 절대로 피해야 한다는 것을 깨닫게 되기는 했다만.

• 오라클 순위 채번 : https://meyouus.tistory.com/175

• EXISTS 함수를 사용하는 방법 : https://gent.tistory.com/278

 PySpark을 못쓰는 상황에서 대규모 데이터는 Impala를 통해서 하둡에서 가져와야 하고, 파이썬에서는 가져온 데이터의 기본적인 확인 및 모델링을 주로 하게 된다. 따라서 임팔라에서 데이터를 랜덤하게 가져와야 하는 상황이라면 해당 쿼리를 짜야 하게 되어서 생각하게 된게, 쿼리를 동적으로 돌리되 왠만한 데이터 생성은 모두 임팔라로 한다였다. 

 1. 모델 생성할 대상군의 샘플링을 모두 임팔라로 진행한다. 여기서는 기본 고객키만을 이용해서 생성하므로 오래 걸리지는 않을 것이다. : 쿼리는 임팔라로 이를 반복 실행하는 것은 파이썬으로 짠다.

 2. 생성된 고객키를 가지고 실제 고객모델링할 데이터를 생성한다. : 1번에서 만들어 놓은 고객키를 기반으로 실제 데이터를 주제별로 생성해서 갖고 있는다.

select * from 
(
  select  
  row_number() over (partition by country order by country , random()) rn,
  count() over (partition by country order by country) cntpartition,
  tab.*
  from   dat.mytable tab
)rs
where rs.rn between 1 and cntpartition* 1/100  -- This is for 1% data

https://stackoverflow.com/questions/68259133/randomly-sampling-n-rows-in-impala-using-random-or-tablesample-system