123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348 |
- -- 空调操作记录表
- 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');
|