離線數倉建設之資料匯出

為了方便報表應用使用資料，需將ADS各項指標統計結果匯出到MySQL，方便熟悉 SQL 人員使用。

1 MySQL建庫建表

1.1 建立資料庫

建立car_data_report資料庫：

CREATE DATABASE IF NOT EXISTS car_data_report
# 字符集
DEFAULT CHARSET utf8mb4
# 排序規則
COLLATE utf8mb4_general_ci;

1.1.2 建立表

① 里程相關統計

建立ads_mileage_stat_last_month表，儲存里程相關統計資料。

DROP TABLE IF EXISTS ads_mileage_stat_last_month;

CREATE TABLE ads_mileage_stat_last_month (
  vin VARCHAR(20) COMMENT '汽車唯一ID',
  mon VARCHAR(7) COMMENT '統計月份',
  avg_mileage INT COMMENT '日均里程',
  avg_speed DECIMAL(16, 2) COMMENT '平均時速分子',
  danger_count DECIMAL(16, 2) COMMENT '平均百公里急加減速次數'
) COMMENT '里程相關統計';

② 告警相關統計

建立ads_alarm_stat_last_month表，儲存告警相關的統計資料。

DROP TABLE IF EXISTS ads_alarm_stat_last_month;

CREATE TABLE ads_alarm_stat_last_month (
  vin VARCHAR(20) COMMENT '汽車唯一ID',
  mon VARCHAR(7) COMMENT '統計月份',
  alarm_count INT COMMENT '告警次數',
  l1_alarm_count INT COMMENT '一級告警次數',
  l2_alarm_count INT COMMENT '二級告警次數',
  l3_alarm_count INT COMMENT '三級告警次數'
) COMMENT '告警相關統計';

3）溫控相關統計

建立ads_temperature_stat_last_month表，儲存溫控相關的統計資料。

DROP TABLE IF EXISTS ads_temperature_stat_last_month;

CREATE TABLE ads_temperature_stat_last_month (
  vin VARCHAR(20) COMMENT '汽車唯一ID',
  mon VARCHAR(7) COMMENT '統計月份',
  max_motor_temperature INT COMMENT '電機最高溫度',
  avg_motor_temperature DECIMAL(16, 2) COMMENT '電機平均溫度',
  max_motor_controller_temperature INT COMMENT '電機控制器最高溫度',
  avg_motor_controller_temperature DECIMAL(16, 2) COMMENT '電機控制器平均溫度',
  max_battery_temperature INT COMMENT '最高電池溫度',
  battery_temperature_abnormal_count INT COMMENT '電池溫度異常值次數'
) COMMENT '溫控相關統計';

4）能耗相關統計

建立ads_consume_stat_last_month表，儲存能耗相關的統計資料。

DROP TABLE IF EXISTS ads_consume_stat_last_month;

CREATE TABLE ads_consume_stat_last_month (
  vin VARCHAR(20) COMMENT '汽車唯一ID',
  mon VARCHAR(7) COMMENT '統計月份',
  soc_per_charge DECIMAL(16, 2) COMMENT '次均充電電量',
  duration_per_charge DECIMAL(16, 2) COMMENT '次均充電時長',
  charge_count INT COMMENT '充電次數',
  fast_charge_count INT COMMENT '快充次數',
  slow_charge_count INT COMMENT '慢充次數',
  fully_charge_count INT COMMENT '深度充電次數',
  soc_per_100km DECIMAL(16, 2) COMMENT 'soc百公里平均消耗',
  soc_per_run DECIMAL(16, 2) COMMENT '每次里程soc平均消耗',
  soc_last_100km DECIMAL(16, 2) COMMENT '最近百公里soc消耗'
) COMMENT '能耗主題統計';

2 資料匯出

DataX作為資料匯出工具，並選擇HDFSReader和MySQLWriter作為資料來源和目標。

2.1 編寫DataX配置檔案

我們需要為每個表編寫一個DataX配置檔案。以ads_alarm_stat_last_month為例：

{
  "job": {
    "setting": {
      "speed": {
        "channel": 1  // DataX 作業的併發通道數，一般根據系統資源進行調整，1 表示單通道
      }
    },
    "content": [
      {
        "reader": {
          ...
        },
        "writer": {
          "name": "mysqlwriter",  // 寫入資料的外掛型別為 MySQL 資料庫寫入
          "parameter": {
            "writeMode": "replace",  // 寫入模式為替換（如果表存在則先刪除再寫入）
            "username": "root",  // 資料庫使用者名稱
            "password": "000000",  // 資料庫密碼
            "column": [  // 寫入的列資訊，包括 vin、mon、alarm_count、l1_alarm_count、l2_alarm_count、l3_alarm_count
              "vin",
              "mon",
              "alarm_count",
              "l1_alarm_count",
              "l2_alarm_count",
              "l3_alarm_count"
            ],
            "connection": [  // 資料庫連線資訊列表，支援多個資料庫連線
              {
                "jdbcUrl": "jdbc:mysql://hadoop102:3306/car_data_report?useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8",  // MySQL 資料庫連線地址，設定了 SSL、公鑰檢索、Unicode 編碼等引數
                "table": [  // 寫入的資料庫表列表，這裡只寫入 ads_alarm_stat_last_month 表
                  "ads_alarm_stat_last_month"
                ]
              }
            ]
          }
        }
      }
    ]
  }
}

匯出路徑引數path並未寫死，需在提交任務時透過引數動態傳入，引數名稱為exportdir。

模版配置引數解析：

HDFSReader：

即：

"reader": {
  "name": "hdfsreader",  // 讀取資料的外掛型別為 HDFS 檔案讀取
  "parameter": {
    "path": "${exportdir}",  // HDFS 檔案路徑，使用 ${exportdir} 變數表示動態路徑
    "defaultFS": "hdfs://hadoop102:8020",  // HDFS 預設檔案系統地址
    "column": [  // 需要讀取的列資訊，這裡使用萬用字元 * 表示讀取所有列
      "*"
    ],
    "fileType": "text",  // 檔案型別為文字檔案
    "encoding": "UTF-8",  // 檔案編碼格式為 UTF-8
    "fieldDelimiter": "\t",  // 欄位分隔符為製表符
    "nullFormat": "\\N"  // 空值格式為 \N
  }
},

MySQLWriter：

"writer": {
  "name": "mysqlwriter",  // 寫入資料的外掛型別為 MySQL 資料庫寫入
  "parameter": {
    "writeMode": "replace",  // 寫入模式為替換（如果表存在則先刪除再寫入）
    "username": "root",  // 資料庫使用者名稱
    "password": "000000",  // 資料庫密碼
    "column": [  // 寫入的列資訊，包括 vin、mon、alarm_count、l1_alarm_count、l2_alarm_count、l3_alarm_count
      "vin",
      "mon",
      "alarm_count",
      "l1_alarm_count",
      "l2_alarm_count",
      "l3_alarm_count"
    ],
    "connection": [  // 資料庫連線資訊列表，支援多個資料庫連線
      {
        "jdbcUrl": "jdbc:mysql://hadoop102:3306/car_data_report?useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8",  // MySQL 資料庫連線地址，設定了 SSL、公鑰檢索、Unicode 編碼等引數
        "table": [  // 寫入的資料庫表列表，這裡只寫入 ads_alarm_stat_last_month 表
          "ads_alarm_stat_last_month"
        ]
      }
    ]
  }
}

2.2 DataX配置檔案生成指令碼

將 TODO（在下載的資料壓縮包裡）datax_config_generator複製到/opt/module。

修改/opt/module/datax_config_generator/configuration.properties：

mysql.username=root
mysql.password=000000
mysql.host=hadoop102
mysql.port=3306
mysql.database.import=car_data
mysql.database.export=car_data_report
mysql.tables.import=
mysql.tables.export=
is.seperated.tables=0
hdfs.uri=hdfs://hadoop102:8020
import_out_dir=/opt/module/datax/job/import
export_out_dir=/opt/module/datax/job/export

執行配置檔案生成器：

java -jar datax-config-generator-1.0.1-jar-with-dependencies.jar

觀察生成的配置檔案：

ll /opt/module/datax/job/export/

總用量 20
-rw-rw-r--. 1 atguigu atguigu  961 4月  26 19:47 car_data_report.ads_alarm_stat_last_month.json
-rw-rw-r--. 1 atguigu atguigu 1095 4月  26 19:47 car_data_report.ads_consume_stat_last_month.json
-rw-rw-r--. 1 atguigu atguigu 1062 4月  26 19:47 car_data_report.ads_electric_stat_last_month.json
-rw-rw-r--. 1 atguigu atguigu  939 4月  26 19:47 car_data_report.ads_mileage_stat_last_month.json
-rw-rw-r--. 1 atguigu atguigu 1083 4月  26 19:47 car_data_report.ads_temperature_stat_last_month.json

2.3 測試生成的DataX配置檔案

以ads_trans_order_stats為例，測試用指令碼生成的配置檔案是否可用。

1）執行DataX同步命令

python /opt/module/datax/bin/datax.py -p"-Dexportdir=/warehouse/car_data/ads/ads_order_stats" /opt/module/datax/job/export/tms_report.ads_order_stats.json

2）觀察同步結果

觀察MySQL目標表是否出現資料。

2.4 編寫匯出指令碼

建立hdfs_to_mysql.sh

vim hdfs_to_mysql.sh

#!/bin/bash

# 設定 DataX 的安裝路徑
DATAX_HOME=/opt/module/datax

# 清理指定路徑下的空檔案
# 引數 $1: 待清理的路徑
handle_export_path() {
  for file in $(hadoop fs -ls -R "$1" | awk '{print $8}'); do
    # 檢查檔案是否為空
    if hadoop fs -test -z "$file"; then
      echo "$file 檔案大小為0，正在刪除..."
      # 刪除空檔案
      hadoop fs -rm -r -f "$file"
    fi
  done
}

# 匯出資料到指定路徑
# 引數 $1: DataX 配置檔案路徑
# 引數 $2: 匯出路徑
export_data() {
  datax_config="$1"
  export_dir="$2"
  # 呼叫清理空檔案函式
  handle_export_path "$export_dir"
  # 執行 DataX 匯出命令
  $DATAX_HOME/bin/datax.py -p"-Dexportdir=$export_dir" "$datax_config"
}

# 主邏輯，根據傳入的引數執行資料匯出操作
case $1 in
  'ads_mileage_stat_last_month' | 'ads_alarm_stat_last_month' | 'ads_temperature_stat_last_month' | 'ads_electric_stat_last_month' | 'ads_consume_stat_last_month')
    # 匯出單個表的資料
    export_data "/opt/module/datax/job/export/car_data_report.$1.json" "/warehouse/car_data/ads/$1"
    ;;
  'all')
    # 匯出所有表的資料
    for table in 'ads_mileage_stat_last_month' 'ads_alarm_stat_last_month' 'ads_temperature_stat_last_month' 'ads_electric_stat_last_month' 'ads_consume_stat_last_month'; do
      export_data "/opt/module/datax/job/export/car_data_report.$table.json" "/warehouse/car_data/ads/$table"
    done
    ;;
  *)
    # 未知引數，列印提示資訊
    echo "Usage: $0 {ads_table_name | all}"
    echo "Example: $0 ads_mileage_stat_last_month"
    ;;
esac

chmod +x hdfs_to_mysql.sh

hdfs_to_mysql.sh all

關注我，緊跟本系列專欄文章，咱們下篇再續！

作者簡介：魔都技術專家兼架構，多家大廠後端一線研發經驗，各大技術社群頭部專家博主。具有豐富的引領團隊經驗，深厚業務架構和解決方案的積累。

負責：

• 中央/分銷預訂系統效能最佳化
• 活動&優惠券等營銷中臺建設
• 交易平臺及資料中臺等架構和開發設計

目前主攻降低軟體複雜性設計、構建高可用系統方向。

參考：

• 程式設計嚴選網

本文由部落格一文多發平臺 OpenWrite 釋出！