bus_hive.sql 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348
  1. -- 空调操作记录表
  2. create table ods_bus_feedback
  3. (
  4. id STRING comment '主键',
  5. project_id STRING comment '项目ID',
  6. object_id STRING comment '空间ID',
  7. source_type STRING comment '反馈来源分类',
  8. user_id STRING,
  9. user_phone STRING,
  10. user_name STRING,
  11. `value_type` STRING comment '调节方式:1按调节项2按精确值',
  12. item_id STRING comment '吐槽内容ID',
  13. value DECIMAL(15, 5),
  14. create_time STRING,
  15. next_open_time STRING comment '下次开启时间',
  16. model STRING comment '反馈模式 1.一般模式 2.专业模式',
  17. duration_type STRING comment '时效, 1.未来两个小时 2.未来24小时(不包含非定制区间)3.每天此刻都保持该温度',
  18. custom_plan STRING comment '温度计划',
  19. curr_temp decimal(4, 2) comment '反馈实际温度',
  20. nick_name STRING comment '反馈人微信昵称',
  21. result STRING comment '控制模块获取结果是否成功',
  22. exe_result STRING comment '控制模块返回结果',
  23. fb_temp decimal(4, 2) comment '反馈后目标温度',
  24. remark STRING comment '备注'
  25. ) COMMENT '空调操作记录表'
  26. PARTITIONED BY (`dt` string)
  27. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  28. STORED AS TEXTFILE
  29. LOCATION
  30. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/tmp/ods_bus_feedback'
  31. TBLPROPERTIES (
  32. 'orc.compress'='lzo');
  33. -- 照明操作记录
  34. create table saga_dw.ods_bus_light_switch
  35. (
  36. id STRING comment '主键',
  37. project_id STRING comment '项目ID',
  38. object_id STRING comment '空间ID',
  39. source_type STRING comment '反馈来源分类',
  40. user_id STRING ,
  41. user_phone STRING ,
  42. user_name STRING ,
  43. on_off STRING comment '1开 0关',
  44. result STRING comment '1成功 0失败',
  45. create_time STRING,
  46. exe_result integer comment '执行结果'
  47. ) COMMENT '照明操作记录'
  48. PARTITIONED BY (`dt` string)
  49. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  50. STORED AS TEXTFILE
  51. LOCATION
  52. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_bus_light_switch'
  53. TBLPROPERTIES (
  54. 'orc.compress'='lzo');
  55. -- 项目信息
  56. create table saga_dw.ods_object_data_project
  57. (
  58. id STRING comment '项目id',
  59. secret STRING comment '',
  60. `name` STRING comment '',
  61. local_id STRING comment '',
  62. local_name STRING comment '',
  63. longitude STRING comment '经度',
  64. latitude STRING comment '纬度',
  65. distance STRING comment '',
  66. city_id STRING comment '',
  67. is_duoduo STRING comment '',
  68. `status` STRING comment '0:无效,1:有效'
  69. ) COMMENT '项目信息'
  70. PARTITIONED BY (`dt` string)
  71. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  72. STORED AS TEXTFILE
  73. LOCATION
  74. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_project'
  75. TBLPROPERTIES (
  76. 'orc.compress'='lzo');
  77. -- 建筑信息
  78. create table saga_dw.ods_object_data_building
  79. (
  80. id STRING,
  81. name STRING,
  82. local_id STRING,
  83. local_name STRING,
  84. project_id STRING,
  85. `status` STRING
  86. ) COMMENT '建筑信息'
  87. PARTITIONED BY (`dt` string)
  88. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  89. STORED AS TEXTFILE
  90. LOCATION
  91. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_building'
  92. TBLPROPERTIES (
  93. 'orc.compress'='lzo');
  94. -- 空间
  95. create table saga_dw.ods_object_data_space
  96. (
  97. id STRING,
  98. name STRING comment '',
  99. local_id STRING comment '',
  100. local_name STRING comment '',
  101. room_func_type STRING comment '',
  102. group_id STRING comment '',
  103. bim_location STRING comment '',
  104. room_q_r_code STRING comment '',
  105. business_type STRING comment '业务区域编码',
  106. function_code STRING comment '',
  107. out_line STRING comment '',
  108. pic STRING comment '',
  109. position_img STRING comment '',
  110. position_img_upload_type STRING comment '',
  111. position_img_create_time STRING comment '',
  112. project_id STRING comment '',
  113. building_id STRING comment '',
  114. floor_id STRING comment '',
  115. `status` STRING comment '',
  116. is_controlled STRING comment '是否为服务定制受控对象',
  117. is_chiller_monitor STRING comment '是否冷站智控监测 1:是 0:不是',
  118. has_window STRING comment '是否有窗户',
  119. has_defect STRING comment '是否有工程缺陷',
  120. region STRING comment '内外区',
  121. zone_orientation STRING comment '朝向,东西南北',
  122. remark STRING comment '备注',
  123. tenant_id STRING comment '租户id',
  124. room_serial STRING comment '空间序号',
  125. monitor_remark STRING comment '日常数据监控的空间备注',
  126. light_group_id STRING comment '照明分组ID'
  127. ) COMMENT ''
  128. PARTITIONED BY (`dt` string)
  129. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  130. STORED AS TEXTFILE
  131. LOCATION
  132. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_space'
  133. TBLPROPERTIES (
  134. 'orc.compress'='lzo');
  135. -- 空间组
  136. create table saga_dw.ods_object_data_space_group
  137. (
  138. id STRING,
  139. project_id STRING comment '',
  140. name STRING comment '',
  141. `status` STRING comment '',
  142. `readonly` STRING comment '',
  143. update_time STRING comment '',
  144. type STRING comment '组类型,默认0 空调;1照明;其它...',
  145. update_user STRING comment '',
  146. remark STRING comment ''
  147. ) COMMENT ''
  148. PARTITIONED BY (`dt` string)
  149. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  150. STORED AS TEXTFILE
  151. LOCATION
  152. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_space_group'
  153. TBLPROPERTIES (
  154. 'orc.compress'='lzo');
  155. -- 楼层
  156. create table saga_dw.ods_object_data_floor
  157. (
  158. id STRING comment '',
  159. name STRING comment '',
  160. local_id STRING comment '',
  161. local_name STRING comment '',
  162. floor_map STRING comment '',
  163. project_id STRING comment '',
  164. building_id STRING comment '',
  165. floor_sequence_id integer comment '',
  166. `status` STRING comment ''
  167. ) COMMENT ''
  168. PARTITIONED BY (`dt` string)
  169. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  170. STORED AS TEXTFILE
  171. LOCATION
  172. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_floor'
  173. TBLPROPERTIES (
  174. 'orc.compress'='lzo');
  175. -- 设备
  176. create table saga_dw.ods_object_data_equipment
  177. (
  178. id STRING comment '',
  179. name STRING comment '',
  180. local_id STRING comment '',
  181. local_name STRING comment '',
  182. major_category STRING comment '',
  183. system_category STRING comment '',
  184. equipment_category STRING comment '',
  185. bim_location STRING comment '',
  186. run_status STRING comment '',
  187. equip_q_r_code STRING comment '',
  188. install_location STRING comment '',
  189. position_img STRING comment '',
  190. position_img_upload_type STRING comment '',
  191. position_img_create_time STRING comment '',
  192. project_id STRING comment '',
  193. building_id STRING comment '',
  194. `status` STRING comment ''
  195. ) COMMENT ''
  196. PARTITIONED BY (`dt` string)
  197. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  198. STORED AS TEXTFILE
  199. LOCATION
  200. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_equipment'
  201. TBLPROPERTIES (
  202. 'orc.compress'='lzo');
  203. -- 设备信息
  204. create table saga_dw.ods_object_data_equipment_infos
  205. (
  206. id STRING comment '',
  207. project_id STRING comment '',
  208. category STRING comment '',
  209. infos STRING comment '',
  210. body STRING comment ''
  211. ) COMMENT ''
  212. PARTITIONED BY (`dt` string)
  213. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  214. STORED AS TEXTFILE
  215. LOCATION
  216. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_equipment_infos'
  217. TBLPROPERTIES (
  218. 'orc.compress'='lzo');
  219. -- 关系表
  220. create table saga_dw.ods_object_data_relation
  221. (
  222. id STRING comment '',
  223. project_id STRING comment '',
  224. graph_id STRING comment '',
  225. from_id STRING comment '',
  226. rel_type STRING comment '',
  227. to_id STRING comment '',
  228. infos STRING comment ''
  229. ) COMMENT ''
  230. PARTITIONED BY (`dt` string)
  231. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  232. STORED AS TEXTFILE
  233. LOCATION
  234. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_relation'
  235. TBLPROPERTIES (
  236. 'orc.compress'='lzo');
  237. -- 租户
  238. create table saga_dw.ods_object_data_tenant
  239. (
  240. id STRING comment '',
  241. name STRING comment '',
  242. local_id STRING comment '',
  243. local_name STRING comment '',
  244. project_id STRING comment '',
  245. `status` STRING comment ''
  246. ) COMMENT ''
  247. PARTITIONED BY (`dt` string)
  248. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  249. STORED AS TEXTFILE
  250. LOCATION
  251. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_tenant'
  252. TBLPROPERTIES (
  253. 'orc.compress'='lzo');
  254. create table saga_dw.ods_custom_content_real
  255. (
  256. id STRING comment '',
  257. project_id STRING comment '',
  258. space_id STRING comment '',
  259. group_id STRING comment '',
  260. `date` STRING comment '',
  261. `time` STRING comment '',
  262. item STRING comment '',
  263. time_point STRING comment '',
  264. fixed_value double comment '',
  265. max_value double comment '',
  266. min_value double comment '',
  267. create_user_id STRING comment '',
  268. create_time STRING comment '',
  269. update_user_id STRING comment '',
  270. update_time STRING comment '',
  271. update_source STRING comment '更新来源 0 初始化配置 1、反馈算法调整季节 2、反馈算法调整日期 3、临时服务定制 4、提前开机 5、小程序捏曲线',
  272. is_beforehand STRING comment '提前开机1 不提前开机0',
  273. custom_type STRING comment '0 初始化 1 算法季节 2算法日期 3 app 4 临时 5灯加班 关机情况有初始服务定制默认0'
  274. ) COMMENT ''
  275. PARTITIONED BY (`dt` string)
  276. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  277. STORED AS TEXTFILE
  278. LOCATION
  279. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_custom_content_real'
  280. TBLPROPERTIES (
  281. 'orc.compress'='lzo');
  282. create table saga_dw.ods_custom_persist_target
  283. (
  284. id STRING comment '',
  285. project_id STRING comment '',
  286. space_id STRING comment '',
  287. `date` STRING comment '',
  288. `time` STRING comment '',
  289. temperature_max double comment '',
  290. temperature_min double comment '',
  291. humidity_max double comment '',
  292. humidity_min double comment '',
  293. co2_max double comment '',
  294. co2_min double comment '',
  295. hcho_max double comment '',
  296. hcho_min double comment '',
  297. pm25_max double comment '',
  298. pm25_min double comment '',
  299. sun_fixed double comment '',
  300. create_time STRING comment '',
  301. is_beforehand STRING comment ''
  302. ) COMMENT ''
  303. PARTITIONED BY (`dt` string)
  304. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  305. STORED AS TEXTFILE
  306. LOCATION
  307. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_custom_persist_target'
  308. TBLPROPERTIES (
  309. 'orc.compress'='lzo');
  310. -- 用户信息
  311. create table saga_dw.ods_user
  312. (
  313. id STRING comment '',
  314. name STRING comment '用户名 游客用户没有名字',
  315. company_id STRING comment '所属企业ID',
  316. type STRING comment '类型:1,普通租户;2,租户行政;3,运营方;4,访客',
  317. `status` STRING comment '用户状态【0:未入驻/1:审核中/2:已入驻激活/3:已入驻待激活/4:已入驻冻结/5:已入驻作废】',
  318. create_date STRING comment '创建时间',
  319. update_date STRING comment '更新时间',
  320. dep_name STRING comment '',
  321. job STRING comment '职务',
  322. phone STRING comment '',
  323. manage_user_type STRING comment '成员类型:1-正式成员, 2-临时成员',
  324. first_use_time STRING comment '首次使用时间(入住、激活时的时间)'
  325. ) COMMENT ''
  326. PARTITIONED BY (`dt` string)
  327. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  328. STORED AS TEXTFILE
  329. LOCATION
  330. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_user'
  331. TBLPROPERTIES (
  332. 'orc.compress'='lzo');