MaxCompute:CSV および TSV 外部テーブル

サポートされているデータ型

MaxCompute のデータ型の詳細については、「データ型バージョン 1.0」および「データ型バージョン 2.0」をご参照ください。

スマート解析の詳細については、「スマート解析による柔軟な型互換性」をご参照ください。

サポートされている圧縮形式

圧縮された OSS ファイルの読み取りまたは書き込みを行う場合、CREATE TABLE 文に with serdeproperties 属性を含める必要があります。詳細については、「with serdeproperties 属性パラメーター」をご参照ください。

サポートされているスキーマ進化

構文

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)] 
STORED BY 'com.aliyun.odps.CsvStorageHandler'  
[WITH serdeproperties (
  ['<property_name>'='<property_value>',...]
)] 
LOCATION '<oss_location>'
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)] 
STORED BY 'com.aliyun.odps.TsvStorageHandler'  
[WITH serdeproperties (
  ['<property_name>'='<property_value>',...]
)] 
LOCATION '<oss_location>'
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
[WITH serdeproperties (
  ['<property_name>'='<property_value>',...]
)]
STORED AS TEXTFILE 
LOCATION '<oss_location>' 
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

共通パラメーター

共通パラメーターの詳細については、「基本構文パラメーター」をご参照ください。

形式固有のパラメーター

ホワイトリストとブラックリスト

MaxCompute の OSS 外部テーブルは、ホワイトリストとブラックリストによるフィルタリングをサポートしています。tblproperties でホワイトリストとブラックリストのパラメーターを設定することで、ディレクトリから読み取るファイルをフィルタリングできます。詳細については、「ホワイトリストとブラックリスト」をご参照ください。

パラメーター

• テーブルレベルのパラメーター：odps.text.option.bad.row.skipping

  • rigid：スキップを強制します。この設定は、セッションレベルまたはプロジェクトレベルの構成でオーバーライドできません。

  • flexible：スキップを有効にします。この設定は柔軟であり、セッションレベルまたはプロジェクトレベルの構成でオーバーライドできます。

• session/project レベルのパラメーター

  • odps.sql.unstructured.text.bad.row.skipping パラメーターは、flexible のテーブルレベルパラメーターをオーバーライドできますが、rigid のパラメーターはオーバーライドできません。

    • on：機能を有効にします。テーブルに機能が構成されていない場合、デフォルトで有効になります。

    • off：機能を無効にします。テーブルが flexible として構成されている場合、機能は無効になります。それ以外の場合、テーブルパラメーターの設定が使用されます。

    • <null> または無効な入力：テーブルレベルの構成が使用されます。

  • odps.sql.unstructured.text.bad.row.skipping.debug.num：Logview の標準出力に出力するエラー結果の数を指定します。

    • 最大値は 1000 です。

    • 値が <=0 の場合、この機能は無効になります。

    • 値が無効な場合、この機能は無効になります。

• セッションレベルのパラメーターとテーブルプロパティの相互作用

  tbl プロパティ	セッションフラグ	結果
  rigid	on	On、強制的に On
  	off
  	<null>、無効な値、またはパラメーターが構成されていない
  flexible	on	On
  	off	Off、セッションによって無効化
  	<null>、無効な値、またはパラメーターが構成されていない	On
  構成されていない	on	On、セッションによって有効化
  	off	Off
  	<null>、無効な値、またはパラメーターが構成されていない

例

1. データの準備

   不正な行を含むテストデータファイル csv_bad_row_skipping.csv を、OSS のディレクトリ (例：oss-mc-test/badrow/) にアップロードします。

2. CSV 外部テーブルの作成

   以下の例は、テーブルレベルとセッションレベルのパラメーターの異なる組み合わせに基づく 3 つのシナリオを示しています。

   • テーブルパラメーター：odps.text.option.bad.row.skipping = flexible | rigid | <not set>

   • セッションフラグ： odps.sql.unstructured.text.bad.row.skipping = on | off | <not set>

   パラメーター未設定

   -- テーブルレベルのパラメーターは設定されていません。セッションレベルのフラグでオーバーライドされない限り、不正な行でクエリは失敗します。
   CREATE EXTERNAL TABLE test_csv_bad_data_skipping_flag
   (
     a INT,
     b INT
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   location '<oss://<your-bucket-name>/<your-file-path>/>';

   柔軟なスキップ

   -- テーブルは不正な行をスキップするように構成されていますが、これはセッションレベルのフラグで無効にできます。
   CREATE EXTERNAL TABLE test_csv_bad_data_skipping_flexible
   (
     a INT,
     b INT
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   location '<oss://<your-bucket-name>/<your-file-path>/>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'flexible'   -- 柔軟なスキップを有効にします。これはセッションレベルで無効にできます。
   );

   厳格なスキップ

   -- テーブルは不正な行を強制的にスキップするように構成されています。これはセッションレベルで無効にできません。
   CREATE EXTERNAL TABLE test_csv_bad_data_skipping_rigid
   (
     a INT,
     b INT
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   location '<oss://<your-bucket-name>/<your-file-path>/>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'rigid'  -- スキップを強制的にオンにします。
   );

3. クエリ結果の検証

   パラメーター未設定

   -- 次のコマンドはスキップを有効にしますが、この例の次のコマンドによってすぐにオーバーライドされます。
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- このコマンドはスキップを無効にし、以下の SELECT クエリの有効な設定となり、クエリは失敗します。
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- スキップが有効な場合にスキップされた行の詳細を出力するためにこのコマンドを使用できます。クエリが失敗するため、ここでは効果がありません。
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flag;

   クエリは次のエラーで失敗します：FAILED: ODPS-0123131:User defined function exception

   柔軟なスキップ

   -- 次のコマンドはスキップを有効にしますが、この例の次のコマンドによってすぐにオーバーライドされます。
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- このコマンドはスキップを無効にし、テーブルの 'flexible' 設定をオーバーライドします。これは以下の SELECT クエリの有効な設定となり、クエリは失敗します。
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- セッションレベルで最大 10 件の不正な行の詳細を出力します。最大値は 1,000 です。0 以下の値は出力を無効にします。
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flexible;

   クエリは次のエラーで失敗します：FAILED: ODPS-0123131:User defined function exception

   厳格なスキップ

   -- 'rigid' 設定がすでにスキップを強制しているため、このコマンドは冗長です。
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- このコマンドはスキップを無効にしようとしますが、'rigid' テーブル設定はオーバーライドできないため、無視されます。
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- このコマンドは、'rigid' 設定によってスキップされた最大 10 件の不正な行の詳細を出力します。
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_rigid;

   次の結果が返されます：

   +------------+------------+
   | a          | b          | 
   +------------+------------+
   | 1          | 26         | 
   | 5          | 37         | 
   +------------+------------+

組み込み CSV/TSV パーサー (OpenCSVSerde)

組み込み CSV/TSV パーサー (OpenCSVSerde) は、CSV ファイルの各データ行が \r\n などの文字で区切られ、列に \r\n が含まれていないことを要求します。並列分割とデータ整合性のロジックは次のとおりです：

• まず、ファイルは分割サイズによって分割され、一部の行は途中で切断される可能性があります。

• 後続のワーカーが分割を消費する際、最初の分割を除くすべての分割は、先頭の部分的または完全な行を積極的にスキップします。

• 各ワーカーは、次の分割の範囲内であっても、末尾の部分的または完全な行を積極的に消費する必要があります。

このパーサーは並列分割をサポートしますが、引用符のエスケープはサポートしていません。

オープンソース CSV パーサー (CsvStorageHandler / TsvStorageHandler)

オープンソース CSV パーサー (CsvStorageHandler / TsvStorageHandler) は、CSV/TSV ファイルを読み取る際に引用符のエスケープを考慮し、\r\n をサポートします。ネストされた引用符や \r\n などの特殊文字が引用符内に現れるシナリオを処理できます。

たとえば、データ "a\ra","b\nb","cc""cc" では、\r\n と二重引用符が正しく解析および出力され、複数行にまたがる値をサポートします。ただし、実際の改行位置はデータ解析を通じてのみ決定できるため、ファイルを単純に \r\n で分割することはできません。その結果、単一ファイルの並列消費はサポートされていません。

データ列に \r\n が含まれず、\r\n が行区切り文字としてのみ使用されていることを確認した場合、odps.sql.unstructured.data.single.file.split.enabled を設定することで並列分割を有効にできます。この場合、大きなファイルは分割サイズによって複数の分割に分割され、組み込みの Text Extractor が自動的に改行境界に整列してデータ整合性を確保します。

前提条件

• MaxCompute プロジェクトが作成されていること。

• OSS バケットとフォルダが利用可能であること。詳細については、「バケットの作成」および「フォルダの管理」をご参照ください。

  MaxCompute は OSS でのフォルダの自動作成をサポートしています。SQL ステートメントに外部テーブルとユーザー定義関数 (UDF) が含まれる場合、単一のステートメントを使用してテーブルの読み取りと書き込み、および UDF の使用ができます。フォルダを手動で作成することもできます。

  MaxCompute は特定のリージョンにのみデプロイされています。クロスリージョンデータ接続に関する潜在的な問題を避けるため、ご利用の OSS バケットが MaxCompute プロジェクトと同じリージョンにあることを確認してください。

• 権限付与

  • OSS にアクセスする権限が必要です。Alibaba Cloud アカウント (root ユーザー)、Resource Access Management (RAM) ユーザー、または RAM ロールを使用して OSS 外部テーブルにアクセスできます。権限付与の詳細については、「OSS の STS モードでのアクセス権限付与」をご参照ください。

  • MaxCompute プロジェクトで CreateTable 権限が必要です。テーブル権限の詳細については、「MaxCompute の権限」をご参照ください。

組み込みテキストパーサーを使用した OSS 外部テーブルの作成

ヘッダー行を持つ外部テーブルの作成

サンプルデータから oss-mc-test バケットに Demo11 ディレクトリを作成し、次のステートメントを実行します：

--外部テーブルを作成します。
CREATE EXTERNAL TABLE mf_oss_wtt
(
  id BIGINT,
  name STRING,
  tran_amt DOUBLE
)
STORED BY 'com.aliyun.odps.CsvStorageHandler'
WITH serdeproperties (
  'odps.text.option.header.lines.count' = '1',
  'odps.sql.text.option.flush.header' = 'true',
  'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo11/';

--データを挿入します。
INSERT OVERWRITE TABLE mf_oss_wtt VALUES (1, 'val1', 1.1),(2, 'value2', 1.3);

--データをクエリします。
--テーブルを作成する際、すべての列を STRING として定義できます。そうしないと、ヘッダーが読み取られるときにエラーが発生します。
--または、テーブル定義に 'odps.text.option.header.lines.count' = '1' パラメーターを追加してヘッダーをスキップします。
SELECT * FROM mf_oss_wtt;

この例では、aliyunodpsdefaultrole RAM ロールを使用しています。別の RAM ロールを使用する場合は、aliyunodpsdefaultrole を対象の RAM ロールの名前に置き換え、OSS にアクセスするために必要な権限を付与してください。

コマンドは次の結果を返します：

+----------+--------+------------+
| id       | name   | tran_amt   |
+----------+--------+------------+
| 1        | val1   | 1.1        |
| 2        | value2 | 1.3        |
+----------+--------+------------+

列が不一致な外部テーブルの作成

1. サンプルデータから oss-mc-test バケットに demo ディレクトリを作成し、test.csv ファイルをアップロードします。test.csv ファイルには次の内容が含まれています：

   1,kyle1,this is desc1
   2,kyle2,this is desc2,this is two
   3,kyle3,this is desc3,this is three, I have 4 columns

2. 外部テーブルを作成します。

   1. 列数が一致しない行の処理方法を TRUNCATE に設定します。

      -- テーブルを削除します。
      DROP TABLE test_mismatch;
      -- 外部テーブルを作成します。
      CREATE EXTERNAL TABLE IF NOT EXISTS test_mismatch
      (
        id string,
        name string,
        dect string,
        col4 string
      )
      STORED BY 'com.aliyun.odps.CsvStorageHandler'
      WITH serdeproperties (
        'odps.sql.text.schema.mismatch.mode' = 'truncate',
        'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole')
      LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/';

   2. 列数が一致しない行の処理方法を IGNORE として指定します。

      -- テーブルを削除します。
      DROP TABLE test_mismatch01;
      -- 外部テーブルを作成します。
      CREATE EXTERNAL TABLE IF NOT EXISTS test_mismatch01
      (
        id STRING,
        name STRING,
        dect STRING,
        col4 STRING
      )
      STORED BY 'com.aliyun.odps.CsvStorageHandler'
      WITH serdeproperties ('odps.sql.text.schema.mismatch.mode' = 'ignore')
      LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/';

   3. テーブル内のデータをクエリします。

      • test_mismatch テーブルをクエリします。

        SELECT * FROM test_mismatch;
        
        --返された結果
        +----+-------+---------------+---------------+
        | id | name  | dect          | col4          |
        +----+-------+---------------+---------------+
        | 1  | kyle1 | this is desc1 | NULL          |
        | 2  | kyle2 | this is desc2 | this is two   |
        | 3  | kyle3 | this is desc3 | this is three |
        +----+-------+---------------+---------------+

      • test_mismatch01 テーブルをクエリします。

        SELECT * FROM test_mismatch01;
        
        --返された結果
        +----+-------+----------------+-------------+
        | id | name  | dect           | col4        |
        +----+-------+----------------+-------------+
        | 2  | kyle2 | this is desc2  | this is two  
        +----+-------+----------------+-------------+

オープンソースパーサーを使用した外部テーブルの作成

この例では、組み込みのオープンソースパーサーを使用して OSS 外部テーブルを作成し、ヘッダー行とフッター行を無視してコンマ区切りファイルを読み取る方法を示します。

1. サンプルデータから oss-mc-test バケットに demo-test ディレクトリを作成し、test.csv ファイルをアップロードします。

   テストファイルには次のデータが含まれています：

   1,1,51,1,46.81006,-92.08174,9/14/2014 0:00,S
   1,2,13,1,46.81006,-92.08174,9/14/2014 0:00,NE
   1,3,48,1,46.81006,-92.08174,9/14/2014 0:00,NE
   1,4,30,1,46.81006,-92.08174,9/14/2014 0:00,W
   1,5,47,1,46.81006,-92.08174,9/14/2014 0:00,S
   1,6,9,1,46.81006,-92.08174,9/15/2014 0:00,S
   1,7,53,1,46.81006,-92.08174,9/15/2014 0:00,N
   1,8,63,1,46.81006,-92.08174,9/15/2014 0:00,SW
   1,9,4,1,46.81006,-92.08174,9/15/2014 0:00,NE
   1,10,31,1,46.81006,-92.08174,9/15/2014 0:00,N

2. 外部テーブルを作成し、区切り文字としてコンマを指定し、ヘッダー行とフッター行を無視するパラメーターを設定します。

   CREATE EXTERNAL TABLE ext_csv_test08
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
   WITH serdeproperties (
   "separatorChar" = ",",
   'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   stored AS textfile
   location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/***/'
   -- ヘッダー行とフッター行を無視するパラメーターを設定します。
   TBLPROPERTIES (
   "skip.header.line.COUNT"="1",
   "skip.footer.line.COUNT"="1"
   )
   ;

3. 外部テーブルからデータを読み取ります。

   SELECT * FROM ext_csv_test08;
   
   -- ヘッダー行とフッター行が無視されるため、結果には 8 行のデータが含まれます。
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime     | direction  |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 1          | 2          | 13         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE         |
   | 1          | 3          | 48         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE         |
   | 1          | 4          | 30         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | W          |
   | 1          | 5          | 47         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | S          |
   | 1          | 6          | 9          | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | S          |
   | 1          | 7          | 53         | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | N          |
   | 1          | 8          | 63         | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | SW         |
   | 1          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | NE         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

カスタム時刻型を持つ CSV 外部テーブルの作成

CSV のカスタム時刻型の解析および出力形式の詳細については、「スマート解析による柔軟な型互換性」をご参照ください。

1. DATE、DATETIME、TIMESTAMP、TIMESTAMP_NTZ など、さまざまな時刻データ型を使用する CSV 外部テーブルを作成します。

   CREATE EXTERNAL TABLE test_csv
   (
     col_date DATE,
     col_datetime DATETIME,
     col_timestamp TIMESTAMP,
     col_timestamp_ntz TIMESTAMP_NTZ
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler'
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   TBLPROPERTIES (
     'odps.text.option.date.io.format' = 'MM/dd/yyyy',
     'odps.text.option.datetime.io.format' = 'yyyy-MM-dd-HH-mm-ss x',
     'odps.text.option.timestamp.io.format' = 'yyyy-MM-dd HH-mm-ss VV',
     'odps.text.option.timestamp_ntz.io.format' = 'yyyy-MM-dd HH:mm:ss.SS'
   );
   
   INSERT OVERWRITE test_csv 
     VALUES(DATE'2025-02-21', DATETIME'2025-02-21 08:30:00', TIMESTAMP'2025-02-21 12:30:00', TIMESTAMP_NTZ'2025-02-21 16:30:00.123456789');

2. データが挿入された後、CSV ファイルの内容は次のようになります：

   02/21/2025,2025-02-21-08-30-00 +08,2025-02-21 12-30-00 Asia/Shanghai,2025-02-21 16:30:00.12

3. データを再度クエリして結果を表示します。

   SELECT * FROM test_csv;

   コマンドは次の結果を返します：

   +------------+---------------------+---------------------+------------------------+
   | col_date   | col_datetime        | col_timestamp       | col_timestamp_ntz      |
   +------------+---------------------+---------------------+------------------------+
   | 2025-02-21 | 2025-02-21 08:30:00 | 2025-02-21 12:30:00 | 2025-02-21 16:30:00.12 |
   +------------+---------------------+---------------------+------------------------+

列数の不一致エラー

• 症状

  このエラーは、CSV または TSV ファイルの行の列数が、外部テーブル DDL で定義された列数と一致しない場合に発生します。MaxCompute は FAILED: ODPS-0123131:User defined function exception - Traceback:java.lang.RuntimeException: SCHEMA MISMATCH:xxx のようなエラーを報告します。

• 解決策

  セッションレベルで odps.sql.text.schema.mismatch.mode パラメーターを設定することで、MaxCompute が不一致をどのように処理するかを制御できます：

  • SET odps.sql.text.schema.mismatch.mode=error：列数の不一致が発生した場合にクエリを失敗させます。これはデフォルトの動作です。

  • SET odps.sql.text.schema.mismatch.mode=truncate：行の列数が外部テーブル DDL で定義されたものより多い場合、余分な列は破棄されます。行の列数が少ない場合、欠落している列は NULL で埋められます。