AnalyticDB：Gunakan pernyataan COPY atau UNLOAD untuk mengimpor atau mengekspor data antara tabel asing OSS dan tabel AnalyticDB for PostgreSQL

COPY

Sintaksis

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

Opsi

Contoh

Contoh 1

1. Buat tabel AnalyticDB for PostgreSQL.

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

2. Gunakan pernyataan COPY untuk mengimpor data ke kolom a dan c. Kolom b diberi 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';

3. Kueri data tabel.

   SELECT * FROM local_t2 LIMIT 10;

   Hasil sampel:

    a  | b |                c
   ----+---+----------------------------------
    12 |   | a24cba6ebdc5e0c485cd88ef60b72fea
    15 |   | c4d3028f5205fab98e5f43c7945db4ba
    20 |   | 769884311db01f400e21a903a3f1cb50
    26 |   | 7d12c981d262e0067ea1a04368f32f2a
    30 |   | 4e64bda52d54d263d16f42771b1d0225
    35 |   | b70c976d4c04568bd497b42a7d2e451d
    40 |   | d07ce2948b8618b47c351b6e222182f6
    46 |   | c2234393f878f5557776b7e778299564
    47 |   | cde904b2331fa274cd8d9266aa858342
    50 |   | 1235b900fb644bb36440a274314e4b6b
   (10 baris)

4. Periksa apakah data di kolom a dan c tabel local_t2 sama dengan 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;

     Hasil sampel:

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

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

     Hasil sampel:

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

5. Simpan data dalam format selain CSV.

   • Simpan data dalam format 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';

   • Simpan data dalam format 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';

Contoh 2

1. Buat tabel AnalyticDB for PostgreSQL.

    CREATE TABLE local_manifest (a int, c text);

2. Buat file manifest di mana objek OSS dapat berada di bucket yang berbeda.

   {
      "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}
      ]
   }

3. Gunakan pernyataan COPY untuk mengimpor data dari file manifest ke tabel AnalyticDB for PostgreSQL.

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

Contoh 3

Saat menggunakan pernyataan COPY untuk mengimpor data dari OSS, baris kesalahan mungkin dikembalikan. Dalam hal ini, Anda dapat mengonfigurasi opsi berikut untuk menerapkan toleransi kesalahan:

• log_errors: Menentukan apakah akan mencatat informasi baris kesalahan dalam file log.

• segment_reject_limit: segment_reject_limit '10' menentukan jika jumlah baris kesalahan lebih besar dari atau sama dengan 10, sistem mengembalikan kesalahan dan keluar. segment_reject_limit '10%' menentukan jika proporsi baris kesalahan lebih besar dari atau sama dengan 10%, sistem mengembalikan kesalahan dan keluar.

1. Buat tabel AnalyticDB for PostgreSQL.

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

2. Gunakan pernyataan COPY untuk mengimpor data dari objek OSS yang memiliki tiga baris kesalahan.

   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';
   NOTICE:  found 3 data formatting errors (3 or more input rows), rejected related input data
   COPY FOREIGN TABLE

3. Jalankan pernyataan berikut untuk menanyakan detail baris kesalahan:

   SELECT * FROM gp_read_error_log('<Nama tabel tujuan pernyataan COPY>');

   Dalam contoh berikut, detail baris kesalahan tabel sales diperiksa:

   SELECT * FROM gp_read_error_log('sales');

   Hasil sampel:

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

   Catatan

   Log baris kesalahan tambahan yang disimpan memakan ruang penyimpanan. Anda dapat menggunakan sintaksis berikut untuk menghapus log baris kesalahan: SELECT gp_truncate_error_log('<table_name>').

UNLOAD

Catatan penggunaan

Saat mengekspor data ke file CSV, Anda harus menyertakan opsi dalam tanda kutip ("") dan menulis opsi dalam huruf kecil. Jika tidak mengikuti persyaratan ini, beberapa opsi mungkin diproses sebagai kata kunci, yang dapat mengakibatkan kesalahan sintaksis. Opsi berikut harus ditentukan dengan cara tertentu: delimiter, quote, null, header, escape, dan encoding. Contoh:

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';

Sintaksis

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

Opsi

Contoh

Contoh 1

1. Buat tabel AnalyticDB for PostgreSQL dan masukkan data uji ke dalam tabel.

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;

2. Kueri data tabel AnalyticDB for PostgreSQL.

   SELECT * FROM local_t LIMIT 5;

   Hasil sampel:

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

3. Gunakan pernyataan UNLOAD untuk mengekspor data dari kolom tertentu tabel AnalyticDB for PostgreSQL ke OSS dan simpan data 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';
NOTICE:  OSS output prefix: "local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618".
UNLOAD

4. Periksa apakah file CSV ditulis ke jalur yang ditentukan.

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

   Hasil sampel:

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

0.153414(s) elapsed

5. Periksa apakah file CSV hanya berisi data di kolom a dan c tabel local_t.

   $ head -n 10 adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv

   Hasil sampel:

7,1225341d0d367a69b1b345536b21ef73
19,424a7a5c36066842f4de8c8a8341fc89
27,c214432e9928e4a6f7bef7bd815424c0
29,ade5d636e2b5d2a606a02e79255da4bd
37,85660e60ede47b68493f6295620db568
77,e1be448ba2b08f0a2ca05b7ed812abfd
80,5e85d597a3b0f2f9736a728724a0f9e0
92,dc23f76f0b1446504b8f1c2274521d2f
94,50304822488d55a500e3a71bcf40890f
97,e970fde8cd0df9c6b610925a488f6042

Contoh 2

1. Gunakan pernyataan UNLOAD untuk mengekspor data dan menghasilkan file manifest. Awalan jalur file manifest sama dengan 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    -- Hasilkan file manifest saat pernyataan UNLOAD digunakan untuk mengekspor data. Awalan jalur file manifest sama dengan file data. 
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';
NOTICE:  OSS output prefix: "local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c".
UNLOAD

2. Lihat daftar file yang diekspor.

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

   Daftar tersebut mencakup beberapa file data dan file manifest. Hasil sampel:

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

0.136180(s) elapsed

3. Lihat isi file manifest.

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

   Hasil sampel:

{
   "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"}
   ]
}

4. Gunakan pernyataan UNLOAD untuk mengekspor data dan menghasilkan file manifest yang ditentukan. Jalur file manifest mungkin berbeda dari file data.

Catatan

Jika Anda mengatur opsi ALLOWOVERWRITE ke true, file manifest yang ada akan ditimpa. Namun, file data tidak ditimpa. File data dapat dihapus secara manual.

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' -- Hasilkan file manifest yang ditentukan saat pernyataan UNLOAD digunakan untuk mengekspor data. 
ALLOWOVERWRITE 'true' -- Timpa file manifest yang ada. 
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';
NOTICE:  OSS output prefix: "local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c".
UNLOAD

5. Lihat daftar file yang diekspor.

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

   Hanya file data yang termasuk dalam daftar. Hasil sampel:

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

0.118540(s) elapsed

6. Lihat isi file manifest yang disimpan di bucket berbeda dari file data.

   ossutil cat oss://adbpg-regress-2/unload_manifest/t_manifest

   Hasil sampel:

{
   "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"}
   ]
}

Pertanyaan Umum

T: Beberapa file CSV dihasilkan saat saya mengekspor data. Mengapa?

A: Saat menggunakan pernyataan UNLOAD untuk mengekspor data dari AnalyticDB for PostgreSQL ke OSS, satu file CSV dihasilkan untuk setiap node komputasi. Sebagai contoh, jika instance Anda memiliki empat node komputasi, empat file CSV dihasilkan.