自訂解析器 - MaxCompute

本文介紹通過自訂解析器建立OSS外部表格以及讀取和寫入資料的方法。

適用範圍

OSS外部表格不支援cluster屬性。
單個檔案大小不能超過2GB，如果檔案過大，建議拆分。

建立外部表格

文法說明

CREATE EXTERNAL TABLE [IF NOT EXISTS] mc_oss_extable_name
(
  col_name date_type,
  ...
)
[comment table_comment]
[partitioned BY (col_name data_type, ...)] 
stored BY '<YOUR_DEFINED_STORAGEHANDLER>' 
WITH serdeproperties (
  ['property_name'='property_value',...]
) 
location 'oss_location' 
USING 'jar_name';

自訂解析器預設不會對資料分區，以避免解析器出現正確性問題，如果您確認能夠處理分區，可通過如下命令啟用資料分區功能，即啟動多個Mapper。

SET odps.sql.unstructured.data.single.file.split.enabled=true;

公用參數

公用參數說明請參見基礎文法參數說明。

專屬參數

參數名稱	是否必填	說明
your_defined_storagehandler	是	通過編寫MaxCompute UDF自訂的解析器，更多編寫MaxCompute UDF資訊，請參見開發UDF。
jar_name	是	指定自訂解析器代碼對應的JAR包，該JAR包需要添加為MaxCompute專案資源。更多添加資源資訊，請參見資源操作。
resource_name	否	當您使用自訂的serde class時，需要指定依賴的資源。資源中包含了自訂的serde class。 serde class相關的JAR包需要添加為MaxCompute專案資源。更多添加資源資訊，請參見資源操作。

資料寫入

MaxCompute寫入文法詳情，請參見將資料寫入OSS。

查詢分析

SELECT文法詳情，請參見讀取OSS資料。
最佳化查詢計劃詳情，請參見查詢最佳化。

樣本：通過自訂解析器建立OSS外部表格

與附錄：準備樣本資料中的SampleData/（自訂解析器）目錄建立映射關係。操作流程如下：

前置準備
- 已建立MaxCompute專案。
- 已準備好OSS儲存空間（Bucket）、OSS目錄。具體操作請參見建立儲存空間、管理目錄。
  MaxCompute已支援在OSS側自動建立目錄，對於攜帶外部表格及UDF的SQL語句，可以通過一條SQL語句執行讀寫外部表格及UDF的操作。原手動建立目錄方式仍然支援。
  由於MaxCompute只在部分地區部署，跨地區的資料連通性可能存在問題，因此建議Bucket與MaxCompute專案所在地區保持一致。
- 授權
  - 已具備訪問OSS的許可權。阿里雲帳號（主帳號）、RAM使用者或RAMRole身份可以訪問OSS外部表格，授權資訊請參見OSS的STS模式授權。
  - 已具備在MaxCompute專案中建立表（CreateTable）的許可權。表操作的許可權資訊請參見MaxCompute許可權。
使用MaxCompute Studio建立TextExtractor.java、TextExtractor.java、SplitReader.java和TextStorageHandler.java四個Java類。更多開發Java程式詳情，請參見開發UDF。
通過MaxCompute Studio的一鍵式打包功能，將其中的TextStorageHandler.java打包並上傳為MaxCompute資源。
假設資源名稱為javatest-1.0-SNAPSHOT.jar。更多打包上傳資訊，請參見打包、上傳及註冊。
說明
如果有多個依賴請分別打包後上傳為MaxCompute資源。

執行如下命令建立OSS外部表格。

CREATE EXTERNAL TABLE ambulance_data_txt_external
(
  vehicleId INT,
  recordId INT,
  patientId INT,
  calls INT,
  locationLatitute DOUBLE,
  locationLongtitue DOUBLE,
  recordTime STRING,
  direction STRING
)
stored BY 'com.aliyun.odps.udf.example.text.TextStorageHandler' 
  WITH serdeproperties (
    'delimiter'='|',  
    'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
  )
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/SampleData/'
USING 'javatest-1.0-SNAPSHOT.jar'; 

-- 您可以執行desc extended ambulance_data_txt_external;命令查看建立好的外部表格結構資訊。

說明

其中delimiter是使用者自訂的、用來決定OSS檔案每行資料不同列值之間的分隔字元，使用者可以選擇任意一個合法的字串。

從OSS讀取資料，命令樣本如下。

SELECT recordId, patientId, direction FROM ambulance_data_txt_external WHERE patientId > 25;

返回結果如下。

+----------+-----------+-----------+
| recordid | patientid | direction |
+----------+-----------+-----------+
| 1        | 51        | S         |
| 3        | 48        | NE        |
| 4        | 30        | W         |
| 5        | 47        | S         |
| 7        | 53        | N         |
| 8        | 63        | SW        |
| 10       | 31        | N         |
+----------+-----------+-----------+

向OSS外部表格寫入資料。

INSERT INTO ambulance_data_txt_external VALUES (1,16,76,1,'46.81006','-92.08174','9/14/2014 0:10','SW');

-- 重新查詢看是否寫入成功；也可看oss目錄下是否產生了新檔案。
SELECT * FROM ambulance_data_txt_external WHERE recordId='16';

常見問題

自訂Extractor在讀取非結構化資料時，如果資料欄位存在DATETIME類型，報錯ODPS-0123131，如何解決？

問題現象

自訂Extractor在讀取非結構化資料時，如果資料欄位存在DATETIME類型（例如2019-11-11 06:43:36），會返回如下報錯。

FAILED: ODPS-0123131:User defined function exception - Traceback:
java.lang.IllegalArgumentException
    at java.sql.Date.valueOf(Date.java:143)
    at com.aliyun.odps.udf.example.text.TextExtractor.textLineToRecord(TextExtractor.java:194)
    at com.aliyun.odps.udf.example.text.TextExtractor.extract(TextExtractor.java:153)
    at com.aliyun.odps.udf.ExtractorHandler.extract(ExtractorHandler.java:120)

產生原因
查看指定位置的代碼Date.valueOf(parts[i])，其中java.sql.Date.valueOf()函數只支援形如"yyyy-[m]m-[d]d"的STRING型別參數，不支援DATETIME時間型別參數。

解決措施

引入Joda-Time依賴並在代碼中增加匯入資訊。

-- 依賴。
<dependency>
  <groupId>joda-time</groupId>
  <artifactId>joda-time</artifactId>
  <version>2.10</version>
</dependency> 
-- 匯入資訊。
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

引入DateTimeFormat.forPattern()函數，將DATETIME類型的日期格式轉化為STRING類型進行讀取。

record.setDate(index, new Date(DateTime.parse(parts[i], DateTimeFormat.forPattern("yyyy-MM-dd HH:mi:ss")).getMillis()));

使用樣本如下。

通過MaxCompute用戶端上傳Extractor專案打包產生的JAR包。
```
add jar /Users/gary/big_data/odps/text_extractor/target/text_extractor-1.0-SNAPSHOT.jar      
```
/Users/gary/big_data/odps/text_extractor/target/text_extractor-1.0-SNAPSHOT.jar為產生JAR包的本地儲存路徑。
通過MaxCompute用戶端上傳Joda-Time第三方JAR包。
```
add jar /Users/gary/.m2/repository/joda-time/joda-time/2.10/joda-time-2.10.jar                         
```
/Users/gary/.m2/repository/joda-time/joda-time/2.10/joda-time-2.10.jar為Joda-Time第三方JAR包本地存放路徑。

上傳測試資料至OSS指定的目錄下。假設檔案名稱為video_play_log.txt，樣本資料如下。

5c661071dba64d5080c91da085ff1073^音樂-點擊-快進^26.12.XX.XX^2019-11-11 06:43:36

通過外部表格讀取資料。

select * from <project_name>.video_play_log;

讀取結果如下。

+------+-------+---+----------------+
| uuid  | action  | ip  | time      |
+------+-------+---+----------------+
| 5c661071dba64d5080c91da085ff1073 | 音樂-點擊-快進 | 26.12.XX.XX | 2019-11-11 06:43:36 |
+------+-------+---+----------------+