Dotazování souborů úložiště pomocí bezserverového fondu SQL ve službě Azure Synapse Analytics

  • Článek

Bezserverový fond SQL umožňuje dotazovat se na data v datovém jezeře. Nabízí oblast dotazů T-SQL, která umožňuje částečně strukturované a nestrukturované dotazy na data. Pro dotazování se podporují následující aspekty T-SQL:

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

Přehled

Pro zajištění bezproblémového prostředí pro dotazování na data umístěná v souborech Azure Storage používá bezserverový fond SQL funkci OPENROWSET s dalšími funkcemi:

Dotazování souborů PARQUET

Pokud chcete dotazovat zdrojová data Parquet, použijte funkci 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 článku o souborech Parquet pro dotazy.

Dotazování souborů CSV

Pokud chcete dotazovat zdrojová data CSV, použijte funkci 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

K úpravě pravidel analýzy vlastního formátu CSv můžete použít několik dalších možností:

  • ESCAPE_CHAR = 'char' Určuje znak v souboru, který se používá k řídicímu znaku a všechny hodnoty oddělovače 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, jestli je pole FIELDQUOTE nebo není povolené. Nebude použit k uvozování znaku. Znak uvozování musí být uvozován jiným znakem uvozování. Znak uvozování se může objevit v hodnotě sloupce pouze v případě, že je hodnota zapouzdř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.

Dotazování formátu DELTA LAKE

Pokud chcete dotazovat zdrojová data Delta Lake, použijte funkci FORMAT = DELTA a odkazujte na kořenovou složku obsahující vaše 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. Příklady použití najdete v článku o formátu Delta Lake.

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 snaží automaticky určit typy sloupců ze souboru pomocí odvození schématu.

Čtení vybrané podmnožině sloupců

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

  • Pokud existují datové soubory CSV, abyste mohli číst všechny sloupce, zadejte názvy sloupců a jejich datové typy. Pokud chcete podmnožinu sloupců, pomocí řadových čísel vyberte sloupce z původního datového souboru podle řad. Sloupce budou vázány 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 budou svázané 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 části Čtení souborů CSV bez zadání všech sloupců.

Odvozování schémat

Vynecháním klauzule WITH 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ů, budou 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 ve složkách dotazů a více souborech .

Funkce metadat souborů

Funkce Filename

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é kartě na pozici zadané v parametru. Například hodnota parametru 1 by vrátila část cesty, která odpovídá prvnímu zástupného znaku.

Další informace najdete v části Cesta k souboru v článku o souborech specifických pro dotazy.

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 rozšíření, která následují.

Projektová vnořená nebo opakovaná data

Pokud chcete projektovat data, spusťte příkaz SELECT přes soubor Parquet, který obsahuje sloupce vnořených datových typů. Ve výstupu se vnořené hodnoty serializují do FORMÁTU JSON a vrátí se jako datový typ VARCHAR(8000) SQL.

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

Podrobnější informace najdete v článku o vnořených nebo opakovaných datových částech Dotazu Parquet vnořených typech dotazů.

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

Pokud chcete získat přístup k vnořeným prvkům z vnořeného sloupce, jako je například struktura, použijte k zřetězení názvů polí do cesty "tečka". Zadejte cestu jako column_name v klauzuli OPENROWSET WITH 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í OPENROWSET funkce odpovídá názvu zdrojového pole a cestě s názvy sloupců zadaný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 prostřednictvím klauzule WITH.

Návratové hodnoty

  • Funkce vrátí skalární hodnotu, například int, decimal a 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 vnořený typ, vrátí funkce fragment JSON počínaje horním prvkem v zadané cestě. Fragment JSON je typu varchar(8000).
  • Pokud vlastnost nelze najít v zadaném column_name, vrátí funkce chybu.
  • Pokud vlastnost nelze najít v zadaném column_path v závislosti na režimu Path, vrátí funkce chybu v přísném režimu nebo null v laxním režimu.

Ukázky dotazů najdete v části Prvky Accessu z vnořených sloupců v článku o vnořených typech dotazu Parquet.

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

Pokud chcete získat přístup k prvkům z opakujícího se sloupce, například elementu Array nebo Map, použijte funkci JSON_VALUE pro každý skalární prvek, který potřebujete promítat a poskytovat:

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

Pokud chcete získat přístup k nes skalárním prvkům z opakovaného sloupce, použijte funkci JSON_QUERY pro každý neškálární prvek, který potřebujete projektovat a poskytnout:

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

Podívejte se na fragment syntaxe níže:

    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ů najdete v článku o vnořených typech Dotazů Parquet.

Další kroky

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: