energy_hive.sql 2.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109
  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. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  27. STORED AS TEXTFILE
  28. LOCATION
  29. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_15_min_tmp'
  30. TBLPROPERTIES (
  31. 'orc.compress'='lzo');
  32. /** 能源原始数据 5min 差值 */
  33. create table ods_energy_5_min
  34. (
  35. building string,
  36. func_id string,
  37. meter string,
  38. data_time string,
  39. data_value decimal(30, 15)
  40. ) COMMENT '能源原始数据15min差值数据'
  41. PARTITIONED BY (`dt` string)
  42. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  43. STORED AS TEXTFILE
  44. LOCATION
  45. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min'
  46. TBLPROPERTIES (
  47. 'orc.compress'='lzo');
  48. /** 能源数据 15min */
  49. create table ods_energy_15_min_fjd
  50. (
  51. building string,
  52. func_id string,
  53. meter string,
  54. data_time string,
  55. data_value decimal(30, 15)
  56. ) COMMENT '能源原始数据 15min'
  57. PARTITIONED BY (`dt` string)
  58. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  59. STORED AS TEXTFILE
  60. LOCATION
  61. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_15_min_fjd'
  62. TBLPROPERTIES (
  63. 'orc.compress'='lzo');
  64. /** 能源数据 5min */
  65. create table ods_energy_5_min_fjd
  66. (
  67. building string,
  68. func_id string,
  69. meter string,
  70. data_time string,
  71. data_value decimal(30, 15)
  72. ) COMMENT '能源原始数据 5min'
  73. PARTITIONED BY (`dt` string)
  74. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  75. STORED AS TEXTFILE
  76. LOCATION
  77. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min_fjd'
  78. TBLPROPERTIES (
  79. 'orc.compress'='lzo');
  80. create table saga_dw.dwd_energy_15_min_hour
  81. (
  82. building string,
  83. func_id string,
  84. branch_type string,
  85. branch_type_name string,
  86. use_range_type string,
  87. value_sum decimal(30, 15),
  88. current_hour string comment '小时',
  89. current_year integer comment '年份',
  90. year_mouth string comment '年份-月份',
  91. week_of_year integer comment '这一年的第几周'
  92. ) COMMENT '能源原始数据15min按小时维度数据'
  93. PARTITIONED BY (`dt` string)
  94. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  95. STORED AS TEXTFILE
  96. LOCATION
  97. 'hdfs://sagaCluster:8020/warehouse/saga_dw/dwd/dwd_energy_15_min_hour'
  98. TBLPROPERTIES (
  99. 'orc.compress' = 'lzo');