Бөлісу құралы:

Хранение документов JSON в SQL Server или базе данных SQL

  • Мақала

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure

Модуль База данных SQL предоставляет собственные функции JSON, позволяющие анализировать документы JSON с помощью стандартного языка SQL. Вы можете хранить документы JSON в SQL Server или Базе данных SQL и запрашивать данные JSON так же, как в базе данных NoSQL. В этой статье описываются параметры хранения документов JSON.

Формат хранения JSON

При проектировании хранилища прежде всего нужно решить, как хранить документы JSON в таблицах. Доступны два варианта:

  • Хранилище LOB — документы JSON могут храниться как есть в столбцах с типом данных json или nvarchar. Это лучший способ быстрой загрузки и приема данных, так как скорость загрузки соответствует скорости загрузки строковых столбцов. Этот подход может привести к дополнительным штрафам производительности во время запроса или анализа, если индексирование значений JSON не выполняется, так как необработанные документы JSON должны быть проанализированы во время выполнения запросов.

  • Реляционное хранилище позволяет с помощью функций OPENJSON, JSON_VALUE или JSON_QUERYанализировать документы JSON во время их вставки в таблицу. Фрагменты входных документов JSON можно хранить в столбцах, содержащих вложенные элементы JSON с типами данных json или nvarchar. Этот подход увеличивает время загрузки, так как анализ JSON выполняется во время загрузки; однако запросы соответствуют производительности классических запросов реляционных данных.

  • В настоящее время в SQL Server JSON не является встроенным типом данных.

  • В настоящее время тип данных JSON доступен в База данных SQL Azure.

Классические таблицы

Самый простой способ хранения документов JSON в SQL Server или База данных SQL Azure — создать таблицу с двумя столбцами, содержащую идентификатор документа и содержимое документа. Например:

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

Или, где поддерживается:

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

Эта структура эквивалентна коллекциям из классических баз данных документов. Первичный ключ _id — это значение с автоматическим приращением, представляющее собой уникальный идентификатор каждого документа для быстрого поиска. Такая структура хорошо подходит для классических сценариев NoSQL, когда получение или изменение хранимого документа осуществляются по идентификатору.

  • Используйте собственный тип данных JSON , где доступно для хранения документов JSON.
  • Тип данных nvarchar(max) позволяет хранить документы JSON размером до 2 ГБ. Если вы уверены, что документы JSON не превышают 8 КБ, рекомендуется использовать nvarchar(4000) вместо nvarchar(max) по соображениям производительности.

В образце таблицы, созданном в предыдущем примере, предполагается, что в столбце log хранятся допустимые документы JSON. Если вы хотите убедиться, что в столбце log хранятся допустимые документы JSON, добавьте для столбца ограничение CHECK. Например:

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

Каждый раз, когда кто-то вставляет или изменяет документ в таблице, это ограничение проверяет, что документ JSON имеет правильный формат. Без этого ограничения таблица будет оптимизирована для операций вставки, так как любой документ JSON добавляется в столбец напрямую, без обработки.

При хранении в таблице документов JSON вы можете выполнять запросы к документам с помощью стандартного языка Transact-SQL. Например:

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

Возможность использования любой функции и предложения запроса T-SQL для запросов к документам JSON является существенным преимуществом. SQL Server и база данных SQL не вводят никаких ограничений к запросам, используемым для анализа документов JSON. Вы можете извлекать из документа JSON значения с помощью функции JSON_VALUE и использовать их в запросе, как любые другие значения.

Наличие полнофункционального синтаксиса запросов T-SQL является ключевым отличием SQL Server и базы данных SQL от классических баз данных NoSQL: в Transact-SQL вам, скорее всего, доступны все необходимые функции для обработки данных JSON.

Индексы

Если вы узнаете, что запросы часто ищут документы по некоторым свойствам (например, severity свойству в документе JSON), можно добавить индекс rowstore, некластеризованный индекс для свойства, чтобы ускорить запросы.

Вы можете создать вычисляемый столбец, который предоставляет значения JSON из столбцов JSON по заданному пути (то есть, по пути $.severity), и создать стандартный индекс в этом столбце. Например:

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

Используемый в этом примере вычисляемый столбец является несохраняемым или виртуальным столбцом, который не добавляет дополнительное место в таблицу. Индекс ix_severity использует его для повышения производительности запросов, как показано в следующем примере.

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

Важное свойство этого индекса — учет параметров сортировки. Если исходный столбец nvarchar имеет свойство (например, конфиденциальность регистра COLLATION или японский язык), индекс упорядочен в соответствии с правилами языка или правилами конфиденциальности регистра, связанными с столбцом nvarchar . Эта осведомленность о сортировке может быть важной функцией, если вы разрабатываете приложения для глобальных рынков, которые должны использовать пользовательские правила языка при обработке документов JSON.

Большие таблицы и формат columnstore

Если в коллекции ожидается большое количество документов JSON, рекомендуется добавить кластеризованный индекс columnstore в коллекцию, как показано в следующем примере:

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

Кластеризованный индекс columnstore обеспечивает высокую степень сжатия данных (до 25x), что может значительно сократить требования к хранилищу, снизить затраты на хранение и повысить производительность операций ввода-вывода рабочей нагрузки. Кроме того, кластеризованные индексы columnstore оптимизированы для сканирования таблиц и аналитики в документах JSON, поэтому этот тип индекса может быть лучшим вариантом для log analytics.

В примере выше для присвоения значений столбцу _id используется объект последовательности. И последовательности, и идентификаторы являются приемлемыми вариантами для этого столбца.

Часто изменяемые документы и таблицы, оптимизированные для памяти

Если в коллекциях ожидается множество операций изменения, вставки и удаления, вы можете хранить документы JSON в таблицах, оптимизированных для памяти. Оптимизированные для памяти коллекции JSON всегда хранят данные в памяти, что позволяет избежать нагрузки, связанной с операциями ввода-вывода в хранилище. Кроме того, эти коллекции абсолютно неблокируемые, то есть действия с документами не препятствуют никаким другим операциям.

Единственное, что необходимо преобразовать классическую коллекцию в оптимизированную для памяти коллекцию, — указать WITH (MEMORY_OPTIMIZED=ON) параметр после определения таблицы, как показано в следующем примере. После этого вы получите оптимизированную для памяти версию коллекции JSON.

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

Таблица, оптимизированная для памяти, — лучший вариант для часто изменяемых документов. При их внедрении также учитывайте производительность. При возможности используйте тип данных nvarchar(4000) вместо nvarchar(max) для документов JSON в оптимизированных для памяти коллекциях, так как это может значительно повысить производительность. Тип данных json не поддерживается в таблицах, оптимизированных для памяти.

Как и с классическими таблицами, вы можете добавлять индексы в поля, предоставляемые в оптимизированных для памяти таблицах, с помощью вычисляемых столбцов. Например:

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)

Чтобы обеспечить максимальную производительность, приведите значение JSON к наименьшему возможному типу, который можно использовать для хранения значения свойства. В примере выше используется tinyint.

Вы также можете поместить запросы SQL, которые изменяют документы JSON, в хранимые процедуры, чтобы воспользоваться преимуществами компиляции в машинный код. Например:

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

Эта скомпилированная в машинный код процедура принимает запрос и создает .DLL-код, который выполняет запрос. Она является самым быстрым способом для создания запросов и изменения данных.

Заключение

Собственные функции JSON в SQL Server и базе данных SQL позволяют работать с документами JSON так же, как в базах данных NoSQL. Каждая база данных (реляционная или NoSQL) обладает рядом преимуществ и недостатков в обработке данных JSON. Основное преимущество хранения документов JSON в SQL Server или базе данных SQL — это полная поддержка языка SQL. Вы можете использовать расширенный язык Transact-SQL для обработки данных и настройки различных вариантов хранения, от индексов columnstore для высокой сжатия и быстрой аналитики до оптимизированных для памяти таблиц для блокировки без блокировки. Кроме того, вам доступны обширные возможности обеспечения безопасности и оптимизации под различные рынки, которые можно легко переносить в сценарии NoSQL. Изложенные выше причины являются веским доводом в пользу хранения документов JSON в SQL Server или базе данных SQL.

Дополнительные сведения о JSON в SQL Server и базе данных SQL Azure

Наглядные инструкции по встроенной поддержке JSON в SQL Server и базе данных SQL Azure см. в следующих видео.

Связанный контент