BUAT TABEL EKSTERNAL (Transact-SQL)

Membuat tabel eksternal.

Artikel ini menyediakan sintaks, argumen, keterangan, izin, dan contoh untuk produk SQL mana pun yang Anda pilih.

Untuk informasi selengkapnya tentang konvensi sintaks, lihat Konvensi sintaks transact-SQL.

Pilih produk

Di baris berikut, pilih nama produk yang Anda minati, dan hanya informasi produk yang ditampilkan.

* SQL Server *  

Microsoft Azure SQL Database

Instans Terkelola Azure SQL

Azure Synapse
Analytics

Platform Analitik
Sistem (PDW)

 

Gambaran Umum: SQL Server

Perintah ini membuat tabel eksternal untuk PolyBase untuk mengakses data yang disimpan dalam kluster Hadoop atau tabel eksternal Azure Blob Storage PolyBase yang mereferensikan data yang disimpan dalam kluster Hadoop atau Azure Blob Storage.

Berlaku untuk: SQL Server 2016 (atau lebih tinggi)

Gunakan tabel eksternal dengan sumber data eksternal untuk kueri PolyBase. Sumber data eksternal digunakan untuk membangun konektivitas dan mendukung kasus penggunaan utama ini:

• Virtualisasi data dan pemuatan data menggunakan PolyBase
• Operasi pemuatan massal menggunakan SQL Server atau SQL Database menggunakan BULK INSERT atau OPENROWSET

Lihat juga MEMBUAT SUMBER DATA EKSTERNAL dan MENGHILANGKAN TABEL EKSTERNAL.

Sintaks

-- Create a new external table
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        [ FILE_FORMAT = external_file_format_name ]
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

Argumen

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

Satu hingga tiga bagian nama tabel yang akan dibuat. Untuk tabel eksternal, SQL hanya menyimpan metadata tabel bersama dengan statistik dasar tentang file atau folder yang dirujuk di Hadoop atau Azure Blob Storage. Tidak ada data aktual yang dipindahkan atau disimpan di SQL Server.

Penting

Untuk performa terbaik, jika driver sumber data eksternal mendukung nama tiga bagian, sangat disarankan untuk memberikan nama tiga bagian.

<> column_definition [ ,...n ]

CREATE EXTERNAL TABLE mendukung kemampuan untuk mengonfigurasi nama kolom, jenis data, nullability, dan collation. Anda tidak bisa menggunakan DEFAULT CONSTRAINT pada tabel eksternal.

Definisi kolom, termasuk jenis data dan jumlah kolom, harus cocok dengan data dalam file eksternal. Jika ada ketidakcocokan, baris file akan ditolak saat mengkueri data aktual.

LOCATION = 'folder_or_filepath'

Menentukan folder atau jalur file dan nama file untuk data aktual di Hadoop atau Azure Blob Storage. Selain itu, penyimpanan objek yang kompatibel dengan S3 didukung mulai SQL Server 2022 (16.x)). Lokasi dimulai dari folder akar. Folder akar adalah lokasi data yang ditentukan dalam sumber data eksternal.

Di SQL Server, pernyataan CREATE EXTERNAL TABLE membuat jalur dan folder jika belum ada. Anda kemudian dapat menggunakan INSERT INTO untuk mengekspor data dari tabel SQL Server lokal ke sumber data eksternal. Untuk informasi selengkapnya, lihat Kueri PolyBase.

Jika Anda menentukan LOCATION menjadi folder, kueri PolyBase yang memilih dari tabel eksternal akan mengambil file dari folder dan semua subfoldernya. Sama seperti Hadoop, PolyBase tidak mengembalikan folder tersembunyi. Ini juga tidak mengembalikan file yang nama filenya dimulai dengan garis bawah (_) atau titik (.).

Dalam contoh gambar berikut, jika LOCATION='/webdata/', kueri PolyBase akan mengembalikan baris dari mydata.txt dan mydata2.txt. Ini tidak akan kembali mydata3.txt karena merupakan file dalam subfolder tersembunyi. Dan itu tidak akan kembali _hidden.txt karena itu adalah file tersembunyi.

Untuk mengubah default dan hanya membaca dari folder akar, atur atribut <polybase.recursive.traversal> ke 'false' dalam file konfigurasi core-site.xml. File ini terletak di bawah <SqlBinRoot>\PolyBase\Hadoop\Conf bin akar SQL Server. Contohnya,C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn.

DATA_SOURCE = external_data_source_name

Menentukan nama sumber data eksternal yang berisi lokasi data eksternal. Lokasi ini adalah Hadoop File System (HDFS), kontainer Azure Blob Storage, atau Azure Data Lake Store. Untuk membuat sumber data eksternal, gunakan CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

Menentukan nama objek format file eksternal yang menyimpan jenis file dan metode kompresi untuk data eksternal. Untuk membuat format file eksternal, gunakan CREATE EXTERNAL FILE FORMAT.

Format file eksternal dapat digunakan kembali oleh beberapa file eksternal serupa.

Opsi Penolakan

Opsi ini hanya dapat digunakan dengan sumber data eksternal di mana TYPE = HADOOP.

Anda dapat menentukan parameter penolakan yang menentukan bagaimana PolyBase akan menangani rekaman kotor yang diambilnya dari sumber data eksternal. Rekaman data dianggap 'kotor' jika jenis data aktual atau jumlah kolom tidak cocok dengan definisi kolom tabel eksternal.

Saat Anda tidak menentukan atau mengubah nilai penolakan, PolyBase menggunakan nilai default. Informasi tentang parameter penolakan ini disimpan sebagai metadata tambahan saat Anda membuat tabel eksternal dengan pernyataan CREATE EXTERNAL TABLE. Saat pernyataan SELECT mendatang atau pernyataan SELECT INTO SELECT memilih data dari tabel eksternal, PolyBase akan menggunakan opsi penolakan untuk menentukan jumlah atau persentase baris yang dapat ditolak sebelum kueri aktual gagal. Kueri akan menampilkan hasil (parsial) sampai ambang penolakan terlampaui. Kueri kemudian gagal dengan pesan kesalahan yang sesuai.

REJECT_TYPE = nilai | Persentase

Mengklarifikasi apakah opsi REJECT_VALUE ditentukan sebagai nilai harfiah atau persentase.

value

REJECT_VALUE adalah nilai harfiah, bukan persentase. Kueri akan gagal ketika jumlah baris yang ditolak melebihi reject_value.

Misalnya, jika REJECT_VALUE = 5 dan REJECT_TYPE = value, kueri SELECT akan gagal setelah lima baris ditolak.

Persentase

REJECT_VALUE adalah persentase, bukan nilai harfiah. Kueri akan gagal ketika persentase baris yang gagal melebihi reject_value. Persentase baris yang gagal dihitung pada interval.

REJECT_VALUE = reject_value

Menentukan nilai atau persentase baris yang dapat ditolak sebelum kueri gagal.

Untuk REJECT_TYPE = nilai, reject_value harus berupa bilangan bulat antara 0 dan 2.147.483.647.

Untuk REJECT_TYPE = persentase, reject_value harus berupa float antara 0 dan 100.

REJECT_SAMPLE_VALUE = reject_sample_value

Atribut ini diperlukan saat Anda menentukan REJECT_TYPE = persentase. Ini menentukan jumlah baris untuk mencoba mengambil sebelum PolyBase menghitung ulang persentase baris yang ditolak.

Parameter reject_sample_value harus berupa bilangan bulat antara 0 dan 2.147.483.647.

Misalnya, jika REJECT_SAMPLE_VALUE = 1000, PolyBase akan menghitung persentase baris yang gagal setelah mencoba mengimpor 1000 baris dari file data eksternal. Jika persentase baris yang gagal kurang dari reject_value, PolyBase akan mencoba mengambil 1000 baris lainnya. Ini terus menghitung ulang persentase baris yang gagal setelah mencoba mengimpor setiap 1000 baris tambahan.

Catatan

Karena PolyBase 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:

• PolyBase mencoba mengambil 100 baris pertama; 25 gagal dan 75 berhasil.
• Persentase baris yang gagal dihitung sebagai 25%, yang kurang dari nilai penolakan 30%. Akibatnya, PolyBase akan terus mengambil data dari sumber data eksternal.
• PolyBase mencoba memuat 100 baris berikutnya; kali ini 25 baris berhasil dan 75 baris gagal.
• Persentase baris yang gagal dihitung ulang sebagai 50%. Persentase baris yang gagal telah melebihi nilai penolakan 30%.
• Kueri PolyBase gagal dengan 50% baris yang ditolak setelah mencoba mengembalikan 200 baris pertama. Perhatikan bahwa baris yang cocok telah dikembalikan sebelum kueri PolyBase mendeteksi ambang penolakan telah terlampaui.

REJECTED_ROW_LOCATION = Lokasi Direktori

Berlaku untuk: SQL Server 2019 CU6 dan versi yang lebih baru, Azure Synapse Analytics.

Menentukan direktori dalam Sumber Data Eksternal bahwa baris yang ditolak dan file kesalahan yang sesuai harus ditulis.

Jika jalur yang ditentukan tidak ada, PolyBase akan membuatnya atas nama Anda. Direktori anak dibuat dengan nama "_rejectedrows". Karakter "_" memastikan bahwa direktori lolos untuk pemrosesan data lain kecuali dinamai secara eksplisit dalam parameter lokasi. Dalam direktori ini, ada folder yang dibuat berdasarkan waktu pengiriman beban dalam format YearMonthDay -HourMinuteSecond (Misalnya). 20230330-173205 Dalam folder ini, dua jenis file ditulis, file _reason dan file data. Opsi ini hanya dapat digunakan dengan sumber data eksternal di mana TYPE = HADOOP dan untuk tabel eksternal menggunakan DELIMITEDTEXT FORMAT_TYPE. Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL dan MEMBUAT FORMAT FILE EKSTERNAL.

File alasan dan file data keduanya memiliki queryID yang terkait dengan pernyataan CTAS. Karena data dan alasannya ada dalam file terpisah, file yang sesuai memiliki akhiran yang cocok.

Izin

Memerlukan izin pengguna ini:

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• UBAH FORMAT FILE EKSTERNAL APA PUN (hanya berlaku untuk hadoop dan sumber data eksternal Azure Storage)
• CONTROL DATABASE (hanya berlaku untuk hadoop dan sumber data eksternal Azure Storage)

Catatan, login jarak jauh yang ditentukan dalam DATABASE SCOPED CREDENTIAL yang digunakan dalam perintah CREATE EXTERNAL TABLE harus memiliki izin baca untuk jalur/tabel/koleksi pada sumber data eksternal yang ditentukan dalam parameter LOCATION. Jika Anda berencana menggunakan TABEL EKSTERNAL ini untuk mengekspor data ke sumber data eksternal Hadoop atau Azure Storage, maka login yang ditentukan harus memiliki izin tulis pada jalur yang ditentukan di LOCATION. Perhatikan bahwa Hadoop saat ini tidak didukung di SQL Server 2022 (16.x).

Untuk Azure Blob Storage, saat mengonfigurasi kunci akses dan tanda tangan akses bersama (SAS) di portal Azure, akun penyimpanan Azure Blob Storage atau ADLS Gen2, konfigurasikan izin yang diizinkan untuk memberikan setidaknya izin Baca dan Tulis. Izin daftar mungkin juga diperlukan saat mencari di seluruh folder. Anda juga harus memilih Kontainer dan Objek sebagai jenis sumber daya yang diizinkan.

Penting

Izin UBAH SUMBER DATA EKSTERNAL apa pun memberi prinsipal kemampuan untuk membuat dan memodifikasi objek sumber data eksternal apa pun, dan oleh karena itu, ia juga memberikan kemampuan untuk mengakses semua kredensial cakupan database pada database. Izin ini harus dianggap sangat istimewa, dan oleh karena itu harus diberikan hanya kepada prinsipal tepercaya dalam sistem.

Penanganan kesalahan

Saat menjalankan pernyataan CREATE EXTERNAL TABLE, PolyBase mencoba menyambungkan ke sumber data eksternal. Jika upaya untuk menyambungkan gagal, pernyataan akan gagal dan tabel eksternal tidak akan dibuat. Diperlukan waktu satu menit atau lebih agar perintah gagal karena PolyBase mencoba kembali koneksi sebelum akhirnya gagal kueri.

Keterangan

Dalam skenario kueri ad hoc, seperti SELECT FROM EXTERNAL TABLE, PolyBase menyimpan baris yang diambil dari sumber data eksternal dalam tabel sementara. Setelah kueri selesai, PolyBase menghapus dan menghapus tabel sementara. Tidak ada data permanen yang disimpan dalam tabel SQL.

Sebaliknya, dalam skenario impor, seperti SELECT INTO FROM EXTERNAL TABLE, PolyBase menyimpan baris yang diambil dari sumber data eksternal sebagai data permanen dalam tabel SQL. Tabel baru dibuat selama eksekusi kueri saat PolyBase mengambil data eksternal.

PolyBase dapat mendorong beberapa komputasi kueri ke Hadoop untuk meningkatkan performa kueri. Tindakan ini disebut pushdown predikat. Untuk mengaktifkannya, tentukan opsi lokasi manajer sumber daya Hadoop di CREATE EXTERNAL DATA SOURCE.

Anda dapat membuat banyak tabel eksternal yang mereferensikan sumber data eksternal yang sama atau berbeda.

Pembatasan dan batasan

Karena data untuk tabel eksternal tidak berada di bawah kontrol manajemen langsung SQL Server, data dapat diubah atau dihapus kapan saja oleh proses eksternal. Akibatnya, hasil kueri terhadap tabel eksternal tidak dijamin deterministik. Kueri yang sama dapat mengembalikan hasil yang berbeda setiap kali dijalankan terhadap tabel eksternal. Demikian pula, kueri mungkin gagal jika data eksternal dipindahkan atau dihapus.

Anda dapat membuat beberapa tabel eksternal yang masing-masing mereferensikan sumber data eksternal yang berbeda. Jika Anda secara bersamaan menjalankan kueri terhadap sumber data Hadoop yang berbeda, maka setiap sumber Hadoop harus menggunakan pengaturan konfigurasi server 'konektivitas hadoop' yang sama. Misalnya, Anda tidak dapat menjalankan kueri secara bersamaan terhadap kluster Cloudera Hadoop dan kluster Hortonworks Hadoop karena ini menggunakan pengaturan konfigurasi yang berbeda. Untuk pengaturan konfigurasi dan kombinasi yang didukung, lihat Konfigurasi Konektivitas PolyBase.

Saat tabel eksternal menggunakan DELIMITEDTEXT, , CSV, PARQUETatau DELTA sebagai jenis data, tabel eksternal hanya mendukung statistik untuk satu kolom per CREATE STATISTICS perintah.

Hanya pernyataan Data Definition Language (DDL) ini yang diizinkan pada tabel eksternal:

• BUAT TABEL dan LETAKKAN TABEL
• MEMBUAT STATISTIK DAN MENGHILANGKAN STATISTIK
• BUAT TAMPILAN dan JATUHKAN TAMPILAN

Konstruksi dan operasi tidak didukung:

• Batasan DEFAULT pada kolom tabel eksternal
• Operasi Data Manipulation Language (DML) dari hapus, sisipkan, dan perbarui

Batasan kueri

PolyBase dapat menggunakan maksimum 33k 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 file 33k. Kami menyarankan agar Anda menjaga jalur file eksternal tetap pendek dan menggunakan tidak lebih dari 30k file per folder HDFS. Ketika terlalu banyak file direferensikan, pengecualian kehabisan memori Java Virtual Machine (JVM) mungkin terjadi.

Batasan lebar tabel

PolyBase di SQL Server 2016 memiliki batas lebar baris 32 KB berdasarkan ukuran maksimum satu baris yang valid menurut definisi tabel. Jika jumlah skema kolom lebih besar dari 32 KB, PolyBase tidak dapat mengkueri data.

Batasan jenis data

Tipe data berikut tidak dapat digunakan dalam tabel eksternal PolyBase:

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• Jenis yang ditentukan pengguna

Batasan spesifik sumber data

Oracle

Sinonim Oracle tidak didukung untuk penggunaan dengan PolyBase.

Tabel eksternal ke koleksi MongoDB yang berisi array

Untuk membuat tabel eksternal ke koleksi MongoDB yang berisi array, Anda harus menggunakan ekstensi Virtualisasi Data untuk Azure Data Studio untuk menghasilkan pernyataan CREATE EXTERNAL TABLE berdasarkan skema yang terdeteksi oleh Driver ODBC PolyBase untuk MongoDB. Tindakan meratakan dilakukan secara otomatis oleh driver. Atau, Anda dapat menggunakan sp_data_source_objects (Transact-SQL) untuk mendeteksi skema koleksi (kolom) dan membuat tabel eksternal secara manual. Prosedur sp_data_source_table_columns tersimpan juga secara otomatis melakukan meratakan melalui Driver ODBC PolyBase untuk driver MongoDB. Ekstensi Virtualisasi Data untuk Azure Data Studio dan sp_data_source_table_columns gunakan prosedur tersimpan internal yang sama untuk mengkueri skema eksternal.

Penguncian

Kunci bersama pada objek SCHEMARESOLUTION.

Keamanan

File data untuk tabel eksternal disimpan di Hadoop atau Azure Blob Storage. File data ini dibuat dan dikelola oleh proses Anda sendiri. Anda bertanggung jawab untuk mengelola keamanan data eksternal.

Contoh

J. Membuat tabel eksternal dengan data dalam format yang dibatasi teks

Contoh ini memperlihatkan semua langkah yang diperlukan untuk membuat tabel eksternal yang memiliki data yang diformat dalam file yang dibatasi teks. Ini mendefinisikan sumber data eksternal mydatasource dan format file eksternal myfileformat. Objek tingkat database ini kemudian dirujuk dalam pernyataan CREATE EXTERNAL TABLE. Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL dan MEMBUAT FORMAT FILE EKSTERNAL.

CREATE EXTERNAL DATA SOURCE mydatasource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')
);

CREATE EXTERNAL TABLE ClickStream (
    url varchar(50),
    event_date date,
    user_IP varchar(50)
)
WITH (
        LOCATION='/webdata/employee.tbl',
        DATA_SOURCE = mydatasource,
        FILE_FORMAT = myfileformat
    )
;

B. Membuat tabel eksternal dengan data dalam format RCFile

Contoh ini memperlihatkan semua langkah yang diperlukan untuk membuat tabel eksternal yang memiliki data yang diformat sebagai RCFiles. Ini mendefinisikan mydatasource_rc sumber data eksternal dan format file eksternal myfileformat_rc. Objek tingkat database ini kemudian dirujuk dalam pernyataan CREATE EXTERNAL TABLE. Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL dan MEMBUAT FORMAT FILE EKSTERNAL.

CREATE EXTERNAL DATA SOURCE mydatasource_rc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_rc
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
)
;

CREATE EXTERNAL TABLE ClickStream_rc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/employee_rc.tbl',
        DATA_SOURCE = mydatasource_rc,
        FILE_FORMAT = myfileformat_rc
    )
;

C. Membuat tabel eksternal dengan data dalam format ORC

Contoh ini memperlihatkan semua langkah yang diperlukan untuk membuat tabel eksternal yang memiliki data yang diformat sebagai file ORC. Ini mendefinisikan mydatasource_orc sumber data eksternal dan format file eksternal myfileformat_orc. Objek tingkat database ini kemudian dirujuk dalam pernyataan CREATE EXTERNAL TABLE. Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL dan MEMBUAT FORMAT FILE EKSTERNAL.

CREATE EXTERNAL DATA SOURCE mydatasource_orc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_orc
WITH (
    FORMAT = ORC,
    COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
;

CREATE EXTERNAL TABLE ClickStream_orc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/',
        DATA_SOURCE = mydatasource_orc,
        FILE_FORMAT = myfileformat_orc
    )
;

D. Mengkueri data Hadoop

ClickStream adalah tabel eksternal yang terhubung ke employee.tbl file teks yang dibatasi pada kluster Hadoop. Kueri berikut ini terlihat seperti kueri terhadap tabel standar. Namun, kueri ini mengambil data dari Hadoop lalu menghitung hasilnya.

SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx';

E. Menggabungkan data Hadoop dengan data SQL

Kueri ini terlihat seperti JOIN standar pada dua tabel SQL. Perbedaannya adalah polyBase mengambil data clickstream dari Hadoop lalu menggabungkannya ke UrlDescription tabel. Satu tabel adalah tabel eksternal dan tabel lainnya adalah tabel SQL standar.

SELECT url.description
FROM ClickStream cs
JOIN UrlDescription url ON cs.url = url.name
WHERE cs.url = 'msdn.microsoft.com';

F. Mengimpor data dari Hadoop ke dalam tabel SQL

Contoh ini membuat tabel ms_user SQL baru yang secara permanen menyimpan hasil gabungan antara tabel user SQL standar dan tabel ClickStreameksternal .

SELECT DISTINCT user.FirstName, user.LastName
INTO ms_user
FROM user INNER JOIN (
    SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'
    ) AS ms
ON user.user_ip = ms.user_ip;

G. Membuat tabel eksternal untuk SQL Server

Sebelum Anda membuat kredensial lingkup database, database pengguna harus memiliki kunci master untuk melindungi kredensial. Untuk informasi selengkapnya, lihat MEMBUAT KUNCI MASTER dan MEMBUAT KREDENSIAL CAKUPAN DATABASE.

     -- Create a Master Key
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';
    GO
     /*  specify credentials to external data source
     *  IDENTITY: user name for external source.
     *  SECRET: password for external source.
     */
     CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
     WITH IDENTITY = 'username', Secret = 'password';
    GO

Buat sumber data eksternal baru bernama SQLServerInstance, dan tabel eksternal bernama sqlserver.customer:

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH (
    LOCATION = 'sqlserver://SqlServer',
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = SQLServerCredentials
    );
    GO

    CREATE SCHEMA sqlserver;
    GO

     /* LOCATION: sql server table/view in 'database_name.schema_name.object_name' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE sqlserver.customer(
     C_CUSTKEY INT NOT NULL,
     C_NAME VARCHAR(25) NOT NULL,
     C_ADDRESS VARCHAR(40) NOT NULL,
     C_NATIONKEY INT NOT NULL,
     C_PHONE CHAR(15) NOT NULL,
     C_ACCTBAL DECIMAL(15,2) NOT NULL,
     C_MKTSEGMENT CHAR(10) NOT NULL,
     C_COMMENT VARCHAR(117) NOT NULL
      )
      WITH (
      LOCATION='tpch_10.dbo.customer',
      DATA_SOURCE=SqlServerInstance
     );

I. Membuat tabel eksternal untuk Oracle

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

   /*
   * LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
   * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
   * CONNECTION_OPTIONS: Specify driver location
   * CREDENTIAL: the database scoped credential, created above.
   */
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (
     LOCATION = 'oracle://<server address>[:<port>]',
     -- PUSHDOWN = ON | OFF,
     CREDENTIAL = credential_name)

   /*
   * LOCATION: Oracle table/view in '<database_name>.<schema_name>.<object_name>' format. Note this may be case sensitive in the Oracle database.
   * DATA_SOURCE: the external data source, created above.
   */
   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH (
    LOCATION='DB1.mySchema.customer',
    DATA_SOURCE= external_data_source_name
   );

j. Membuat tabel eksternal untuk Teradata

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = teradata://<server address>[:<port>],
   -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );


     /* LOCATION: Teradata table/view in '<database_name>.<object_name>' format
      * DATA_SOURCE: the external data source, created above.
      */
     CREATE EXTERNAL TABLE customer(
      L_ORDERKEY INT NOT NULL,
      L_PARTKEY INT NOT NULL,
     L_SUPPKEY INT NOT NULL,
     L_LINENUMBER INT NOT NULL,
     L_QUANTITY DECIMAL(15,2) NOT NULL,
     L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
     L_DISCOUNT DECIMAL(15,2) NOT NULL,
     L_TAX DECIMAL(15,2) NOT NULL,
     L_RETURNFLAG CHAR NOT NULL,
     L_LINESTATUS CHAR NOT NULL,
     L_SHIPDATE DATE NOT NULL,
     L_COMMITDATE DATE NOT NULL,
     L_RECEIPTDATE DATE NOT NULL,
     L_SHIPINSTRUCT CHAR(25) NOT NULL,
     L_SHIPMODE CHAR(10) NOT NULL,
     L_COMMENT VARCHAR(44) NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

K. Membuat tabel eksternal untuk MongoDB

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

     /* LOCATION: Location string should be of format '<type>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = mongodb://<server>[:<port>],
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = credential_name
    );

     /* LOCATION: MongoDB table/view in '<database_name>.<schema_name>.<object_name>' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE customers(
     [O_ORDERKEY] DECIMAL(38) NOT NULL,
     [O_CUSTKEY] DECIMAL(38) NOT NULL,
     [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
     [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
     [O_ORDERDATE] DATETIME2(0) NOT NULL,
     [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

L. Kueri penyimpanan objek yang kompatibel dengan S3 melalui tabel eksternal

Berlaku untuk: SQL Server 2022 (16.x) dan yang lebih baru

Contoh berikut menunjukkan penggunaan T-SQL untuk mengkueri file parket yang disimpan dalam penyimpanan objek yang kompatibel dengan S3 melalui kueri tabel eksternal. Sampel menggunakan jalur relatif dalam sumber data eksternal.

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE Region(
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds',
FILE_FORMAT = ParquetFileFormat);
GO

Langkah berikutnya

Pelajari selengkapnya tentang konsep terkait dalam artikel berikut:

• CREATE EXTERNAL DATA SOURCE
• BUAT FORMAT FILE EKSTERNAL
• sp_data_source_objects (T-SQL)
• Ekstensi Virtualisasi Data untuk Azure Data Studio