Cara mengimpor data menggunakan COPY atau mengekspor data menggunakan UNLOAD ke OSS - AnalyticDB

Pernyataan COPY mengimpor data dari Object Storage Service (OSS) ke tabel AnalyticDB for PostgreSQL, sedangkan UNLOAD mengekspor hasil kueri dari tabel AnalyticDB for PostgreSQL ke OSS. Kedua pernyataan ini beroperasi melalui tabel eksternal OSS. Untuk informasi latar belakang, lihat Gunakan tabel eksternal OSS untuk mengakses data OSS.

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

Instans AnalyticDB for PostgreSQL
Bucket OSS yang dapat diakses dengan kredensial AccessKey Anda
Ekstensi oss_fdw yang telah diinstal pada instans

COPY

Gunakan pernyataan COPY untuk mengimpor data dari path bucket OSS atau file manifest ke dalam tabel AnalyticDB for PostgreSQL.

Sintaks

COPY <table_name>
[ <column_list> ]
FROM <data_source>
ACCESS_KEY_ID '<access_key_id>'
SECRET_ACCESS_KEY '<secret_access_key>'
[ [ FORMAT ] [ AS ] <data_format> ]
[ MANIFEST ]
[ option '<value>' [ ... ] ]

Parameter

Parameter	Wajib	Deskripsi
`table_name`	Ya	Tabel AnalyticDB for PostgreSQL tujuan. Tabel tersebut harus sudah ada di instans.
`column_list`	Tidak	Kolom yang akan diisi data. Jika dihilangkan, data ditulis ke semua kolom.
`data_source`	Ya	Path OSS yang akan dibaca. Format: `oss://<bucket_name>/path_prefix`.
`access_key_id`	Ya	ID AccessKey dari Akun Alibaba Cloud atau pengguna Resource Access Management (RAM) yang memiliki akses ke OSS. Gunakan pengguna RAM dengan izin minimal yang diperlukan, bukan kredensial akun root Anda. Untuk petunjuknya, lihat Dapatkan pasangan AccessKey.
`secret_access_key`	Ya	Rahasia AccessKey yang sesuai dengan ID AccessKey. Untuk petunjuknya, lihat Dapatkan pasangan AccessKey.
`[ FORMAT ] [ AS ] <data_format>`	Tidak	Format data sumber. Nilai default adalah CSV. Nilai yang didukung: BINARY, CSV, JSON, JSONLINE, ORC, PARQUET, TEXT. `FORMAT AS CSV`, `FORMAT CSV`, dan `CSV` setara.
`MANIFEST`	Tidak	Perlakukan `data_source` sebagai file manifest, bukan sebagai path data. Lihat format file MANIFEST di bawah ini.
`option 'value'`	Tidak	Opsi tambahan dalam format `key value`. Lihat tabel opsi di bawah ini.

Opsi

Opsi	Tipe	Wajib	Deskripsi
`ENDPOINT`	STRING	Ya	Titik akhir OSS. Untuk daftar titik akhir berdasarkan wilayah, lihat Wilayah dan titik akhir.
`FDW`	STRING	Ya	Nama ekstensi oss_fdw. Diperlukan untuk membuat server OSS temporary untuk pernyataan COPY.
`FORMAT`, `FILETYPE`, `DELIMITER`, `ESCAPE`, dan lainnya	—	—	Opsi untuk membuat tabel eksternal OSS temporary. Untuk detailnya, lihat Ikhtisar tabel eksternal OSS.

Format file MANIFEST

Ketika MANIFEST ditentukan, data_source harus mengarah ke file JSON dengan struktur berikut:

{
  "entries": [
    {"url": "oss://adbpg-regress/local_t/_seg2_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress/local_t/_seg1_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress/local_t/_seg0_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_seg1_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_seg2_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_seg0_0.csv", "mandatory": true}
  ]
}

Field	Deskripsi
`entries`	Array objek OSS. Objek dapat tersebar di bucket dan path yang berbeda, tetapi semuanya harus dapat diakses dengan ID AccessKey dan rahasia yang sama.
`url`	Path lengkap objek OSS.
`mandatory`	Jika `true`, impor gagal ketika objek tidak ditemukan.

Toleransi kesalahan

Saat mengimpor data, beberapa baris mungkin gagal diurai. Konfigurasikan opsi berikut untuk menangani error tanpa menghentikan seluruh proses impor:

Opsi	Deskripsi
`log_errors 'true'`	Catat baris yang rusak ke log error alih-alih langsung gagal.
`segment_reject_limit '<n>'`	Hentikan impor dan kembalikan error jika jumlah baris yang ditolak mencapai `n` (misalnya, `'10'`) atau laju penolakan mencapai `n`% (misalnya, `'10%'`). Gunakan ambang batas jumlah untuk anggaran error yang dapat diprediksi; gunakan ambang batas persentase untuk set data berukuran variabel di mana jumlah absolut lebih sulit dipertimbangkan.

Setelah impor selesai, kueri log error:

SELECT * FROM gp_read_error_log('<table_name>');

Log error mengonsumsi ruang penyimpanan. Hapus log tersebut jika tidak lagi diperlukan:

SELECT gp_truncate_error_log('<table_name>');

Contoh

Impor kolom tertentu dari CSV

Buat tabel tujuan.

CREATE TABLE local_t2 (a int, b float8, c text);

Impor data ke kolom a dan c. Kolom b menerima nilai NULL.

COPY local_t2 (a, c)
FROM 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Verifikasi hasil impor.

SELECT * FROM local_t2 LIMIT 10;

Output yang diharapkan:

 a  | b |                c
----+---+----------------------------------
  12 |   | a24cba6ebdc5e0c485cd88ef60b72fea
  15 |   | c4d3028f5205fab98e5f43c7945db4ba
  20 |   | 769884311db01f400e21a903a3f1cb50
...
(10 rows)

Periksa apakah data di kolom a dan c pada tabel local_t2 cocok dengan data di tabel local_t.

SELECT sum(hashtext(t.a::text)) AS col_a_hash, sum(hashtext(t.c::text)) AS col_c_hash FROM local_t2 t;

Output yang diharapkan:

 col_a_hash  | col_c_hash
-------------+-------------
 23725368368 | 13447976580
(1 row)

SELECT sum(hashtext(t.a::text)) AS col_a_hash, sum(hashtext(t.c::text)) AS col_c_hash FROM local_t t;

Output yang diharapkan:

 col_a_hash  | col_c_hash
-------------+-------------
 23725368368 | 13447976580
(1 row)

Impor file ORC atau Parquet menggunakan sintaks yang sama dengan FORMAT berbeda:

-- ORC
COPY tt
FROM 'oss://adbpg-regress/q_oss_orc_list/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS ORC
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

-- Parquet
COPY tp
FROM 'oss://adbpg-regress/test_parquet/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS PARQUET
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Impor dari file manifest

Buat tabel tujuan.

CREATE TABLE local_manifest (a int, c text);

Buat file manifest yang mereferensikan objek dari beberapa bucket.

{
  "entries": [
    {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1450404435_seg2_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1856683967_seg1_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1880804901_seg0_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1008521914_seg1_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1234881553_seg2_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1711667760_seg0_0.csv", "mandatory": true}
  ]
}

Impor menggunakan path file manifest.

COPY local_manifest
FROM 'oss://adbpg-regress-2/unload_manifest/t_manifest'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
MANIFEST
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Tangani error impor dengan toleransi kesalahan

Buat tabel tujuan.

CREATE TABLE sales (id integer, value float8, x text) DISTRIBUTED BY (id);

Impor dengan pencatatan error diaktifkan. Impor tetap berjalan meskipun ada baris yang gagal, tetapi berhenti ketika terdapat 10 atau lebih error.

COPY sales
FROM 'oss://adbpg-const/error_sales/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
log_errors 'true'
segment_reject_limit '10'
endpoint 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Output yang diharapkan:

NOTICE:  found 3 data formatting errors (3 or more input rows), rejected related input data
COPY FOREIGN TABLE

Kueri detail error.

SELECT * FROM gp_read_error_log('sales');

Output yang diharapkan:

         cmdtime            |                    relname                     |        filename         | linenum | bytenum |                          errmsg                           | rawdata | rawbytes
----------------------------+------------------------------------------------+-------------------------+---------+---------+-----------------------------------------------------------+---------+----------
 2021-02-08 14:24:04.225238 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.2.csv |       2 |         | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad |         | \x
 2021-02-08 14:24:04.225238 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.2.csv |       3 |         | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad |         | \x
 2021-02-08 14:24:04.225269 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.3.csv |       2 |         | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad |         | \x
(3 rows)

UNLOAD

Gunakan pernyataan UNLOAD untuk mengekspor hasil kueri dari tabel AnalyticDB for PostgreSQL ke OSS.

Catatan penggunaan

Saat mengekspor ke CSV, sertakan opsi berikut dalam tanda kutip ganda dan tulis dalam huruf kecil: delimiter, quote, null, header, escape, dan encoding. Tanpa tanda kutip, nama opsi ini dapat diinterpretasikan sebagai kata kunci SQL, menyebabkan error sintaks.

UNLOAD ('SELECT * FROM test')
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT csv
"delimiter" '|'
"quote" '"'
"null" ''
"header" 'true'
"escape" 'E'
"encoding" 'utf-8'
FDW 'oss_fdw'
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com';

Sintaks

UNLOAD ('<select_statement>')
TO <destination_url>
ACCESS_KEY_ID '<access_key_id>'
SECRET_ACCESS_KEY '<secret_access_key>'
[ [ FORMAT ] [ AS ] <data_format> ]
[ MANIFEST [ '<manifest_url>' ] ]
[ PARALLEL [ { ON | TRUE } | { OFF | FALSE } ] ]
[ option '<value>' [ ... ] ]

Parameter

Parameter	Wajib	Deskripsi
`select_statement`	Ya	Pernyataan SELECT yang hasilnya ditulis ke OSS.
`destination_url`	Ya	Path OSS tempat menulis data. Format: `oss://<bucket_name>/path_prefix`.
`access_key_id`	Ya	ID AccessKey dari Akun Alibaba Cloud atau pengguna RAM yang memiliki akses ke OSS. Gunakan pengguna RAM dengan izin minimal yang diperlukan, bukan kredensial akun root Anda. Untuk petunjuknya, lihat Dapatkan pasangan AccessKey.
`secret_access_key`	Ya	Rahasia AccessKey yang sesuai dengan ID AccessKey. Untuk petunjuknya, lihat Dapatkan pasangan AccessKey.
`[ FORMAT ] [ AS ] <data_format>`	Tidak	Format data yang diekspor. Nilai default adalah CSV. Nilai yang didukung: CSV, ORC, TEXT.
`MANIFEST [ '<manifest_url>' ]`	Tidak	Hasilkan file manifest yang mencantumkan semua objek yang diekspor. Jika `manifest_url` ditentukan, manifest ditulis ke path tersebut (yang harus diakhiri dengan sufiks `manifest`) dan dapat berada di bucket yang berbeda dari file data. Jika dihilangkan, awalan path manifest sama dengan file data. Jika file manifest sudah ada, atur `ALLOWOVERWRITE 'true'` untuk menimpanya.
`PARALLEL`	Tidak	Mengontrol ekspor paralel. Default: `ON`. Ketika `ON`, setiap node komputasi menulis file output terpisah — jumlah file output sama dengan jumlah node komputasi di instans Anda. Ketika `OFF` atau `FALSE`, data diekspor secara serial dan disimpan ke satu file jika ukuran totalnya 8 GB atau kurang.
`option 'value'`	Tidak	Opsi tambahan dalam format `key value`. Lihat tabel opsi di bawah ini.

Opsi

Opsi	Tipe	Wajib	Deskripsi
`ENDPOINT`	STRING	Ya	Titik akhir OSS. Untuk daftar titik akhir berdasarkan wilayah, lihat Wilayah dan titik akhir.
`FDW`	STRING	Ya	Nama ekstensi oss_fdw. Diperlukan untuk membuat server OSS temporary untuk pernyataan UNLOAD.
`FORMAT`, `FILETYPE`, `DELIMITER`, `ESCAPE`, dan lainnya	—	—	Opsi untuk membuat tabel eksternal OSS temporary. Untuk detailnya, lihat Ikhtisar tabel eksternal OSS.

Contoh

Ekspor ke CSV

Buat tabel sumber dan masukkan data uji.

CREATE TABLE local_t (a int, b float8, c text);
INSERT INTO local_t
SELECT r, random() * 1000, md5(random()::text)
FROM generate_series(1, 1000) r;

Verifikasi data sumber.

SELECT * FROM local_t LIMIT 5;

Output yang diharapkan:

 a  |        b         |                c
----+------------------+----------------------------------
   5 |  550.81393988803 | 8009fa725372e996786849213a695ce0
   6 | 95.8335199393332 | ce7952c6728cdffdee06cc5b502d6457
   9 | 421.379795763642 | d3260ccbf6b9c03f3658d96bb7678b4d
  10 | 362.347379792482 | 2bbbf89d23a2f83b089b589f55b5c4fc
  11 | 800.203878898174 | a52994c5573e6b36d8a1c357bf800ce5
(5 rows)

Ekspor kolom a dan c ke OSS dalam format CSV.

UNLOAD ('SELECT a, c FROM local_t')
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Output yang diharapkan:

NOTICE:  OSS output prefix: "local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618".
UNLOAD

Konfirmasi bahwa file telah ditulis ke OSS.

ossutil --config hangzhou-zmf.config ls oss://adbpg-regress/local_t/

Output yang diharapkan (satu file per node komputasi):

LastModifiedTime                   Size(B)  StorageClass   ETAG                                  ObjectName
2020-09-07 16:48:01 +0800 CST        12023      Standard   9F38B5407142C044C1F3555F00000000      oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg0_0.csv
2020-09-07 16:48:01 +0800 CST        12469      Standard   807BA680A0DED49BC1F3555F00000000      oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg1_0.csv
2020-09-07 16:48:01 +0800 CST        12401      Standard   3524F68F628CEB64C1F3555F00000000      oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv
Object Number is: 3

Periksa konten CSV.

head -n 10 adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv

Output yang diharapkan:

7,1225341d0d367a69b1b345536b21ef73
19,424a7a5c36066842f4de8c8a8341fc89
27,c214432e9928e4a6f7bef7bd815424c0
29,ade5d636e2b5d2a606a02e79255da4bd
37,85660e60ede47b68493f6295620db568

Ekspor dengan file manifest

Ketiga contoh berikut menggunakan tabel sumber yang sama (local_t) yang dibuat pada bagian sebelumnya.

Skenario 1: Hasilkan file manifest bersama file data

UNLOAD ('SELECT * FROM local_t')
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
MANIFEST
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Daftar file yang diekspor:

ossutil ls -s oss://adbpg-regress/local_t/

Output yang diharapkan — tiga file data dan satu file manifest berbagi awalan path yang sama:

oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_162488956_seg1_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_163756258_seg0_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_1741120517_seg2_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_manifest
Object Number is: 4

Lihat konten manifest:

ossutil cat oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_manifest

{
  "entries": [
    {"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_162488956_seg1_0.csv"},
    {"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_163756258_seg0_0.csv"},
    {"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_1741120517_seg2_0.csv"}
  ]
}

Skenario 2: Tulis file manifest ke bucket berbeda

ALLOWOVERWRITE 'true' hanya menimpa file manifest yang sudah ada. File data tidak ditimpa dan harus dihapus secara manual jika diperlukan.

UNLOAD ('SELECT * FROM local_t')
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
MANIFEST 'oss://adbpg-regress-2/unload_manifest/t_manifest'
ALLOWOVERWRITE 'true'
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Verifikasi bahwa file data berada di bucket asli dan manifest berada di bucket terpisah:

# File data
ossutil ls -s oss://adbpg-regress/local_t/

oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1736161168_seg0_0.csv
oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1925769064_seg2_0.csv
oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_644328153_seg1_0.csv
Object Number is: 3

# File manifest di bucket berbeda
ossutil cat oss://adbpg-regress-2/unload_manifest/t_manifest

{
  "entries": [
    {"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1736161168_seg0_0.csv"},
    {"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1925769064_seg2_0.csv"},
    {"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_644328153_seg1_0.csv"}
  ]
}

FAQ

Mengapa ekspor menghasilkan beberapa file CSV?

UNLOAD menulis satu file output per node komputasi secara default (PARALLEL ON). Jika instans Anda memiliki empat node komputasi, Anda akan mendapatkan empat file. Untuk mengekspor ke satu file, atur PARALLEL OFF — tetapi hanya jika ukuran total data tidak melebihi 8 GB.

AnalyticDB:Impor atau ekspor data ke OSS menggunakan perintah COPY atau UNLOAD

Prasyarat

COPY

Sintaks

Parameter

Opsi

Format file MANIFEST

Toleransi kesalahan

Contoh

Impor kolom tertentu dari CSV

Impor dari file manifest

Tangani error impor dengan toleransi kesalahan

UNLOAD

Catatan penggunaan

Sintaks

Parameter

Opsi

Contoh

Ekspor ke CSV

Ekspor dengan file manifest

FAQ

Langkah selanjutnya