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

サポートされるデータ型

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

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

サポートされる圧縮形式

圧縮された 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：スキップを有効にします。この設定は柔軟であり、セッションレベルまたはプロジェクトレベルの構成で上書きできます。

• 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 の場合、この機能は無効になります。

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

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

  テーブルプロパティ	セッションフラグ	結果
  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         | 
   +------------+------------+

前提条件

• MaxCompute プロジェクト。「MaxCompute プロジェクトの作成」をご参照ください。

• MaxCompute プロジェクトと同じリージョンにある OSS バケット。「バケットの作成」および「フォルダーの管理」をご参照ください。MaxCompute は OSS フォルダーを自動的に作成できます。SQL を実行する前に作成する必要はありません。

• Alibaba Cloud アカウント、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'
)
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 で埋められます。