CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)

Artikel
07/20/2022
9 menit untuk membaca

Berlaku untuk: Azure Synapse Analytics Sistem Platform Analitik (PDW)

Membuat tabel eksternal lalu mengekspor, secara paralel, hasil pernyataan T-SQL SELECT ke penyimpanan Hadoop atau Azure Blob.

Topic link icon Konvensi Sintaksis SQL Transaksi (SQL Transact)

Sintaks

CREATE EXTERNAL TABLE {[ [database_name  . [ schema_name ] . ] | schema_name . ] table_name }
    [(column_name [,...n ] ) ]
    WITH (   
        LOCATION = 'hdfs_folder',  
        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 adalah nama tabel satu hingga tiga bagian untuk dibuat dalam database. Untuk tabel eksternal, hanya metadata tabel yang disimpan dalam database relasional.

[ ( column_name [ ,... n ] ) ] adalah nama kolom tabel.

LOCATION = '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 akan membuat jalur dan folder jika belum ada.

File eksternal ditulis ke hdfs_folder dan diberi nama QueryID_date_time_ID.format, di mana ID adalah pengidentifikasi dan format inkremental adalah format data yang diekspor. Contohnya adalah QID776_20160130_182739_0.orc.

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 tidak berlaku pada saat pernyataan CREATE EXTERNAL TABLE AS SELECT ini dijalankan. Sebaliknya, mereka 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 tolak untuk menentukan jumlah atau persentase baris yang bisa 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 ditentukan sebagai nilai harfiah atau persentase.
- Nilai digunakan jika REJECT_VALUE adalah nilai harfiah, bukan persentase. Database akan berhenti mengimpor baris dari file data eksternal ketika jumlah baris yang gagal melebihi reject_value.
  
  Misalnya, jika REJECT_VALUE = 5 dan REJECT_TYPE = nilai, database akan berhenti mengimpor baris setelah lima baris gagal diimpor.
- Persentase digunakan jika REJECT_VALUE adalah persentase, bukan nilai harfiah. Database akan berhenti mengimpor baris dari file data eksternal ketika persentase baris yang gagal melebihi reject_value. Persentase baris yang gagal dihitung pada interval.
REJECT_SAMPLE_VALUE = reject_sample_value diperlukan saat REJECT_TYPE = persentase, ini 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 akan 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 = persentase, REJECT_VALUE = 30, dan 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 tolak 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)

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

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 akan 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 akan menggunakan nama kolom dalam hasil pernyataan pilih.

Anda tidak dapat menentukan opsi kolom lain seperti tipe 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:

IZIN ALTER SCHEMA pada skema lokal yang akan berisi tabel atau keanggotaan baru dalam peran database tetap db_ddladmin .
Izin atau keanggotaan CREATE TABLE dalam peran database tetap db_ddladmin .
Izin SELECT pada objek apa pun yang direferensikan dalam select_criteria.

MENGELOLA OPERASI MASSAL
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT
Izin tulis untuk membaca dan menulis ke folder eksternal pada kluster Hadoop atau di penyimpanan Blob.

Penting

Izin ALTER ANY EXTERNAL DATA SOURCE memberi prinsipal kemampuan untuk membuat dan memodifikasi objek sumber data eksternal apa pun, sehingga juga memberikan kemampuan untuk mengakses semua kredensial lingkup 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 kluster Hadoop eksternal atau penyimpanan Blob. Jika koneksi gagal, perintah akan gagal dan tabel eksternal tidak akan 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 akan melakukan upaya satu kali untuk menghapus file dan folder baru yang sudah dibuat di sumber data eksternal.

Database akan melaporkan kesalahan Java apa pun yang terjadi pada sumber data eksternal selama ekspor data.

Pernyataan umum

Setelah pernyataan CREATE EXTERNAL TABLE AS SELECT selesai, Anda bisa menjalankan kueri transact-SQL pada tabel eksternal. Operasi ini akan mengimpor data ke dalam 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.

File eksternal diberi nama QueryID_date_time_ID.format, di mana ID adalah pengidentifikasi dan format inkremental adalah format data yang diekspor. Contohnya adalah QID776_20160130_182739_0.orc.

Pernyataan CREATE EXTERNAL TABLE AS SELECT selalu membuat tabel yang tidak dipartisi, meskipun tabel sumber dipartisi.

Untuk rencana kueri, dibuat dengan EXPLAIN, database menggunakan operasi rencana kueri ini untuk tabel eksternal:

Pemindahan pengacakan eksternal
Pemindahan siaran eksternal
Pemindahan partisi eksternal

Berlaku untuk: Gudang Data Paralel

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 akan beroperasi pada data yang disimpan dalam database. Akibatnya, hanya metadata yang akan 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 akan 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 Transact-SQLstatements pembaruan, sisipkan, atau hapus SQL Transact-SQL untuk mengubah 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 untuk kumpulan SQL tanpa server 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.

PolyBase dapat menggunakan maksimal 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. Kami menyarankan agar 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.

SET ROWCOUNT (Transact-SQL) tidak berpengaruh pada CREATE EXTERNAL TABLE AS SELECT ini. Untuk mencapai perilaku serupa, gunakan TOP (Transact-SQL).

Saat CREATE EXTERNAL TABLE AS SELECT memilih dari RCFile, nilai kolom di RCFile tidak boleh berisi karakter "|" pipa.

CREATE EXTERNAL TABLE AS SELECT ke file Parquet atau ORC akan menyebabkan kesalahan, yang dapat menyertakan rekaman yang ditolak ketika karakter berikut ada dalam data:

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

Penguncian

Mengambil kunci bersama pada objek SCHEMARESOLUTION.

Contoh

A. Membuat tabel Hadoop dengan menggunakan CREATE EXTERNAL TABLE AS SELECT

Contoh berikut membuat tabel eksternal baru bernama hdfsCustomer yang menggunakan definisi kolom dan data dari tabel dimCustomersumber .

Definisi tabel disimpan dalam database, dan hasil pernyataan SELECT diekspor ke file '/pdwdata/customer.tbl' 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 membuatnya.

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;

B. Menggunakan petunjuk kueri dengan CREATE EXTERNAL TABLE AS SELECT

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 Klausul OPTION (Transact-SQL).