create database etl default character set utf8 collate utf8_general_ci;
create user 'etl'@'%' identified by 'Aip20ju1!';
grant all privileges on etl.* to 'etl'@'%';
flush privileges;

create table ETL_JOB (
  JOB_ID bigint not null auto_increment comment '작업ID',
  EXEC_DT char(8) not null comment '실행일자',
  JOB_NM varchar(255) not null comment '작업명',
  STAT int not null default -1 comment '상태(-1:실행중,0:완료,1<=:실패)',
  STA_DTM timestamp not null default current_timestamp comment '시작일시',
  END_DTM timestamp NULL default NULL comment '종료일시',
  SQP_CNT bigint not null default 0 comment 'Sqoop건수',
  constraint PK_ETL_JOB PRIMARY KEY (JOB_ID),
  KEY ETL_JOB_IDX1 (EXEC_DT,JOB_NM)
)
ENGINE=InnoDB DEFAULT charset=utf8 comment='ETL작업';


create table ETL_DD_JOB (
  EXEC_DT char(8) not null comment '실행일자',
  JOB_NM varchar(255) not null comment '작업명',
  JOB_ID bigint not null comment '작업ID',
  STAT int not null comment '상태(-1:실행중,0:완료,1<=:실패)',
  STA_DTM timestamp NULL default NULL comment '시작일시',
  END_DTM timestamp NULL default NULL comment '종료일시',
  SQP_CNT bigint not null default 0 comment 'Sqoop건수',
  constraint PK_ETL_DD_JOB PRIMARY KEY (EXEC_DT, JOB_NM)
)
ENGINE=InnoDB DEFAULT charset=utf8 comment='ETL작업';


create table ETL_LOG (
  JOB_ID bigint not null comment '작업ID',
  JOB_LOG mediumtext default NULL comment '작업로그',
  constraint PK_ETL_LOG PRIMARY KEY (JOB_ID)
  /*,CONSTRAINT ETL_LOG_FK1 FOREIGN KEY (JOB_ID) REFERENCES ETL_JOB (JOB_ID)*/
)
ENGINE=InnoDB DEFAULT charset=utf8 comment='ETL로그';


create table ETL_INFO (
  SRC_TBL varchar(255) not null comment '소스테이블',
  HIV_TBL varchar(255) not null comment 'Hive테이블',
  SQP_JOB varchar(255) not null comment 'Sqoop작업',
  HIV_JOB varchar(255) not null comment 'Hive작업',
  SRC_CON varchar(20) not null comment '소스연결(properties prefix)',
  EXEC_DV varchar(4) not null comment '실행구분(D:매일,M:매월,B:영업일,S:일요일,0:당일,1:전일,2:전전일)',
  LOAD_DV char(1) not null comment '적재구분(A:전체적재,D:일적재,M:월적재,C:변경적재)',
  FROM_VLU varchar(8) default null comment 'FROM값(년월,년월일,D:일,M:월,Y:년)',
  TO_VLU varchar(8) default null comment 'TO값(B:영업일,D:일)',
  SRC_COL varchar(255) default null comment '소스컬럼',
  SRC_ADD_TRCN text default null comment '소스추가조건',
  SRC_CNT_SQL text comment '소스건수SQL(#VALUE# or #COND# 필수)',
  SRC_GRP_SQL text comment '소스그룹SQL(#VALUE# or #COND# 필수)',
  HIV_COL varchar(255) default null comment 'Hive컬럼',
  HIV_ADD_TRCN text default null comment 'Hive추가조건',
  SQP_MAPS int not null default 1 comment 'Sqoop매퍼수',
  SQP_TRCN text default null comment 'Sqoop조건',
  APCL_DT char(8) not null COMMENT '적용일자',
  RMK text comment '비고',
  CMP_IGN_COLS text comment '비교무시컬럼들',
  REG_DTM timestamp not null default current_timestamp comment '등록일시',
  constraint PK_ETL_INFO PRIMARY KEY (SRC_TBL)
)
ENGINE=InnoDB DEFAULT charset=utf8 comment='ETL정보';

create table ETL_DD_CNT (
  EXEC_DT char(8) not null comment '실행일자',
  SRC_TBL varchar(255) not null comment '소스테이블',
  HIV_TBL varchar(255) not null comment 'Hive테이블',
  SRC_TRCN text default null comment '소스조건',
  HIV_TRCN text default null comment 'Hive조건',
  SRC_CNT bigint not null default 0 comment '소스건수',
  HIV_CNT bigint not null default 0 comment 'Hive건수',
  HIV_GRP_CNT bigint default null comment 'Hive그룹건수',
  CNT_DFRC bigint not null default 0 comment '건수차이(소스-Hive)',
  SRC_ACHV_DRTM bigint not null default 0 comment '소스수행시간(밀리초)',
  HIV_ACHV_DRTM bigint not null default 0 comment 'Hive수행시간(밀리초)',
  SRC_GRP_ACHV_DRTM bigint default null comment '소스그룹수행시간(밀리초)',
  HIV_GRP_ACHV_DRTM bigint default null comment 'Hive그룹수행시간(밀리초)',
  EXEC_DTM timestamp not null default current_timestamp comment '실행일시',
  constraint PK_ETL_DD_CNT PRIMARY KEY (EXEC_DT, SRC_TBL)
)
ENGINE=InnoDB DEFAULT charset=utf8 comment='ETL일별건수';

create table ETL_DD_GRP_CNT (
  EXEC_DT char(8) not null comment '실행일자',
  SRC_TBL varchar(255) not null comment '소스테이블',
  SRC_COL varchar(255) not null comment '소스컬럼',
  SRC_VLU varchar(255) not null comment '소스값',
  SRC_CNT bigint not null default 0 comment '소스건수',
  HIV_CNT bigint not null default 0 comment 'Hive건수',
  CNT_DFRC bigint not null default 0 comment '건수차이(소스-Hive)',
  EXEC_DTM timestamp not null default current_timestamp comment '실행일시',
  constraint PK_ETL_DD_GRP_CNT PRIMARY KEY (EXEC_DT, SRC_TBL, SRC_VLU)
)
ENGINE=InnoDB DEFAULT charset=utf8 comment='ETL일별그룹건수';
