全部產品
Search

搜尋本產品

    DataWorks：外部表格

    文件中心

    DataWorks：外部表格

    更新時間：Nov 15, 2024

    本文將為您介紹如何通過DataWorks建立、配置外部表格，以及外部表格支援的欄位類型。

    外部表格概述

    使用外部表格前，您需要瞭解下表中的定義。

    名稱

    描述

    Object Storage Service

    提供標準、低頻、Archive Storage類型，能夠覆蓋不同的儲存情境。同時，OSS能夠與Hadoop開源社區及EMR、批次運算、MaxCompute、機器學習和Function Compute等產品進行深度結合。

    MaxCompute

    MaxCompute為您提供快速且完全託管的資料倉儲解決方案，並可以通過與OSS的結合，高效經濟地分析處理海量資料。

    MaxCompute外部表格

    該功能基於MaxCompute新一代的V2.0計算架構，可以協助您直接對OSS中的海量檔案進行查詢，無需將資料載入至MaxCompute表中。既減少了資料移轉的時間和人力，也節省了儲存的成本。

    下圖為外部表格的整體處理架構。架構圖

    目前，MaxCompute主要支援OSS和OTS等非結構化儲存的外部表格。從資料的流動和處理邏輯的角度，非結構化處理架構在MaxCompute計算平台兩端有耦合地進行資料匯入和匯出。以OSS外部表格為例，處理邏輯如下：

    1. 外部的OSS資料經過非結構化架構轉換，使用JAVA InputStream類提供給您自訂代碼介面。您可以自己實現Extract邏輯，只需要負責對輸入的InputStream進行讀取、解析、轉化和計算，最終返回MaxCompute計算平台通用的Record格式。

    2. 上述Record可以自由參與MaxCompute的SQL邏輯運算，該部分計算基於MaxCompute內建的結構化SQL運算引擎，並可能產生新的Record。

    3. 經過運算的Record傳遞給使用者自訂的Output邏輯，您可以進行進一步的計算轉換，並最終通過系統提供的OutputStream，輸出Record中需要輸出的資訊，由系統負責寫入至OSS。

    您可以通過DataWorks配合MaxCompute，對外部表格進行可視化的建立、搜尋、查詢、配置、加工和分析等操作。

    網路與許可權認證

    由於MaxCompute與OSS是兩個獨立的雲端運算與雲端儲存體服務，所以在不同的部署叢集上的網路連通性有可能影響MaxCompute訪問OSS的資料的可達性。在MaxCompute中訪問OSS儲存時，建議您使用OSS私網地址（即以-internal.aliyuncs.com結尾的host地址）。

    MaxCompute需要有一個安全的授權通道訪問OSS資料。MaxCompute結合了阿里雲的存取控制服務（RAM）和令牌服務（STS）實現對資料的安全訪問。MaxCompute在擷取許可權時，以表的建立者的身份在STS申請許可權（OTS的使用權限設定與OSS一致）。

    1. STS模式授權

      MaxCompute需要直接存取OSS的資料，因此需要將OSS資料相關許可權賦給MaxCompute的訪問帳號。STS是阿里雲為客戶提供的一種安全性權杖管理服務，它是資源訪問管理（RAM）產品中的一員。通過STS服務，獲得許可的雲端服務或RAM使用者，可以自主頒發自訂時效和子許可權的存取權杖。獲得存取權杖的應用程式，可以使用令牌直接調用阿里雲服務API操作資源。

      您可以通過以下兩種方式進行授權：

      • 當MaxCompute和OSS的專案所有者是同一個帳號時，請直接登入阿里雲帳號後進行一鍵授權。

        1. 開啟建立表的編輯頁面，找到物理模型設計模組。

        2. 勾選表類型後的外部表格。

        3. 單擊選擇儲存地址後的一鍵授權。一鍵授權

        4. 單擊雲資源訪問授權對話方塊中的同意授權。1

      • 自訂授權，在RAM中授予MaxCompute訪問OSS的許可權。

        1. 登入RAM控制台。

          說明

          如果MaxCompute和OSS不是同一個帳號，此處需要由OSS帳號登入並授權。

        2. 單擊左側導覽列中的身份管理 > 角色。

        3. 單擊建立角色，選擇可信實體類型為阿里雲帳號，單擊下一步。

        4. 輸入角色名稱和備忘。

          說明

          設定角色名稱為AliyunODPSDefaultRole或AliyunODPSRoleForOtherUser。

        5. 選擇信任的雲帳號為當前雲帳號或其他雲帳號。

          說明

          如果選擇其他雲帳號，請輸入其他雲帳號的ID。

        6. 單擊完成。

        7. 配置角色詳情。

          在RAM角色管理頁面，單擊相應的RAM角色名稱。在信任策略管理頁簽下，單擊編輯信任策略，根據自身情況輸入下述策略內容。 

          --當MaxCompute和OSS的Owner是同一個帳號。
{
"Statement": [
{
"Action": "sts:AssumeRole",
"Effect": "Allow",
"Principal": {
"Service": [
"odps.aliyuncs.com"
      ]
    }
  }
],
"Version": "1"
}                                           
          --當MaxCompute和OSS的Owner不是同一個帳號。
{
"Statement": [
{
"Action": "sts:AssumeRole",
"Effect": "Allow",
"Principal": {
"Service": [
"MaxCompute的Owner雲帳號id@odps.aliyuncs.com"
      ]
    }
  }
],
"Version": "1"
}

          配置完成後，單擊儲存信任策略。

        8. 配置角色授權策略，並找到授予角色訪問OSS必要的許可權AliyunODPSRolePolicy，將許可權AliyunODPSRolePolicy授權給該角色。如果您無法通過搜尋授權找到，可以通過精確授權直接添加。 

          {
    "Version": "1",
    "Statement": [
        {
            "Action": [
                "oss:ListBuckets",
                "oss:GetObject",
                "oss:ListObjects",
                "oss:PutObject",
                "oss:DeleteObject",
                "oss:AbortMultipartUpload",
                "oss:ListParts"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": [
                "ots:ListTable",
                "ots:DescribeTable",
                "ots:GetRow",
                "ots:PutRow",
                "ots:UpdateRow",
                "ots:DeleteRow",
                "ots:GetRange",
                "ots:BatchGetRow",
                "ots:BatchWriteRow",
                "ots:ComputeSplitPointsBySize"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": [
                "pvtz:DescribeRegions",
                "pvtz:DescribeZones",
                "pvtz:DescribeZoneInfo",
                "pvtz:DescribeVpcs",
                "pvtz:DescribeZoneRecords"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": [
                "dlf:CreateFunction",
                "dlf:BatchGetPartitions",
                "dlf:ListDatabases",
                "dlf:CreateLock",
                "dlf:UpdateFunction",
                "dlf:BatchUpdateTables",
                "dlf:DeleteTableVersion",
                "dlf:UpdatePartitionColumnStatistics",
                "dlf:ListPartitions",
                "dlf:DeletePartitionColumnStatistics",
                "dlf:BatchUpdatePartitions",
                "dlf:GetPartition",
                "dlf:BatchDeleteTableVersions",
                "dlf:ListFunctions",
                "dlf:DeleteTable",
                "dlf:GetTableVersion",
                "dlf:AbortLock",
                "dlf:GetTable",
                "dlf:BatchDeleteTables",
                "dlf:RenameTable",
                "dlf:RefreshLock",
                "dlf:DeletePartition",
                "dlf:UnLock",
                "dlf:GetLock",
                "dlf:GetDatabase",
                "dlf:GetFunction",
                "dlf:BatchCreatePartitions",
                "dlf:ListPartitionNames",
                "dlf:RenamePartition",
                "dlf:CreateTable",
                "dlf:BatchCreateTables",
                "dlf:UpdateTableColumnStatistics",
                "dlf:ListTableNames",
                "dlf:UpdateDatabase",
                "dlf:GetTableColumnStatistics",
                "dlf:ListFunctionNames",
                "dlf:ListPartitionsByFilter",
                "dlf:GetPartitionColumnStatistics",
                "dlf:CreatePartition",
                "dlf:CreateDatabase",
                "dlf:DeleteTableColumnStatistics",
                "dlf:ListTableVersions",
                "dlf:BatchDeletePartitions",
                "dlf:ListCatalogs",
                "dlf:UpdateTable",
                "dlf:ListTables",
                "dlf:DeleteDatabase",
                "dlf:BatchGetTables",
                "dlf:DeleteFunction"
            ],
            "Resource": "*",
            "Effect": "Allow"
        }
    ]
}

    2. 使用OSS資料來源

      如果您已建立並儲存了OSS資料來源，請在工作空間列表頁面找到您所建立的工作空間，單擊操作列的管理，在資料來源頁面進行查看和使用。

    建立外部表格

    1. DDL模式建表

      進入資料開發頁面，參見建立並使用MaxCompute表進行DDL模式建表，您只需要遵守正常的MaxCompute文法即可。如果您的STS服務已成功授權，則無需設定odps.properties.rolearn屬性。

      DDL建表語句樣本如下，其中EXTERNAL參數說明該表為外部表格。 

      CREATE EXTERNAL TABLE IF NOT EXISTS ambulance_data_csv_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' --STORED BY用於指定自訂格式StorageHandler的類名或其它外部表格檔案格式，必選。
with SERDEPROPERTIES (
'delimiter'='\\|', --SERDEPROPERTIES序列化屬性參數，可以通過DataAttributes傳遞到Extractor代碼中，可選。
'odps.properties.rolearn'='acs:ram::xxxxxxxxxxxxx:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-shanghai-internal.aliyuncs.com/oss-odps-test/Demo/SampleData/CustomTxt/AmbulanceData/'     --外部表格存放地址，必選。
USING 'odps-udf-example.jar'; --指定自訂格式時類定義所在的Jar包，如果未使用自訂格式無需指定。

      關於STORED BY後接參數，其中CSV或TSV檔案對應預設內建的StorageHandler，具體參數如下：

      • CSV為com.aliyun.odps.CsvStorageHandler ，定義如何讀寫CSV格式資料，資料格式約定資料行分隔符號為英文逗號（,）、分行符號為（\n）。實際參數輸入樣本：STORED BY'com.aliyun.odps.CsvStorageHandler'。

      • TSV為 com.aliyun.odps.TsvStorageHandler，定義如何讀寫TSV格式資料，資料格式約定資料行分隔符號為（\t）、分行符號為（\n）。

      STORED BY後接參數還支援ORC、PARQUET、SEQUENCEFILE、RCFILE、AVRO和TEXTFILE 開源格式外部表格，如下所示。對於textFile可以指定序列化類別，例如org.apache.hive.hcatalog.data.JsonSerDe。

      • org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe -> stored as textfile

      • org.apache.hadoop.hive.ql.io.orc.OrcSerde -> stored as orc

      • org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe -> stored as parquet

      • org.apache.hadoop.hive.serde2.avro.AvroSerDe -> stored as avro

      • org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe -> stored as sequencefile

      對於開源格式外部表格，建表語句如下。 

        CREATE EXTERNAL TABLE [IF NOT EXISTS] (<column schemas>)
  [PARTITIONED BY (partition column schemas)]
  [ROW FORMAT SERDE '']
  STORED AS 
  [WITH SERDEPROPERTIES ( 'odps.properties.rolearn'='${roleran}'
  [,'name2'='value2',...]
  ) ]
  LOCATION 'oss://${endpoint}/${bucket}/${userfilePath}/';

      SERDEPROPERTIES序列化屬性列表如下所示。

      屬性名稱

      屬性值

      預設值

      描述

      odps.text.option.gzip.input.enabled

      true/false

      false

      開啟或關閉讀壓縮

      odps.text.option.gzip.output.enabled

      true/false

      false

      開啟或關閉寫壓縮

      odps.text.option.header.lines.count

      非負整數

      0

      跳過文字檔頭N行

      odps.text.option.null.indicator

      字串

      Null 字元串

      在解析或者寫出NULL值時，代表NULL的字串

      odps.text.option.ignore.empty.lines

      true/false

      true

      是否忽略空行

      odps.text.option.encoding

      UTF-8/UTF-16/US-ASCII

      UTF-8

      指定文本的字元編碼

      說明

      MaxCompute目前僅支援通過內建extractor讀取OSS上gzip壓縮的CSV或TSV資料，您可以選擇檔案是否是gzip壓縮，不同的檔案格式對應不同的屬性設定。

      LOCATION參數，格式為：oss://oss-cn-shanghai-internal.aliyuncs.com/Bucket名稱/目錄名稱。您可以通過圖形對話方塊選擇獲得OSS目錄位址，目錄後無需加檔案名稱。

      DDL模式建立的表會出現在表管理的表節點樹下，可以通過修改其一級、二級主題來調整顯示位置。

    2. OTS外部表格

      OTS外部表格建表語句如下。 

      CREATE EXTERNAL TABLE IF NOT EXISTS ots_table_external(
odps_orderkey bigint,
odps_orderdate string,
odps_custkey bigint,
odps_orderstatus string,
odps_totalprice double
)
STORED BY 'com.aliyun.odps.TableStoreStorageHandler' 
WITH SERDEPROPERTIES (
'tablestore.columns.mapping'=':o_orderkey,:o_orderdate,o_custkey, o_orderstatus,o_totalprice', -- (3)
'tablestore.table.name'='ots_tpch_orders'
'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole'
)
LOCATION 'tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com';

      參數說明如下：

      • com.aliyun.odps.TableStoreStorageHandler是MaxCompute內建的處理TableStore資料的StorageHandler。

      • SERDEPROPERTIES是提供參數選項的介面，在使用TableStoreStorageHandler時，有兩個必須指定的選項：tablestore.columns.mapping和 tablestore.table.name。

        • tablestore.columns.mapping：必選項，用來描述MaxCompute將訪問的Table Store表的列，包括主鍵和屬性以（:）打頭的用來表示Table Store主鍵，例如此語句中的:o_orderkey和:o_orderdate，其它均為屬性列。

          Table Store支援1~4個主鍵，主鍵類型為STRING、INTEGER和BINARY，其中第一個主鍵為分區鍵。指定映射時，您必須提供指定Table Store表的所有主鍵，對於屬性列則沒有必要全部提供，可以只提供需要通過MaxCompute來訪問的屬性列。

        • tablestore.table.name：需要訪問的Table Store表名。如果指定的Table Store表名錯誤（不存在），則會報錯， MaxCompute不會主動去建立Table Store表。

      • LOCATION：用來指定Table Storeinstance名字、endpoint等具體資訊。

    3. 圖形化建表

      進入資料開發頁面，參見建立並使用MaxCompute表進行圖形化建表。外部表格具有如下屬性：

      • 基本屬性

        • 英文表名（在建立表時輸入）

        • 中文表名

        • 一級、二級主題

        • 描述

      • 物理模型設計

        • 表類型：請選擇為外部表格。

        • 分區類型：OTS類型外部表格不支援分區。

        • 選擇儲存地址：即LOCATION參數。您可以在物理模型設計欄中設定LOCATION參數。單擊點擊選擇，即可選擇儲存地址。選擇完成後，單擊一鍵授權。

        • 選擇儲存格式：根據業務需求進行選擇，支援CSV、TSV、ORC、PARQUET、SEQUENCEFILE、RCFILE、AVRO、TEXTFILE和自訂檔案格式。如果您選擇了自訂檔案格式，需要選擇自訂的資源。在提交資源時，可以自動解析出其包含的類名並可以供使用者選取。

        • rolearn：如果STS已授權，可以不填寫。

      • 表結構設計表結構設計

        參數

        描述

        欄位類型

        MaxCompute 2.0支援TINYINT、SMALLINT、INT、BIGINT、VARCHAR和STRING類型。

        操作

        支援新增、修改和刪除。

        長度/設定

        對於VARCHAR類型，可以支援設定長度。對於複雜類型可以直接填寫複雜類型的定義。

    支援的欄位類型

    外部表格支援的簡單欄位類型如下表所示。

    類型

    是否新增

    格式舉例

    描述

    TINYINT

    是

    1Y，-127Y

    8位有符號整型，範圍為-128~127。

    SMALLINT

    是

    32767S， -100S

    16位有符號整型，範圍為-32,768~32,767。

    INT

    是

    1000，-15645787

    32位有符號整型，範圍為-2^31~2^31-1。

    BIGINT

    否

    100000000000L， -1L

    64位有符號整型，範圍為-2^63+1~2^63-1。

    FLOAT

    是

    無

    32位二進位浮點型。

    DOUBLE

    否

    3.1415926 1E+7

    8位元組雙精確度浮點數，64位二進位浮點型。

    DECIMAL

    否

    3.5BD，99999999999.9999999BD

    10進位精確數字類型，整型部分範圍為-1,036+1~1,036-1，小數部分精確到10~18。

    VARCHAR(n)

    是

    無

    變長字元類型，n為長度，取值範圍為1~65,535。

    STRING

    否

    “abc”，’bcd’，”alibaba”

    字串類型，目前長度限制為8MB。

    BINARY

    是

    無

    位元據類型，目前長度限制為8MB。

    DATETIME

    否

    DATETIME ‘2017-11-11 00:00:00’

    日期時間類型，使用東八區時間作為系統標準時間。範圍0000年1月1日~9999年12月31日，精確到毫秒。

    TIMESTAMP

    是

    TIMESTAMP ‘2017-11-11 00:00:00.123456789’

    與時區不轉換的時間戳記類型，範圍為0000年1月1日~9999年12月31日23.59:59.999,999,999，精確到納秒。

    BOOLEAN

    否

    包括TRUE和FALSE

    BOOLEAN類型，取值TRUE或FALSE。

    外部表格支援的複雜欄位類型如下表所示。

    類型

    定義方法

    構造方法

    ARRAY

    array< int >; array< struct< a:int, b:string >>

    array(1, 2, 3); array(array(1, 2); array(3, 4))

    MAP

    map< string, string >; map< smallint, array< string>>

    map(“k1”, “v1”, “k2”, “v2”); map(1S, array(‘a’, ‘b’), 2S, array(‘x’, ‘y))

    STRUCT

    struct< x:int, y:int>; struct< field1:bigint, field2:array< int>, field3:map< int, int>>

    named_struct(‘x’, 1, ‘y’, 2); named_struct(‘field1’, 100L, ‘field2’, array(1, 2), ‘field3’, map(1, 100, 2, 200))

    如果需要使用MaxCompute 2.0支援的新資料類型（TINYINT、SMALLINT、 INT、 FLOAT、VARCHAR、TIMESTAMP 、BINARY或複雜類型），需要在建表語句前加上語句set odps.sql.type.system.odps2=true;，set語句和建表語句一起提交執行。如果需要相容HIVE，建議加上語句odps.sql.hive.compatible=true;。

    查看和處理外部表格

    您可以在資料開發頁面，單擊左側導覽列中的表管理，查詢外部表格，詳情請參見表管理。處理外部表格的方式與內部表基本一致。