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

サポートされるデータ型

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

SmartParse の詳細については、「Smart Parse の柔軟な型互換性」をご参照ください。

サポートされる圧縮形式

圧縮済み OSS ファイルから読み取る場合、または圧縮済み 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>',...)];

共通パラメーター

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

形式固有のパラメーター

パラメーター

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

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

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

• セッション／プロジェクト レベルのパラメーター

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

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

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

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

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

    • 最大値は 1000 です。

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

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

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

  テーブルプロパティー	セッションフラグ	結果
  rigid	on	オン、強制的にオン
  	off
  	<null>、無効な値、またはパラメーターが未設定
  flexible	on	オン
  	off	オフ、セッションにより無効化
  	<null>、無効な値、またはパラメーターが未設定	オン
  未設定	on	オン、セッションにより有効化
  	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         | 
   +------------+------------+

前提条件

組み込みテキストパーサーを使用した 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'
)
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')
      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" = ","
   )
   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 のカスタム時刻型の解析および出力フォーマットの詳細については、「Smart Parse の柔軟な型互換性」をご参照ください。

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/'
   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 で埋められます。