1.Oracle建表
drop table T_PSSC_TRAN_PORTRAIT_CENTER;
create table T_PSSC_TRAN_PORTRAIT_CENTER
(
OBJ_ID VARCHAR(50) DEFAULT sys_guid() PRIMARY KEY,
TRAN_ID VARCHAR(50),
TRAN_NAME VARCHAR(255),
ORG_CITY_NAME VARCHAR(255),
ORG_CITY_ID VARCHAR(50),
ORG_COUNTY_NAME VARCHAR(255),
ORG_COUNTY_ID VARCHAR(50),
ORG_MAINT_GROUP_NAME VARCHAR(255),
ORG_MAINT_GROUP_ID VARCHAR(50),
PSR_RATE NUMBER(16,4),
PSR_TAL_DUR NUMBER(16,4),
PSR_FAILT_TAL_DUR NUMBER(16,4),
PSR_FAILT_TMS NUMBER(16),
PSR_FAILT_AVG_DUR NUMBER(16,4),
PSR_PLAN_TAL_DUR NUMBER(16,4),
PSR_PLAN_TMS NUMBER(16),
PSR_PLAN_AVG_DUR NUMBER(16,4),
PSA_TAL_TMS NUMBER(16),
PSA_HEAVY_TMS NUMBER(16),
PSA_OVER_TMS NUMBER(16),
PSA_TAL_DUR NUMBER(16,4),
PSA_HEAVY_DUR NUMBER(16,4),
PSA_OVER_DUR NUMBER(16,4),
PSA_SINGLE_MAX_DUR NUMBER(16,4),
PSA_SINGLE_HMAX_DUR NUMBER(16,4),
PSA_SINGLE_OMAX_DUR NUMBER(16,4),
PSA_MAX_LOAD_RATE NUMBER(16,4),
PQ_L_VOLT_TMS NUMBER(16),
PQ_O_VOLT_TMS NUMBER(16),
PQ_UNBALA_TMS NUMBER(16),
PQ_L_VOLT_DUR NUMBER(16,4),
PQ_O_VOLT_DUR NUMBER(16,4),
PQ_UNBALA_DUR NUMBER(16,4),
PQ_L_VOLT_MAX_DUR NUMBER(16,4),
PQ_O_VOLT_MAX_DUR NUMBER(16,4),
PQ_UNBALA_MAX_DUR NUMBER(16,4),
RUN_DATE date,
CV_PATROL_TMS NUMBER(16),
CV_PATROL_DEFECT_NUM NUMBER(16),
CV_DETECTION_TMS NUMBER(16),
CV_DETECTION_ABNORMAL_NUM NUMBER(16),
CV_MAINTPLAN_TMS NUMBER(16),
CV_MAINTPLAN_DUR NUMBER(16,4),
CV_REPAIR_TMS NUMBER(16),
CV_REPAIR_DUR NUMBER(16,4),
CV_REPAIR_AVG_DUR NUMBER(16,4),
CV_DEFECT_TMS NUMBER(16),
CV_DEFECT_FIX_DUR NUMBER(16,4),
CV_DEFECT_FIX_AVG_DUR NUMBER(16,4),
CV_SENSITIVE_NUM NUMBER(16),
PR_CMON_AB_DEV_NUM NUMBER(16),
PR_CMON_DEFECT_NUM NUMBER(16),
PR_DC_CHARGED_TMS NUMBER(16),
PR_DC_CHARGED_AB_TMS NUMBER(16),
PR_DC_ROUTINE_TMS NUMBER(16),
PR_DC_ROUTINE_AB_TMS NUMBER(16),
PR_DC_DIAG_TMS NUMBER(16),
PR_DC_DIAG_AB_TMS NUMBER(16),
STAT_DATE NUMBER(6),
INS_TIME DATE default sysdate
);
comment on table T_PSSC_TRAN_PORTRAIT_CENTER is '变压器设备中心';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.OBJ_ID is '唯一表示';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.TRAN_ID is '设备编码';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.TRAN_NAME is '设备名称';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.ORG_CITY_NAME is '设备地市';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.ORG_CITY_ID is '设备地市编码';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.ORG_COUNTY_NAME is '设备县公司';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.ORG_COUNTY_ID is '设备县公司编码';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.ORG_MAINT_GROUP_NAME is '设备班组';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.ORG_MAINT_GROUP_ID is '设备班组编码';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSR_RATE is '供电可靠率';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSR_TAL_DUR is '停电总时常';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSR_FAILT_TAL_DUR is '故障停电时常';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSR_FAILT_TMS is '故障停电次数 ';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSR_FAILT_AVG_DUR is '故障平均停电时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSR_PLAN_TAL_DUR is '计划停电时常';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSR_PLAN_TMS is '计划停电次数 ';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSR_PLAN_AVG_DUR is '计划平均停电时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_TAL_TMS is '异常次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_HEAVY_TMS is '重载次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_OVER_TMS is '过载次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_TAL_DUR is '异常总时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_HEAVY_DUR is '重载时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_OVER_DUR is '过载总时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_SINGLE_MAX_DUR is '单次一次最大时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_SINGLE_HMAX_DUR is '单次重载最大时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_SINGLE_OMAX_DUR is '单次过载最大时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PSA_MAX_LOAD_RATE is '最大负载率';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PQ_L_VOLT_TMS is '低电压次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PQ_O_VOLT_TMS is '过电压次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PQ_UNBALA_TMS is '三相不平衡次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PQ_L_VOLT_DUR is '低电压总时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PQ_O_VOLT_DUR is '过电压总时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PQ_UNBALA_DUR is '三相不平衡总时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PQ_L_VOLT_MAX_DUR is '单次低电压最大时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PQ_O_VOLT_MAX_DUR is '单次过电压最大时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PQ_UNBALA_MAX_DUR is '单次三相不平衡最大时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.RUN_DATE is '投运日期';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_PATROL_TMS is '巡视次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_PATROL_DEFECT_NUM is '巡视发现缺陷数量';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_DETECTION_TMS is '检测次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_DETECTION_ABNORMAL_NUM is '检测结果异常数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_MAINTPLAN_TMS is '检修次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_MAINTPLAN_DUR is '检修总时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_REPAIR_TMS is '抢修次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_REPAIR_DUR is '抢修修复时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_REPAIR_AVG_DUR is '平均修复时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_DEFECT_TMS is '缺陷次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_DEFECT_FIX_DUR is '消缺时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_DEFECT_FIX_AVG_DUR is '平均消缺时长';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.CV_SENSITIVE_NUM is '敏感用户数量';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PR_CMON_AB_DEV_NUM is '异常设备数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PR_CMON_DEFECT_NUM is '关联缺陷数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PR_DC_CHARGED_TMS is '带电检测次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PR_DC_CHARGED_AB_TMS is '带电检测异常次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PR_DC_ROUTINE_TMS is '例行试验次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PR_DC_ROUTINE_AB_TMS is '例行试验异常次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PR_DC_DIAG_TMS is '诊断性试验次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.PR_DC_DIAG_AB_TMS is '诊断性试验异常次数';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.STAT_DATE is '统计日期(yyyymm)';
comment on column T_PSSC_TRAN_PORTRAIT_CENTER.INS_TIME is '写入时间';
2.Hive建表
CREATE TABLE PDR.T_PSSC_TRAN_PORTRAIT_CENTER (
TRAN_ID STRING comment '设备编码'
,TRAN_NAME STRING comment '设备名称'
,ORG_CITY_NAME STRING comment '设备地市'
,ORG_CITY_ID STRING comment '设备地市编码'
,ORG_COUNTY_NAME STRING comment '设备县公司'
,ORG_COUNTY_ID STRING comment '设备县公司编码'
,ORG_MAINT_GROUP_NAME STRING comment '设备班组'
,ORG_MAINT_GROUP_ID STRING comment '设备班组编码'
,PSR_RATE DECIMAL(16,4) comment '供电可靠率'
,PSR_TAL_DUR DECIMAL(16,4) comment '停电总时常'
,PSR_FAILT_TAL_DUR DECIMAL(16,4) comment '故障停电时常'
,PSR_FAILT_TMS INT comment '故障停电次数'
,PSR_FAILT_AVG_DUR DECIMAL(16,4) comment '故障平均停电时长'
,PSR_PLAN_TAL_DUR DECIMAL(16,4) comment '计划停电时常'
,PSR_PLAN_TMS INT comment '计划停电次数'
,PSR_PLAN_AVG_DUR DECIMAL(16,4) comment '计划平均停电时长'
,PSA_TAL_TMS INT comment '异常次数'
,PSA_HEAVY_TMS INT comment '重载次数'
,PSA_OVER_TMS INT comment '过载次数'
,PSA_TAL_DUR DECIMAL(16,4) comment '异常总时长'
,PSA_HEAVY_DUR DECIMAL(16,4) comment '重载时长'
,PSA_OVER_DUR DECIMAL(16,4) comment '过载总时长'
,PSA_SINGLE_MAX_DUR DECIMAL(16,4) comment '单次一次最大时长'
,PSA_SINGLE_HMAX_DUR DECIMAL(16,4) comment '单次重载最大时长'
,PSA_SINGLE_OMAX_DUR DECIMAL(16,4) comment '单次过载最大时长'
,PSA_MAX_LOAD_RATE DECIMAL(16,4) comment '最大负载率'
,PQ_L_VOLT_TMS INT comment '低电压次数'
,PQ_O_VOLT_TMS INT comment '过电压次数'
,PQ_UNBALA_TMS INT comment '三相不平衡次数'
,PQ_L_VOLT_DUR DECIMAL(16,4) comment '低电压总时长'
,PQ_O_VOLT_DUR DECIMAL(16,4) comment '过电压总时长'
,PQ_UNBALA_DUR DECIMAL(16,4) comment '三相不平衡总时长'
,PQ_L_VOLT_MAX_DUR DECIMAL(16,4) comment '单次低电压最大时长'
,PQ_O_VOLT_MAX_DUR DECIMAL(16,4) comment '单次过电压最大时长'
,PQ_UNBALA_MAX_DUR DECIMAL(16,4) comment '单次三相不平衡最大时长'
,RUN_DATE STRING comment '投运日期'
,CV_PATROL_TMS INT comment '巡视次数'
,CV_PATROL_DEFECT_NUM INT comment '巡视发现缺陷数量'
,CV_DETECTION_TMS INT comment '检测次数'
,CV_DETECTION_ABNORMAL_NUM INT comment '检测结果异常数'
,CV_MAINTPLAN_TMS INT comment '检修次数'
,CV_MAINTPLAN_DUR DECIMAL(16,4) comment '检修总时长'
,CV_REPAIR_TMS INT comment '抢修次数'
,CV_REPAIR_DUR DECIMAL(16,4) comment '抢修修复时长'
,CV_REPAIR_AVG_DUR DECIMAL(16,4) comment '平均修复时长'
,CV_DEFECT_TMS INT comment '缺陷次数'
,CV_DEFECT_FIX_DUR DECIMAL(16,4) comment '消缺时长'
,CV_DEFECT_FIX_AVG_DUR DECIMAL(16,4) comment '平均消缺时长'
,CV_SENSITIVE_NUM INT comment '敏感用户数量'
,PR_CMON_AB_DEV_NUM INT comment '异常设备数'
,PR_CMON_DEFECT_NUM INT comment '关联缺陷数'
,PR_DC_CHARGED_TMS INT comment '带电检测次数'
,PR_DC_CHARGED_AB_TMS INT comment '带电检测异常次数'
,PR_DC_ROUTINE_TMS INT comment '例行试验次数'
,PR_DC_ROUTINE_AB_TMS INT comment '例行试验异常次数'
,PR_DC_DIAG_TMS INT comment '诊断性试验次数'
,PR_DC_DIAG_AB_TMS INT comment '诊断性试验异常次数'
,STAT_DATE STRING comment '记录月份'
)
COMMENT '变压器设备中心'
PARTITIONED BY ( dt STRING COMMENT '写入时间(天)' )
STORED AS PARQUET;