energy_hive.sql 2.5 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192
  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. /** 能源原始数据 5min 历史数据 */
  18. create table ods_energy_5_min
  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_5_min'
  31. TBLPROPERTIES (
  32. 'orc.compress'='lzo');
  33. /** 能源数据 15min */
  34. create table ods_energy_15_min_fjd
  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_15_min_fjd'
  47. TBLPROPERTIES (
  48. 'orc.compress'='lzo');
  49. /** 能源数据 5min */
  50. create table ods_energy_5_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 '能源原始数据 5min'
  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_5_min_fjd'
  63. TBLPROPERTIES (
  64. 'orc.compress'='lzo');
  65. create table saga_dw.dwd_energy_15_min_hour
  66. (
  67. building string,
  68. func_id string,
  69. branch_type string,
  70. branch_type_name string,
  71. value_sum decimal(30, 15),
  72. current_hour string comment '小时',
  73. current_year integer comment '年份',
  74. year_mouth string comment '年份-月份',
  75. week_of_year integer comment '这一年的第几周'
  76. ) COMMENT '能源原始数据15min按小时维度数据'
  77. PARTITIONED BY (`dt` string)
  78. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  79. STORED AS TEXTFILE
  80. LOCATION
  81. 'hdfs://sagaCluster:8020/warehouse/saga_dw/dwd/dwd_energy_15_min_hour'
  82. TBLPROPERTIES (
  83. 'orc.compress' = 'lzo');