dwh_saga/task_day/energy_hive.sql

create database saga_dw location "/saga/saga_dw";

/** 能源原始数据 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://HDFS85113:8020/saga/saga_dw/ods/ods_energy_15_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://HDFS85113:8020/saga/saga_dw/ods/ods_energy_15_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,
    use_range_type 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://HDFS85113:8020/saga/saga_dw/dwd/dwd_energy_15_min_hour'
    TBLPROPERTIES (
        'orc.compress' = 'lzo');


/** CO2 15min 分精度 */
create table ods_co2_15_min
(
    building   string,
    func_id    string,
    meter      string,
    data_time  string,
    data_value decimal(30, 15)
) COMMENT 'CO2 15min 分精度'
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://HDFS85113:8020/saga/saga_dw/ods/ods_co2_15_min'
    TBLPROPERTIES (
        'orc.compress'='lzo');

/** PM2.5 15min 分精度 */
create table ods_pm25_15_min
(
    building   string,
    func_id    string,
    meter      string,
    data_time  string,
    data_value decimal(30, 15)
) COMMENT 'PM2.5 15min 分精度'
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://HDFS85113:8020/saga/saga_dw/ods/ods_pm25_15_min'
    TBLPROPERTIES (
        'orc.compress'='lzo');

/** 甲醛 15 分钟分精度 */
create table ods_hcho_15_min
(
    building   string,
    func_id    string,
    meter      string,
    data_time  string,
    data_value decimal(30, 15)
) COMMENT '甲醛 15 分钟分精度'
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://HDFS85113:8020/saga/saga_dw/ods/ods_hcho_15_min'
    TBLPROPERTIES (
        'orc.compress'='lzo');

/** 温度 15 分钟分精度 */
create table ods_temperature_15_min
(
    building   string,
    func_id    string,
    meter      string,
    data_time  string,
    data_value decimal(30, 15)
) COMMENT '温度 15 分钟分精度'
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://HDFS85113:8020/saga/saga_dw/ods/ods_temperature_15_min'
    TBLPROPERTIES (
        'orc.compress'='lzo');

/** 湿度 15 分钟分精度 */
create table ods_humidity_15_min
(
    building   string,
    func_id    string,
    meter      string,
    data_time  string,
    data_value decimal(30, 15)
) COMMENT '湿度 15 分钟分精度'
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://HDFS85113:8020/saga/saga_dw/ods/ods_humidity_15_min'
    TBLPROPERTIES (
        'orc.compress'='lzo');