Использование метаданных файлов в запросах к бессерверному пулу SQL
Из этой статьи вы узнаете, как запрашивать определенные файлы или папки с помощью метаданных. Бессерверный пул SQL может обращаться к нескольким файлам и папкам. Дополнительные сведения см. в статье "Запрос папок" и нескольких файлов.
Иногда может потребоваться знать, какой файл или источник папок сопоставляется с определенной строкой в результирующем наборе. Функции можно использовать filepath
и filename
возвращать имена файлов и (или) путь в результирующем наборе или использовать их для фильтрации данных на основе имени файла или пути к папке. Эти функции описаны в функции имени файла и функции filepath.
В следующих разделах приведены краткие описания и примеры кода.
Необходимые компоненты
Первым шагом является создание базы данных с источником данных, ссылающимся на учетную запись хранения. Затем инициализировать объекты путем выполнения скрипта установки в этой базе данных. Этот скрипт установки создает источники данных, учетные данные базы данных и форматы внешних файлов, которые используются в этих примерах.
Функции
Имя файла
Функция filename
возвращает имя файла, из которого исходит строка.
В следующем примере считываются файлы данных NYC Yellow Taxi в сентябре 2017 года и возвращаются количество поездок на файл. Часть OPENROWSET
запроса указывает, какие файлы считываются.
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();
В следующем примере показано, как filename()
можно использовать в WHERE
предложении для фильтрации файлов для чтения. Он обращается ко всей папке в OPENROWSET
части запроса и фильтрует файлы в предложении WHERE
.
Результаты будут такими же, как в предыдущем примере.
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
Функция filepath
возвращает полный или частичный путь:
- При вызове без параметра возвращается полный путь к файлу, из которого происходит строка. При
DATA_SOURCE
использованииOPENROWSET
он возвращает путь относительноDATA_SOURCE
. - При вызове с параметром она возвращает ту часть пути, которая соответствует подстановочному знаку в той позиции, которую определяет этот параметр. Например, значение параметра 1 возвращает часть пути, соответствующего первому подстановочным знаку.
Следующий пример считывает файлы данных о желтом такси Нью-Йорка за последние три месяца 2017 года. Он возвращает число поездок, сохраненных в каждом пути. Часть OPENROWSET
запроса указывает, какие файлы считываются.
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;
В следующем примере показано, как filepath()
можно использовать в WHERE
предложении для фильтрации файлов для чтения.
Вы можете использовать подстановочные знаки в OPENROWSET
части запроса и отфильтровать файлы в предложении WHERE
. Результаты будут такими же, как в предыдущем примере.
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;