Membuat dan mengubah tabel eksternal Azure Storage
Perintah dalam artikel ini dapat digunakan untuk membuat atau mengubah tabel eksternal Azure Storage dalam database tempat perintah dijalankan. Tabel eksternal Azure Storage mereferensikan data yang terletak di Azure Blob Storage, Azure Data Lake Store Gen1, atau Azure Data Lake Store Gen2.
Catatan
Jika tabel ada, perintah .create akan gagal dengan kesalahan. Gunakan .create-or-alter atau .alter mengubah tabel yang ada.
Izin
Untuk .create memerlukan setidaknya izin Pengguna Database, dan untuk .alter memerlukan setidaknya izin Admin Tabel.
Ke .create-or-alter tabel eksternal menggunakan autentikasi identitas terkelola memerlukan izin AllDatabasesAdmin .
Sintaks
(.create.alter | .create-or-alter | ) externaltableTableName(Schema)=storagekind [partitionby(Partisi) [(pathformat=PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Properti [, ...])]
Catatan
kind adalah storage untuk semua jenis penyimpanan data eksternal Azure Storage. blob dan adl merupakan istilah yang tidak digunakan lagi.
Pelajari selengkapnya tentang konvensi sintaksis.
Parameter
| Nama | Jenis | Diperlukan | Deskripsi |
|---|---|---|---|
| TableName | string |
✔️ | Nama tabel eksternal yang mematuhi aturan nama entitas . Tabel eksternal tidak dapat memiliki nama yang sama dengan tabel biasa dalam database yang sama. |
| Skema | string |
✔️ | Skema data eksternal adalah daftar satu atau beberapa nama kolom dan jenis data yang dipisahkan koma, di mana setiap item mengikuti format: ColumnName:ColumnType. Jika skema tidak diketahui, gunakan infer_storage_schema untuk menyimpulkan skema berdasarkan konten file eksternal. |
| Partisi | string |
Daftar kolom yang dipisahkan koma tempat tabel eksternal dipartisi. Kolom partisi dapat ada di file data itu sendiri, atau sebagai bagian dari jalur file. Lihat pemformatan partisi untuk mempelajari tampilan nilai ini. | |
| PathFormat | string |
Format jalur URI folder data eksternal untuk digunakan dengan partisi. Lihat format jalur. | |
| DataFormat | string |
✔️ | Format data, yang dapat berupa salah satu format penyerapan. Sebaiknya gunakan Parquet format untuk tabel eksternal untuk meningkatkan performa kueri dan ekspor, kecuali Anda menggunakan JSON pemetaan jalur. Saat menggunakan tabel eksternal untuk skenario ekspor, Anda terbatas pada format berikut: CSV, , TSVJSON dan Parquet. |
| StorageConnectionString | string |
✔️ | Satu atau beberapa jalur yang dipisahkan koma ke kontainer blob Azure Blob Storage, sistem file Azure Data Lake Gen 2, atau kontainer Azure Data Lake Gen 1, termasuk kredensial. Jenis penyimpanan tabel eksternal ditentukan oleh string koneksi yang disediakan. Lihat string koneksi penyimpanan. |
| Properti | string |
Pasangan properti nilai kunci dalam format PropertyName=PropertyValue. Lihat properti opsional. |
Catatan
File CSV dengan skema yang tidak identik dapat mengakibatkan data muncul bergeser atau hilang. Sebaiknya pisahkan file CSV dengan skema yang berbeda untuk memisahkan kontainer penyimpanan dan menentukan tabel eksternal untuk setiap kontainer penyimpanan dengan skema yang tepat.
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.
Autentikasi dan Otorisasi
Metode autentikasi untuk mengakses tabel eksternal didasarkan pada string koneksi yang disediakan selama pembuatannya, dan izin yang diperlukan untuk mengakses tabel bervariasi tergantung pada metode autentikasi.
Tabel berikut ini mencantumkan metode autentikasi yang didukung untuk tabel eksternal Azure Storage dan izin yang diperlukan untuk membaca atau menulis ke tabel.
| Metode autentikasi | Azure Blob Storage/Data Lake Storage Gen2 | Data Lake Storage Gen1 |
|---|---|---|
| Penyamaran | Izin baca: Pembaca Data Blob Penyimpanan Izin tulis: Kontributor Data Blob Penyimpanan |
Izin baca: Pembaca Izin tulis: Kontributor |
| Identitas terkelola | Izin baca: Pembaca Data Blob Penyimpanan Izin tulis: Kontributor Data Blob Penyimpanan |
Izin baca: Pembaca Izin tulis: Kontributor |
| Token Akses Bersama (SAS) | Izin baca: Daftar + Baca Izin tulis: Menulis |
Metode autentikasi ini tidak didukung di Gen1. |
| token akses Microsoft Entra | Tidak diperlukan izin tambahan. | Tidak diperlukan izin tambahan. |
| Kunci akses akun penyimpanan | Tidak diperlukan izin tambahan. | Metode autentikasi ini tidak didukung di Gen1. |
Pemformatan partisi
Daftar partisi adalah kombinasi kolom partisi apa pun, yang ditentukan menggunakan salah satu formulir yang diperlihatkan dalam tabel berikut.
| Jenis Partisi | Sintaks | Catatan |
|---|---|---|
| Kolom virtual | PartitionName: (datetime | string) |
Baca selengkapnya tentang kolom virtual. |
| Nilai kolom string | PartitionName:string=ColumnName |
|
| Hash nilai kolom string | PartitionName:long=hash(ColumnName,Number) |
Hash adalah Nomor modulo. |
| Kolom tanggalwaktu terpotong (nilai) | PartitionName:datetime= (startofyear | startofmonth | startofweek | startofday) (ColumnName) |
Lihat dokumentasi tentang fungsi startofyear, startofmonth, startofweek, atau startofday . |
| Nilai Kolom Datetime Terpotong (bin) | PartitionName:datetime=bin(ColumnName,TimeSpan) |
Baca selengkapnya tentang fungsi bin . |
Format jalur
Parameter PathFormat memungkinkan Anda menentukan format untuk jalur URI folder data eksternal selain partisi. Ini terdiri dari urutan elemen partisi dan pemisah teks. Elemen partisi mengacu pada partisi yang dideklarasikan dalam klausa partisi by , dan pemisah teks adalah teks apa pun yang diapit dalam tanda kutip. Elemen partisi berturut-turut harus dipisahkan menggunakan pemisah teks.
[ StringSeparator ] Partisi [ StringSeparator ] [Partisi [ StringSeparator ] ...]
Untuk membuat awalan jalur file asli, elemen partisi dirender sebagai string dan dipisahkan dengan pemisah teks yang sesuai. Anda dapat menggunakan datetime_pattern makro (datetime_pattern(DateTimeFormat,PartitionName)) untuk menentukan format yang digunakan untuk merender nilai partisi tanggalwaktu. Makro mematuhi spesifikasi format .NET, dan memungkinkan penentu format diapit kurung kurawal. Misalnya, dua format berikut setara:
- 'year='yyyy'/month='MM
- 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 |
Tip
Untuk memeriksa kebenaran definisi Partisi dan PathFormat , gunakan properti sampleUris atau filesPreview saat membuat tabel eksternal.
Kolom virtual
Saat data diekspor dari Spark, kolom partisi (yang disediakan untuk metode penulis partitionBy 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 pembacaan data ini dalam bentuk virtual colums. Kolom virtual dapat berjenis string atau datetime, dan ditentukan menggunakan sintaks berikut:
.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")
Properti opsional
| 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 menyediakan beberapa contoh URI file data eksternal yang disimulasikan 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. |
Catatan
Tabel eksternal tidak diakses selama pembuatan, hanya selama kueri dan ekspor. validateNotEmpty Gunakan properti opsional selama pembuatan untuk memastikan definisi tabel valid dan penyimpanan dapat diakses.
Tip
Untuk mempelajari selengkapnya tentang properti namePrefix dan fileExtension peran yang digunakan dalam pemfilteran file data selama kueri, lihat bagian logika pemfilteran file.
Logika pemfilteran file
Saat mengkueri tabel eksternal, performa ditingkatkan 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 dirender menggunakan PathFormat, lalu ditambahkan ke pola URI.
Untuk semua file yang ditemukan di bawah pola URI yang dibuat, periksa apakah:
- 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.
Catatan
Pola URI awal dibuat menggunakan nilai predikat kueri. Ini paling cocok untuk satu set nilai string terbatas serta untuk rentang waktu tertutup.
Contoh
Tabel eksternal yang tidak dipartisi
Dalam tabel eksternal yang tidak dipartisi berikut, file diharapkan ditempatkan langsung di bawah kontainer yang ditentukan:
.create external table ExternalTable (x:long, s:string)
kind=storage
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Dipartisi menurut tanggal
Dalam tabel eksternal berikut yang dipartisi berdasarkan tanggal, file diharapkan ditempatkan di bawah direktori format yyyy/MM/ddtanggalwaktu default :
.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'
)
Dipartisi menurut bulan
Dalam tabel eksternal berikut yang dipartisi berdasarkan bulan, format direktorinya adalah 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'
)
Dipartisi berdasarkan nama dan tanggal
Dalam tabel eksternal berikut, data dipartisi terlebih dahulu dengan nama pelanggan lalu berdasarkan tanggal, yang berarti bahwa 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'
)
Dipartisi oleh hash dan tanggal
Tabel eksternal berikut dipartisi terlebih dahulu dengan hash nama pelanggan (modulo sepuluh), lalu berdasarkan tanggal. Struktur direktori yang diharapkan adalah, misalnya, customer_id=5/dt=20190201, dan nama file data berakhir dengan .txt ekstensi:
.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")
Memfilter menurut kolom partisi dalam kueri
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) |
Konten terkait
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk