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

Návod

Úplnou syntaxi funkce OPENROWSET najdete v dokumentaci 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 alias řádky používá 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 .

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 zahrnout jenom file1.csv.
https://mydatalake.blob.core.windows.net/data/files/file*.csv: Všechny soubory .csv ve složce s názvy začínajícími na "soubor".
https://mydatalake.blob.core.windows.net/data/files/*: Všechny soubory ve složce .
https://mydatalake.blob.core.windows.net/data/files/**: Všechny soubory ve složce 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šími parametry podle potřeby pro zpracování konkrétních podrobností formátování vašich dat. Napří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í, 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ží k přeskočení řádků v textovém souboru, k odstranění nestrukturovaného textu preambule 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 pomocí znaku 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 UNIX používají jeden znak odřádkování, který lze znamét pomocí kódu 0x0a.
FIELDQUOTE – znak použitý k uzavření řetězcových hodnot v uvozových hodnotách. Pokud chcete například zajistit, aby čárka v hodnotě pole adresy 126 Main St, apt 2 není 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.

Návod

Podrobnosti o dalších parametrech při práci s textovými soubory s oddělovači najdete v dokumentaci 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	cena v ceníku
123	Widget	12.9900
124	Přístroj	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, C3atd.

C1	C2	C3
123	Widget	12.9900
124	Přístroj	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	cena v ceníku
123	Widget	12,99
124	Přístroj	3.99

Návod

Při práci s textovými soubory můžete zaznamenat nekompatibilitu s daty s kódováním 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, FIELDQUOTEa ROWTERMIN ATOR nastaven na 0x0ba schéma, které zahrnuje jeden sloupec NVARCHAR(MAX). Výsledkem tohoto dotazu je sada řádků obsahující jeden sloupec dokumentů JSON, například takto:

dokument
{"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:

výrobek	cena
Widget	12,99
Přístroj	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 FORMATparquet; Nějak tak:

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é metry v názvech složek v cestě BULK -so parametr 1 je * v názvu složky year=* název složky a parametr 2 je * v názvu složky month=*.

Váš názor

Byla tato stránka užitečná?