energy_hive.sql 2.4 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071
  1. /** 能源差值数据 15min 历史数据 */
  2. create table ods_energy_15_min_history
  3. (
  4. building string comment '大楼id',
  5. func_id string comment '仪表功能号',
  6. meter string comment '仪表号',
  7. data_time string comment '数据采集时间',
  8. data_value decimal(30, 15) comment '采集值',
  9. `dt` string comment '采集日期'
  10. ) COMMENT '能源原始数据 15min 历史数据'
  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_history'
  15. TBLPROPERTIES (
  16. 'orc.compress'='lzo');
  17. /** 能源差值数据 5min 历史数据 */
  18. create table ods_energy_5_min_history
  19. (
  20. building string comment '大楼id',
  21. func_id string comment '仪表功能号',
  22. meter string comment '仪表号',
  23. data_time string comment '数据采集时间',
  24. data_value decimal(30, 15) comment '采集值',
  25. `dt` string comment '采集日期'
  26. ) COMMENT '能源原始数据 5min 历史数据'
  27. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  28. STORED AS TEXTFILE
  29. LOCATION
  30. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min_history'
  31. TBLPROPERTIES (
  32. 'orc.compress'='lzo');
  33. /** 能源 fjd 数据 15min 历史数据 */
  34. create table ods_energy_15_min_fjd_history
  35. (
  36. building string comment '大楼id',
  37. func_id string comment '仪表功能号',
  38. meter string comment '仪表号',
  39. data_time string comment '数据采集时间',
  40. data_value decimal(30, 15) comment '采集值',
  41. `dt` string comment '采集日期'
  42. ) COMMENT '能源 fjd 数据 15min 历史数据'
  43. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  44. STORED AS TEXTFILE
  45. LOCATION
  46. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_15_min_fjd_history'
  47. TBLPROPERTIES (
  48. 'orc.compress'='lzo');
  49. /** 能源 fjd 数据 5min 历史数据 */
  50. create table ods_energy_5_min_fjd_history
  51. (
  52. building string comment '大楼id',
  53. func_id string comment '仪表功能号',
  54. meter string comment '仪表号',
  55. data_time string comment '数据采集时间',
  56. data_value decimal(30, 15) comment '采集值',
  57. `dt` string comment '采集日期'
  58. ) COMMENT '能源 fjd 数据 5min 历史数据'
  59. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  60. STORED AS TEXTFILE
  61. LOCATION
  62. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min_fjd_history'
  63. TBLPROPERTIES (
  64. 'orc.compress'='lzo');