BUAT TABEL EKSTERNAL (Transact-SQL)

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru dari Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsPlatform Analitik System (PDW)Warehouse di Microsoft FabricSQL database di Microsoft Fabric

Membuat tabel eksternal.

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

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

Gudang Data Fabri

Database SQL Fabric

Azure Synapse
Analitik

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 (13.x) dan versi yang lebih baru.

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 virtualisasi Data dengan PolyBase di SQL Server
• Operasi pemuatan massal menggunakan SQL Server atau SQL Database menggunakan BULK INSERT atau OPENROWSET

Tabel eksternal didasarkan pada sumber data eksternal.

Konvensi sintaks Transact-SQL

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, Anda harus memberikan nama tiga bagian.

< > column_definition [ ,...n ]

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

Definisi kolom, termasuk jenis data dan jumlah kolom, harus cocok dengan data dalam file eksternal. Jika ada ketidakcocokan, baris file 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.

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 REJECT

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

Anda dapat menentukan parameter penolakan yang menentukan bagaimana PolyBase 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 CREATE EXTERNAL TABLE pernyataan. Saat pernyataan atau SELECT INTO SELECT pernyataan mendatang SELECT memilih data dari tabel eksternal, PolyBase menggunakan opsi penolakan untuk menentukan jumlah atau persentase baris yang dapat ditolak sebelum kueri aktual gagal. Kueri mengembalikan hasil (parsial) hingga ambang penolakan terlampaui. Kueri kemudian gagal dengan pesan kesalahan yang sesuai.

REJECT_TYPE = { value | percentage }

Mengklarifikasi apakah REJECT_VALUE opsi ditentukan sebagai nilai harfiah atau persentase.

• nilai

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

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

• Persentase

  REJECT_VALUE adalah persentase, bukan nilai harfiah. Kueri 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 = value, reject_value harus berupa bilangan bulat antara 0 dan 2.147.483.647.

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

REJECT_SAMPLE_VALUE = reject_sample_value

Atribut ini diperlukan saat Anda menentukan REJECT_TYPE = percentage. 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 menghitung persentase baris yang gagal setelah mencoba mengimpor 1.000 baris dari file data eksternal. Jika persentase baris yang gagal kurang dari reject_value, PolyBase mencoba mengambil 1.000 baris lainnya. Ini terus menghitung ulang persentase baris yang gagal setelah mencoba mengimpor setiap 1.000 baris tambahan.

Catatan

Karena PolyBase menghitung persentase baris yang gagal pada interval, persentase aktual baris yang gagal dapat melebihi reject_value.

Example

Contoh ini menunjukkan bagaimana tiga REJECT opsi berinteraksi satu sama lain. Misalnya, jika REJECT_TYPE = percentage, , REJECT_VALUE = 30dan 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 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. Baris yang cocok dikembalikan sebelum kueri PolyBase mendeteksi ambang penolakan telah terlampaui.

REJECTED_ROW_LOCATION = lokasi direktori

Berlaku untuk: SQL Server 2019 (15.x) CU 6 dan versi yang lebih baru, dan 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, _reason file dan file data. Opsi ini hanya dapat digunakan dengan sumber data eksternal di mana TYPE = HADOOP dan untuk tabel eksternal menggunakan DELIMITEDTEXTFORMAT_TYPE. Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL dan MEMBUAT FORMAT FILE EKSTERNAL.

Alasan file 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
• ALTER ANY EXTERNAL FILE FORMAT (hanya berlaku untuk hadoop dan sumber data eksternal Azure Storage)
• CONTROL DATABASE (hanya berlaku untuk hadoop dan sumber data eksternal Azure Storage)

Perhatikan, login jarak jauh yang ditentukan dalam DATABASE SCOPED CREDENTIAL yang digunakan dalam CREATE EXTERNAL TABLE perintah harus memiliki izin Baca untuk jalur/tabel/koleksi pada sumber data eksternal yang ditentukan dalam LOCATION parameter . Jika Anda berencana menggunakan ini EXTERNAL TABLE untuk mengekspor data ke hadoop atau sumber data eksternal Azure Storage, maka login yang ditentukan harus memiliki izin tulis pada jalur yang ditentukan dalam LOCATION. Hadoop 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 ini ALTER ANY EXTERNAL DATA SOURCE memberikan kemampuan kepada perwakilan mana pun 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 gagal dan tabel eksternal tidak 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.

Format Hadoop hanya didukung di SQL Server 2016 (13.x), SQL Server 2017 (14.x), dan SQL Server 2019 (15.x).

PolyBase dapat mendorong beberapa komputasi kueri ke Hadoop untuk meningkatkan performa kueri. Tindakan ini dikenal sebagai 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.

Keterbatasan

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:

• CREATE TABLE dan DROP TABLE
• CREATE STATISTICS dan DROP STATISTICS
• CREATE VIEW dan DROP VIEW

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

Di SQL Server 2016 (13.x), PolyBase 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:

• geografi
• geometri
• hierarkis
• gambar
• teks
• 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

Gunakan sp_data_source_objects untuk mendeteksi skema koleksi (kolom) untuk koleksi MongoDB yang berisi array, dan buat tabel eksternal secara manual. Prosedur sp_data_source_table_columns tersimpan juga secara otomatis melakukan meratakan melalui Driver ODBC PolyBase untuk driver MongoDB.

Penguncian

Kunci bersama pada SCHEMARESOLUTION objek.

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 mydatasource sumber data eksternal 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'
);
GO

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

CREATE EXTERNAL TABLE ClickStream
(
    url VARCHAR (50),
    event_date DATE,
    user_IP VARCHAR (50)
)
WITH (
    DATA_SOURCE = mydatasource,
    LOCATION = '/webdata/employee.tbl',
    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'
);
GO

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

CREATE EXTERNAL TABLE ClickStream_rc
(
    url VARCHAR (50),
    event_date DATE,
    user_ip VARCHAR (50)
)
WITH (
    DATA_SOURCE = mydatasource_rc,
    LOCATION = '/webdata/employee_rc.tbl',
    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'
);
GO

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

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 standar JOIN 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 AS cs
     INNER JOIN UrlDescription AS 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 = '<password>';
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>';

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;

/* 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
);

Saya. Membuat tabel eksternal untuk Oracle

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

/*
* 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>';
GO

/*
* 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. 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 (
    DATA_SOURCE = external_data_source_name,
    LOCATION = 'DB1.mySchema.customer'
);

j. Membuat tabel eksternal untuk Teradata

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

/*
* 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>';
GO

/* 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
);
GO

/* 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>';
GO

/*
* 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>';
GO

/* 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 versi 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
);

Konten terkait

• Buat Sumber Data Eksternal (Transact-SQL)
• BUAT FORMAT FILE EKSTERNAL (TRANSACT-SQL)
• sp_data_source_objects (T-SQL)