AnalyticDB:Analisis data lake dengan tabel eksternal OSS

Batasan

Instans AnalyticDB for PostgreSQL dan bucket OSS harus berada di wilayah yang sama.

Fitur

OSS FDW didasarkan pada kerangka kerja PostgreSQL Foreign Data Wrapper (FDW) dan memungkinkan Anda:

• Mengimpor data dari OSS ke dalam tabel lokal berorientasi baris atau berorientasi kolom untuk mempercepat analisis.

• Langsung melakukan kueri dan menganalisis volume data besar di OSS.

• Menggabungkan (join) tabel eksternal OSS dengan tabel lokal untuk analisis.

OSS FDW mendukung berbagai format file data, termasuk:

• File teks tidak terkompresi dalam format CSV, TEXT, JSON, dan JSON Lines.

• File teks terkompresi GZIP dan Snappy dalam format CSV dan TEXT.

• File teks terkompresi GZIP dalam format JSON dan JSON Lines.

• File biner ORC. Untuk pemetaan tipe data antara ORC dan AnalyticDB for PostgreSQL, lihat Pemetaan tipe data untuk file ORC.

• File biner Parquet. Untuk pemetaan tipe data antara Parquet dan AnalyticDB for PostgreSQL, lihat Pemetaan tipe data untuk file Parquet.

• File biner Avro. Untuk pemetaan tipe data antara Avro dan AnalyticDB for PostgreSQL, lihat Pemetaan tipe data untuk file Avro.

Persiapan awal

Siapkan data OSS

Siapkan file contoh example.csv.

Cari informasi bucket OSS

Bagian ini menjelaskan cara menemukan Buckets, Bucket Name, Port, dan Bucket Domain Name.

1. Masuk ke Konsol OSS.

2. Di panel navigasi kiri, klik Access from ECS over the VPC (internal network).

3. Pada halaman Endpoint, klik bucket yang dituju.

   Di halaman Object Management, Anda dapat menemukan bucket name.

4. Di halaman Object Management, temukan path objek.

5. Di panel navigasi kiri, klik Overview.

6. Di bagian Port pada halaman Overview, temukan endpoint dan bucket domain name.

   Kami merekomendasikan penggunaan endpoint untuk Access from ECS over the VPC (internal network).

Dapatkan ID AccessKey dan Rahasia AccessKey

Untuk mendapatkan ID AccessKey dan Rahasia AccessKey, lihat Create an AccessKey.

Buat server OSS

Gunakan pernyataan CREATE SERVER untuk membuat server OSS. Server ini mendefinisikan koneksi ke layanan OSS yang ingin Anda akses. Untuk informasi lebih lanjut tentang CREATE SERVER, lihat CREATE SERVER.

Sintaks

CREATE SERVER server_name
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

Parameter

Tabel berikut menjelaskan parameter untuk klausa OPTIONS.

Contoh

CREATE SERVER oss_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-********.aliyuncs.com',
        bucket 'adb-pg'
  );

Anda juga dapat menggunakan pernyataan ALTER SERVER untuk mengubah konfigurasi server OSS. Untuk informasi lebih lanjut, lihat ALTER SERVER.

Contoh:

• Ubah opsi untuk server OSS.

  ALTER SERVER oss_serv OPTIONS(SET endpoint 'oss-cn-********.aliyuncs.com');

• Tambahkan opsi ke server OSS.

  ALTER SERVER oss_serv OPTIONS(ADD connect_timeout '20');

• Hapus opsi dari server OSS.

  ALTER SERVER oss_serv OPTIONS(DROP connect_timeout);

Untuk menghapus server OSS, gunakan pernyataan DROP SERVER. Untuk informasi lebih lanjut, lihat DROP SERVER.

Buat pemetaan pengguna OSS

Setelah membuat server OSS, Anda juga harus membuat pemetaan pengguna. Pernyataan CREATE USER MAPPING mendefinisikan pemetaan antara pengguna di database AnalyticDB for PostgreSQL Anda dan kredensial untuk mengakses server OSS. Untuk informasi lebih lanjut, lihat CREATE USER MAPPING.

Sintaks

CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
    SERVER <server_name>
    [ OPTIONS ( option 'value' [, ... ] ) ]

Parameter

Tabel berikut menjelaskan parameter untuk klausa OPTIONS.

Contoh

CREATE USER MAPPING FOR PUBLIC
    SERVER oss_serv
    OPTIONS (
        id 'LTAI****************',
        key 'yourAccessKeySecret'
    );

Anda juga dapat menggunakan pernyataan DROP USER MAPPING untuk menghapus pemetaan pengguna. Untuk informasi lebih lanjut, lihat DROP USER MAPPING.

Buat tabel eksternal OSS

Setelah Anda memiliki server OSS dan pengguna yang dapat mengaksesnya, gunakan pernyataan CREATE FOREIGN TABLE untuk membuat tabel eksternal OSS. Untuk informasi lebih lanjut, lihat CREATE FOREIGN TABLE.

Sintaks

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
      [, ... ]
] )
    SERVER server_name
  [ OPTIONS ( option 'value' [, ... ] ) ]

Parameter

Tabel berikut mencantumkan parameter untuk klausa OPTIONS.

Contoh

CREATE FOREIGN TABLE ossexample (
    date text,
    time text,
    open float,
    high float,
    low float,
    volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');

Anda juga dapat menggunakan pernyataan DROP FOREIGN TABLE untuk menghapus tabel eksternal OSS. Untuk informasi lebih lanjut, lihat DROP FOREIGN TABLE.

Lakukan kueri dan analisis data OSS

Anda dapat melakukan kueri pada tabel eksternal OSS seperti halnya melakukan kueri pada tabel lokal. Contoh berikut menunjukkan kueri umum.

• Lakukan kueri data menggunakan filter pasangan kunci-nilai.

  SELECT * FROM ossexample WHERE volume = 5;

• Lakukan kueri data menggunakan fungsi agregat.

  SELECT count(*) FROM ossexample WHERE volume = 5;

• Lakukan kueri data menggunakan klausa GROUP BY dan LIMIT.

  SELECT low, sum(volume)
    FROM ossexample
   GROUP BY low
   ORDER BY low
   limit 5;

Analisis gabungan dengan tabel eksternal OSS

1. Buat tabel lokal bernama example dan masukkan data uji.

   CREATE TABLE example (id int, volume int);
   INSERT INTO example VALUES(1,1), (2,3), (4,5);

2. Jalankan kueri gabungan pada tabel example dan ossexample.

   SELECT example.volume, min(high), max(low)
   FROM
   ossexample,
   example
   WHERE ossexample.volume = example.volume
   GROUP BY(example.volume)
   ORDER BY example.volume;

Toleransi kesalahan

OSS FDW menyediakan toleransi kesalahan menggunakan parameter log_errors dan segment_reject_limit untuk mencegah kesalahan pada data mentah mengganggu proses pemindaian tabel eksternal OSS.

Untuk informasi lebih lanjut tentang parameter log_errors dan segment_reject_limit, lihat Buat tabel eksternal OSS.

• Buat tabel eksternal OSS yang toleran terhadap kesalahan.

  CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text)
      SERVER oss_serv
      OPTIONS (log_errors 'true', -- Aktifkan pencatatan kesalahan.
               segment_reject_limit '10', -- Tetapkan batas penolakan menjadi 10 baris. Pemindaian dibatalkan jika batas ini terlampaui.
               dir 'error_sales/', -- Tentukan direktori objek OSS untuk tabel eksternal.
               format 'csv', -- Tentukan format objek sebagai CSV.
               encoding 'utf8'); -- Tentukan encoding. 

• Lakukan kueri log kesalahan.

  SELECT * FROM gp_read_error_log('oss_error_sales');

• Bersihkan log kesalahan.

  SELECT gp_truncate_error_log('oss_error_sales');

FAQ

T: Apakah menghapus data dari tabel eksternal OSS juga menghapus data dasar di OSS?

J: Tidak. Menghapus data dari tabel eksternal OSS tidak menghapus data dasar di OSS.

Dokumentasi terkait

• Menggunakan tabel eksternal OSS terpartisi

• Mengimpor dan mengekspor data dengan tabel eksternal OSS

• Praktik terbaik tabel eksternal OSS

• Pemetaan tipe data untuk tabel eksternal OSS