Dotazování souborů pomocí bezserverového fondu SQL

10 min

Bezserverový fond SQL můžete použít k dotazování datových souborů v různých běžných formátech souborů, mezi které patří:

Text s oddělovači, například soubory hodnot oddělených čárkami (CSV).
Soubory zápisu objektů JavaScriptu (JSON).
Soubory Parquet.

Základní syntaxe pro dotazování je stejná pro všechny tyto typy souborů a je postavena na funkci OPENROWSET SQL; který generuje tabulkovou sadu řádků z dat v jednom nebo více souborech. Například následující dotaz se dá použít k extrakci dat ze souborů CSV.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

Funkce OPENROWSET obsahuje další parametry, které určují faktory, jako jsou:

Schéma výsledné sady řádků
Další možnosti formátování pro textové soubory s oddělovači

Tip

Úplnou syntaxi funkce OPENROWSET najdete v dokumentaci k Azure Synapse Analytics.

Výstup z OPENROWSET je sada řádků, ke které musí být přiřazen alias. V předchozím příkladu se řádky aliasu používají k pojmenování výsledné sady řádků.

Parametr BULK obsahuje úplnou adresu URL umístění v datovém jezeře obsahující datové soubory. Může se jednat o jednotlivé soubory nebo složku s výrazem se zástupným znakem pro filtrování typů souborů, které by měly být zahrnuty. Parametr FORMAT určuje typ dotazovaných dat. Výše uvedený příklad čte text s oddělovači ze všech .csv souborů ve složce souborů .

Poznámka:

Tento příklad předpokládá, že uživatel má přístup k souborům v podkladovém úložišti, pokud jsou soubory chráněné pomocí klíče SAS nebo vlastní identity, budete muset vytvořit přihlašovací údaje v oboru serveru.

Jak je vidět v předchozím příkladu, můžete k zahrnutí nebo vyloučení souborů v dotazu použít zástupné znamény v parametru BULK . Následující seznam ukazuje několik příkladů, jak se dá použít:

https://mydatalake.blob.core.windows.net/data/files/file1.csv: Do složky souborů zahrňte pouze file1.csv.
https://mydatalake.blob.core.windows.net/data/files/file*.csv: Všechny .csv soubory ve složce souborů s názvy, které začínají na "soubor".
https://mydatalake.blob.core.windows.net/data/files/*: Všechny soubory ve složce souborů .
https://mydatalake.blob.core.windows.net/data/files/**: Všechny soubory ve složce souborů a rekurzivně její podsložky.

V parametru BULK můžete také zadat více cest k souborům, přičemž každou cestu oddělíte čárkou.

Dotazování textových souborů s oddělovači

Textové soubory s oddělovači jsou běžným formátem souborů v mnoha firmách. Konkrétní formátování použité v souborech s oddělovači se může lišit, například:

S řádkem záhlaví a bez.
Hodnoty oddělené čárkami a tabulátory
Konce čar ve stylu Windows a Unix.
Neuvozované a citované hodnoty a znaky pro escaping.

Bez ohledu na typ souboru s oddělovači, který používáte, můžete z nich číst data pomocí funkce OPENROWSET s parametrem CSV FORMAT a dalších parametrů, které jsou potřeba ke zpracování konkrétních podrobností formátování dat. Příklad:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

PARSER_VERSION slouží k určení toho, jak dotaz interpretuje kódování textu použitého v souborech. Verze 1.0 je výchozí a podporuje širokou škálu kódování souborů, zatímco verze 2.0 podporuje méně kódování, ale nabízí lepší výkon. Parametr FIRSTROW slouží ke přeskočení řádků v textovém souboru, k odstranění nestrukturovaného preambule textu nebo k ignorování řádku obsahujícího záhlaví sloupců.

Mezi další parametry, které můžete vyžadovat při práci s textovými soubory s oddělovači, patří:

FIELDTERMINATOR – znak použitý k oddělení hodnot polí v každém řádku. Například soubor oddělený tabulátorem odděluje pole znakem TAB (\t). Výchozí ukončovací znak pole je čárka (,).
ROWTERMINATOR – znak použitý k označení konce řádku dat. Například standardní textový soubor Systému Windows používá kombinaci návratu na začátek řádku (CR) a odřádkování (LF), který je označen kódem \n; zatímco textové soubory ve stylu systém UNIX používají jeden znak odřádkování, který lze určit pomocí kódu 0x0a.
FIELDQUOTE – znak použitý k uzavření řetězcových hodnot v uvozových hodnotách. Pokud například chcete zajistit, aby čárka v hodnotě pole adresy 126 Main St, apt 2 nebyla interpretována jako oddělovač polí, můžete celou hodnotu pole uzavřít do uvozovek takto: "126 Main St, apt 2". Dvojitá uvozovka (") je výchozí znak uvozovky pole.

Tip

Podrobnosti o dalších parametrech při práci s textovými soubory s oddělovači najdete v dokumentaci k Azure Synapse Analytics.

Určení schématu sady řádků

Pro textové soubory s oddělovači je běžné zahrnout názvy sloupců do prvního řádku. Funkce OPENROWSET tuto funkci může použít k definování schématu výsledné sady řádků a automatickému odvození datových typů sloupců na základě hodnot, které obsahují. Představte si například následující text s oddělovači:

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

Data se skládají z následujících tří sloupců:

product_id (celé číslo)
product_name (řetězec)
list_price (desetinné číslo)

Pomocí následujícího dotazu můžete extrahovat data se správnými názvy sloupců a odpovídajícím způsobem odvozené datové typy SQL Serveru (v tomto případě INT, NVARCHAR a DECIMAL).

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

Parametr HEADER_ROW (který je k dispozici pouze při použití analyzátoru verze 2.0) dává dotazovacímu modulu pokyn, aby jako názvy sloupců použil první řádek dat v každém souboru:

product_id	product_name	list_price
123	Widget	12.9900
124	Gadget	3.9900

Teď zvažte následující data:

123,Widget,12.99
124,Gadget,3.99

Tentokrát soubor neobsahuje názvy sloupců v řádku záhlaví; zatímco datové typy mohou být stále odvozeny, názvy sloupců budou nastaveny na C1, C2, C3 atd.

S1	C2	C3
123	Widget	12.9900
124	Gadget	3.9900

Pokud chcete zadat explicitní názvy sloupců a datové typy, můžete přepsat výchozí názvy sloupců a odvozené datové typy zadáním definice schématu v klauzuli WITH , například takto:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

Tento dotaz vytvoří očekávané výsledky:

product_id	product_name	list_price
123	Widget	12.99
124	Gadget	3.99

Tip

Při práci s textovými soubory můžete zaznamenat nekompatibilitu s daty zakódovanými UTF-8 a kolací používanou v hlavní databázi pro bezserverový fond SQL. Chcete-li to překonat, můžete určit kompatibilní kolaci pro jednotlivé sloupce VARCHAR ve schématu. Další podrobnosti najdete v doprovodných materiálech k řešení potíží.

Dotazování souborů JSON

JSON je oblíbený formát pro webové aplikace, které vyměňují data prostřednictvím rozhraní REST nebo používají úložiště dat NoSQL, jako je Azure Cosmos DB. Není tedy neobvyklé uchovávat data jako dokumenty JSON v souborech v datovém jezeře pro účely analýzy.

Například soubor JSON, který definuje jednotlivé produkty, může vypadat takto:

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Pokud chcete vrátit data o produktech ze složky obsahující více souborů JSON v tomto formátu, můžete použít následující dotaz SQL:

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET nemá pro soubory JSON žádný konkrétní formát, takže je nutné použít formát CSV s FIELDTERMINATOR, FIELDQUOTE a ROWTERMINATOR nastaveným na 0x0b a schéma, které obsahuje jeden sloupec NVARCHAR(MAX). Výsledkem tohoto dotazu je sada řádků obsahující jeden sloupec dokumentů JSON, například takto:

Doc
{"product_id":123;"product_name":"Widget","list_price": 12,99}
{"product_id":124,"product_name":"Miniaplikace","list_price": 3,99}

K extrahování jednotlivých hodnot z JSON můžete použít funkci JSON_VALUE v příkazu SELECT, jak je znázorněno tady:

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Tento dotaz by vrátil sadu řádků podobnou následujícím výsledkům:

product	price
Widget	12.99
Gadget	3.99

Dotazování souborů Parquet

Parquet je běžně používaný formát pro zpracování velkých objemů dat v distribuovaném úložišti souborů. Jedná se o efektivní datový formát, který je optimalizovaný pro kompresi a analytické dotazování.

Ve většině případů je schéma dat vloženo do souboru Parquet, takže stačí zadat parametr BULK s cestou k souborům, které chcete číst, a parametr FORMAT parquet, například takto:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Dotazování na dělená data

V datovém jezeře se data dělí rozdělením mezi několik souborů v podsložkách, které odrážejí kritéria dělení. To umožňuje distribuované systémy zpracování pracovat paralelně na několika oddílech dat nebo snadno eliminovat čtení dat z konkrétních složek na základě kritérií filtrování. Předpokládejme například, že potřebujete efektivně zpracovávat data prodejních objednávek a často je potřeba filtrovat podle roku a měsíce, ve kterém byly objednávky umístěny. Data můžete rozdělit do oddílů pomocí složek, například takto:

/Objednávky
- /year=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

Pokud chcete vytvořit dotaz, který filtruje výsledky tak, aby zahrnoval pouze objednávky pro leden a únor 2020, můžete použít následující kód:

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

Parametry očíslované cesty k souboru v klauzuli WHERE odkazují na zástupné cardy v názvech složek v cestě BULK -so parametr 1 je * v názvu složky year=* a parametr 2 je * v názvu složky month=* .

Pokračovat