Mengimpor dokumen JSON ke SQL Server

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL DatabaseAzure SQL Managed Instance yang lebih baru

Artikel ini menjelaskan cara mengimpor file JSON ke SQL Server. Dokumen JSON menyimpan banyak jenis data, misalnya, log aplikasi, data sensor, dan sebagainya. Penting untuk dapat membaca data JSON yang disimpan dalam file, memuat data ke SQL Server, dan menganalisisnya.

Izin

Pada tingkat instans, fitur ini memerlukan keanggotaan peran server tetap bulkadmin , atau mengelola izin OPERASI MASSAL.

Untuk tingkat database, fitur ini memerlukan izin ADMINISTER DATABASE BULK OPERATIONS.

Mengakses Azure Blob Storage memerlukan akses baca-tulis.

Mengimpor dokumen JSON ke dalam satu kolom

OPENROWSET(BULK) adalah fungsi bernilai tabel yang dapat membaca data dari file apa pun di drive atau jaringan lokal, jika SQL Server memiliki akses baca ke lokasi tersebut. Ini mengembalikan tabel dengan satu kolom yang berisi konten file. Ada berbagai opsi yang dapat Anda gunakan dengan OPENROWSET(BULK) fungsi , seperti pemisah. Tetapi dalam kasus paling sederhana, Anda hanya dapat memuat seluruh konten file sebagai nilai teks. (Nilai besar tunggal ini dikenal sebagai objek besar karakter tunggal, atau SINGLE_CLOB.)

Berikut adalah contoh OPENROWSET(BULK) fungsi yang membaca konten file JSON dan mengembalikannya kepada pengguna sebagai nilai tunggal:

SELECT BulkColumn
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j;

OPENJSON(BULK) membaca konten file dan mengembalikannya di BulkColumn.

Anda juga dapat memuat konten file ke dalam variabel lokal atau ke dalam tabel, seperti yang ditunjukkan dalam contoh berikut:

-- Load file contents into a variable
DECLARE @json NVARCHAR(MAX);
SELECT @json = BulkColumn
 FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

-- Load file contents into a table
SELECT BulkColumn
INTO #temp
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

Setelah memuat konten file JSON, Anda dapat menyimpan teks JSON dalam tabel.

Mengimpor dokumen JSON dari Azure File Storage

Anda juga dapat menggunakan OPENROWSET(BULK) seperti yang dijelaskan di atas untuk membaca file JSON dari lokasi file lain yang dapat diakses SQL Server. Misalnya, Azure File Storage mendukung protokol SMB. Akibatnya Anda dapat memetakan drive virtual lokal ke berbagi penyimpanan File Azure menggunakan prosedur berikut:

  1. Buat akun penyimpanan file (misalnya, mystorage), berbagi file (misalnya, sharejson), dan folder di Azure File Storage dengan menggunakan portal Azure atau Azure PowerShell.

  2. Unggah beberapa file JSON ke berbagi penyimpanan file.

  3. Buat aturan firewall keluar di Windows Firewall pada komputer Anda yang memperbolehkan port 445. Penyedia layanan Internet Anda mungkin memblokir port ini. Jika Anda mendapatkan kesalahan DNS (kesalahan 53) di langkah berikut, maka Anda belum membuka port 445, atau ISP Anda memblokirnya.

  4. Pasang berbagi Azure File Storage sebagai drive lokal (misalnya T:).

    Berikut adalah sintaks perintah:

    net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]

    Berikut adalah contoh yang menetapkan huruf T: drive lokal ke berbagi Azure File Storage:

    net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==

    Anda dapat menemukan kunci akun penyimpanan dan kunci akses akun penyimpanan primer atau sekunder di bagian Kunci Pengaturan di portal Azure.

  5. Sekarang Anda dapat mengakses file JSON dari berbagi Azure File Storage dengan menggunakan drive yang dipetakan, seperti yang ditunjukkan dalam contoh berikut:

    SELECT book.*
FROM OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book

Untuk informasi selengkapnya tentang Azure File Storage, lihat Penyimpanan file.

Mengimpor dokumen JSON dari Azure Blob Storage

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL

Anda dapat memuat file langsung ke Azure SQL Database dari Azure Blob Storage dengan perintah T-SQL BULK INSERT atau OPENROWSET fungsi .

Pertama, buat sumber data eksternal, seperti yang ditunjukkan dalam contoh berikut.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
    CREDENTIAL = MyAzureBlobStorageCredential
);

Selanjutnya, jalankan perintah INSERT MASSAL dengan opsi DATA_SOURCE.

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

Mengurai dokumen JSON ke dalam baris dan kolom

Alih-alih membaca seluruh file JSON sebagai nilai tunggal, Anda mungkin ingin mengurainya dan mengembalikan buku dalam file dan propertinya dalam baris dan kolom. Contoh berikut menggunakan file JSON dari situs ini yang berisi daftar buku.

Contoh 1

Dalam contoh paling sederhana, Anda hanya dapat memuat seluruh daftar dari file.

SELECT value
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn);

Sebelumnya OPENROWSET membaca satu nilai teks dari file. OPENROWSET mengembalikan nilai sebagai BulkColumn, dan meneruskan BulkColumn ke OPENJSON fungsi. OPENJSON melakukan iterasi melalui array objek JSON dalam array BulkColumn, dan mengembalikan satu buku di setiap baris. Setiap baris diformat sebagai JSON, diperlihatkan berikutnya.

{"id":"978-0641723445", "cat":["book","hardcover"], "name":"The Lightning Thief", ... }
{"id":"978-1423103349", "cat":["book","paperback"], "name":"The Sea of Monsters", ... }
{"id":"978-1857995879", "cat":["book","paperback"], "name":"Sophie's World : The Greek", ... }
{"id":"978-1933988177", "cat":["book","paperback"], "name":"Lucene in Action, Second", ... }

Contoh 2

Fungsi ini OPENJSON dapat mengurai konten JSON dan mengubahnya menjadi tabel atau kumpulan hasil. Contoh berikut memuat konten, mengurai JSON yang dimuat, dan mengembalikan lima bidang sebagai kolom:

SELECT book.*
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book;

Dalam contoh ini, OPENROWSET(BULK) membaca konten file dan meneruskan konten tersebut OPENJSON ke fungsi dengan skema yang ditentukan untuk output. OPENJSON mencocokkan properti dalam objek JSON dengan menggunakan nama kolom. Misalnya, price properti dikembalikan sebagai price kolom dan dikonversi ke jenis data float. Berikut hasilnya:

Id Nama harga pages_i Penulis
978-0641723445 Pencuri Petir 12.5 384 Rick Riordan
978-1423103349 Laut monster 6.49 304 Rick Riordan
978-1857995879 Dunia Sophie : Filsuf Yunani 3.07 64 Jostein Gaarder
978-1933988177 Lucene in Action, Edisi Kedua 30.5 475 Michael McCandless

Sekarang Anda dapat mengembalikan tabel ini kepada pengguna, atau memuat data ke tabel lain.

Baca juga