Sdílet prostřednictvím

Vytváření a změny externích tabulek služby Azure Storage

  • Článek

Příkazy v tomto článku můžete použít k vytvoření nebo změně externí tabulky Azure Storage v databázi, ze které se příkaz spustí. Externí tabulka Azure Storage odkazuje na data umístěná ve službě Azure Blob Storage, Azure Data Lake Store Gen1 nebo Azure Data Lake Store Gen2.

Poznámka:

Pokud tabulka existuje, .create příkaz selže s chybou. Použijte .create-or-alter nebo .alter upravte existující tabulky.

Oprávnění

Pokud chcete .create vyžadovat aspoň oprávnění uživatele databáze a vyžaduje .alter alespoň oprávnění správce tabulky.

K .create-or-alter externí tabulce používající ověřování spravované identity se vyžadují oprávnění AllDatabasesAdmin .

Syntaxe

(.create | .create-or-alter | .alter) external table TableName (Schema) storage = kind [partition by(Partitions) [pathformat= (PathFormat)]] dataformat = DataFormat (StorageConnectionString [, ...] ) [with (Vlastnost [, ...])]

Poznámka:

kind je storage určená pro všechny typy externích úložišť dat Azure Storage. blob a adl jsou zastaralé termíny.

Přečtěte si další informace o konvencích syntaxe.

Parametry

Název Type Požadováno Popis
TableName string ✔️ Název externí tabulky, který dodržuje pravidla názvů entit. Externí tabulka nemůže mít stejný název jako běžná tabulka ve stejné databázi.
Schéma string ✔️ Schéma externích dat je čárkami oddělený seznam jednoho nebo více názvů sloupců a datových typů, kde každá položka má formát: ColumnName : ColumnType. Pokud schéma není známo, použijte infer_storage_schema k odvození schématu na základě obsahu externího souboru.
Oddíly string Čárkami oddělený seznam sloupců, podle kterých je externí tabulka rozdělená na oddíly. Sloupec oddílu může existovat v samotném datovém souboru nebo jako součást cesty k souboru. Informace o tom, jak by tato hodnota měla vypadat, najdete v formátování oddílů.
PathFormat string Formát cesty URI externí složky dat, který se má použít s oddíly. Viz formát cesty.
Datový formát string ✔️ Formát dat, který může být libovolný z formátů příjmu dat. K vylepšení výkonu dotazů a exportu Parquet doporučujeme použít formát externích tabulek, pokud nepoužíváte JSON mapování cest. Při použití externí tabulky pro scénář exportu jste omezeni na následující formáty: CSV, TSVJSON a Parquet.
StorageConnectionString string ✔️ Jednu nebo více cest oddělených čárkami ke kontejnerům objektů blob služby Azure Blob Storage, systémům souborů Azure Data Lake Gen2 nebo kontejnerům Azure Data Lake Gen1, včetně přihlašovacích údajů. Typ externího úložiště tabulek určuje poskytnutá připojovací řetězec. Viz připojovací řetězec úložiště.
Vlastnost string Dvojice vlastností klíč-hodnota ve formátu PropertyName = PropertyValue. Viz volitelné vlastnosti.

Poznámka:

Soubory CSV s neidentickým schématem můžou mít za následek posunutá nebo chybějící data. Doporučujeme oddělit soubory CSV s odlišnými schématy k oddělení kontejnerů úložiště a definování externí tabulky pro každý kontejner úložiště se správným schématem.

Tip

Poskytněte více než jeden účet úložiště, abyste se vyhnuli omezování úložiště při exportu velkých objemů dat do externí tabulky. Export distribuuje zápisy mezi všechny zadané účty.

Ověřování a autorizace

Metoda ověřování pro přístup k externí tabulce je založená na připojovací řetězec poskytnuté během jejího vytváření a oprávnění potřebná pro přístup k tabulce se liší v závislosti na metodě ověřování.

Následující tabulka uvádí podporované metody ověřování pro externí tabulky Azure Storage a oprávnění potřebná ke čtení nebo zápisu do tabulky.

Metoda ověřování Azure Blob Storage / Data Lake Storage Gen2 Data Lake Storage Gen1
Zosobnění Oprávnění ke čtení: Čtenář dat objektů blob služby Storage
Oprávnění k zápisu: Přispěvatel dat objektů blob služby Storage		 Oprávnění ke čtení: Čtenář
Oprávnění k zápisu: Přispěvatel
Spravovaná identita Oprávnění ke čtení: Čtenář dat objektů blob služby Storage
Oprávnění k zápisu: Přispěvatel dat objektů blob služby Storage		 Oprávnění ke čtení: Čtenář
Oprávnění k zápisu: Přispěvatel
Token sdíleného přístupu (SAS) Oprávnění ke čtení: Seznam + Čtení
Oprávnění k zápisu: Zápis		 Tato metoda ověřování není v Gen1 podporovaná.
Přístupový token Microsoft Entra Nevyžaduje se žádná další oprávnění. Nevyžaduje se žádná další oprávnění.
Přístupový klíč účtu úložiště Nevyžaduje se žádná další oprávnění. Tato metoda ověřování není v Gen1 podporovaná.

Formátování oddílů

Seznam oddílů je libovolná kombinace sloupců oddílů zadaná pomocí jednoho z formulářů zobrazených v následující tabulce.

Typ oddílu Syntaxe Notes
Virtuální sloupec PartitionName : (datetime | string) Přečtěte si další informace o virtuálních sloupcích.
Řetězcová hodnota sloupce PartitionName ColumnName : string =
Hodnota hash () řetězcové hodnoty sloupce PartitionName ColumnName=: long hash(, Number) Hodnota hash je modulo Number.
Zkrácený sloupec datetime (hodnota) PartitionName datetime : = (startofyearstartofweek | | | startofdaystartofmonth) ( ColumnName ) Viz dokumentace k funkcím startofyear, startofmonth, startofweek nebo startofday .
Zkrácený sloupec datetime (hodnota) PartitionName ColumnName , = bin datetime : ( TimeSpan ) Přečtěte si další informace o funkci přihrádky .

Formát cesty

Parametr PathFormat umožňuje kromě oddílů zadat formát cesty URI externí složky dat. Skládá se z posloupnosti prvků oddílu a oddělovačů textu. Element oddílu odkazuje na oddíl deklarovaný v klauzuli partition by a oddělovač textu je jakýkoli text uzavřený v uvozovkách. Po sobě jdoucí prvky oddílu musí být odděleny pomocí oddělovače textu.

[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]

Chcete-li vytvořit předponu původní cesty k souboru, prvky oddílu se vykreslují jako řetězce a jsou oddělené odpovídajícími oddělovači textu. Makro (DateTimeFormat, PartitionName)) můžete použít datetime_pattern k určení formátu použitého k vykreslení hodnoty oddílu datetime.datetime_pattern( Makro dodržuje specifikaci formátu .NET a umožňuje, aby specifikátory formátu byly uzavřeny ve složených závorkách. Například následující dva formáty jsou ekvivalentní:

  • 'year='yyyy'/month='MM
  • year={yyyy}/month={MM}

Ve výchozím nastavení se hodnoty datetime vykreslují pomocí následujících formátů:

Partition – funkce Výchozí formát
startofyear yyyy
startofmonth yyyy/MM
startofweek yyyy/MM/dd
startofday yyyy/MM/dd
bin(Sloupec, 1d) yyyy/MM/dd
bin(Sloupec, 1h) yyyy/MM/dd/HH
bin(Sloupec, 1m) yyyy/MM/dd/HH/mm

Tip

Chcete-li zkontrolovat správnost definic oddílů a PathFormat , použijte vlastnost sampleUris nebo filesPreview při vytváření externí tabulky.

Virtuální sloupce

Při exportu dat ze Sparku se do datových souborů nezapisují sloupce oddílů (které jsou k dispozici metodě zapisovače datového partitionBy rámce). Tento proces zabraňuje duplikaci dat, protože data už jsou v názvech složek (například column1=<value>/column2=<value>/) a Spark je může při čtení rozpoznat.

Externí tabulky podporují čtení těchto dat ve formě virtual colums. Virtuální sloupce můžou být typu string nebo datetimea zadává se pomocí následující syntaxe:

.create external table ExternalTable (EventName:string, Revenue:double)  
kind=storage  
partition by (CustomerName:string, Date:datetime)  
pathformat=("customer=" CustomerName "/date=" datetime_pattern("yyyyMMdd", Date))  
dataformat=parquet
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)

Pokud chcete filtrovat podle virtuálních sloupců v dotazu, zadejte názvy oddílů v predikátu dotazu:

external_table("ExternalTable")
 | where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Volitelné vlastnosti

Vlastnost Type Popis
folder string Složka tabulky
docString string Řetězec dokumentující tabulku
compressed bool Pokud je tato sada nastavená, označuje, jestli jsou soubory komprimované jako .gz soubory (používá se pouze ve scénáři exportu).
includeHeaders string U textových formátů s oddělovači (CSV, TSV, ...) označuje, jestli soubory obsahují záhlaví. Možné hodnoty jsou: All (všechny soubory obsahují záhlaví), FirstFile (první soubor ve složce obsahuje záhlaví) None (žádné soubory neobsahují záhlaví).
namePrefix string Pokud je tato sada nastavená, označuje předponu souborů. Při operacích zápisu budou všechny soubory zapsány s touto předponou. Při operacích čtení se čtou pouze soubory s touto předponou.
fileExtension string Pokud je tato sada nastavená, označuje přípony souborů. Při zápisu budou názvy souborů končit touto příponou. Při čtení se budou číst pouze soubory s touto příponou souboru.
encoding string Určuje, jak je text kódován: UTF8NoBOM (výchozí) nebo UTF8BOM.
sampleUris bool Pokud je tato sada nastavená, poskytuje výsledek příkazu několik příkladů identifikátoru URI simulovaných externích datových souborů, protože se očekávají v definici externí tabulky. Tato možnost pomáhá ověřit, jestli jsou správně definované parametry Partitions a PathFormat .
filesPreview bool Pokud je tato sada nastavená, jedna z tabulek výsledků příkazu obsahuje náhled příkazu .show external table artifacts . Podobně jako sampleUritato možnost pomáhá ověřit parametry Partitions a PathFormat definice externí tabulky.
validateNotEmpty bool Pokud je tato možnost nastavená, ověřují se připojovací řetězec, že mají obsah v nich. Příkaz selže, pokud zadané umístění identifikátoru URI neexistuje nebo pokud k němu nemáte dostatečná oprávnění.
dryRun bool Pokud je tato sada nastavená, definice externí tabulky se neuchová. Tato možnost je užitečná pro ověřování definice externí tabulky, zejména ve spojení s parametrem nebo parametrem filesPreview sampleUris .

Poznámka:

Externí tabulka není během vytváření přístupná, pouze během dotazu a exportu. Při vytváření použijte volitelnou validateNotEmpty vlastnost, abyste měli jistotu, že je definice tabulky platná a úložiště je přístupné.

Tip

Další informace o roli namePrefix a fileExtension vlastnostech při filtrování datových souborů během dotazu najdete v části logiky filtrování souborů.

Logika filtrování souborů

Při dotazování na externí tabulku se výkon vylepšuje filtrováním irelevantních externích souborů úložiště. Proces iterace souborů a rozhodování o tom, jestli se má soubor zpracovat, je následující:

  1. Vytvořte vzor identifikátoru URI, který představuje místo, kde se nacházejí soubory. Vzor identifikátoru URI se zpočátku rovná připojovací řetězec poskytnuté jako součást definice externí tabulky. Pokud jsou definované nějaké oddíly, vykreslí se pomocí PathFormat a pak se připojí k vzoru identifikátoru URI.

  2. U všech souborů nalezených ve vytvořených vzorech identifikátorů URI zkontrolujte, že:

    • Hodnoty oddílů odpovídají predikátům použitým v dotazu.
    • Název objektu blob začíná NamePrefix, pokud je taková vlastnost definována.
    • Název objektu blob končí FileExtension, pokud je taková vlastnost definována.

Po splnění všech podmínek se soubor načte a zpracuje.

Poznámka:

Počáteční vzor identifikátoru URI se sestavuje pomocí hodnot predikátu dotazu. To je nejvhodnější pro omezenou sadu řetězcových hodnot i pro uzavřené časové rozsahy.

Příklady

Nedělená externí tabulka

V následující nedělené externí tabulce se očekává, že se soubory umístí přímo pod definované kontejnery:

.create external table ExternalTable (x:long, s:string)  
kind=storage 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

Dělené podle data

V následující externí tabulce rozdělené podle data se očekává umístění souborů do adresářů výchozího formátu yyyy/MM/dddatetime:

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage
partition by (Date:datetime = bin(Timestamp, 1d)) 
dataformat=csv 
( 
   h@'abfss://filesystem@storageaccount.dfs.core.windows.net/path;secretKey'
)

Dělené podle měsíce

V následující externí tabulce rozdělené podle měsíce je year=yyyy/month=MMformát adresáře:

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage 
partition by (Month:datetime = startofmonth(Timestamp)) 
pathformat=(datetime_pattern("'year='yyyy'/month='MM", Month)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

Dělené podle názvu a data

V následující externí tabulce jsou data nejprve rozdělena podle jména zákazníka a potom podle data, což znamená, že očekávaná adresářová struktura je například customer_name=Softworks/2019/02/01:

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerNamePart:string = CustomerName, Date:datetime = startofday(Timestamp)) 
pathformat=("customer_name=" CustomerNamePart "/" Date)
dataformat=csv 
(  
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

Dělené podle hodnoty hash a data

Následující externí tabulka je nejprve rozdělena podle hodnoty hash názvu zákazníka (modulo ten) a potom podle data. Očekávaná adresářová struktura je například customer_id=5/dt=20190201a názvy datových souborů končí příponou .txt :

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerId:long = hash(CustomerName, 10), Date:datetime = startofday(Timestamp)) 
pathformat=("customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd", Date)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
with (fileExtension = ".txt")

Filtrování podle sloupců oddílů v dotazu

Pokud chcete filtrovat podle sloupců oddílů v dotazu, zadejte původní název sloupce v predikátu dotazu:

external_table("ExternalTable")
 | where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Ukázkový výstup

TableName TableType Složka DocString Vlastnosti Připojovací řetězce Oddíly PathFormat
Externí tabulka Objekt blob Externí tabulky Dokumenty {"Format":"Csv","Compressed":false,"CompressionType":null,"FileExtension":null,"IncludeHeaders":"None","Encoding":null,"NamePrefix":null} ["https://storageaccount.blob.core.windows.net/container1;*******"] [{"Mod":10,"Name":"CustomerId","ColumnName":"CustomerName","Ordinal":0},{"Function":"StartOfDay","Name":"Date","ColumnName":"Timestamp","Ordinal":1}] "customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd";Date)

Související obsah