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

  • Článek

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

Další informace o tom, co se aktuálně nepodporuje, najdete v článku Přehled bezserverového fondu SQL nebo v následujících článcích:

Přehled

Pro podporu bezproblémového dotazování na data, která jsou umístěná v souborech Azure Storage, bezserverový fond SQL používá funkci OPENROWSET s dalšími funkcemi:

Dotazování souborů PARQUET

Pokud chcete dotazovat zdrojová data Parquet, použijte FORMÁT = 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 Dotazování souborů Parquet .

Dotazování souborů CSV

Pokud chcete dotazovat zdrojová data CSV, použijte FORMÁT = 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

Existují některé další možnosti, které můžete 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 bloku, a všechny hodnoty oddělovače v souboru. Pokud za řídicím znakem následuje jiná hodnota než sama o sobě nebo jakákoli hodnota oddělovače, je řídicí znak při čtení hodnoty vyřazen. Parametr ESCAPE_CHAR se použije bez ohledu na to, jestli je nebo není povolený parametr FIELDQUOTE. Nepoužije se k uvozování znaku uvozování. Uvozovací znak musí být uvozován jiným znakem uvozování. Znak uvozování se může zobrazit v hodnotě sloupce pouze v případě, že je hodnota zapouzdřena znaky uvozování.
  • FIELDTERMINATOR ='field_terminator' Určuje koncovou hodnotu pole, která se má použít. Výchozí ukončovací znak pole je čárka (",")
  • ROWTERMINATOR ='row_terminator' Určuje, který ukončovač řádků 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 příkaz 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 článku o formátu delta lake dotazu .

Schéma souboru

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í odvozování 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, pro čtení všech sloupců zadejte názvy sloupců a jejich datové typy. Pokud chcete vytvořit podmnožinu sloupců, vyberte sloupce z datových souborů podle řad pomocí pořadových čísel. 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í datové soubory. Sloupce budou vázány názvem.
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 musíte zadat název sloupce a zadat klauzuli WITH . Ukázky najdete v tématu Čtení souborů CSV bez zadání všech sloupců.

Odvozování schémat

Vynecháním klauzule WITH z OPENROWSET příkazu můžete dát službě pokyn, aby automaticky rozpoznala (odvodil) schéma 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 se pro optimální výkon používají vhodné odvozené datové typy .

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

Pokud chcete spustit dotaz T-SQL na sadu souborů v rámci složky nebo sady složek a současně je považovat za jednu entitu nebo sadu řádků, zadejte cestu ke složce nebo vzor (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.
  • Ve stejném 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 v tématu Věnovaném složkám dotazů a více souborům .

Funkce metadat souborů

Funkce Název 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 Dotazování na konkrétní soubory .

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

Funkce Filepath

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

  • Při volání bez parametru vrátí úplnou cestu k souboru, ze které pochází řádek.
  • Při volání pomocí parametru vrátí část cesty, která odpovídá zástupné sadě na pozici zadané v parametru. Například hodnota parametru 1 vrátí část cesty, která odpovídá prvnímu zástupného znaku.

Další informace najdete v části Cesta k souborům v článku Dotazování na konkrétní 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 je použitá odpovídající délka.

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

Aby bylo možné bezproblémově používat data uložená ve vnořených nebo opakovaných datových typech, například v souborech Parquet , bezserverový fond SQL přidal následující rozšíření.

Vnořená nebo opakovaná data projektu

Pokud chcete promítat data, spusťte příkaz SELECT 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 SQL varchar(8000).

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

Podrobnější informace najdete v části vnořená nebo opakovaná data projektu v článku Dotazování vnořených typů 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čkovou notaci". 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 a cestě zdrojového pole s názvy sloupců zadanými v klauzuli WITH. Prvky obsažené na různých úrovních vnoření ve stejném zdrojovém souboru Parquet jsou přístupné prostřednictvím klauzule WITH.

Vrácené hodnoty

  • Funkce vrátí skalární hodnotu, například int, decimal a varchar, ze zadaného elementu 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ého typu, vrátí funkce fragment JSON od horního elementu na 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 na zadaném column_path, v závislosti na režimu Cesta vrátí funkce chybu v přísném režimu nebo null v režimu laxní.

Ukázky dotazů najdete v části Access elements from vnořených sloupců v článku Dotazování vnořených typů 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 mapování, 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 element nebo vlastnost pro přístup jako druhý parametr

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

  • Vnořený nebo opakovaný sloupec jako první parametr
  • Cesta JSON, která určuje element 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 Dotazování vnořených typů Parquet .

Ukázky dotazů

Další informace o dotazování různých typů dat pomocí ukázkových dotazů.

nástroje

Nástroje, které potřebujete k vydávání dotazů: – Azure Synapse Studio – Azure Data Studio – SQL Server Management Studio

Ukázkové nastavení

Prvním krokem je vytvoření databáze , ve které budete spouštět dotazy. Pak inicializujete objekty spuštěním instalačního skriptu v této databázi.

Tento instalační skript vytvoří zdroje dat, přihlašovací údaje v oboru databáze a externí formáty souborů, které se používají ke čtení dat v těchto ukázkách.

Poznámka

Databáze se používají pouze pro zobrazení metadat, nikoli pro skutečná data. Název databáze, který používáte, si poznamenejte. Budete ho potřebovat později.

CREATE DATABASE mydbname;

Poskytnutá ukázková data

Ukázková data obsahují následující datové sady:

  • NYC Taxi - Yellow Taxi Trip Records - součást veřejné NYC dat ve formátu CSV a Parquet
  • Datová sada populace ve formátu CSV
  • Ukázkové soubory Parquet s vnořenými sloupci
  • Knihy ve formátu JSON
Cesta ke složce Popis
/Csv/ Nadřazená složka pro data ve formátu CSV
/csv/population/
/csv/population-unix/
/csv/population-unix-hdr/
/csv/population-unix-hdr-escape
/csv/population-unix-hdr-quoted		 Složky s datovými soubory population v různých formátech CSV.
/csv/taxi/ Složka s veřejnými datovými soubory NYC ve formátu CSV
/Parketové/ Nadřazená složka pro data ve formátu Parquet
/parquet/taxi Veřejné datové soubory NYC ve formátu Parquet, rozdělené podle roku a měsíce pomocí schématu dělení Hive/Hadoop.
/parquet/nested/ Ukázkové soubory Parquet s vnořenými sloupci
/Json/ Nadřazená složka pro data ve formátu JSON
/json/books/ Soubory JSON s daty knih

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: