Sdílet prostřednictvím

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

  • Článek

Bezserverový fond SQL umožňuje dotazovat 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:

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:

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

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.

Dotazování souborů 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 řídicímu znaku samotného souboru, 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 FIELDQUOTE , jestli je nebo není povolený. Nepoužívá se k uvozování znaku uvozování. 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 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. Příklady použití najdete v souborech Delta Lake (v1) dotazů.

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.

Čtení vybrané podmnožině sloupců

Pokud chcete zadat sloupce, které chcete číst, můžete v OPENROWSET příkazu zadat volitelnou WITH 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ího datového souboru podle ř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 vá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 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 tématu složek dotazů a více souborů.

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 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ů. 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]

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 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čku . 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í OPENROWSET funkce odpovídá názvu a cestě zdrojového pole 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 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 vnořený typ, vrátí funkce fragment JSON počínaje horním prvkem v zadané cestě. Fragment JSON je typu varchar(8000).
  • Pokud se vlastnost na zadaném místě column_namenenašla, vrátí funkce chybu.
  • Pokud vlastnost nelze najít v zadaném column_pathrežimu v závislosti na režimu Cesta, vrátí funkce chybu, pokud je v přísném režimu nebo null v laxním režimu.

Ukázky dotazů najdete v části Čtení vlastností z vnořených sloupců objektů v článku o vnořených typech dotazů 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:

  • 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 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:

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

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: