BUAT TABEL EKSTERNAL SEBAGAI PILIH (CETAS) (Transact-SQL)
Berlaku untuk: SQL Server 2022 (16.x) dan yang lebih baru
Sistem Platform Analitik Azure Synapse Analytics
(PDW)
Membuat tabel eksternal lalu mengekspor, secara paralel, hasil pernyataan Transact-SQL SELECT.
- Azure Synapse Analytics dan Analytics Platform System mendukung penyimpanan Hadoop atau Azure Blob.
- SQL Server 2022 (16.x) dan versi yang lebih baru mendukung CREATE EXTERNAL TABLE AS SELECT (CETAS) untuk membuat tabel eksternal lalu mengekspor, secara paralel, hasil pernyataan Transact-SQL SELECT ke Azure Data Lake Storage (ADLS) Gen2, Akun Azure Storage V2, dan penyimpanan objek yang kompatibel dengan S3.
Catatan
Kemampuan dan keamanan CETAS untuk Azure SQL Managed Instance berbeda dari SQL Server atau Azure Synapse Analytics. Untuk informasi selengkapnya, lihat versi Azure SQL Managed Instance dari CREATE EXTERNAL TABLE AS SELECT.
Catatan
Kemampuan dan keamanan CETAS untuk kumpulan tanpa server di Azure Synapse Analytics berbeda dari SQL Server. Untuk informasi selengkapnya, lihat CETAS dengan Synapse SQL.
Sintaks
CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
[ (column_name [ , ...n ] ) ]
WITH (
LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
DATA_SOURCE = external_data_source_name ,
FILE_FORMAT = external_file_format_name
[ , <reject_options> [ , ...n ] ]
)
AS <select_statement>
[;]
<reject_options> ::=
{
| REJECT_TYPE = value | percentage
| REJECT_VALUE = reject_value
| REJECT_SAMPLE_VALUE = reject_sample_value
}
<select_statement> ::=
[ WITH <common_table_expression> [ , ...n ] ]
SELECT <select_criteria>
Argumen
[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name
Nama tabel satu hingga tiga bagian yang akan dibuat dalam database. Untuk tabel eksternal, database relasional hanya menyimpan metadata tabel.
[ ( column_name [ ,... n ] ) ]
Nama kolom tabel.
LOKASI
Berlaku untuk: Azure Synapse Analytics dan Analytics Platform System
'hdfs_folder'**
Menentukan tempat untuk menulis hasil pernyataan SELECT pada sumber data eksternal. Lokasi adalah nama folder dan dapat secara opsional menyertakan jalur yang relatif terhadap folder akar kluster Hadoop atau penyimpanan Blob. PolyBase membuat jalur dan folder jika belum ada.
File eksternal ditulis ke hdfs_folder
dan bernama QueryID_date_time_ID.format
, di mana ID
adalah pengidentifikasi inkremental dan format
merupakan format data yang diekspor. Contohnya QID776_20160130_182739_0.orc
.
LOCATION harus menunjuk ke folder dan memiliki trailing /
, misalnya: aggregated_data/
.
Berlaku untuk: SQL Server 2022 (16.x) dan yang lebih baru
prefix://path[:port]
menyediakan protokol konektivitas (awalan), jalur, dan secara opsional port, ke sumber data eksternal, di mana hasil pernyataan SELECT akan ditulis.
Jika tujuannya adalah penyimpanan objek yang kompatibel dengan S3, wadah harus terlebih dahulu ada, tetapi PolyBase dapat membuat subfolder jika perlu. SQL Server 2022 (16.x) mendukung Azure Data Lake Storage Gen2, Azure Storage Account V2, dan penyimpanan objek yang kompatibel dengan S3. File ORC saat ini tidak didukung.
DATA_SOURCE = external_data_source_name
Menentukan nama objek sumber data eksternal yang berisi lokasi tempat data eksternal disimpan atau akan disimpan. Lokasinya adalah kluster Hadoop atau penyimpanan Azure Blob. Untuk membuat sumber data eksternal, gunakan CREATE EXTERNAL DATA SOURCE (Transact-SQL).
FILE_FORMAT = external_file_format_name
Menentukan nama objek format file eksternal yang berisi format untuk file data eksternal. Untuk membuat format file eksternal, gunakan CREATE EXTERNAL FILE FORMAT (Transact-SQL).
Opsi REJECT
Opsi REJECT tidak berlaku pada saat pernyataan CREATE EXTERNAL TABLE AS SELECT ini dijalankan. Sebagai gantinya, database ditentukan di sini sehingga database dapat menggunakannya di lain waktu saat mengimpor data dari tabel eksternal. Nantinya, ketika pernyataan CREATE TABLE AS SELECT memilih data dari tabel eksternal, database akan menggunakan opsi penolakan untuk menentukan jumlah atau persentase baris yang dapat gagal diimpor sebelum menghentikan impor.
REJECT_VALUE = reject_value
Menentukan nilai atau persentase baris yang bisa gagal diimpor sebelum database menghentikan impor.
REJECT_TYPE = nilai | Persentase
Mengklarifikasi apakah opsi REJECT_VALUE adalah nilai harfiah atau persentase.
value
Digunakan jika REJECT_VALUE adalah nilai harfiah, bukan persentase. Database berhenti mengimpor baris dari file data eksternal saat jumlah baris yang gagal melebihi reject_value.
Misalnya, jika
REJECT_VALUE = 5
danREJECT_TYPE = value
, database berhenti mengimpor baris setelah lima baris gagal diimpor.Persentase
Digunakan jika REJECT_VALUE adalah persentase, bukan nilai harfiah. Database berhenti mengimpor baris dari file data eksternal saat persentase baris yang gagal melebihi reject_value. Persentase baris yang gagal dihitung pada interval. Hanya berlaku di kumpulan SQL khusus saat
TYPE=HADOOP
.
REJECT_SAMPLE_VALUE = reject_sample_value
Diperlukan saat
REJECT_TYPE = percentage
. Menentukan jumlah baris yang akan diimpor sebelum database menghitung ulang persentase baris yang gagal.Misalnya, jika REJECT_SAMPLE_VALUE = 1000, database akan menghitung persentase baris yang gagal setelah mencoba mengimpor 1000 baris dari file data eksternal. Jika persentase baris yang gagal kurang dari reject_value, database mencoba memuat 1000 baris lainnya. Database terus menghitung ulang persentase baris yang gagal setelah mencoba mengimpor setiap 1000 baris tambahan.
Catatan
Karena database menghitung persentase baris yang gagal pada interval, persentase aktual baris yang gagal dapat melebihi reject_value.
Contoh:
Contoh ini menunjukkan bagaimana tiga opsi REJECT berinteraksi satu sama lain. Misalnya, jika
REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100
, skenario berikut dapat terjadi:- Database mencoba memuat 100 baris pertama, di mana 25 gagal dan 75 berhasil.
- Persentase baris yang gagal dihitung sebagai 25%, yang kurang dari nilai penolakan 30%. Jadi, tidak perlu menghentikan beban.
- Database mencoba memuat 100 baris berikutnya. Kali ini, 25 berhasil dan 75 gagal.
- Persentase baris yang gagal dihitung ulang sebagai 50%. Persentase baris yang gagal telah melebihi nilai penolakan 30%.
- Beban gagal dengan 50% baris gagal setelah mencoba memuat 200 baris, yang lebih besar dari batas 30% yang ditentukan.
WITH common_table_expression
Menentukan kumpulan hasil bernama sementara, yang dikenal sebagai ekspresi tabel umum (CTE). Untuk informasi selengkapnya, lihat WITH common_table_expression (Transact-SQL)
PILIH <select_criteria>
Mengisi tabel baru dengan hasil dari pernyataan SELECT. select_criteria adalah isi pernyataan SELECT yang menentukan data yang akan disalin ke tabel baru. Untuk informasi tentang pernyataan SELECT, lihat SELECT (Transact-SQL).
Catatan
Klausa ORDER BY dalam SELECT tidak berpengaruh pada CETAS.
Opsi kolom
column_name [ ,... n ]
Nama kolom tidak memperbolehkan opsi kolom yang disebutkan dalam CREATE TABLE. Sebagai gantinya, Anda dapat menyediakan daftar opsional dari satu atau beberapa nama kolom untuk tabel baru. Kolom dalam tabel baru menggunakan nama yang Anda tentukan. Saat Anda menentukan nama kolom, jumlah kolom dalam daftar kolom harus cocok dengan jumlah kolom dalam hasil pemilihan. Jika Anda tidak menentukan nama kolom apa pun, tabel target baru menggunakan nama kolom dalam hasil pernyataan pilih.
Anda tidak dapat menentukan opsi kolom lain seperti jenis data, kolase, atau nullability. Masing-masing atribut ini berasal dari hasil pernyataan SELECT. Namun, Anda dapat menggunakan pernyataan SELECT untuk mengubah atribut. Misalnya, lihat Menggunakan CETAS untuk mengubah atribut kolom.
Izin
Untuk menjalankan perintah ini, pengguna database memerlukan semua izin atau keanggotaan ini:
- UBAH izin SKEMA pada skema lokal yang akan berisi tabel atau keanggotaan baru dalam peran database tetap db_ddladmin .
- Izin CREATE TABLE atau keanggotaan dalam peran database tetap db_ddladmin .
- Izin SELECT pada objek apa pun yang dirujuk dalam select_criteria.
Login memerlukan semua izin ini:
- MENGELOLA OPERASI MASSAL
- ALTER ANY EXTERNAL DATA SOURCE
- ALTER ANY EXTERNAL FILE FORMAT
- Secara umum, Anda harus memiliki izin untuk Mencantumkan konten folder dan Menulis ke folder LOCATION untuk CETAS.
- Di Azure Synapse Analytics dan Analytics Platform System, Tulis izin untuk membaca dan menulis ke folder eksternal pada kluster Hadoop atau di penyimpanan Azure Blob.
- Di SQL Server 2022 (16.x), juga diperlukan untuk mengatur izin yang tepat pada lokasi eksternal. Tulis izin untuk mengeluarkan data ke lokasi dan Izin baca untuk mengaksesnya.
- Untuk Azure Blob Storage dan Azure Data Lake Gen2
SHARED ACCESS SIGNATURE
, token harus diberikan hak istimewa berikut pada kontainer: Baca, Tulis, Daftar, Buat. - Untuk Azure Blog Storage,
Allowed Services
kotak centang :Blob
harus dipilih untuk menghasilkan token SAS. - Untuk Azure Data Lake Gen2,
Allowed Services
kotak centang :Container
danObject
harus dipilih untuk menghasilkan token SAS.
Penting
Izin UBAH SUMBER DATA EKSTERNAL memberi prinsipal apa pun kemampuan untuk membuat dan memodifikasi objek sumber data eksternal apa pun, sehingga juga memberikan kemampuan untuk mengakses semua kredensial cakupan database pada database. Izin ini harus dianggap sangat istimewa dan harus diberikan hanya kepada prinsipal tepercaya dalam sistem.
Penanganan kesalahan
Saat CREATE EXTERNAL TABLE AS SELECT mengekspor data ke file yang dibatasi teks, tidak ada file penolakan untuk baris yang gagal diekspor.
Saat Anda membuat tabel eksternal, database mencoba menyambungkan ke lokasi eksternal. Jika koneksi gagal, perintah gagal, dan tabel eksternal tidak dibuat. Diperlukan waktu satu menit atau lebih agar perintah gagal karena database mencoba kembali koneksi setidaknya tiga kali.
Jika CREATE EXTERNAL TABLE AS SELECT dibatalkan atau gagal, database membuat upaya satu kali untuk menghapus file dan folder baru yang sudah dibuat di sumber data eksternal.
Di Azure Synapse Analytics dan Analytics Platform System, database melaporkan kesalahan Java yang terjadi pada sumber data eksternal selama ekspor data.
Keterangan
Setelah pernyataan CREATE EXTERNAL TABLE AS SELECT selesai, Anda dapat menjalankan kueri Transact-SQL pada tabel eksternal. Operasi ini mengimpor data ke database selama durasi kueri kecuali Anda mengimpor dengan menggunakan pernyataan CREATE TABLE AS SELECT.
Nama dan definisi tabel eksternal disimpan dalam metadata database. Data disimpan di sumber data eksternal.
Pernyataan CREATE EXTERNAL TABLE AS SELECT selalu membuat tabel yang tidak dipartisi, meskipun tabel sumber dipartisi.
Untuk SQL Server 2022 (16.x), opsi allow polybase export
harus diaktifkan dengan menggunakan sp_configure
. Untuk informasi selengkapnya, lihat allow polybase export
Mengatur opsi konfigurasi.
Untuk rencana kueri di Azure Synapse Analytics dan Analytics Platform System, dibuat dengan EXPLAIN, database menggunakan operasi rencana kueri ini untuk tabel eksternal: Pemindahan acak eksternal, Pemindahan siaran eksternal, Pemindahan partisi eksternal.
Di Analytics Platform System, sebagai prasyarat untuk membuat tabel eksternal, administrator appliance perlu mengonfigurasi konektivitas Hadoop. Untuk informasi selengkapnya, lihat "Mengonfigurasi Konektivitas ke Data Eksternal (Sistem Platform Analitik)" dalam dokumentasi Sistem Platform Analitik, yang dapat Anda unduh dari Pusat Unduhan Microsoft.
Pembatasan dan batasan
Karena data tabel eksternal berada di luar database, operasi pencadangan dan pemulihan hanya beroperasi pada data yang disimpan dalam database. Akibatnya, hanya metadata yang dicadangkan dan dipulihkan.
Database tidak memverifikasi koneksi ke sumber data eksternal saat memulihkan cadangan database yang berisi tabel eksternal. Jika sumber asli tidak dapat diakses, pemulihan metadata tabel eksternal akan tetap berhasil, tetapi operasi SELECT pada tabel eksternal gagal.
Database tidak menjamin konsistensi data antara database dan data eksternal. Anda, pelanggan, bertanggung jawab sepenuhnya untuk menjaga konsistensi antara data eksternal dan database.
Operasi bahasa manipulasi data (DML) tidak didukung pada tabel eksternal. Misalnya, Anda tidak dapat menggunakan pernyataan Transact-SQL update, insert, atau delete Transact-SQL untuk memodifikasi data eksternal.
CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW, dan DROP VIEW adalah satu-satunya operasi bahasa definisi data (DDL) yang diizinkan pada tabel eksternal.
Batasan dan pembatasan untuk Azure Synapse Analytics
Di kumpulan SQL khusus Azure Synapse Analytics, dan Sistem Platform Analitik, PolyBase dapat menggunakan maksimum 33.000 file per folder saat menjalankan 32 kueri PolyBase bersamaan. Jumlah maksimum ini mencakup file dan subfolder di setiap folder HDFS. Jika tingkat konkurensi kurang dari 32, pengguna dapat menjalankan kueri PolyBase terhadap folder dalam HDFS yang berisi lebih dari 33.000 file. Sebaiknya pengguna Hadoop dan PolyBase menjaga jalur file tetap pendek dan menggunakan tidak lebih dari 30.000 file per folder HDFS. Ketika terlalu banyak file direferensikan, pengecualian JVM di luar memori terjadi.
Di kumpulan SQL tanpa server, tabel eksternal tidak dapat dibuat di lokasi tempat Anda saat ini memiliki data. Untuk menggunakan kembali lokasi yang telah digunakan untuk menyimpan data, lokasi harus dihapus secara manual di ADLS. Untuk keterbatasan dan praktik terbaik lainnya, lihat Praktik terbaik pengoptimalan filter.
Di kumpulan SQL khusus Azure Synapse Analytics, dan Sistem Platform Analitik, saat CREATE EXTERNAL TABLE AS SELECT memilih dari RCFile, nilai kolom di RCFile tidak boleh berisi karakter pipa (|
).
SET ROWCOUNT (Transact-SQL) tidak berpengaruh pada CREATE EXTERNAL TABLE AS SELECT. Untuk mencapai perilaku serupa, gunakan TOP (Transact-SQL).
Tinjau Penamaan dan Mereferensikan Kontainer, Blob, dan Metadata untuk batasan nama file.
Kesalahan karakter
Karakter berikut yang ada dalam data dapat menyebabkan kesalahan termasuk rekaman yang ditolak dengan CREATE EXTERNAL TABLE AS SELECT ke file Parquet.
Di Azure Synapse Analytics dan Analytics Platform System, ini juga berlaku untuk file ORC.
|
"
(karakter tanda kutip)\r\n
\r
\n
Untuk menggunakan CREATE EXTERNAL TABLE AS SELECT yang berisi karakter ini, Anda harus terlebih dahulu menjalankan pernyataan CREATE EXTERNAL TABLE AS SELECT untuk mengekspor data ke file teks yang dibatasi di mana Anda kemudian dapat mengonversinya ke Parquet atau ORC dengan menggunakan alat eksternal.
Bekerja dengan parkquet
Saat bekerja dengan file parket, CREATE EXTERNAL TABLE AS SELECT
akan menghasilkan satu file parket per CPU yang tersedia, hingga tingkat paralelisme maksimum yang dikonfigurasi (MAXDOP). Setiap file dapat tumbuh hingga 190 GB, setelah itu SQL Server akan menghasilkan lebih banyak file Parquet sesuai kebutuhan.
Petunjuk OPTION (MAXDOP n)
kueri hanya akan memengaruhi bagian SELECT dari CREATE EXTERNAL TABLE AS SELECT
, itu tidak berpengaruh pada jumlah file parket. Hanya MAXDOP tingkat database dan MAXDOP tingkat instans yang dipertimbangkan.
Penguncian
Mengambil kunci bersama pada objek SCHEMARESOLUTION.
Tipe data yang didukung
CETAS dapat digunakan untuk menyimpan kumpulan hasil dengan jenis data SQL berikut:
- biner
- varbinary
- char
- varchar
- nchar
- nvarchar
- smalldate
- date
- datetime
- tanggalwaktu2
- tanggalwaktulewat
- waktu
- desimal
- numeric
- float
- real
- bigint
- tinyint
- smallint
- int
- bigint
- bit
- money
- smallmoney
Contoh
J. Membuat tabel Hadoop dengan menggunakan CREATE EXTERNAL TABLE AS SELECT
Berlaku untuk: Azure Synapse Analytics dan Analytics Platform System
Contoh berikut membuat tabel eksternal baru bernama hdfsCustomer
yang menggunakan definisi kolom dan data dari tabel dimCustomer
sumber .
Definisi tabel disimpan dalam database, dan hasil pernyataan SELECT diekspor ke /pdwdata/customer.tbl
file pada sumber data eksternal Hadoop customer_ds. File diformat sesuai dengan format file eksternal customer_ff.
Nama file dihasilkan oleh database dan berisi ID kueri untuk memudahkan penyelarasan file dengan kueri yang menghasilkannya.
Jalur hdfs://xxx.xxx.xxx.xxx:5000/files/
sebelum direktori Pelanggan harus sudah ada. Jika direktori Pelanggan tidak ada, database akan membuat direktori.
Catatan
Contoh ini menentukan untuk 5000. Jika port tidak ditentukan, database menggunakan 8020 sebagai port default.
Lokasi Hadoop yang dihasilkan dan nama file akan menjadi hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt.
.
-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
WITH (
LOCATION = '/pdwdata/customer.tbl',
DATA_SOURCE = customer_ds,
FILE_FORMAT = customer_ff
) AS
SELECT *
FROM dimCustomer;
GO
B. Menggunakan petunjuk kueri dengan CREATE EXTERNAL TABLE AS SELECT
Berlaku untuk: Azure Synapse Analytics dan Analytics Platform System
Kueri ini memperlihatkan sintaks dasar untuk menggunakan petunjuk gabungan kueri dengan pernyataan CREATE EXTERNAL TABLE AS SELECT. Setelah kueri dikirimkan, database menggunakan strategi gabungan hash untuk menghasilkan rencana kueri. Untuk informasi selengkapnya tentang petunjuk gabungan dan cara menggunakan klausa OPTION, lihat Klausa OPTION (Transact-SQL).
Catatan
Contoh ini menentukan untuk 5000. Jika port tidak ditentukan, database menggunakan 8020 sebagai port default.
-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
WITH (
LOCATION = '/files/Customer',
DATA_SOURCE = customer_ds,
FILE_FORMAT = customer_ff
) AS
SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO
C. Menggunakan CETAS untuk mengubah atribut kolom
Berlaku untuk: Azure Synapse Analytics dan Analytics Platform System
Contoh ini menggunakan CETAS untuk mengubah jenis data, nullability, dan collation untuk beberapa kolom dalam FactInternetSales
tabel.
-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
WITH (
LOCATION = '/files/Customer',
DATA_SOURCE = customer_ds,
FILE_FORMAT = customer_ff
) AS
SELECT T1.ProductKey AS ProductKeyNoChange,
T1.OrderDateKey AS OrderDate,
T1.ShipDateKey AS ShipDate,
T1.CustomerKey AS CustomerKeyNoChange,
T1.OrderQuantity AS Quantity,
T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO
D. Gunakan CREATE EXTERNAL TABLE AS SELECT yang mengekspor data sebagai parquet
Berlaku untuk: SQL Server 2022 (16.x)
Contoh berikut membuat tabel eksternal baru bernama ext_sales
yang menggunakan data dari tabel SalesOrderDetail
AdventureWorks2022
. Opsi konfigurasi ekspor izinkan polybase harus diaktifkan.
Hasil pernyataan SELECT akan disimpan pada penyimpanan objek yang kompatibel dengan S3 yang sebelumnya dikonfigurasi dan diberi nama s3_eds
, dan kredensial yang tepat yang dibuat sebagai s3_dsc
. Lokasi file parket akan menjadi <ip>:<port>/cetas/sales.parquet
tempat cetas
wadah penyimpanan yang dibuat sebelumnya.
Catatan
Format Delta saat ini hanya didukung sebagai baca-saja.
-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = '<accesskeyid>:<secretkeyid>'
GO
-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
WITH (
LOCATION = 's3://<ip>:<port>',
CREDENTIAL = s3_dsc
)
-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
WITH (FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE ext_sales
WITH (
LOCATION = '/cetas/sales.parquet',
DATA_SOURCE = s3_eds,
FILE_FORMAT = ParquetFileFormat
) AS
SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO
E. Gunakan CREATE EXTERNAL TABLE AS SELECT dari tabel delta ke parquet
Berlaku untuk: SQL Server 2022 (16.x)
Contoh berikut membuat tabel eksternal baru bernama Delta_to_Parquet
, yang menggunakan jenis Data Tabel Delta yang terletak di penyimpanan objek yang kompatibel dengan S3 bernama s3_delta
, dan menulis hasilnya di sumber data lain bernama s3_parquet
sebagai file parket. Untuk itu contoh menggunakan perintah OPENROWSET. Opsi konfigurasi ekspor izinkan polybase harus diaktifkan.
-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
WITH (FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE Delta_to_Parquet
WITH (
LOCATION = '/backup/sales.parquet',
DATA_SOURCE = s3_parquet,
FILE_FORMAT = ParquetFileFormat
) AS
SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO
F. Gunakan CREATE EXTERNAL TABLE AS SELECT dengan tampilan sebagai sumber
Berlaku untuk: Kumpulan SQL tanpa server Azure Synapse Analytics dan kumpulan SQL khusus.
Dalam contoh ini, kita dapat melihat contoh kode templat untuk menulis CETAS dengan tampilan yang ditentukan pengguna sebagai sumber, menggunakan identitas terkelola sebagai autentikasi, dan wasbs:
.
CREATE DATABASE [<mydatabase>];
GO
USE [<mydatabase>];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO
CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO
CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
LOCATION = 'wasbs://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
CREDENTIAL = [WorkspaceIdentity]
);
GO
CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
LOCATION = '<myoutputsubfolder>/',
DATA_SOURCE = [SynapseSQLwriteable],
FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO
G. Gunakan CREATE EXTERNAL TABLE AS SELECT dengan tampilan sebagai sumber
Berlaku untuk: Kumpulan SQL tanpa server Azure Synapse Analytics dan kumpulan SQL khusus.
Dalam contoh ini, kita dapat melihat contoh kode templat untuk menulis CETAS dengan tampilan yang ditentukan pengguna sebagai sumber, menggunakan identitas terkelola sebagai autentikasi, dan https:
.
CREATE DATABASE [<mydatabase>];
GO
USE [<mydatabase>];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO
CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO
CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
CREDENTIAL = [WorkspaceIdentity]
);
GO
CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
LOCATION = '<myoutputsubfolder>/',
DATA_SOURCE = [SynapseSQLwriteable],
FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO
Langkah berikutnya
Berlaku untuk: Azure SQL Managed Instance
Membuat tabel eksternal lalu mengekspor, secara paralel, hasil pernyataan Transact-SQL SELECT.
Anda bisa menggunakan CREATE EXTERNAL TABLE AS SELECT (CETAS) untuk menyelesaikan tugas berikut:
- Buat tabel eksternal di atas file Parquet atau CSV di penyimpanan Azure Blob atau Azure Data Lake Storage (ADLS) Gen2.
- Ekspor, secara paralel, hasil pernyataan T-SQL SELECT ke dalam tabel eksternal yang dibuat.
- Untuk kemampuan virtualisasi data azure SQL Managed Instance lainnya, lihat Virtualisasi data dengan Azure SQL Managed Instance.
Catatan
Konten ini hanya berlaku untuk Azure SQL Managed Instance. Untuk platform lain, pilih versi CREATE EXTERNAL TABLE AS SELECT yang sesuai dari pemilih dropdrown.
Sintaks
CREATE EXTERNAL TABLE [ [database_name . [ schema_name ] . ] | schema_name . ] table_name
WITH (
LOCATION = 'path_to_folder/',
DATA_SOURCE = external_data_source_name,
FILE_FORMAT = external_file_format_name
[, PARTITION ( column_name [ , ...n ] ) ]
)
AS <select_statement>
[;]
<select_statement> ::=
[ WITH <common_table_expression> [ ,...n ] ]
SELECT <select_criteria>
Argumen
[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name
Satu hingga tiga bagian nama tabel yang akan dibuat. Untuk tabel eksternal, hanya metadata tabel yang disimpan. Tidak ada data aktual yang dipindahkan atau disimpan.
LOCATION = 'path_to_folder'
Menentukan tempat untuk menulis hasil pernyataan SELECT pada sumber data eksternal. Folder akar adalah lokasi data yang ditentukan dalam sumber data eksternal. LOCATION harus menunjuk ke folder dan memiliki trailing /
. Contoh: aggregated_data/
.
Folder tujuan untuk CETAS harus kosong. Jika jalur dan folder belum ada, jalur dan folder akan dibuat secara otomatis.
DATA_SOURCE = external_data_source_name
Menentukan nama objek sumber data eksternal yang berisi lokasi penyimpanan data eksternal. Untuk membuat sumber data eksternal, gunakan CREATE EXTERNAL DATA SOURCE (Transact-SQL).
FILE_FORMAT = external_file_format_name
Menentukan nama objek format file eksternal yang berisi format untuk file data eksternal. Untuk membuat format file eksternal, gunakan CREATE EXTERNAL FILE FORMAT (Transact-SQL). Hanya format file eksternal dengan FORMAT_TYPE=PARQUET dan FORMAT_TYPE=DELIMITEDTEXT yang saat ini didukung. Pemadatan GZip untuk format DELIMITEDTEXT tidak didukung.
[, PARTITION ( nama kolom [ , ... n ] ) ]
Mempartisi data output ke dalam beberapa jalur file parquet. Pemartisian terjadi per kolom tertentu (column_name
), cocok dengan kartubebas (*) di LOKASI dengan kolom partisi masing-masing. Jumlah kolom di bagian PARTISI harus cocok dengan jumlah kartubebas di LOCATION. Setidaknya harus ada satu kolom yang tidak digunakan untuk pemartisian.
WITH <common_table_expression>
Menentukan kumpulan hasil bernama sementara, yang dikenal sebagai ekspresi tabel umum (CTE). Untuk informasi selengkapnya, lihat WITH common_table_expression (Transact-SQL).
PILIH <select_criteria>
Mengisi tabel baru dengan hasil dari pernyataan SELECT. select_criteria adalah isi pernyataan SELECT yang menentukan data yang akan disalin ke tabel baru. Untuk informasi tentang pernyataan SELECT, lihat SELECT (Transact-SQL).
Catatan
Klausul ORDER BY di SELECT tidak didukung untuk CETAS.
Izin
Izin dalam penyimpanan
Anda harus memiliki izin untuk mencantumkan konten folder dan menulis ke jalur LOCATION agar CETAS berfungsi.
Metode autentikasi yang didukung adalah identitas terkelola atau token Tanda Tangan Akses Bersama (SAS).
- Jika Anda menggunakan identitas terkelola untuk autentikasi, pastikan bahwa perwakilan layanan instans terkelola SQL Anda memiliki peran Kontributor Data Blob Penyimpanan pada kontainer tujuan.
- Jika Anda menggunakan token SAS, izin Baca, Tulis, dan Daftar diperlukan.
- Untuk Azure Blog Storage,
Allowed Services
kotak centang :Blob
harus dipilih untuk menghasilkan token SAS. - Untuk Azure Data Lake Gen2,
Allowed Services
kotak centang :Container
danObject
harus dipilih untuk menghasilkan token SAS.
Identitas terkelola yang ditetapkan pengguna tidak didukung. Autentikasi passthrough Microsoft Entra tidak didukung. ID Microsoft Entra adalah (sebelumnya Azure Active Directory).
Izin dalam instans terkelola SQL
Untuk menjalankan perintah ini, pengguna database memerlukan semua izin atau keanggotaan ini:
- UBAH izin SKEMA pada skema lokal yang akan berisi tabel atau keanggotaan baru dalam peran database tetap db_ddladmin .
- Izin CREATE TABLE atau keanggotaan dalam peran database tetap db_ddladmin .
- Izin SELECT pada objek apa pun yang dirujuk dalam select_criteria.
Login memerlukan semua izin ini:
- MENGELOLA OPERASI MASSAL
- ALTER ANY EXTERNAL DATA SOURCE
- ALTER ANY EXTERNAL FILE FORMAT
Penting
Izin UBAH SUMBER DATA EKSTERNAL memberi prinsipal apa pun kemampuan untuk membuat dan memodifikasi objek sumber data eksternal apa pun, sehingga juga memberikan kemampuan untuk mengakses semua kredensial cakupan database pada database. Izin ini harus dianggap sangat istimewa dan harus diberikan hanya kepada prinsipal tepercaya dalam sistem.
Tipe data yang didukung
CETAS menyimpan kumpulan hasil dengan jenis data SQL berikut:
- biner
- varbinary
- char
- varchar
- nchar
- nvarchar
- smalldatetime
- date
- datetime
- tanggalwaktu2
- tanggalwaktulewat
- waktu
- desimal
- numeric
- float
- real
- bigint
- tinyint
- smallint
- int
- bigint
- bit
- money
- smallmoney
Catatan
LOB yang lebih besar dari 1MB tidak dapat digunakan dengan CETAS.
Pembatasan dan batasan
- CREATE EXTERNAL TABLE AS SELECT (CETAS) untuk Azure SQL Managed Instance dinonaktifkan secara default. Untuk informasi selengkapnya, lihat bagian berikutnya, Dinonaktifkan secara default.
- Untuk informasi selengkapnya tentang batasan atau masalah yang diketahui dengan virtualisasi data di Azure SQL Managed Instance, lihat Batasan dan Masalah yang diketahui.
Karena data tabel eksternal berada di luar database, operasi pencadangan dan pemulihan hanya beroperasi pada data yang disimpan dalam database. Akibatnya, hanya metadata yang dicadangkan dan dipulihkan.
Database tidak memverifikasi koneksi ke sumber data eksternal saat memulihkan cadangan database yang berisi tabel eksternal. Jika sumber asli tidak dapat diakses, pemulihan metadata tabel eksternal masih berhasil, tetapi operasi SELECT pada tabel eksternal gagal.
Database tidak menjamin konsistensi data antara database dan data eksternal. Anda, pelanggan, bertanggung jawab sepenuhnya untuk menjaga konsistensi antara data eksternal dan database.
Operasi bahasa manipulasi data (DML) tidak didukung pada tabel eksternal. Misalnya, Anda tidak dapat menggunakan pembaruan Transact-SQL, menyisipkan, atau menghapus Transact-SQLstatements untuk memodifikasi data eksternal.
CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW, dan DROP VIEW adalah satu-satunya operasi bahasa definisi data (DDL) yang diizinkan pada tabel eksternal.
Tabel eksternal tidak dapat dibuat di lokasi tempat Anda saat ini memiliki data. Untuk menggunakan kembali lokasi yang telah digunakan untuk menyimpan data, lokasi harus dihapus secara manual di ADLS.
SET ROWCOUNT (Transact-SQL) tidak berpengaruh pada CREATE EXTERNAL TABLE AS SELECT. Untuk mencapai perilaku serupa, gunakan TOP (Transact-SQL).
Tinjau Penamaan dan Mereferensikan Kontainer, Blob, dan Metadata untuk batasan nama file.
Tipe penyimpanan
File dapat disimpan di Azure Data Lake Storage Gen2 atau Azure Blob Storage. Untuk mengkueri file, Anda perlu menyediakan lokasi dalam format tertentu dan menggunakan awalan jenis lokasi yang sesuai dengan jenis sumber eksternal dan titik akhir/protokol, seperti contoh berikut:
--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
--Data Lake endpoint
adls://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
Penting
Awalan jenis Lokasi yang disediakan digunakan untuk memilih protokol optimal untuk komunikasi dan untuk memanfaatkan kemampuan lanjutan yang ditawarkan oleh jenis penyimpanan tertentu.
Menggunakan awalan generik https://
dinonaktifkan. Selalu gunakan awalan khusus titik akhir.
Dinonaktifkan secara default
CREATE EXTERNAL TABLE AS SELECT (CETAS) memungkinkan Anda mengekspor data dari instans terkelola SQL Anda ke akun penyimpanan eksternal, sehingga ada potensi risiko penyelundupan data dengan kemampuan ini. Oleh karena itu, CETAS dinonaktifkan secara default untuk Azure SQL Managed Instance.
Aktifkan CETAS
CETAS untuk Azure SQL Managed Instance hanya dapat diaktifkan melalui metode yang memerlukan izin Azure yang ditinggikan, dan tidak dapat diaktifkan melalui T-SQL. Karena risiko penyelundupan data yang tidak sah, CETAS tidak dapat diaktifkan melalui sp_configure
prosedur tersimpan T-SQL, tetapi sebaliknya mengharuskan tindakan pengguna di luar instans terkelola SQL.
Izin untuk mengaktifkan CETAS
Untuk mengaktifkan melalui Azure PowerShell, pengguna yang menjalankan perintah harus memiliki peran RBAC Azure Kontributor atau SQL Security Manager untuk instans terkelola SQL Anda.
Peran kustom juga dapat dibuat untuk ini, yang memerlukan tindakan Baca dan Tulis untuk tindakan tersebut Microsoft.Sql/managedInstances/serverConfigurationOptions
.
Metode untuk mengaktifkan CETAS
Untuk memanggil perintah PowerShell di komputer, paket Az versi 9.7.0 atau yang lebih baru harus diinstal secara lokal. Atau, pertimbangkan untuk menggunakan Azure Cloud Shell untuk menjalankan Azure PowerShell di shell.azure.com.
Pertama, masuk ke Azure dan atur konteks yang tepat untuk langganan Anda:
Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID
Untuk mengelola opsi konfigurasi server "allowPolybaseExport", sesuaikan skrip PowerShell berikut ke langganan Anda dan nama instans terkelola SQL, lalu jalankan perintah. Untuk informasi selengkapnya, lihat Set-AzSqlServerConfigurationOption dan Get-AzSqlServerConfigurationOption.
# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1
Untuk menonaktifkan opsi konfigurasi server "allowPolybaseExport":
# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0
Untuk mendapatkan nilai opsi konfigurasi server saat ini "allowPolybaseExport":
# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"
Memverifikasi status CETAS
Kapan saja Anda dapat memeriksa status opsi konfigurasi CETAS saat ini.
Sambungkan ke instans terkelola SQL Anda. Jalankan T-SQL berikut dan amati value
kolom respons. Setelah perubahan konfigurasi server selesai, hasil kueri ini harus cocok dengan pengaturan yang Anda inginkan.
SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';
Pecahkan masalah
Untuk langkah-langkah selengkapnya untuk memecahkan masalah virtualisasi data di Azure SQL Managed Instance, lihat Memecahkan masalah. Penanganan kesalahan dan pesan kesalahan umum untuk CETAS di Azure SQL Managed Instance mengikutinya.
Penanganan kesalahan
Saat CREATE EXTERNAL TABLE AS SELECT mengekspor data ke file yang dibatasi teks, tidak ada file penolakan untuk baris yang gagal diekspor.
Saat Anda membuat tabel eksternal, database mencoba menyambungkan ke lokasi eksternal. Jika koneksi gagal, perintah gagal, dan tabel eksternal tidak akan dibuat. Diperlukan waktu satu menit atau lebih agar perintah gagal karena database mencoba kembali koneksi setidaknya tiga kali.
Pesan kesalahan umum
Pesan kesalahan umum ini memiliki penjelasan cepat untuk CETAS untuk Azure SQL Managed Instance.
Menentukan lokasi yang sudah ada di penyimpanan.
Solusi: Hapus lokasi penyimpanan (termasuk rekam jepret), atau ubah parameter lokasi dalam kueri.
Contoh pesan kesalahan:
Msg 15842: Cannot create external table. External table location already exists.
Nilai kolom diformat menggunakan objek JSON.
Solusi: Konversi kolom nilai ke satu kolom VARCHAR atau NVARCHAR, atau sekumpulan kolom dengan jenis yang ditentukan secara eksplisit.
Contoh pesan kesalahan:
Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.
Parameter lokasi tidak valid (misalnya, beberapa
//
).Solusi: Perbaiki parameter lokasi.
Contoh pesan kesalahan:
Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.
Kehilangan salah satu opsi yang diperlukan (DATA_SOURCE, FILE_FORMAT, LOCATION).
Solusi: Tambahkan parameter yang hilang ke kueri CETAS.
Contoh pesan kesalahan:
Msg 46505: Missing required external DDL option 'FILE_FORMAT'
Masalah akses (kredensial tidak valid, kredensial kedaluwarsa, atau kredensial dengan izin yang tidak mencukupi). Kemungkinan alternatif adalah jalur yang tidak valid, di mana instans terkelola SQL menerima Kesalahan 404 dari penyimpanan.
Solusi: Verifikasi validitas dan izin kredensial. Atau, validasi jalur valid dan penyimpanan ada. Gunakan jalur
adls://<container>@<storage_account>.blob.core.windows.net/<path>/
URL .Contoh pesan kesalahan:
Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'
Bagian lokasi dari DATA_SOURCE berisi kartubebas.
Solusi: Hapus kartubebas dari lokasi.
Contoh pesan kesalahan:
Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.
Jumlah kartubebas dalam parameter LOCATION dan jumlah kolom yang dipartisi tidak cocok.
Solusi: Pastikan jumlah kartubebas yang sama di LOCATION sebagai kolom partisi.
Contoh pesan kesalahan:
Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.
Nama kolom dalam klausa PARTITION tidak cocok dengan kolom apa pun dalam daftar.
Solusi: Pastikan bahwa kolom dalam PARTISI valid.
Contoh pesan kesalahan:
Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list
Kolom ditentukan lebih dari sekali dalam daftar PARTISI.
Solusi: Pastikan kolom dalam klausa PARTITION unik.
Contoh pesan kesalahan:
Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.
Kolom ditentukan lebih dari sekali dalam daftar PARTISI, atau tidak cocok dengan kolom dari daftar SELECT.
Solusi: Pastikan tidak ada duplikat dalam daftar partisi, dan kolom partisi ada di bagian SELECT.
Contoh pesan kesalahan:
Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter.
atauMsg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.
Menggunakan semua kolom dalam daftar PARTISI.
Solusi: Setidaknya salah satu kolom dari bagian SELECT tidak boleh berada di bagian PARTISI kueri.
Contoh pesan kesalahan:
Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.
Fitur dinonaktifkan.
Solusi: Aktifkan fitur, menggunakan bagian Dinonaktifkan secara default di artikel ini.
Contoh pesan kesalahan:
Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information
Penguncian
Mengambil kunci bersama pada objek SCHEMARESOLUTION.
Contoh
J. Menggunakan CETAS dengan tampilan untuk membuat tabel eksternal menggunakan identitas terkelola
Contoh ini menyediakan kode untuk menulis CETAS dengan tampilan sebagai sumber, menggunakan identitas terkelola sistem sebagai autentikasi.
CREATE DATABASE [<mydatabase>];
GO
USE [<mydatabase>];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO
CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO
CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
CREDENTIAL = [WorkspaceIdentity]
);
GO
CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
LOCATION = '<myoutputsubfolder>/',
DATA_SOURCE = [SQLwriteable],
FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO
B. Menggunakan CETAS dengan tampilan untuk membuat tabel eksternal dengan autentikasi SAS
Contoh ini menyediakan kode untuk menulis CETAS dengan tampilan sebagai sumber, menggunakan token SAS sebagai autentikasi.
CREATE DATABASE [<mydatabase>];
GO
USE [<mydatabase>];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
CREATE DATABASE SCOPED CREDENTIAL SAS_token
WITH
IDENTITY = 'SHARED ACCESS SIGNATURE',
-- Remove ? from the beginning of the SAS token
SECRET = '<azure_shared_access_signature>' ;
GO
CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO
CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
CREDENTIAL = [SAS_token]
);
GO
CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
LOCATION = '<myoutputsubfolder>/',
DATA_SOURCE = [SQLwriteable],
FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO
C. Membuat tabel eksternal ke dalam satu file parquet pada penyimpanan
Dua contoh berikutnya menunjukkan cara membongkar beberapa data dari tabel lokal ke dalam tabel eksternal yang disimpan sebagai file parket pada kontainer penyimpanan Azure Blob. Mereka dirancang untuk bekerja dengan AdventureWorks2022
database. Contoh ini memperlihatkan pembuatan tabel eksternal sebagai file parket tunggal, di mana contoh berikutnya memperlihatkan cara membuat tabel eksternal dan mempartisinya ke dalam beberapa folder dengan file parket.
Contoh di bawah ini berfungsi menggunakan identitas terkelola untuk autentikasi. Dengan demikian, pastikan bahwa perwakilan layanan Azure SQL Managed Instance Anda memiliki peran Kontributor Data Blob Penyimpanan pada Kontainer Azure Blob Storage Anda. Atau, Anda dapat mengubah contoh dan menggunakan token Rahasia Akses Bersama (SAS) untuk autentikasi.
Contoh berikut, Anda membuat tabel eksternal ke dalam satu file parket di Azure Blob Storage, memilih dari SalesOrderHeader
tabel untuk pesanan yang lebih lama dari 1-Jan-2014:
--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO
CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
WITH IDENTITY = 'managed identity';
GO
CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
CREDENTIAL = [CETASCredential] );
GO
CREATE EXTERNAL FILE FORMAT [CETASFileFormat]
WITH(
FORMAT_TYPE=PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO
-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
OrderDate < '2013-12-31';
-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
LOCATION = 'SalesOrders/',
DATA_SOURCE = [CETASExternalDataSource],
FILE_FORMAT = [CETASFileFormat])
AS
SELECT
*
FROM
[AdventureWorks2022].[Sales].[SalesOrderHeader]
WHERE
OrderDate < '2013-12-31';
-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;
D. Membuat tabel eksternal yang dipartisi ke dalam beberapa file parket yang disimpan di pohon folder
Contoh ini dibangun pada contoh sebelumnya untuk menunjukkan cara membuat tabel eksternal dan mempartisinya ke dalam beberapa folder dengan file parket. Anda dapat menggunakan tabel yang dipartisi untuk mendapatkan manfaat performa jika himpunan data Anda besar.
Buat tabel eksternal dari SalesOrderHeader
data, menggunakan langkah-langkah dari Contoh B, tetapi partisi tabel eksternal menurut OrderDate
tahun dan bulan. Saat mengkueri tabel eksternal yang dipartisi, kita dapat memperoleh manfaat dari eliminasi partisi untuk performa.
--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
LOCATION = 'PartitionedOrders/year=*/month=*/',
DATA_SOURCE = CETASExternalDataSource,
FILE_FORMAT = CETASFileFormat,
--year and month will correspond to the two respective wildcards in folder path
PARTITION (
[Year],
[Month]
)
)
AS
SELECT
*,
YEAR(OrderDate) AS [Year],
MONTH(OrderDate) AS [Month]
FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
WHERE
OrderDate < '2013-12-31';
GO
-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;