/** 能源原始数据 15min 历史数据 */ create table ods_energy_15_min ( building string, func_id string, meter string, data_time string, data_value decimal(30, 15) ) COMMENT '能源原始数据15min历史数据' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_15_min' TBLPROPERTIES ( 'orc.compress'='lzo'); /** 能源原始数据 5min 历史数据 */ create table ods_energy_5_min ( building string, func_id string, meter string, data_time string, data_value decimal(30, 15) ) COMMENT '能源原始数据15min历史数据' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min' TBLPROPERTIES ( 'orc.compress'='lzo'); /** 能源数据 15min */ create table ods_energy_15_min_fjd ( building string, func_id string, meter string, data_time string, data_value decimal(30, 15) ) COMMENT '能源原始数据 15min' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_15_min_fjd' TBLPROPERTIES ( 'orc.compress'='lzo'); /** 能源数据 5min */ create table ods_energy_5_min_fjd ( building string, func_id string, meter string, data_time string, data_value decimal(30, 15) ) COMMENT '能源原始数据 5min' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min_fjd' TBLPROPERTIES ( 'orc.compress'='lzo'); create table saga_dw.dwd_energy_15_min_hour ( building string, func_id string, branch_type string, branch_type_name string, value_sum decimal(30, 15), current_hour string comment '小时', current_year integer comment '年份', year_mouth string comment '年份-月份', week_of_year integer comment '这一年的第几周' ) COMMENT '能源原始数据15min按小时维度数据' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/dwd/dwd_energy_15_min_hour' TBLPROPERTIES ( 'orc.compress' = 'lzo');