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

サポートされるデータの型

MaxCompute のデータの型の詳細については、「データの型 V1.0」および「データの型 V2.0」をご参照ください。

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

サポートされる圧縮フォーマット

圧縮された OSS ファイルの読み取りまたは書き込みを行う場合、with serdeproperties プロパティを CREATE TABLE 文に追加できます。詳細については、「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：このパラメーターは、柔軟なテーブルレベルのパラメーターを上書きできますが、固定のテーブルレベルのパラメーターは上書きできません。

    • on：アクティブに有効化。このパラメーターがテーブルに構成されていない場合、自動的に有効になります。

    • off：アクティブに無効化。テーブルレベルのパラメーターが flexible に設定されている場合、この機能は無効になります。その他の場合、テーブルレベルのパラメーターが優先されます。

    • <null>/入力が無効な場合：動作はテーブルレベルの構成によってのみ決定されます。

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

    • 最大値は 1000 です。

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

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

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

  tbl プロパティ	セッションフラグ	結果
  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 specified>

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

   テーブルレベルのパラメーターが構成されていない

   -- テーブルレベルのパラメーターは構成されていません。エラーは発生時に報告されます。動作はセッションレベルのフラグによって制御されます。
   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://databucketpath>';

   テーブルレベルのパラメーターはエラー行をスキップするように設定されているが、セッションレベルで無効にできる

   -- テーブルレベルのパラメーターはエラー行をスキップするように設定されていますが、セッションレベルのフラグによってアクティブに無効にできます。
   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://databucketpath>'
   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://databucketpath>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'rigid'  -- 強制的に有効化。
   );

3. クエリ結果の検証

   テーブルレベルのパラメーターが構成されていない

   -- セッションレベルで有効化。
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- セッションレベルで無効化。テーブルレベルのパラメーターが 'flexible' に設定されている場合、この機能は無効になります。テーブルレベルのパラメーターが 'rigid' に設定されている場合、この設定は効果がありません。
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- セッションレベルで問題のある行データを出力。最大 1,000 行まで出力できます。値が 0 以下の場合、出力は無効になります。
   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' に設定されている場合、この機能は無効になります。テーブルレベルのパラメーターが 'rigid' に設定されている場合、この設定は効果がありません。
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- セッションレベルで問題のある行データを出力。最大 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

   テーブルレベルのパラメーターはエラー行をスキップするように設定されており、セッションレベルで無効にできない

   -- セッションレベルで有効化。
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- セッションレベルで無効化。テーブルレベルのパラメーターが 'flexible' に設定されている場合、この機能は無効になります。テーブルレベルのパラメーターが 'rigid' に設定されている場合、この設定は効果がありません。
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- セッションレベルで問題のある行データを出力。最大 1,000 行まで出力できます。値が 0 以下の場合、出力は無効になります。
   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 プロジェクトが作成されていること。

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

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

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

• 権限付与

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

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

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

対応する OSS ファイルの最初の行をテーブルヘッダーとして指定して 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 です。別のロールを使用する場合は、aliyunodpsdefaultrole をご利用のロール名に置き換え、そのロールに OSS へのアクセス権限を付与してください。

次の結果が返されます：

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

列数が OSS データの列数と一致しない OSS 外部テーブルの作成

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 外部テーブルの作成

この例では、組み込みオープンソースパーサを使用して 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          | 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          | 
   +------------+------------+------------+------------+------------------+-------------------+------------+------------+

カスタムの日付と時刻のデータの型を持つ CSV 形式の OSS 外部テーブルの作成

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

1. 各時刻データの型 (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 データの読み取り時に「列数の不一致」エラーが報告される

• 症状

  CSV/TSV ファイルの列数が外部テーブルの DDL の列数と一致しない場合、CSV/TSV データを読み取るときに「列数の不一致」エラーが報告されます。例：FAILED: ODPS-0123131:User defined function exception - Traceback:java.lang.RuntimeException: SCHEMA MISMATCH:xxx。

• 解決策

  セッションレベルで odps.sql.text.schema.mismatch.mode パラメーターを設定することで、出力を制御できます：

  • SET odps.sql.text.schema.mismatch.mode=error：列数が一致しない場合、システムはエラーを報告します。

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