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

  • Статья

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

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

Разрешения

На уровне экземпляра эта функция требует членства в предопределенных ролях сервера bulkadmin или разрешениях АДМИНИСТРИРОВАНИЯ BULK OPERATIONS.

Для уровня базы данных для этой функции требуются разрешения АДМИНИСТРИРОВАНИЯ 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

Дополнительные сведения о хранилище файлов Azure см. на этой странице.

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

Область применения: SQL Server 2017 (14.x) и более поздних версий, а также SQL Azure

Файлы можно загрузить непосредственно в База данных SQL Azure из Хранилище BLOB-объектов Azure с помощью команды 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 как отдельное значение, вы можете выполнить его синтаксический анализ и вернуть содержимое файла и свойства этого содержимого в строках и столбцах. В следующем примере используется JSON-файл с этого сайта, содержащий список документации.

Пример 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", ... }

Пример 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. Результаты приведены ниже.

Идентификатор Имя цена pages_i Автор
978-0641723445 The Lightning Thief 12.5 384 Рик Риордан (Rick Riordan)
978-1423103349 The Sea of Monsters 6.49 304 Рик Риордан (Rick Riordan)
978-1857995879 Sophie's World : The Greek Philosophers 3.07 64 Юстейн Гордер (Jostein Gaarder)
978-1933988177 Lucene in Action, Second Edition 30,5 475 Майкл Маккэндлесс (Michael McCandless)

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

См. также