Použití externích tabulek s synapse SQL

Externí tabulka odkazuje na data umístěná v Hadoopu, Azure Storage blob nebo Azure Data Lake Storage. Externí tabulky se používají ke čtení dat ze souborů nebo k zápisu dat do souborů v Azure Storage. Díky synapse SQL můžete pomocí externích tabulek číst externí data pomocí vyhrazeného fondu SQL nebo bez serveru SQL fondu.

V závislosti na typu externího zdroje dat můžete použít dva typy externích tabulek:

• Externí tabulky Hadoop, které můžete použít ke čtení a exportu dat v různých formátech dat, jako jsou CSV, Parquet a ORC. Externí tabulky Hadoop jsou k dispozici ve vyhrazených SQL fondech, ale nejsou k dispozici ve fondech SQL serveru.
• Nativní externí tabulky, které můžete použít ke čtení a exportu dat v různých formátech dat, jako jsou CSV a Parquet. Nativní externí tabulky jsou k dispozici ve fondech SQL a jsou ve verzi Public Preview ve vyhrazených SQL fondech.

Klíčové rozdíly mezi Hadoopem a nativními externími tabulkami jsou uvedeny v následující tabulce:

Externí tabulky ve vyhrazeném SQL a bez serveru SQL fond

Externí tabulky můžete použít k:

• Dotazování azure blob Storage a Azure Data Lake Gen2 pomocí příkazů transact-SQL.
• Ukládat výsledky dotazů do souborů ve službě Azure Blob Storage nebo Azure Data Lake Storage pomocí CETAS.
• Importujte data z Azure Blob Storage a Azure Data Lake Storage a uložte je do vyhrazeného fondu SQL (pouze tabulky Hadoop ve vyhrazeném fondu).

Externí tabulky ve fondech SQL synapse můžete vytvořit pomocí následujících kroků:

1. CREATE EXTERNAL DATA SOURCE pro odkazování na externí úložiště Azure a zadání přihlašovacích údajů, které se mají použít pro přístup k úložišti.
2. CREATE EXTERNAL FILE FORMAT popisuje formát souborů CSV nebo Parquet.
3. CREATE EXTERNAL TABLE nad soubory umístěnými ve zdroji dat ve stejném formátu.

Zabezpečení

Uživatel musí mít SELECT oprávnění k externí tabulce ke čtení dat. Externí tabulky přistupovat k základnímu úložišti Azure pomocí přihlašovacích údajů definovaných ve zdroji dat s oborem databáze pomocí následujících pravidel:

• Zdroj dat bez přihlašovacích údajů umožňuje externím tabulkám přístup k veřejně dostupným souborům v úložišti Azure.
• Zdroj dat může mít přihlašovací údaje, které umožňují externím tabulkám přístup jenom k souborům v úložišti Azure pomocí tokenu SAS nebo spravované identity pracovního prostoru – příklady najdete v článku Vývoj souborů úložiště pro řízení přístupu k úložišti.

VYTVOŘENÍ EXTERNÍHO ZDROJE DAT

Externí zdroje dat se používají pro připojení k účtům úložiště. Kompletní dokumentace je uvedená tady.

Syntaxe pro CREATE EXTERNAL DATA SOURCE

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
     , TYPE = HADOOP
)
[;]

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
)
[;]

Argumenty pro CREATE EXTERNAL DATA SOURCE

data_source_name

Určuje uživatelem definovaný název zdroje dat. Název musí být v rámci databáze jedinečný.

Umístění

LOCATION = '<prefix>://<path>' – Poskytuje protokol připojení a cestu k externímu zdroji dat. V umístění je možné použít následující vzory:

https: umožňuje v cestě použít podsložku .

Přihlašovací údaj

CREDENTIAL = <database scoped credential> volitelné přihlašovací údaje, které se budou používat k ověření ve službě Azure Storage. Externí zdroj dat bez přihlašovacích údajů má přístup k veřejnému účtu úložiště nebo používá identitu Azure AD volajícího pro přístup k souborům v úložišti.

• Ve vyhrazeném SQL může přihlašovací údaje s oborem databáze zadat vlastní identitu aplikace, spravovanou identitu pracovního prostoru nebo klíč SAK.
• Ve fondu SQL může přihlašovací údaje s oborem databáze zadat spravovanou identitu pracovního prostoru nebo klíč SAS.

TYP

TYPE = je možnost, která určuje, že technologie založená na jazyce Java by měla být použita HADOOP pro přístup k podkladovým souborům. Tento parametr není možné použít v bez serveru SQL, který používá integrovanou nativní čtečku.

Příklad vytvoření EXTERNÍHO ZDROJE DAT

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

CREATE EXTERNAL FILE FORMAT

Vytvoří externí objekt formátu souboru, který definuje externí data uložená ve službě Azure Blob Storage nebo Azure Data Lake Storage. Vytvoření formátu externího souboru je předpokladem pro vytvoření externí tabulky. Kompletní dokumentaci najdete tady.

Vytvořením formátu externího souboru určíte skutečné rozložení dat, na která odkazuje externí tabulka.

Syntaxe pro CREATE EXTERNAL FILE FORMAT

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = PARQUET  
    [ , DATA_COMPRESSION = {  
        'org.apache.hadoop.io.compress.SnappyCodec'  
      | 'org.apache.hadoop.io.compress.GzipCodec'      }  
    ]);  

--Create an external file format for DELIMITED TEXT files
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT  
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec' ]
    [ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]  
    );  

<format_options> ::=  
{  
    FIELD_TERMINATOR = field_terminator  
    | STRING_DELIMITER = string_delimiter
    | First_Row = integer
    | USE_TYPE_DEFAULT = { TRUE | FALSE }
    | Encoding = {'UTF8' | 'UTF16'}
    | PARSER_VERSION = {'parser_version'}
}

Argumenty pro CREATE EXTERNAL FILE FORMAT

file_format_name – Určuje název formátu externího souboru.

FORMAT_TYPE = [ PARQUET | DELIMITEDTEXT]– Určuje formát externích dat.

• PARQUET – určuje formát Parquet.
• DELIMITEDTEXT – Určuje textový formát s oddělovači sloupců, které se také nazývají ukončovací znaky polí.

FIELD_TERMINATOR = field_terminator – platí jenom pro textové soubory s oddělovači. Ukončovací znak pole určuje jeden nebo více znaků, které označí konec každého pole (sloupce) v textovém souboru s oddělovači. Výchozí hodnota je znak kanálu (ꞌ|ꞌ).

Příklady:

• FIELD_TERMINATOR = "|"
• FIELD_TERMINATOR = ' '
• FIELD_TERMINATOR = ꞌ\tꞌ

STRING_DELIMITER = string_delimiter – Určuje ukončovací znak pole pro data řetězce typu v textovém souboru s oddělovači. Oddělovač řetězců má jeden nebo více znaků a je uzavřený v jednoduchých uvozovkách. Výchozí hodnota je prázdný řetězec ("").

Příklady:

• STRING_DELIMITER = '"'
• STRING_DELIMITER = '*'
• STRING_DELIMITER = ꞌ,ꞌ

FIRST_ROW = First_row_int – Určuje číslo řádku, které se přečte jako první a použije se pro všechny soubory. Nastavení hodnoty na dvě způsobí, že se při načtení dat první řádek v každém souboru (řádek záhlaví) přeskočí. Řádky se přeskočí na základě existence ukončovače řádků (/r/n, /r, /n).

USE_TYPE_DEFAULT = { TRUE | FALSE } – Určuje, jak zpracovat chybějící hodnoty v textových souborech s oddělovači při načítání dat z textového souboru.

TRUE – pokud načítáte data z textového souboru, uložte každou chybějící hodnotu pomocí datového typu výchozí hodnoty pro odpovídající sloupec v definici externí tabulky. Například chybějící hodnotu nahraďte následujícím kódem:

• 0, pokud je sloupec definovaný jako číselný sloupec. Desetinné sloupce nejsou podporované a způsobí chybu.
• Prázdný řetězec (""), pokud je sloupec sloupcem řetězce.
• 1900-01-01, pokud je sloupec sloupcem data.

FALSE – Všechny chybějící hodnoty uložte jako HODNOTU NULL. Všechny hodnoty NULL, které jsou uloženy pomocí slova NULL v textovém souboru s oddělovači, se importuje jako řetězec NULL.

Encoding = {'UTF8' | UTF16} – bez serveru SQL může číst textové soubory s oddělovači v kódování UTF8 a UTF16.

DATA_COMPRESSION = data_compression_method – tento argument určuje metodu komprese dat pro externí data.

Typ formátu souboru PARQUET podporuje následující metody komprese:

• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

Při čtení z externích tabulek PARQUET se tento argument ignoruje, ale používá se při zápisu do externích tabulek pomocí CETAS.

Formát souboru DELIMITEDTEXT podporuje následující metodu komprese:

• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'

PARSER_VERSION = "parser_version" Určuje verzi analyzátoru, která se má použít při čtení souborů CSV. Dostupné verze analyzátoru jsou a 1.0 2.0 . Tato možnost je dostupná jenom v bezdomovových SQL fondech.

Příklad pro CREATE EXTERNAL FILE FORMAT

Následující příklad vytvoří formát externího souboru pro soubory sčítání lidu:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

CREATE EXTERNAL TABLE

Příkaz CREATE EXTERNAL TABLE vytvoří externí tabulku pro službu Synapse SQL přístup k datům uloženým ve službě Azure Blob Storage nebo Azure Data Lake Storage.

Syntaxe pro CREATE EXTERNAL TABLE

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )  
    WITH (
        LOCATION = 'folder_or_filepath',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]
        [, <reject_options> [ ,...n ] ] 
    )
[;] 

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

<reject_options> ::=  
{  
    | REJECT_TYPE = value,  
    | REJECT_VALUE = reject_value,  
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}   

Argumenty CREATE EXTERNAL TABLE

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

Název tabulky, která se má vytvořit, má jednu až tři části. U externí tabulky ukládá synapse SQL jen metadata tabulky. Ve službě Synapse se ve službě Synapse SQL žádná skutečná data.

<column_definition>, ... n ]

CREATE EXTERNAL TABLE podporuje možnost konfigurovat název sloupce, datový typ a kolaci. U externích tabulek nemůžete použít VÝCHOZÍ OMEZENÍ.

Při čtení ze souborů Parquet můžete zadat pouze sloupce, které chcete číst, a zbytek přeskočit.

LOCATION = 'folder_or_filepath'

Určuje složku nebo cestu k souboru a název souboru pro skutečná data v azure blob Storage. Umístění začíná od kořenové složky. Kořenová složka je umístění dat zadané v externím zdroji dat.

Nativní externí tabulky na rozdíl od externích tabulek Hadoop nevrací podsložky, pokud na konci cesty nezadáte /**. V tomto příkladu, pokud LOCATION='/webdata/', bez serveru SQL fondu, vrátí řádky z mydata.txt. Nevrátí žádné další mydata2.txt mydata3.txt protože jsou umístěné v podsložce. Tabulky Hadoop vrátí všechny soubory v rámci jakékoli dílčí složky.

Hadoop i nativní externí tabulky přeskočí soubory s názvy, které začínají podtrženým (_) nebo tečkou (.).

DATA_SOURCE = external_data_source_name

Určuje název externího zdroje dat, který obsahuje umístění externích dat. Pokud chcete vytvořit externí zdroj dat, použijte CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

Určuje název objektu formátu externího souboru, ve kterém je uložený typ souboru a metoda komprese pro externí data. Pokud chcete vytvořit formát externího souboru, použijte CREATE EXTERNAL FILE FORMAT.

Možnosti odmítnutí

Můžete zadat parametry zamítnutí, které určují, jak bude služba zpracovávat nečtené záznamy, které načte z externího zdroje dat. Datový záznam se považuje za "nezačištěný", pokud skutečné datové typy neodpovídají definicím sloupců externí tabulky.

Pokud nezadáte nebo nezměníte možnosti zamítnutí, služba použije výchozí hodnoty. Tyto informace o parametrech reject se ukládají jako další metadata při vytváření externí tabulky pomocí příkazu CREATE EXTERNAL TABLE. Když budoucí příkaz SELECT nebo příkaz SELECT INTO SELECT vybere data z externí tabulky, služba použije možnosti zamítnutí k určení počtu řádků, které lze odmítnout před selháním skutečného dotazu. Dotaz vrátí (částečné) výsledky, dokud se prahová hodnota zamítnutí nedosáhne. Pak selže s příslušnou chybovou zprávou.

REJECT_TYPE = hodnota

V tuto chvíli je to jediná podporovaná hodnota. Vysvětluje, že REJECT_VALUE je zadána jako hodnota literálu.

hodnota

REJECT_VALUE je hodnota literálu. Dotaz selže, pokud počet zamítnutých řádků překročí reject_value.

Pokud například REJECT_VALUE = 5 a REJECT_TYPE = hodnota, dotaz SELECT selže po zamítnutí pěti řádků.

REJECT_VALUE = reject_value

Určuje počet řádků, které lze odmítnout, než dotaz selže.

Pro REJECT_TYPE = hodnota reject_value celé číslo v rozmezí od 0 do 2 147 483 647.

REJECTED_ROW_LOCATION = umístění adresáře

Určuje adresář v externím zdroji dat, na který by se měly zapsat odmítnuté řádky a odpovídající chybový soubor. Pokud zadaná cesta neexistuje, služba ji vytvoří za vás. Vytvoří se podřízený adresář s názvem "rejectedrows". Znak "" zajišťuje, aby byl adresář řídicím adresářem pro jiné zpracování dat, pokud není explicitně pojmenován v parametru Location. V tomto adresáři je vytvořena složka založená na čase odeslání zatížení ve formátu YearMonthDay_HourMinuteSecond_StatementID (ex. 20180330-173205-559EE7D2-196D-400A-806D-3BF5D007F891). ID příkazu můžete použít ke korelaci složky s dotazem, který je vygeneroval. V této složce jsou zapsány dva soubory: Error. JSON a datový soubor.

soubor Error. JSON obsahuje pole JSON se zjištěnými chybami souvisejícími s ODMÍTNUTÝMI Řádky. Každý element představující chybu obsahuje následující atributy:

TABLE_OPTIONS

TABLE_OPTIONS = JSON – možnosti – určuje sadu možností, které popisují způsob čtení podkladových souborů. V současné době je k dispozici pouze možnost, která je k dispozici, aby bylo možné "READ_OPTIONS":["ALLOW_INCONSISTENT_READS"] Ignorovat aktualizace provedené v podkladových souborech, a to i v případě, že to může způsobit nekonzistentní operace čtení. Tuto možnost použijte pouze ve zvláštních případech, kde máte často připojené soubory. tato možnost je k dispozici ve fondu SQL bez serveru pro formát CSV.

Oprávnění vytvořit externí tabulku

Chcete-li vybrat z externí tabulky, budete potřebovat správné přihlašovací údaje se seznamem a oprávněními ke čtení.

Příklad vytvoření externí tabulky

Následující příklad vytvoří externí tabulku. Vrátí první řádek:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Vytvoření a dotazování externích tabulek ze souboru v Azure Data Lake

pomocí Data Lake možností průzkumu v Synapse studiu teď můžete vytvořit a zadat dotaz na externí tabulku pomocí SQL fondu Synapse s jednoduchým kliknutím pravým tlačítkem myši na soubor. Gesto jedním kliknutím pro vytvoření externích tabulek z ADLS Gen2ho účtu úložiště je podporované jenom pro soubory Parquet.

Požadavky

• Musíte mít přístup k pracovnímu prostoru s alespoň Storage Blob Data Contributor rolí přístupu k účtu adls Gen2 nebo seznamům Access Control (ACL), které vám umožní dotazovat se na soubory.

• musíte mít aspoň oprávnění k vytváření a dotazování externích tabulek v Synapse fondu SQL (vyhrazeno nebo bez serveru).

Z panelu data vyberte soubor, ze kterého chcete vytvořit externí tabulku:

Otevře se dialogové okno. vyberte vyhrazený fond SQL nebo fond SQL bez serveru, zadejte název tabulky a vyberte otevřít skript:

skript SQL automaticky vygeneroval odvození schématu ze souboru:

Spusťte skript. Skript automaticky spustí výběr Top 100 *.:

Externí tabulka je teď vytvořená, aby ji uživatel mohl příště dotazovat na obsah této externí tabulky, a to přímo z podokna data:

Další kroky

Informace o tom, jak uložit výsledky dotazu do externí tabulky v Azure Storage, najdete v článku CETAS . Můžete také spustit dotazování Apache Spark pro externí tabulky Azure synapse.