energy_hive.sql 1.9 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576
  1. /** 能源原始数据 15min 历史数据 */
  2. create table ods_energy_15_min_history
  3. (
  4. building string,
  5. func_id string,
  6. meter string,
  7. data_time string,
  8. data_value decimal(20, 15),
  9. `dt` string
  10. ) COMMENT '能源原始数据15min历史数据'
  11. partitioned by (`dt` string)
  12. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  13. STORED AS TEXTFILE
  14. LOCATION
  15. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_15_min_history'
  16. TBLPROPERTIES (
  17. 'orc.compress'='lzo');
  18. /** 能源原始数据 5min 历史数据 */
  19. create table ods_energy_5_min_history
  20. (
  21. building string,
  22. func_id string,
  23. meter string,
  24. data_time string,
  25. data_value decimal(20, 15),
  26. `dt` string
  27. ) COMMENT '能源原始数据15min历史数据'
  28. partitioned by (`dt` string)
  29. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  30. STORED AS TEXTFILE
  31. LOCATION
  32. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min_history'
  33. TBLPROPERTIES (
  34. 'orc.compress'='lzo');
  35. /** 能源数据 15min */
  36. create table ods_energy_15_min
  37. (
  38. building string,
  39. func_id string,
  40. meter string,
  41. data_time string,
  42. data_value decimal(20, 15)
  43. ) COMMENT '能源原始数据15min'
  44. partitioned by (`dt` string)
  45. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  46. STORED AS TEXTFILE
  47. LOCATION
  48. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_15_min'
  49. TBLPROPERTIES (
  50. 'orc.compress'='lzo');
  51. /** 能源数据 5min */
  52. create table ods_energy_5_min
  53. (
  54. building string,
  55. func_id string,
  56. meter string,
  57. data_time string,
  58. data_value decimal(20, 15)
  59. ) COMMENT '能源原始数据15min'
  60. partitioned by (`dt` string)
  61. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
  62. STORED AS TEXTFILE
  63. LOCATION
  64. 'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_energy_5_min'
  65. TBLPROPERTIES (
  66. 'orc.compress'='lzo');