energy_hive.sql 5.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210
  1. /** 能源原始数据 15min 差值 */
  2. create table ods_energy_15_min
  3. (
  4. building string,
  5. func_id string,
  6. meter string,
  7. data_time string,
  8. data_value decimal(30, 15)
  9. ) COMMENT '能源原始数据15min差值数据'
  10. PARTITIONED BY (`dt` string)
  11. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  12. STORED AS TEXTFILE
  13. LOCATION
  14. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_15_min'
  15. TBLPROPERTIES (
  16. 'orc.compress'='lzo');
  17. /** 能源原始数据 15min 差值临时表 */
  18. create table ods_energy_15_min_tmp
  19. (
  20. building string,
  21. func_id string,
  22. meter string,
  23. data_time string,
  24. data_value decimal(30, 15)
  25. ) COMMENT '能源原始数据15min差值临时表'
  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/ods_energy_15_min_tmp'
  31. TBLPROPERTIES (
  32. 'orc.compress'='lzo');
  33. /** 能源原始数据 5min 差值 */
  34. create table ods_energy_5_min
  35. (
  36. building string,
  37. func_id string,
  38. meter string,
  39. data_time string,
  40. data_value decimal(30, 15)
  41. ) COMMENT '能源原始数据15min差值数据'
  42. PARTITIONED BY (`dt` string)
  43. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  44. STORED AS TEXTFILE
  45. LOCATION
  46. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min'
  47. TBLPROPERTIES (
  48. 'orc.compress'='lzo');
  49. /** 能源数据 15min */
  50. create table ods_energy_15_min_fjd
  51. (
  52. building string,
  53. func_id string,
  54. meter string,
  55. data_time string,
  56. data_value decimal(30, 15)
  57. ) COMMENT '能源原始数据 15min'
  58. PARTITIONED BY (`dt` string)
  59. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  60. STORED AS TEXTFILE
  61. LOCATION
  62. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_15_min_fjd'
  63. TBLPROPERTIES (
  64. 'orc.compress'='lzo');
  65. /** 能源数据 5min */
  66. create table ods_energy_5_min_fjd
  67. (
  68. building string,
  69. func_id string,
  70. meter string,
  71. data_time string,
  72. data_value decimal(30, 15)
  73. ) COMMENT '能源原始数据 5min'
  74. PARTITIONED BY (`dt` string)
  75. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  76. STORED AS TEXTFILE
  77. LOCATION
  78. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min_fjd'
  79. TBLPROPERTIES (
  80. 'orc.compress'='lzo');
  81. /** 能源数据按小时 */
  82. create table saga_dw.dwd_energy_15_min_hour
  83. (
  84. building string,
  85. func_id string,
  86. branch_type string,
  87. branch_type_name string,
  88. use_range_type string,
  89. value_sum decimal(30, 15),
  90. current_hour string comment '小时',
  91. current_year integer comment '年份',
  92. year_mouth string comment '年份-月份',
  93. week_of_year integer comment '这一年的第几周'
  94. ) COMMENT '能源原始数据15min按小时维度数据'
  95. PARTITIONED BY (`dt` string)
  96. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  97. STORED AS TEXTFILE
  98. LOCATION
  99. 'hdfs://sagaCluster:8020/warehouse/saga_dw/dwd/dwd_energy_15_min_hour'
  100. TBLPROPERTIES (
  101. 'orc.compress' = 'lzo');
  102. /** CO2 15min 分精度 */
  103. create table ods_co2_15_min
  104. (
  105. building string,
  106. func_id string,
  107. meter string,
  108. data_time string,
  109. data_value decimal(30, 15)
  110. ) COMMENT 'CO2 15min 分精度'
  111. PARTITIONED BY (`dt` string)
  112. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  113. STORED AS TEXTFILE
  114. LOCATION
  115. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_co2_15_min'
  116. TBLPROPERTIES (
  117. 'orc.compress'='lzo');
  118. /** PM2.5 15min 分精度 */
  119. create table ods_pm25_15_min
  120. (
  121. building string,
  122. func_id string,
  123. meter string,
  124. data_time string,
  125. data_value decimal(30, 15)
  126. ) COMMENT 'PM2.5 15min 分精度'
  127. PARTITIONED BY (`dt` string)
  128. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  129. STORED AS TEXTFILE
  130. LOCATION
  131. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_pm25_15_min'
  132. TBLPROPERTIES (
  133. 'orc.compress'='lzo');
  134. /** 甲醛 15 分钟分精度 */
  135. create table ods_hcho_15_min
  136. (
  137. building string,
  138. func_id string,
  139. meter string,
  140. data_time string,
  141. data_value decimal(30, 15)
  142. ) COMMENT '甲醛 15 分钟分精度'
  143. PARTITIONED BY (`dt` string)
  144. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  145. STORED AS TEXTFILE
  146. LOCATION
  147. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_hcho_15_min'
  148. TBLPROPERTIES (
  149. 'orc.compress'='lzo');
  150. /** 温度 15 分钟分精度 */
  151. create table ods_temperature_15_min
  152. (
  153. building string,
  154. func_id string,
  155. meter string,
  156. data_time string,
  157. data_value decimal(30, 15)
  158. ) COMMENT '温度 15 分钟分精度'
  159. PARTITIONED BY (`dt` string)
  160. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  161. STORED AS TEXTFILE
  162. LOCATION
  163. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_temperature_15_min'
  164. TBLPROPERTIES (
  165. 'orc.compress'='lzo');
  166. drop table ods_humidity_15_min;
  167. /** 湿度 15 分钟分精度 */
  168. create table ods_humidity_15_min
  169. (
  170. building string,
  171. func_id string,
  172. meter string,
  173. data_time string,
  174. data_value decimal(30, 15)
  175. ) COMMENT '湿度 15 分钟分精度'
  176. PARTITIONED BY (`dt` string)
  177. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  178. STORED AS TEXTFILE
  179. LOCATION
  180. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_humidity_15_min'
  181. TBLPROPERTIES (
  182. 'orc.compress'='lzo');
  183. create table ods_history_tmp
  184. (
  185. building string,
  186. func_id string,
  187. meter string,
  188. data_time string,
  189. data_value decimal(30, 15)
  190. ) COMMENT '历史数据中间表'
  191. PARTITIONED BY (`dt` string)
  192. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  193. STORED AS TEXTFILE
  194. LOCATION
  195. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/tmp/ods_history_tmp'
  196. TBLPROPERTIES (
  197. 'orc.compress'='lzo');