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 spouští. Externí tabulka Azure Storage odkazuje na data umístěná v 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. Slouží .create-or-alter k úpravě existujících tabulek nebo .alter .

Oprávnění

Vyžaduje .create alespoň uživatelská oprávnění databáze a .alter minimálně oprávnění Správa tabulky.

K .create-or-alter externí tabulce s využitím ověřování spravované identity se vyžaduje oprávnění AllDatabasesAdmin .

Syntax

(.create.alter | .create-or-alter | ) externaltableTableName(Schema)=storagekind [partitionby(Partitions) [pathformat(=PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Vlastnost [, ...])]

Poznámka

kind je storage 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 Typ Vyž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 seznam jednoho nebo více názvů sloupců a datových typů oddělených čárkami, kde každá položka má formát ColumnName:ColumnType. Pokud je schéma neznámé, použijte infer_storage_schema k odvození schématu na základě obsahu externího souboru.
Oddíly string Seznam sloupců oddělených čárkami, 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 tématu Formátování oddílů .
PathFormat string Formát cesty URI externí složky dat pro použití s oddíly. Viz formát cesty.
Dataformat string ✔️ Formát dat, kterým může být kterýkoli z formátů příjmu dat. Pokud nepoužíváte JSON mapování cest, doporučujeme pro externí tabulky použít Parquet formát pro zvýšení výkonu dotazů a exportu. Při použití externí tabulky pro scénář exportu jste omezeni na následující formáty: CSV, TSVJSON a Parquet.
StorageConnectionString string ✔️ Jedna nebo více cest oddělených čárkami k Azure Blob Storage kontejnery objektů blob, systémy souborů Azure Data Lake Gen2 nebo kontejnery Azure Data Lake Gen1, včetně přihlašovacích údajů. Typ úložiště externí tabulky je určen zadanými připojovacími řetězci. Viz připojovací řetězce úložiště.
Vlastnost string Pár vlastnosti 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, aby se oddělily kontejnery úložiště, a definovat externí tabulku 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 zadané při jejím vytváření a oprávnění požadovaná 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 v objektech 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 v objektech 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á.
Microsoft Entra přístupový token Nevyžadují se žádná další oprávnění. Nevyžadují se žádná další oprávnění.
Přístupový klíč účtu úložiště Nevyžadují 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í některého z formulářů uvedených v následující tabulce.

Typ oddílu Syntax Poznámky
Virtuální sloupec Název oddílu: (datetime | string) Přečtěte si další informace o virtuálních sloupcích.
Hodnota sloupce řetězce Název oddílu:string=Columnname
Hodnota hash sloupce řetězce Název oddílu:long=hash(Columnname,Číslo) Hodnota hash je modulo Number.
Zkrácený sloupec datetime (hodnota) Název oddílu:datetime= (startofyear | startofdaystartofweekstartofmonth | | ) (Název sloupce) Projděte si dokumentaci k funkcím startofyear, startofmonth, startofweek nebo startofday .
Zkrácená hodnota sloupce Datetime (bin) Název oddílu:datetime=bin(Columnname,Timespan) Přečtěte si další informace o funkci bin .

Formát cesty

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

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

K vytvoření předpony původní cesty k souboru se elementy oddílů vykreslují jako řetězce a oddělují se odpovídajícími oddělovači textu. Pomocí makra (datetime_pattern(DateTimeFormat,PartitionName)) můžete datetime_pattern určit formát použitý pro vykreslení hodnoty oddílu datetime. Makro dodržuje specifikaci formátu .NET a umožňuje specifikátory formátu uzavřít do složených závorek. 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

Pokud chcete zkontrolovat správnost definice 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 poskytovány metodě zapisovače datového partitionBy rámce). Tento proces zabrání duplikaci dat, column1=<value>/column2=<value>/protože data už jsou v názvech složek (například ) a Spark je při čtení rozpozná.

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 v dotazu filtrovat podle virtuálních sloupců, 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 Typ Description
folder string Složka tabulky
docString string Řetězec dokumentující tabulku
compressed bool Pokud je nastaveno, 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í hlavičku. 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 nastaveno, 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 jenom soubory s touto předponou.
fileExtension string Pokud je 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 jenom soubory s touto příponou.
encoding string Označuje, jak je text zakódován: UTF8NoBOM (výchozí) nebo UTF8BOM.
sampleUris bool Pokud je nastaveno, výsledek příkazu poskytuje několik příkladů simulovaného identifikátoru URI externích datových souborů podle očekávání v definici externí tabulky. Tato možnost pomáhá ověřit, jestli jsou správně definovány parametry Partitions a PathFormat .
filesPreview bool Pokud je tato možnost nastavená, jedna z tabulek výsledků příkazu obsahuje náhled příkazu .show external table artifacts . Podobně jako sampleUrimožnost pomáhá ověřit parametry Partitions a PathFormat definice externí tabulky.
validateNotEmpty bool Pokud je tato možnost nastavená, připojovací řetězce se ověří, jestli jsou v nich obsah. Příkaz selže, pokud zadané umístění identifikátoru URI neexistuje nebo pokud nemáte dostatečná oprávnění pro přístup k němu.
dryRun bool Pokud je tato možnost nastavená, definice externí tabulky se neuchová. Tato možnost je užitečná při ověřování definice externí tabulky, zejména ve spojení s parametrem filesPreview nebo sampleUris .

Poznámka

Externí tabulka není přístupná během vytváření, pouze během dotazu a exportu. validateNotEmpty Při vytváření použijte volitelnou vlastnost a ujistěte se, ž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 Logika filtrování souborů .

Logika filtrování souborů

Při dotazování externí tabulky se výkon zlepšuje filtrováním irelevantních externích souborů úložiště. Proces iterace souborů a rozhodování, 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. Zpočátku se vzor identifikátoru URI rovná připojovací řetězec poskytnuté jako součást definice externí tabulky. Pokud jsou definovány nějaké oddíly, vykreslí se pomocí PathFormat a pak se připojí ke vzoru identifikátoru URI.

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

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

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

Poznámka

Vzor počátečního identifikátoru URI se sestavuje pomocí hodnot predikátu dotazu. To funguje nejlépe pro omezenou sadu řetězcových hodnot i pro uzavřené časové rozsahy.

Příklady

Externí tabulka bez oddílů

V následující externí tabulce bez oddílů se očekává umístění souborů přímo do definovaných kontejnerů:

.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íců

V následující externí tabulce rozdělené podle měsíců 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 rozdělena nejprve 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 se nejprve rozdělí podle hodnoty hash názvu zákazníka (modulo ten) a pak podle data. Očekávaná adresářová struktura je například customer_id=5/dt=20190201, a 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 v dotazu filtrovat podle sloupců oddílů, 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 Connectionstrings Oddíly PathFormat
Externí tabulka Objekt blob Externí tabulky Docs {"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