########################
# #
# mysql DB V5.7 #
# #
########################
1. 쿼리 로그 확인
출처: https://extbrain.tistory.com/98 [확장형 뇌 저장소:티스토리]
프로그램에서 사용한 MySQL 쿼리를 확인하고 싶을 때 사용하는 방법입니다.
아래의 방법은 일시적으로 조회할 수 있게 해주는 방법입니다.
영구적으로 적용하기 위해서는 설정 파일을 변경해야하고, 좋지 않기 때문에 기록하지 않았습니다.
Step.1 로그 입력 방식 확인
SELECT @@GLOBAL.log_output;
보통 FILE로 설정되어 있습니다.
Step.2 General Log 사용 여부 확인
SELECT @@GLOBAL.general_log;
보통 0 또는 OFF 로 설정되어 있습니다.
Step.3 로그 입력 방식 변경
SET GLOBAL log_output = 'table';
로그를 테이블에 입력하도록 변경합니다.
Step.4 General Log 활성화
SET GLOBAL general_log = 1;
1 또는 ON 으로 설정을 변경합니다.
Step.5 조회
SELECT * FROM mysql.general_log;
########################
# #
# MARIA DB V5.5 #
# #
########################
1. 버전확인
SELECT version()
2. 유닉스 타임 변환
FROM_UNIXTIME(유닉스타임컬럼)
3. 타임 -> 문자 변환 ( 오라클의 TO_CHAR )
DATE_FORMAT( 시간컬럼 , '포맷' )
DATE_FORMAT(B.TRANSIENT_LASTDDLTIME , '%Y-%m-%d %T' )
참조 : https://forgiveall.tistory.com/129
https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=sjh7622&logNo=221098264254
4. 딕셔너리 조회
/* 테이블 목록 조회 */
SELECT TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
, ORDINAL_POSITION
, COLUMN_DEFAULT
, IS_NULLABLE
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, CHARACTER_OCTET_LENGTH
, NUMERIC_PRECISION
, NUMERIC_SCALE
, DATETIME_PRECISION
, CHARACTER_SET_NAME
, COLLATION_NAME
, COLUMN_TYPE
, COLUMN_KEY
, EXTRA
, `PRIVILEGES`
, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE 1=1
AND TABLE_SCHEMA IN ('hive', 'hue', 'oz')
ORDER BY TABLE_SCHEMA
, TABLE_NAME
;
########################
# #
# MARIA DB V10 #
# #
########################
# 1. 데이터 베이스 조회
SHOW DATABASES
;
# 2. 조회된 데이터 베이스에서 사용할 db 선택
USE dm
;
# 3. 데이터베이스의 테이블 목록 조회
SHOW TABLES
;
# VERSION 체크
SELECT VERSION()
########################
# #
# 파이썬 데이터분석 #
# #
########################
/* 데이터 프로파일링 */
https://wikidocs.net/47193
df.describe()
/* 달력 */
SELECT TO_CHAR(SOCAL_DATE , 'YYYYMM') AS STD_YM
FROM ORD_SOLU_M
WHERE TO_CHAR(SOCAL_DATE , 'YYYYMM') >= '201701'
AND TO_CHAR(SOCAL_DATE , 'YYYYMM') < '202301'
GROUP BY TO_CHAR(SOCAL_DATE , 'YYYYMM')
ORDER BY TO_CHAR(SOCAL_DATE , 'YYYYMM')
;
과장님!
아까 말씀하신 거 예시로 선할인 들어간 상품인데요~
SELECT * FROM CAM_OFR_GIVE_COND
WHERE OFR_ID = '201701043120';
여기가 오퍼지급조건 테이블이거든요!
저희가 crm에서 오퍼지급조건 설정할 때 상품별로 금액또는 비율로 선할인 설정할 수 있어서
OFR_GIVE_COND_VAL : 상품
OFR_GIVE_RSL_VAL : 선할인 금액 또는 비율
COND_VAL_TP_CD : 금액, 비율, 수량인지 (CAM014)
이렇게 구분해서 보시면 될 것 같아요!!~
/* 할인관련 조건 */
SELECT *
FROM CAM_OFR_GIVE_COND
WHERE 1=1
-- AND INST_DTM >= TO_DATE('20220511','YYYYMMDD')
AND OFR_GIVE_COND_VAL IN ('10009642' )
ORDER BY OFR_ID
########################
# #
# KUDU쿠두 #
# #
########################
http://ignis.kr/impala-kudu-hwangyeong-guseong/
/* 쿠두 KUDU 버전 조회 */
1) http://192.168.91.159:8051
2) kudu -version
[root@HadoopNameSvr02 ~]# kudu -version
kudu 1.7.0-cdh5.15.0
revision 7dd4889729bb5ee0b331a5a6c3cd4427dbf79308
build type RELEASE
built by jenkins at 24 May 2018 05:04:39 PST on impala-ec2-pkg-centos-7-1e57.vpc.cloudera.com
build id 2018-05-24_04-07-47
########################
# #
# YARN 얀 #
# #
########################
/* 얀 yarn 버전 조회 -> 에러발생 */
yarn --version
[root@HadoopNameSvr02 ~]# yarn --version
Unrecognized option: --version
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
########################
# #
# hive 하이브 #
# #
########################
1. 하이브 버전 체크
1) 하이브설치된 하둡 네임서버2에 putty로 접속
2) hive --version
[root@HadoopNameSvr02 ~]# hive --version
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Hive 1.1.0-cdh5.15.0
Subversion file:///data/jenkins/workspace/generic-package-centos64-7-0/topdir/BUILD/hive-1.1.0-cdh5.15.0 -r Unknown
Compiled by jenkins on Thu May 24 04:17:02 PDT 2018
From source with checksum 493255612021cd90286fcf5a3712d24e
########################
# #
# 하둡 haddop #
# #
########################
1. 하둡 버전체크
hadoop version
[root@HadoopNameSvr02 ~]# hadoop version
Hadoop 2.6.0-cdh5.15.0
Subversion http://github.com/cloudera/hadoop -r e3cb23a1cb2b89d074171b44e71f207c3d6ffa50
Compiled by jenkins on 2018-05-24T11:19Z
Compiled with protoc 2.5.0
From source with checksum 2efeda2e2d6b27ecf59ab56fffd9881
This command was run using /opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/jars/hadoop-common-2.6.0-cdh5.15.0.jar
########################
# #
# 임팔라/하이브 #
# #
########################
/* 하둡 테이블 생성시, 컬럼에 쌍따옴표 사용하면 에러 발생 - 오라클 과는 다르다 */
/* 튜닝 */
1. Validate join order and join strategy
1) Optimal Join Order
- RHS should be smaller than LHS
- Minimize intermediate results
2) Strategy – Broadcast vs. Partitioned
- Network costs (partition and send lhs+rhs or broadcast rhs)
- Memory costs
- RHS must fit in memory!
3. Pick distributed join strategy: broadcast vs. partitioned
- Join inputs have very different size a broadcast small side.
- Join inputs have roughly equal size a partitioned.
union all 을 활용
4. 실제 메모리 사용량 조회
프로파일에서 Per Node Peak Memory Usage 이다.
복잡한 쿼리의 경우 hue의 프로파일에서 Peak Mem 컬럼이다.
5. filter가 미적용되는경우??
p.79
filter doesnt take effect because scan was short and finished before filter arrived.
해결
increate RUNTIME_FILTER_WAIT_TIME_MS to 5000ms to let Scan Node 03 wait onger time for the filter
If the cluster is relatively busy, consider increasing the wait time too so that complicated queries do not miss
opportunities for optimization
--query_option=RUNTIME_FILTER_WAIT_TIME_MS=5000.
MEMORY_PER_NODE_PEAK_NODE
;
/* 플랜 조회 */
set explain_level=3;
set num_nodes=10;
explain select * from dm.cmm_calendar
;
/* 임팔라 버전 조회 */
SELECT VERSION()
;
impalad version 2.12.0-cdh5.15.0 RELEASE (build 23f574543323301846b41fa5433690df32efe085)
Built on Thu May 24 04:07:31 PDT 2018
;
/* 임팔라 DB 정보 */
SELECT *
FROM hive.DBS
;
/* 임팔라 테이블 정보 조회 - 코멘트는 미존재 */
SELECT *
FROM hive.TBLS
WHERE 1=1
AND TBL_ID = '4300'
;
/* 임팔라 테이블 부가정보 - 코멘트,건수,사이즈,최종수정일시 등 */
SELECT *
FROM hive.TABLE_PARAMS
WHERE 1=1
AND TBL_ID = '4300'
;
/* 테이블 내 파티션 키 정보 - KUDU는 미존재함. */
SELECT *
FROM hive.PARTITION_KEYS
;
/* 임팔라 테이블 내 적재된 파티션 정보 */
SELECT *
FROM hive.PARTITIONS
WHERE 1=1
AND TBL_ID = '4300'
ORDER BY PART_NAME
;
/* TABLE KYE : CD_ID EX) 4300 임팔라 컬럼정보 */
SELECT *
FROM hive.COLUMNS_V2
WHERE 1=1
AND CD_ID = '4300'
;
/* 임팔라 컬럼 통계정보 - 주의! 통계정보연산을 한 테이블만 해당된다. */
SELECT *
FROM hive.TAB_COL_STATS
;
/*임팔라와 하이브는 메타스토어를 공유한다. */
/* sd_id 와 cd_id 의 맵핑 테이블 - 테이블 및 파티션 동일 레벨로 적재 */
SELECT *
FROM hive.SDS
WHERE 1=1
AND cd_id IN ('439782','439787')
;
/* 유닉스 타임변환 예)1533181835 -> 2018-08-02 12:50:35 */
SELECT CREATE_TIME
, FROM_UNIXTIME(CREATE_TIME)
FROM hive.PARTITIONS
WHERE 1=1
AND TBL_ID = '4300'
ORDER BY PART_NAME
;
/* 컬럼정보 조회 */
/* pk: CD_ID, COLUMN_NAME */
SELECT *
FROM hive.COLUMNS_V2
;
------------------------------------------------------------------
-- 1083906 >> 약 10분 소요 약 10만 건 당 1분
compute stats admin.bdd_ccd_wf_job_log
1. kudu table의 경우 compute stats를 하면
NUMROWS,TRANSIENT_LASTDDLTIME 는 갱싱 되지만
TOTALSIZE 는 갱신 되지 않는다.
2. text table의 경우 compute stats를 하면
NUMROWS,TRANSIENT_LASTDDLTIME, TOTALSIZE 갱신
3. view 는 지원안함.
COMPUTE STATS not supported for view
4. kudu, view 는 컬럼 코멘트 지원 안함.
5. 컬럼 코멘트 붙일때 컬럼명에 쌍따옴표 붙이면 에러발생 -> 오라클과 다르다.
6. HBase와 비슷하게 마스터 서버가 카탈로그 역할을 하고 각 태블릿(tablet) 서버가 각자의 태블릿을 저장하는 구조다.
태블릿이 실제 데이터를 저장하고 있는 파일이다. 네이버 콘텐츠 통계 서비스에서는 Kudu를 사용할 때 3대의 마스터 서버와 9대의 태블릿 서버로 클러스터를 구성했다.
약 800억 건 이상의 데이터를 저장하는 적지 않은 규모다.
/* 임팔라 테이블 정보 조회 - 테이블 정의서용 */
SELECT concat(A.DB_NAME, '.', MST.TBL_NAME) AS TBL_ID
, A.DB_NAME
, MST.TBL_NAME
, C.INPUT_FORMAT
, MST.TBL_TYPE
, B.TBL_COMMENT
, '' AS USER_TBL_COMMENTS /* 사용자가 정리하는 코멘트 */
, B.NUMROWS
-- , B.TOTALSIZE
, B.TOTALSIZE_MB
, D.PKEY_NAME
, DATE_FORMAT(B.TRANSIENT_LASTDDLTIME , '%Y-%m-%d %T' ) AS TRANSIENT_LASTDDLTIME
, B.DO_NOT_UPDATE_STATS
-- , B.EXTERNAL_VALUE
-- , B.STATS_GENERATED_VIA_STATS_TASK
, DATE_FORMAT( FROM_UNIXTIME(MST.CREATE_TIME) , '%Y-%m-%d %T' ) AS TBL_CREATE_TIME
/* 참고 정보 */
-- , MST.DB_ID
-- , A.DB_OWNER_NAME
-- , A.DB_OWNER_TYPEA
-- , A.DB_LOCATION_URI
-- , MST.TBL_ID
-- , MST.OWNER
-- , MST.SD_ID /* TBL_ID, CD_ID 사이의 맵핑 테이블 */
, MST.VIEW_EXPANDED_TEXT /* 뷰생성 확장 쿼리 */
-- , MST.VIEW_ORIGINAL_TEXT /* 뷰생성 기본 쿼리 */
-- , C.CD_ID
, C.OUTPUT_FORMAT
-- , C.LOCATION
-- , C.SERDE_ID
-- , C.NUM_BUCKETS
, C.IS_COMPRESSED /* 전체 0 */
-- , C.IS_STOREDASSUBDIRECTORIES /* 전체 0 */
/* 쿼리생성 */
, CASE WHEN MST.TBL_TYPE = 'VIRTUAL_VIEW' THEN ''
ELSE concat('compute stats ', A.DB_NAME, '.', MST.TBL_NAME, ';')
END AS compute_qry
FROM hive.TBLS MST
LEFT OUTER JOIN (
/* 임팔라 DB 목록 */
SELECT DB_ID
, NAME AS DB_NAME
, OWNER_NAME AS DB_OWNER_NAME
, OWNER_TYPE AS DB_OWNER_TYPE
, DB_LOCATION_URI
FROM hive.DBS
) A
ON MST.DB_ID = A.DB_ID
LEFT OUTER JOIN (
/* 임팔라 테이블 부가정보 - 코멘트,건수,사이즈,최종수정일시 등 */
SELECT TBL_ID
, MAX(CASE WHEN PARAM_KEY = 'comment' THEN PARAM_VALUE END) AS TBL_COMMENT
, MAX(CASE WHEN PARAM_KEY = 'numRows' THEN PARAM_VALUE END) AS NUMROWS
, MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END) AS TOTALSIZE
, ROUND(MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END)/1024/1024) AS TOTALSIZE_MB
, MAX(CASE WHEN PARAM_KEY = 'transient_lastDdlTime' THEN FROM_UNIXTIME(PARAM_VALUE) END) AS TRANSIENT_LASTDDLTIME
, MAX(CASE WHEN PARAM_KEY = 'DO_NOT_UPDATE_STATS' THEN PARAM_VALUE END) AS DO_NOT_UPDATE_STATS
, MAX(CASE WHEN PARAM_KEY = 'EXTERNAL' THEN PARAM_VALUE END) AS EXTERNAL_VALUE
, MAX(CASE WHEN PARAM_KEY = 'STATS_GENERATED_VIA_STATS_TASK' THEN PARAM_VALUE END) AS STATS_GENERATED_VIA_STATS_TASK
FROM hive.TABLE_PARAMS
GROUP BY TBL_ID
) B
ON MST.TBL_ID = B.TBL_ID
LEFT OUTER JOIN (
/* kudu 타입일 경우 파티션 미존재 */
/* 테이블의 저장 포맷조회 - input,output은 현재 모두 동일 */
SELECT SD_ID /* TBLS의 SD_ID 와 맵핑 */
, CD_ID /* 컬럼정보의 CD_ID 와 맵핑 */
, CASE WHEN INPUT_FORMAT LIKE '%Avro%' THEN 'Avro'
WHEN INPUT_FORMAT LIKE '%Parquet%' THEN 'Parquet'
WHEN INPUT_FORMAT LIKE '%Text%' THEN 'Text'
WHEN INPUT_FORMAT LIKE '%Kudu%' THEN 'Kudu'
ELSE 'na'
END AS INPUT_FORMAT
, CASE WHEN OUTPUT_FORMAT LIKE '%Avro%' THEN 'Avro'
WHEN OUTPUT_FORMAT LIKE '%Parquet%' THEN 'Parquet'
WHEN OUTPUT_FORMAT LIKE '%Text%' THEN 'Text'
WHEN OUTPUT_FORMAT LIKE '%Kudu%' THEN 'Kudu'
ELSE 'na'
END AS OUTPUT_FORMAT
, LOCATION
, SERDE_ID
, NUM_BUCKETS
, IS_COMPRESSED /* 전체 0 */
, IS_STOREDASSUBDIRECTORIES /* 전체 0 */
FROM hive.SDS
) C
ON MST.SD_ID = C.SD_ID
LEFT OUTER JOIN (
/* 테이블 내 파티션 키 정보 - 파티션컬럼이 복수개일경우, 쉼표로 구분 */
SELECT TBL_ID
, GROUP_CONCAT(PKEY_NAME SEPARATOR ',') AS PKEY_NAME
, CASE WHEN LENGTH( GROUP_CONCAT(PKEY_COMMENT SEPARATOR ',') ) = 1 THEN ''
ELSE GROUP_CONCAT(PKEY_COMMENT SEPARATOR ',')
END AS PKEY_COMMENT
, CASE WHEN LENGTH( GROUP_CONCAT(PKEY_TYPE SEPARATOR ',') ) = 1 THEN ''
ELSE GROUP_CONCAT(PKEY_TYPE SEPARATOR ',')
END AS PKEY_TYPE
FROM hive.PARTITION_KEYS
GROUP BY TBL_ID
) D
ON MST.TBL_ID = D.TBL_ID
WHERE 1=1
ORDER BY CASE WHEN DB_NAME LIKE 'temp_%' THEN 98
WHEN DB_NAME = 'test' THEN 99
ELSE 0
END
, A.DB_NAME
, MST.TBL_NAME
;
/* 임팔라 테이블 정보 조회 - 작업용 */
SELECT concat(A.DB_NAME, '.', MST.TBL_NAME) AS ID
, A.DB_NAME
, MST.TBL_NAME
, C.INPUT_FORMAT
, MST.TBL_TYPE
, B.TBL_COMMENT
, '' AS USER_TBL_COMMENTS /* 사용자가 정리하는 코멘트 */
, B.NUMROWS
, B.TOTALSIZE
, B.TOTALSIZE_MB
, D.PKEY_NAME
, B.TRANSIENT_LASTDDLTIME
, B.DO_NOT_UPDATE_STATS
, B.EXTERNAL_VALUE
, B.STATS_GENERATED_VIA_STATS_TASK
, FROM_UNIXTIME(MST.CREATE_TIME) AS TBL_CREATE_TIME
/* 참고 정보 */
, MST.DB_ID
, A.DB_OWNER_NAME
, A.DB_OWNER_TYPE
, A.DB_LOCATION_URI
, MST.TBL_ID
, MST.OWNER
, MST.SD_ID /* TBL_ID, CD_ID 사이의 맵핑 테이블 */
, MST.VIEW_EXPANDED_TEXT /* 뷰생성 확장 쿼리 */
, MST.VIEW_ORIGINAL_TEXT /* 뷰생성 기본 쿼리 */
, C.CD_ID
, C.OUTPUT_FORMAT
, C.LOCATION
, C.SERDE_ID
, C.NUM_BUCKETS
, C.IS_COMPRESSED /* 전체 0 */
, C.IS_STOREDASSUBDIRECTORIES /* 전체 0 */
/* 쿼리생성 */
, CASE WHEN MST.TBL_TYPE = 'VIRTUAL_VIEW' THEN ''
ELSE concat('compute stats ', A.DB_NAME, '.', MST.TBL_NAME, ';')
END AS compute_qry
FROM hive.TBLS MST
LEFT OUTER JOIN (
/* 임팔라 DB 목록 */
SELECT DB_ID
, NAME AS DB_NAME
, OWNER_NAME AS DB_OWNER_NAME
, OWNER_TYPE AS DB_OWNER_TYPE
, DB_LOCATION_URI
FROM hive.DBS
) A
ON MST.DB_ID = A.DB_ID
LEFT OUTER JOIN (
/* 임팔라 테이블 부가정보 - 코멘트,건수,사이즈,최종수정일시 등 */
SELECT TBL_ID
, MAX(CASE WHEN PARAM_KEY = 'comment' THEN PARAM_VALUE END) AS TBL_COMMENT
, MAX(CASE WHEN PARAM_KEY = 'numRows' THEN PARAM_VALUE END) AS NUMROWS
, MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END) AS TOTALSIZE
, ROUND(MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END)/1024/1024) AS TOTALSIZE_MB
, MAX(CASE WHEN PARAM_KEY = 'transient_lastDdlTime' THEN FROM_UNIXTIME(PARAM_VALUE) END) AS TRANSIENT_LASTDDLTIME
, MAX(CASE WHEN PARAM_KEY = 'DO_NOT_UPDATE_STATS' THEN PARAM_VALUE END) AS DO_NOT_UPDATE_STATS
, MAX(CASE WHEN PARAM_KEY = 'EXTERNAL' THEN PARAM_VALUE END) AS EXTERNAL_VALUE
, MAX(CASE WHEN PARAM_KEY = 'STATS_GENERATED_VIA_STATS_TASK' THEN PARAM_VALUE END) AS STATS_GENERATED_VIA_STATS_TASK
FROM hive.TABLE_PARAMS
GROUP BY TBL_ID
) B
ON MST.TBL_ID = B.TBL_ID
LEFT OUTER JOIN (
/* kudu 타입일 경우 파티션 미존재 */
/* 테이블의 저장 포맷조회 - input,output은 현재 모두 동일 */
SELECT SD_ID /* TBLS의 SD_ID 와 맵핑 */
, CD_ID /* 컬럼정보의 CD_ID 와 맵핑 */
, CASE WHEN INPUT_FORMAT LIKE '%Avro%' THEN 'Avro'
WHEN INPUT_FORMAT LIKE '%Parquet%' THEN 'Parquet'
WHEN INPUT_FORMAT LIKE '%Text%' THEN 'Text'
WHEN INPUT_FORMAT LIKE '%Kudu%' THEN 'Kudu'
ELSE 'na'
END AS INPUT_FORMAT
, CASE WHEN OUTPUT_FORMAT LIKE '%Avro%' THEN 'Avro'
WHEN OUTPUT_FORMAT LIKE '%Parquet%' THEN 'Parquet'
WHEN OUTPUT_FORMAT LIKE '%Text%' THEN 'Text'
WHEN OUTPUT_FORMAT LIKE '%Kudu%' THEN 'Kudu'
ELSE 'na'
END AS OUTPUT_FORMAT
, LOCATION
, SERDE_ID
, NUM_BUCKETS
, IS_COMPRESSED /* 전체 0 */
, IS_STOREDASSUBDIRECTORIES /* 전체 0 */
FROM hive.SDS
) C
ON MST.SD_ID = C.SD_ID
LEFT OUTER JOIN (
/* 테이블 내 파티션 키 정보 - 파티션컬럼이 복수개일경우, 쉼표로 구분 */
SELECT TBL_ID
, GROUP_CONCAT(PKEY_NAME SEPARATOR ',') AS PKEY_NAME
, CASE WHEN LENGTH( GROUP_CONCAT(PKEY_COMMENT SEPARATOR ',') ) = 1 THEN ''
ELSE GROUP_CONCAT(PKEY_COMMENT SEPARATOR ',')
END AS PKEY_COMMENT
, CASE WHEN LENGTH( GROUP_CONCAT(PKEY_TYPE SEPARATOR ',') ) = 1 THEN ''
ELSE GROUP_CONCAT(PKEY_TYPE SEPARATOR ',')
END AS PKEY_TYPE
FROM hive.PARTITION_KEYS
GROUP BY TBL_ID
) D
ON MST.TBL_ID = D.TBL_ID
WHERE 1=1
ORDER BY CASE WHEN DB_NAME LIKE 'temp_%' THEN 98
WHEN DB_NAME = 'test' THEN 99
ELSE 0
END
, A.DB_NAME
, MST.TBL_NAME
;
/* 임팔라 컬럼 정보 조회 - 테이블정의서용도 */
SELECT concat(A.DB_NAME, '.', MST.TBL_NAME) AS ID
, A.DB_NAME
, MST.TBL_NAME AS TBL_ID
, B.TBL_COMMENT
, F.INPUT_FORMAT
, MST.TBL_TYPE
, D.INTEGER_IDX+1
, D.COLUMN_NAME
, '' AS "WORK"
, '' AS "CHK"
, D.COMMENT
, '' AS USER_COL_COMMENTS /* 사용자가 정리하는 코멘트 */
, '' AS code_list
, '' AS pk
, UPPER(D.TYPE_NAME) AS TYPE_NAME
, '' AS WIDTH
, '' AS NULLS_YN
, '' AS DEFAULT_VALUE
, '' AS CODE
, E.NUM_DISTINCTS
, E.AVG_COL_LEN
, E.MAX_COL_LEN
, FROM_UNIXTIME(E.LAST_ANALYZED) AS LAST_ANALYZED
-- , C.CD_ID -- KUDE COLUMN COMMENT 입력 KEY
FROM hive.TBLS MST
LEFT OUTER JOIN (
/* 임팔라 DB 목록 */
SELECT DB_ID
, NAME AS DB_NAME
, OWNER_NAME AS DB_OWNER_NAME
, OWNER_TYPE AS DB_OWNER_TYPE
, DB_LOCATION_URI
FROM hive.DBS
) A
ON MST.DB_ID = A.DB_ID
LEFT OUTER JOIN (
/* 임팔라 테이블 부가정보 - 코멘트,건수,사이즈,최종수정일시 등 */
SELECT TBL_ID
, MAX(CASE WHEN PARAM_KEY = 'comment' THEN PARAM_VALUE END) AS TBL_COMMENT
, MAX(CASE WHEN PARAM_KEY = 'numRows' THEN PARAM_VALUE END) AS NUMROWS
, MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END) AS TOTALSIZE
, ROUND(MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END)/1024/1024) AS TOTALSIZE_MB
, MAX(CASE WHEN PARAM_KEY = 'transient_lastDdlTime' THEN FROM_UNIXTIME(PARAM_VALUE) END) AS TRANSIENT_LASTDDLTIME
, MAX(CASE WHEN PARAM_KEY = 'DO_NOT_UPDATE_STATS' THEN PARAM_VALUE END) AS DO_NOT_UPDATE_STATS
, MAX(CASE WHEN PARAM_KEY = 'EXTERNAL' THEN PARAM_VALUE END) AS EXTERNAL_VALUE
, MAX(CASE WHEN PARAM_KEY = 'STATS_GENERATED_VIA_STATS_TASK' THEN PARAM_VALUE END) AS STATS_GENERATED_VIA_STATS_TASK
FROM hive.TABLE_PARAMS
GROUP BY TBL_ID
) B
ON MST.TBL_ID = B.TBL_ID
LEFT OUTER JOIN (
/* 테이블ID, 컬럼ID 맵핑 테이블 */
SELECT SD_ID
, CD_ID
, IS_COMPRESSED
, IS_STOREDASSUBDIRECTORIES
FROM hive.SDS
) C
ON MST.SD_ID = C.SD_ID
LEFT OUTER JOIN (
SELECT CD_ID
, COLUMN_NAME
, INTEGER_IDX
, COMMENT
, TYPE_NAME
FROM hive.COLUMNS_V2
) D /* 주의! 파티션 컬럼의 경우 조회되지 않는다 */
ON C.CD_ID = D.CD_ID
LEFT OUTER JOIN (
/* 테이블ID, 컬럼ID 맵핑 테이블 */
SELECT DB_NAME
, TBL_ID
, CS_ID
, COLUMN_NAME
, NUM_DISTINCTS
, AVG_COL_LEN
, MAX_COL_LEN
, FROM_UNIXTIME(LAST_ANALYZED) AS LAST_ANALYZED
FROM hive.TAB_COL_STATS
) E
ON MST.TBL_ID = E.TBL_ID
AND A.DB_NAME = E.DB_NAME
AND D.COLUMN_NAME = E.COLUMN_NAME
LEFT OUTER JOIN (
/* kudu 타입일 경우 파티션 미존재 */
/* 테이블의 저장 포맷조회 - input,output은 현재 모두 동일 */
SELECT SD_ID /* TBLS의 SD_ID 와 맵핑 */
, CD_ID /* 컬럼정보의 CD_ID 와 맵핑 */
, CASE WHEN INPUT_FORMAT LIKE '%Avro%' THEN 'Avro'
WHEN INPUT_FORMAT LIKE '%Parquet%' THEN 'Parquet'
WHEN INPUT_FORMAT LIKE '%Text%' THEN 'Text'
WHEN INPUT_FORMAT LIKE '%Kudu%' THEN 'Kudu'
ELSE 'na'
END AS INPUT_FORMAT
, CASE WHEN OUTPUT_FORMAT LIKE '%Avro%' THEN 'Avro'
WHEN OUTPUT_FORMAT LIKE '%Parquet%' THEN 'Parquet'
WHEN OUTPUT_FORMAT LIKE '%Text%' THEN 'Text'
WHEN OUTPUT_FORMAT LIKE '%Kudu%' THEN 'Kudu'
ELSE 'na'
END AS OUTPUT_FORMAT
, LOCATION
, SERDE_ID
, NUM_BUCKETS
, IS_COMPRESSED /* 전체 0 */
, IS_STOREDASSUBDIRECTORIES /* 전체 0 */
FROM hive.SDS
) F
ON MST.SD_ID = F.SD_ID
WHERE 1=1
-- AND MST.TBL_NAME = 'ord_dlv_svc_surv_stt_info_ccd'
ORDER BY CASE WHEN A.DB_NAME LIKE 'temp_%' THEN 98
WHEN A.DB_NAME = 'test' THEN 99
ELSE 0
END
, A.DB_NAME
, MST.TBL_NAME
, D.INTEGER_IDX
;
;
/* 임팔라 테이블 정보 조회 - 코멘트는 미존재 */
SELECT MST.DB_ID
, MST.TBL_ID
, FROM_UNIXTIME(MST.CREATE_TIME) AS TBL_CREATE_TIME
, MST.OWNER
, MST.SD_ID /* TBL_ID, CD_ID 사이의 맵핑 테이블 */
, MST.TBL_NAME
, MST.TBL_TYPE
, MST.VIEW_EXPANDED_TEXT /* 뷰생성 확장 쿼리 */
, MST.VIEW_ORIGINAL_TEXT /* 뷰생성 기본 쿼리 */
FROM hive.TBLS MST
WHERE 1=1
;
/* 임팔라 DB 목록 */
SELECT DB_ID
, NAME AS DB_NAME
, OWNER_NAME AS DB_OWNER_NAME
, OWNER_TYPE AS DB_OWNER_TYPE
, DB_LOCATION_URI
FROM hive.DBS
;
/* kudu 타입일 경우 파티션 미존재 */
/* 테이블의 저장 포맷조회 - input,output은 현재 모두 동일 */
SELECT SD_ID /* TBLS의 SD_ID 와 맵핑 */
, CD_ID /* 컬럼정보의 CD_ID 와 맵핑 */
, CASE WHEN INPUT_FORMAT LIKE '%Avro%' THEN 'Avro'
WHEN INPUT_FORMAT LIKE '%Parquet%' THEN 'Parquet'
WHEN INPUT_FORMAT LIKE '%Text%' THEN 'Text'
WHEN INPUT_FORMAT LIKE '%Kudu%' THEN 'Kudu'
ELSE 'na'
END AS INPUT_FORMAT
, CASE WHEN OUTPUT_FORMAT LIKE '%Avro%' THEN 'Avro'
WHEN OUTPUT_FORMAT LIKE '%Parquet%' THEN 'Parquet'
WHEN OUTPUT_FORMAT LIKE '%Text%' THEN 'Text'
WHEN OUTPUT_FORMAT LIKE '%Kudu%' THEN 'Kudu'
ELSE 'na'
END AS OUTPUT_FORMAT
, LOCATION
, SERDE_ID
, NUM_BUCKETS
, IS_COMPRESSED /* 전체 0 */
, IS_STOREDASSUBDIRECTORIES /* 전체 0 */
FROM hive.SDS
WHERE 1=1
ORDER BY SD_ID
;
/* 임팔라 테이블 부가정보 - 코멘트,건수,사이즈,최종수정일시 등 */
SELECT TBL_ID
, MAX(CASE WHEN PARAM_KEY = 'comment' THEN PARAM_VALUE END) AS TBL_COMMENT
, MAX(CASE WHEN PARAM_KEY = 'numRows' THEN PARAM_VALUE END) AS NUMROWS
, MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END) AS TOTALSIZE
, ROUND(MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END)/1024/1024) AS TOTALSIZE_MB
, MAX(CASE WHEN PARAM_KEY = 'transient_lastDdlTime' THEN FROM_UNIXTIME(PARAM_VALUE) END) AS TRANSIENT_LASTDDLTIME
, MAX(CASE WHEN PARAM_KEY = 'DO_NOT_UPDATE_STATS' THEN PARAM_VALUE END) AS DO_NOT_UPDATE_STATS
, MAX(CASE WHEN PARAM_KEY = 'EXTERNAL' THEN PARAM_VALUE END) AS EXTERNAL_VALUE
, MAX(CASE WHEN PARAM_KEY = 'STATS_GENERATED_VIA_STATS_TASK' THEN PARAM_VALUE END) AS STATS_GENERATED_VIA_STATS_TASK
FROM hive.TABLE_PARAMS
WHERE 1=1
AND TBL_ID = '4300'
GROUP BY TBL_ID
;
/* 테이블 내 파티션 키 정보 */
SELECT P1.TBL_ID
, P2.PART_ID
, SUBSTRING_INDEX(P1.PART_NAME, '=', 1) AS PART_KEY
, P2.PART_KEY_VAL
, FROM_UNIXTIME(CREATE_TIME) AS PKEY_CREATE_TIME
, SD_ID
FROM hive.PARTITIONS P1
LEFT OUTER JOIN hive.PARTITION_KEY_VALS P2
ON P1.PART_ID = P2.PART_ID
WHERE 1=1
ORDER BY P1.TBL_ID
, P2.PART_ID
;
/* 테이블 내 파티션 정보 */
SELECT P1.TBL_ID
, P2.PART_ID
, SUBSTRING_INDEX(P1.PART_NAME, '=', 1) AS PART_KEY
, P2.PART_KEY_VAL
, FROM_UNIXTIME(CREATE_TIME) AS PKEY_CREATE_TIME
, SD_ID
FROM hive.PARTITIONS P1
LEFT OUTER JOIN hive.PARTITION_KEY_VALS P2
ON P1.PART_ID = P2.PART_ID
WHERE 1=1
ORDER BY P1.TBL_ID
, P2.PART_ID
;
/* 파티션별 정보 KEY : PART_ID */
SELECT PART_ID
, MAX(CASE WHEN PARAM_KEY = 'numRows' THEN PARAM_VALUE END) AS NUMROWS
, ROUND(MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END)/1024/1024,3) AS TOTALSIZE_MB
, MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END) AS TOTALSIZE
, FROM_UNIXTIME(MAX(CASE WHEN PARAM_KEY = 'transient_lastDdlTime' THEN PARAM_VALUE END)) AS TRANSIENT_LASTDDLTIME
, MAX(CASE WHEN PARAM_KEY = 'COLUMN_STATS_ACCURATE' THEN PARAM_VALUE END) AS COLUMN_STATS_ACCURATE
, MAX(CASE WHEN PARAM_KEY = 'rawDataSize' THEN PARAM_VALUE END) AS RAWDATASIZE
FROM hive.PARTITION_PARAMS
WHERE 1=1
GROUP BY PART_ID
;
/* 파티션별 데이터 적재 확인 - 전체 */
SELECT P5.*
, CASE WHEN LENGTH(P5.MIN_PART_KEY_VAL) = 6 AND LENGTH(P5.MAX_PART_KEY_VAL) = 6 THEN 'MONTHLY'
WHEN LENGTH(P5.MIN_PART_KEY_VAL) = 8 AND LENGTH(P5.MAX_PART_KEY_VAL) = 8 THEN 'DAILY'
ELSE 'ETC'
END PART_TERM
FROM (
/* 파티션별 데이터 적재 확인 - 전체 */
SELECT MAX(P3.DB_ID) AS DB_ID
, MAX(P1.TBL_ID) AS TBL_ID
, P3.TBL_NAME
, MAX(P4.TBL_COMMENT) AS TBL_COMMENT
, MAX(P3.TBL_TYPE) AS TBL_TYPE
, REPLACE(REPLACE(MIN(P2.PART_KEY_VAL),'-', ''),' 00:00:00','') AS MIN_PART_KEY_VAL
, REPLACE(REPLACE(MAX(P2.PART_KEY_VAL),'-', ''),' 00:00:00','') AS MAX_PART_KEY_VAL
, COUNT(*) AS PART_CNT
, MIN(FROM_UNIXTIME(P1.CREATE_TIME)) AS MIN_PKEY_CREATE_TIME
, MAX(FROM_UNIXTIME(P1.CREATE_TIME)) AS MAX_PKEY_CREATE_TIME
FROM hive.PARTITIONS P1
LEFT OUTER JOIN hive.PARTITION_KEY_VALS P2
ON P1.PART_ID = P2.PART_ID
LEFT OUTER JOIN hive.TBLS P3
ON P1.TBL_ID = P3.TBL_ID
LEFT OUTER JOIN (
/* 임팔라 테이블 부가정보 - 코멘트,건수,사이즈,최종수정일시 등 */
SELECT TBL_ID
, MAX(CASE WHEN PARAM_KEY = 'comment' THEN PARAM_VALUE END) AS TBL_COMMENT
, MAX(CASE WHEN PARAM_KEY = 'numRows' THEN PARAM_VALUE END) AS NUMROWS
, MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END) AS TOTALSIZE
, ROUND(MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END)/1024/1024) AS TOTALSIZE_MB
, MAX(CASE WHEN PARAM_KEY = 'transient_lastDdlTime' THEN FROM_UNIXTIME(PARAM_VALUE) END) AS TRANSIENT_LASTDDLTIME
, MAX(CASE WHEN PARAM_KEY = 'DO_NOT_UPDATE_STATS' THEN PARAM_VALUE END) AS DO_NOT_UPDATE_STATS
, MAX(CASE WHEN PARAM_KEY = 'EXTERNAL' THEN PARAM_VALUE END) AS EXTERNAL_VALUE
, MAX(CASE WHEN PARAM_KEY = 'STATS_GENERATED_VIA_STATS_TASK' THEN PARAM_VALUE END) AS STATS_GENERATED_VIA_STATS_TASK
FROM hive.TABLE_PARAMS
GROUP BY TBL_ID
) P4
ON P1.TBL_ID = P4.TBL_ID
WHERE 1=1
AND P2.PART_KEY_VAL <> '__HIVE_DEFAULT_PARTITION__'
AND P2.PART_KEY_VAL <> '19000101'
GROUP BY P3.TBL_NAME
) P5
ORDER BY CASE WHEN P5.DB_NAME LIKE 'temp_%' THEN 98
WHEN P5.DB_NAME = 'test' THEN 99
ELSE 0
END
, P5.DB_NAME
, P5.TBL_NAME
;
/* KUDU 및 비파티션 파켓 파일을 제외한 테이블별 파티션 요약정보 확인 */
SELECT CONCAT(P4.DB_NM,'.',P4.TBL_NAME) AS ID
, P4.DB_ID
, P4.DB_NM
, P4.TBL_ID
, P4.TBL_NAME
, P4.TBL_COMMENT
, P4.TBL_TYPE
, P4.MIN_PART_KEY_VAL
, P4.MAX_PART_KEY_VAL
, P4.PART_CNT
, P4.PART_TERM
, CASE WHEN P4.PART_TERM = 'MONTHLY' THEN TIMESTAMPDIFF(MONTH, CONCAT(P4.MIN_PART_KEY_VAL,'01'), CONCAT(P4.MAX_PART_KEY_VAL,'01'))
WHEN P4.PART_TERM = 'DAILY' THEN TIMESTAMPDIFF(DAY, P4.MIN_PART_KEY_VAL, P4.MAX_PART_KEY_VAL)
ELSE 'NA'
END TERM_PERIOD
, P4.MIN_PKEY_CREATE_TIME
, P4.MAX_PKEY_CREATE_TIME
FROM (
/* 파티션별 데이터 적재 확인 - 전체 */
SELECT MAX(P3.DB_ID) AS DB_ID
, MAX(P1.TBL_ID) AS TBL_ID
, P3.TBL_NAME
, MAX(P4.TBL_COMMENT) AS TBL_COMMENT
, MAX(P3.TBL_TYPE) AS TBL_TYPE
, MIN(P2.PART_KEY_VAL) AS MIN_PART_KEY_VAL
, MAX(P2.PART_KEY_VAL) AS MAX_PART_KEY_VAL
, COUNT(*) AS PART_CNT
, MIN(FROM_UNIXTIME(P1.CREATE_TIME)) AS MIN_PKEY_CREATE_TIME
, MAX(FROM_UNIXTIME(P1.CREATE_TIME)) AS MAX_PKEY_CREATE_TIME
, CASE WHEN LENGTH(P2.PART_KEY_VAL ) = 6 AND LENGTH(P2.PART_KEY_VAL) = 6 THEN 'MONTHLY'
WHEN LENGTH(P2.PART_KEY_VAL ) = 8 AND LENGTH(P2.PART_KEY_VAL) = 8 THEN 'DAILY'
ELSE 'ETC'
END PART_TERM
, P5.NAME AS DB_NM
FROM hive.PARTITIONS P1
LEFT OUTER JOIN (
SELECT PART_ID
, REPLACE(REPLACE(PART_KEY_VAL,'-', ''),' 00:00:00','') AS PART_KEY_VAL
FROM hive.PARTITION_KEY_VALS
) P2
ON P1.PART_ID = P2.PART_ID
LEFT OUTER JOIN hive.TBLS P3
ON P1.TBL_ID = P3.TBL_ID
LEFT OUTER JOIN (
/* 임팔라 테이블 부가정보 - 코멘트,건수,사이즈,최종수정일시 등 */
SELECT TBL_ID
, MAX(CASE WHEN PARAM_KEY = 'comment' THEN PARAM_VALUE END) AS TBL_COMMENT
, MAX(CASE WHEN PARAM_KEY = 'numRows' THEN PARAM_VALUE END) AS NUMROWS
, MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END) AS TOTALSIZE
, ROUND(MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END)/1024/1024) AS TOTALSIZE_MB
, MAX(CASE WHEN PARAM_KEY = 'transient_lastDdlTime' THEN FROM_UNIXTIME(PARAM_VALUE) END) AS TRANSIENT_LASTDDLTIME
, MAX(CASE WHEN PARAM_KEY = 'DO_NOT_UPDATE_STATS' THEN PARAM_VALUE END) AS DO_NOT_UPDATE_STATS
, MAX(CASE WHEN PARAM_KEY = 'EXTERNAL' THEN PARAM_VALUE END) AS EXTERNAL_VALUE
, MAX(CASE WHEN PARAM_KEY = 'STATS_GENERATED_VIA_STATS_TASK' THEN PARAM_VALUE END) AS STATS_GENERATED_VIA_STATS_TASK
FROM hive.TABLE_PARAMS
GROUP BY TBL_ID
) P4
ON P1.TBL_ID = P4.TBL_ID
LEFT OUTER JOIN hive.DBS P5
ON P3.DB_ID = P5.DB_ID
WHERE 1=1
AND P2.PART_KEY_VAL <> '__HIVE_DEFAULT_PARTITION__'
AND P2.PART_KEY_VAL <> '19000101'
GROUP BY P3.TBL_NAME
) P4
WHERE 1=1
--AND P4.TBL_NAME = 'd_jigu_2'
ORDER BY P4.DB_ID
, P4.TBL_NAME
;
/* 파티션별 데이터 적재 확인 - 상세 */
SELECT CONCAT(P5.NAME, '.', P3.TBL_NAME) AS ID
, P5.NAME AS DB_NM
, P3.TBL_ID
, P3.TBL_NAME
, P4.TBL_COMMENT AS TBL_COMMENT
, P3.TBL_TYPE AS TBL_TYPE
, P1.PART_ID
, P2.PART_KEY_VAL
, P6.NUMROWS
, P6.TOTALSIZE_MB
, P6.TOTALSIZE
, P6.TRANSIENT_LASTDDLTIME
, P6.COLUMN_STATS_ACCURATE
, FROM_UNIXTIME(P1.CREATE_TIME) AS PKEY_CREATE_TIME
, CASE WHEN LENGTH(P2.PART_KEY_VAL ) = 6 AND LENGTH(P2.PART_KEY_VAL) = 6 THEN 'MONTHLY'
WHEN LENGTH(P2.PART_KEY_VAL ) = 8 AND LENGTH(P2.PART_KEY_VAL) = 8 THEN 'DAILY'
ELSE 'ETC'
END PART_TERM
FROM hive.PARTITIONS P1
LEFT OUTER JOIN (
SELECT PART_ID
, REPLACE(REPLACE(PART_KEY_VAL,'-', ''),' 00:00:00','') AS PART_KEY_VAL
FROM hive.PARTITION_KEY_VALS
) P2
ON P1.PART_ID = P2.PART_ID
LEFT OUTER JOIN hive.TBLS P3
ON P1.TBL_ID = P3.TBL_ID
LEFT OUTER JOIN (
/* 임팔라 테이블 부가정보 - 코멘트,건수,사이즈,최종수정일시 등 */
SELECT TBL_ID
, MAX(CASE WHEN PARAM_KEY = 'comment' THEN PARAM_VALUE END) AS TBL_COMMENT
, MAX(CASE WHEN PARAM_KEY = 'numRows' THEN PARAM_VALUE END) AS NUMROWS
, MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END) AS TOTALSIZE
, ROUND(MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END)/1024/1024) AS TOTALSIZE_MB
, MAX(CASE WHEN PARAM_KEY = 'transient_lastDdlTime' THEN FROM_UNIXTIME(PARAM_VALUE) END) AS TRANSIENT_LASTDDLTIME
, MAX(CASE WHEN PARAM_KEY = 'DO_NOT_UPDATE_STATS' THEN PARAM_VALUE END) AS DO_NOT_UPDATE_STATS
, MAX(CASE WHEN PARAM_KEY = 'EXTERNAL' THEN PARAM_VALUE END) AS EXTERNAL_VALUE
, MAX(CASE WHEN PARAM_KEY = 'STATS_GENERATED_VIA_STATS_TASK' THEN PARAM_VALUE END) AS STATS_GENERATED_VIA_STATS_TASK
FROM hive.TABLE_PARAMS
GROUP BY TBL_ID
) P4
ON P1.TBL_ID = P4.TBL_ID
LEFT OUTER JOIN hive.DBS P5
ON P3.DB_ID = P5.DB_ID
LEFT OUTER JOIN (
/* 파티션별 정보 KEY : PART_ID */
SELECT PART_ID
, MAX(CASE WHEN PARAM_KEY = 'numRows' THEN PARAM_VALUE END) AS NUMROWS
, ROUND(MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END)/1024/1024,3) AS TOTALSIZE_MB
, MAX(CASE WHEN PARAM_KEY = 'totalSize' THEN PARAM_VALUE END) AS TOTALSIZE
, FROM_UNIXTIME(MAX(CASE WHEN PARAM_KEY = 'transient_lastDdlTime' THEN PARAM_VALUE END)) AS TRANSIENT_LASTDDLTIME
, MAX(CASE WHEN PARAM_KEY = 'COLUMN_STATS_ACCURATE' THEN PARAM_VALUE END) AS COLUMN_STATS_ACCURATE
, MAX(CASE WHEN PARAM_KEY = 'rawDataSize' THEN PARAM_VALUE END) AS RAWDATASIZE
FROM hive.PARTITION_PARAMS
WHERE 1=1
GROUP BY PART_ID
) P6
ON P1.PART_ID = P6.PART_ID
WHERE 1=1
AND P2.PART_KEY_VAL <> '__HIVE_DEFAULT_PARTITION__'
AND P2.PART_KEY_VAL <> '19000101'
AND P3.TBL_ID <> '201778' /* hy_weblog 제외 */
AND STR_TO_DATE(P2.PART_KEY_VAL, '%Y%m%d' ) >= STR_TO_DATE('20210101', '%Y%m%d' ) /* 2021년 이후만 추출 */
-- AND P5.NAME = 'temp_ccip'
AND P5.NAME = 'bo'
-- AND P1.PART_ID = '613026'
-- AND P3.TBL_NAME IN ('ord_ctr_prd_pfmc_d','ord_ctr_prd_pfmc_d_ccd','ord_ctr_prd_pfmc_d_dlv','ord_ctr_prd_pfmc_d_dlv_ccd','anl_dd_jigu_ctr_cst_lst','anl_dd_jigu_ctr_cst_lst_ccd','ord_ctr_prd_pfmc_h','ord_ctr_prd_pfmc_h_ccd' )
-- AND P3.TBL_NAME IN ('ord_ctr_prd_pfmc_d','ord_ctr_prd_pfmc_d_ccd' )
-- AND P3.TBL_NAME IN ('ord_ctr_prd_pfmc_d_ccd' )
-- AND P3.TBL_NAME IN ('anl_pre_def_new_cst_lst')
ORDER BY ID
, P2.PART_KEY_VAL
;
;
MYSQL 구버전 ROWNUM 구현
SELECT TBL_ID
, @rownum := @rownum+1 AS RNUM
FROM hive.PARTITIONS
, (SELECT @rownum :=0) AS R
;
2. PARTITION BY 구현
ROW_NUMBER(PARTITION BY 컬럼1 ORDER BY 컬럼2)
SELECT @ROW_NUM := IF(@PREV_VALUE = A.컬럼1, @ROW_NUM + 1, 1) AS ROW_NUMBER
, @PREV_VALUE := A.컬럼1
, A.*
FROM 테이블 A
, (SELECT @ROW_NUM := 1) X
, (SELECT @PREV_VALUE := '') Y
ORDER BY A.컬럼2
-- 참고 샘플
SELECT @row_num := IF(@prev_value=o.Customer,@row_num+1,1) AS RowNumber
,o.Customer
,o.OrderDate
,o.Amount
,@prev_value := o.Customer
FROM Orders o,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY o.Customer, o.OrderDate DESC
;
3. LAG 구현
-- 1. Self Join --
-- > 너무 ~~~ 느리다. 사용불가
SELECT a.user_id
, a.login_dt
, MAX(b.login_dt) leg_dt
FROM t a
LEFT OUTER JOIN t b
ON a.user_id = b.user_id
AND a.login_dt > b.login_dt
GROUP BY a.user_id, a.login_dt
;
-- 2. @변수 사용 --
-- 값이 이전값이 가져오지 못함. 사용 불가
SELECT user_id
, login_dt
, lag_dt
FROM (SELECT user_id
, login_dt
, CASE WHEN @id = user_id THEN @dt END lag_dt
, @dt := login_dt
, @id := user_id
FROM t
ORDER BY user_id, login_dt
) a
;
-- 3. 스칼라서브쿼리 --
SELECT user_id
, login_dt
, (SELECT login_dt
FROM t b
WHERE b.user_id = a.user_id
AND b.login_dt < a.login_dt
ORDER BY login_dt DESC
LIMIT 1
) lag_dt
FROM t a
ORDER BY user_id, login_dt
;
########################
# #
# 리눅스 #
# #
########################
1.CLI 창에서 REST 방식 콜
curl -X POST http://192.168.91.159:8888/oozie/editor/coordinator/submit/9529
[root@HadoopAnalSvr oozie]# history
1 cd /var/log/oozie/
2 ls
3 vi oozie-cmf-oozie-OOZIE_SERVER-hadoopanalsvr.yakult.co.kr.log.out
4 tail -f oozie-cmf-oozie-OOZIE_SERVER-hadoopanalsvr.yakult.co.kr.log.out
5 curl -X POST http://192.168.91.159:8888/oozie/editor/coordinator/submit/9529
6 tail -f oozie-cmf-oozie-OOZIE_SERVER-hadoopanalsvr.yakult.co.kr.log.out
7 curl -X POST http://192.168.91.159:8888/oozie/editor/coordinator/submit/9529
8 hist
9 history
[root@HadoopAnalSvr hue]# history
1 cd /var/log/oozie/
2 ls
3 vi oozie-cmf-oozie-OOZIE_SERVER-hadoopanalsvr.yakult.co.kr.log.out
4 ls
5 cd ..
6 ls
7 cd hue/
8 ls
9 vi runcpserver.log
10 vi error.log
11 cd ..
12 ls
13 cd oozie/
14 ls
15 vi oozie-instrumentation.log
16 history
17 ll
18 vi oozie-audit.log
19 vi oozie-cmf-oozie-OOZIE_SERVER-hadoopanalsvr.yakult.co.kr.log
20 vi oozie-cmf-oozie-OOZIE_SERVER-hadoopanalsvr.yakult.co.kr.log.out
21 tail -f oozie-cmf-oozie-OOZIE_SERVER-hadoopanalsvr.yakult.co.kr.log.out
22 ls
23 vi oozie-instrumentation.log
24 vi oozie-audit.log
25 pwd
26 cd ../hue/
27 ls
28 vi access.log
29 vi migrate.log
30 vi runcpserver.log
31 ls -al
32 vi runcpserver.log.1
33 ls
34 vi syncdb.log
35 vi runcpserver.log
36 tail -f runcpserver.log
37 ls /tmp/tmpiDtI9a/oozie/editor2/gen/workflow-subworkflow.xml.mako.py
38 ls /opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Mako-0.8.1-py2.7.egg/mako/runtime.py
39 vi /opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Mako-0.8.1-py2.7.egg/mako/runtime.py
40 vi "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/core/handlers/base.py"
41 vi /opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/core/handlers/base.py
42 curl -X POST http://192.168.91.159:8888/oozie/editor/coordinator/submit/9529
43 ls
44 vi runcpserver.log
45 tail runcpserver.log
46 tail -f runcpserver.log
47 vi runcpserver.log
48 ls
49 vi runcpserver.log.1
50 vi runcpserver.log.2
51 tail -f runcpserver.log
52 hdfs dfs -ls /source/ccip/common/shellscript/add_partition.sh
53 hdfs dfs -ls /user/hue/oozie/workspaces/hue-oozie-1532579122.92/
54 history
2. 디렉토리 용량 조회
du -sh /*
*/
df : 디스크의 남은 용량을 확인
df -k : 킬로바이트 단위로 현재 남은 용량을 확인
df -m : 메가바이트 단위로 남은 용량을 확인
df -h : 보기 좋게 보여줌
df . : 현재 디렉토리가 포함된 파티션의 남은 용량을 확인
du : 현재 디렉토리에서 서브디렉토리까지의 사용량을 확인
du -a : 현재 디렉토리의 사용량을 파일단위 출력
du -s : 총 사용량을 확인
du -h : 보기 좋게 바꿔줌
du -sh * : 한단계 서브디렉토리 기준으로 보여준다.
전체용량
df -P | grep -v ^Filesystem | awk '{sum += $2} END { print sum/1024/1024 " GB" }'
전체사용량
df -P | grep -v ^Filesystem | awk '{sum += $3} END { print sum/1024/1024 " GB" }'
전체남은 용량
df -P | grep -v ^Filesystem | awk '{sum += $4} END { print sum/1024/1024 " GB" }'
전체사용률
DISK_TOTAL=`df -P | grep -v ^Filesystem | awk '{sum += $2} END { print sum; }'`
DISK_USED=`df -P | grep -v ^Filesystem | awk '{sum += $3} END { print sum; }'`
DISK_PER=`echo "100*$DISK_USED/$DISK_TOTAL" | bc -l`
echo "$DISK_PER %"
;
전체용량
df -P | grep -v ^Filesystem | awk '{sum += $2} END { print sum/1024/1024 }'
전체사용량
df -P | grep -v ^Filesystem | awk '{sum += $3} END { print sum/1024/1024 }'
전체남은 용량
df -P | grep -v ^Filesystem | awk '{sum += $4} END { print sum/1024/1024 }'
전체사용률
DISK_TOTAL=`df -P | grep -v ^Filesystem | awk '{sum += $2} END { print sum; }'`
DISK_USED=`df -P | grep -v ^Filesystem | awk '{sum += $3} END { print sum; }'`
DISK_PER=`echo "100*$DISK_USED/$DISK_TOTAL" | bc -l`
echo "$DISK_PER %"
3. 구동중인 서비스 목록 조회
systemctl list-units --type service
4. 파일찾기
find / -name "*zookeeper*"
/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/jars/zookeeper-3.4.5-cdh5.15. 0.jar
find / -name "*yarn*"
find / -name "*sqoop*"
/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/jars/sqoop-submission-mapreduce-1.99.5-cdh5.15.0.jar
/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/jars/sqoop-tools-1.99.5-cdh5.15.0.jar
find / -name "*oozie*"
/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/jars/oozie-client-4.1.0-cdh5.15.0.jar
find / -name "*hdfs-site*"
# 현재 디렉토리에서 test가 들어가는 디렉토리 찾기
find / -name "*zookeeper*" -type d
/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/apps/zookeeper/src/zookeeper
find / -name "*apache-zookeeper*"
/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/zookeeper
/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/cloudera
########################
# #
# zookeeper #
# #
########################
1. 버전조회
1) 분석서버 접속
2) /opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/zookeeper/cloudera
3) cdh_version.properties 조회
########################
# #
# HUE #
# #
########################
1. JOB이 포함된 Workflow 조회
SELECT *
FROM hue.desktop_document2
WHERE 1=1
AND TYPE = 'oozie-workflow2'
AND DATA LIKE '%ORD_CTR_PRD_PFMC_D%'
ORDER BY LAST_MODIFIED DESC
########################
# #
# 파이썬 #
# #
########################
1. 실행전 공통 실행
파이참 실행시 공통 스크립트 수행 방법
파이참 세팅(Ctrl-Alt-S)을 열고,
Build, Execution, Deployment - Console - Python Console 메뉴로 들어간다.
하단에 Starting script에 두 줄 정도 기본 스크립트가 들어가 있을 것이다.
그곳에 넣고자 하는 스크립트를 기입한다.
예)
제일 아래에다가 !chcp 65001 을 추가한다.
########################
# #
# DBEVER #
# #
########################
1. CSV EXPORT
UTF-8 > EUC-KR 로 설정( 한글 깨짐 처리 )
0. 단편 선분이력 검증 - FOR IMPALA
SELECT B.*
FROM (
SELECT A.*
, CASE WHEN A.DATAB > A.DATBI THEN '1.종료일자보다 시작일자가 큼'
WHEN A.DATAB > A.CALED_ST_DT THEN '2.선분끊어짐'
WHEN A.DATAB < A.CALED_ST_DT THEN '3.선분겹침'
END AS ERR_CHK
FROM (
SELECT MST.BIZTM /* 영업점ID */
, MST.VERNO
, HST.DATAB /* 효력시작일 */
, HST.DATBI /* 효력종료일 */
, ADDDATE(TO_TIMESTAMP(CAST( LAG(HST.DATBI) OVER(PARTITION BY MST.BIZTM ORDER BY HST.DATAB) AS STRING), 'yyyyMMdd'),1) AS CALED_ST_DT
, HST.AEDAT
, MST.OPRTM /* 부문ID */
, MST.VKBUR /* 지점ID */
, MST.VKGRP /* 영업소ID */
, MST.BUKRS /* HY Paldo 구분코드 Y100,P100 */
, MST.MANDT
FROM SAPPRD.ZSDTOG05 MST /* 조직단위:조직구조 */
INNER JOIN SAPPRD.ZSDTOG06 HST /* 조직단위:조직구조 이력변경내역 */
ON MST.MANDT = HST.MANDT /* 클라이언트 기본값:100 */
AND MST.VERNO = HST.VERNO /* Version no */
) A
) B
WHERE 1=1
AND B.ERR_CHK IS NOT NULL
;
1. DEFAULT 컬럼값이 줄바꿈 존재하는 경우
TABLE_NAME = 'POS_RGLR_MKTG_SMS_HIS'
"SYSDATE
-- CONSTRAINT "POS_RGLR_MKTG_SMS_HIS" PRIMARY KEY ("SEQ")
-- USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
-- TABLESPACE "CCIP_INDEX1" ENABLE
"
;
2. 누적합
SELECT TO_CHAR(BIZ_DATE,'YYYYMM')
, PCODE_ID
, SUM(PAAMT)
, SUM(SUM(PAAMT)) OVER(PARTITION BY PCODE_ID)
, SUM(SUM(PAAMT)) OVER(ORDER BY TO_CHAR(BIZ_DATE,'YYYYMM')) AS CUM_PAAMT
, MIN(BIZ_DATE) AS BGN_BIZ_DATE
, MAX(BIZ_DATE) AS END_BIZ_DATE
, MAX(BIZ_DATE) - MIN(BIZ_DATE) AS WRK_DATE
FROM WEB_PCODE_SL_PFMC_I
WHERE 1=1
AND BIZTM_ID = '2001'
AND JIGU_ID = '08'
AND PCODE_ID = '05046054'
GROUP BY TO_CHAR(BIZ_DATE,'YYYYMM'), PCODE_ID
ORDER BY TO_CHAR(BIZ_DATE,'YYYYMM')
;
3. 누적합 예제 - 연속 애음기간
SELECT D.RGLR_ORD_CTR_ID
, D.CUM_SUM AS "구분"
, MONTHS_BETWEEN( TO_DATE(MAX(PFMC_YYMM),'YYYYMM'), TO_DATE(MIN(PFMC_YYMM),'YYYYMM')) AS "연속애음기간"
FROM (
SELECT C.RGLR_ORD_CTR_ID
, SUM(SUM(C.CHK)) OVER(ORDER BY C.PFMC_YYMM) AS CUM_SUM
, C.PFMC_YYMM
, MIN(C.PFMC_YYMM)
, MAX(C.PFMC_YYMM)
FROM (
SELECT B.PFMC_YYMM
, CASE WHEN B.PFMC_YYMM <> B.TEMP THEN 1
ELSE 0
END AS CHK
, B.RGLR_ORD_CTR_ID
FROM (
SELECT A.PFMC_YYMM
, A.PFMC_YYMM_PREV
, TO_CHAR(ADD_MONTHS( TO_DATE(A.PFMC_YYMM_PREV||'01','YYYYMMDD'),1),'YYYYMM') AS TEMP
, A.RGLR_ORD_CTR_ID
FROM (
SELECT RGLR_ORD_CTR_ID
, PFMC_YYMM
, LAG(PFMC_YYMM,1, TO_CHAR(ADD_MONTHS( TO_DATE(PFMC_YYMM||'01','YYYYMMDD'),-1),'YYYYMM')) OVER(ORDER BY PFMC_YYMM) AS PFMC_YYMM_PREV
FROM ORD_MM_SL_PFMC_M
WHERE 1=1
AND RGLR_ORD_CTR_ID = '20180605176463'
AND PRD_ID = '31337'
) A
) B
) C
GROUP BY C.RGLR_ORD_CTR_ID
, C.PFMC_YYMM
) D
WHERE 1=1
GROUP BY D.RGLR_ORD_CTR_ID
, D.CUM_SUM
;
3. 테이블 컬럼 리스트 산출시 에러
TG_AGENT_FEE
4.
WITH TMP AS (
SELECT 'A' AS ID, '101' AS GUBUN FROM DUAL
UNION ALL
SELECT 'A' AS ID, '201' AS GUBUN FROM DUAL
UNION ALL
SELECT 'A' AS ID, '301' AS GUBUN FROM DUAL
UNION ALL
SELECT 'B' AS ID, '101' AS GUBUN FROM DUAL
UNION ALL
SELECT 'B' AS ID, '201' AS GUBUN FROM DUAL
UNION ALL
SELECT 'B' AS ID, '301' AS GUBUN FROM DUAL
UNION ALL
SELECT 'C' AS ID, '301' AS GUBUN FROM DUAL
UNION ALL
SELECT 'C' AS ID, '301' AS GUBUN FROM DUAL
)
SELECT ID
, COUNT(*)
, COUNT(GUBUN)
, COUNT(DISTINCT GUBUN)
FROM TMP
GROUP BY ID
;
WITH TMP AS (
SELECT 'A' AS ID, '101' AS GUBUN
UNION ALL
SELECT 'A' AS ID, '201' AS GUBUN
UNION ALL
SELECT 'A' AS ID, '301' AS GUBUN
UNION ALL
SELECT 'B' AS ID, '101' AS GUBUN
UNION ALL
SELECT 'B' AS ID, '201' AS GUBUN
UNION ALL
SELECT 'B' AS ID, '301' AS GUBUN
UNION ALL
SELECT 'C' AS ID, '301' AS GUBUN
UNION ALL
SELECT 'C' AS ID, '301' AS GUBUN
)
SELECT ID
, COUNT(*)
, COUNT(GUBUN)
, COUNT(DISTINCT GUBUN)
FROM TMP
GROUP BY ID
;
5. NDV -> 추정치를 보여준다.정확한 값 아님
NDV(Number of Distinct Value)
6.
set num_nodes=1; to explain the single node plan
7. 오라클 DML 이력 보기
/* 오라클 DML 이력 보기 - COMMIT여부와 상관없이 DML 발생시 이력이 남음 */
SELECT *
FROM DBA_TAB_MODIFICATIONS
WHERE TABLE_NAME = 'WEB_PCODE_FEE_I'
;
8. GROUP BY 와 OVER() 동시 사용시.
GROUP BY 컬럼만 사용가능
GROUP BY 된 결과가 모집단이 된다.
예)
WITH TMP AS (
SELECT 'A' AS CST_ID, '일반' AS PAPAY_GNL_STS_TP_CD, '가' AS ETC FROM DUAL
UNION ALL
SELECT 'A' AS CST_ID, '후불' AS PAPAY_GNL_STS_TP_CD, '가' AS ETC FROM DUAL
UNION ALL
SELECT 'A' AS CST_ID, '후불' AS PAPAY_GNL_STS_TP_CD, '가' AS ETC FROM DUAL
UNION ALL
SELECT 'A' AS CST_ID, '선불' AS PAPAY_GNL_STS_TP_CD, '가' AS ETC FROM DUAL
)
SELECT CST_ID
, PAPAY_GNL_STS_TP_CD
, ROW_NUMBER() OVER(PARTITION BY CST_ID ORDER BY PAPAY_GNL_STS_TP_CD ) AS CNT
FROM TMP
GROUP BY CST_ID
, PAPAY_GNL_STS_TP_CD
;
/* 일별매출집계를 매출기간 선분이력으로 변경 */
SELECT B.*
FROM (
SELECT
A.BIZ_DATE /* 영업일자 */
, A.BIZ_DATE_PREV
, A.BIZ_BGN_DATE /* 영업시작일자 */
, LEAD(A.BIZ_BGN_DATE) OVER(ORDER BY A.BIZ_DATE) AS BIZ_END_DATE /* 영업종료일자 */
, A.BIZTM_ID /* 영업점코드 */
, A.JIGU_ID /* 지구코드 */
, A.PRD_ID /* 상품ID */
, A.PLTYP /* 판매유형코드 */
, A.PCODE_ID /* 판매점ID */
, A.PCODE_ID_PREV
, A.PCODE_ID_NEXT
, A.ZTYPE /* 가격리스트유형 */
, A.PAQTY /* 판매수량 */
, A.PAAMT /* 판매금액 */
, A.MEINS /* 상품단위 */
, A.INST_ID /* 등록자ID */
, A.INST_DTM /* 등록일시 */
, A.MDF_ID /* 수정자ID */
, A.MDF_DTM /* 수정일시 */
, A.SPART /* 제품군 */
FROM (
SELECT /*+PARALLEL(4) */
BIZ_DATE /* 영업일자 */
, LAG(BIZ_DATE) OVER(ORDER BY BIZ_DATE) AS BIZ_DATE_PREV
, LEAD(BIZ_DATE,1,TO_DATE('29991231','YYYYMMDD')) OVER(ORDER BY BIZ_DATE) AS BIZ_BGN_DATE
, BIZTM_ID /* 영업점코드 */
, JIGU_ID /* 지구코드 */
, PRD_ID /* 상품ID */
, PLTYP /* 판매유형코드 */
, PCODE_ID /* 판매점ID */
, LAG(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) AS PCODE_ID_PREV
, LEAD(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) AS PCODE_ID_NEXT
, ZTYPE /* 가격리스트유형 */
, PAQTY /* 판매수량 */
, PAAMT /* 판매금액 */
, MEINS /* 상품단위 */
, INST_ID /* 등록자ID */
, INST_DTM /* 등록일시 */
, MDF_ID /* 수정자ID */
, MDF_DTM /* 수정일시 */
, SPART /* 제품군 */
FROM WEB_PCODE_SL_PFMC_I
WHERE 1=1
AND BIZTM_ID = '2793'
AND JIGU_ID = '13'
AND PCODE_ID IS NOT NULL /* 해당지구에 매니저 정보가 없다면 매출로 산입하지 않음 */
) A
WHERE 1=1
AND ( A.PCODE_ID <> A.PCODE_ID_NEXT
OR A.PCODE_ID_PREV = 'NULL' )
) B
WHERE B.BIZ_END_DATE IS NOT NULL
;
/* 선분이력 생성 예제 */
3000617505 휴면시작 2019/03/06 01:30:51 2019/03/10 18:32:47
3000617505 휴면해제 2019/03/10 18:32:47 2020/03/10 01:31:59
3000617505 휴면시작 2020/03/10 01:31:59 2020/10/26 15:25:49
3000617505 휴면해제 2020/10/26 15:25:49 2021/10/27 01:30:07
3000617505 휴면시작 2021/10/27 01:30:07 2022/05/17 11:34:36
-- 월 마지막 상태값을 기준으로 함.
SELECT TO_CHAR(B.SOCAL_DATE , 'YYYYMM') AS STD_YM
, B.MEM_ID
, B.DORM_NM
FROM (
SELECT MST.SOCAL_DATE
, A.MEM_ID
, A.DORM_NM
, ROW_NUMBER() OVER(PARTITION BY TO_CHAR(MST.SOCAL_DATE , 'YYYYMM'), A.MEM_ID ORDER BY MST.SOCAL_DATE DESC ) AS RN
FROM ORD_SOLU_M MST
INNER JOIN (
SELECT MEM_ID
, DORM_NM
, INST_DTM AS START_DTM
, LEAD(INST_DTM,1,SYSDATE) OVER(ORDER BY INST_DTM) AS END_DTM
FROM WEB_MEM_DORM_HISTORY
) A
ON A.START_DTM <= MST.SOCAL_DATE
AND A.END_DTM >= MST.SOCAL_DATE
) B
WHERE B.RN = 1
ORDER BY B.MEM_ID, B.SOCAL_DATE
;
/* 선분이력테이블 월별로 나누기 */
SELECT A.MON, B.SDATE, B.EDATE, A.SDATE, A.EDATE, B.ETC
FROM MINA.TMONTH A -- 기준달력
, MINA.TSEGMENT B -- 선분이력 테이블
WHERE B.SDATE <= A.EDATE
AND B.EDATE >= A.SDATE
;
/* 일별매출집계를 매출기간 선분이력으로 변경 */
SELECT B.PCODE_ID
, MAX(B.BIZTM_ID) AS BIZTM_ID
, MAX(B.JIGU_ID) AS JIGU_ID
, MIN(B.BIZ_DATE) AS BIZ_BGN_DATE
, MAX(B.BIZ_DATE) AS BIZ_END_DATE
FROM (
SELECT A.PCODE_ID
, A.BIZTM_ID
, A.JIGU_ID
, A.BIZ_DATE
, SUM(A.CHK) OVER(ORDER BY A.BIZ_DATE) AS CUM_SUM
FROM (
SELECT /*+PARALLEL(4) */
BIZ_DATE /* 영업일자 */
, BIZTM_ID /* 영업점코드 */
, JIGU_ID /* 지구코드 */
, PCODE_ID /* 판매점ID */
, LAG(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) AS PCODE_ID_PREV
, LEAD(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) AS PCODE_ID_NEXT
, CASE WHEN PCODE_ID <> LAG(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) THEN 1
ELSE 0
END AS CHK
FROM WEB_PCODE_SL_PFMC_I
WHERE 1=1
AND BIZTM_ID = '2793'
AND JIGU_ID = '13'
AND PCODE_ID IS NOT NULL /* 해당지구에 매니저 정보가 없다면 매출로 산입하지 않음 */
AND BIZ_DATE >= TO_DATE('20170101','YYYYMMDD')
) A
) B
GROUP BY B.PCODE_ID
, B.CUM_SUM
ORDER BY MIN(B.BIZ_DATE)
;
/* 월별 FM */
SELECT D.STD_YYMM
, C.PCODE_ID
, MAX(C.BIZTM_ID) AS BIZTM_ID
, MAX(C.JIGU_ID) AS JIGU_ID
FROM (
/* 일별매출집계를 매출기간 선분이력으로 변경 */
SELECT B.PCODE_ID
, MAX(B.BIZTM_ID) AS BIZTM_ID
, MAX(B.JIGU_ID) AS JIGU_ID
, MIN(B.BIZ_DATE) AS BIZ_BGN_DATE
, MAX(B.BIZ_DATE) AS BIZ_END_DATE
FROM (
SELECT A.PCODE_ID
, A.BIZTM_ID
, A.JIGU_ID
, A.BIZ_DATE
, SUM(A.CHK) OVER(ORDER BY A.BIZ_DATE) AS CUM_SUM
FROM (
SELECT /*+PARALLEL(4) */
BIZ_DATE /* 영업일자 */
, BIZTM_ID /* 영업점코드 */
, JIGU_ID /* 지구코드 */
, PCODE_ID /* 판매점ID */
, LAG(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) AS PCODE_ID_PREV
, LEAD(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) AS PCODE_ID_NEXT
, CASE WHEN PCODE_ID <> LAG(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) THEN 1
ELSE 0
END AS CHK
FROM WEB_PCODE_SL_PFMC_I
WHERE 1=1
AND BIZTM_ID = '2793'
AND JIGU_ID = '13'
AND PCODE_ID IS NOT NULL /* 해당지구에 매니저 정보가 없다면 매출로 산입하지 않음 */
AND BIZ_DATE >= TO_DATE('20170101','YYYYMMDD') /* 20170101시점부터 적재 */
) A
) B
GROUP BY B.PCODE_ID
, B.CUM_SUM
ORDER BY MIN(B.BIZ_DATE)
) C
LEFT OUTER JOIN (
SELECT TO_CHAR(HDY_DATE, 'YYYYMM') AS STD_YYMM
, TO_DATE(TO_CHAR(HDY_DATE, 'YYYYMM')||'01','YYYYMMDD') AS FIRST_DATE
, LAST_DAY(MAX(HDY_DATE)) AS LAST_DATE
, COUNT(*) AS CNT
FROM CMM_HDY /* 휴일정보 */
WHERE USE_YN = 'Y'
AND HDY_TP_CD = '20' /* 20:은행휴일 */
GROUP BY TO_CHAR(HDY_DATE, 'YYYYMM')
ORDER BY TO_CHAR(HDY_DATE, 'YYYYMM')
) D
ON D.FIRST_DATE <= C.BIZ_END_DATE /* 월중 교체 된 경우는 제외된다. */
AND D.LAST_DATE >= C.BIZ_BGN_DATE
WHERE 1=1
AND D.STD_YYMM IS NOT NULL
GROUP BY D.STD_YYMM
, C.PCODE_ID
ORDER BY STD_YYMM
;
/* 월별 테이블의 대표 테이블 계산 */
SELECT B.TABLE_NAME
, B.STD_TABLE_NAME
FROM (
SELECT A.TABLE_NAME
, A.STD_TABLE_NAME
, ROW_NUMBER () OVER (PARTITION BY A.STD_TABLE_NAME ORDER BY A.TABLE_NAME )AS RN
FROM (
SELECT TABLE_NAME
, REGEXP_REPLACE(TABLE_NAME, '[0-9]{6,8}', '1' ) AS STD_TABLE_NAME
FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME NOT LIKE 'BIN$%'
AND OWNER IN ('CCIP', '00CRM', 'GIS', 'OEM_DBSV1', 'HUMUSON', 'ORDDATA', 'AMAIL', 'ANL', 'HYFACTORY' )
AND REGEXP_LIKE ( TABLE_NAME , '[0-9]{6}+')
) A
) B
WHERE B.RN = 1
;
/* 월별생성 테이블을 제외한 테이블 목록 */
SELECT A.TABLE_NAME
, A.TABLE_NAME AS STD_TABLE_NAME
FROM ALL_TAB_COMMENTS A
WHERE NOT EXISTS (
SELECT 1
FROM ALL_TAB_COMMENTS B
WHERE B.TABLE_NAME NOT LIKE 'BIN$%'
AND B.OWNER IN ('CCIP', '00CRM', 'GIS', 'OEM_DBSV1', 'HUMUSON', 'ORDDATA', 'AMAIL', 'ANL', 'HYFACTORY' )
AND REGEXP_LIKE ( B.TABLE_NAME , '[0-9]{6}+')
AND A.TABLE_NAME = B.TABLE_NAME
)
AND A.TABLE_NAME NOT LIKE 'BIN$%'
AND A.OWNER IN ('CCIP', '00CRM', 'GIS', 'OEM_DBSV1', 'HUMUSON', 'ORDDATA', 'AMAIL', 'ANL', 'HYFACTORY' )
;
/* 월별 테이블의 대표 테이블 계산 */
SELECT B.TABLE_NAME
FROM (
SELECT A.TABLE_NAME
, A.STD_TABLE_NAME
, ROW_NUMBER () OVER (PARTITION BY A.STD_TABLE_NAME ORDER BY A.TABLE_NAME )AS RN
FROM (
SELECT TABLE_NAME
, REGEXP_REPLACE(TABLE_NAME, '[0-9]{6,8}', '1' ) AS STD_TABLE_NAME
FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME NOT LIKE 'BIN$%'
AND OWNER IN ('CCIP', '00CRM', 'GIS', 'OEM_DBSV1', 'HUMUSON', 'ORDDATA', 'AMAIL', 'ANL', 'HYFACTORY' )
AND REGEXP_LIKE ( TABLE_NAME , '[0-9]{6}+')
) A
) B
WHERE B.RN = 1
UNION ALL
/* 월별생성 테이블을 제외한 테이블 목록 */
SELECT A.TABLE_NAME
FROM ALL_TAB_COMMENTS A
WHERE NOT EXISTS (
SELECT 1
FROM ALL_TAB_COMMENTS B
WHERE B.TABLE_NAME NOT LIKE 'BIN$%'
AND B.OWNER IN ('CCIP', '00CRM', 'GIS', 'OEM_DBSV1', 'HUMUSON', 'ORDDATA', 'AMAIL', 'ANL', 'HYFACTORY' )
AND REGEXP_LIKE ( B.TABLE_NAME , '[0-9]{6}+')
AND A.TABLE_NAME = B.TABLE_NAME
)
AND A.TABLE_NAME NOT LIKE 'BIN$%'
AND A.OWNER IN ('CCIP', '00CRM', 'GIS', 'OEM_DBSV1', 'HUMUSON', 'ORDDATA', 'AMAIL', 'ANL', 'HYFACTORY' )