Использование метаданных файлов в запросах к бессерверному пулу SQL

Из этой статьи вы узнаете, как запрашивать определенные файлы или папки с помощью метаданных. Бессерверный пул SQL может обращаться к нескольким файлам и папкам. Для получения дополнительной информации см. Запрос папок и множественных файлов.

Иногда может потребоваться знать, какой файл или источник папок сопоставляется с определенной строкой в результирующем наборе. Вы можете использовать функции filepath и filename для возврата имен файлов и/или пути в результирующем наборе, или для фильтрации данных на основе имени файла или пути к папке. Эти функции описаны в функциях имени файла и файлового пути.

В следующих разделах приведены краткие описания и примеры кода.

Предварительные условия

Первым шагом является создание базы данных с источником данных, ссылающимся на учетную запись хранения. Затем инициализируйте объекты, выполнив скрипт установки в данной базе данных. Этот скрипт установки создает источники данных, учетные данные базы данных и форматы внешних файлов, которые используются в этих примерах.

Функции

Имя файла

Функция 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 возвращает полный или частичный путь:

• При вызове без параметра возвращается полный путь к файлу, из которого происходит строка. Когда 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;

Следующий шаг