Importar documentos JSON en SQL Server

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed Instance

En este artículo se describe cómo importar archivos JSON en SQL Server. Los documentos JSON almacenan muchos tipos de datos, por ejemplo, registros de aplicaciones, datos del sensor, etc. Es importante ser capaz de leer los datos JSON almacenados en archivos, cargar los datos en SQL Server y analizarlos.

Permisos

En el nivel de instancia, esta característica requiere la pertenencia al rol fijo de servidor bulkadmin o a los permisos ADMINISTER BULK OPERATIONS.

Para el nivel de base de datos, esta característica requiere permisos ADMINISTER DATABASE BULK OPERATIONS.

El acceso a Azure Blob Storage requiere acceso de lectura y escritura.

Importar un documento JSON en una sola columna

OPENROWSET(BULK) es una función con valores de tabla que puede leer datos de cualquier archivo que se encuentre en la unidad local o la red, si SQL Server tiene acceso de lectura a esa ubicación. Devuelve una tabla con una sola columna con el contenido del archivo. Hay varias opciones que puedes utilizar con la función OPENROWSET(BULK), como pueden ser los separadores. Pero en el caso más simple, solamente puede cargar todo el contenido de un archivo como un valor de texto. (Este valor grande único se conoce como un objeto grande de carácter único o SINGLE_CLOB).

Este es un ejemplo de la función OPENROWSET(BULK) que lee el contenido de un archivo JSON y lo devuelve al usuario como un valor único:

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

OPENJSON(BULK) lee el contenido del archivo y lo devuelve en BulkColumn.

También puede cargar el contenido del archivo en una variable local o en una tabla, como se muestra en el ejemplo siguiente:

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

Después de cargar el contenido del archivo JSON, puede guardar el texto JSON en una tabla.

Importar documentos JSON desde Azure File Storage

También puedes usar la opción OPENROWSET(BULK) como se ha descrito anteriormente para leer archivos JSON de otras ubicaciones de archivo a las que SQL Server puede acceder. Por ejemplo, Azure File Storage admite el protocolo SMB. Como resultado, puede asignar una unidad virtual local al recurso compartido de Azure File Storage mediante el procedimiento siguiente:

  1. Cree una cuenta de almacenamiento de archivos (por ejemplo, mystorage), un recurso compartido de archivos (por ejemplo, sharejson) y una carpeta de archivos en Azure File Storage mediante Azure Portal o Azure PowerShell.

  2. Cargue algunos archivos JSON en el recurso compartido de almacenamiento de archivos.

  3. Cree una regla de firewall de salida en el Firewall de Windows en el equipo que permite el puerto 445. Tu proveedor de servicio de Internet puede bloquear este puerto. Si recibes un error DNS (error 53) en el paso siguiente, es que no has abierto el puerto 445 o su ISP lo bloquea.

  4. Monte el recurso compartido de Azure File Storage como una unidad local (por ejemplo, T:).

    La sintaxis del comando es la siguiente:

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

    Aquí tiene un ejemplo que asigna la letra de unidad local T: al recurso compartido de Azure File Storage:

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

    Puede encontrar la clave de la cuenta de almacenamiento y la clave de acceso de la cuenta de almacenamiento principal o secundaria en la sección Claves de Configuración en Azure Portal.

  5. Ahora puede acceder a los archivos JSON desde el recurso compartido de Azure File Storage mediante la unidad asignada, tal como se muestra en el ejemplo siguiente:

    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 más información sobre Azure File Storage, vea File Storage.

Importar documentos JSON desde Azure Blob Storage

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores, y Azure SQL

Puedes cargar archivos directamente en Azure SQL Database desde Azure Blob Storage con el comando BULK INSERT de T-SQL o la función OPENROWSET.

En primer lugar, cree un origen de datos externo, como se muestra en el ejemplo siguiente.

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

A continuación, ejecute un comando BULK INSERT con la opción DATA_SOURCE.

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

Analizar documentos JSON en filas y columnas

En lugar de leer un archivo completo de JSON como un valor único, puede interesarle analizarlo y devolver los libros del archivo y sus propiedades en filas y columnas. En este ejemplo se usa un archivo JSON de este sitio que contiene una lista de libros.

Ejemplo 1

En el ejemplo más simple, solo puede cargar toda la lista desde el archivo.

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

La función OPENROWSET anterior lee un solo valor de texto del archivo. OPENROWSET devuelve el valor como BulkColumn y pasa BulkColumn a la función OPENJSON. OPENJSON itera a través de la matriz de objetos JSON de la matriz BulkColumn y devuelve un libro en cada fila. Cada fila tiene formato JSON, como se muestra a continuación.

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

Ejemplo 2

La función OPENJSON puede analizar el contenido JSON y transformarlo en una tabla o un conjunto de resultados. En el ejemplo siguiente se carga el contenido, se analiza el JSON cargado y se devuelven los cinco campos como columnas:

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;

En este ejemplo, OPENROWSET(BULK) lee el contenido del archivo y pasa ese contenido a la función OPENJSON con un esquema definido para la salida. OPENJSON hace coincidir las propiedades de los objetos JSON utilizando nombres de columna. Por ejemplo, la propiedad price se devuelve como una columna price y se convierte al tipo de datos float. He aquí los resultados:

Identificador Nombre price pages_i Autor
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

Ahora puede devolver esta tabla al usuario o cargar los datos en otra tabla.

Consulte también