Consulta de archivos mediante un grupo de SQL sin servidor

  • 10 minutos

Puede usar un grupo de SQL sin servidor para consultar archivos de datos en varios formatos de archivo comunes, como:

  • Texto delimitado, como archivos csv (valores separados por comas).
  • Archivos de notación de objetos JavaScript (JSON).
  • Archivos de Parquet.

La sintaxis básica para realizar consultas es la misma para todos estos tipos de archivo y se basa en la función SQL OPENROWSET, que genera un conjunto de filas tabular a partir de datos en uno o varios archivos. Por ejemplo, la consulta siguiente podría usarse para extraer datos de archivos CSV.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

La función OPENROWSET incluye más parámetros que determinan factores como:

  • El esquema del conjunto de filas resultante.
  • Opciones de formato adicionales para archivos de texto delimitados.

Sugerencia

Encontrará la sintaxis completa de la función OPENROWSET en la documentación de Azure Synapse Analytics.

La salida de OPENROWSET es un conjunto de filas al que se debe asignar un alias. En el ejemplo anterior, las filas de alias se usaron para asignar un nombre al conjunto de filas resultante.

El parámetro BULK incluye la dirección URL completa a la ubicación del lago de datos que contiene los archivos de datos. Puede ser un archivo individual o una carpeta con una expresión comodín para filtrar los tipos de archivo que se deben incluir. El parámetro FORMAT especifica el tipo de datos que se consultan. El ejemplo anterior lee el texto delimitado de todos los archivos .csv de la carpeta de archivos .

Nota

En este ejemplo se supone que el usuario tiene acceso a los archivos en el almacén subyacente. Si los archivos están protegidos con una clave SAS o una identidad personalizada, tendría que crear una credencial de ámbito de servidor.

Como se muestra en el ejemplo anterior, puede usar caracteres comodín en el parámetro BULK para incluir o excluir archivos en la consulta. La siguiente lista muestra algunos ejemplos de cómo se pueden usar:

  • https://mydatalake.blob.core.windows.net/data/files/file1.csv: incluye solo file1.csv en la carpeta de archivos.
  • https://mydatalake.blob.core.windows.net/data/files/file*.csv: todos los archivos .csv en la carpeta de archivos con nombres que comienzan por "archivo".
  • https://mydatalake.blob.core.windows.net/data/files/*: todos los archivos de la carpeta de archivos.
  • https://mydatalake.blob.core.windows.net/data/files/**: todos los archivos de la carpeta de archivos, y recursivamente sus subcarpetas.

También puede especificar varias rutas de archivo en el parámetro BULK, separando cada ruta de acceso con una coma.

Consulta de archivos de texto delimitado

Los archivos de texto delimitado son un formato de archivo común en muchas empresas. El formato específico que se usa en los archivos delimitados puede variar, por ejemplo:

  • Con y sin fila de encabezado.
  • Valores delimitados por comas y tabulaciones.
  • Finales de línea de estilo Windows y Unix.
  • Valores sin comillas y entre comillas, y caracteres de escape.

Independientemente del tipo de archivo delimitado que use, puede leer sus datos mediante la función OPENROWSET con el parámetro csv FORMAT y otros parámetros según sea necesario para controlar los detalles de formato específicos de los datos. Por ejemplo:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

PARSER_VERSION se usa para determinar cómo interpreta la consulta la codificación de texto usada en los archivos. La versión 1.0 es la predeterminada y admite una amplia gama de codificaciones de archivos, mientras que la versión 2.0 admite menos codificaciones, pero ofrece un mejor rendimiento. El parámetro FIRSTROW se usa para omitir filas en el archivo de texto, para eliminar cualquier texto de preámbulo no estructurado o para omitir una fila que contiene los encabezados de columna.

Entre los parámetros adicionales que puede requerir al trabajar con archivos de texto delimitado se incluyen:

  • FIELDTERMINATOR: el carácter usado para separar los valores de campo en cada fila. Por ejemplo, un archivo delimitado por tabulaciones separa los campos con un carácter TAB (\t). El terminador de campo predeterminado es una coma (,).
  • ROWTERMINATOR: el carácter usado para indicar el final de una fila de datos. Por ejemplo, un archivo de texto de Windows estándar usa una combinación de un retorno de carro (CR) y un avance de línea (LF), indicados por el código \n, mientras que los archivos de texto de estilo UNIX utilizan un único carácter de avance de línea, que se puede indicar mediante el código 0x0a.
  • FIELDQUOTE: el carácter usado para escribir valores de cadena entre comillas. Por ejemplo, para asegurarse de que la coma del valor del campo de dirección 126 Main St, apt 2 no se interprete como un delimitador de campo, puede escribir el valor de campo completo entre comillas como en este ejemplo: "126 Main St, apt 2". Las comillas dobles (") son el carácter de comillas de campo predeterminado.

Sugerencia

Para obtener información detallada sobre parámetros adicionales al trabajar con archivos de texto delimitados, consulte la documentación de Azure Synapse Analytics.

Especificación del esquema de conjunto de filas

Es habitual que los archivos de texto delimitado incluyan los nombres de columna en la primera fila. La función OPENROWSET puede usarlo para definir el esquema del conjunto de filas resultante y deducir automáticamente los tipos de datos de las columnas en función de los valores que contienen. Por ejemplo, considere el siguiente texto delimitado:

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

Los datos constan de las tres columnas siguientes:

  • product_id (número entero)
  • product_name (cadena)
  • list_price (número decimal)

Podría usar la consulta siguiente para extraer los datos con los nombres de columna correctos y deducir adecuadamente los tipos de datos de SQL Server (en este caso INT, NVARCHAR y DECIMAL).

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

El parámetro HEADER_ROW (que solo está disponible cuando se usa la versión 2.0 del analizador) indica al motor de consulta que use la primera fila de datos de cada archivo como nombre de columna, como en este ejemplo:

product_id product_name list_price
123 Widget 12.9900
124 Gadget 3.9900

Ahora considere los siguientes datos:

123,Widget,12.99
124,Gadget,3.99

Esta vez, el archivo no contiene los nombres de columna en una fila de encabezado; por lo tanto, aunque los tipos de datos aún se pueden deducir, los nombres de columna se establecerán en C1, C2, C3, etc.

C1 C2 C3
123 Widget 12.9900
124 Gadget 3.9900

Para especificar nombres de columna y tipos de datos explícitos, puede invalidar los nombres de columna predeterminados y los tipos de datos deducidos proporcionando una definición de esquema en una cláusula WITH como esta:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

Esta consulta genera los resultados previstos:

product_id product_name list_price
123 Widget 12.99
124 Gadget 3,99

Sugerencia

Al trabajar con archivos de texto, es posible que encuentre alguna incompatibilidad con los datos codificados con UTF-8 y la intercalación usada en la base de datos maestra para el grupo de SQL sin servidor. Para solucionarlo, puede especificar una intercalación compatible para las columnas VARCHAR individuales del esquema. Consulte las instrucciones de solución de problemas para obtener más información.

Consulta de archivos JSON

JSON es un formato popular para aplicaciones web que intercambia datos a través de interfaces REST o usan almacenes de datos NoSQL como Azure Cosmos DB. Por lo tanto, no es raro conservar los datos como documentos JSON en archivos en un lago de datos para su análisis.

Por ejemplo, un archivo JSON que defina un producto individual podría tener el siguiente aspecto:

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Para devolver datos del producto desde una carpeta que contiene varios archivos JSON en este formato, podría usar la siguiente consulta de SQL:

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET no tiene ningún formato específico para archivos JSON, por lo que debe usar el formato csv con FIELDTERMINATOR, FIELDQUOTE y ROWTERMINATOR establecidos en 0x0b y un esquema que incluya una sola columna NVARCHAR(MAX). El resultado de esta consulta es un conjunto de filas que contiene una única columna de documentos JSON, como esta:

doc
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

Para extraer valores individuales del archivo JSON, puede usar la función JSON_VALUE en la instrucción SELECT, como se muestra aquí:

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Esta consulta devolverá un conjunto de filas similar a los resultados siguientes:

product price
Widget 12.99
Gadget 3,99

Consulta de archivos de Parquet

Parquet es un formato comúnmente utilizado para el procesamiento de big data en el almacenamiento de archivos distribuido. Es un formato de datos eficiente que está optimizado para la compresión y las consultas analíticas.

En la mayoría de los casos, el esquema de los datos se incrusta dentro del archivo Parquet, por lo que solo necesita especificar el parámetro BULK con una ruta de acceso a los archivos que desea leer y un parámetro FORMAT de parquet, por ejemplo:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Consulta de datos con particiones

Es habitual en un lago de datos particionar datos dividiendo varios archivos en subcarpetas que reflejen criterios de partición. Esto permite que los sistemas de procesamiento distribuido funcionen en paralelo en varias particiones de los datos o eliminen fácilmente las lecturas de datos de carpetas específicas en función de los criterios de filtrado. Por ejemplo, supongamos que necesita procesar de forma eficaz los datos de pedidos de ventas y, a menudo, necesita filtrar según el año y el mes en que se realizaron los pedidos. Puede particionar los datos mediante carpetas como en este ejemplo:

  • /orders
    • /year=2020
      • /month=1
        • /01012020.parquet
        • /02012020.parquet
        • ...
      • /month=2
        • /01022020.parquet
        • /02022020.parquet
        • ...
      • ...
    • /year=2021
      • /month=1
        • /01012021.parquet
        • /02012021.parquet
        • ...
      • ...

Para crear una consulta que filtre los resultados para incluir solo los pedidos de enero y febrero de 2020, puede usar el siguiente código:

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

Los parámetros de ruta de archivo numerados de la cláusula WHERE hacen referencia a los caracteres comodín en los nombres de carpeta en la ruta de acceso BULK, por lo que el parámetro 1 es el * en el nombre de carpeta year=* y el parámetro 2 es el * en el nombre de carpeta month=*.