Tworzenie i zmienianie tabel zewnętrznych usługi Azure Storage
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 zakoń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.
Do .create-or-alter tabeli zewnętrznej przy użyciu uwierzytelniania tożsamości zarządzanej wymagane są uprawnienia AllDatabasesAdmin .
Składnia
(.create | | .alter.create-or-alter) external table Schemat storage=) kind TableName ([partition (byPartycje) [pathformat =(PathFormat]] dataformat = DataFormat)( StorageConnectionString [, ...] ) [ Właściwość [,with (...]])
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 | Type | 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 : ColumnType. 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, TSVi JSON 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ń. Typ magazynu tabel zewnętrznych jest określany przez podane parametry połączenia. 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 z niezwiązanym schematem 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 spowoduje dystrybucję zapisów 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 |
| Tożsamość zarządzana | 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 : = (startofyearstartofweek | | | startofdaystartofmonth) ( 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(DateTimeFormat, 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ść | Type | Opis |
|---|---|---|
folder |
string |
Folder tabeli |
docString |
string |
Ciąg dokumentujący tabelę |
compressed |
bool |
Jeśli ustawiono, wskazuje, czy pliki są kompresowane jako .gz pliki (używane tylko w scenariuszu eksportu) |
includeHeaders |
string |
W przypadku formatów tekstu rozdzielanego (CSV, TSV, ...) wskazuje, 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 wskazuje prefiks plików. W przypadku operacji zapisu wszystkie pliki zostaną zapisane przy użyciu tego prefiksu. W przypadku operacji odczytu tylko pliki z tym prefiksem są odczytywane. |
fileExtension |
string |
W przypadku ustawienia wskazuje rozszerzenia plików. Podczas zapisu nazwy plików będą kończyć się tym sufiksem. W przypadku odczytu będą odczytywane tylko pliki z tym rozszerzeniem pliku. |
encoding |
string |
Wskazuje, jak tekst jest zakodowany: 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 zakoń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:
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.
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
Tabela zewnętrzna bez partycjonowania
W poniższej tabeli zewnętrznej bez partycji 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ść
- Wykonywanie zapytań względem tabel zewnętrznych.
Eksportowanie danych do tabeli zewnętrznej.
Ciągły eksport danych do tabeli zewnętrznej.