Azure Depolama dış tablolarını oluşturma ve değiştirme

  • Makale

Bu makaledeki komutlar, komutun yürütüldiği veritabanında bir Azure Depolama dış tablosu oluşturmak veya değiştirmek için kullanılabilir. Azure Depolama dış tablosu Azure Blob Depolama, Azure Data Lake Store 1. Nesil veya Azure Data Lake Store 2. Nesil'de bulunan verilere başvurur.

Not

Tablo varsa, .create komut bir hatayla başarısız olur. Varolan tabloları değiştirmek için veya .alter kullanın.create-or-alter.

İzinler

Için .create en az Veritabanı Kullanıcısı izinleri ve .alter en az Tablo Yönetici izinleri gerekir.

Yönetilen kimlik kimlik doğrulaması kullanan bir dış tablo için .create-or-alterAllDatabasesAdmin izinleri gerekir.

Syntax

(.create | .create-or-alter | .alter) externaltableTableName(Şeması)=kindstorage [partitionby(Partitions) [pathformat(=PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Özellik [, ...])]

Not

kind tüm storage Azure Depolama dış veri deposu türleri içindir. blob ve adl kullanım dışı bırakılan terimlerdir.

Söz dizimi kuralları hakkında daha fazla bilgi edinin.

Parametreler

Ad Tür Gerekli Açıklama
TableName string ✔️ Varlık adları kurallarına bağlı olan bir dış tablo adı. Dış tablonun adı aynı veritabanındaki normal tabloyla aynı olamaz.
Şema string ✔️ Dış veri şeması, bir veya daha fazla sütun adı ve veri türünden oluşan virgülle ayrılmış bir listedir ve her öğe şu biçimdedir: ColumnName:ColumnType. Şema bilinmiyorsa, dış dosya içeriğine göre şemayı çıkarsamak için infer_storage_schema kullanın.
Bölüm string Dış tablonun bölümlendiği sütunların virgülle ayrılmış listesi. Bölüm sütunu veri dosyasının kendisinde veya dosya yolunun bir parçası olarak bulunabilir. Bu değerin nasıl görünmesi gerektiğini öğrenmek için bölüm biçimlendirmesine bakın.
PathFormat string Bölümler ile kullanılacak bir dış veri klasörü URI yolu biçimi. Bkz. yol biçimi.
Dataformat string ✔️ Veri biçimi; alım biçimlerinden herhangi biri olabilir. Yol eşlemesi Parquet kullanmadığınız sürece sorgu ve dışarı aktarma performansını geliştirmek için dış tablolar biçimini kullanmanızı JSON öneririz. Dışarı aktarma senaryosunda dış tablo kullanırken, şu biçimler ile sınırlısınız: CSV, TSVve JSONParquet.
StorageConnectionString string ✔️ Kimlik bilgileri dahil olmak üzere Azure Blob Depolama blob kapsayıcılarına, Azure Data Lake 2. Nesil dosya sistemlerine veya Azure Data Lake 1. Nesil kapsayıcılarına yönelik bir veya daha fazla virgülle ayrılmış yol. Dış tablo depolama türü, sağlanan bağlantı dizeleri tarafından belirlenir. Bkz. depolama bağlantı dizeleri.
Özellik string PropertyName= PropertyValue biçiminde bir anahtar-değerözellik çifti. İsteğe bağlı özelliklere bakın.

Not

Aynı olmayan şemaya sahip CSV dosyaları verilerin kaydırılmış veya eksik görünmesine neden olabilir. Ayrı şemalara sahip CSV dosyalarını ayrı depolama kapsayıcılarına ayırmanızı ve uygun şemaya sahip her depolama kapsayıcısı için bir dış tablo tanımlamanızı öneririz.

İpucu

Büyük miktarlardaki verileri dış tabloya aktarırken depolama azaltmasını önlemek için tek bir depolama hesabından daha fazlasını sağlayın. Dışarı aktarma, yazmaları sağlanan tüm hesaplar arasında dağıtır.

Kimlik doğrulaması ve yetkilendirme

Dış tabloya erişmek için kullanılan kimlik doğrulama yöntemi, oluşturma sırasında sağlanan bağlantı dizesi temel alır ve tabloya erişmek için gereken izinler kimlik doğrulama yöntemine bağlı olarak değişir.

Aşağıdaki tabloda, Azure Depolama dış tabloları için desteklenen kimlik doğrulama yöntemleri ve tabloyu okumak veya tabloya yazmak için gereken izinler listelenmektedir.

Kimlik doğrulama yöntemi Azure Blob Depolama / Data Lake Storage 2. Nesil Data Lake Storage 1. Nesil
Kimliğe bürünme Okuma izinleri: Depolama Blobu Veri Okuyucusu
Yazma izinleri: Depolama Blob Verileri Katkıda Bulunanı		 Okuma izinleri: Okuyucu
Yazma izinleri: Katılımcı
Yönetilen kimlik Okuma izinleri: Depolama Blobu Veri Okuyucusu
Yazma izinleri: Depolama Blob Verileri Katkıda Bulunanı		 Okuma izinleri: Okuyucu
Yazma izinleri: Katılımcı
Paylaşılan Erişim (SAS) belirteci Okuma izinleri: Liste + Okuma
Yazma izinleri: Yazmak		 Bu kimlik doğrulama yöntemi 1. Nesil'de desteklenmez.
erişim belirtecini Microsoft Entra Ek izin gerekmez. Ek izin gerekmez.
Depolama hesabı erişim anahtarı Ek izin gerekmez. Bu kimlik doğrulama yöntemi 1. Nesil'de desteklenmez.

Bölümleri biçimlendirme

Bölümler listesi, aşağıdaki tabloda gösterilen formlardan biri kullanılarak belirtilen bölüm sütunlarının herhangi bir bileşimidir.

Bölüm Türü Syntax Notlar
Sanal sütun Partitionname: (datetime | string) Sanal sütunlar hakkında daha fazla bilgi edinin.
Dize sütun değeri Partitionname:string=Columnname
Dize sütun değeri karması Partitionname:long=hash(Columnname,Numarası) Karma modulo Number şeklindedir.
Kesilmiş tarih saat sütunu (değer) Partitionname:datetime= (startofyear | startofdaystartofweekstartofmonth | | ) (ColumnName) startofyear, startofmonth, startofweek veya startofday işlevleriyle ilgili belgelere bakın.
Kesilmiş Tarih Saat Sütun Değeri (bölme) Partitionname:datetime=bin(Columnname,Timespan) Bölme işlevi hakkında daha fazla bilgi edinin.

Yol biçimi

PathFormat parametresi, bölümlere ek olarak dış veri klasörü URI yolunun biçimini belirtmenize olanak tanır. Bir dizi bölüm öğesinden ve metin ayırıcılarından oluşur. Bölüm öğesi, bölüm yan tümcesinde bildirilen bir bölüme by başvurur ve metin ayırıcısı tırnak içine alınmış herhangi bir metindir. Ardışık bölüm öğelerinin metin ayırıcısı kullanılarak ayrılmaları gerekir.

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

Özgün dosya yolu ön ekini oluşturmak için bölüm öğeleri dize olarak işlenir ve karşılık gelen metin ayırıcılarıyla ayrılır. Bir tarih saat bölümü değerini işlemek için kullanılan biçimi belirtmek için makroyu (datetime_pattern(DateTimeFormat,PartitionName)) kullanabilirsinizdatetime_pattern. Makro .NET biçim belirtimine bağlıdır ve biçim tanımlayıcılarının köşeli ayraç içine alınmasına izin verir. Örneğin, aşağıdaki iki biçim eşdeğerdir:

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

Varsayılan olarak, tarih saat değerleri aşağıdaki biçimler kullanılarak işlenir:

Bölüm işlevi Varsayılan biçim
startofyear yyyy
startofmonth yyyy/MM
startofweek yyyy/MM/dd
startofday yyyy/MM/dd
bin(Sütun, 1d) yyyy/MM/dd
bin(Sütun, 1h) yyyy/MM/dd/HH
bin(Sütun, 1m) yyyy/MM/dd/HH/mm

İpucu

Bölümlerin ve PathFormat tanımının doğruluğunu denetlemek için veya dış tablo oluştururken özelliğini sampleUrisfilesPreview kullanın.

Sanal sütunlar

Veriler Spark'tan dışarı aktarıldığında bölüm sütunları (veri çerçevesi yazıcısının partitionBy yöntemine sağlanır) veri dosyalarına yazılamaz. Bu işlem, veriler klasör adlarında (örneğin) column1=<value>/column2=<value>/zaten bulunduğundan ve Spark okunduktan sonra bunu tanıyabileceğinden, veri yinelemeyi önler.

Dış tablolar bu verilerin biçiminde virtual columsokunmasını destekler. Sanal sütunlar veya datetimetüründe string olabilir ve aşağıdaki söz dizimi kullanılarak belirtilir:

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

Sorgudaki sanal sütunlara göre filtrelemek için sorgu koşulunda bölüm adlarını belirtin:

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

İsteğe bağlı özellikler

Özellik Tür Description
folder string Tablonun klasörü
docString string Tabloyu belgeleme dizesi
compressed bool Ayarlanırsa, dosyaların dosya olarak .gz sıkıştırılıp sıkıştırılmadığını gösterir (yalnızca dışarı aktarma senaryosunda kullanılır)
includeHeaders string Sınırlandırılmış metin biçimleri için (CSV, TSV, ...), dosyaların üst bilgi içerip içermediğini gösterir. Olası değerler şunlardır: All (tüm dosyalar üst bilgi içerir), FirstFile (klasördeki ilk dosya üst bilgi içerir), None (hiçbir dosya üst bilgi içermez).
namePrefix string Ayarlanırsa, dosyaların ön ekini gösterir. Yazma işlemlerinde, tüm dosyalar bu ön ek ile yazılır. Okuma işlemlerinde yalnızca bu ön eke sahip dosyalar okunur.
fileExtension string Ayarlanırsa, dosyaların dosya uzantılarını gösterir. Yazmada, dosya adları bu sonekle sona erer. Okumada, yalnızca bu dosya uzantısına sahip dosyalar okunur.
encoding string Metnin nasıl kodlanmış olduğunu gösterir: UTF8NoBOM (varsayılan) veya UTF8BOM.
sampleUris bool Ayarlanırsa, komut sonucu dış tablo tanımı tarafından beklendiği gibi sanal dış veri dosyası URI'si örnekleri sağlar. Bu seçenek , Bölümlerin ve PathFormat parametrelerinin düzgün tanımlanıp tanımlanmadığını doğrulamaya yardımcı olur.
filesPreview bool Ayarlanırsa, komut sonuç tablolarından biri .show dış tablo yapıtları komutunun önizlemesini içerir. gibi sampleUriseçeneği de dış tablo tanımının Bölümleri ve PathFormat parametrelerini doğrulamaya yardımcı olur.
validateNotEmpty bool Ayarlanırsa, bağlantı dizelerinde içerik olduğu doğrulanır. Belirtilen URI konumu yoksa veya erişim için yeterli izin yoksa komut başarısız olur.
dryRun bool Ayarlanırsa, dış tablo tanımı kalıcı olmaz. Bu seçenek, özellikle veya sampleUris parametresiyle filesPreview birlikte dış tablo tanımını doğrulamak için kullanışlıdır.

Not

Dış tabloya oluşturma sırasında değil, yalnızca sorgu ve dışarı aktarma sırasında erişilir. Tablo tanımının validateNotEmpty geçerli ve depolama alanının erişilebilir olduğundan emin olmak için oluşturma sırasında isteğe bağlı özelliğini kullanın.

İpucu

Sorgu sırasında veri dosyası filtrelemede rol ve fileExtension özellikler hakkında namePrefix daha fazla bilgi edinmek için dosya filtreleme mantığı bölümüne bakın.

Dosya filtreleme mantığı

Dış tablo sorgulanırken, ilgisiz dış depolama dosyaları filtrelenerek performans iyileştirilir. Dosyaları yineleme ve bir dosyanın işlenip işlenmeyeceğine karar verme işlemi aşağıdaki gibidir:

  1. Dosyaların bulunduğu bir yeri temsil eden bir URI deseni oluşturun. Başlangıçta, URI deseni dış tablo tanımının bir parçası olarak sağlanan bir bağlantı dizesi eşittir. Tanımlanmış bölümler varsa, bunlar PathFormat kullanılarak işlenir ve ardından URI desenine eklenir.

  2. Oluşturulan URI desenleri altında bulunan tüm dosyalar için şunları denetleyin:

    • Bölüm değerleri, sorguda kullanılan koşullarla eşleşmektedir.
    • Blob adı, böyle bir özellik tanımlanmışsa ile NamePrefixbaşlar.
    • Böyle bir özellik tanımlanmışsa blob adı ile FileExtensionbiter.

Tüm koşullar karşılandıktan sonra dosya getirilir ve işlenir.

Not

İlk URI deseni sorgu koşulu değerleri kullanılarak oluşturulur. Bu, hem sınırlı bir dize değerleri kümesi hem de kapalı zaman aralıkları için en iyi sonucu verir.

Örnekler

Bölümlenmemiş dış tablo

Aşağıdaki bölümlenmemiş dış tabloda, dosyaların doğrudan tanımlanan kapsayıcıların altına yerleştirilmesi beklenir:

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

Tarihe göre bölümlenmiş

Tarihe göre bölümlenmiş aşağıdaki dış tabloda, dosyaların varsayılan datetime biçiminin yyyy/MM/dddizinlerine yerleştirilmesi beklenir:

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

Aya göre bölümlenmiş

Aya göre bölümlenmiş aşağıdaki dış tabloda dizin biçimi: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' 
)

Ada ve tarihe göre bölümlenmiş

Aşağıdaki dış tabloda veriler önce müşteri adına ve ardından tarihe göre bölümlenmiştir; başka bir deyişle beklenen dizin yapısı şu şekildedir 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' 
)

Karma ve tarihe göre bölümlenmiş

Aşağıdaki dış tablo önce müşteri adı karması (mod on) ve ardından tarihe göre bölümlenmiştir. Beklenen dizin yapısı, örneğin , customer_id=5/dt=20190201ve veri dosyası adları uzantısıyla .txt biter:

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

Sorgudaki bölüm sütunlarına göre filtreleme

Sorgudaki bölüm sütunlarına göre filtrelemek için sorgu koşulunda özgün sütun adını belirtin:

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

Örnek Çıkış

TableName TableType Klasör DocString Özellikler Connectionstrings Bölümler PathFormat
ExternalTable Blob ExternalTable'lar 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)

İlgili içerik