Membuat dan mengubah tabel eksternal Azure Storage

Artikel
06/23/2022
8 menit untuk membaca

Perintah berikut menjelaskan cara membuat tabel eksternal yang terletak di Azure Blob Storage, Azure Data Lake Store Gen1, atau Azure Data Lake Store Gen2.

Untuk pengenalan fitur tabel Azure Storage eksternal, lihat Mengkueri data di Azure Data Lake menggunakan Azure Data Explorer.

.create atau .alter external table

Sintaks

(.create|.alter|.create-or-alter) externaltableTableName(Schema)
kind = storage
[partitionby(Partitions) [pathformat=(PathFormat)]]
dataformat=Format
(StorageConnectionString [, ...] )
[with(PropertyName=Value, ... )]

Membuat atau mengubah tabel eksternal baru dalam database tempat perintah dijalankan.

Catatan

Jika tabel ada, perintah .create akan gagal dengan kesalahan. Gunakan .create-or-alter atau .alter mengubah tabel yang ada.
Tabel eksternal tidak diakses selama waktu pembuatan. Ini hanya akan diakses selama kueri/ekspor. Anda dapat menggunakan properti validateNotEmpty (opsional) selama waktu pembuatan untuk memastikan definisi tabel eksternal valid dan penyimpanan yang mendasarinya dapat diakses.
Operasi ini memerlukan izin pengguna database untuk .create dan izin admin tabel untuk .alter.

Parameter

TableName

Nama tabel eksternal yang mematuhi aturan nama entitas. Tabel eksternal tidak dapat memiliki nama yang sama dengan tabel biasa dalam database yang sama.

Schema

Skema data eksternal dijelaskan menggunakan format berikut:

ColumnName:ColumnType [,ColumnName:ColumnType ...]

dengan ColumnName mematuhi aturan penamaan entitas, dan ColumnType adalah salah satu jenis data yang didukung.

Tip

Jika skema data eksternal tidak diketahui, gunakan plug-in infer_storage_schema, yang membantu menyimpulkan skema berdasarkan konten file eksternal.

Kind

Jenis tabel eksternal. Dalam hal ini, storage harus digunakan (bukan sql).

Catatan

Istilah yang tidak digunakan lagi: blob untuk Azure Storage Blob atau Storage Azure Data Lake Gen 2, adl untuk Storage Azure Data Lake Gen 1.

Partitions

Daftar kolom yang dipisahkan koma di mana tabel eksternal dipartisi. Kolom partisi dapat berada di file data itu sendiri, atau sebagai bagian dari jalur file (baca selengkapnya tentang kolom virtual).

Daftar partisi adalah kombinasi dari kolom partisi, yang ditentukan menggunakan salah satu bentuk berikut:

Partisi, mewakili kolom virtual.

PartitionName: (datetime | string)
Partisi, berdasarkan nilai kolom string.

PartitionName:string=ColumnName
Partisi, berdasarkan hash nilai kolom string, modulo Number.

PartitionName:long=hash(ColumnName,Number)
Partisi, berdasarkan nilai terpotong dari kolom tanggalwaktu. Lihat dokumentasi tentang fungsi startofyear, startofmonth, startofweek, startofday, atau bin.

PartitionName:datetime= (startofyear | startofmonth | startofweek | startofday) (ColumnName)
PartitionName:datetime=bin(ColumnName,TimeSpan)

Untuk memeriksa kebenaran definisi pemartisian, gunakan properti sampleUris atau filesPreview saat membuat tabel eksternal.

PathFormat

Format jalur URI folder data eksternal, yang dapat ditentukan selain partisi. Format jalur adalah urutan elemen partisi dan pemisah teks:

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

Dengan Partition mengacu pada partisi yang dideklarasikan dalam klausa partitionby, dan StringSeparator adalah teks apa pun yang terlampir dalam tanda kutip. Elemen partisi berturut-turut harus dipisahkan menggunakan StringSeparator.

Awalan jalur file asli dapat disusun menggunakan elemen partisi yang dirender sebagai string dan dipisahkan dengan pemisah teks yang sesuai. Untuk menentukan format yang digunakan untuk merender nilai partisi tanggalwaktu, makro berikut dapat digunakan:

datetime_pattern(DateTimeFormat,PartitionName)

Dengan DateTimeFormat mematuhi spesifikasi format .NET, dengan ekstensi yang memungkinkan untuk melampirkan penentu format ke dalam kurung kurawal. Misalnya, dua format berikut setara:

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

Secara default, nilai tanggalwaktu dirender menggunakan format berikut:

Fungsi partisi	Format default
`startofyear`	`yyyy`
`startofmonth`	`yyyy/MM`
`startofweek`	`yyyy/MM/dd`
`startofday`	`yyyy/MM/dd`
`bin(`Column`, 1d)`	`yyyy/MM/dd`
`bin(`Column`, 1h)`	`yyyy/MM/dd/HH`
`bin(`Column`, 1m)`	`yyyy/MM/dd/HH/mm`

Jika PathFormat dihilangkan dari definisi tabel eksternal, ini diasumsikan bahwa semua partisi, dalam urutan yang sama persis dengan yang didefinisikan, akan dipisahkan menggunakan pemisah /. Partisi dirender menggunakan presentasi string default-nya.

Untuk memeriksa kebenaran definisi format jalur, gunakan properti sampleUris atau filesPreview saat membuat tabel eksternal.

Catatan

PathFormat hanya dapat menggambarkan jalur URI "folder" penyimpanan. Untuk memfilter berdasarkan nama file, gunakan properti tabel eksternal NamePrefix dan/atau FileExtension.

Format

Format data, salah satu format penyerapan.

Catatan

Menggunakan tabel eksternal untuk skenario ekspor terbatas pada format berikut: CSV, TSV, JSON, dan Parquet.

Tip

Sebaiknya gunakan format Parquet untuk tabel eksternal guna meningkatkan performa kueri dan ekspor kecuali: Anda menentukan kolom virtual, menggunakan pemetaan jalur JSON, atau data tabel eksternal ada di Azure Data Lake Storage (ADLS) Gen1.

StorageConnectionString

Satu atau beberapa jalur ke kontainer blob Azure Blob Storage, sistem file Azure Data Lake Gen 2 atau kontainer Azure Data Lake Gen 1, menyertakan info masuk. Jenis penyimpanan tabel eksternal ditentukan oleh string koneksi yang disediakan. Lihat string koneksi penyimpanan untuk detailnya.

Tip

Sediakan lebih dari satu akun penyimpanan untuk menghindari pembatasan penyimpanan saat mengekspor data dalam jumlah besar ke tabel eksternal. Ekspor akan mendistribusikan operasi tulis di antara semua akun yang disediakan.

Optional Properties

Properti	Jenis	Deskripsi
`folder`	`string`	Folder tabel
`docString`	`string`	String yang mendokumentasikan tabel
`compressed`	`bool`	Jika diatur, ini menunjukkan apakah file dikompresi sebagai file `.gz` (hanya digunakan dalam skenario ekspor)
`includeHeaders`	`string`	Untuk format teks yang dipisah (CSV, TSV, ...), tunjukkan apakah file berisi header. Nilai yang mungkin adalah: `All` (semua file berisi header), `FirstFile` (file pertama dalam folder berisi header), `None` (tidak ada file yang berisi header).
`namePrefix`	`string`	Jika diatur, tunjukkan awalan file. Pada operasi tulis, semua file akan ditulis dengan awalan ini. Pada operasi baca, hanya file dengan awalan ini yang dibaca.
`fileExtension`	`string`	Jika diatur, tunjukkan ekstensi file dari file. Saat operasi tulis, nama file akan diakhiri dengan akhiran ini. Saat operasi baca, hanya file dengan ekstensi file ini yang akan dibaca.
`encoding`	`string`	Tunjukkan cara teks dienkode: `UTF8NoBOM` (default) atau `UTF8BOM`.
`sampleUris`	`bool`	Jika diatur, hasil perintah memberikan beberapa contoh simulasi URI file data eksternal seperti yang diharapkan oleh definisi tabel eksternal. Opsi ini membantu memvalidasi apakah parameter Partition dan PathFormat didefinisikan dengan benar.
`filesPreview`	`bool`	Jika diatur, salah satu tabel hasil perintah berisi pratinjau perintah .show external table artifacts. Seperti `sampleUri`, opsi ini membantu memvalidasi parameter Partition dan PathFormat dari definisi tabel eksternal.
`validateNotEmpty`	`bool`	Jika diatur, string koneksi divalidasi untuk memiliki konten di dalamnya. Perintah ini akan gagal jika lokasi URI yang ditentukan tidak ada, atau jika tidak ada izin yang cukup untuk mengaksesnya.
`dryRun`	`bool`	Jika diatur, definisi tabel eksternal tidak dipertahankan. Opsi ini berguna untuk memvalidasi definisi tabel eksternal, terutama dalam hubungannya dengan parameter `filesPreview` atau `sampleUris`.

Tip

Untuk mempelajari selengkapnya tentang properti namePrefix dan fileExtension peran yang digunakan dalam pemfilteran file data selama kueri, lihat bagian logika pemfilteran file.

Examples

Tabel eksternal yang tidak dipartisi. File data diharapkan untuk ditempatkan langsung di kontainer yang ditentukan:

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

Tabel eksternal yang dipartisi berdasarkan tanggal. File data diharapkan untuk ditempatkan di direktori format tanggalwaktu default yyyy/MM/dd:

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

Tabel eksternal yang dipartisi berdasarkan bulan, dengan format direktori 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' 
)

Tabel eksternal yang dipartisi pertama berdasarkan nama pelanggan, lalu berdasarkan tanggal. Struktur direktori yang diharapkan adalah, misalnya, 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' 
)

Tabel eksternal yang dipartisi pertama dengan hash nama pelanggan (modulo sepuluh), lalu berdasarkan tanggal. Struktur direktori yang diharapkan adalah, misalnya, customer_id=5/dt=20190201. Nama file data diakhiri dengan ekstensi .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")

Untuk memfilter berdasarkan kolom partisi dalam kueri, tentukan nama kolom asli dalam predikat kueri:

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

Output sampel

TableName	TableType	Folder	DocString	Properti	ConnectionStrings	Partisi	PathFormat
ExternalTable	Blob	ExternalTables	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)

Kolom virtual

Saat data diekspor dari Spark, kolom partisi (yang disediakan untuk metode partitionBy penulis dataframe) tidak ditulis ke file data. Proses ini menghindari duplikasi data karena data sudah ada dalam nama folder (misalnya column1=<value>/column2=<value>/), dan Spark dapat mengenalinya saat dibaca.

Tabel eksternal mendukung sintaks berikut untuk menentukan kolom virtual:

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

Untuk memfilter berdasarkan kolom virtual dalam kueri, tentukan nama partisi dalam predikat kueri:

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

Logika pemfilteran file

Saat mengkueri tabel eksternal, mesin kueri meningkatkan performa dengan memfilter file penyimpanan eksternal yang tidak relevan. Proses pengulangan file dan memutuskan apakah file harus diproses adalah sebagai berikut:

Buat pola URI yang mewakili tempat di mana file ditemukan. Awalnya, pola URI sama dengan string koneksi yang disediakan sebagai bagian dari definisi tabel eksternal. Jika ada partisi yang ditentukan, partisi akan dirender menggunakan PathFormat, lalu ditambahkan ke pola URI.
Untuk semua file yang ditemukan di pola URI yang dibuat, periksa bahwa:
- Nilai partisi cocok dengan predikat yang digunakan dalam kueri.
- Nama blob dimulai dengan NamePrefix, jika properti tersebut ditentukan.
- Nama blob diakhiri dengan FileExtension, jika properti tersebut ditentukan.

Setelah semua kondisi terpenuhi, file diambil dan diproses oleh mesin kueri.

Catatan

Pola URI awal dibuat menggunakan nilai predikat kueri. Ini paling cocok untuk satu set nilai string terbatas serta untuk rentang waktu tertutup.

.show external table artifacts

Menampilkan daftar semua file yang akan diproses saat mengkueri tabel eksternal tertentu.

Catatan

Operasi ini memerlukan izin pengguna database.

Sintaksis:

.showexternaltableTableNameartifacts [limitMaxResults]

Dengan MaxResults adalah parameter opsional, yang dapat diatur untuk membatasi jumlah hasil.

Output

Parameter output	Jenis	Deskripsi
Uri	string	URI file data penyimpanan eksternal
Ukuran	long	Panjang file dalam byte
Partisi	dinamis	Objek dinamis yang menjelaskan partisi file untuk tabel eksternal yang dipartisi

Tip

Mengulangi semua file yang direferensikan oleh tabel eksternal bisa memerlukan banyak sumber daya, bergantung pada jumlah file. Pastikan untuk menggunakan parameter limit jika Anda hanya ingin melihat beberapa contoh URI.

Contoh:

.show external table T artifacts

Output:

Uri	Ukuran	Partisi
`https://storageaccount.blob.core.windows.net/container1/folder/file.csv`	10743	`{}`

Untuk tabel berpartisi, kolom Partition akan berisi nilai partisi yang diekstrak:

Output:

Uri	Ukuran	Partisi
`https://storageaccount.blob.core.windows.net/container1/customer=john.doe/dt=20200101/file.csv`	10743	`{"Customer": "john.doe", "Date": "2020-01-01T00:00:00.0000000Z"}`

.create external table mapping

.createexternaltableExternalTableNamemappingMappingNameMappingInJsonFormat

Membuat pemetaan baru. Untuk informasi selengkapnya, lihat Pemetaan Data.

Contoh

.create external table MyExternalTable mapping "Mapping1" '[{"Column": "rownumber", "Properties": {"Path": "$.rownumber"}}, {"Column": "rowguid", "Properties": {"Path": "$.rowguid"}}]'

Contoh output

Nama	Jenis	Pemetaan
mapping1	JSON	[{"ColumnName":"rownumber","Properties":{"Path":"$.rownumber"}},{"ColumnName":"rowguid","Properties":{"Path":"$.rowguid"}}]

.alter external table mapping

.alterexternaltableExternalTableNamemappingMappingNameMappingInJsonFormat

Mengubah pemetaan yang sudah ada.

Contoh

.alter external table MyExternalTable mapping "Mapping1" '[{"Column": "rownumber", "Properties": {"Path": "$.rownumber"}}, {"Column": "rowguid", "Properties": {"Path": "$.rowguid"}}]'

Contoh output

Nama	Jenis	Pemetaan
mapping1	JSON	[{"ColumnName":"rownumber","Properties":{"Path":"$.rownumber"}},{"ColumnName":"rowguid","Properties":{"Path":"$.rowguid"}}]

.show external table mappings

.showexternaltableExternalTableNamemappingMappingName

.showexternaltableExternalTableNamemappings

Menampilkan pemetaan (semua atau yang ditentukan berdasarkan nama).

Contoh

.show external table MyExternalTable mapping "Mapping1" 

.show external table MyExternalTable mappings

Contoh output

Nama	Jenis	Pemetaan
mapping1	JSON	[{"ColumnName":"rownumber","Properties":{"Path":"$.rownumber"}},{"ColumnName":"rowguid","Properties":{"Path":"$.rowguid"}}]

.drop external table mapping

.dropexternaltableExternalTableNamemappingMappingName

Menghilangkan pemetaan dari database.

Contoh

.drop external table MyExternalTable mapping "Mapping1"

Membuat dan mengubah tabel eksternal Azure Storage

.create atau .alter external table

.show external table artifacts

.create external table mapping

.alter external table mapping

.show external table mappings

.drop external table mapping

Langkah berikutnya