Używanie metadanych plików w zapytaniach bezserwerowej puli SQL

Bezserwerowa pula SQL może adresować wiele plików i folderów zgodnie z opisem w artykule Query folders and multiple files (Foldery zapytań i wiele plików ). Z tego artykułu dowiesz się, jak używać informacji o metadanych dotyczących nazw plików i folderów w zapytaniach.

Czasami może być konieczne określenie, które źródło pliku lub folderu jest skorelowane z określonym wierszem w zestawie wyników.

Możesz użyć funkcji filepath i filename zwrócić nazwy plików i/lub ścieżkę w zestawie wyników. Możesz też użyć ich do filtrowania danych na podstawie nazwy pliku i/lub ścieżki folderu. Te funkcje opisano w funkcji nazwy pliku sekcji składni i funkcji filepath. W poniższych sekcjach znajdziesz krótkie opisy wraz z przykładami.

Wymagania wstępne

Pierwszym krokiem jest utworzenie bazy danych ze źródłem danych, które odwołuje się do konta magazynu. Następnie zainicjuj obiekty, wykonując skrypt instalacyjny w tej bazie danych. Ten skrypt instalacyjny utworzy źródła danych, poświadczenia o zakresie bazy danych i zewnętrzne formaty plików, które są używane w tych przykładach.

Funkcje

Nazwa pliku

Ta funkcja zwraca nazwę pliku, z którego pochodzi wiersz.

Poniższy przykład odczytuje pliki danych żółtych taksówek NYC we wrześniu 2017 r. i zwraca liczbę przejazdów na plik. Część OPENROWSET zapytania określa, które pliki będą odczytywane.

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();

W poniższym przykładzie pokazano, jak można użyć nazwy pliku() w klauzuli WHERE do filtrowania plików do odczytu. Uzyskuje dostęp do całego folderu w części OPENROWSET zapytania i filtruje pliki w klauzuli WHERE.

Wyniki będą takie same jak w poprzednim przykładzie.

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];

Ścieżka pliku

Funkcja filepath zwraca pełną lub częściową ścieżkę:

• Po wywołaniu bez parametru zwraca pełną ścieżkę pliku, z którego pochodzi wiersz. Gdy DATA_SOURCE jest używana w systemie OPENROWSET, zwraca ścieżkę względem DATA_SOURCE.
• Po wywołaniu za pomocą parametru zwraca część ścieżki zgodnej z symbolem wieloznacznymi na pozycji określonej w parametrze . Na przykład wartość parametru 1 zwróci część ścieżki zgodnej z pierwszym symbolem wieloznacznymi.

Poniższy przykład odczytuje pliki danych NYC Yellow Taxi w ciągu ostatnich trzech miesięcy 2017 roku. Zwraca liczbę przejazdów na ścieżkę pliku. Część OPENROWSET zapytania określa, które pliki będą odczytywane.

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;

W poniższym przykładzie pokazano, jak można użyć metody filepath() w klauzuli WHERE do filtrowania plików do odczytu.

Symbole wieloznaczne można używać w części OPENROWSET zapytania i filtrować pliki w klauzuli WHERE. Wyniki będą takie same jak w poprzednim przykładzie.

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;

Następne kroki

W następnym artykule dowiesz się, jak wykonywać zapytania dotyczące plików Parquet.