Partilhar via

Importar documentos JSON para o SQL Server

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL Database Azure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

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.

Os exemplos neste artigo usam um arquivo JSON de um exemplo do GitHub contendo uma lista de livros.

Permissions

No nível da instância, este recurso requer ser membro da função de servidor fixa bulkadmin, ou possuir permissões de ADMINISTER BULK OPERATIONS.

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

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

Importar um documento JSON para uma única coluna

OPENROWSET(BULK) é uma função com valor de tabela que pode ler dados de qualquer ficheiro na unidade de disco local ou na rede, se o SQL Server tiver acesso de leitura a essa localização. Ele retorna uma tabela com uma única coluna que contém o conteúdo do arquivo. Existem várias opções que você pode usar 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 único valor grande é 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 o retorna ao 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.

Você também pode carregar o conteúdo do arquivo em uma variável local ou em 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 Armazenamento de Arquivos do Azure

Você também pode usar OPENROWSET(BULK) conforme descrito anteriormente para ler arquivos JSON de outros locais de arquivos que o SQL Server pode acessar. Por exemplo, o Armazenamento de Arquivos do Azure dá suporte ao protocolo SMB. Como resultado, você pode mapear uma unidade virtual local para o compartilhamento de armazenamento de arquivos do Azure usando o seguinte procedimento:

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

  2. Carregue alguns ficheiros JSON na partilha de armazenamento de ficheiros.

  3. Crie uma regra de firewall de saída no Firewall do Windows no seu computador que permita a porta 445. O seu fornecedor de serviços de Internet poderá bloquear esta porta. Se você receber um erro de DNS (erro 53) na etapa a seguir, a porta 445 não está aberta ou o seu ISP está bloqueando-a.

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

    Aqui está a sintaxe do comando:

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

    Aqui está um exemplo que atribui a letra de unidade local T: ao compartilhamento do Armazenamento de Arquivos do Azure:

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

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

  5. Agora você pode acessar seus 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 obter mais informações sobre o Armazenamento de Arquivos do Azure, consulte Armazenamento de Arquivos.

Importar documentos JSON do Armazenamento de Blobs do Azure

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

Você pode carregar arquivos diretamente no Banco de Dados SQL do Azure a partir 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, conforme 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 ficheiro JSON inteiro como um único valor, convém analisá-lo e retornar os livros presentes no ficheiro e suas propriedades em formato de linhas e colunas.

Exemplo 1

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

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

A OPENROWSET anterior lê um único valor de texto do arquivo. OPENROWSET retorna o valor como BulkColumn e passa BulkColumn para a função OPENJSON. OPENJSON itera através da matriz de objetos JSON na matriz BulkColumn e retorna um livro em cada linha. Cada linha é formatada como JSON, mostrada a seguir.

{"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 JSON e transformá-lo em uma tabela ou 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. OPENJSON corresponde às propriedades nos objetos JSON usando nomes de coluna. Por exemplo, a propriedade price é retornada como uma coluna price e convertida para o tipo de dados float. Eis os resultados:

Id Name price pages_i Author
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 O Mundo de Sofia : Os Filósofos Gregos 3.07 64 Jostein Gaarder
978-1933988177 Lucene em Ação, Segunda Edição 30.5 475 Michael McCandless

Agora você pode retornar essa tabela para o usuário ou carregar os dados em outra tabela.

Conteúdo relacionado

  • Last updated on