Импорт документов JSON на SQL Server

Applies to: SQL Server 2016 (13.x) and later versions Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

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

Примеры, приведенные в этой статье, используют JSON-файл из примера GitHub, содержащего список книг.

Permissions

At the instance level, this feature requires membership of the bulkadmin fixed server role, or ADMINISTER BULK OPERATIONS permissions.

На уровне базы данных для использования этой функции требуются ADMINISTER DATABASE BULK OPERATIONS разрешения.

Для доступа к Хранилище BLOB-объектов Azure требуется доступ на чтение и запись.

Импорт документа JSON в единый столбец

OPENROWSET(BULK) — это табличная функция, которая может считывать данные из любого файла на локальном диске или сети, если SQL Server имеет доступ на чтение к этой локации. Эта функция возвращает таблицу с одним столбцом, включающим содержимое файла. С функцией OPENROWSET(BULK) можно использовать различные параметры, такие как разделители. В самом простом случае вы можете просто загрузить все содержимое файла как текстовое значение. (Это большое единичное значение известно как символьный объект большого размера или SINGLE_CLOB.)

Ниже приведен пример OPENROWSET(BULK) функции, которая считывает содержимое JSON-файла и возвращает его пользователю в виде одного значения:

SELECT BulkColumn
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j;

OPENJSON(BULK) считывает содержимое файла и возвращает его в BulkColumn.

Вы также можете загрузить содержимое файла в локальную переменную или таблицу, как показано в следующем примере.

-- Load file contents into a variable
DECLARE @json NVARCHAR(MAX);
SELECT @json = BulkColumn
 FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

-- Load file contents into a table
SELECT BulkColumn
INTO #temp
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

После загрузки содержимого JSON-файла текст JSON можно сохранить в таблицу.

Импорт документов JSON из хранилища файлов Azure

Вы также можете использовать OPENROWSET(BULK) как описано ранее для чтения JSON-файлов из других расположений файлов, к которым может получить доступ SQL Server. Предположим, что хранилище файлов Azure поддерживает протокол SMB. В результате вы можете сопоставить локальный виртуальный диск с общей папкой хранилища файлов Azure с помощью следующей процедуры:

1. Создайте учетную запись хранилища файлов (например, mystorage), общую папку (например, sharejson), а также папку в хранилище файлов Azure с помощью портала Azure или Azure PowerShell.

2. Отправьте несколько файлов JSON в общую папку хранилища файлов.

3. Создайте исходящее правило брандмауэра в брандмауэре Windows на компьютере, где разрешен доступ к порту 445. Поставщик услуг Интернета может заблокировать этот порт. Если вы получите ошибку DNS (ошибка 53) на следующем шаге, порт 445 не открыт или поставщик услуг интернета вещей блокирует его.

4. Подключите общую папку хранилища файлов Azure как локальный диск (например, T:).

   Ниже приведен синтаксис команды:

   net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]
   

   Ниже показан пример, в котором общей папке хранилища файлов Azure назначается буква локального диска T::

   net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==
   

   Вы можете найти ключ учетной записи хранения и первичный или вторичный ключ доступа к учетной записи хранения в разделе Ключи в настройках на портале Azure.

5. Теперь доступ к JSON-файлам можно получить из общей папки хранилища файлов Azure с помощью подключенного диска, как показано в следующем примере.

   SELECT book.*
   FROM OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json
   CROSS APPLY OPENJSON(BulkColumn) WITH (
       id NVARCHAR(100),
       name NVARCHAR(100),
       price FLOAT,
       pages_i INT,
       author NVARCHAR(100)
   ) AS book
   

For more info about Azure File Storage, see File storage.

Импорт документов JSON из хранилища BLOB-объектов Azure

Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL

Файлы можно загрузить непосредственно в базу данных Azure SQL из Azure Blob Storage с помощью команды T-SQL BULK INSERT или функции OPENROWSET.

Сначала необходимо создать внешний источник данных, как показано в примере ниже.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
    CREDENTIAL = MyAzureBlobStorageCredential
);

Затем выполните команду BULK INSERT с использованием функции DATA_SOURCE.

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

Синтаксический анализ документов JSON с преобразованием в строки и столбцы

Вместо чтения всего JSON-файла в виде одного значения может потребоваться проанализировать его и вернуть книги в файле и их свойства в строках и столбцах.

Example 1

В самом простом случае вы можете просто загрузить весь список из файла.

SELECT value
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn);

Предыдущий OPENROWSET считывает одно текстовое значение из файла. OPENROWSET возвращает значение в виде BulkColumn и передает BulkColumn функции OPENJSON . OPENJSON Выполняет итерацию по массиву объектов JSON в массиве BulkColumn и возвращает одну книгу в каждой строке. Каждая строка форматируется в JSON, как показано ниже.

{"id":"978-0641723445", "cat":["book","hardcover"], "name":"The Lightning Thief", ... }
{"id":"978-1423103349", "cat":["book","paperback"], "name":"The Sea of Monsters", ... }
{"id":"978-1857995879", "cat":["book","paperback"], "name":"Sophie's World : The Greek", ... }
{"id":"978-1933988177", "cat":["book","paperback"], "name":"Lucene in Action, Second", ... }

Example 2

Функция OPENJSON может анализировать содержимое JSON и преобразовывать его в таблицу или результирующий набор. В примере ниже показана загрузка содержимого, синтаксический анализ загруженного содержимого JSON и возвращение пяти полей в качестве столбцов.

SELECT book.*
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book;

В этом примере OPENROWSET(BULK) считывает содержимое файла и передает это содержимое OPENJSON функции с определенной схемой выходных данных. OPENJSON сопоставляет свойства в объектах JSON с помощью имен столбцов. Например, свойство price возвращается как столбец price и преобразовывается в тип данных float. Результаты приведены ниже.

Теперь вы можете вернуть эту таблицу пользователю или загрузить данные в другую таблицу.

Related content

• JSON as a bridge between NoSQL and relational worlds (JSON как мост между NoSQL и реляционными решениями)
• Анализ и преобразование данных JSON с помощью OPENJSON