Consulta de carpetas y varios archivos
En este artículo, aprenderá a escribir una consulta mediante un grupo de SQL sin servidor en Azure Synapse Analytics.
El grupo de SQL sin servidor admite la lectura de varios archivos o carpetas mediante caracteres comodín, que son similares a los caracteres comodín usados en Windows. Sin embargo, existe una mayor flexibilidad, ya que se permiten varios caracteres comodín.
Requisitos previos
El primer paso es crear una base de datos donde puede ejecutar las consultas. A continuación, inicialice los objetos ejecutando un script de configuración en esa base de datos. Este script de instalación crea 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.
Use la carpeta csv/taxi para seguir las consultas de ejemplo. Contiene NYC Taxi - Yellow Taxi Trip Records datos de julio de 2016 a junio de 2018. Los archivos de csv/taxi se denominan año y mes con el siguiente patrón:
yellow_tripdata_<year>-<month>.csv*
Lectura de todos los archivos de una carpeta
En el ejemplo siguiente se leen todos los archivos de datos NYC Yellow Taxi de la carpeta csv/taxi y, a continuación, se devuelve el número total de pasajeros y viajes al año. También muestra el uso de las funciones de agregado.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/taxi/*.csv',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
pickup_datetime DATETIME2 2,
passenger_count INT 4
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Nota:
Todos los archivos a los que se accede con el único OPENROWSET deben tener la misma estructura (número de columnas y sus tipos de datos).
Lectura de subconjunto de archivos en una carpeta
En el ejemplo siguiente se leen los archivos de datos 2017 NYC Yellow Taxi de la carpeta csv/taxi mediante un carácter comodín y se devuelve el importe total de la tarifa por tipo de pago.
SELECT
payment_type,
SUM(fare_amount) AS fare_total
FROM OPENROWSET(
BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
payment_type INT 10,
fare_amount FLOAT 11
) AS nyc
GROUP BY payment_type
ORDER BY payment_type;
Nota:
Todos los archivos a los que se accede con el único OPENROWSET deben tener la misma estructura (número de columnas y sus tipos de datos).
Lectura de subconjunto de archivos en una carpeta mediante varias rutas de acceso de archivo
En el ejemplo siguiente se leen los archivos de datos NYC Yellow Taxi de 2017 de la carpeta csv/taxi mediante dos rutas de acceso de archivo. La primera usa la ruta de acceso completa al archivo que contiene datos del mes de enero y el segundo usa un carácter comodín para leer los meses de octubre, noviembrey diciembre. Para cada ruta de acceso, se devuelve el importe total de la tarifa por tipo de pago.
SELECT
payment_type,
SUM(fare_amount) AS fare_total
FROM OPENROWSET(
BULK (
'csv/taxi/yellow_tripdata_2017-01.csv',
'csv/taxi/yellow_tripdata_2017-1*.csv'
),
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
payment_type INT 10,
fare_amount FLOAT 11
) AS nyc
GROUP BY payment_type
ORDER BY payment_type;
Nota:
Todos los archivos a los que se accede con el único OPENROWSET deben tener la misma estructura (número de columnas y sus tipos de datos).
Lectura de carpetas
La ruta de acceso que proporcione a OPENROWSET también puede ser una ruta de acceso a una carpeta. En las siguientes secciones se incluyen estos tipos de consultas.
Lectura de todos los archivos de una carpeta específica
Puede leer todos los archivos de una carpeta mediante el comodín de nivel de archivo como se muestra en Leer todos los archivos de la carpeta. Sin embargo, hay una manera de consultar una carpeta y consumir todos los archivos de esa carpeta.
Si la ruta de acceso proporcionada en OPENROWSET apunta a una carpeta, todos los archivos de esa carpeta se usan como origen para la consulta. La consulta siguiente lee todos los archivos de la carpeta csv/taxi.
Nota:
Tenga en cuenta la existencia del / al final de la ruta de acceso de la consulta. Denota una carpeta. Si se omite el /, la consulta tiene como destino un archivo denominado taxi en su lugar.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/taxi/',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_datetime DATETIME2,
dropoff_datetime DATETIME2,
passenger_count INT,
trip_distance FLOAT,
rate_code INT,
store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_location_id INT,
dropoff_location_id INT,
payment_type INT,
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
improvement_surcharge FLOAT,
total_amount FLOAT
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Nota:
Todos los archivos a los que se accede con el único OPENROWSET deben tener la misma estructura (número de columnas y sus tipos de datos).
Lectura de todos los archivos de varias carpetas
Es posible leer archivos de varias carpetas mediante un carácter comodín. La consulta siguiente lee todos los archivos de todas las carpetas ubicadas en la carpeta csv que tienen nombres que comienzan por t y terminan con i.
Nota:
Tenga en cuenta la existencia del / al final de la ruta de acceso de la consulta. Denota una carpeta. Si se omite el /, la consulta tiene como destino los archivos denominados t*i en su lugar.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/t*i/',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_datetime DATETIME2,
dropoff_datetime DATETIME2,
passenger_count INT,
trip_distance FLOAT,
rate_code INT,
store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_location_id INT,
dropoff_location_id INT,
payment_type INT,
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
improvement_surcharge FLOAT,
total_amount FLOAT
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Nota:
Todos los archivos a los que se accede con el único OPENROWSET deben tener la misma estructura (número de columnas y sus tipos de datos).
Puesto que solo tiene una carpeta que coincida con los criterios, el resultado de la consulta es el mismo que Lectura de todos los archivos de una carpeta.
Atravesar carpetas de forma recursiva
El grupo de SQL sin servidor puede recorrer de forma recursiva carpetas si especifica /** al final de la ruta de acceso. La consulta siguiente lee todos los archivos de todas las carpetas y subcarpetas que se encuentran en la carpeta csv/taxi.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/taxi/**',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_datetime DATETIME2,
dropoff_datetime DATETIME2,
passenger_count INT,
trip_distance FLOAT,
rate_code INT,
store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_location_id INT,
dropoff_location_id INT,
payment_type INT,
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
improvement_surcharge FLOAT,
total_amount FLOAT
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Nota:
Todos los archivos a los que se accede con el único OPENROWSET deben tener la misma estructura (número de columnas y sus tipos de datos).
Uso de varios caracteres comodín
Puede usar varios caracteres comodín en diferentes niveles de la ruta de acceso. Por ejemplo, puede enriquecer una consulta anterior para leer solo archivos con datos de 2017, desde todas las carpetas donde los nombres comienzan por t y terminan con i.
Nota:
Tenga en cuenta la existencia del / al final de la ruta de acceso de la consulta. Denota una carpeta. Si se omite el /, la consulta tiene como destino los archivos denominados t*i en su lugar.
Hay un límite máximo de 10 caracteres comodín por consulta.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/t*i/yellow_tripdata_2017-*.csv',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_datetime DATETIME2,
dropoff_datetime DATETIME2,
passenger_count INT,
trip_distance FLOAT,
rate_code INT,
store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_location_id INT,
dropoff_location_id INT,
payment_type INT,
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
improvement_surcharge FLOAT,
total_amount FLOAT
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Nota:
Todos los archivos a los que se accede con el único OPENROWSET deben tener la misma estructura (número de columnas y sus tipos de datos).
Puesto que solo tiene una carpeta que coincida con los criterios, el resultado de la consulta es el mismo que Lectura de subconjunto de archivos en una carpeta y Lectura de todos los archivos de una carpeta específica. Para ver escenarios de uso de caracteres comodín más complejos, consulte Consultar archivos Parquet.