Bagikan melalui

Menyimpan dokumen JSON di SQL Server atau SQL Database

  • Artikel

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

Mesin SQL Database menyediakan fungsi JSON asli yang memungkinkan Anda mengurai dokumen JSON menggunakan bahasa SQL standar. Anda dapat menyimpan dokumen JSON di SQL Server atau SQL Database dan mengkueri data JSON seperti dalam database NoSQL. Artikel ini menjelaskan opsi untuk menyimpan dokumen JSON.

Format penyimpanan JSON

Keputusan desain penyimpanan pertama adalah cara menyimpan dokumen JSON dalam tabel. Ada dua opsi yang tersedia:

  • Penyimpanan LOB - Dokumen JSON dapat disimpan apa adanya dalam kolom dengan jenis data json atau nvarchar. Ini adalah cara terbaik untuk pemuatan dan penyerapan data cepat karena kecepatan pemuatan cocok dengan kecepatan pemuatan kolom string. Pendekatan ini mungkin memperkenalkan penalti performa tambahan pada waktu kueri/analisis jika pengindeksan pada nilai JSON tidak dilakukan, karena dokumen JSON mentah harus diurai saat kueri berjalan.

  • Penyimpanan relasional - Dokumen JSON dapat diurai saat disisipkan dalam tabel menggunakan OPENJSON, JSON_VALUE atau JSON_QUERY fungsi. Fragmen dari dokumen JSON input dapat disimpan di kolom yang berisi sub-elemen JSON dengan jenis data json atau nvarchar. Pendekatan ini meningkatkan waktu muat karena penguraian JSON dilakukan selama beban; namun, kueri cocok dengan performa kueri klasik pada data relasional.

  • Saat ini di SQL Server, JSON bukan jenis data bawaan.

  • Saat ini, jenis data JSON tersedia di Azure SQL Database.

Tabel klasik

Cara paling sederhana untuk menyimpan dokumen JSON di SQL Server atau Azure SQL Database adalah dengan membuat tabel dua kolom yang berisi ID dokumen dan konten dokumen. Contohnya:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max)
);

Atau, jika didukung:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] json
);

Struktur ini setara dengan koleksi yang dapat Anda temukan di database dokumen klasik. Kunci _id primer adalah nilai peningkatan otomatis yang menyediakan pengidentifikasi unik untuk setiap dokumen dan memungkinkan pencarian cepat. Struktur ini adalah pilihan yang baik untuk skenario NoSQL klasik di mana Anda ingin mengambil dokumen berdasarkan ID atau memperbarui dokumen yang disimpan berdasarkan ID.

  • Gunakan jenis data json asli di mana tersedia untuk menyimpan dokumen JSON.
  • Jenis data nvarchar(max) memungkinkan Anda menyimpan dokumen JSON yang berukuran hingga 2 GB. Namun, jika Anda yakin bahwa dokumen JSON Anda tidak lebih besar dari 8 KB, kami sarankan Anda menggunakan nvarchar(4000) alih-alih nvarchar(maks) karena alasan performa.

Tabel sampel yang dibuat dalam contoh sebelumnya mengasumsikan bahwa dokumen JSON yang valid disimpan di log kolom . Jika Anda ingin memastikan bahwa JSON yang valid disimpan di log kolom, Anda bisa menambahkan batasan CHECK pada kolom. Contohnya:

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON([log])=1)

Setiap kali seseorang menyisipkan atau memperbarui dokumen dalam tabel, batasan ini memverifikasi bahwa dokumen JSON diformat dengan benar. Tanpa batasan, tabel dioptimalkan untuk sisipan, karena dokumen JSON ditambahkan langsung ke kolom tanpa pemrosesan apa pun.

Saat menyimpan dokumen JSON dalam tabel, Anda bisa menggunakan bahasa Transact-SQL standar untuk mengkueri dokumen. Contohnya:

SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
 FROM WebSite.Logs
 WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
 GROUP BY JSON_VALUE([log], '$.severity')
 HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
 ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC

Ini adalah keuntungan kuat bahwa Anda dapat menggunakan fungsi T-SQL dan klausa kueri apa pun untuk mengkueri dokumen JSON. SQL Server dan SQL Database tidak memperkenalkan batasan apa pun dalam kueri yang dapat Anda gunakan untuk menganalisis dokumen JSON. Anda dapat mengekstrak nilai dari dokumen JSON dengan JSON_VALUE fungsi dan menggunakannya dalam kueri seperti nilai lainnya.

Kemampuan untuk menggunakan sintaks kueri T-SQL yang kaya ini adalah perbedaan utama antara SQL Server dan SQL Database dan database NoSQL klasik - dalam Transact-SQL Anda mungkin memiliki fungsi apa pun yang Anda butuhkan untuk memproses data JSON.

Indeks

Jika Anda mengetahui bahwa kueri Anda sering mencari dokumen oleh beberapa properti (misalnya, severity properti dalam dokumen JSON), Anda dapat menambahkan indeks nonclustered rowstore pada properti untuk mempercepat kueri.

Anda dapat membuat kolom komputasi yang mengekspos nilai JSON dari kolom JSON pada jalur yang ditentukan (yaitu, pada jalur $.severity) dan membuat indeks standar pada kolom komputasi ini. Contohnya:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max),
    [severity] AS JSON_VALUE([log], '$.severity'),
    index ix_severity (severity)
);

Kolom komputasi yang digunakan dalam contoh ini adalah kolom yang tidak bertahan atau virtual yang tidak menambahkan ruang tambahan ke tabel. Ini digunakan oleh indeks ix_severity untuk meningkatkan performa kueri seperti contoh berikut:

SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'

Salah satu karakteristik penting dari indeks ini adalah bahwa indeks ini sadar kolatasi. Jika kolom nvarchar asli Anda memiliki COLLATION properti (misalnya, sensitivitas huruf besar/kecil atau bahasa Jepang), indeks diatur sesuai dengan aturan bahasa atau aturan sensitivitas huruf besar/kecil yang terkait dengan kolom nvarchar. Kesadaran kolase ini mungkin merupakan fitur penting jika Anda mengembangkan aplikasi untuk pasar global yang perlu menggunakan aturan bahasa kustom saat memproses dokumen JSON.

Tabel besar & format penyimpan kolom

Jika Anda berharap memiliki sejumlah besar dokumen JSON dalam koleksi Anda, sebaiknya tambahkan indeks penyimpan kolom berkluster pada koleksi, seperti yang ditunjukkan dalam contoh berikut:

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    [_id] bigint default(next value for WebSite.LogID),
    [log] json,
    INDEX cci CLUSTERED COLUMNSTORE
);

Indeks penyimpan kolom berkluster menyediakan kompresi data tinggi (hingga 25x) yang dapat secara signifikan mengurangi persyaratan ruang penyimpanan Anda, menurunkan biaya penyimpanan, dan meningkatkan performa I/O beban kerja Anda. Selain itu, indeks penyimpan kolom berkluster dioptimalkan untuk pemindaian tabel dan analitik pada dokumen JSON Anda, sehingga jenis indeks ini mungkin menjadi opsi terbaik untuk analitik log.

Contoh sebelumnya menggunakan objek urutan untuk menetapkan nilai ke _id kolom. Urutan dan identitas adalah opsi yang valid untuk kolom ID.

Dokumen yang sering diubah & tabel yang dioptimalkan memori

Jika Anda mengharapkan sejumlah besar operasi pembaruan, sisipkan, dan hapus di koleksi Anda, Anda dapat menyimpan dokumen JSON Anda dalam tabel yang dioptimalkan memori. Koleksi JSON yang dioptimalkan memori selalu menyimpan data dalam memori, sehingga tidak ada overhead I/O penyimpanan. Selain itu, koleksi JSON yang dioptimalkan memori benar-benar bebas kunci - yaitu, tindakan pada dokumen tidak memblokir operasi lain.

Satu-satunya hal yang harus Anda lakukan mengonversi koleksi klasik ke koleksi yang dioptimalkan memori adalah menentukan WITH (MEMORY_OPTIMIZED=ON) opsi setelah definisi tabel, seperti yang ditunjukkan dalam contoh berikut. Kemudian Anda memiliki versi koleksi JSON yang dioptimalkan memori.

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json
) WITH (MEMORY_OPTIMIZED=ON)

Tabel yang dioptimalkan memori adalah opsi terbaik untuk dokumen yang sering diubah. Saat Anda mempertimbangkan tabel yang dioptimalkan memori, pertimbangkan juga performa. Gunakan jenis data nvarchar(4000) alih-alih nvarchar(max) untuk dokumen JSON dalam koleksi yang dioptimalkan memori Anda, jika memungkinkan, karena mungkin secara drastis meningkatkan performa. Jenis data json tidak didukung dengan tabel yang dioptimalkan memori.

Seperti halnya tabel klasik, Anda dapat menambahkan indeks pada bidang yang Anda ekspos dalam tabel yang dioptimalkan memori dengan menggunakan kolom komputasi. Contohnya:

CREATE TABLE WebSite.Logs (

  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json,

  [severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
  INDEX ix_severity (severity)

) WITH (MEMORY_OPTIMIZED=ON)

Untuk memaksimalkan performa, berikan nilai JSON ke jenis sekecil mungkin yang dapat digunakan untuk menahan nilai properti. Dalam contoh sebelumnya, tinyint digunakan.

Anda juga dapat menempatkan kueri SQL yang memperbarui dokumen JSON dalam prosedur tersimpan untuk mendapatkan manfaat kompilasi asli. Contohnya:

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION

AS BEGIN
    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

    UPDATE WebSite.Logs
    SET [log] = JSON_MODIFY([log], @Property, @Value)
    WHERE _id = @Id;

END

Prosedur yang dikompilasi secara asli ini mengambil kueri dan membuat kode .DLL yang menjalankan kueri. Prosedur yang dikompilasi secara asli adalah pendekatan yang lebih cepat untuk mengkueri dan memperbarui data.

Kesimpulan

Fungsi JSON asli di SQL Server dan SQL Database memungkinkan Anda memproses dokumen JSON seperti dalam database NoSQL. Setiap database - relasional atau NoSQL - memiliki beberapa pro dan kontra untuk pemrosesan data JSON. Manfaat utama menyimpan dokumen JSON di SQL Server atau SQL Database adalah dukungan bahasa SQL penuh. Anda dapat menggunakan bahasa Transact-SQL yang kaya untuk memproses data dan mengonfigurasi berbagai opsi penyimpanan, dari indeks penyimpan kolom untuk kompresi tinggi dan analitik cepat, hingga tabel yang dioptimalkan memori untuk pemrosesan bebas kunci. Pada saat yang sama, Anda mendapatkan manfaat fitur keamanan dan internasionalisasi yang matang yang dapat Anda gunakan kembali dengan mudah dalam skenario NoSQL Anda. Alasan yang dijelaskan dalam artikel ini adalah alasan yang sangat baik untuk mempertimbangkan untuk menyimpan dokumen JSON di SQL Server atau SQL Database.

Pelajari selengkapnya tentang JSON di SQL Server dan Azure SQL Database

Untuk pengenalan visual dukungan JSON bawaan di SQL Server dan Azure SQL Database, lihat video berikut ini:

Konten terkait