Dotazování souborů úložiště pomocí bezserverového fondu SQL

Fond SQL bez serveru umožňuje provádět dotazy na data v datovém jezeře. Nabízí oblast dotazu Transact-SQL (T-SQL), která umožňuje částečně strukturované a nestrukturované dotazy na data. Pro dotazování se podporují následující aspekty T-SQL:

• Úplná oblast povrchu SELECT , včetně většiny funkcí a operátorů SQL.
• CREATE EXTERNAL TABLE AS SELECT (CETAS) vytvoří externí tabulku a pak paralelně exportuje výsledky příkazu T-SQL SELECT do Azure Storage.

Další informace o tom, co je nebo není aktuálně podporováno, najdete v přehledu bezserverového fondu SQL nebo v následujících článcích:

• Vyvíjejte přístup k úložišti, kde můžete ke čtení dat z úložiště použít externí tabulky a funkci OPENROWSET .
• Řídit přístup k úložišti, kde se dozvíte, jak povolit Synapse SQL přístup k úložišti pomocí ověřování SAS nebo spravované identity pracovního prostoru.

Přehled

Pro zajištění bezproblémového prostředí pro místní dotazování dat umístěných v souborech Azure Storage používá bezserverový fond SQL funkci OPENROWSET s dalšími možnostmi:

• Dotazování souborů PARQUET
• Dotazování na soubory CSV a text s oddělovači (ukončovací znak pole, ukončovací znak řádku, řídicí znak)
• Dotazování formátu DELTA LAKE
• Číst vybranou podmnožinu sloupců
• Odvození schématu
• Dotazování na více souborů nebo složek
• Funkce názvu souboru
• Funkce Cesta k souboru
• Práce se složitými typy a vnořenými nebo opakovanými datovými strukturami

Dotazování souborů PARQUET

K dotazování zdrojových dat Parquet použijte FORMAT = 'PARQUET':

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Příklady použití najdete v tématu Dotazování souborů Parquet.

Dotaz na soubory CSV

Pokud chcete dotazovat zdrojová data CSV, použijte FORMAT = 'CSV'. Schéma souboru CSV můžete zadat jako součást OPENROWSET funkce při dotazování souborů CSV:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Existuje několik dalších možností, které je možné použít k úpravě pravidel analýzy na vlastní formát CSV:

• ESCAPE_CHAR = 'char' Určuje znak v souboru, který se používá k vyznačení sebe sama a všech hodnot oddělovačů v souboru. Pokud za řídicím znakem následuje jiná hodnota než samotná nebo jakákoli hodnota oddělovače, při čtení hodnoty se řídicí znak zahodí. Parametr ESCAPE_CHAR se použije bez ohledu na to FIELDQUOTE , jestli je nebo není povolený. Nepoužívá se k odstranění účinku uvozovacího znaku. Uvozující znak musí být eskapován dalším uvozujícím znakem. Znak uvozování se může objevit v hodnotě sloupce pouze v případě, že je hodnota uzavřena znaky uvozování.
• FIELDTERMINATOR ='field_terminator' Určuje ukončovací znak pole, který se má použít. Výchozí ukončovací znak pole je čárka (,).
• ROWTERMINATOR ='row_terminator' Určuje ukončovací znak řádku, který se má použít. Výchozí ukončovací znak řádku je znak nového řádku (\r\n).

Dotazovat formát DELTA LAKE

Pokud chcete dotazovat zdrojová data Delta Lake, použijte FORMAT = 'DELTA' a odkazujte na kořenovou složku obsahující soubory Delta Lake.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Kořenová složka musí obsahovat podsložku s názvem _delta_log. Chcete-li příklady použití, podívejte se na soubory Delta Lake (v1) pro dotazy.

Schéma souborů

Jazyk SQL v Synapse SQL umožňuje definovat schéma souboru jako součást OPENROWSET funkce a číst všechny sloupce nebo podmnožinu sloupců nebo se pokusí automaticky určit typy sloupců ze souboru pomocí odvození schématu.

Přečíst vybrané sloupce

Pokud chcete zadat sloupce, které chcete číst, můžete v WITH příkazu zadat volitelnou OPENROWSET klauzuli.

• Pokud existují datové soubory CSV, zadejte názvy sloupců a jejich datové typy pro čtení všech sloupců. Pokud chcete podmnožinu sloupců, pomocí řadových čísel vyberte sloupce z původních datových souborů podle pořadí. Sloupce jsou svázané pořadovým označením.
• Pokud existují datové soubory Parquet, zadejte názvy sloupců, které odpovídají názvům sloupců v původních datových souborech. Sloupce jsou přiřazené podle názvu.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows;

Pro každý sloupec je potřeba zadat název sloupce a zadat do WITH klauzule. Ukázky najdete v tématu Čtení souborů CSV bez zadání všech sloupců.

Odvozování schémat

Vynecháním WITH klauzule z OPENROWSET příkazu můžete službě dát pokyn k automatickému rozpoznání (odvození) schématu z podkladových souborů.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 

Ujistěte se, že jsou pro optimální výkon použity vhodné odvozené datové typy .

Dotazování na více souborů nebo složek

Pokud chcete spustit dotaz T-SQL na sadu souborů ve složce nebo sadě složek a současně s nimi zacházet jako s jednou entitou nebo sadou řádků, zadejte cestu ke složce nebo vzoru (pomocí zástupných znaků) přes sadu souborů nebo složek.

Platí následující pravidla:

• Vzory se můžou objevit buď v části cesty k adresáři, nebo v názvu souboru.
• V jednom kroku adresáře nebo názvu souboru se může zobrazit několik vzorů.
• Pokud existuje více zástupných znaků, jsou soubory ve všech odpovídajících cestách zahrnuty do výsledné sady souborů.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Příklady použití najdete v části dotazy na složky a více souborů.

Funkce metadat souborů

Funkce názvu souboru

Tato funkce vrátí název souboru, ze kterého řádek pochází.

Pokud chcete dotazovat konkrétní soubory, přečtěte si část Název souboru v článku o souborech specifických pro dotaz.

Návratový datový typ je nvarchar(1024). Pro zajištění optimálního výkonu vždy přetypujte výsledek funkce názvu souboru na příslušný datový typ. Pokud používáte datový typ znaku, ujistěte se, že se používá příslušná délka.

Funkce Filepath

Tato funkce vrátí úplnou cestu nebo část cesty:

• Při zavolání bez parametru vrátí úplnou cestu k souboru, ze které řádek pochází.
• Při zavolání s parametrem vrátí část cesty, která odpovídá zástupnému znaku na pozici určené parametrem. Například hodnota parametru 1 by vrátila část trasy, která odpovídá prvnímu zástupnému znaku.

Pro další informace si přečtěte část Filepath v článku Dotazy na specifické soubory.

Návratový datový typ je nvarchar(1024). Pro zajištění optimálního výkonu vždy přetypujte výsledek funkce filepath na příslušný datový typ. Pokud používáte datový typ znaku, ujistěte se, že se používá příslušná délka.

Práce se složitými typy a vnořenými nebo opakovanými datovými strukturami

Pro zajištění hladkého prostředí s daty uloženými ve vnořených nebo opakovaných datových typech, například v souborech Parquet , přidal bezserverový fond SQL následující rozšíření.

Projektová vnořená nebo opakovaná data

Pokud chcete projektovat data, spusťte SELECT příkaz nad souborem Parquet, který obsahuje sloupce vnořených datových typů. Při výstupu jsou vnořené hodnoty serializovány do formátu JSON a jsou vráceny jako datový typ varchar(8000) SQL.

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Další informace najdete v části Project vnořená nebo opakovaná data v článku o vnořených typech dotazů Parquet.

Přístup k prvkům ve vnořených sloupcích

Pokud chcete získat přístup k vnořeným prvkům z vnořeného sloupce, jako je struktura, použijte tečkovou notaci ke zřetězení názvů polí do cesty. Zadejte cestu jako column_name v WITH klauzuli OPENROWSET funkce.

Příklad fragmentu syntaxe je následující:

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ('column_name' 'column_type')
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

Ve výchozím nastavení funkce OPENROWSET porovnává název a cestu zdrojového pole s názvy sloupců uvedenými v klauzuli WITH. K prvkům obsaženým na různých úrovních vnoření v rámci stejného zdrojového souboru Parquet je možné přistupovat pomocí WITH klauzule.

Návratové hodnoty

• Funkce vrátí skalární hodnotu, například int, decimala varchar, ze zadaného prvku a na zadané cestě pro všechny typy Parquet, které nejsou ve skupině Vnořený typ .
• Pokud cesta odkazuje na prvek, který je typu Nested Type, funkce vrátí fragment JSON počínaje prvním prvkem na zadané cestě. Fragment JSON je typu varchar(8000).
• Pokud se vlastnost na zadaném místě column_namenenašla, vrátí funkce chybu.
• Pokud nelze najít vlastnost na zadaném column_path, v závislosti na režimu cesty, funkce vrátí chybu, pokud je v přísném režimu, nebo vrátí null, pokud je v laxním režimu.

Ukázky dotazů najdete v části Čtení vlastností ze vnořených sloupců objektů v článku Dotazování na vnořené typy Parquet.

Přístup k prvkům z opakovaných sloupců

Pokud chcete získat přístup k prvkům z opakovaného sloupce, jako je například prvek pole nebo mapy, použijte funkci JSON_VALUE pro každý skalární prvek, který potřebujete promítat a poskytnout:

• První parametr jako vnořený nebo opakovaný sloupec
• Cesta JSON, která jako druhý parametr určuje prvek nebo vlastnost pro přístup.

Pokud chcete získat přístup k nescalarovým prvkům z opakovaného sloupce, použijte funkci JSON_QUERY pro každý nescalar prvek, který potřebujete promítnout a poskytnout:

• První parametr jako vnořený nebo opakovaný sloupec
• Cesta JSON, která jako druhý parametr určuje prvek nebo vlastnost pro přístup.

Podívejte se na následující fragment syntaxe:

    SELECT
       JSON_VALUE (column_name, path_to_sub_element),
       JSON_QUERY (column_name [ , path_to_sub_element ])
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Ukázky dotazů pro přístup k prvkům z opakovaných sloupců můžete najít v článku Query Parquet – vnořené typy.

Související obsah

Další informace o dotazování různých typů souborů a vytváření a používání zobrazení najdete v následujících článcích:

• Dotazování souborů CSV
• Dotazování souborů Parquet
• Dotazování souborů JSON
• Dotazování vnořených hodnot
• Procházení složek a vícero souborů CSV
• Použití metadat souborů v dotazech
• Vytváření a používání zobrazení