Speichern von JSON-Dokumenten in SQL Server oder SQL-Datenbank

  • Artikel

SQL Server und Azure SQL-Datenbank verfügen über native JSON-Funktionen, mit denen Sie JSON-Dokumente durch die standardmäßige SQL-Sprache analysieren können. Sie können JSON-Dokumente in SQL Server oder SQL-Datenbank speichern und JSON-Daten wie in einer NoSQL-Datenbank abfragen. Dieser Artikel beschreibt die Optionen zum Speichern von JSON-Dokumenten in SQL Server oder SQL-Datenbank.

JSON-Speicherformat

Die erste Entscheidung zum Speicherentwurf betrifft das Speichern von JSON-Dokumenten in den Tabellen. Es stehen zwei Optionen zur Verfügung:

  • LOB-Speicher: JSON-Dokumente können unverändert in NVARCHAR-Spalten gespeichert werden. Dies ist die beste Methode zum schnellen Laden und Aufnehmen von Daten, da die Ladegeschwindigkeit mit der Ladegeschwindigkeit von Zeichenfolgenspalten übereinstimmt. Bei diesem Ansatz kann es zu einer zusätzlichen Leistungseinbuße bei der Abfrage-/Analysezeit kommen, wenn die Indizierung für JSON-Werte nicht ausgeführt wird, da die unformatierten JSON-Dokumente während der Ausführung der Abfragen analysiert werden müssen.
  • Relationaler Speicher: JSON-Dokumente können analysiert werden, während sie mithilfe von OPENJSON-, JSON_VALUE- oder JSON_QUERY-Funktionen in die Tabelle eingefügt werden. Fragmente aus den eingegebenen JSON-Dokumenten können in den SQL-Datentypspalten oder in NVARCHAR-Spalten gespeichert werden, die JSON-Unterelemente enthalten. Dieser Ansatz erhöht die Ladezeit, da die JSON-Analyse während des Ladens erfolgt; Abfragen stimmen jedoch mit der Leistung klassischer Abfragen für die relationalen Daten überein.

Klassische Tabellen

Die einfachste Möglichkeit zum Speichern von JSON-Dokumenten in SQL Server oder SQL-Datenbank stellt die Erstellung einer zweispaltigen Tabelle dar, die die ID und den Inhalt des Dokuments enthält. Beispiel:

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

Diese Struktur entspricht den Sammlungen, die Sie in klassischen Dokumentdatenbanken finden. Der Primärschlüssel _id ist ein automatisch inkrementierter Wert, der einen eindeutigen Bezeichner für jedes Dokument bereitstellt und schnelle Suchvorgänge ermöglicht. Diese Struktur ist eine gute Wahl für die klassischen NoSQL-Szenarios, in denen Sie ein Dokument mit der ID abrufen oder ein gespeichertes Dokument mit einer bestimmten ID aktualisieren möchten.

Mit dem Datentyp „nvarchar(max)“ können Sie JSON-Dokumente mit einer Größe von bis zu 2 GB speichern. Wenn Sie jedoch sicher sind, dass Ihre JSON-Dokumente nicht größer als 8 KB sind, wird empfohlen, aus Leistungsgründen NVARCHAR(4000) anstelle von NVARCHAR(max) zu verwenden.

Bei der im vorherigen Beispiel erstellten Beispieltabelle wird davon ausgegangen, dass gültige JSON-Dokumente in der Spalte log gespeichert sind. Wenn Sie sichergehen wollen, dass ein gültiges JSON-Objekt in der Spalte log gespeichert ist, können Sie für die Spalte eine CHECK-Einschränkung hinzufügen. Beispiel:

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

Jedes Mal, wenn ein Dokument in die Tabelle eingefügt oder in der Tabelle aktualisiert wird, überprüft diese Einschränkung, ob das JSON-Dokument korrekt formatiert ist. Ohne diese Einschränkung ist die Tabelle für Einfügungen optimiert, da jedes JSON-Dokument ohne weitere Verarbeitung direkt zur Spalte hinzugefügt wird.

Wenn Sie die JSON-Dokumente in der Tabelle speichern, können Sie zum Abfragen der Dokumente die Standardsprache Transact-SQL verwenden. Beispiel:

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

Die Möglichkeit, jede T-SQL-Funktion und Abfrageklausel zum Abfragen von JSON-Dokumenten verwenden zu können, ist besonders nützlich. SQL Server und SQL-Datenbank führen in den Abfragen, die Sie zur Analyse von JSON-Dokumenten verwenden können, keine Einschränkungen ein. Sie können mit der Funktion JSON_VALUE Werte aus einem JSON-Dokument extrahieren und wie jeden anderen Wert in der Abfrage verwenden.

Diese Fähigkeit, umfassende T-SQL-Abfragesyntax zu verwenden, ist der entscheidende Unterschied zwischen SQL Server und SQL-Datenbank und klassischen NoSQL-Datenbanken – bei Transact-SQL sind aller Wahrscheinlichkeit nach alle Funktionen vorhanden, die Sie zur Verarbeitung von JSON-Daten benötigen.

Indizes

Wenn Sie feststellen, dass Ihre Abfragen häufig Dokumente nach bestimmten Eigenschaften durchsuchen (z.B. nach einer severity-Eigenschaft in einem JSON-Dokument), können Sie einen klassischen NONCLUSTERED-Index für die Eigenschaft hinzufügen, um die Abfragen zu beschleunigen.

Sie können eine berechnete Spalte erstellen, die JSON-Werte von den JSON-Spalten für den angegebenen Pfad (d.h. für den Pfad $.severity) verfügbar macht, und einen Standardindex für diese berechnete Spalte erstellen. Beispiel:

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

    severity AS JSON_VALUE(log, '$.severity'),
    index ix_severity (severity)
);

Bei der in diesem Beispiel verwendeten berechneten Spalte handelt es sich um eine nicht persistierte oder virtuelle Spalte, die den Speicherplatz der Tabelle nicht erweitert. Diese wird vom Index ix_severity verwendet, um wie im folgenden Beispiel die Leistung von Abfragen zu verbessern:

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

Ein wichtiges Merkmal dieses Index ist, dass er sich sortieren lässt. Wenn Ihre ursprüngliche NVARCHAR-Spalte über eine COLLATION-Eigenschaft verfügt (z.B. Groß-/Kleinschreibung oder Japanisch), wird der Index nach den Sprach- oder Groß-/Kleinschreibungsregeln der NVARCHAR-Spalte organisiert. Die Unterstützung dieser Sortierung kann sich für die Entwicklung von Anwendungen für globale Märkte, die benutzerdefinierte Sprachregeln bei der Verarbeitung von JSON-Dokumenten erfordern, als wichtiges Feature erweisen.

Große Formate für Tabellen und Columnstore-Indizes

Wenn Sie mit einer großen Anzahl von JSON-Dokumenten in Ihrer Sammlung rechnen, empfehlen wir Ihnen, einen CLUSTERED COLUMNSTORE-Index für die Sammlung hinzuzufügen, wie im folgenden Beispiel gezeigt wird:

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

Ein CLUSTERED COLUMNSTORE-Index ermöglicht eine hohe (bis zu 25-fache) Datenkomprimierung, wodurch Sie Ihren Speicherplatzbedarf deutlich reduzieren, die Speicherkosten senken und die E/A-Leistung Ihrer Workloads erhöhen können. Außerdem sind CLUSTERED COLUMNSTORE-Indizes für Tabellenscans und Analysen für Ihre JSON-Dokumente optimiert, sodass dieser Indextyp die beste Option für Protokollanalysen darstellen kann.

Im vorhergehende Beispiel wird ein Sequenzobjekt verwendet, um der Spalte _id Werte zuzuweisen. Sowohl Sequenzen als auch Identitäten sind gültige Optionen für die ID-Spalte.

Häufig geänderte Dokumente und speicheroptimierte Tabellen

Wenn Sie mit einer großen Anzahl von Aktualisierungs-, Einfüge- und Löschvorgängen in Ihren Sammlungen rechnen, können Sie Ihre JSON-Dokumente in speicheroptimierten Tabellen speichern. Speicheroptimierte JSON-Sammlungen verwalten die Daten immer im Arbeitsspeicher, sodass kein Speicher-E/A-Overhead erzeugt wird. Darüber hinaus sind speicheroptimierte JSON-Sammlungen komplett sperrenfrei, d. h., andere Vorgänge werden durch Aktionen für Dokumente nicht blockiert.

Um eine klassische Sammlung in eine speicheroptimierte Sammlung zu konvertieren, müssen Sie lediglich die Option with (memory_optimized=on) nach der Tabellendefinition angeben, wie im folgenden Beispiel gezeigt wird. Dann haben Sie eine speicheroptimierte Version der JSON-Sammlung erstellt.

create table WebSite.Logs (
  _id bigint identity primary key nonclustered,
  log nvarchar(4000)
) with (memory_optimized=on)

Eine speicheroptimierte Tabelle ist die beste Option für häufig geänderte Dokumente. Wenn Sie speicheroptimierte Tabellen in Erwägung ziehen, sollten Sie auch die Leistung berücksichtigen. Verwenden Sie für die JSON-Dokumente in Ihren speicheroptimierten Sammlungen möglichst NVARCHAR(4000) anstelle von NVARCHAR(max), da die Leistung hierdurch deutlich verbessert werden kann.

Wie bei klassischen Tabellen können Sie durch berechnete Spalten Indizes für die Felder, die Sie in speicheroptimierten Tabellen verfügbar machen, hinzufügen. Beispiel:

create table WebSite.Logs (

  _id bigint identity primary key nonclustered,
  log nvarchar(4000),

  severity AS cast(JSON_VALUE(log, '$.severity') as tinyint) persisted,
  index ix_severity (severity)

) with (memory_optimized=on)

Um die Leistung zu maximieren, sollten Sie den JSON-Wert in den kleinstmöglichen Typ umwandeln, der zum Speichern des Eigenschaftswerts verwendet werden kann. Im vorherigen Beispiel wurde tinyint verwendet.

Sie können auch SQL-Abfragen, die JSON-Dokumente aktualisieren, in gespeicherte Prozeduren einbinden, um von den Vorteilen der nativen Kompilierung zu profitieren. Beispiel:

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

Diese nativ kompilierte Prozedur erstellt anhand der Abfrage einen DLL-Code, der die Abfrage ausführt. Mit einer nativ kompilierten Prozedur können Sie schneller Daten abfragen und aktualisieren.

Zusammenfassung

Durch native JSON-Funktionen in SQL Server und SQL-Datenbank können JSON-Dokumente wie in NoSQL-Datenbanken verarbeitet werden. Datenbanken – ganz gleich, ob relationale oder NoSQL-Datenbanken – weisen Vor- und Nachteile hinsichtlich der Verarbeitung von JSON-Daten auf. Der größte Vorteil bei der Speicherung von JSON-Dokumenten in SQL Server oder SQL-Datenbank besteht in der vollständigen Unterstützung für SQL-Sprachen. Sie können mithilfe der umfangreichen Transact-SQL-Sprache Daten verarbeiten und eine Vielzahl von Speicheroptionen konfigurieren (von Columnstore-Indizes für umfangreiche Komprimierungen und schnelle Analysen bis hin zu speicheroptimierten Tabellen für eine Verarbeitung ohne Sperren). Gleichzeitig profitieren Sie von ausgereiften Sicherheits- und Internationalisierungsfeatures, die Sie in Ihrem NoSQL-Szenario einfach wiederverwenden können. Die in diesem Artikel beschriebenen Gründe sind überzeugende Argumente dafür, die Speicherung von JSON-Dokumenten in SQL Server oder SQL-Datenbank in Erwägung zu ziehen.

Weitere Informationen zu JSON in SQL Server und Azure SQL-Datenbank

Microsoft-Videos

Hinweis

Einige der Videolinks in diesem Abschnitt funktionieren derzeit möglicherweise nicht. Microsoft migriert Inhalte, die zuvor auf Channel 9 veröffentlicht wurden, zu einer neuen Plattform. Wir aktualisieren die Links, wenn die Videos zur neuen Plattform migriert werden.

Eine visuelle Einführung in die JSON-Unterstützung, die in SQL Server und Azure SQL-Datenbank integriert ist, finden Sie in den folgenden Videos: