Hive SQL 動態建表插入：按小時分區上傳高用戶電表數據

Hive SQL 動態建表插入：按小時分區上傳高用戶電表數據

展示一段實際專案中用於預付費高壓用戶電表曲線數據上傳的 Hive SQL 動態生成邏輯，核心為根據當前小時決定寫入分區欄位（r1 或 r49）並聯合多指標查詢。

業務背景

• 來源表：e_mp_read_curve_prepay（電表曲線讀數）、prepay_collobjid_data_usual（採集物件關聯）
• 目標：生成 prepay_high_cons_upload_data 供上游系統消費
• 指標映射：14 種電量指標（正/反向有功、無功、各象限）對應 coll_item_code
• 分區邏輯：上午 12 點前寫 r1（00:00 讀數），下午寫 r49（12:00 讀數）

核心 Java 程式碼片段

public void getHighConsMeterData(String jssj) throws Exception {
    // 1. 指標代碼映射
    Map<String, String> map = new HashMap<>();
    map.put("1", "'9010'");  // 正向有功總
    map.put("9", "'9011'");  // 正向有功總尖
    // ... 省略其餘 12 項映射
    map.put("8", "'9160'");  // 無功 Q4 象限

    // 2. 判斷時間分區
    Date datetmp = new Date();
    int hour = datetmp.getHours(); // 已棄用，建議用 Calendar
    String datatime = jssj;
    String columnName = "r1";
    if (hour < 12) {
        datatime = jssj + " 000000";
        columnName = "r1";
    } else {
        datatime = jssj + " 120000";
        columnName = "r49";
    }

    // 3. 刪除舊表
    hql.setHql("drop table if exists prepay_high_cons_upload_data");
    hql.executeUpdate();

    // 4. 動態構建 CTAS (Create Table As Select) 語句
    StringBuilder sb = new StringBuilder();
    sb.append("create table prepay_high_cons_upload_data ")
      .append("row format delimited fields terminated by ? as ")
      .append("select coll_obj_id, org_no, coll_item_code, ")
      .append("'").append(datatime).append("' data_time, ")
      .append("data_value, '").append(jssj).append("' write_time ")
      .append("from ( ");

    Iterator<Map.Entry<String, String>> iter = map.entrySet().iterator();
    while (iter.hasNext()) {
        Map.Entry<String, String> entry = iter.next();
        String key = entry.getKey();   // data_type
        String val = entry.getValue(); // coll_item_code

        sb.append("select pc.coll_obj_id, pc.org_no, ")
          .append(val).append(" coll_item_code, ")
          .append("em.data_date data_time, ")
          .append("COALESCE(em.").append(columnName).append(", em.r1) data_value ")
          .append("from e_mp_read_curve_prepay em ")
          .append("join prepay_collobjid_data_usual pc on pc.sp_id = em.id ")
          .append("where pc.cons_sort_code != '02' ")
          .append("and pc.cons_sort_code != '03' ")
          .append("and em.").append(columnName).append(" is not null ")
          .append("and em.data_type = '").append(key).append("' ");

        if (iter.hasNext()) {
            sb.append("union all ");
        }
    }
    sb.append(") a");

    // 5. 執行建表
    hql.setHql(sb.toString());
    hql.setString(1, Constants.SEPARATOR); // 分隔符參數
    hql.executeUpdate();
}

關鍵技術點解析

1. 動態 SQL 構建：使用 StringBuilder 迴圈拼接 UNION ALL 子查詢，避免硬編碼 14 條 SQL。
2. COALESCE 容錯：COALESCE(em.r49, em.r1) 若當前分區讀數為空則回退基礎讀數 r1。
3. 分隔符參數化：fields terminated by ? + setString(1, SEPARATOR) 防注入。
4. 時間邊界處理：按小時切換目標欄位與 data_time 格式，符合電力行業「日凍結/月凍結」讀數習慣。

優化建議

• 將指標映射移至配置表或常數類，避免硬編碼。
• 使用 Calendar 或 java.time 替代棄用的 Date.getHours()。
• 考慮改用 Hive 分區表 + INSERT OVERWRITE 替代 DROP + CTAS，保留歷史分區。
• 大數據量場景下，UNION ALL 可能導致 MapReduce 作業過多，可評估 LATERAL VIEW explode() 或 Spark SQL 重寫。