MaxCompute湖上資料加工和多情境聯動實踐 - MaxCompute

情境介紹

通過MaxLake實現資料入湖入倉及多情境分析聯動，以車連網資料為例，通過車輛上報的GPS定位資訊分析行駛裡程和速度，並聯動多引擎滿足即時查詢報表、跨團隊協作與脫敏分享、AI訓練等應用情境，實現一份資料，多重價值。整體流程如下圖所示：

自動探索未經處理資料，建立ODS層

ODS層儲存在OSS中，本樣本中資料即車連網未經處理資料表，儲存原始車連網定位資訊，按小時分區。

在MaxCompute中建立資料湖串連（CONNECTION）管理外部儲存訪問憑證。
建立資料發現（DataScan）任務，通過CONNECTION訪問OSS，自動將parquet、orc等資料檔案有組織地註冊成外部表格。

增量資料加工，輸出DWD、ADS層

在ODS層基礎上，過濾無效資料，統一欄位格式（時間格式化、經緯度校正等）。

多引擎計算，滿足細分應用情境

OLAP+AI：使用StarRocks引擎做即時查詢和報表，如查詢車輛的總裡程和平均時速。
跨團隊協作與資料安全分享：對第三方使用Spark引擎做資料分析的團隊開啟資料動態脫敏。
AI訓練：處理過的資料可進一步應用於模型訓練。
對車輛總行駛里程錶去標識化，對車輛唯一編號進行脫敏，只保留首尾字元，其他字元用“*”代替，使用脫敏後的資料統計每日最高時速

操作步驟

上傳測試資料
1. 登入Object Storage Service控制台。
2. 在左側導覽列單擊Bucket 列表。
  在Bucket 列表頁面，單擊建立 Bucket。
  本樣本中Bucket名稱為vehicle-raw。
3. 在Bucket 列表頁面，單擊目標Bucket 名稱，進入檔案清單頁面。
  單擊上傳文件，上傳解壓後的測試資料Maxlake_example_parquet.zip。
授權
1. 如果以RAM使用者身份建立和管理CONNECTION，請先獲得租戶級Connection_Admin角色。授權方式參考：租戶層級角色授權。
2. 阿里雲帳號或者具備租戶級Super_Administrator和Admin角色的使用者可執行Connection_Admin角色授權。
  1. 登入MaxCompute控制台，在左上方選擇地區。
  2. 在左側導覽列，選擇管理配置 > 租户管理 。
  3. 在租户管理頁面，單擊角色管理頁簽。
  4. 在角色管理頁簽，選擇Connection_Admin、Datascan_Admin，單擊對應的操作列的新增授权。
  5. 在彈出的新增授权對話方塊，添加需要授權的使用者，單擊確定完成授權。

在MaxCompute中建立資料湖串連（CONNECTION）

登入MaxCompute控制台，在左上方選擇地區。
在左側導覽列，選擇MaxLake > 数据湖连接。
在数据湖连接（CONNECTION）頁面，單擊创建数据湖连接。

在彈出的创建数据湖连接對話方塊，填寫如下參數，然後單擊確定完成建立資料湖串連。

參數名稱	說明
数据湖连接名称	資料湖串連名稱，在租戶內命名唯一。
RAMRoleARN	選擇RAM Role中具有訪問OSS許可權的RAMRoleARN資訊。可以建立和填寫自訂角色的RAMRoleARN資訊，建立詳情請參見STS模式授權。
数据湖连接描述	資料湖串連描述。

建立資料發現（DataScan）任務

登入MaxCompute控制台，在左上方選擇地區。
在左側導覽列，選擇MaxLake > 数据发现。
在数据发现頁面，單擊创建数据发现任务。

在彈出的创建任务對話方塊，填寫如下參數，然後單擊创建。

基本配置
參數名稱
說明
任务名称
任務名稱，在租戶內命名唯一。
任务周期
5分鐘

湖数据配置

參數名稱

說明

数据湖连接

選擇資料湖串連（CONNECTION）作為外部儲存訪問憑證。

LOCATION

填寫資料檔案所在OSS路徑。

格式：oss://<Bucket名稱>/<OSS 路徑>/
OSS Bucket必須和MaxCompute資料發現任務歸屬於同一阿里雲主帳號的相同Region下。
樣本：oss://vehicle-raw/Maxlake_example_parquet

Catalog配置

參數名稱

說明

项目

選擇開啟Schema級文法開關的專案。

Schema

選擇Schema。

建議選擇和將要發現的外部表格表名不衝突的Schema ，如果新發現的外部表格和Schema中使用者建立的表同名，發現任務將不會繼續建立同名的外部表格。

資料發現每5分鐘運行一次，查看資料發現結果有3張車連網表，並會不斷追加新分區。
說明
- 如果解析失敗，排查檔案中是否有.DS_Store檔案，不符合資料要求，會導致掃描失敗，需要手動刪除此類髒資料。
- 下一個發布的服務版本會支援自動忽略.DS_Store以及使用者佈建忽略檔案。

SQL資料查詢指令碼

ODS: 原始上報資料表

--業務情境設計
--情境：車連網平台擷取車輛的即時位置資料（GPS），並統計每小時每輛車的行駛裡程和平均速度

--資料來源：車載終端每隔幾秒上報一次位置、速度等
--ODS：直接落原始上報資料
--DWD：清洗、解析後轉換成“每次上報記錄”
--ADS：統計每小時每輛車總行駛裡程、平均速度

-- ODS: 原始上報資料表
--儲存原始車連網定位元據，按小時分區。

SHOW PARTITIONS ods_vehicle_gps_raw;
SET odps.sql.allow.fullscan=true;

SELECT  * FROM ods_vehicle_gps_raw WHERE dt='2025-09-17' AND hh='23';

-- 部分返回結果樣本
+------------+------------+-------------+------------+------------+------------+-------------+------------+------------+
| vin        | device_id  | report_time | lng        | lat        | speed      | raw_payload | dt         | hh         | 
+------------+------------+-------------+------------+------------+------------+-------------+------------+------------+
| VIN001     | DEV001     | 2025-09-16 00:00:00 | 120.00023573730152 | 30.39975989605289 | 73.3843581906447 | mock_payload | 2025-09-17 | 23         | 
| VIN002     | DEV002     | 2025-09-16 00:00:00 | 120.00517998985256 | 30.33811818824062 | 67.43035716350673 | mock_payload | 2025-09-17 | 23         | 
| VIN003     | DEV003     | 2025-09-16 00:00:00 | 120.24295999679852 | 30.143229002199707 | 40.8918776553552 | mock_payload | 2025-09-17 | 23         | 
| VIN001     | DEV001     | 2025-09-16 00:30:00 | 120.24754980497414 | 30.373484773735274 | 49.50436236779409 | mock_payload | 2025-09-17 | 23         | 
| VIN002     | DEV002     | 2025-09-16 00:30:00 | 120.00510501582413 | 30.42486370328109 | 55.8400627485663 | mock_payload | 2025-09-17 | 23         | 
| VIN003     | DEV003     | 2025-09-16 00:30:00 | 120.36073125682805 | 30.065016013833237 | 61.82996654036919 | mock_payload | 2025-09-17 | 23         | 
| VIN002     | DEV002     | 2025-09-16 19:30:00 | 120.29488938268968 | 30.12474152125639 | 66.48209032904454 | mock_payload | 2025-09-17 | 23         | 
| VIN002     | DEV002     | 2025-09-16 19:00:00 | 120.35157954057287 | 30.459823299646295 | 76.36574370617315 | mock_payload | 2025-09-17 | 23         | 
| VIN001     | DEV001     | 2025-09-16 19:30:00 | 120.3113710027241 | 30.33402715522518 | 62.601762741153024 | mock_payload | 2025-09-17 | 23         | 
+------------+------------+-------------+------------+------------+------------+-------------+------------+------------+

DWD層

-- DWD 層（dwd_vehicle_gps）
-- 在 ODS 基礎上，過濾無效資料，統一欄位格式（時間格式化、經緯度校正等）
CREATE TABLE IF NOT EXISTS dwd_vehicle_gps (
  vin          STRING COMMENT '車輛唯一識別碼',
  event_time   DATETIME COMMENT '上報時間',
  lng          DOUBLE COMMENT '經度',
  lat          DOUBLE COMMENT '緯度',
  speed        DOUBLE COMMENT '速度(km/h)',
  loc_valid    BOOLEAN COMMENT '定位是否有效'
)
PARTITIONED BY (dt STRING, hh STRING);

-- 加工 SQL
INSERT OVERWRITE TABLE dwd_vehicle_gps PARTITION (dt='2025-09-17', hh='23')
SELECT
vin,
TO_DATE(report_time,'yyyy-MM-dd HH:mi:ss') AS event_time,
lng, lat,
speed,
CASE WHEN lng BETWEEN 70 AND 140 AND lat BETWEEN 10 AND 60 THEN TRUE ELSE FALSE END AS loc_valid
FROM ods_vehicle_gps_raw
WHERE dt='2025-09-17' AND hh='23'
AND speed >= 0
AND vin IS NOT NULL;

SELECT  * FROM dwd_vehicle_gps WHERE dt='2025-09-17' AND hh='23';

-- 部分返回結果樣本
+------------+------------+------------+------------+------------+-----------+------------+------------+
| vin        | event_time | lng        | lat        | speed      | loc_valid | dt         | hh         | 
+------------+------------+------------+------------+------------+-----------+------------+------------+
| VIN001     | 2025-09-16 00:00:00 | 120.00023573730152 | 30.39975989605289 | 73.3843581906447 | true      | 2025-09-17 | 23         | 
| VIN002     | 2025-09-16 00:00:00 | 120.00517998985256 | 30.33811818824062 | 67.43035716350673 | true      | 2025-09-17 | 23         | 
| VIN003     | 2025-09-16 00:00:00 | 120.24295999679852 | 30.143229002199707 | 40.8918776553552 | true      | 2025-09-17 | 23         | 
| VIN001     | 2025-09-16 00:30:00 | 120.24754980497414 | 30.373484773735274 | 49.50436236779409 | true      | 2025-09-17 | 23         | 
| VIN003     | 2025-09-16 00:30:00 | 120.36073125682805 | 30.065016013833237 | 61.82996654036919 | true      | 2025-09-17 | 23         | 
| VIN001     | 2025-09-16 05:00:00 | 120.13891993725622 | 30.39267490566367 | 53.99676876794396 | true      | 2025-09-17 | 23         | 
| VIN003     | 2025-09-16 05:30:00 | 120.04798104849084 | 30.012209889484666 | 65.01092831837522 | true      | 2025-09-17 | 23         | 
| VIN002     | 2025-09-16 20:00:00 | 120.42721760246307 | 30.051330581564144 | 79.73892066615583 | true      | 2025-09-17 | 23         | 
| VIN003     | 2025-09-16 20:00:00 | 120.47715870033818 | 30.302941456112517 | 58.61057150112957 | true      | 2025-09-17 | 23         | 
| VIN001     | 2025-09-16 20:30:00 | 120.3067564206695 | 30.179763514166588 | 47.77533756931095 | true      | 2025-09-17 | 23         | 
+------------+------------+-------------+------------+------------+------------+-------------+------------+------------+

ADS 層

-- ADS 層（ads_vehicle_hourly_stat）
--按小時統計每輛車總行駛裡程（簡單用位置差計算）、平均速度
CREATE TABLE IF NOT EXISTS ads_vehicle_hourly_stat (
  vin           STRING COMMENT '車輛唯一識別碼',
  stat_hour     STRING COMMENT '統計小時(yyyy-MM-dd HH)',
  total_distance DOUBLE COMMENT '總行駛裡程(公裡)',
  avg_speed     DOUBLE COMMENT '平均速度(km/h)'
)
PARTITIONED BY (dt STRING, hh STRING);

--統計加工 SQL
--注意：這裡的“距離”計算我用簡化版公式（實際可用 haversine），以示範為主
-- 簡單按經緯度差近似求距離（展示用）
SET odps.sql.type.system.odps2=true;

WITH ordered AS (
  SELECT
  vin, event_time, lng, lat, speed,
  ROW_NUMBER() OVER (PARTITION BY vin ORDER BY event_time) AS rn
  FROM dwd_vehicle_gps
  WHERE dt='2025-09-17' AND hh='23' AND loc_valid = TRUE
),
with_prev AS (
  SELECT
  a.vin, a.event_time, a.speed,
  -- 歐氏距離簡單近似，這裡1度經緯度差約111公裡，不精準但可示範
  ABS(a.lng - b.lng)*111 AS dx,
  ABS(a.lat - b.lat)*111 AS dy
  FROM ordered a
  LEFT JOIN ordered b
  ON a.vin = b.vin AND a.rn = b.rn + 1
)
INSERT OVERWRITE TABLE ads_vehicle_hourly_stat PARTITION (dt='2025-09-17', hh='23')
  SELECT
  vin,
  '2025-09-17 23' AS stat_hour,
  SUM( SQRT( COALESCE(dx,0)*COALESCE(dx,0) + COALESCE(dy,0)*COALESCE(dy,0) ) ) AS total_distance,
  AVG(speed) AS avg_speed
  FROM with_prev
  GROUP BY vin;

-- 查詢VIN001車輛的行駛裡程和平均時速
SET odps.sql.allow.fullscan=true;
SELECT * FROM ads_vehicle_hourly_stat WHERE vin='VIN001'
  ORDER BY stat_hour DESC;
  
-- 返回結果。
+------------+---------------+--------------------+-------------------+------------+------------+
| vin        | stat_hour     | total_distance     | avg_speed         | dt         | hh         | 
+------------+---------------+--------------------+-------------------+------------+------------+
| VIN001     | 2025-09-17 23 | 1510.7384548492398 | 59.33624859907179 | 2025-09-17 | 23         | 
+------------+---------------+--------------------+-------------------+------------+------------+

SET odps.sql.allow.fullscan=true;
SELECT
vin AS 車輛編碼,
stat_hour AS 統計時間,
CONCAT(CAST(ROUND(total_distance, 2) AS STRING), ' km') AS 行駛裡程,
CONCAT(CAST(ROUND(avg_speed, 2) AS STRING), ' km/h') AS 平均時速
FROM ads_vehicle_hourly_stat WHERE vin='VIN001'
  ORDER BY stat_hour DESC;
  
-- 返回結果。
+------------+---------------+------------+------------+
| 車輛編碼    | 統計時間        | 行駛裡程    | 平均時速     | 
+------------+---------------+------------+------------+
| VIN001     | 2025-09-17 23 | 1510.74 km | 59.34 km/h | 
+------------+---------------+------------+------------+

跨團隊協作與資料安全分享，對第三方使用Spark引擎做資料分析的團隊開啟資料動態脫敏。

-- 跨團隊協作與資料安全分享，對第三方使用Spark引擎做資料分析的團隊開啟資料動態脫敏
CREATE role thirdparty;
GRANT CreateInstance, List ON project <project_name> TO ROLE thirdparty;
GRANT SELECT ON TABLE ods_vehicle_gps_raw TO ROLE thirdparty;

ADD USER RAM$<your aliyun account>;
GRANT thirdparty TO RAM$<your aliyun account>;

對車輛總行駛里程錶去標識化，對車輛唯一編號脫敏，只保留首尾字元，其他字元用“*”代替

-- 專案開啟資料脫敏功能。
setproject odps.data.masking.policy.enable=true;

-- 對車輛總行駛里程錶去標識化，對車輛唯一編號脫敏，只保留首尾字元，其他字元用“*”代替。
CREATE data masking policy IF NOT EXISTS masking_vin
TO role (thirdparty)
USING MASKED_STRING_UNMASKED_BA(1, 1);

apply data masking policy masking_vin bind TO
TABLE ads_vehicle_hourly_stat COLUMN vin;

參數名稱	說明
任务名称	任務名稱，在租戶內命名唯一。
任务周期	5分鐘