Tworzenie i zmienianie tabel zewnętrznych usługi Azure Storage

Przełączanie usług przy użyciu listy rozwijanej Wersja . Dowiedz się więcej o nawigacji.
Dotyczy: ✅ Microsoft Fabric ✅ Azure Data Explorer

Polecenia w tym artykule mogą służyć do tworzenia lub zmieniania tabeli zewnętrznej usługi Azure Storage w bazie danych, z której jest wykonywane polecenie. Tabela zewnętrzna usługi Azure Storage odwołuje się do danych znajdujących się w usłudze Azure Blob Storage, Azure Data Lake Store Gen1 lub Azure Data Lake Store Gen2.

Uwaga

Jeśli tabela istnieje, .create polecenie kończy się niepowodzeniem z powodu błędu. Użyj polecenia .create-or-alter lub .alter , aby zmodyfikować istniejące tabele.

Uprawnienia

Wymaga .create co najmniej uprawnień użytkownika bazy danych i wymaga .alter co najmniej uprawnień administratora tabeli.

Składnia

(.create | .alter | .create-or-alter) externaltable[()kind= [storagepartitionby( [ ...] ) [ Właściwośćpathformat=(...]]

Uwaga

kind jest storage przeznaczony dla wszystkich typów zewnętrznych magazynów danych usługi Azure Storage. blob i adl są przestarzałymi terminami.

Dowiedz się więcej na temat konwencji składni.

Parametry

Nazwisko	Typ	Wymagania	opis
TableName	string	✔️	Nazwa tabeli zewnętrznej zgodna z regułami nazw jednostek. Tabela zewnętrzna nie może mieć takiej samej nazwy jak zwykła tabela w tej samej bazie danych.
Schemat	string	✔️	Schemat danych zewnętrznych jest rozdzielaną przecinkami listą co najmniej jednej nazwy kolumn i typów danych, gdzie każdy element jest zgodny z formatem ColumnName Jeśli schemat jest nieznany, użyj infer_storage_schema , aby wywnioskować schemat na podstawie zawartości pliku zewnętrznego.
Partycje	string		Rozdzielona przecinkami lista kolumn, według których tabela zewnętrzna jest partycjonowana. Kolumna partycji może istnieć w samym pliku danych lub w ramach ścieżki pliku. Zobacz formatowanie partycji, aby dowiedzieć się, jak ta wartość powinna wyglądać.
PathFormat	string		Format ścieżki identyfikatora URI folderu danych zewnętrznych do użycia z partycjami. Zobacz format ścieżki.
DataFormat	string	✔️	Format danych, który może być dowolnym z formatów pozyskiwania. Zalecamy użycie Parquet formatu tabel zewnętrznych w celu zwiększenia wydajności zapytań i eksportu, chyba że używasz JSON mapowania ścieżek. W przypadku korzystania z tabeli zewnętrznej na potrzeby scenariusza eksportu można ograniczyć do następujących formatów: CSV, , TSVJSONi Parquet.
StorageConnectionString	string	✔️	Co najmniej jedna ścieżka rozdzielona przecinkami do kontenerów obiektów blob usługi Azure Blob Storage, systemów plików usługi Azure Data Lake Gen 2 lub kontenerów usługi Azure Data Lake Gen 1, w tym poświadczeń. Podane parametry połączenia określają typ magazynu tabel zewnętrznych. Zobacz parametry połączenia magazynu.
Właściwości	string		Para właściwości klucz-wartość w formacie PropertyName = PropertyValue. Zobacz właściwości opcjonalne.

Uwaga

Pliki CSV ze schematem nieidentycznym mogą spowodować, że dane pojawią się przesunięte lub brakujące. Zalecamy rozdzielenie plików CSV z odrębnymi schematami na oddzielne kontenery magazynu i zdefiniowanie tabeli zewnętrznej dla każdego kontenera magazynu przy użyciu odpowiedniego schematu.

Napiwek

Zapewnij więcej niż jedno konto magazynu, aby uniknąć ograniczania przepustowości magazynu podczas eksportowania dużych ilości danych do tabeli zewnętrznej. Eksport dystrybuuje zapisy między wszystkimi podanymi kontami.

Uwierzytelnianie i autoryzacja

Metoda uwierzytelniania dostępu do tabeli zewnętrznej jest oparta na parametry połączenia podanym podczas tworzenia, a uprawnienia wymagane do uzyskania dostępu do tabeli różnią się w zależności od metody uwierzytelniania.

W poniższej tabeli wymieniono obsługiwane metody uwierzytelniania dla tabel zewnętrznych usługi Azure Storage oraz uprawnienia wymagane do odczytu lub zapisu w tabeli.

Metoda uwierzytelniania	Azure Blob Storage / Data Lake Storage Gen2	Data Lake Storage Gen1
Personifikacja	Uprawnienia do odczytu: Czytelnik danych obiektu blob usługi Storage Uprawnienia do zapisu: Współautor danych obiektu blob usługi Storage	Uprawnienia do odczytu: Czytelnik Uprawnienia do zapisu: Współautor
Token dostępu współdzielonego (SAS)	Uprawnienia do odczytu: Lista i odczyt Uprawnienia do zapisu: zapis	Ta metoda uwierzytelniania nie jest obsługiwana w usłudze Gen1.
Token dostępu firmy Microsoft Entra	Nie są wymagane żadne dodatkowe uprawnienia.	Nie są wymagane żadne dodatkowe uprawnienia.
Klucz dostępu do konta magazynu	Nie są wymagane żadne dodatkowe uprawnienia.	Ta metoda uwierzytelniania nie jest obsługiwana w usłudze Gen1.

Formatowanie partycji

Lista partycji to dowolna kombinacja kolumn partycji określona przy użyciu jednej z formularzy przedstawionych w poniższej tabeli.

Typ partycji	Składnia	Uwagi
Kolumna wirtualna	PartitionName: (datetime | string)	Przeczytaj więcej na temat kolumn wirtualnych.
Wartość kolumny ciągu	: string = Nazwa_kolumny_partycji
Skrót wartości kolumny ciągu()	Numer nazwy kolumny PartitionName:long=hash(,)	Skrót to liczba modulo.
Obcięta kolumna data/godzina (wartość)	PartitionName:datetime= (startofyear | startofmonth | startofweek | startofday) (Nazwakolumny)	Zobacz dokumentację funkcji startofyear, startofmonth, startofweek lub startofday .
Obcięta wartość =bin(kolumny datetime nazwa_kolumny timeSpan,)	Przeczytaj więcej na temat funkcji bin .

Format ścieżki

Parametr PathFormat umożliwia określenie formatu ścieżki identyfikatora URI folderu danych zewnętrznych oprócz partycji. Składa się z sekwencji elementów partycji i separatorów tekstu. Element partycji odwołuje się do partycji zadeklarowanej w klauzuli partycji by , a separator tekstu jest dowolnym tekstem ujętym w cudzysłowie. Kolejne elementy partycji muszą być ustawione oddzielnie przy użyciu separatora tekstu.

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

Aby utworzyć oryginalny prefiks ścieżki pliku, elementy partycji są renderowane jako ciągi i oddzielone odpowiednimi separatorami tekstu. Możesz użyć makra datetime_pattern (datetime_pattern( PartitionName,), aby określić format używany do renderowania wartości partycji daty/godziny. Makro jest zgodne ze specyfikacją formatu .NET i umożliwia ujęcie specyfikatorów formatu w nawiasy klamrowe. Na przykład następujące dwa formaty są równoważne:

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

Domyślnie wartości daty/godziny są renderowane przy użyciu następujących formatów:

Partition — Funkcja	Format domyślny
startofyear	yyyy
startofmonth	yyyy/MM
startofweek	yyyy/MM/dd
startofday	yyyy/MM/dd
bin( Kolumna, 1d)	yyyy/MM/dd
bin( Kolumna, 1h)	yyyy/MM/dd/HH
bin( Kolumna, 1m)	yyyy/MM/dd/HH/mm

Napiwek

Aby sprawdzić poprawność definicji Partitions i PathFormat , użyj właściwości sampleUris lub filesPreview podczas tworzenia tabeli zewnętrznej.

Kolumny wirtualne

Gdy dane są eksportowane z platformy Spark, kolumny partycji (dostarczane do metody modułu zapisywania partitionBy ramki danych) nie są zapisywane w plikach danych. Ten proces pozwala uniknąć duplikowania danych, ponieważ dane są już obecne w nazwach folderów (na przykład column1=<value>/column2=<value>/), a platforma Spark może je rozpoznać podczas odczytu.

Tabele zewnętrzne obsługują odczytywanie tych danych w postaci virtual colums. Kolumny wirtualne mogą być typu string lub datetime, i są określone przy użyciu następującej składni:

.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'
)

Aby filtrować według kolumn wirtualnych w zapytaniu, określ nazwy partycji w predykacie zapytania:

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

Właściwości opcjonalne

Właściwość	Typ	opis
folder	string	Folder tabeli
docString	string	Ciąg dokumentujący tabelę
compressed	bool	Dotyczy tylko scenariusza eksportu . W przypadku ustawienia wartości true dane są eksportowane w formacie określonym przez właściwość compressionType. W przypadku ścieżki odczytu kompresja jest wykrywana automatycznie.
compressionType	string	Dotyczy tylko scenariusza eksportu . Typ kompresji wyeksportowanych plików. W przypadku plików innych niż Parquet dozwolone są tylko gzip. W przypadku plików Parquet możliwe wartości obejmują gzip, snappy, lz4_raw, brotlii zstd. Wartość domyślna to gzip. W przypadku ścieżki odczytu typ kompresji jest wykrywany automatycznie.
includeHeaders	string	W przypadku formatów tekstu rozdzielanych (CSV, TSV, ...) określa, czy pliki zawierają nagłówek. Możliwe wartości to: All (wszystkie pliki zawierają nagłówek), FirstFile (pierwszy plik w folderze zawiera nagłówek), None (żadne pliki nie zawierają nagłówka).
namePrefix	string	W przypadku ustawienia określa prefiks plików. W przypadku operacji zapisu wszystkie pliki są zapisywane przy użyciu tego prefiksu. W przypadku operacji odczytu tylko pliki z tym prefiksem są odczytywane.
fileExtension	string	W przypadku ustawienia określa rozszerzenie plików. Na zapisie nazwy plików kończą się tym sufiksem. W przypadku odczytu tylko pliki z tym rozszerzeniem pliku są odczytywane.
encoding	string	Określa sposób kodowania tekstu: UTF8NoBOM (wartość domyślna) lub UTF8BOM.
sampleUris	bool	W przypadku ustawienia wynik polecenia zawiera kilka przykładów symulowanych zewnętrznych plików danych identyfikator URI, ponieważ są one oczekiwane przez definicję tabeli zewnętrznej. Ta opcja pomaga sprawdzić, czy parametry Partitions i PathFormat są prawidłowo zdefiniowane.
filesPreview	bool	W przypadku ustawienia jedna z tabel wyników polecenia zawiera podgląd polecenia .show external table artifacts . Podobnie jak sampleUri, opcja pomaga zweryfikować parametry Partitions i PathFormat definicji tabeli zewnętrznej.
validateNotEmpty	bool	W przypadku ustawienia parametry połączenia są weryfikowane pod kątem zawartości w nich. Polecenie kończy się niepowodzeniem, jeśli określona lokalizacja identyfikatora URI nie istnieje lub jeśli nie ma wystarczających uprawnień dostępu do niego.
dryRun	bool	W przypadku ustawienia definicja tabeli zewnętrznej nie jest utrwalana. Ta opcja jest przydatna do sprawdzania poprawności definicji tabeli zewnętrznej, szczególnie w połączeniu z parametrem filesPreview or sampleUris .

Uwaga

Tabela zewnętrzna nie jest dostępna podczas tworzenia, tylko podczas wykonywania zapytań i eksportowania. Użyj właściwości opcjonalnej validateNotEmpty podczas tworzenia, aby upewnić się, że definicja tabeli jest prawidłowa, a magazyn jest dostępny.

Napiwek

Aby dowiedzieć się więcej na temat roli namePrefix i fileExtension właściwości odgrywanych w filtrowaniu plików danych podczas wykonywania zapytania, zobacz sekcję logiki filtrowania plików.

Logika filtrowania plików

Podczas wykonywania zapytań względem tabeli zewnętrznej wydajność jest większa przez filtrowanie nieistotnych plików magazynu zewnętrznego. Proces iteracji plików i decydowanie, czy plik powinien być przetwarzany, jest następujący:

1. Utwórz wzorzec identyfikatora URI, który reprezentuje miejsce, w którym można znaleźć pliki. Początkowo wzorzec identyfikatora URI jest równy parametry połączenia podany w ramach definicji tabeli zewnętrznej. Jeśli istnieją jakiekolwiek zdefiniowane partycje, są one renderowane przy użyciu parametru PathFormat, a następnie dołączane do wzorca identyfikatora URI.

2. W przypadku wszystkich plików znalezionych w utworzonych wzorcach identyfikatora URI sprawdź, czy:

   • Wartości partycji pasują do predykatów używanych w zapytaniu.
   • Nazwa obiektu blob zaczyna się od NamePrefix, jeśli taka właściwość jest zdefiniowana.
   • Nazwa obiektu blob kończy się ciągiem FileExtension, jeśli taka właściwość jest zdefiniowana.

Po spełnieniu wszystkich warunków plik jest pobierany i przetwarzany.

Uwaga

Początkowy wzorzec identyfikatora URI jest tworzony przy użyciu wartości predykatu zapytania. Działa to najlepiej w przypadku ograniczonego zestawu wartości ciągów, a także dla zamkniętych zakresów czasu.

Przykłady

Niepartycjonowana tabela zewnętrzna

W poniższej niepartyjnej tabeli zewnętrznej pliki powinny zostać umieszczone bezpośrednio w zdefiniowanych kontenerach:

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

Partycjonowane według daty

W poniższej tabeli zewnętrznej partycjonowanej według daty pliki powinny zostać umieszczone w katalogach domyślnego formatu yyyy/MM/dddaty/godziny:

.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'
)

Partycjonowane według miesięcy

W poniższej tabeli zewnętrznej podzielonej na partycje według miesiąca format katalogu to year=yyyy/month=MM:

.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' 
) 

Partycjonowane według nazwy i daty

W poniższej tabeli zewnętrznej dane są najpierw partycjonowane według nazwy klienta, a następnie według daty, co oznacza, że oczekiwana struktura katalogów to na przykład 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' 
)

Partycjonowane według wartości skrótu i daty

Poniższa tabela zewnętrzna jest najpierw partycjonowana według skrótu nazwy klienta (modulo dziesięć), a następnie według daty. Oczekiwana struktura katalogów to na przykład customer_id=5/dt=20190201, a nazwy plików danych kończą się .txt rozszerzeniem:

.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")

Filtrowanie według kolumn partycji w zapytaniu

Aby filtrować według kolumn partycji w zapytaniu, określ oryginalną nazwę kolumny w predykacie zapytania:

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

Przykładowe dane wyjściowe

TableName	TableType	Folder	DocString	Właściwości	Ciągi połączeń	Partycje	PathFormat
Tabela zewnętrzna	Obiekt blob	Tabele zewnętrzne	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)

Powiązana zawartość

• Eksportowanie danych do tabeli zewnętrznej.
• Ciągły eksport danych do tabeli zewnętrznej.