Delen via

JSON-documenten opslaan

  • Artikel

van toepassing op: SQL Server 2016 (13.x) en hoger Azure SQL DatabaseAzure SQL Managed Instance

De SQL Database Engine biedt systeemeigen JSON-functies waarmee u JSON-documenten kunt parseren met behulp van de standaard-SQL-taal. U kunt JSON-documenten opslaan in SQL Server of SQL Database en JSON-gegevens opvragen zoals in een NoSQL-database. In dit artikel worden de opties beschreven voor het opslaan van JSON-documenten.

JSON-opslagindeling

De eerste beslissing over opslagontwerp is het opslaan van JSON-documenten in de tabellen. Er zijn twee beschikbare opties:

  • LOB-opslag - JSON-documenten kunnen as-is worden opgeslagen in kolommen met het gegevenstype json- of nvarchar-. Dit is de beste manier om snel gegevens te laden en op te nemen, omdat de laadsnelheid overeenkomt met de laadsnelheid van tekenreekskolommen. Deze aanpak kan leiden tot een extra prestatiestraf voor query-/analysetijd als indexering op JSON-waarden niet wordt uitgevoerd, omdat de onbewerkte JSON-documenten moeten worden geparseerd terwijl de query's worden uitgevoerd.

  • Relationele opslag - JSON-documenten kunnen worden geparseerd terwijl ze in de tabel worden ingevoegd met behulp van OPENJSON, JSON_VALUE- of JSON_QUERY-functies. Fragmenten uit de JSON-invoerdocumenten kunnen worden opgeslagen in de kolommen met JSON-subelementen met gegevenstypen json- of nvarchar-. Deze benadering verhoogt de laadtijd omdat JSON-parsering wordt uitgevoerd tijdens het laden; Query's komen echter overeen met de prestaties van klassieke query's op de relationele gegevens.

  • Het JSON-gegevenstype is momenteel in preview voor Azure SQL Database en Azure SQL Managed Instance (geconfigureerd met de Always-up-to-date updatebeleid).

  • Momenteel in SQL Server is JSON geen ingebouwd gegevenstype.

Klassieke tabellen

De eenvoudigste manier om JSON-documenten op te slaan in SQL Server of Azure SQL Database is door een tabel met twee kolommen te maken die de id van het document en de inhoud van het document bevat. Bijvoorbeeld:

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

Of, indien ondersteund:

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

Deze structuur is gelijk aan de verzamelingen die u kunt vinden in klassieke documentdatabases. De primaire sleutel _id is een automatisch verhogende waarde die een unieke id biedt voor elk document en snelle zoekacties mogelijk maakt. Deze structuur is een goede keuze voor de klassieke NoSQL-scenario's waarin u een document wilt ophalen op basis van id of een opgeslagen document op id wilt bijwerken.

  • Gebruik het systeemeigen json gegevenstype, waar beschikbaar voor het opslaan van JSON-documenten.
  • Met het nvarchar(max) gegevenstype kunt u JSON-documenten opslaan die maximaal 2 GB groot zijn. Als u zeker weet dat uw JSON-documenten niet groter zijn dan 8 kB, raden we u echter aan om prestatieredenen nvarchar(4000) te gebruiken in plaats van nvarchar(max).

In de voorbeeldtabel die in het vorige voorbeeld is gemaakt, wordt ervan uitgegaan dat geldige JSON-documenten zijn opgeslagen in de kolom log. Als u er zeker van wilt zijn dat geldige JSON wordt opgeslagen in de kolom log, kunt u een CHECK-beperking toevoegen aan de kolom. Bijvoorbeeld:

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

Telkens wanneer iemand een document in de tabel invoegt of bijwerkt, wordt met deze beperking gecontroleerd of het JSON-document correct is opgemaakt. Zonder de beperking is de tabel geoptimaliseerd voor invoegingen, omdat een JSON-document rechtstreeks aan de kolom wordt toegevoegd zonder dat er iets hoeft te worden verwerkt.

Wanneer u uw JSON-documenten opslaat in de tabel, kunt u de standaardtaal Transact-SQL gebruiken om een query uit te voeren op de documenten. Bijvoorbeeld:

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

Het is een krachtig voordeel dat u elke T-SQL-functie en querycomponent kunt gebruiken om query's uit te voeren op JSON-documenten. SQL Server en SQL Database introduceren geen beperkingen in de query's die u kunt gebruiken om JSON-documenten te analyseren. U kunt waarden uit een JSON-document extraheren met de JSON_VALUE-functie en deze gebruiken in de query, net als elke andere waarde.

Deze mogelijkheid om uitgebreide T-SQL-querysyntaxis te gebruiken, is het belangrijkste verschil tussen SQL Server en SQL Database en klassieke NoSQL-databases. In Transact-SQL u waarschijnlijk een functie hebt die u nodig hebt om JSON-gegevens te verwerken.

Indexen

Als u erachter komt dat uw query's vaak documenten doorzoeken op een bepaalde eigenschap (bijvoorbeeld een severity eigenschap in een JSON-document), kunt u een niet-geclusterde rijopslagindex toevoegen aan de eigenschap om de query's sneller te maken.

U kunt een berekende kolom maken waarmee JSON-waarden worden weergegeven uit de JSON-kolommen op het opgegeven pad (dat wil gezegd, op het pad $.severity) en een standaardindex voor deze berekende kolom maken. Bijvoorbeeld:

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

De berekende kolom die in dit voorbeeld wordt gebruikt, is een niet-persistente of virtuele kolom die geen extra ruimte toevoegt aan de tabel. Het wordt gebruikt door de index ix_severity om de prestaties van de query's te verbeteren, zoals in het volgende voorbeeld:

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

Een belangrijk kenmerk van deze index is dat deze sorteringsbewust is. Als de oorspronkelijke nvarchar kolom een eigenschap COLLATION heeft (bijvoorbeeld hoofdlettergevoeligheid of Japanse taal), wordt de index ingedeeld volgens de taalregels of de hoofdlettergevoeligheidsregels die zijn gekoppeld aan de kolom nvarchar. Deze sorteringsbewustzijn kan een belangrijke functie zijn als u toepassingen ontwikkelt voor wereldwijde markten die aangepaste taalregels moeten gebruiken bij het verwerken van JSON-documenten.

Grote tabellen & columnstore formaat

Als u verwacht een groot aantal JSON-documenten in uw verzameling te hebben, raden we u aan een geclusterde columnstore-index toe te voegen aan de verzameling, zoals wordt weergegeven in het volgende voorbeeld:

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
);

Een geclusterde columnstore-index biedt een hoge gegevenscompressie (maximaal 25x) die uw opslagruimte aanzienlijk kan verminderen, de opslagkosten verlagen en de I/O-prestaties van uw workload verhogen. Bovendien zijn geclusterde columnstore-indexen geoptimaliseerd voor tabelscans en -analyses op uw JSON-documenten. Dit type index is dus mogelijk de beste optie voor Log Analytics.

In het voorgaande voorbeeld wordt een reeksobject gebruikt om waarden toe te wijzen aan de kolom _id. Zowel reeksen als identiteiten zijn geldige opties voor de id-kolom.

Documenten regelmatig wijzigen & tabellen die zijn geoptimaliseerd voor geheugen

Als u een groot aantal update-, invoeg- en verwijderbewerkingen in uw verzamelingen verwacht, kunt u uw JSON-documenten opslaan in tabellen die zijn geoptimaliseerd voor geheugen. JSON-verzamelingen die zijn geoptimaliseerd voor geheugen, bewaren altijd gegevens in het geheugen, zodat er geen I/O-overhead voor opslag is. Bovendien zijn JSON-verzamelingen die zijn geoptimaliseerd voor geheugen volledig vergrendeld. Acties op documenten blokkeren dus geen andere bewerkingen.

Het enige wat u moet doen om een klassieke verzameling te converteren naar een verzameling die is geoptimaliseerd voor geheugen, is door de optie WITH (MEMORY_OPTIMIZED=ON) op te geven na de tabeldefinitie, zoals wordt weergegeven in het volgende voorbeeld. Vervolgens hebt u een voor geheugen geoptimaliseerde versie van de JSON-verzameling.

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

Een tabel die is geoptimaliseerd voor geheugen is de beste optie voor het regelmatig wijzigen van documenten. Houd ook rekening met prestaties wanneer u tabellen overweegt die zijn geoptimaliseerd voor geheugen. Gebruik de nvarchar(4000) gegevenstype in plaats van nvarchar(max) voor JSON-documenten in uw verzamelingen die zijn geoptimaliseerd voor geheugen, omdat dit de prestaties aanzienlijk kan verbeteren. Het json- gegevenstype wordt niet ondersteund met tabellen die zijn geoptimaliseerd voor geheugen.

Net als bij klassieke tabellen kunt u indexen toevoegen voor de velden die u beschikbaar maakt in tabellen die zijn geoptimaliseerd voor geheugen met behulp van berekende kolommen. Bijvoorbeeld:

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)

Als u de prestaties wilt maximaliseren, cast u de JSON-waarde naar het kleinste mogelijke type dat kan worden gebruikt om de waarde van de eigenschap te bewaren. In het voorgaande voorbeeld wordt tinyint gebruikt.

U kunt ook SQL-query's plaatsen die JSON-documenten bijwerken in opgeslagen procedures om het voordeel van systeemeigen compilatie te krijgen. Bijvoorbeeld:

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

Deze systeemeigen gecompileerde procedure neemt de query en maakt .DLL code waarmee de query wordt uitgevoerd. Een systeemeigen gecompileerde procedure is de snellere benadering voor het opvragen en bijwerken van gegevens.

Conclusie

Met systeemeigen JSON-functies in SQL Server en SQL Database kunt u JSON-documenten verwerken, net als in NoSQL-databases. Elke database, relationeel of NoSQL, heeft enkele voor- en nadelen voor JSON-gegevensverwerking. Het belangrijkste voordeel van het opslaan van JSON-documenten in SQL Server of SQL Database is volledige ondersteuning voor SQL-talen. U kunt de uitgebreide Transact-SQL taal gebruiken om gegevens te verwerken en verschillende opslagopties te configureren, van columnstore-indexen voor hoge compressie en snelle analyse, tot tabellen die zijn geoptimaliseerd voor geheugen voor vergrendelingsvrije verwerking. Tegelijkertijd krijgt u het voordeel van volwassen beveiligings- en internationalisatiefuncties die u eenvoudig kunt hergebruiken in uw NoSQL-scenario. De redenen die in dit artikel worden beschreven, zijn uitstekende redenen om JSON-documenten op te slaan in SQL Server of SQL Database.

Meer informatie over JSON in SQL Server en Azure SQL Database

Zie de volgende video's voor een visuele inleiding tot de ingebouwde JSON-ondersteuning in SQL Server en Azure SQL Database:

Verwante inhoud