Uso de metadatos de archivo en consultas de grupo de SQL sin servidor

En este artículo, aprenderá a consultar archivos o carpetas específicos mediante metadatos. El grupo de SQL sin servidor puede abordar varios archivos y carpetas. Para obtener más información, consulte Consultar carpetas y varios archivos.

A veces, puede que necesite saber qué origen de archivo o carpeta se correlaciona con una fila específica en el conjunto de resultados. Puede usar las funciones filepath y filename para devolver los nombres de archivo o la ruta de acceso en el conjunto de resultados, o puede usarlos para filtrar los datos en función del nombre de archivo o la ruta de acceso de la carpeta. Estas funciones se describen en las secciones función filename y función filepath.

En las secciones siguientes se proporcionan descripciones breves y ejemplos de código.

Requisitos previos

El primer paso es crear una base de datos con un origen de datos que haga referencia a la cuenta de almacenamiento. Luego, se inicializan los objetos ejecutando un script de configuración en esa base de datos. Este script de configuración creará los orígenes de datos, las credenciales con ámbito de base de datos y los formatos de archivo externos que se usan en estos ejemplos.

Funciones

Nombre de archivo

La función filename devuelve el nombre de archivo del que se origina la fila.

En el ejemplo siguiente se leen los archivos de datos de NYC Yellow Taxi de septiembre de 2017 y se devuelve el número de carreras por archivo. La parte OPENROWSET de la consulta especifica qué archivos se leerán.

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

En el ejemplo siguiente se muestra cómo se puede usar filename() en la cláusula WHERE para filtrar los archivos que se van a leer. Accede a toda la carpeta en la parte de OPENROWSET de la consulta y filtra los archivos en la cláusula WHERE.

Los resultados serán los mismos que en el ejemplo anterior.

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

Filepath

La función filepath devuelve una ruta de acceso completa o parcial:

• Cuando se la llama sin ningún parámetro, devuelve la ruta de acceso completa al archivo del que se origina la fila. Cuando se usa DATA_SOURCE en OPENROWSET, devuelve la ruta de acceso relativa a DATA_SOURCE.
• Cuando se la llama con un parámetro, devuelve la parte de la ruta de acceso que coincide con el carácter comodín en la posición especificada del parámetro. Por ejemplo, el valor 1 del parámetro devolvería la parte de la ruta de acceso que coincida con el primer carácter comodín.

En el ejemplo siguiente se leen los archivos de datos de NYC Yellow Taxi para los últimos tres meses de 2017. Devuelve el número de carreras por ruta de acceso de archivo. La parte OPENROWSET de la consulta especifica qué archivos se leerán.

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

En el ejemplo siguiente se muestra cómo se puede usar filepath() en la cláusula WHERE para filtrar los archivos que se van a leer.

Puede usar los caracteres comodín en la parte de OPENROWSET de la consulta y filtrar los archivos en la cláusula WHERE. Los resultados serán los mismos que en el ejemplo anterior.

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',        
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Paso siguiente

Consulta de archivos de Parquet mediante un grupo de SQL sin servidor