MaxCompute:Tabel eksternal CSV dan TSV

Tipe data yang didukung

Untuk informasi selengkapnya mengenai tipe data MaxCompute, lihat Data Type Version 1.0 dan Data Type Version 2.0.

Untuk informasi selengkapnya mengenai SmartParse, lihat Kompatibilitas tipe fleksibel Smart Parse.

Format kompresi yang didukung

Saat membaca atau menulis ke file OSS terkompresi, Anda harus menyertakan atribut with serdeproperties dalam pernyataan CREATE TABLE. Untuk informasi selengkapnya, 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 selengkapnya mengenai parameter umum, lihat Parameter sintaksis dasar.

Parameter khusus format

Parameter

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

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

  • flexible: Mengaktifkan pelewatkan. Pengaturan ini fleksibel, sehingga dapat diganti oleh konfigurasi tingkat sesi atau proyek.

• Parameter tingkat sesi/proyek

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

    • on: Mengaktifkan fitur. Jika fitur tidak dikonfigurasi untuk tabel, fitur ini 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 antara 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 diganti 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 diganti 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, menyebabkan kueri 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 diganti oleh perintah berikutnya dalam contoh ini.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Perintah ini menonaktifkan pelewatkan, mengganti pengaturan 'flexible' tabel. Ini merupakan pengaturan aktif untuk kueri SELECT di bawah, menyebabkan kueri 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 berlebihan 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 diganti.
   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         | 
   +------------+------------+

Prasyarat

• Proyek MaxCompute. Lihat Buat proyek MaxCompute.

• Bucket OSS di wilayah yang sama dengan proyek MaxCompute Anda. Lihat Buat bucket dan Kelola folder. MaxCompute dapat membuat folder OSS secara otomatis — Anda tidak perlu membuatnya sebelum menjalankan SQL.

• Izin akses OSS melalui akun Alibaba Cloud, pengguna Resource Access Management (RAM), atau peran RAM. Lihat Otorisasi akses dalam mode STS untuk OSS.

• Izin CreateTable di proyek MaxCompute Anda. 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'
)
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 membaca header.
--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')
      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 di 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 guna 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" = ","
   )
   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 mengenai 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/'
   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 ketika 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 ketika 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.