Memuat data semi-terstruktur ke Amazon Redshift

PDFRSS

Gunakan tipe SUPER data untuk mempertahankan dan menanyakan data hierarkis dan generik di Amazon Redshift. Amazon Redshift memperkenalkan json_parse fungsi untuk mengurai data dalam JSON format dan mengubahnya menjadi representasi. SUPER Amazon Redshift juga mendukung pemuatan SUPER kolom menggunakan perintah. COPY Format file yang didukung adalahJSON, Avro, teks, format value (CSV) dipisahkan koma, Parket, dan format file. ORC

Untuk informasi tentang tabel yang digunakan dalam contoh berikut, lihatSUPERsampel dataset.

Untuk informasi tentang json_parse fungsi, lihatFungsi JSON_PARSE.

Pengkodean default untuk tipe SUPER data adalahZSTD.

Mengurai JSON dokumen ke kolom SUPER

Anda dapat menyisipkan atau memperbarui JSON data ke dalam SUPER kolom menggunakan json_parse fungsi. Fungsi mem-parsing data dalam JSON format dan mengubahnya menjadi tipe SUPER data, yang dapat Anda gunakan dalam INSERT atau UPDATE pernyataan.

Contoh berikut menyisipkan JSON data ke dalam SUPER kolom. Jika json_parse fungsi tidak ada dalam kueri, Amazon Redshift memperlakukan nilai sebagai string tunggal, bukan string JSON berformat -yang harus diuraikan.

Jika Anda memperbarui kolom SUPER data, Amazon Redshift memerlukan dokumen lengkap untuk diteruskan ke nilai kolom. Amazon Redshift tidak mendukung pembaruan sebagian.

INSERT INTO region_nations VALUES(0,
   'lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to',
   'AFRICA',
   JSON_PARSE('{"r_nations":[
      {"n_comment":" haggle. carefully final deposits detect slyly agai",
         "n_nationkey":0,
         "n_name":"ALGERIA"
      },
      {"n_comment":"ven packages wake quickly. regu",
         "n_nationkey":5,
         "n_name":"ETHIOPIA"
      },
      {"n_comment":" pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t",
         "n_nationkey":14,
         "n_name":"KENYA"
      },
      {"n_comment":"rns. blithely bold courts among the closely regular packages use furiously bold platelets?",
         "n_nationkey":15,
         "n_name":"MOROCCO"
      },
      {"n_comment":"s. ironic, unusual asymptotes wake blithely r",
         "n_nationkey":16,
         "n_name":"MOZAMBIQUE"
      }
   ]
}'));

Menggunakan COPY untuk memuat SUPER kolom di Amazon Redshift

Di bagian berikut, Anda dapat mempelajari berbagai cara menggunakan COPY perintah untuk memuat JSON data ke Amazon Redshift.

Menyalin data dari JSON dan Avro

Dengan menggunakan dukungan data semi-terstruktur di Amazon Redshift, Anda dapat memuat JSON dokumen tanpa merobek-robek atribut JSON strukturnya menjadi beberapa kolom.

Amazon Redshift menyediakan dua metode untuk menelan JSON dokumen menggunakanCOPY, bahkan dengan JSON struktur yang sepenuhnya atau sebagian tidak diketahui:

1. Simpan data yang berasal dari JSON dokumen ke dalam kolom SUPER data tunggal menggunakan noshred opsi. Metode ini berguna ketika skema tidak diketahui atau diharapkan berubah. Dengan demikian, metode ini memudahkan untuk menyimpan seluruh tupel dalam satu SUPER kolom.

2. Rusak JSON dokumen menjadi beberapa kolom Amazon Redshift menggunakan opsi orauto. jsonpaths Atribut dapat berupa skalar atau nilai Amazon Redshift. SUPER

Anda dapat menggunakan opsi ini dengan format JSON atau Avro.

Ukuran maksimum untuk JSON objek sebelum merobek-robek adalah 4 MB.

Menyalin JSON dokumen ke dalam kolom SUPER data tunggal

Untuk menyalin JSON dokumen ke dalam kolom SUPER data tunggal, buat tabel dengan kolom SUPER data tunggal.

CREATE TABLE region_nations_noshred (rdata SUPER);

Salin data dari Amazon S3 ke kolom SUPER data tunggal. Untuk menyerap data JSON sumber ke dalam satu kolom SUPER data, tentukan noshred opsi dalam FORMAT JSON klausa.

COPY region_nations_noshred FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'noshred';

Setelah COPY berhasil menelanJSON, tabel Anda memiliki kolom rdata SUPER data yang berisi data dari seluruh JSON objek. Data yang dicerna mempertahankan semua properti JSON hierarki. Namun, daun dikonversi ke jenis skalar Amazon Redshift untuk pemrosesan kueri yang efisien.

Gunakan query berikut untuk mengambil JSON string asli.

SELECT rdata FROM region_nations_noshred;

Ketika Amazon Redshift menghasilkan kolom SUPER data, itu menjadi dapat diakses menggunakan JDBC sebagai string melalui JSON serialisasi. Untuk informasi selengkapnya, lihat Serialisasi kompleks bersarang JSON.

Menyalin JSON dokumen ke beberapa kolom SUPER data

Anda dapat menghancurkan JSON dokumen menjadi beberapa kolom yang dapat berupa kolom SUPER data atau jenis skalar Amazon Redshift. Amazon Redshift menyebarkan bagian JSON objek yang berbeda ke kolom yang berbeda.

CREATE TABLE region_nations
(
 r_regionkey smallint
 ,r_name varchar
 ,r_comment varchar
 ,r_nations super
 );

Untuk menyalin data dari contoh sebelumnya ke dalam tabel, tentukan AUTO opsi dalam FORMAT JSON klausa untuk membagi JSON nilai di beberapa kolom. COPYmencocokkan JSON atribut tingkat atas dengan nama kolom dan memungkinkan nilai bersarang untuk dicerna sebagai SUPER nilai, seperti JSON array dan objek.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

Ketika nama JSON atribut dalam kasus atas dan bawah campuran, tentukan auto ignorecase opsi dalam FORMAT JSON klausa. Untuk informasi selengkapnya tentang COPY perintah, lihatMuat dari data JSON menggunakan opsi 'auto ignorecase'.

Dalam beberapa kasus, ada ketidakcocokan antara nama kolom dan JSON atribut atau atribut untuk memuat bersarang lebih dari satu level. Jika demikian, gunakan jsonpaths file untuk memetakan JSON atribut secara manual ke kolom Amazon Redshift.

CREATE TABLE nations
(
 regionkey smallint
 ,name varchar
 ,comment super
 ,nations super
 ); 

Misalkan Anda ingin memuat data ke tabel di mana nama kolom tidak cocok dengan JSON atribut. Dalam contoh berikut, nations tabel adalah tabel seperti itu. Anda dapat membuat jsonpaths file yang memetakan jalur atribut ke kolom tabel berdasarkan posisinya dalam jsonpaths array.

{"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$.r_comment",
       "$.r_nations
    ]
}

Lokasi jsonpaths file digunakan sebagai argumen untuk FORMATJSON.

COPY nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_jsonpaths.json';

Gunakan kueri berikut untuk mengakses tabel yang menunjukkan penyebaran data ke beberapa kolom. Kolom SUPER data dicetak menggunakan JSON format.

SELECT r_regionkey,r_name,r_comment,r_nations[0].n_nationkey FROM region_nations ORDER BY 1,2,3 LIMIT 1;

Jsonpaths memetakan bidang file dalam kolom JSON dokumen ke tabel. Anda dapat mengekstrak kolom tambahan, seperti distribusi dan kunci pengurutan, sambil tetap memuat dokumen lengkap sebagai SUPER kolom. Kueri berikut memuat dokumen lengkap ke kolom negara. nameKolom adalah kunci sortir dan regionkey kolom adalah kunci distribusi.

CREATE TABLE nations_sorted (
    regionkey smallint,
    name varchar,
    nations super
) DISTKEY(regionkey) SORTKEY(name);

Root jsonpath “$” memetakan ke root dokumen sebagai berikut:

{"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$"
    ]
}

Lokasi file jsonpaths digunakan sebagai argumen untuk. FORMAT JSON

COPY nations_sorted FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_sorted_jsonpaths.json';

Menyalin data dari teks dan CSV

Amazon Redshift mewakili SUPER kolom dalam teks dan CSV format sebagai serial. JSON JSONPemformatan yang valid diperlukan agar SUPER kolom dimuat dengan informasi jenis yang benar. Hapus kutipan objek, array, angka, boolean, dan nilai null. Bungkus nilai string dalam tanda kutip ganda. SUPERkolom menggunakan aturan escape standar untuk teks dan CSV format. SebabCSV, pembatas lolos sesuai dengan standar. CSV Untuk teks, jika pembatas yang dipilih mungkin juga muncul di SUPER bidang, gunakan ESCAPE opsi selama COPY dan. UNLOAD

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/csv/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT CSV;

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/text/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
DELIMITER ','
ESCAPE;

Menyalin data dari Parket format kolom dan ORC

Jika data semi-terstruktur atau bersarang Anda sudah tersedia dalam ORC format Apache Parquet atau Apache, Anda dapat menggunakan COPY perintah untuk menyerap data ke Amazon Redshift.

Struktur tabel Amazon Redshift harus sesuai dengan jumlah kolom dan tipe data kolom Parket atau file. ORC Dengan menentukan SERIALIZETOJSON dalam COPY perintah, Anda dapat memuat semua jenis kolom dalam file yang sejajar dengan SUPER kolom dalam tabel sebagai. SUPER Ini termasuk tipe struktur dan array.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/parquet/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT PARQUET SERIALIZETOJSON;

Contoh berikut menggunakan ORC format.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/orc/region_nation'
IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT ORC SERIALIZETOJSON;

Saat atribut tipe data tanggal atau waktu masukORC, Amazon Redshift mengonversinya menjadi varchar setelah menyandikannya. SUPER