AnalyticDB：Gunakan tabel eksternal untuk mengimpor data dari OSS

Ikhtisar gpossext

gpossext dapat membaca dan menulis data ke file TEXT dan CSV, baik yang dikompresi dalam format GZIP maupun tidak.

Gambar berikut menunjukkan arsitektur gpossext.

Format TEXT dan CSV

Parameter berikut menentukan format file yang dibaca dan ditulis ke OSS. Parameter ini dapat ditentukan di parameter DDL tabel eksternal.

• \n: karakter yang digunakan sebagai pembatas baris atau jeda baris untuk file TEXT atau CSV.

• DELIMITER: pembatas kolom.

  • Jika Anda menentukan parameter DELIMITER, Anda juga harus menentukan parameter QUOTE.

  • Pembatas kolom yang direkomendasikan mencakup koma (,), tanda batang vertikal (|), serta karakter khusus seperti \t.

• QUOTE: Melingkupi setiap kolom data pengguna yang berisi karakter khusus.

  • String yang mengandung karakter khusus harus dibungkus dengan karakter QUOTE guna membedakan data pengguna dari karakter kontrol.

  • Untuk meningkatkan efisiensi, disarankan agar Anda tidak menyertakan data seperti bilangan bulat dalam karakter QUOTE.

  • Karakter QUOTE tidak boleh sama dengan yang ditentukan oleh DELIMITER. Nilai default QUOTE adalah sepasang tanda kutip dua ("").

  • Data pengguna yang mengandung karakter QUOTE juga harus menyertakan karakter ESCAPE guna membedakan data pengguna dari kode mesin.

• ESCAPE: karakter escape.

  • Tempatkan karakter escape sebelum karakter khusus yang perlu dihindari untuk menunjukkan bahwa itu bukan karakter khusus.

  • Nilai default ESCAPE sama dengan nilai QUOTE.

  • Anda juga dapat menggunakan karakter lain, seperti backslash (\), sebagai karakter ESCAPE yang digunakan oleh MySQL.

Versi yang didukung

Instansi AnalyticDB for PostgreSQL V6.0.

Catatan penggunaan

• Sintaks yang digunakan untuk membuat dan menggunakan tabel eksternal sama dengan sintaks Greenplum Database, kecuali untuk sintaks parameter terkait lokasi.

• Kinerja impor dan ekspor data bervariasi berdasarkan performa OSS dan sumber daya instansi AnalyticDB for PostgreSQL, seperti CPU, I/O, memori, dan sumber daya jaringan. Untuk memaksimalkan kinerja impor dan ekspor, kami sarankan Anda menggunakan penyimpanan berorientasi kolom dan kompresi saat membuat tabel. Misalnya, Anda dapat menentukan klausa berikut: "WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576)". Untuk informasi lebih lanjut, lihat CREATE TABLE.

• Untuk memastikan kinerja impor dan ekspor, bucket OSS dan instansi AnalyticDB for PostgreSQL harus berada di wilayah yang sama.

Prosedur

1. Buat ekstensi tabel eksternal OSS.

   Sebelum menggunakan tabel eksternal OSS, Anda harus terlebih dahulu membuat ekstensi tabel eksternal OSS di AnalyticDB for PostgreSQL. Buat ekstensi untuk setiap database yang ingin Anda akses. Jalankan pernyataan berikut untuk membuat ekstensi:

   CREATE EXTENSION IF NOT EXISTS oss_ext;

2. Distribusikan data yang ingin Anda impor ke AnalyticDB for PostgreSQL secara merata ke beberapa objek di OSS. Untuk informasi lebih lanjut, lihat Ikhtisar Tabel Asing OSS.

   Catatan

   Setiap partisi data (node komputasi) dari AnalyticDB for PostgreSQL menggunakan mekanisme polling untuk membaca objek OSS secara paralel. Untuk meningkatkan efisiensi pembacaan, kami sarankan Anda menyetel jumlah objek yang dapat dibaca secara paralel menjadi kelipatan integer dari jumlah node komputasi.

3. Buat tabel eksternal yang dapat dibaca di AnalyticDB for PostgreSQL.

   Jalankan pernyataan berikut untuk membuat tabel eksternal OSS:

   CREATE [READABLE] EXTERNAL TABLE tablename
   ( columnname datatype [, ...] | LIKE othertable )
   LOCATION ('ossprotocol')
   FORMAT 'TEXT'
               [( [HEADER]
                  [DELIMITER [AS] 'delimiter' | 'OFF']
                  [NULL [AS] 'null string']
                  [ESCAPE [AS] 'escape' | 'OFF']
                  [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
                  [FILL MISSING FIELDS] )]
              | 'CSV'
               [( [HEADER]
                  [QUOTE [AS] 'quote']
                  [DELIMITER [AS] 'delimiter']
                  [NULL [AS] 'null string']
                  [FORCE NOT NULL column [, ...]]
                  [ESCAPE [AS] 'escape']
                  [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
                  [FILL MISSING FIELDS] )]
   [ ENCODING 'encoding' ]
   [ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count
          [ROWS | PERCENT] ]
   ossprotocol:
      oss://oss_endpoint [prefix=prefix_name|dir=[folder/[folder/]...]/file_name|filepath=[folder/[folder/]...]/file_name]
       id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

   Tabel berikut menjelaskan parameter.

   Parameter	Deskripsi
   FORMAT	Format file yang didukung, seperti TEXT dan CSV.
   ENCODING	Format yang digunakan untuk mengkodekan data dalam objek, seperti UTF-8.
   LOG ERRORS	Mengabaikan data yang gagal diimpor dan menulis data tersebut ke error_table. Anda dapat menggunakan parameter count untuk menentukan ambang toleransi kesalahan. Catatan Anda dapat menggunakan pernyataan LOG ERRORS untuk mencatat informasi tentang baris yang gagal diimpor dalam file internal. LOG ERRORS SEGMENT REJECT LIMIT 5; Anda dapat menggunakan fungsi gp_read_error_log('external_table_name') untuk mendapatkan informasi tentang baris yang gagal diimpor. SELECT * FROM gp_read_error_log('external_table_name'); Setelah Anda menghapus tabel eksternal, file internal juga akan dihapus. Anda juga dapat menggunakan fungsi gp_truncate_error_log('external_table_name') untuk menghapus file internal. SELECT gp_truncate_error_log('external_table_name');
   oss://oss_endpoint	URL endpoint OSS dalam format protocol name://oss_endpoint. oss adalah nama protokol dan oss_endpoint adalah nama domain yang digunakan untuk mengakses OSS di suatu wilayah. Contoh: oss://oss-cn-hangzhou.aliyuncs.com Penting Jika Anda mengakses instansi AnalyticDB for PostgreSQL Anda dari server Alibaba Cloud, gunakan endpoint internal untuk menghindari menghasilkan lalu lintas Internet. Endpoint internal berisi kata kunci internal.
   id	ID AccessKey akun Alibaba Cloud Anda. Untuk informasi tentang cara mendapatkan pasangan AccessKey, lihat Buat pasangan AccessKey.
   key	Rahasia AccessKey akun Alibaba Cloud Anda. Untuk informasi tentang cara mendapatkan pasangan AccessKey, lihat Buat pasangan AccessKey.
   bucket	Bucket tempat objek disimpan. Sebelum mengimpor data, Anda harus membuat bucket OSS.
   prefix	Awalan jalur objek OSS. Ekspresi reguler tidak didukung. Catatan Anda hanya dapat mengonfigurasi salah satu parameter berikut: dir, filepath, dan prefix. Saat tabel eksternal yang dapat dibaca digunakan untuk impor data, data dari semua objek OSS yang jalurnya berisi awalan diimpor. Jika Anda menetapkan parameter prefix ke test/filename, objek berikut diimpor: test/filename test/filenamexxx test/filename/aa test/filenameyyy/aa test/filenameyyy/bb/aa Jika Anda menetapkan parameter prefix ke test/filename/, hanya objek berikut dari objek sebelumnya yang diimpor: test/filename/aa
   dir	Direktori OSS yang menyimpan objek data. Catatan Anda hanya dapat mengonfigurasi salah satu parameter berikut: prefix, filepath, dan dir. Jalur direktori harus diakhiri dengan garis miring maju (/). Contoh: test/mydir/. Jika Anda mengonfigurasi parameter ini saat membuat tabel eksternal untuk mengimpor data, semua objek di direktori diimpor, tidak termasuk subdirektorinya dan objek di subdirektori. Parameter dir berbeda dari parameter filepath dan tidak memerlukan Anda untuk menentukan nama objek di direktori.
   filepath	Nama objek yang berisi jalur objek OSS. Catatan Anda hanya dapat mengonfigurasi salah satu parameter berikut: prefix, dir, dan filepath. Anda hanya dapat mengonfigurasi parameter ini saat membuat tabel eksternal yang dapat dibaca. Parameter ini tersedia hanya saat Anda mengimpor data.
   compressiontype	Format kompresi file yang diimpor. Nilai valid: none (default): File tidak dikompresi. gzip: File dikompresi dalam format GZIP. Catatan Hanya format GZIP yang didukung.
   compressionlevel	Tingkat kompresi file yang ditulis ke OSS. Nilai valid: 1 hingga 9. Nilai default: 6. compressionlevel=6
   oss_connect_timeout	Periode waktu habis koneksi. Nilai default: 10. Unit: detik.
   oss_dns_cache_timeout	Periode waktu habis untuk resolusi DNS. Nilai default: 60. Unit: detik.
   oss_speed_limit	Jumlah minimum data yang ditransmisikan per detik. Jika jumlah data yang ditransmisikan per detik kurang dari nilai yang ditentukan selama periode waktu tertentu, timeout dipicu. Unit: byte. Nilai default: 1024. 1024 byte sama dengan 1 KB. Jika Anda mengonfigurasi parameter ini, Anda juga harus mengonfigurasi parameter oss_speed_time. Catatan Jika laju transmisi lebih rendah dari 1 KB/s selama 15 detik berturut-turut ketika nilai default digunakan untuk parameter oss_speed_limit dan oss_speed_time, timeout terjadi. Untuk informasi lebih lanjut, lihat Penanganan kesalahan.
   oss_speed_time	Periode waktu maksimum selama laju transmisi minimum dapat ditoleransi. Jika laju transmisi lebih rendah dari nilai yang ditentukan selama periode waktu yang ditentukan, timeout dipicu. Nilai default: 15. Unit: detik. Jika Anda mengonfigurasi parameter ini, Anda juga harus mengonfigurasi parameter oss_speed_limit. Catatan Jika laju transmisi lebih rendah dari 1 KB/s selama 15 detik berturut-turut ketika nilai default digunakan untuk parameter oss_speed_limit dan oss_speed_time, timeout terjadi. Untuk informasi lebih lanjut, lihat Penanganan kesalahan.
   async	Menentukan apakah akan mengaktifkan impor data asinkron. Anda dapat mengaktifkan thread tambahan untuk mempercepat impor data dari OSS. Secara default, impor data asinkron diaktifkan. Anda dapat menetapkan parameter async ke false atau f untuk menonaktifkan impor data asinkron. Impor data asinkron mengonsumsi lebih banyak sumber daya perangkat keras dibandingkan dengan impor data biasa.

4. Impor data secara paralel.

   Jalankan pernyataan berikut di AnalyticDB for PostgreSQL untuk mengimpor data dari OSS ke AnalyticDB for PostgreSQL secara paralel:

   INSERT INTO <Tabel tujuan> SELECT * FROM <Tabel eksternal>

Contoh

Dalam contoh ini, data diimpor dari OSS ke tabel tujuan bernama example.

1. Jalankan pernyataan berikut untuk membuat ekstensi tabel eksternal OSS:

   CREATE EXTENSION IF NOT EXISTS oss_ext;

2. Jalankan pernyataan berikut untuk membuat tabel bernama example yang digunakan untuk menyimpan data yang akan diimpor:

   CREATE TABLE example
           (date text, time text, open float,
            high float, low float, volume int)
            DISTRIBUTED BY (date);

3. Buat tabel eksternal OSS bernama ossexample untuk mengimpor data.

   • Jalankan pernyataan berikut jika parameter prefix digunakan untuk menentukan jalur objek yang ingin Anda impor:

     CREATE READABLE EXTERNAL TABLE ossexample
             (date text, time text, open float, high float,
             low float, volume int)
             location('oss://oss-cn-hangzhou.aliyuncs.com
             prefix=osstest/example id=XXX
             key=XXX bucket=testbucket compressiontype=gzip')
             FORMAT 'csv' (QUOTE '''' DELIMITER E'\t')
             ENCODING 'utf8'
             LOG ERRORS SEGMENT REJECT LIMIT 5;

   • Jalankan pernyataan berikut jika parameter dir digunakan untuk menentukan jalur objek yang ingin Anda impor:

     CREATE READABLE EXTERNAL TABLE ossexample
             (date text, time text, open float, high float,
             low float, volume int)
             location('oss://oss-cn-hangzhou.aliyuncs.com
             dir=osstest/ id=XXX
             key=XXX bucket=testbucket')
             FORMAT 'csv'
             LOG ERRORS SEGMENT REJECT LIMIT 5;

   • Jalankan pernyataan berikut jika parameter filepath digunakan untuk menentukan jalur objek yang ingin Anda impor:

     CREATE READABLE EXTERNAL TABLE ossexample
             (date text, time text, open float, high float,
             low float, volume int)
             location('oss://oss-cn-hangzhou.aliyuncs.com
             filepath=osstest/example.csv id=XXX
             key=XXX bucket=testbucket')
             FORMAT 'csv'
             LOG ERRORS SEGMENT REJECT LIMIT 5;

4. Impor data secara paralel dari tabel eksternal ossexample ke tabel example.

   INSERT INTO example SELECT * FROM ossexample;

Jalankan rencana kueri berikut. Hasilnya menunjukkan bahwa node komputasi mengimpor data dari OSS secara paralel. Node redistribusi motion meng-hash data dan mendistribusikan data ke node komputasi yang sesuai. Node komputasi yang menerima data memasukkan data ke dalam database.

EXPLAIN INSERT INTO example SELECT * FROM ossexample;
                                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Insert (slice0; segments: 4)  (rows=250000 width=92)
   ->  Redistribute Motion 4:4  (slice1; segments: 4)  (cost=0.00..11000.00 rows=250000 width=92)
         Hash Key: ossexample.date
         ->  External Scan on ossexample  (cost=0.00..11000.00 rows=250000 width=92)
(4 rows)

Pemecahan masalah SDK

Saat terjadi kesalahan selama proses impor atau ekspor, log kesalahan berisi informasi berikut:

• code: kode status HTTP dari permintaan yang gagal.

• error_code: kode kesalahan yang dikembalikan oleh OSS.

• error_msg: pesan kesalahan yang dikembalikan oleh OSS.

• req_id: UUID dari permintaan yang gagal. Jika masalah berlanjut, Anda dapat memberikan UUID permintaan kepada OSS untuk dukungan teknis.

Untuk informasi lebih lanjut, lihat Respon kesalahan. Anda dapat memperbaiki kesalahan terkait timeout menggunakan parameter terkait dengan oss_ext.

Referensi

• Nama domain OSS

• Dokumentasi Object Storage Service (OSS)

• Penanganan kesalahan

• Respon kesalahan