MaxCompute:Tabel eksternal CSV dan TSV

Tipe data yang didukung

Untuk informasi lebih lanjut tentang tipe data MaxCompute, lihat Versi Tipe Data 1.0 dan Versi Tipe Data 2.0.

Untuk informasi lebih lanjut tentang SmartParse, lihat Kompatibilitas Tipe Fleksibel Smart Parse.

Format kompresi yang didukung

Saat membaca atau menulis file OSS terkompresi, Anda harus menyertakan atribut with serdeproperties dalam pernyataan CREATE TABLE. Untuk informasi lebih lanjut, lihat Parameter Atribut with serdeproperties.

Evolusi skema yang didukung

Sintaksis

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

Parameter umum

Untuk informasi lebih lanjut tentang parameter umum, lihat Parameter Sintaksis Dasar.

Parameter khusus format

Daftar putih dan daftar hitam

Tabel eksternal OSS MaxCompute mendukung penyaringan daftar putih dan daftar hitam. Dengan mengatur parameter daftar putih dan daftar hitam dalam tblproperties, Anda dapat menyaring file mana yang akan dibaca dari direktori. Untuk detailnya, lihat Daftar Putih dan Daftar Hitam.

Parameter

• Parameter tingkat tabel: odps.text.option.bad.row.skipping

  • rigid: Memaksa pelewatkan. Pengaturan ini tidak dapat di-override oleh konfigurasi tingkat sesi atau proyek.

  • flexible: Mengaktifkan pelewatkan. Pengaturan ini fleksibel, memungkinkannya di-override oleh konfigurasi tingkat sesi atau proyek.

• Parameter tingkat sesi/proyek

  • Parameter odps.sql.unstructured.text.bad.row.skipping dapat meng-override parameter tingkat tabel flexible, tetapi tidak dapat meng-override yang rigid.

    • on: Mengaktifkan fitur. Jika fitur tidak dikonfigurasi untuk tabel, fitur diaktifkan secara default.

    • off: Menonaktifkan fitur. Jika tabel dikonfigurasi sebagai flexible, fitur dinonaktifkan. Jika tidak, pengaturan parameter tabel yang digunakan.

    • <null> atau input tidak valid: Konfigurasi tingkat tabel yang digunakan.

  • odps.sql.unstructured.text.bad.row.skipping.debug.num: Menentukan jumlah hasil error yang dicetak ke stdout di Logview.

    • Nilai maksimum adalah 1000.

    • Jika nilainya <=0, fitur ini dinonaktifkan.

    • Jika nilainya tidak valid, fitur ini dinonaktifkan.

• Interaksi antara parameter tingkat sesi dan properti tabel

  properti tbl	flag sesi	hasil
  rigid	on	On, Dipaksa aktif
  	off
  	<null>, nilai tidak valid, atau parameter tidak dikonfigurasi
  flexible	on	On
  	off	Off, Dinonaktifkan oleh sesi
  	<null>, nilai tidak valid, atau parameter tidak dikonfigurasi	On
  Not configured	on	On, Diaktifkan oleh sesi
  	off	Off
  	<null>, nilai tidak valid, atau parameter tidak dikonfigurasi

Contoh

1. Siapkan data

   Unggah file data uji csv_bad_row_skipping.csv, yang berisi baris buruk, ke direktori di OSS, seperti oss-mc-test/badrow/.

2. Buat tabel eksternal CSV

   Contoh berikut menunjukkan tiga skenario berdasarkan kombinasi berbeda parameter tingkat tabel dan tingkat sesi.

   • Parameter tabel: odps.text.option.bad.row.skipping = flexible | rigid | <not set>

   • Flag sesi: odps.sql.unstructured.text.bad.row.skipping = on | off | <not set>

   Parameter tidak diatur

   -- Tidak ada parameter tingkat tabel yang diatur. Kueri akan gagal pada baris buruk kecuali di-override oleh flag tingkat sesi.
   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>/>';

   Pelewatkan fleksibel

   -- Tabel dikonfigurasi untuk melewati baris buruk, tetapi ini dapat dinonaktifkan oleh flag tingkat sesi.
   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'   -- Mengaktifkan pelewatkan fleksibel, yang dapat dinonaktifkan pada tingkat sesi.
   );

   Pelewatkan kaku

   -- Tabel dikonfigurasi untuk memaksa melewati baris buruk. Ini tidak dapat dinonaktifkan pada tingkat sesi.
   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'  -- Memaksa pelewatkan aktif.
   );

3. Verifikasi hasil kueri

   Parameter tidak diatur

   -- Perintah berikut mengaktifkan pelewatkan, tetapi segera di-override oleh perintah berikutnya dalam contoh ini.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Perintah ini menonaktifkan pelewatkan dan merupakan pengaturan aktif untuk kueri SELECT di bawah, menyebabkannya gagal.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Anda dapat menggunakan perintah ini untuk mencetak detail baris yang dilewati saat pelewatkan diaktifkan. Tidak berpengaruh di sini karena kueri gagal.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flag;

   Kueri gagal dengan error berikut: FAILED: ODPS-0123131:User defined function exception

   Pelewatkan fleksibel

   -- Perintah berikut mengaktifkan pelewatkan, tetapi segera di-override oleh perintah berikutnya dalam contoh ini.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Perintah ini menonaktifkan pelewatkan, meng-override pengaturan 'flexible' tabel. Ini adalah pengaturan aktif untuk kueri SELECT di bawah, menyebabkannya gagal.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Cetak detail hingga 10 baris buruk pada tingkat sesi. Maksimum adalah 1.000. Nilai 0 atau kurang menonaktifkan pencetakan.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flexible;

   Kueri gagal dengan error berikut: FAILED: ODPS-0123131:User defined function exception

   Pelewatkan kaku

   -- Perintah ini redundan karena pengaturan 'rigid' sudah memberlakukan pelewatkan.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Perintah ini mencoba menonaktifkan pelewatkan, tetapi diabaikan karena pengaturan tabel 'rigid' tidak dapat di-override.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Perintah ini mencetak detail hingga 10 baris buruk yang dilewati oleh pengaturan 'rigid'.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_rigid;

   Hasil berikut dikembalikan:

   +------------+------------+
   | a          | b          | 
   +------------+------------+
   | 1          | 26         | 
   | 5          | 37         | 
   +------------+------------+

Parser CSV/TSV bawaan (OpenCSVSerde)

Parser CSV/TSV bawaan (OpenCSVSerde) mengharuskan setiap baris data dalam file CSV dipisahkan oleh \r\n atau karakter serupa, dan kolom tidak boleh berisi \r\n. Logika pemisahan paralel dan integritas data adalah sebagai berikut:

• Pertama, file dibagi berdasarkan ukuran split, dan beberapa baris mungkin terpotong di tengah.

• Saat worker berikutnya mengonsumsi split, semua split kecuali yang pertama secara aktif melewati baris parsial atau penuh di awal.

• Setiap worker juga harus secara aktif mengonsumsi baris parsial atau penuh di akhir, bahkan jika berada dalam rentang split berikutnya.

Parser ini mendukung pemisahan paralel tetapi tidak mendukung escape quote.

Parser CSV open-source (CsvStorageHandler / TsvStorageHandler)

Parser CSV open-source (CsvStorageHandler / TsvStorageHandler) mempertimbangkan escape quote saat membaca file CSV/TSV dan mendukung \r\n. Parser ini dapat menangani skenario di mana karakter khusus seperti quote bersarang atau \r\n muncul di dalam quote.

Misalnya, dalam data "a\ra","b\nb","cc""cc", \r\n dan tanda kutip ganda dapat diurai dan di-output dengan benar, mendukung nilai lintas baris. Namun, karena posisi line break sebenarnya hanya dapat ditentukan melalui penguraian data, file tidak dapat dibagi secara sederhana berdasarkan \r\n. Akibatnya, konsumsi paralel satu file tidak didukung.

Jika Anda memastikan bahwa kolom data tidak berisi \r\n dan \r\n hanya digunakan sebagai pemisah baris, Anda dapat mengaktifkan pemisahan paralel dengan mengatur odps.sql.unstructured.data.single.file.split.enabled. Dalam kasus ini, file besar dapat dibagi menjadi beberapa split berdasarkan ukuran split, dan Text Extractor bawaan secara otomatis menyelaraskan ke batas line break untuk memastikan integritas data.

Prasyarat

• Proyek MaxCompute telah dibuat.

• Bucket dan folder OSS tersedia. Untuk informasi lebih lanjut, lihat Buat bucket dan Kelola folder.

  MaxCompute mendukung pembuatan folder otomatis di OSS. Jika pernyataan SQL melibatkan tabel eksternal dan fungsi yang ditentukan pengguna (UDF), Anda dapat menggunakan satu pernyataan untuk membaca dan menulis ke tabel serta menggunakan UDF. Anda juga dapat membuat folder secara manual.

  MaxCompute hanya diterapkan di wilayah tertentu. Untuk menghindari potensi masalah koneksi data lintas wilayah, pastikan bucket OSS Anda berada di wilayah yang sama dengan proyek MaxCompute Anda.

• Otorisasi

  • Anda harus memiliki izin untuk mengakses OSS. Anda dapat menggunakan Akun Alibaba Cloud, pengguna Resource Access Management (RAM), atau peran RAM untuk mengakses tabel eksternal OSS. Untuk informasi lebih lanjut tentang otorisasi, lihat Otorisasi Akses dalam Mode STS untuk OSS.

  • Anda harus memiliki izin CreateTable di proyek MaxCompute. Untuk informasi lebih lanjut tentang izin tabel, lihat Izin MaxCompute.

Buat tabel eksternal OSS dengan parser teks bawaan

Buat tabel eksternal dengan baris header

Buat direktori 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',
  'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
)
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 kolom sebagai STRING. Jika tidak, terjadi error saat header dibaca.
--Atau, tambahkan parameter 'odps.text.option.header.lines.count' = '1' ke definisi tabel untuk melewati header.
SELECT * FROM mf_oss_wtt;

Contoh ini menggunakan peran RAM aliyunodpsdefaultrole. Jika Anda menggunakan peran RAM berbeda, ganti aliyunodpsdefaultrole dengan nama peran RAM target Anda dan berikan izin yang diperlukan untuk mengakses OSS.

Perintah mengembalikan hasil berikut:

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

Buat tabel eksternal dengan kolom tidak sesuai

1. Buat direktori 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 metode penanganan untuk baris dengan jumlah kolom tidak konsisten ke TRUNCATE.

      -- Hapus tabel.
      DROP TABLE test_mismatch;
      -- Buat tabel eksternal.
      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',
        'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole')
      LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/';

   2. Tentukan metode penanganan untuk baris dengan jumlah kolom tidak konsisten sebagai IGNORE.

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

      • Kueri tabel test_mismatch.

        SELECT * FROM test_mismatch;
        
        --Hasil yang dikembalikan
        +----+-------+---------------+---------------+
        | 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 yang dikembalikan
        +----+-------+----------------+-------------+
        | id | name  | dect           | col4        |
        +----+-------+----------------+-------------+
        | 2  | kyle2 | this is desc2  | this is two  
        +----+-------+----------------+-------------+

Buat tabel eksternal dengan parser open-source

Contoh ini menunjukkan cara menggunakan parser open source bawaan untuk membuat tabel eksternal OSS untuk membaca file yang dipisahkan koma sambil mengabaikan baris header dan footer.

1. Buat direktori demo-test di bucket oss-mc-test dari data sampel dan unggah file 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 koma sebagai pemisah, dan atur parameter untuk mengabaikan baris header dan footer.

   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" = ",",
   'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   stored AS textfile
   location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/***/'
   -- Atur parameter untuk mengabaikan baris header dan footer.
   TBLPROPERTIES (
   "skip.header.line.COUNT"="1",
   "skip.footer.line.COUNT"="1"
   )
   ;

3. Baca data dari tabel eksternal.

   SELECT * FROM ext_csv_test08;
   
   -- Hasil mencakup 8 baris data karena baris header dan footer diabaikan.
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 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         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

Buat tabel eksternal CSV dengan tipe waktu kustom

Untuk detail tentang format penguraian dan output untuk tipe waktu kustom dalam CSV, lihat Kompatibilitas Tipe Fleksibel dengan Smart Parse.

1. Buat tabel eksternal CSV yang menggunakan berbagai tipe data waktu, seperti DATE, 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/'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   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 data dimasukkan, konten file CSV adalah sebagai berikut:

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

   SELECT * FROM test_csv;

   Perintah mengembalikan hasil berikut:

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

Error ketidaksesuaian jumlah kolom

• Gejala

  Error ini terjadi saat jumlah kolom dalam baris file CSV atau TSV tidak sesuai dengan yang didefinisikan dalam DDL tabel eksternal. MaxCompute melaporkan error serupa dengan FAILED: ODPS-0123131:User defined function exception - Traceback:java.lang.RuntimeException: SCHEMA MISMATCH:xxx.

• Resolusi

  Anda dapat mengontrol cara MaxCompute menangani ketidaksesuaian dengan mengatur parameter odps.sql.text.schema.mismatch.mode pada tingkat sesi:

  • SET odps.sql.text.schema.mismatch.mode=error: Menggagalkan kueri saat terjadi ketidaksesuaian jumlah kolom. Ini adalah perilaku default.

  • SET odps.sql.text.schema.mismatch.mode=truncate: Jika baris memiliki lebih banyak kolom daripada yang didefinisikan dalam DDL tabel eksternal, kolom tambahan dibuang. Jika baris memiliki lebih sedikit kolom, kolom yang hilang diisi dengan NULL.