Importar documentos JSON para o SQL ServerImport JSON documents into SQL Server

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Este artigo descreve como importar arquivos JSON para o SQL Server.This article describes how to import JSON files into SQL Server. No momento, há muitos documentos JSON armazenados em arquivos.Currently there are lots of JSON documents stored in files. Informações de log de aplicativos em arquivos JSON, sensores que geram informações são armazenadas em arquivos JSON e assim por diante.Applications log information in JSON files, sensors generate information that's stored in JSON files, and so forth. É importante ser capaz de ler os dados JSON armazenados em arquivos, carregar os dados no SQL Server e analisá-los.It's important to be able to read the JSON data stored in files, load the data into SQL Server, and analyze it.

Importar um documento JSON em uma única colunaImport a JSON document into a single column

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.OPENROWSET(BULK) is a table-valued function that can read data from any file on the local drive or network, if SQL Server has read access to that location. Ela retorna uma tabela com uma única coluna com o conteúdo do arquivo.It returns a table with a single column that contains the contents of the file. Há várias opções que podem ser usadas com a função OPENROWSET(BULK), como separadores.There are various options that you can use with the OPENROWSET(BULK) function, such as separators. Mas, no caso mais simples, você pode simplesmente carregar todo o conteúdo de um arquivo como um valor de texto.But in the simplest case, you can just load the entire contents of a file as a text value. (Esse valor grande único é conhecido como um objeto grande de caractere único ou SINGLE_CLOB.)(This single large value is known as a single character large object, or 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:Here's an example of the OPENROWSET(BULK) function that reads the contents of a JSON file and returns it to the user as a single value:

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.OPENJSON(BULK) reads the content of the file and returns it in BulkColumn.

Também é possível carregar o conteúdo do arquivo para uma variável local ou uma tabela, conforme mostrado no exemplo a seguir:You can also load the contents of the file into a local variable or into a table, as shown in the following example:

-- Load file contents into a variable
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.After loading the contents of the JSON file, you can save the JSON text in a table.

Importar vários documentos JSONImport multiple JSON documents

Use a mesma abordagem para carregar um conjunto de arquivos JSON do sistema de arquivos para variáveis locais um por vez.You can use the same approach to load a set of JSON files from the file system into a local variable one at a time. Suponha que os arquivos são nomeados como book<index>.json.Assume that the files are named book<index>.json.

DECLARE @i INT = 1
DECLARE @json AS NVARCHAR(MAX)

WHILE(@i < 10)
BEGIN
    SET @file = 'C:\JSON\Books\book' + cast(@i AS VARCHAR(5)) + '.json';
    SELECT @json = BulkColumn FROM OPENROWSET (BULK (@file), SINGLE_CLOB) AS j
    SELECT * FROM OPENJSON(@json) AS json
    -- Optionally, save the JSON text in a table.
    SET @i = @i + 1 ;
END

Importar documentos JSON do Azure File StorageImport JSON documents from 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.You can also use OPENROWSET(BULK) as described above to read JSON files from other file locations that SQL Server can access. Por exemplo, o Azure File Storage dá suporte ao protocolo SMB.For example, Azure File Storage supports the SMB protocol. Como resultado, você pode mapear uma unidade virtual local para o compartilhamento de armazenamento do Azure File usando o seguinte procedimento:As a result you can map a local virtual drive to the Azure File storage share using the following procedure:

  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.Create a file storage account (for example, mystorage), a file share (for example, sharejson), and a folder in Azure File Storage by using the Azure portal or Azure PowerShell.

  2. Carregar alguns arquivos JSON no compartilhamento de armazenamento de arquivos.Upload some JSON files to the file storage share.

  3. Crie uma regra de firewall de saída no Firewall do Windows no computador que permite a porta 445.Create an outbound firewall rule in Windows Firewall on your computer that allows port 445. Observe que o seu provedor de serviços de Internet pode bloquear essa porta.Note that your Internet service provider may block this port. 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.If you get a DNS error (error 53) in the following step, then you have not opened port 445, or your ISP is blocking it.

  4. Monte o compartilhamento do Armazenamento de Arquivos do Azure como uma unidade local (por exemplo, T:).Mount the Azure File Storage share as a local drive (for example T:).

    Esta é a sintaxe do comando:Here is the command syntax:

    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:Here's an example that assigns local drive letter T: to the Azure File Storage share:

    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.You can find the storage account key and the primary or secondary storage account access key in the Keys section of Settings in the Azure portal.

  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:Now you can access your JSON files from the Azure File Storage share by using the mapped drive, as shown in the following example:

    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.For more info about Azure File Storage, see File storage.

Importar documentos JSON do Armazenamento de Blobs do AzureImport JSON documents from Azure Blob Storage

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.You can load files directly into Azure SQL Database from Azure Blob Storage with the T-SQL BULK INSERT command or the OPENROWSET function.

Primeiro, crie uma fonte de dados externa, como mostrado no exemplo a seguir.First, create an external data source, as shown in the following example.

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.Next, run a BULK INSERT command with the DATA_SOURCE option.

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

Analisar documentos JSON em linhas e colunasParse JSON documents into rows and columns

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.Instead of reading an entire JSON file as a single value, you may want to parse it and return the books in the file and their properties in rows and columns. Este exemplo usa um arquivo JSON deste site que contém uma lista de livros.The following example uses a JSON file from this site containing a list of books.

Exemplo 1Example 1

No exemplo mais simples, você pode carregar apenas a lista inteira do arquivo.In the simplest example, you can just load the entire list from the file.

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

O OPENROWSET anterior lê um único valor de texto do arquivo.The preceding OPENROWSET reads a single text value from the file. O OPENROWSET retorna o valor como um BulkColumn e passa-o para a função OPENJSON.OPENROWSET returns the value as a BulkColumn, and passes BulkColumn to the OPENJSON function. O OPENJSON percorre a matriz de objetos JSON da matriz BulkColumn e retorna um livro em cada linha.OPENJSON iterates through the array of JSON objects in the BulkColumn array, and returns one book in each row. Cada linha é formatada como JSON, mostrado ao lado.Each row is formatted as JSON, shown next.

{"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 2Example 2

A função OPENJSON pode analisar o conteúdo do JSON e transformá-lo em uma tabela ou em um conjunto de resultados.The OPENJSON function can parse the JSON content and transform it into a table or a result set. O exemplo a seguir carrega o conteúdo, analisa o JSON carregado e retorna os cinco campos como colunas:The following example loads the content, parses the loaded JSON, and returns the five fields as columns:

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.In this example, OPENROWSET(BULK) reads the content of the file and passes that content to the OPENJSON function with a defined schema for the output. O OPENJSON corresponde a propriedades em objetos JSON usando nomes de coluna.OPENJSON matches properties in the JSON objects by using column names. Por exemplo, a propriedade price é retornada como uma coluna price e convertida para o tipo de dados float.For example, the price property is returned as a price column and converted to the float data type. Estes são os resultados:Here are the results:

IDId NomeName priceprice pages_ipages_i AutorAuthor
978-0641723445978-0641723445 O ladrão de raiosThe Lightning Thief 12,512.5 384384 Rick RiordanRick Riordan
978-1423103349978-1423103349 O mar de monstrosThe Sea of Monsters 6,496.49 304304 Rick RiordanRick Riordan
978-1857995879978-1857995879 O mundo de Sofia: Os filósofos gregosSophie's World : The Greek Philosophers 3.073.07 6464 Jostein GaarderJostein Gaarder
978-1933988177978-1933988177 Lucene em ação, Second EditionLucene in Action, Second Edition 30,530.5 475475 Michael McCandlessMichael McCandless

Agora você pode retornar esta tabela ao usuário ou carregar os dados em outra tabela.Now you can return this table to the user, or load the data into another table.

Saiba mais sobre JSON no SQL Server e no Banco de Dados SQL do AzureLearn more about JSON in SQL Server and Azure SQL Database

Vídeos da MicrosoftMicrosoft videos

Para obter uma introdução visual ao suporte interno para JSON no SQL Server e no Banco de Dados SQL do Azure, consulte os seguintes vídeos:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

Consulte TambémSee Also

Converter dados JSON em linhas e colunas com OPENJSONConvert JSON Data to Rows and Columns with OPENJSON