Przechowywanie dokumentów JSON
Dotyczy:
SQL Server 2016 (13.x) i nowszych Azure SQL DatabaseAzure SQL Managed Instance
Aparat bazy danych SQL udostępnia natywne funkcje JSON, które umożliwiają analizowanie dokumentów JSON przy użyciu standardowego języka SQL. Dokumenty JSON można przechowywać w programie SQL Server lub usłudze SQL Database i wykonywać zapytania dotyczące danych JSON w taki sposób, jak w bazie danych NoSQL. W tym artykule opisano opcje przechowywania dokumentów JSON.
Format przechowywania JSON
Pierwszą decyzją projektową dotyczącą sposobu przechowywania dokumentów JSON w tabelach. Dostępne są dwie opcje:
LOB storage - dokumenty JSON mogą być przechowywane as-is w kolumnach z typem danych json lub nvarchar. Jest to najlepszy sposób szybkiego ładowania i pozyskiwania danych, ponieważ szybkość ładowania jest zgodna z szybkością ładowania kolumn ciągów. Takie podejście może spowodować wprowadzenie dodatkowej kary za wydajność w czasie wykonywania zapytań/analizy, jeśli indeksowanie wartości JSON nie jest wykonywane, ponieważ nieprzetworzone dokumenty JSON muszą być analizowane podczas uruchamiania zapytań.
Przechowywanie relacyjne — dokumenty JSON można analizować podczas wstawiania ich do tabeli przy użyciu funkcji
OPENJSON,JSON_VALUElubJSON_QUERY. Fragmenty z wejściowych dokumentów JSON mogą być przechowywane w kolumnach zawierających elementy podrzędne JSON z typami danych json lub nvarchar. Takie podejście zwiększa czas ładowania, ponieważ analizowanie kodu JSON odbywa się podczas ładowania; jednak zapytania są zgodne z wydajnością klasycznych zapytań dotyczących danych relacyjnych.Typ danych JSON jest obecnie w wersji zapoznawczej dla usług Azure SQL Database i Azure SQL Managed Instance (skonfigurowany z Always-up-to-date update policy).
Obecnie w programie SQL Server kod JSON nie jest wbudowanym typem danych.
Tabele klasyczne
Najprostszym sposobem przechowywania dokumentów JSON w programie SQL Server lub usłudze Azure SQL Database jest utworzenie dwukolumna tabeli zawierającej identyfikator dokumentu i zawartość dokumentu. Na przykład:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] nvarchar(max)
);
Lub tam, gdzie jest obsługiwane:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] json
);
Ta struktura jest odpowiednikiem kolekcji, które można znaleźć w klasycznych bazach danych dokumentów. Klucz podstawowy _id to wartość automatycznego zwiększania, która udostępnia unikatowy identyfikator dla każdego dokumentu i umożliwia szybkie wyszukiwanie. Ta struktura jest dobrym wyborem w przypadku klasycznych scenariuszy NoSQL, w których chcesz pobrać dokument według identyfikatora lub zaktualizować przechowywany dokument według identyfikatora.
- Użyj natywnego json typu danych, gdzie jest dostępny do przechowywania dokumentów JSON.
- Typ danych nvarchar(max) umożliwia przechowywanie dokumentów JSON o rozmiarze do 2 GB. Jeśli jednak masz pewność, że dokumenty JSON nie są większe niż 8 KB, zalecamy jednak użycie nvarchar(4000) zamiast nvarchar(max) ze względów wydajności.
Przykładowa tabela utworzona w poprzednim przykładzie zakłada, że prawidłowe dokumenty JSON są przechowywane w kolumnie log. Jeśli chcesz mieć pewność, że prawidłowy kod JSON został zapisany w kolumnie log, możesz dodać ograniczenie CHECK dla kolumny. Na przykład:
ALTER TABLE WebSite.Logs
ADD CONSTRAINT [Log record should be formatted as JSON]
CHECK (ISJSON([log])=1)
Za każdym razem, gdy ktoś wstawia lub aktualizuje dokument w tabeli, to ograniczenie sprawdza, czy dokument JSON jest poprawnie sformatowany. Bez ograniczenia tabela jest zoptymalizowana pod kątem wstawiania, ponieważ dowolny dokument JSON jest dodawany bezpośrednio do kolumny bez żadnego przetwarzania.
Podczas przechowywania dokumentów JSON w tabeli można użyć standardowego języka Transact-SQL do wykonywania zapytań dotyczących dokumentów. Na przykład:
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
Silną przewagą jest to, że można użyć dowolnej funkcji T-SQL i klauzuli zapytania do wykonywania zapytań dotyczących dokumentów JSON. Program SQL Server i usługa SQL Database nie wprowadza żadnych ograniczeń w zapytaniach, których można użyć do analizowania dokumentów JSON. Możesz wyodrębnić wartości z dokumentu JSON za pomocą funkcji JSON_VALUE i użyć jej w zapytaniu tak jak każda inna wartość.
Ta możliwość korzystania z rozbudowanej składni zapytań języka T-SQL jest kluczową różnicą między bazami danych SQL Server i SQL Database i klasycznymi bazami danych NoSQL — w Transact-SQL prawdopodobnie masz jakąkolwiek funkcję, która jest potrzebna do przetwarzania danych JSON.
Indeksy
Jeśli dowiesz się, że zapytania często wyszukują dokumenty według określonej właściwości (na przykład właściwość severity w dokumencie JSON), możesz dodać indeks nieklastrowany rowstore dla tej właściwości, aby przyspieszyć zapytania.
Możesz utworzyć obliczoną kolumnę, która uwidacznia wartości JSON z kolumn JSON w określonej ścieżce (czyli na ścieżce $.severity) i utworzyć standardowy indeks dla tej obliczonej kolumny. Na przykład:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] nvarchar(max),
[severity] AS JSON_VALUE([log], '$.severity'),
index ix_severity (severity)
);
Obliczona kolumna używana w tym przykładzie jest nietrwałą lub wirtualną kolumną, która nie dodaje dodatkowego miejsca do tabeli. Indeks ix_severity jest używany w celu zwiększenia wydajności zapytań, takich jak w poniższym przykładzie:
SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'
Jedną z ważnych cech tego indeksu jest to, że uwzględnia sortowanie. Jeśli oryginalna kolumna nvarchar ma właściwość COLLATION (na przykład czułość na wielkość liter lub język japoński), indeks jest zorganizowany zgodnie z regułami języka lub regułami czułości na wielkość liter skojarzonymi z kolumną nvarchar. Ta świadomość sortowania może być ważną funkcją, jeśli tworzysz aplikacje dla rynków globalnych, które muszą używać niestandardowych reguł języka podczas przetwarzania dokumentów JSON.
Duże tabele & format magazynu kolumn
Jeśli spodziewasz się dużej liczby dokumentów JSON w kolekcji, zalecamy dodanie klastrowanego indeksu kolumnowego do kolekcji, jak pokazano w poniższym przykładzie:
create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
[_id] bigint default(next value for WebSite.LogID),
[log] nvarchar(max),
INDEX cci CLUSTERED COLUMNSTORE
);
Indeks klastrowanego magazynu kolumnowego zapewnia wysoką kompresję danych (do 25x), która może znacznie zmniejszyć wymagania dotyczące miejsca do przechowywania, obniżyć koszt przechowywania i zwiększyć wydajność I/O obciążenia. Ponadto klastrowane indeksy magazynu kolumn są zoptymalizowane pod kątem skanowania tabel i analizy dokumentów JSON, więc ten typ indeksu może być najlepszą opcją analizy dzienników.
W poprzednim przykładzie użyto obiektu sekwencji do przypisania wartości do kolumny _id. Zarówno sekwencje, jak i tożsamości, są prawidłowymi opcjami dla kolumny ID.
Często zmieniane dokumenty & tabele zoptymalizowane pod kątem pamięci
Jeśli spodziewasz się dużej liczby operacji aktualizacji, wstawiania i usuwania w kolekcjach, możesz przechowywać dokumenty JSON w tabelach zoptymalizowanych pod kątem pamięci. Kolekcje JSON zoptymalizowane pod kątem pamięci zawsze przechowują dane w pamięci, więc nie ma obciążenia wejścia/wyjścia magazynu. Ponadto kolekcje JSON zoptymalizowane pod kątem pamięci są całkowicie wolne od blokady — czyli akcje na dokumentach nie blokują żadnej innej operacji.
Jedyną rzeczą, którą musisz wykonać, aby przekonwertować kolekcję klasyczną na kolekcję zoptymalizowaną pod kątem pamięci, jest określenie opcji WITH (MEMORY_OPTIMIZED=ON) po definicji tabeli, jak pokazano w poniższym przykładzie. Następnie masz zoptymalizowaną pod kątem pamięci wersję kolekcji JSON.
CREATE TABLE WebSite.Logs (
[_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
[log] nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)
Tabela zoptymalizowana pod kątem pamięci jest najlepszą opcją dla często zmieniających się dokumentów. Rozważając tabele zoptymalizowane pod kątem pamięci, należy również rozważyć wydajność. Użyj nvarchar(4000) typu danych zamiast nvarchar(max) dla dokumentów JSON w kolekcjach zoptymalizowanych w pamięci, jeśli to możliwe, ponieważ może to znacząco poprawić wydajność. Typ danych json nie jest obsługiwany w przypadku tabel zoptymalizowanych pod kątem pamięci.
Podobnie jak w przypadku tabel klasycznych, można dodawać indeksy w polach uwidacznianych w tabelach zoptymalizowanych pod kątem pamięci przy użyciu kolumn obliczanych. Na przykład:
CREATE TABLE WebSite.Logs (
[_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
[log] nvarchar(max),
[severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
INDEX ix_severity (severity)
) WITH (MEMORY_OPTIMIZED=ON)
Aby zmaksymalizować wydajność, rzutuj wartość JSON na najmniejszy możliwy typ, który może służyć do przechowywania wartości właściwości. W poprzednim przykładzie użyto tinyint.
Możesz również umieścić zapytania SQL, które aktualizują dokumenty JSON w procedurach składowanych, aby uzyskać korzyści z kompilacji natywnej. Na przykład:
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
Ta natywnie skompilowana procedura wykonuje zapytanie i tworzy .DLL kod, który uruchamia zapytanie. Natywnie skompilowana procedura jest szybszym podejściem do wykonywania zapytań i aktualizowania danych.
Konkluzja
Natywne funkcje JSON w programie SQL Server i usłudze SQL Database umożliwiają przetwarzanie dokumentów JSON tak jak w bazach danych NoSQL. Każda baza danych — relacyjna lub NoSQL — ma pewne zalety i wady przetwarzania danych JSON. Kluczową zaletą przechowywania dokumentów JSON w programie SQL Server lub usłudze SQL Database jest pełna obsługa języka SQL. Możesz użyć zaawansowanego języka Transact-SQL do przetwarzania danych i skonfigurowania różnych opcji magazynowania, od indeksów magazynu kolumn na potrzeby wysokiej kompresji i szybkiej analizy, po tabele zoptymalizowane pod kątem pamięci na potrzeby przetwarzania bez blokady. Jednocześnie uzyskujesz korzyści z dojrzałych funkcji zabezpieczeń i internacjonalizacji, które można łatwo użyć ponownie w scenariuszu NoSQL. Powody opisane w tym artykule są doskonałymi przyczynami, dla których warto rozważyć przechowywanie dokumentów JSON w programie SQL Server lub usłudze SQL Database.
Dowiedz się więcej o formacie JSON w programie SQL Server i usłudze Azure SQL Database
Aby zapoznać się z wizualnym wprowadzeniem do wbudowanej obsługi kodu JSON w programie SQL Server i usłudze Azure SQL Database, zobacz następujące wideo: