MaxCompute：Tabel eksternal CSV/TSV

Sintaks

Untuk informasi lebih lanjut tentang sintaks pembuatan tabel eksternal dalam berbagai format, lihat Tabel eksternal OSS.

• Parser data teks bawaan

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

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

• Parser data open source bawaan

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

Parameter umum

Untuk informasi lebih lanjut tentang parameter umum, lihat Parameter sintaks dasar.

Parameter unik

Catatan

• Untuk semua tipe data, string kosong dalam file data CSV diurai sebagai NULL saat dibaca ke dalam tabel.

• Tipe kompleks seperti STRUCT, ARRAY, dan MAP tidak didukung karena nilainya dapat dengan mudah bertentangan dengan pemisah CSV umum, seperti koma (,), yang dapat menyebabkan penguraian gagal.

• Tipe BINARY dan INTERVAL saat ini tidak didukung. Untuk menggunakan tipe-tipe ini, Anda dapat menghubungi dukungan teknis MaxCompute.

• Untuk tipe data INT, SMALLINT, TINYINT, BIGINT, FLOAT, DOUBLE, dan DECIMAL, kemampuan penguraian default bersifat fleksibel.

  Jika Anda ingin hanya mengurai string angka dasar, Anda dapat mengatur parameter odps.text.option.smart.parse.level ke naive di tblproperties. Ini mengonfigurasi parser untuk hanya mendukung string angka biasa, seperti "123" dan "123.456". Mengurai format string lainnya menghasilkan kesalahan.

• Untuk keempat tipe terkait tanggal dan waktu (DATE, DATETIME, TIMESTAMP, dan TIMESTAMP_NTZ), kode dasar menggunakan java.time.format.DateTimeFormatter untuk pemrosesan. MaxCompute memiliki beberapa format penguraian default bawaan. Anda juga dapat mendefinisikan beberapa format penguraian dan satu format keluaran dengan mengatur odps.text.option.<date|datetime|timestamp|timestamp_ntz>.io.format di tblproperties. Anda dapat memisahkan format penguraian dengan simbol hash (#).

  • Format kustom memiliki prioritas lebih tinggi daripada format penguraian bawaan MaxCompute. Format keluaran menggunakan format kustom pertama.

    Misalnya, jika Anda mendefinisikan string format kustom untuk tipe DATE sebagai pattern1#pattern2#pattern3, Anda dapat mengurai string yang cocok dengan pattern1, pattern2, atau pattern3. Namun, saat Anda menulis data ke file, keluaran menggunakan format pattern1. Untuk informasi lebih lanjut, lihat DateTimeFormatter.

  • Jangan gunakan 'z' (nama zona waktu) sebagai format zona waktu kustom, terutama untuk pengguna di Tiongkok, karena 'z' bisa ambigu dalam beberapa konteks.

    Misalnya, 'CST' biasanya berarti China Standard Time (UTC+8) di Tiongkok. Namun, saat 'CST' diurai oleh java.time.format.DateTimeFormatter, itu diidentifikasi sebagai US Central Standard Time (UTC-6). Hal ini dapat menyebabkan masukan atau keluaran yang tidak terduga. Gunakan pola 'x' (offset-zona) atau 'VV' (ID zona waktu) sebagai gantinya.

Buat tabel eksternal OSS menggunakan parser data teks bawaan

Buat tabel eksternal OSS dan tentukan baris pertama file OSS yang sesuai sebagai header

Buat folder Demo11 di bucket oss-mc-test dari data sampel dan jalankan pernyataan berikut:

--Buat tabel eksternal.
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/';

--Masukkan data.
INSERT OVERWRITE TABLE mf_oss_wtt VALUES (1, 'val1', 1.1),(2, 'value2', 1.3);

--Kueri data.
--Saat membuat tabel, Anda dapat mendefinisikan semua bidang sebagai STRING. Jika tidak, kesalahan terjadi saat header dibaca.
--Atau, tambahkan parameter 'odps.text.option.header.lines.count' = '1' saat membuat tabel untuk melewati header.
SELECT * FROM mf_oss_wtt;

Hasil berikut dikembalikan:

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

Buat tabel eksternal OSS dengan jumlah kolom yang tidak cocok

1. Buat folder demo di bucket oss-mc-test dari data sampel dan unggah file test.csv. File test.csv berisi konten berikut.

   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. Buat tabel eksternal.

   1. Atur perilaku untuk baris dengan jumlah kolom yang tidak cocok ke TRUNCATE.

      --Hapus tabel.
      DROP TABLE test_mismatch;
      --Buat tabel eksternal baru.
      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. Atur perilaku untuk baris dengan jumlah kolom yang tidak cocok ke IGNORE.

      --Hapus tabel.
      DROP TABLE test_mismatch01;
      --Buat tabel eksternal baru.
      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. Kueri data tabel.

      • Kueri tabel test_mismatch.

        SELECT * FROM test_mismatch;
        
        --Hasil
        +----+-------+---------------+---------------+
        | 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 |
        +----+-------+---------------+---------------+

      • Kueri tabel test_mismatch01.

        SELECT * FROM test_mismatch01;
        
        --Hasil
        +----+-------+----------------+-------------+
        | id | name  | dect           | col4        |
        +----+-------+----------------+-------------+
        | 2  | kyle2 | this is desc2  | this is two  
        +----+-------+----------------+-------------+

Buat tabel eksternal OSS menggunakan parser open source bawaan

Contoh ini menunjukkan cara membuat tabel eksternal OSS menggunakan parser open source bawaan untuk membaca file yang dipisahkan koma dan mengabaikan baris pertama dan terakhir data.

1. Buat folder demo-test di bucket oss-mc-test dari data sampel dan unggah file uji test.csv.

   File uji berisi data berikut.

   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. Buat tabel eksternal, tentukan pemisah sebagai koma, dan atur parameter untuk mengabaikan baris pertama dan terakhir.

   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/***/'
   -- Atur parameter untuk mengabaikan baris pertama dan terakhir.
   TBLPROPERTIES (
   "skip.header.line.COUNT"="1",
   "skip.footer.line.COUNT"="1"
   )
   ;

3. Baca tabel eksternal.

   SELECT * FROM ext_csv_test08;
   
   -- Hasil menunjukkan bahwa hanya 8 baris yang dibaca, mengabaikan baris pertama dan terakhir.
   +------------+------------+------------+------------+------------------+-------------------+------------+------------+
   | 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          | 
   +------------+------------+------------+------------+------------------+-------------------+------------+------------+

Buat tabel eksternal OSS berformat CSV dengan tipe data tanggal dan waktu kustom

Untuk informasi lebih lanjut tentang format penguraian dan keluaran kustom untuk tipe waktu CSV, lihat Penguraian tipe yang lebih fleksibel dengan Smart Parse.

1. Buat tabel eksternal CSV untuk berbagai tipe data waktu (datetime, timestamp, dan timestamp_ntz).

   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. Setelah Anda memasukkan data, konten file CSV adalah:

   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. Baca data lagi untuk melihat hasilnya.

   SELECT * FROM test_csv;

   Hasil berikut dikembalikan:

   +------------+---------------------+---------------------+------------------------+
   | 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 |
   +------------+---------------------+---------------------+------------------------+