-- 空调操作记录表 create table ods_bus_feedback ( id STRING comment '主键', project_id STRING comment '项目ID', object_id STRING comment '空间ID', source_type STRING comment '反馈来源分类', user_id STRING, user_phone STRING, user_name STRING, `value_type` STRING comment '调节方式:1按调节项2按精确值', item_id STRING comment '吐槽内容ID', value DECIMAL(15, 5), create_time STRING, next_open_time STRING comment '下次开启时间', model STRING comment '反馈模式 1.一般模式 2.专业模式', duration_type STRING comment '时效, 1.未来两个小时 2.未来24小时(不包含非定制区间)3.每天此刻都保持该温度', custom_plan STRING comment '温度计划', curr_temp decimal(4, 2) comment '反馈实际温度', nick_name STRING comment '反馈人微信昵称', result STRING comment '控制模块获取结果是否成功', exe_result STRING comment '控制模块返回结果', fb_temp decimal(4, 2) comment '反馈后目标温度', remark STRING comment '备注' ) COMMENT '空调操作记录表' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/tmp/ods_bus_feedback' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 照明操作记录 create table saga_dw.ods_bus_light_switch ( id STRING comment '主键', project_id STRING comment '项目ID', object_id STRING comment '空间ID', source_type STRING comment '反馈来源分类', user_id STRING , user_phone STRING , user_name STRING , on_off STRING comment '1开 0关', result STRING comment '1成功 0失败', create_time STRING, exe_result integer comment '执行结果' ) COMMENT '照明操作记录' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_bus_light_switch' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 项目信息 create table saga_dw.ods_object_data_project ( id STRING comment '项目id', secret STRING comment '', `name` STRING comment '', local_id STRING comment '', local_name STRING comment '', longitude STRING comment '经度', latitude STRING comment '纬度', distance STRING comment '', city_id STRING comment '', is_duoduo STRING comment '', `status` STRING comment '0:无效,1:有效' ) COMMENT '项目信息' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_project' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 建筑信息 create table saga_dw.ods_object_data_building ( id STRING, name STRING, local_id STRING, local_name STRING, project_id STRING, `status` STRING ) COMMENT '建筑信息' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_building' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 空间 create table saga_dw.ods_object_data_space ( id STRING, name STRING comment '', local_id STRING comment '', local_name STRING comment '', room_func_type STRING comment '', group_id STRING comment '', bim_location STRING comment '', room_q_r_code STRING comment '', business_type STRING comment '业务区域编码', function_code STRING comment '', out_line STRING comment '', pic STRING comment '', position_img STRING comment '', position_img_upload_type STRING comment '', position_img_create_time STRING comment '', project_id STRING comment '', building_id STRING comment '', floor_id STRING comment '', `status` STRING comment '', is_controlled STRING comment '是否为服务定制受控对象', is_chiller_monitor STRING comment '是否冷站智控监测 1:是 0:不是', has_window STRING comment '是否有窗户', has_defect STRING comment '是否有工程缺陷', region STRING comment '内外区', zone_orientation STRING comment '朝向,东西南北', remark STRING comment '备注', tenant_id STRING comment '租户id', room_serial STRING comment '空间序号', monitor_remark STRING comment '日常数据监控的空间备注', light_group_id STRING comment '照明分组ID' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_space' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 空间组 create table saga_dw.ods_object_data_space_group ( id STRING, project_id STRING comment '', name STRING comment '', `status` STRING comment '', `readonly` STRING comment '', update_time STRING comment '', type STRING comment '组类型,默认0 空调;1照明;其它...', update_user STRING comment '', remark STRING comment '' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_space_group' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 楼层 create table saga_dw.ods_object_data_floor ( id STRING comment '', name STRING comment '', local_id STRING comment '', local_name STRING comment '', floor_map STRING comment '', project_id STRING comment '', building_id STRING comment '', floor_sequence_id integer comment '', `status` STRING comment '' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_floor' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 设备 create table saga_dw.ods_object_data_equipment ( id STRING comment '', name STRING comment '', local_id STRING comment '', local_name STRING comment '', major_category STRING comment '', system_category STRING comment '', equipment_category STRING comment '', bim_location STRING comment '', run_status STRING comment '', equip_q_r_code STRING comment '', install_location STRING comment '', position_img STRING comment '', position_img_upload_type STRING comment '', position_img_create_time STRING comment '', project_id STRING comment '', building_id STRING comment '', `status` STRING comment '' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_equipment' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 设备信息 create table saga_dw.ods_object_data_equipment_infos ( id STRING comment '', project_id STRING comment '', category STRING comment '', infos STRING comment '', body STRING comment '' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_equipment_infos' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 关系表 create table saga_dw.ods_object_data_relation ( id STRING comment '', project_id STRING comment '', graph_id STRING comment '', from_id STRING comment '', rel_type STRING comment '', to_id STRING comment '', infos STRING comment '' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_relation' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 租户 create table saga_dw.ods_object_data_tenant ( id STRING comment '', name STRING comment '', local_id STRING comment '', local_name STRING comment '', project_id STRING comment '', `status` STRING comment '' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_tenant' TBLPROPERTIES ( 'orc.compress'='lzo'); create table saga_dw.ods_custom_content_real ( id STRING comment '', project_id STRING comment '', space_id STRING comment '', group_id STRING comment '', `date` STRING comment '', `time` STRING comment '', item STRING comment '', time_point STRING comment '', fixed_value double comment '', max_value double comment '', min_value double comment '', create_user_id STRING comment '', create_time STRING comment '', update_user_id STRING comment '', update_time STRING comment '', update_source STRING comment '更新来源 0 初始化配置 1、反馈算法调整季节 2、反馈算法调整日期 3、临时服务定制 4、提前开机 5、小程序捏曲线', is_beforehand STRING comment '提前开机1 不提前开机0', custom_type STRING comment '0 初始化 1 算法季节 2算法日期 3 app 4 临时 5灯加班 关机情况有初始服务定制默认0' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_custom_content_real' TBLPROPERTIES ( 'orc.compress'='lzo'); create table saga_dw.ods_custom_persist_target ( id STRING comment '', project_id STRING comment '', space_id STRING comment '', `date` STRING comment '', `time` STRING comment '', temperature_max double comment '', temperature_min double comment '', humidity_max double comment '', humidity_min double comment '', co2_max double comment '', co2_min double comment '', hcho_max double comment '', hcho_min double comment '', pm25_max double comment '', pm25_min double comment '', sun_fixed double comment '', create_time STRING comment '', is_beforehand STRING comment '' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_custom_persist_target' TBLPROPERTIES ( 'orc.compress'='lzo'); -- 用户信息 create table saga_dw.ods_user ( id STRING comment '', name STRING comment '用户名 游客用户没有名字', company_id STRING comment '所属企业ID', type STRING comment '类型:1,普通租户;2,租户行政;3,运营方;4,访客', `status` STRING comment '用户状态【0:未入驻/1:审核中/2:已入驻激活/3:已入驻待激活/4:已入驻冻结/5:已入驻作废】', create_date STRING comment '创建时间', update_date STRING comment '更新时间', dep_name STRING comment '', job STRING comment '职务', phone STRING comment '', manage_user_type STRING comment '成员类型:1-正式成员, 2-临时成员', first_use_time STRING comment '首次使用时间(入住、激活时的时间)' ) COMMENT '' PARTITIONED BY (`dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE LOCATION 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_user' TBLPROPERTIES ( 'orc.compress'='lzo');