MaxCompute:CSV/TSV 外部テーブル

構文

さまざまなフォーマットの外部テーブルを作成するための構文の詳細については、「OSS 外部テーブル」をご参照ください。

• 組み込みテキストデータパーサー

  CSV フォーマット

  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>',...)];

  TSV フォーマット

  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>',...)];

共通パラメーター

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

固有のパラメーター

注意

• どのデータの型でも、CSV データファイル内の空の文字列は、テーブルに読み込まれるときに NULL として解析されます。

• STRUCT、ARRAY、MAP などの複雑な型は、その値がカンマ (,) などの一般的な CSV デリミタと競合しやすく、解析が失敗する可能性があるため、サポートされていません。

• BINARY および INTERVAL 型は現在サポートされていません。これらの型を使用するには、MaxCompute のテクニカルサポートにお問い合わせください。

• INT、SMALLINT、TINYINT、BIGINT、FLOAT、DOUBLE、および DECIMAL データの型では、デフォルトの解析機能は柔軟です。

  基本的な数値文字列のみを解析したい場合は、tblproperties で odps.text.option.smart.parse.level パラメーターを naive に設定できます。これにより、パーサーは "123" や "123.456" などの通常の数値文字列のみをサポートするように構成されます。他の文字列フォーマットを解析するとエラーになります。

• 4 つの日付と時刻関連の型 (DATE、DATETIME、TIMESTAMP、および TIMESTAMP_NTZ) では、基盤となるコードは処理に java.time.format.DateTimeFormatter を使用します。MaxCompute には、いくつかの組み込みのデフォルト解析フォーマットがあります。tblproperties で odps.text.option.<date|datetime|timestamp|timestamp_ntz>.io.format を設定することで、複数の解析フォーマットと 1 つの出力フォーマットを定義することもできます。解析フォーマットはハッシュ記号 (#) で区切ることができます。

  • カスタムフォーマットは、MaxCompute の組み込み解析フォーマットよりも優先度が高くなります。出力フォーマットは、最初のカスタムフォーマットを使用します。

    たとえば、DATE 型のカスタムフォーマット文字列を pattern1#pattern2#pattern3 として定義した場合、pattern1、pattern2、または pattern3 に一致する文字列を解析できます。ただし、ファイルにデータを書き込む場合、出力は pattern1 フォーマットを使用します。詳細については、「DateTimeFormatter」をご参照ください。

  • 特に中国のユーザーは、'z' (タイムゾーン名) は一部のコンテキストであいまいになる可能性があるため、カスタムタイムゾーンフォーマットとして使用しないでください。

    たとえば、中国では 'CST' は通常、中国標準時 (UTC+8) を意味します。しかし、'CST' が java.time.format.DateTimeFormatter によって解析されると、米国中部標準時 (UTC-6) として識別されます。これにより、予期しない入力または出力が発生する可能性があります。代わりに 'x' (ゾーンオフセット) または 'VV' (タイムゾーン ID) パターンを使用してください。

組み込みテキストデータパーサーを使用した 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;

次の結果が返されます。

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

列数が一致しない 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,
     locationLongitude 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 | locationlongitude | 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 |
   +------------+---------------------+---------------------+------------------------+