Importar documentos JSON para o SQL Server

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Este artigo descreve como importar arquivos JSON para o SQL Server. Os documentos JSON armazenam muitos tipos de dados, por exemplo, logs de aplicativos, dados de sensores e assim por diante. É importante ser capaz de ler os dados JSON armazenados em arquivos, carregar os dados no SQL Server e analisá-los.

Permissões

No nível da instância, esse recurso requer a associação da função de servidor fixa bulkadmin ou permissões ADMINISTER BULK OPERATIONS.

Para o nível de banco de dados, esse recurso requer permissões ADMINISTER DATABASE BULK OPERATIONS.

O acesso ao Armazenamento de Blobs do Azure requer acesso de leitura-gravação.

Importar um documento JSON em uma única coluna

OPENROWSET(BULK) é uma função com valor de tabela que pode ler dados de qualquer arquivo na unidade local ou rede, se o SQL Server tiver acesso de leitura para esse local. Ela retorna uma tabela com uma única coluna com o conteúdo do arquivo. Há várias opções que podem ser usadas com a função OPENROWSET(BULK), como separadores. Mas, no caso mais simples, você pode simplesmente carregar todo o conteúdo de um arquivo como um valor de texto. (Esse valor grande único é conhecido como um objeto grande de caractere único ou SINGLE_CLOB.)

Aqui está um exemplo da função OPENROWSET(BULK) que lê o conteúdo de um arquivo JSON e retorna para o usuário como um único valor:

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

OPENJSON(BULK) lê o conteúdo do arquivo e o retorna em BulkColumn.

Também é possível carregar o conteúdo do arquivo em uma variável local ou uma tabela, conforme mostrado no exemplo a seguir:

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

Depois de carregar o conteúdo do arquivo JSON, você pode salvar o texto JSON em uma tabela.

Importar documentos JSON do Azure File Storage

Também é possível usar OPENROWSET(BULK) conforme descrito acima para ler os arquivos JSON de outros locais de arquivos que o SQL Server pode acessar. Por exemplo, o Azure File Storage dá suporte ao protocolo SMB. Como resultado, você pode mapear uma unidade virtual local para o compartilhamento de armazenamento do Azure File usando o seguinte procedimento:

  1. Crie uma conta de armazenamento do arquivo (por exemplo, mystorage), um compartilhamento de arquivo (por exemplo, sharejson) e uma pasta no Azure File Storage usando o portal do Azure ou o Azure PowerShell.

  2. Carregar alguns arquivos JSON no compartilhamento de armazenamento de arquivos.

  3. Crie uma regra de firewall de saída no Firewall do Windows no computador que permite a porta 445. Seu provedor de serviços de Internet pode bloquear essa porta. Se você receber um erro DNS (erro 53) na etapa seguinte, então você não abriu a porta 445 ou seu ISP está bloqueando a porta.

  4. Monte o compartilhamento do Armazenamento de Arquivos do Azure como uma unidade local (por exemplo, T:).

    Esta é a sintaxe do comando:

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

    Veja este exemplo que atribui a letra da unidade local T: ao compartilhamento de Armazenamento de Arquivos do Azure:

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

    Você pode encontrar a chave de conta de armazenamento e a chave de acesso da conta de armazenamento primária ou secundária na seção Chaves em Configurações no portal do Azure.

  5. Agora você pode acessar os arquivos JSON do compartilhamento de Armazenamento de Arquivos do Azure usando a unidade mapeada, conforme mostrado no exemplo a seguir:

    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

Para mais informações sobre o Azure File Storage, consulte Armazenamento de arquivo.

Importar documentos JSON do Armazenamento de Blobs do Azure

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores, e ao SQL do Azure

Você pode carregar arquivos diretamente no Banco de Dados SQL do Azure do Armazenamento de Blobs do Azure com o comando T-SQL BULK INSERT ou a função OPENROWSET.

Primeiro, crie uma fonte de dados externa, como mostrado no exemplo a seguir.

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

Em seguida, execute um comando BULK INSERT com a opção DATA_SOURCE.

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

Analisar documentos JSON em linhas e colunas

Em vez de ler um arquivo JSON inteiro como um único valor, pode ser útil analisá-lo e retornar os livros no arquivo e suas propriedades em linhas e colunas. Este exemplo usa um arquivo JSON deste site que contém uma lista de livros.

Exemplo 1

No exemplo mais simples, você pode carregar apenas a lista inteira do arquivo.

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

A função OPENROWSET anterior lê um único valor de texto do arquivo. A função OPENROWSET retorna o valor como um BulkColumn e passa-o para a função OPENJSON. A função OPENJSON percorre a matriz de objetos JSON da matriz BulkColumn e retorna um livro em cada linha. Cada linha é formatada como JSON, mostrado ao lado.

{"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", ... }

Exemplo 2

A função OPENJSON pode analisar o conteúdo do JSON e transformá-lo em uma tabela ou em um conjunto de resultados. O exemplo a seguir carrega o conteúdo, analisa o JSON carregado e retorna os cinco campos como colunas:

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;

Neste exemplo, OPENROWSET(BULK) lê o conteúdo do arquivo e passa esse conteúdo para a função OPENJSON com um esquema definido para a saída. A função OPENJSON corresponde a propriedades em objetos JSON usando nomes de coluna. Por exemplo, a propriedade price é retornada como uma coluna price e convertida para o tipo de dados float. Estes são os resultados:

ID Nome price pages_i Autor
978-0641723445 O ladrão de raios 12.5 384 Rick Riordan
978-1423103349 O mar de monstros 6,49 304 Rick Riordan
978-1857995879 Sophie's World: The Greek Philosophers (O mundo de Sofia: os filósofos gregos) 3.07 64 Jostein Gaarder
978-1933988177 Lucene em ação, Second Edition 30,5 475 Michael McCandless

Agora você pode retornar esta tabela ao usuário ou carregar os dados em outra tabela.

Confira também