Contoh COPY - Amazon Redshift
Muat FAVORITEMOVIES dari tabel DynamoDBMuat LISTING dari Bucket Amazon S3Muat LISTING dari kluster EMR AmazonMenggunakan manifes untuk menentukan file dataMuat LISTING dari file yang dibatasi pipa (pembatas default)Muat LISTING menggunakan data kolumnar dalam format ParketMuat LISTING menggunakan data kolumnar dalam format ORCMuat EVENT dengan opsiMuat VENUE dari file data dengan lebar tetapMuat KATEGORI dari file CSVMuat VENUE dengan nilai eksplisit untuk kolom IDENTITYMuat WAKTU dari file GZIP yang dibatasi pipaMemuat stempel waktu atau datestampMemuat data dari file dengan nilai defaultSALIN data dengan opsi ESCAPESalin dari contoh JSONSalin dari contoh AvroMempersiapkan file untuk COPY dengan opsi ESCAPEMemuat shapefileCOPY perintah dengan opsi NOLOAD

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Contoh COPY

catatan

Contoh-contoh ini berisi jeda baris untuk keterbacaan. Jangan sertakan jeda baris atau spasi dalam string credentials-args Anda.

Muat FAVORITEMOVIES dari tabel DynamoDB

AWS SDK menyertakan contoh sederhana untuk membuat tabel DynamoDB yang disebut Movies. (Untuk contoh ini, lihat Memulai dengan DynamoDB.) Contoh berikut memuat tabel Amazon Redshift MOVIES dengan data dari tabel DynamoDB. Tabel Amazon Redshift harus sudah ada di database.

copy favoritemovies from 'dynamodb://Movies'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
readratio 50;

Muat LISTING dari bucket Amazon S3

Contoh berikut memuat LISTING dari bucket Amazon S3. Perintah COPY memuat semua file di /data/listing/ folder.

copy listing
from 's3://mybucket/data/listing/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

Muat LISTING dari kluster EMR Amazon

Contoh berikut memuat tabel PENJUALAN dengan data yang dibatasi tab dari file terkompresi lzop di cluster EMR Amazon. COPY memuat setiap file di myoutput/ folder yang dimulai denganpart-.

copy sales
from 'emr://j-SAMPLE2B500FC/myoutput/part-*' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '\t' lzop;

Contoh berikut memuat tabel PENJUALAN dengan data yang diformat JSON di cluster EMR Amazon. COPY memuat setiap file di myoutput/json/ folder.

copy sales
from 'emr://j-SAMPLE2B500FC/myoutput/json/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
JSON 's3://mybucket/jsonpaths.txt';

Menggunakan manifes untuk menentukan file data

Anda dapat menggunakan manifes untuk memastikan bahwa perintah COPY memuat semua file yang diperlukan, dan hanya file yang diperlukan, dari Amazon S3. Anda juga dapat menggunakan manifes saat Anda perlu memuat beberapa file dari bucket atau file berbeda yang tidak memiliki awalan yang sama.

Misalnya, anggaplah Anda perlu memuat tiga file berikut:custdata1.txt,custdata2.txt, dancustdata3.txt. Anda dapat menggunakan perintah berikut untuk memuat semua file mybucket yang dimulai custdata dengan menentukan awalan: 

copy category
from 's3://mybucket/custdata' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

Jika hanya dua file yang ada karena kesalahan, COPY hanya memuat dua file tersebut dan selesai dengan sukses, menghasilkan beban data yang tidak lengkap. Jika bucket juga berisi file yang tidak diinginkan yang kebetulan menggunakan awalan yang sama, seperti file bernama custdata.backup misalnya, COPY memuat file itu juga, sehingga data yang tidak diinginkan dimuat.

Untuk memastikan bahwa semua file yang diperlukan dimuat dan untuk mencegah file yang tidak diinginkan dimuat, Anda dapat menggunakan file manifes. Manifes adalah file teks berformat JSON yang mencantumkan file yang akan diproses oleh perintah COPY. Misalnya, manifes berikut memuat tiga file dalam contoh sebelumnya.

{  
   "entries":[  
      {  
         "url":"s3://mybucket/custdata.1",
         "mandatory":true
      },
      {  
         "url":"s3://mybucket/custdata.2",
         "mandatory":true
      },
      {  
         "url":"s3://mybucket/custdata.3",
         "mandatory":true
      }
   ]
}

mandatoryBendera opsional menunjukkan apakah COPY harus dihentikan jika file tidak ada. Nilai default-nya false. Terlepas dari pengaturan wajib apa pun, COPY berakhir jika tidak ada file yang ditemukan. Dalam contoh ini, COPY mengembalikan kesalahan jika salah satu file tidak ditemukan. File yang tidak diinginkan yang mungkin telah diambil jika Anda menetapkan hanya sebuah key prefix, seperti, diabaikancustdata.backup, karena mereka tidak berada di manifes.

Saat memuat dari file data dalam format ORC atau Parket, meta bidang diperlukan, seperti yang ditunjukkan pada contoh berikut.

{  
   "entries":[  
      {  
         "url":"s3://mybucket-alpha/orc/2013-10-04-custdata",
         "mandatory":true,
         "meta":{  
            "content_length":99
         }
      },
      {  
         "url":"s3://mybucket-beta/orc/2013-10-05-custdata",
         "mandatory":true,
         "meta":{  
            "content_length":99
         }
      }
   ]
}

Contoh berikut menggunakan manifes bernamacust.manifest. 

copy customer
from 's3://mybucket/cust.manifest' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as orc
manifest;

Anda dapat menggunakan manifes untuk memuat file dari bucket atau file yang berbeda yang tidak memiliki awalan yang sama. Contoh berikut menunjukkan JSON untuk memuat data dengan file yang namanya dimulai dengan cap tanggal.

{
  "entries": [
    {"url":"s3://mybucket/2013-10-04-custdata.txt","mandatory":true},
    {"url":"s3://mybucket/2013-10-05-custdata.txt","mandatory":true},
    {"url":"s3://mybucket/2013-10-06-custdata.txt","mandatory":true},
    {"url":"s3://mybucket/2013-10-07-custdata.txt","mandatory":true}
  ]
}

Manifes dapat mencantumkan file yang ada di bucket yang berbeda, selama bucket berada di AWS Wilayah yang sama dengan cluster. 

{
  "entries": [
    {"url":"s3://mybucket-alpha/custdata1.txt","mandatory":false},
    {"url":"s3://mybucket-beta/custdata1.txt","mandatory":false},
    {"url":"s3://mybucket-beta/custdata2.txt","mandatory":false}
  ]
}

Muat LISTING dari file yang dibatasi pipa (pembatas default)

Contoh berikut adalah kasus yang sangat sederhana di mana tidak ada opsi yang ditentukan dan file input berisi pembatas default, karakter pipa ('|'). 

copy listing 
from 's3://mybucket/data/listings_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

Muat LISTING menggunakan data kolumnar dalam format Parket

Contoh berikut memuat data dari folder di Amazon S3 bernama parket. 

copy listing 
from 's3://mybucket/data/listings/parquet/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as parquet;

Muat LISTING menggunakan data kolumnar dalam format ORC

Contoh berikut memuat data dari folder di Amazon S3 bernama. orc 

copy listing 
from 's3://mybucket/data/listings/orc/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as orc;

Muat EVENT dengan opsi

Contoh berikut memuat data yang dibatasi pipa ke dalam tabel EVENT dan menerapkan aturan berikut:

  • Jika pasangan tanda kutip digunakan untuk mengelilingi string karakter apa pun, mereka akan dihapus.

  • Baik string kosong dan string yang berisi kosong dimuat sebagai nilai NULL.

  • Beban gagal jika lebih dari 5 kesalahan dikembalikan.

  • Nilai stempel waktu harus sesuai dengan format yang ditentukan; misalnya, stempel waktu yang valid adalah. 2008-09-26 05:43:12

copy event
from 's3://mybucket/data/allevents_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
removequotes
emptyasnull
blanksasnull
maxerror 5
delimiter '|'
timeformat 'YYYY-MM-DD HH:MI:SS';

Muat VENUE dari file data dengan lebar tetap

copy venue
from 's3://mybucket/data/venue_fw.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
fixedwidth 'venueid:3,venuename:25,venuecity:12,venuestate:2,venueseats:6';

Contoh sebelumnya mengasumsikan file data yang diformat dengan cara yang sama seperti data sampel yang ditampilkan. Dalam contoh berikut, spasi bertindak sebagai placeholder sehingga semua kolom memiliki lebar yang sama seperti yang tercantum dalam spesifikasi: 

1  Toyota Park              Bridgeview  IL0
2  Columbus Crew Stadium    Columbus    OH0
3  RFK Stadium              Washington  DC0
4  CommunityAmerica BallparkKansas City KS0
5  Gillette Stadium         Foxborough  MA68756

Muat KATEGORI dari file CSV

Misalkan Anda ingin memuat KATEGORI dengan nilai yang ditunjukkan pada tabel berikut.

catid kelompok kucing nama kucing catdesc
12 Menunjukkan Musikal Teater musikal
13 Menunjukkan Dimainkan Semua teater “non-musikal”
14 Menunjukkan Opera Semua opera, cahaya, dan opera “rock”
15 Konser Klasik Semua konser simfoni, konser, dan paduan suara

Contoh berikut menunjukkan isi file teks dengan nilai bidang dipisahkan dengan koma.

12,Shows,Musicals,Musical theatre
13,Shows,Plays,All "non-musical" theatre  
14,Shows,Opera,All opera, light, and "rock" opera
15,Concerts,Classical,All symphony, concerto, and choir concerts

Jika Anda memuat file menggunakan parameter DELIMITER untuk menentukan input yang dibatasi koma, perintah COPY gagal karena beberapa kolom input berisi koma. Anda dapat menghindari masalah itu dengan menggunakan parameter CSV dan melampirkan bidang yang berisi koma dalam karakter tanda kutip. Jika karakter tanda kutip muncul dalam string yang dikutip, Anda harus menghindarinya dengan menggandakan karakter tanda kutip. Karakter tanda kutip default adalah tanda kutip ganda, jadi Anda harus keluar dari setiap tanda kutip ganda dengan tanda kutip ganda tambahan. File input baru Anda terlihat seperti ini. 

12,Shows,Musicals,Musical theatre
13,Shows,Plays,"All ""non-musical"" theatre"
14,Shows,Opera,"All opera, light, and ""rock"" opera"
15,Concerts,Classical,"All symphony, concerto, and choir concerts"

Dengan asumsi nama filecategory_csv.txt, Anda dapat memuat file dengan menggunakan perintah COPY berikut:

copy category
from 's3://mybucket/data/category_csv.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
csv;

Atau, untuk menghindari kebutuhan untuk menghindari tanda kutip ganda di input Anda, Anda dapat menentukan karakter tanda kutip yang berbeda dengan menggunakan parameter QUOTE AS. Misalnya, versi berikut category_csv.txt menggunakan '%' sebagai karakter tanda kutip.

12,Shows,Musicals,Musical theatre
13,Shows,Plays,%All "non-musical" theatre%
14,Shows,Opera,%All opera, light, and "rock" opera%
15,Concerts,Classical,%All symphony, concerto, and choir concerts%

Perintah COPY berikut menggunakan QUOTE AS untuk memuatcategory_csv.txt:

copy category
from 's3://mybucket/data/category_csv.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
csv quote as '%';

Muat VENUE dengan nilai eksplisit untuk kolom IDENTITY

Contoh berikut mengasumsikan bahwa ketika tabel VENUE dibuat bahwa setidaknya satu kolom (seperti venueid kolom) ditentukan untuk menjadi kolom IDENTITY. Perintah ini mengesampingkan perilaku IDENTITY default dari nilai autogenerating untuk kolom IDENTITY dan sebagai gantinya memuat nilai eksplisit dari file venue.txt. Amazon Redshift tidak memeriksa apakah nilai IDENTITAS duplikat dimuat ke dalam tabel saat menggunakan opsi EXLICIT_IDS. 

copy venue
from 's3://mybucket/data/venue.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
explicit_ids;

Muat WAKTU dari file GZIP yang dibatasi pipa

Contoh berikut memuat tabel TIME dari file GZIP yang dibatasi pipa:

copy time
from 's3://mybucket/data/timerows.gz' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
gzip
delimiter '|';

Memuat stempel waktu atau datestamp

Contoh berikut memuat data dengan stempel waktu yang diformat.

catatan

TIMEFORMAT juga HH:MI:SS dapat mendukung detik pecahan di SS luar tingkat detail mikrodetik. File yang time.txt digunakan dalam contoh ini berisi satu baris,2009-01-12 14:15:57.119568.

copy timestamp1 
from 's3://mybucket/data/time.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
timeformat 'YYYY-MM-DD HH:MI:SS';

Hasil dari salinan ini adalah sebagai berikut: 

select * from timestamp1;
c1
----------------------------
2009-01-12 14:15:57.119568
(1 row)

Memuat data dari file dengan nilai default

Contoh berikut menggunakan variasi tabel VENUE dalam database TICKIT. Pertimbangkan tabel VENUE_NEW yang didefinisikan dengan pernyataan berikut: 

create table venue_new(
venueid smallint not null,
venuename varchar(100) not null,
venuecity varchar(30),
venuestate char(2),
venueseats integer not null default '1000');

Pertimbangkan file data venue_noseats.txt yang tidak berisi nilai untuk kolom VENUESEATS, seperti yang ditunjukkan pada contoh berikut: 

1|Toyota Park|Bridgeview|IL|
2|Columbus Crew Stadium|Columbus|OH|
3|RFK Stadium|Washington|DC|
4|CommunityAmerica Ballpark|Kansas City|KS|
5|Gillette Stadium|Foxborough|MA|
6|New York Giants Stadium|East Rutherford|NJ|
7|BMO Field|Toronto|ON|
8|The Home Depot Center|Carson|CA|
9|Dick's Sporting Goods Park|Commerce City|CO|
10|Pizza Hut Park|Frisco|TX|

Pernyataan COPY berikut akan berhasil memuat tabel dari file dan menerapkan nilai DEFAULT ('1000') ke kolom dihilangkan: 

copy venue_new(venueid, venuename, venuecity, venuestate) 
from 's3://mybucket/data/venue_noseats.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|';

Sekarang lihat tabel yang dimuat: 

select * from venue_new order by venueid;
venueid |         venuename          |    venuecity    | venuestate | venueseats
---------+----------------------------+-----------------+------------+------------
1 | Toyota Park                | Bridgeview      | IL         |       1000
2 | Columbus Crew Stadium      | Columbus        | OH         |       1000
3 | RFK Stadium                | Washington      | DC         |       1000
4 | CommunityAmerica Ballpark  | Kansas City     | KS         |       1000
5 | Gillette Stadium           | Foxborough      | MA         |       1000
6 | New York Giants Stadium    | East Rutherford | NJ         |       1000
7 | BMO Field                  | Toronto         | ON         |       1000
8 | The Home Depot Center      | Carson          | CA         |       1000
9 | Dick's Sporting Goods Park | Commerce City   | CO         |       1000
10 | Pizza Hut Park             | Frisco          | TX         |       1000
(10 rows)

Untuk contoh berikut, selain mengasumsikan bahwa tidak ada data VENUESEATS yang disertakan dalam file, asumsikan juga bahwa tidak ada data VENUENAME yang disertakan: 

1||Bridgeview|IL|
2||Columbus|OH|
3||Washington|DC|
4||Kansas City|KS|
5||Foxborough|MA|
6||East Rutherford|NJ|
7||Toronto|ON|
8||Carson|CA|
9||Commerce City|CO|
10||Frisco|TX|

Menggunakan definisi tabel yang sama, pernyataan COPY berikut gagal karena tidak ada nilai DEFAULT yang ditentukan untuk VENUENAME, dan VENUENAME adalah kolom NOT NULL: 

copy venue(venueid, venuecity, venuestate) 
from 's3://mybucket/data/venue_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|';

Sekarang pertimbangkan variasi tabel VENUE yang menggunakan kolom IDENTITY: 

create table venue_identity(
venueid int identity(1,1),
venuename varchar(100) not null,
venuecity varchar(30),
venuestate char(2),
venueseats integer not null default '1000');

Seperti contoh sebelumnya, asumsikan bahwa kolom VENUESEATS tidak memiliki nilai yang sesuai dalam file sumber. Pernyataan COPY berikut berhasil memuat tabel, termasuk nilai data IDENTITAS yang telah ditentukan sebelumnya, bukan membuat nilai tersebut secara otomatis: 

copy venue(venueid, venuename, venuecity, venuestate) 
from 's3://mybucket/data/venue_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|' explicit_ids;

Pernyataan ini gagal karena tidak menyertakan kolom IDENTITY (VENUEID hilang dari daftar kolom) namun menyertakan parameter EXPLICIT_IDS: 

copy venue(venuename, venuecity, venuestate) 
from 's3://mybucket/data/venue_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|' explicit_ids;

Pernyataan ini gagal karena tidak menyertakan parameter EXPLICIT_IDS: 

copy venue(venueid, venuename, venuecity, venuestate)
from 's3://mybucket/data/venue_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|';

SALIN data dengan opsi ESCAPE

Contoh berikut menunjukkan cara memuat karakter yang cocok dengan karakter pembatas (dalam hal ini, karakter pipa). Dalam file input, pastikan bahwa semua karakter pipa (|) yang ingin Anda muat lolos dengan karakter garis miring terbalik (\). Kemudian muat file dengan parameter ESCAPE. 

$ more redshiftinfo.txt
1|public\|event\|dwuser
2|public\|sales\|dwuser

create table redshiftinfo(infoid int,tableinfo varchar(50));

copy redshiftinfo from 's3://mybucket/data/redshiftinfo.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
delimiter '|' escape;

select * from redshiftinfo order by 1;
infoid |       tableinfo
-------+--------------------
1      | public|event|dwuser
2      | public|sales|dwuser
(2 rows)

Tanpa parameter ESCAPE, perintah COPY ini gagal dengan Extra column(s) found kesalahan.

penting

Jika Anda memuat data Anda menggunakan COPY dengan parameter ESCAPE, Anda juga harus menentukan parameter ESCAPE dengan perintah UNLOAD Anda untuk menghasilkan file output timbal balik. Demikian pula, jika Anda BONGKAR menggunakan parameter ESCAPE, Anda perlu menggunakan ESCAPE saat Anda MENYALIN data yang sama.

Salin dari contoh JSON

Dalam contoh berikut, Anda memuat tabel CATEGORY dengan data berikut.

CATID KELOMPOK KUCING NAMA KUCING CATDESC
1 Olahraga MLB Major League Baseball
2 Olahraga NHL Liga Hoki Nasional
3 Olahraga NFL Liga Sepak Bola Nasional
4 Olahraga NBA Asosiasi Bola Basket Nasional
5 Konser Klasik Semua konser simfoni, konser, dan paduan suara

Muat dari data JSON menggunakan opsi 'auto'

Untuk memuat dari data JSON menggunakan 'auto' opsi, data JSON harus terdiri dari satu set objek. Nama kunci harus cocok dengan nama kolom, tetapi urutannya tidak masalah. Berikut ini menunjukkan isi dari sebuah file bernamacategory_object_auto.json.

{
    "catdesc": "Major League Baseball",
    "catid": 1,
    "catgroup": "Sports",
    "catname": "MLB"
}
{
    "catgroup": "Sports",
    "catid": 2,
    "catname": "NHL",
    "catdesc": "National Hockey League"
}{
    "catid": 3,
    "catname": "NFL",
    "catgroup": "Sports",
    "catdesc": "National Football League"
}
{
    "bogus": "Bogus Sports LLC",
    "catid": 4,
    "catgroup": "Sports",
    "catname": "NBA",
    "catdesc": "National Basketball Association"
}
{
    "catid": 5,
    "catgroup": "Shows",
    "catname": "Musicals",
    "catdesc": "All symphony, concerto, and choir concerts"
}

Untuk memuat dari file data JSON dalam contoh sebelumnya, jalankan perintah COPY berikut.

copy category
from 's3://mybucket/category_object_auto.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 'auto';

Muat dari data JSON menggunakan opsi 'auto ignorecase'

Untuk memuat dari data JSON menggunakan 'auto ignorecase' opsi, data JSON harus terdiri dari satu set objek. Kasus nama kunci tidak harus cocok dengan nama kolom dan urutannya tidak masalah. Berikut ini menunjukkan isi dari sebuah file bernamacategory_object_auto-ignorecase.json.

{
    "CatDesc": "Major League Baseball",
    "CatID": 1,
    "CatGroup": "Sports",
    "CatName": "MLB"
}
{
    "CatGroup": "Sports",
    "CatID": 2,
    "CatName": "NHL",
    "CatDesc": "National Hockey League"
}{
    "CatID": 3,
    "CatName": "NFL",
    "CatGroup": "Sports",
    "CatDesc": "National Football League"
}
{
    "bogus": "Bogus Sports LLC",
    "CatID": 4,
    "CatGroup": "Sports",
    "CatName": "NBA",
    "CatDesc": "National Basketball Association"
}
{
    "CatID": 5,
    "CatGroup": "Shows",
    "CatName": "Musicals",
    "CatDesc": "All symphony, concerto, and choir concerts"
}

Untuk memuat dari file data JSON dalam contoh sebelumnya, jalankan perintah COPY berikut.

copy category
from 's3://mybucket/category_object_auto ignorecase.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 'auto ignorecase';

Muat dari data JSON menggunakan file JSONPaths

Jika objek data JSON tidak sesuai langsung dengan nama kolom, Anda dapat menggunakan file JSONPaths untuk memetakan elemen JSON ke kolom. Urutan tidak masalah dalam data sumber JSON, tetapi urutan ekspresi file JSONPaths harus cocok dengan urutan kolom. Misalkan Anda memiliki file data berikut, bernamacategory_object_paths.json.

{
    "one": 1,
    "two": "Sports",
    "three": "MLB",
    "four": "Major League Baseball"
}
{
    "three": "NHL",
    "four": "National Hockey League",
    "one": 2,
    "two": "Sports"
}
{
    "two": "Sports",
    "three": "NFL",
    "one": 3,
    "four": "National Football League"
}
{
    "one": 4,
    "two": "Sports",
    "three": "NBA",
    "four": "National Basketball Association"
}
{
    "one": 6,
    "two": "Shows",
    "three": "Musicals",
    "four": "All symphony, concerto, and choir concerts"
}

File JSONPaths berikut, bernamacategory_jsonpath.json, memetakan data sumber ke kolom tabel.

{
    "jsonpaths": [
        "$['one']",
        "$['two']",
        "$['three']",
        "$['four']"
    ]
}

Untuk memuat dari file data JSON dalam contoh sebelumnya, jalankan perintah COPY berikut.

copy category
from 's3://mybucket/category_object_paths.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 's3://mybucket/category_jsonpath.json';

Muat dari array JSON menggunakan file JSONPaths

Untuk memuat dari data JSON yang terdiri dari sekumpulan array, Anda harus menggunakan file JSONPaths untuk memetakan elemen array ke kolom. Misalkan Anda memiliki file data berikut, bernamacategory_array_data.json.

[1,"Sports","MLB","Major League Baseball"]
[2,"Sports","NHL","National Hockey League"]
[3,"Sports","NFL","National Football League"]
[4,"Sports","NBA","National Basketball Association"]
[5,"Concerts","Classical","All symphony, concerto, and choir concerts"]

File JSONPaths berikut, bernamacategory_array_jsonpath.json, memetakan data sumber ke kolom tabel.

{
    "jsonpaths": [
        "$[0]",
        "$[1]",
        "$[2]",
        "$[3]"
    ]
}

Untuk memuat dari file data JSON dalam contoh sebelumnya, jalankan perintah COPY berikut.

copy category
from 's3://mybucket/category_array_data.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 's3://mybucket/category_array_jsonpath.json';

Salin dari contoh Avro

Dalam contoh berikut, Anda memuat tabel CATEGORY dengan data berikut.

CATID KELOMPOK KUCING NAMA KUCING CATDESC
1 Olahraga MLB Major League Baseball
2 Olahraga NHL Liga Hoki Nasional
3 Olahraga NFL Liga Sepak Bola Nasional
4 Olahraga NBA Asosiasi Bola Basket Nasional
5 Konser Klasik Semua konser simfoni, konser, dan paduan suara

Muat dari data Avro menggunakan opsi 'auto'

Untuk memuat dari data Avro menggunakan 'auto' argumen, nama bidang dalam skema Avro harus cocok dengan nama kolom. Saat menggunakan 'auto' argumen, urutan tidak masalah. Berikut ini menunjukkan skema untuk file bernamacategory_auto.avro.

{
    "name": "category",
    "type": "record",
    "fields": [
        {"name": "catid", "type": "int"},
        {"name": "catdesc", "type": "string"},
        {"name": "catname", "type": "string"},
        {"name": "catgroup", "type": "string"},
}

Data dalam file Avro dalam format biner, jadi tidak dapat dibaca manusia. Berikut ini menunjukkan representasi JSON dari data dalam category_auto.avro file. 

{
   "catid": 1,
   "catdesc": "Major League Baseball",
   "catname": "MLB",
   "catgroup": "Sports"
}
{
   "catid": 2,
   "catdesc": "National Hockey League",
   "catname": "NHL",
   "catgroup": "Sports"
}
{
   "catid": 3,
   "catdesc": "National Basketball Association",
   "catname": "NBA",
   "catgroup": "Sports"
}
{
   "catid": 4,
   "catdesc": "All symphony, concerto, and choir concerts",
   "catname": "Classical",
   "catgroup": "Concerts"
}

Untuk memuat dari file data Avro pada contoh sebelumnya, jalankan perintah COPY berikut.

copy category
from 's3://mybucket/category_auto.avro'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as avro 'auto';

Muat dari data Avro menggunakan opsi 'auto ignorecase'

Untuk memuat dari data Avro menggunakan 'auto ignorecase' argumen, kasus nama bidang dalam skema Avro tidak harus cocok dengan kasus nama kolom. Saat menggunakan 'auto ignorecase' argumen, urutan tidak masalah. Berikut ini menunjukkan skema untuk file bernamacategory_auto-ignorecase.avro.

{
    "name": "category",
    "type": "record",
    "fields": [
        {"name": "CatID", "type": "int"},
        {"name": "CatDesc", "type": "string"},
        {"name": "CatName", "type": "string"},
        {"name": "CatGroup", "type": "string"},
}

Data dalam file Avro dalam format biner, jadi tidak dapat dibaca manusia. Berikut ini menunjukkan representasi JSON dari data dalam category_auto-ignorecase.avro file. 

{
   "CatID": 1,
   "CatDesc": "Major League Baseball",
   "CatName": "MLB",
   "CatGroup": "Sports"
}
{
   "CatID": 2,
   "CatDesc": "National Hockey League",
   "CatName": "NHL",
   "CatGroup": "Sports"
}
{
   "CatID": 3,
   "CatDesc": "National Basketball Association",
   "CatName": "NBA",
   "CatGroup": "Sports"
}
{
   "CatID": 4,
   "CatDesc": "All symphony, concerto, and choir concerts",
   "CatName": "Classical",
   "CatGroup": "Concerts"
}

Untuk memuat dari file data Avro pada contoh sebelumnya, jalankan perintah COPY berikut.

copy category
from 's3://mybucket/category_auto-ignorecase.avro'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as avro 'auto ignorecase';

Muat dari data Avro menggunakan file JSONPaths

Jika nama bidang dalam skema Avro tidak sesuai langsung dengan nama kolom, Anda dapat menggunakan file JSONPaths untuk memetakan elemen skema ke kolom. Urutan ekspresi file JSONPaths harus sesuai dengan urutan kolom.

Misalkan Anda memiliki file data bernama category_paths.avro yang berisi data yang sama seperti pada contoh sebelumnya, tetapi dengan skema berikut.

{
    "name": "category",
    "type": "record",
    "fields": [
        {"name": "id", "type": "int"},
        {"name": "desc", "type": "string"},
        {"name": "name", "type": "string"},
        {"name": "group", "type": "string"},
        {"name": "region", "type": "string"} 
     ]
}

File JSONPaths berikut, bernamacategory_path.avropath, memetakan data sumber ke kolom tabel.

{
    "jsonpaths": [
        "$['id']",
        "$['group']",
        "$['name']",
        "$['desc']"
    ]
}

Untuk memuat dari file data Avro pada contoh sebelumnya, jalankan perintah COPY berikut.

copy category
from 's3://mybucket/category_object_paths.avro'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
format avro 's3://mybucket/category_path.avropath ';

Mempersiapkan file untuk COPY dengan opsi ESCAPE

Contoh berikut menjelaskan bagaimana Anda menyiapkan data untuk “melarikan diri” karakter baris baru sebelum mengimpor data ke dalam tabel Amazon Redshift menggunakan perintah COPY dengan parameter ESCAPE. Tanpa menyiapkan data untuk membatasi karakter baris baru, Amazon Redshift mengembalikan kesalahan pemuatan saat Anda menjalankan perintah COPY, karena karakter baris baru biasanya digunakan sebagai pemisah catatan.

Misalnya, pertimbangkan file atau kolom dalam tabel eksternal yang ingin Anda salin ke tabel Amazon Redshift. Jika file atau kolom berisi konten berformat XML atau data serupa, Anda perlu memastikan bahwa semua karakter baris baru (\n) yang merupakan bagian dari konten diloloskan dengan karakter garis miring terbalik (\).

File atau tabel yang berisi karakter baris baru yang disematkan memberikan pola yang relatif mudah untuk dicocokkan. Setiap karakter baris baru yang disematkan kemungkinan besar selalu mengikuti > karakter dengan potensi beberapa karakter spasi putih (' 'atau tab) di antaranya, seperti yang dapat Anda lihat dalam contoh file teks bernama nlTest1.txt berikut. 

$ cat nlTest1.txt
<xml start>
<newline characters provide>
<line breaks at the end of each>
<line in content>
</xml>|1000
<xml>
</xml>|2000

Dengan contoh berikut, Anda dapat menjalankan utilitas pemrosesan teks untuk pra-proses file sumber dan menyisipkan karakter escape jika diperlukan. (|Karakter dimaksudkan untuk digunakan sebagai pembatas untuk memisahkan data kolom saat disalin ke tabel Amazon Redshift.) 

$ sed -e ':a;N;$!ba;s/>[[:space:]]*\n/>\\\n/g' nlTest1.txt > nlTest2.txt

Demikian pula, Anda dapat menggunakan Perl untuk melakukan operasi serupa: 

cat nlTest1.txt | perl -p -e 's/>\s*\n/>\\\n/g' > nlTest2.txt

Untuk mengakomodasi pemuatan data dari nlTest2.txt file ke Amazon Redshift, kami membuat tabel dua kolom di Amazon Redshift. Kolom pertama c1, adalah kolom karakter yang menyimpan konten berformat XML dari file. nlTest2.txt Kolom kedua c2 memegang nilai integer dimuat dari file yang sama.

Setelah menjalankan sed perintah, Anda dapat memuat data dengan benar dari nlTest2.txt file ke tabel Amazon Redshift menggunakan parameter ESCAPE.

catatan

Ketika Anda menyertakan parameter ESCAPE dengan perintah COPY, ia lolos dari sejumlah karakter khusus yang menyertakan karakter garis miring terbalik (termasuk baris baru). 

copy t2 from 's3://mybucket/data/nlTest2.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'  
escape
delimiter as '|';

select * from t2 order by 2;

c1           |  c2
-------------+------
<xml start>
<newline characters provide>
<line breaks at the end of each>
<line in content>
</xml>
| 1000
<xml>
</xml>       | 2000
(2 rows)

Anda dapat menyiapkan file data yang diekspor dari database eksternal dengan cara yang sama. Misalnya, dengan database Oracle, Anda dapat menggunakan fungsi REPLACE pada setiap kolom yang terpengaruh dalam tabel yang ingin Anda salin ke Amazon Redshift. 

SELECT c1, REPLACE(c2, \n',\\n' ) as c2 from my_table_with_xml

Selain itu, banyak alat ekspor dan ekstrak basis data, transformasi, muat (ETL) yang secara rutin memproses sejumlah besar data menyediakan opsi untuk menentukan karakter escape dan delimiter.

Memuat shapefile ke Amazon Redshift

Contoh berikut menunjukkan cara memuat shapefile Esri menggunakan COPY. Untuk informasi selengkapnya tentang memuat shapefile, lihat. Memuat shapefile ke Amazon Redshift

Memuat shapefile

Langkah-langkah berikut menunjukkan cara menelan OpenStreetMap data dari Amazon S3 menggunakan perintah COPY. Contoh ini mengasumsikan bahwa arsip shapefile Norwegia dari situs unduhan Geofabrik telah diunggah ke bucket Amazon S3 pribadi di Wilayah Anda. AWS .dbfFile.shp,.shx, dan harus berbagi awalan dan nama file Amazon S3 yang sama.

Menelan data tanpa penyederhanaan

Perintah berikut membuat tabel dan menelan data yang dapat muat dalam ukuran geometri maksimum tanpa penyederhanaan apa pun. Buka perangkat gis_osm_natural_free_1.shp lunak GIS pilihan Anda dan periksa kolom di lapisan ini. Secara default, kolom IDENTITAS atau GEOMETRI adalah yang pertama. Ketika kolom GEOMETRI pertama, Anda dapat membuat tabel seperti yang ditunjukkan berikut.

CREATE TABLE norway_natural (
   wkb_geometry GEOMETRY,
   osm_id BIGINT,
   code INT,
   fclass VARCHAR,
   name VARCHAR);

Atau, ketika kolom IDENTITY pertama, Anda dapat membuat tabel seperti yang ditunjukkan berikut.

CREATE TABLE norway_natural_with_id (
   fid INT IDENTITY(1,1),
   wkb_geometry GEOMETRY,
   osm_id BIGINT,
   code INT,
   fclass VARCHAR,
   name VARCHAR);

Sekarang Anda dapat menelan data menggunakan COPY.

COPY norway_natural FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
INFO: Load into table 'norway_natural' completed, 83891 record(s) loaded successfully

Atau Anda dapat menelan data seperti yang ditunjukkan berikut. 

COPY norway_natural_with_id FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
INFO: Load into table 'norway_natural_with_id' completed, 83891 record(s) loaded successfully.

Menelan data dengan penyederhanaan

Perintah berikut membuat tabel dan mencoba untuk menelan data yang tidak dapat muat dalam ukuran geometri maksimum tanpa penyederhanaan apa pun. Periksa gis_osm_water_a_free_1.shp shapefile dan buat tabel yang sesuai seperti yang ditunjukkan berikut.

CREATE TABLE norway_water (
   wkb_geometry GEOMETRY,
   osm_id BIGINT,
   code INT,
   fclass VARCHAR,
   name VARCHAR);

Ketika perintah COPY berjalan, itu menghasilkan kesalahan.

COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
ERROR:  Load into table 'norway_water' failed.  Check 'stl_load_errors' system table for details.

Query STL_LOAD_ERRORS menunjukkan bahwa geometri terlalu besar. 

SELECT line_number, btrim(colname), btrim(err_reason) FROM stl_load_errors WHERE query = pg_last_copy_id();
 line_number |    btrim     |                                 btrim
-------------+--------------+-----------------------------------------------------------------------
     1184705 | wkb_geometry | Geometry size: 1513736 is larger than maximum supported size: 1048447

Untuk mengatasinya, SIMPLIFY AUTO parameter ditambahkan ke perintah COPY untuk menyederhanakan geometri.

COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp'
FORMAT SHAPEFILE
SIMPLIFY AUTO
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';

INFO:  Load into table 'norway_water' completed, 1989196 record(s) loaded successfully.

Untuk melihat baris dan geometri yang disederhanakan, kueriSVL_SPATIAL_SIMPLIFY.

SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id();
 query | line_number | maximum_tolerance | initial_size | simplified | final_size |   final_tolerance
-------+-------------+-------------------+--------------+------------+------------+----------------------
    20 |     1184704 |                -1 |      1513736 | t          |    1008808 |   1.276386653895e-05
    20 |     1664115 |                -1 |      1233456 | t          |    1023584 | 6.11707814796635e-06

Menggunakan SIMPLIFY AUTO max_tolerance dengan toleransi yang lebih rendah dari yang dihitung secara otomatis mungkin menghasilkan kesalahan konsumsi. Dalam hal ini, gunakan MAXERROR untuk mengabaikan kesalahan.

COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp'
FORMAT SHAPEFILE
SIMPLIFY AUTO 1.1E-05
MAXERROR 2
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';

INFO:  Load into table 'norway_water' completed, 1989195 record(s) loaded successfully.
INFO:  Load into table 'norway_water' completed, 1 record(s) could not be loaded.  Check 'stl_load_errors' system table for details.

Kueri SVL_SPATIAL_SIMPLIFY lagi untuk mengidentifikasi catatan yang tidak berhasil dimuat COPY.

SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id();
 query | line_number | maximum_tolerance | initial_size | simplified | final_size | final_tolerance
-------+-------------+-------------------+--------------+------------+------------+-----------------
    29 |     1184704 |           1.1e-05 |      1513736 | f          |          0 |               0
    29 |     1664115 |           1.1e-05 |      1233456 | t          |     794432 |         1.1e-05

Dalam contoh ini, rekaman pertama tidak berhasil cocok, sehingga simplified kolom menunjukkan false. Rekor kedua dimuat dalam toleransi yang diberikan. Namun, ukuran akhir lebih besar daripada menggunakan toleransi yang dihitung secara otomatis tanpa menentukan toleransi maksimum.

Memuat dari shapefile terkompresi

Amazon Redshift COPY mendukung pengambilan data dari shapefile terkompresi. Semua komponen shapefile harus memiliki awalan Amazon S3 yang sama dan akhiran kompresi yang sama. Sebagai contoh, misalkan Anda ingin memuat data dari contoh sebelumnya. Dalam hal ini, filegis_osm_water_a_free_1.shp.gz,gis_osm_water_a_free_1.dbf.gz, dan gis_osm_water_a_free_1.shx.gz harus berbagi direktori Amazon S3 yang sama. Perintah COPY memerlukan opsi GZIP, dan klausa FROM harus menentukan file terkompresi yang benar, seperti yang ditunjukkan berikut.

COPY norway_natural FROM 's3://bucket_name/shapefiles/norway/compressed/gis_osm_natural_free_1.shp.gz'
FORMAT SHAPEFILE
GZIP
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
INFO:  Load into table 'norway_natural' completed, 83891 record(s) loaded successfully.

Memuat data ke dalam tabel dengan urutan kolom yang berbeda

Jika Anda memiliki tabel yang tidak memiliki GEOMETRY kolom pertama, Anda dapat menggunakan pemetaan kolom untuk memetakan kolom ke tabel target. Misalnya, buat tabel dengan osm_id ditentukan sebagai kolom pertama.

CREATE TABLE norway_natural_order (
   osm_id BIGINT,
   wkb_geometry GEOMETRY,
   code INT,
   fclass VARCHAR,
   name VARCHAR);

Kemudian menelan shapefile menggunakan pemetaan kolom.

COPY norway_natural_order(wkb_geometry, osm_id, code, fclass, name) 
FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
INFO:  Load into table 'norway_natural_order' completed, 83891 record(s) loaded successfully.

Memuat data ke dalam tabel dengan kolom geografi

Jika Anda memiliki tabel yang memiliki GEOGRAPHY kolom, pertama-tama Anda menelan ke dalam GEOMETRY kolom dan kemudian melemparkan objek ke GEOGRAPHY objek. Misalnya, setelah Anda menyalin shapefile Anda ke GEOMETRY kolom, ubah tabel untuk menambahkan kolom tipe data. GEOGRAPHY

ALTER TABLE norway_natural ADD COLUMN wkb_geography GEOGRAPHY;

Kemudian ubah geometri menjadi geografi.

UPDATE norway_natural SET wkb_geography = wkb_geometry::geography;

Secara opsional, Anda dapat menjatuhkan GEOMETRY kolom.

ALTER TABLE norway_natural DROP COLUMN wkb_geometry;

COPY perintah dengan opsi NOLOAD

Untuk memvalidasi file data sebelum Anda benar-benar memuat data, gunakan opsi NOLOAD dengan perintah COPY. Amazon Redshift mem-parsing file input dan menampilkan kesalahan apa pun yang terjadi. Contoh berikut menggunakan opsi NOLOAD dan tidak ada baris yang benar-benar dimuat ke dalam tabel.

COPY public.zipcode1
FROM 's3://mybucket/mydata/zipcode.csv' 
DELIMITER ';' 
IGNOREHEADER 1 REGION 'us-east-1'
NOLOAD
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/myRedshiftRole';

Warnings:
Load into table 'zipcode1' completed, 0 record(s) loaded successfully.