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

  • Artykuł

Polecenia w tym artykule mogą służyć do tworzenia lub zmieniania zewnętrznej tabeli 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 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 .create-or-alter polecenia lub .alter zmodyfikuj istniejące tabele.

Uprawnienia

Aby .create wymagać co najmniej uprawnień użytkownika bazy danych i .alter wymaga co najmniej uprawnień tabeli Administracja.

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 | ) externaltableSchemat)=kindstorageTableName( [Partycje) [pathformatpartition(=(byPathFormat]] dataformat=DataFormat()StorageConnectionString [, ...] ) [with(Właściwość [, ...])]

Uwaga

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

Dowiedz się więcej o konwencjach składniowych.

Parametry

Nazwa Typ Wymagane 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.
Partycji string Rozdzielona przecinkami lista kolumn, według których tabela zewnętrzna jest podzielona na partycje. Kolumna partycji może istnieć w samym pliku danych lub w ramach ścieżki pliku. Zobacz formatowanie partycji, aby dowiedzieć się, jak powinna wyglądać ta wartość.
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 formatem pozyskiwania. Zalecamy użycie Parquet formatu tabel zewnętrznych w celu zwiększenia wydajności zapytań i eksportowania, chyba że używasz JSON mapowania ścieżek. W przypadku korzystania z tabeli zewnętrznej dla scenariusza eksportu są ograniczone do następujących formatów: CSV, TSVi JSONParquet.
StorageConnectionString string ✔️ Co najmniej jedna ścieżka rozdzielona przecinkami w celu Azure Blob Storage kontenerów obiektów blob, 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 zewnętrznego magazynu tabel zależy od podanych parametrów połączenia. Zobacz Parametry połączenia magazynu.
Właściwość string Para właściwości klucz-wartość w formacie PropertyName=PropertyValue. Zobacz właściwości opcjonalne.

Uwaga

Pliki CSV z nie identycznym schematem mogą spowodować wyświetlenie danych przeniesionych lub brakujących. Zalecamy oddzielenie plików CSV z odrębnymi schematami w celu oddzielenia kontenerów magazynu i zdefiniowania tabeli zewnętrznej dla każdego kontenera magazynu przy użyciu odpowiedniego schematu.

Porada

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 będzie dystrybuować 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 obiektów blob usługi Storage
Uprawnienia do zapisu: Współautor danych obiektu blob usługi Storage		 Uprawnienia do odczytu: Czytnik
Uprawnienia do zapisu: Współautorów
Tożsamość zarządzana Uprawnienia do odczytu: Czytelnik danych obiektów blob usługi Storage
Uprawnienia do zapisu: Współautor danych obiektu blob usługi Storage		 Uprawnienia do odczytu: Czytnik
Uprawnienia do zapisu: Współautorów
Token dostępu współdzielonego (SAS) Uprawnienia do odczytu: Lista i odczyt
Uprawnienia do zapisu: Napisz		 Ta metoda uwierzytelniania nie jest obsługiwana w usłudze Gen1.
Microsoft Entra token dostępu 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 PartitionName:string=Columnname
Skrót wartości kolumny ciągu PartitionName:long=hash(Columnname,Numer) Skrót jest numerem modulo.
Obcięta kolumna daty/godziny (wartość) PartitionName:datetime= (startofyear | startofdaystartofweekstartofmonth | | ) (Nazwa kolumny) Zobacz dokumentację dotyczącą funkcji startofyear, startofmonth, startofweek lub startofday .
Obcięta wartość kolumny daty/godziny (bin) PartitionName:datetime=bin(Columnname,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 to dowolny tekst ujęta w cudzysłów. Kolejne elementy partycji należy ustawić 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ć datetime_pattern makra (datetime_pattern(DateTimeFormat,PartitionName)), aby określić format używany do renderowania wartości partycji datetime. 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='yy'/month='MM
  • year={rrrr}/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

Porada

Aby sprawdzić poprawność definicji Partitions and 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 unika duplikowania danych, ponieważ dane są już obecne w nazwach folderów (na przykład column1=<value>/column2=<value>/), a platforma Spark może rozpoznać je 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 W przypadku ustawienia 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. Na zapisie nazwy plików zakończą 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 identyfikatorów URI plików danych zewnętrznych zgodnie z oczekiwaniami definicji tabeli zewnętrznej. Ta opcja pomaga sprawdzić, czy parametry Partitions i PathFormat są poprawnie 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 lub 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.

Porada

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 iterowania plików i decydowania, czy plik powinien zostać przetworzony, jest następujący:

  1. Utwórz wzorzec identyfikatora URI, który reprezentuje miejsce, w którym znajdują się pliki. Początkowo wzorzec identyfikatora URI odpowiada parametry połączenia podanej jako część definicji tabeli zewnętrznej. Jeśli istnieją zdefiniowane partycje, są 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 są zgodne z predykatami używanymi 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 zostanie pobrany i przetworzony.

Uwaga

Początkowy wzorzec identyfikatora URI jest kompilowany przy użyciu wartości predykatu zapytania. Najlepiej sprawdza się 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 mają być umieszczane 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 jest na customer_name=Softworks/2019/02/01przykład :

.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 podzielona na partycje 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=20190201a 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 Connectionstrings Partycje PathFormat
Tabela zewnętrzna Obiekt blob Tabele zewnętrzne 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("yyyMMdd",Date)

Zawartość pokrewna