Bagikan melalui

Menyambungkan, mengkueri, dan mengekspor data dengan PolyBase

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceSQL database di Microsoft Fabric

Virtualisasi data memungkinkan Anda menjalankan kueri Transact-SQL (T-SQL) melalui data eksternal tanpa memuatnya ke database Anda. PolyBase adalah fitur Mesin Database yang mengimplementasikan virtualisasi data di seluruh SQL Server dan Azure SQL. Anda menentukan sumber data eksternal, format file opsional, dan tabel eksternal, lalu mengkueri tabel eksternal dengan SELECT seperti tabel lainnya.

Panduan ini membantu Anda:

  • Pahami PolyBase mana yang menampilkan platform SQL dan dukungan versi Anda.
  • Pilih antara OPENROWSET, tabel eksternal, dan BULK INSERT untuk mengkueri atau menyerap data.
  • Ikuti tautan langkah demi langkah untuk skenario umum.
  • Tinjau performa, pemecahan masalah, dan praktik terbaik untuk beban kerja produksi.

Kasus penggunaan umum

Tabel berikut ini menjelaskan kemungkinan skenario penggunaan.

Skenario Pakai
Eksplorasi ad-hoc file OPENROWSET(BULK ...)
Kueri file yang dapat digunakan kembali untuk BI/pelaporan Tabel eksternal di atas file
Kueri lintas database (SQL Server, Oracle, Teradata, MongoDB, ODBC) Konektor PolyBase dengan tabel eksternal
Mengekspor hasil kueri ke file CREATE EXTERNAL TABLE AS SELECT (CETAS)
Penyerapan massal ke dalam tabel BULK INSERT atau OPENROWSET(BULK ...) dengan INSERT ... SELECT

Fitur mana yang tersedia di mana?

Tabel berikut menunjukkan polyBase inti dan fitur virtualisasi data mana yang tersedia di setiap platform SQL. Gunakan tabel ini untuk menentukan apa yang dapat Anda lakukan di platform Anda sebelum Anda menggunakan panduan terperinci.

Feature SQL Server 2019 SQL Server 2022 SQL Server 2025 Azure SQL Database Azure SQL Managed Instance Database SQL di Microsoft Fabric
Tabel eksternal Yes Yes Yes Yes Yes Yes
OPENROWSET (BULK) Ya 1 Yes Yes Yes Yes Yes
CETAS (ekspor) No Yes Yes No Yes No
File CSV atau terbatas Ya 2 Yes Yes Yes Yes Yes
File Parquet No Yes Yes Yes Yes Yes
Tabel Delta Lake No Yes Yes No No No
Menyambungkan ke SQL Server lain Yes Yes Yes No No No
Menyambungkan ke Azure SQL Database atau Azure SQL Managed Instance Ya 3 Ya 3 Ya 3 No No No
Menyambungkan ke Oracle / Teradata / MongoDB Yes Yes Yes No No No
Menyambungkan ke Azure Blob Storage Yes Yes Yes Yes Yes No
Menyambungkan ke ADLS Gen2 No Yes Yes Yes Yes No
Menyambungkan ke penyimpanan yang kompatibel dengan S3 No Yes Yes No No No
Sambungkan ke OneLake (Fabric) No No No No No Yes
Komputasi tumpukan Yes Yes Yes No No No
Autentikasi Identitas Terkelola No No Ya 4 Yes Yes No

1 SQL Server 2019 (15.x) mendukung jalur file lokal dan jaringan. Di SQL Server 2022 (16.x) dan versi yang lebih baru, OPENROWSET(BULK...) juga mendukung pembacaan dari penyimpanan cloud dengan FORMAT = 'PARQUET', FORMAT = DELTA, dan FORMAT = 'CSV'.

2 dukungan CSV di SQL Server 2019 (15.x) diperlukan Hadoop. Di SQL Server 2022 (16.x) dan versi yang lebih baru, CSV didukung secara asli tanpa Hadoop.

3 Menggunakan konektor SQL Server (sqlserver://). Kredensial cakupan database menargetkan titik akhir Azure SQL, langkah yang sama seperti menyambungkan ke SQL Server lain.

4 Autentikasi Identitas Terkelola didukung untuk menyambungkan ke Azure Blob Storage (ABS) dan ADLS Gen2. Ini memerlukan SQL Server yang diaktifkan Azure Arc atau SQL Server di Komputer Virtual Azure untuk SQL Server lokal. Ini tersedia secara asli di Azure SQL Database dan Azure SQL Managed Instance.

Nota

Mulai SQL Server 2025 (17.x), mengkueri file data (CSV, Parquet, dan Delta) di Penyimpanan Azure Blob, ADLS Gen2, atau penyimpanan yang kompatibel dengan S3 adalah kemampuan mesin asli dan tidak lagi memerlukan penginstalan atau menjalankan layanan PolyBase. Konektor RDBMS (SQL Server, Oracle, Teradata, MongoDB, ODBC) masih memerlukan layanan PolyBase untuk diinstal dan dijalankan. SQL Server 2025 (17.x) juga menambahkan dukungan Linux untuk konektor ini, yang sebelumnya hanya tersedia di Windows.

Kueri data eksternal

Sebelum Anda memilih skenario tertentu, pahami tiga cara untuk mengkueri data eksternal:

Pendekatan Sintaksis Gunakan ketika diperlukan Authentication PolyBase diperlukan
Kueri ad hoc OLE DB OPENROWSET(provider, connection, query) Anda menginginkan kueri satu kali cepat tanpa objek persisten, atau memerlukan autentikasi ID Microsoft Entra Autentikasi SQL, autentikasi Windows, ID Microsoft Entra (MSOLEDBSQL) No
File kueri ad hoc OPENROWSET(BULK ...) Anda ingin menjelajahi data file dengan cepat atau menguji skema sebelum membuat tabel Token SAS, kunci akses, Identitas Terkelola, ID Microsoft Entra Ya untuk Azure SQL Database dan Azure SQL Managed Instance

Tidak untuk instans SQL Server
Konektor data yang berkelanjutan CREATE EXTERNAL TABLE dengan sqlserver://, oracle://, teradata://, dll. Anda memerlukan akses yang berulang secara otomatis, tata kelola, statistik, dan komputasi terdorong untuk produksi Autentikasi SQL saja Yes

Layanan PolyBase diperlukan untuk akses file cloud di SQL Server 2019 (15.x) dan SQL Server 2022 (16.x). SQL Server 2025 (17.x) dan versi yang lebih baru memiliki dukungan asli untuk CSV, Parquet, dan Delta tanpa PolyBase.

Panduan keputusan

Skenario Recommendation
Saya memerlukan autentikasi ID Microsoft Entra untuk SQL jarak jauh, atau ingin menghindari layanan PolyBase Gunakan OPENROWSET(MSOLEDBSQL, ...) (ad hoc, tidak ada objek persisten)
Saya memerlukan tabel persisten, statistik, atau komputasi pushdown ke basis data yang berjauhan. Gunakan CREATE EXTERNAL TABLE dengan konektor PolyBase (sqlserver://, , oracle://, teradata://mongodb://, odbc://). OPENROWSET tidak mendukung konektor
Saya menjelajahi file baru atau menguji skema Gunakan OPENROWSET(BULK ...) (perulangan cepat, tidak ada objek persisten)
Saya memasukkan data file ke dalam tabel dengan transformasi Gunakan INSERT ... SELECT dari OPENROWSET(BULK ...)
Saya memerlukan tata kelola atau akses bersama untuk banyak pengguna atau aplikasi Gunakan CREATE EXTERNAL TABLE agar izin dan metadata terpusat
Saya bekerja di database SQL di Fabric Gunakan OPENROWSET(BULK ...) untuk kueri Ad Hoc OneLake atau tabel eksternal untuk akses yang dapat digunakan kembali; untuk penyimpanan eksternal, gunakan pintasan OneLake

Pilih skenario Anda

Sekarang setelah Anda memahami tiga pendekatan, gunakan salah satu panduan berikut untuk mengimplementasikan kasus penggunaan spesifik Anda.

Berkas kueri (Parquet, CSV, atau Delta)

Jika data Anda berada dalam file Parquet, CSV, atau Delta di Azure Blob Storage, ADLS Gen2, penyimpanan yang kompatibel dengan S3, atau OneLake, ikuti salah satu panduan berikut:

Skenario Panduan yang direkomendasikan Platforms
Kueri ad hoc cepat pada file Parquet atau CSV Gunakan OPENROWSET. Tidak diperlukan tabel eksternal SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, Azure SQL Managed Instance, database SQL di Fabric
Kueri berulang pada file Parquet dengan skema persisten Membuat tabel eksternal melalui Parquet SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, Azure SQL Managed Instance, database SQL di Fabric
Mengkueri file CSV dengan tabel eksternal Membuat tabel eksternal dengan format file untuk teks yang dibatasi SQL Server 2019 (15.x) dan versi yang lebih baru, Azure SQL Database, Azure SQL Managed Instance, database SQL di Fabric
Menanyakan tabel Delta Lake Membuat tabel eksternal dengan FILE_FORMAT = DeltaLakeFileFormat SQL Server 2022 (16.x) dan versi yang lebih baru
Mengekspor hasil kueri ke file Parquet atau CSV (CETAS) Gunakan CREATE EXTERNAL TABLE AS SELECT SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Managed Instance

Anda juga dapat mengikuti salah satu tutorial langkah demi langkah ini:

Tutorial Deskripsi
Mulai menggunakan PolyBase di SQL Server 2022 OPENROWSET Mencakup dengan Parquet dan CSV, tabel eksternal, dan navigasi folder.
Memvirtualisasi file parquet dalam penyimpanan objek yang kompatibel dengan S3 menggunakan PolyBase Tutorial untuk SQL Server 2022 (16.x) dan versi yang lebih baru.
Virtualisasi file CSV dengan PolyBase Tutorial untuk SQL Server 2022 (16.x) dan versi yang lebih baru.
Virtualisasi tabel delta dengan PolyBase Tutorial untuk SQL Server 2022 (16.x) dan versi yang lebih baru.
Virtualisasi data dengan Azure SQL Database (Pratinjau) Panduan Azure SQL Database untuk Parquet dan CSV.
Virtualisasi data dengan Azure SQL Managed Instance Panduan Azure SQL Managed Instance untuk Parquet, CSV, dan CETAS.
Virtualisasi data dalam database SQL di Fabric Database SQL dalam panduan Fabric untuk file OneLake.

Menyambungkan ke instans SQL Server lain, Azure SQL Database, atau SQL Managed Instance

Di SQL Server 2019 (15.x) dan versi yang lebih baru, PolyBase dapat mengkueri tabel di instans SQL Server lain, Azure SQL Database, atau Azure SQL Managed Instance, tanpa menggunakan server tertaut.

Penting

Konektor sqlserver:// tidak didukung dalam database SQL di Fabric. Konektor PolyBase RDBMS menggunakan autentikasi SQL melalui CREATE DATABASE SCOPED CREDENTIAL dan tidak mendukung ID Microsoft Entra, Identitas Terkelola, atau autentikasi perwakilan layanan. Karena database SQL di Fabric memerlukan autentikasi Microsoft Entra, Anda tidak dapat menyambungkannya menggunakan PolyBase.

Step Apa yang harus dilakukan
1. Instal PolyBase Menginstal PolyBase di Windows atau Menginstal PolyBase di Linux
2. Buat kredensial CREATE DATABASE SCOPED CREDENTIAL dengan target masuk
3. Buat sumber data eksternal CREATE EXTERNAL DATA SOURCE ... WITH (LOCATION = 'sqlserver://<server>')
4. Buat tabel eksternal CREATE EXTERNAL TABLE ... WITH (LOCATION = '<db>.<schema>.<table>')
5. Kueri SELECT * FROM <external_table>

Petunjuk / Saran

Konektor SQL Server (sqlserver://) juga berfungsi untuk Azure SQL Database dan Azure SQL Managed Instance. Gunakan langkah yang sama, dan atur LOCATION ke titik akhir Azure SQL (misalnya, sqlserver://myserver.database.windows.net).

Untuk panduan terperinci, lihat Mengonfigurasi PolyBase untuk mengakses data eksternal di SQL Server.

Menyambungkan ke Oracle, Teradata, atau MongoDB

SQL Server 2019 (15.x) dan versi yang lebih baru dapat mengkueri Oracle, Teradata, MongoDB, dan Cosmos DB melalui konektor PolyBase ODBC.

Sumber data Panduan Persyaratan
Oracle Mengonfigurasi PolyBase untuk mengakses data eksternal di Oracle SQL Server 2019 (15.x) dan versi yang lebih baru, driver klien Oracle
Teradata Mengonfigurasi PolyBase untuk mengakses data eksternal di Teradata SQL Server 2019 (15.x) dan versi yang lebih baru, driver ODBC Teradata
MongoDB / Cosmos DB Mengonfigurasi PolyBase untuk mengakses data eksternal di MongoDB SQL Server 2019 (15.x) dan versi yang lebih baru, driver ODBC MongoDB
Sumber data ODBC apa pun Mengonfigurasi PolyBase untuk mengakses data eksternal dengan jenis generik ODBC SQL Server 2019 (15.x) dan versi yang lebih baru (Windows)

(Linux dimulai dengan SQL Server 2025 (17.x))

Menyambungkan ke Azure Blob Storage atau ADLS Gen2

Platform SQL Opsi autentikasi Panduan
SQL Server 2022 (16.x) dan versi yang lebih baru Token SAS, kunci akses, Identitas Terkelola (dimulai dengan SQL Server 2025 (17.x)) Mengonfigurasi PolyBase untuk mengakses data eksternal di Azure Blob Storage
SQL Server 2019 (15.x) Kunci akses (melalui konektor Hadoop) Mengonfigurasi PolyBase untuk mengakses data eksternal di Azure Blob Storage
Azure SQL Database Token SAS, Identitas Terkelola, Microsoft Entra pass-through Virtualisasi data dengan Azure SQL Database (Pratinjau)
Azure SQL Managed Instance Token SAS, Identitas Terkelola Virtualisasi data dengan Azure SQL Managed Instance

Di SQL Server 2022 (16.x), awalan URI berubah. Saat bermigrasi dari SQL Server 2019 (15.x) atau versi yang lebih lama:

  • Azure Blob Storage: Ubah wasb[s]:// menjadi abs://
  • ADLS Gen2: Ubah abfs[s]:// menjadi adls://

Untuk informasi selengkapnya, lihat Mengonfigurasi PolyBase untuk mengakses data eksternal di Azure Blob Storage.

Menyambungkan ke penyimpanan objek yang kompatibel dengan S3

SQL Server 2022 (16.x) dan versi yang lebih baru mendukung penyimpanan yang kompatibel dengan S3, seperti Amazon S3, MinIO, dan Ceph.

Untuk informasi selengkapnya, lihat Mengonfigurasi PolyBase untuk mengakses data eksternal di penyimpanan objek yang kompatibel dengan S3.

Mengekspor data dengan CREATE EXTERNAL TABLE AS SELECT (CETAS)

CETAS mengekspor hasil kueri ke file eksternal (Parquet atau CSV) di Penyimpanan Azure Blob, ADLS Gen2, atau penyimpanan yang kompatibel dengan S3.

Platform SQL Dukungan Mengekspor format Catatan
SQL Server 2022 (16.x) dan versi yang lebih baru Yes Parquet, CSV Memerlukan konfigurasi Server: izinkan ekspor Polybase
Azure SQL Managed Instance Yes Parquet, CSV Dinonaktifkan secara default
Azure SQL Database No Tidak Tidak tersedia
Basis data SQL dalam Fabric No Tidak Tidak tersedia

Untuk referensi Transact-SQL, lihat CREATE EXTERNAL TABLE AS SELECT (CETAS).

Contoh Penggunaan Cepat

Contoh 1: Kueri ad hoc pada file Parquet (OPENROWSET)

Tidak diperlukan tabel eksternal. Berfungsi pada SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, Azure SQL Managed Instance, dan database SQL di Fabric.

SELECT TOP 10 *
FROM OPENROWSET (
    BULK 'abs://mycontainer@mystorageaccount.blob.core.windows.net/data/sales/*.parquet',
    FORMAT = 'PARQUET'
) AS [result];

Contoh 2: Tabel eksternal melalui CSV di Azure Blob Storage

Contoh ini berfungsi pada semua platform SQL yang mendukung PolyBase.

  • Langkah 1: Buat kunci master database (DMK). Langkah ini diperlukan karena kredensial menyimpan rahasia token SAS. Namun, Anda dapat melakukan langkah ini jika Anda menggunakan Identitas Terkelola atau autentikasi Microsoft Entra.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong_password>';

  • Langkah 2: Buat kredensial dengan token SAS. Hilangkan bagian depan ?.

    CREATE DATABASE SCOPED CREDENTIAL MyStorageCred
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '<your_SAS_token>'; -- omit the leading '?'

  • Langkah 3: Buat sumber data eksternal.

    CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'abs://mycontainer@mystorageaccount.blob.core.windows.net',
    CREDENTIAL = MyStorageCred
);

  • Langkah 4: Buat format file untuk CSV.

    CREATE EXTERNAL FILE FORMAT CsvFormat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2
    )
);

  • Langkah 5: Buat tabel eksternal.

    CREATE EXTERNAL TABLE dbo.SalesExternal
(
    OrderId INT,
    OrderDate DATE,
    Amount DECIMAL (18, 2),
    Customer NVARCHAR (100)
)
WITH (
    DATA_SOURCE = MyAzureStorage,
    LOCATION = '/data/sales/',
    FILE_FORMAT = CsvFormat
);

  • Langkah 6: Mengkueri tabel eksternal.

    SELECT *
FROM dbo.SalesExternal
WHERE OrderDate >= '2025-01-01';

Contoh 3: Mengkueri tabel di SQL Server lain

Contoh ini berfungsi pada SQL Server 2019 (15.x) dan versi yang lebih baru.

  • Langkah 1: Buat kunci master database (diperlukan karena kredensial menyimpan kata sandi).

    CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '<strong_password>';

  • Langkah 2: Buat kredensial untuk instans SQL Server jarak jauh.

    CREATE DATABASE SCOPED CREDENTIAL RemoteSqlCred
WITH IDENTITY = 'remote_user',
     SECRET = '<password>';

  • Langkah 3: Buat sumber data eksternal.

    CREATE EXTERNAL DATA SOURCE RemoteSqlServer
WITH (
    LOCATION = 'sqlserver://remote-server.contoso.com',
    PUSHDOWN = ON,
    CREDENTIAL = RemoteSqlCred
);

  • Langkah 4: Buat tabel eksternal (nama tiga bagian di LOCATION).

    CREATE EXTERNAL TABLE dbo.RemoteCustomers
(
    CustomerId INT,
    CustomerName NVARCHAR (200)
        COLLATE SQL_Latin1_General_CP1_CI_AS
)
WITH (
    DATA_SOURCE = RemoteSqlServer,
    LOCATION = 'SalesDB.dbo.Customers'
);

  • Langkah 5: Kueri di seluruh server.

    SELECT c.CustomerName,
       s.Amount
FROM dbo.RemoteCustomers AS c
     INNER JOIN dbo.LocalSales AS s
         ON c.CustomerId = s.CustomerId;

Contoh 4: Mengekspor hasil ke Parquet dengan CETAS

Berfungsi pada SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Managed Instance.

  • Langkah 1: Aktifkan CETAS (hanya SQL Server).

    EXECUTE sp_configure 'allow polybase export', 1;
RECONFIGURE;

  • Langkah 2: Buat kredensial dan sumber data (gunakan kembali dari contoh sebelumnya).

  • Langkah 3: Buat format file untuk ekspor Parquet.

    CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET
);

  • Langkah 4: Ekspor hasil kueri.

    CREATE EXTERNAL TABLE dbo.Sales2025Export
WITH (
    DATA_SOURCE = MyAzureStorage,
    LOCATION = '/exports/sales_2025.parquet',
    FILE_FORMAT = ParquetFormat
) AS
SELECT *
FROM Sales.Orders
WHERE OrderDate >= '2025-01-01';

Blok penyusun T-SQL untuk PolyBase

Sebelum menerapkan skenario apa pun, pahami objek T-SQL inti yang digunakan PolyBase dan bagaimana mereka cocok bersama:

Diagram memperlihatkan polyBase Transact-SQL objek dan hubungannya.

Untuk informasi tentang pernyataan T-SQL ini, lihat:

Untuk referensi Transact-SQL lengkap untuk semua objek, lihat referensi Transact-SQL PolyBase.

Penting

Periksa pemetaan jenis data untuk format file eksternal Anda. Saat Anda membuat format file eksternal, atau file kueri menggunakan OPENROWSET, PolyBase secara otomatis memetakan jenis data sumber (Parquet, CSV, Delta, Oracle, Teradata, MongoDB) ke jenis data SQL Server. Jenis yang tidak cocok dapat menyebabkan pemotongan diam-diam, kehilangan presisi, atau kesalahan kueri. Misalnya, sebuah Parquet DECIMAL(38,18) dipetakan menjadi DECIMAL(18,0). Tinjau tabel pemetaan sebelum Anda menentukan kolom tabel eksternal atau WITH klausa. Untuk referensi lengkapnya, lihat Pemetaan jenis dengan PolyBase.

Kapan CREATE MASTER KEY diperlukan?

Kunci master database (DMK) dibuat menggunakan CREATE MASTER KEY sintaksis. DMK mengenkripsi rahasia yang disimpan di dalam kredensial yang dicakup oleh database. Ini diperlukan hanya ketika kredensial berisi nilai rahasia, yaitu, ketika menyimpan kata sandi, token, atau kunci akses.

  • DMK diperlukan (kredensial menyimpan rahasia):

    Jenis autentikasi IDENTITY nilai Memiliki rahasia DMK
    token SAS 'SHARED ACCESS SIGNATURE' Yes Required
    Kunci akses S3 'S3 ACCESS KEY' Yes Required
    Login SQL / autentikasi dasar '<username>' Yes Required
    Kunci akses akun penyimpanan '<storage_account_name>' Yes Required

  • DMK tidak diperlukan (tidak ada rahasia yang disimpan):

    Jenis autentikasi IDENTITY nilai Memiliki rahasia DMK
    Identitas yang Dikelola 'Managed Identity' No Tidak diperlukan
    Microsoft Entra ID 'User Identity' atau 'Managed Identity' No Tidak diperlukan

Petunjuk / Saran

Jika tidak ada rahasia dalam pernyataan CREATE DATABASE SCOPED CREDENTIAL Anda, Anda tidak memerlukan DMK. Autentikasi Identitas Terkelola dan ID Microsoft Entra mendelegasikan kepercayaan ke platform. Database tidak menyimpan kata sandi atau token.

contoh :

Dalam contoh kueri ini, DMK diperlukan (Kredensial menyimpan token SAS).

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong_password>';

CREATE DATABASE SCOPED CREDENTIAL SasCred
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '<your_SAS_token>';

Dalam contoh kueri ini, DMK tidak diperlukan (Identitas Terkelola, tanpa rahasia).

CREATE DATABASE SCOPED CREDENTIAL ManagedIdentityCred
WITH IDENTITY = 'Managed Identity';

Dalam contoh kueri ini, DMK tidak diperlukan (Microsoft Entra pass-through, tanpa rahasia).

CREATE DATABASE SCOPED CREDENTIAL EntraIdCred
WITH IDENTITY = 'User Identity';

Akses data jarak jauh dengan OPENROWSET dan tabel eksternal

SQL Server menawarkan tiga pendekatan berbeda untuk mengkueri data jarak jauh. Anda dapat memilih pendekatan yang tepat saat memahami perbedaan sintaks, autentikasi, dan arsitektur.

Pendekatan Sintaksis Menyambungkan ke Authentication Layanan PolyBase Platforms
Kueri OLE DB OPENROWSET(provider, connection, query) Sumber OLE DB apa pun melalui MSOLEDBSQL, SQLOLEDB, atau penyedia lainnya Autentikasi SQL, autentikasi Windows, ID Microsoft Entra (MSOLEDBSQL) No SQL Server (semua versi yang didukung)
Kueri file OPENROWSET(BULK ...) File di disk, jaringan, atau cloud lokal (Azure Blob, ADLS, S3, OneLake) Token SAS, kunci akses, Identitas Terkelola, ID Microsoft Entra Ya untuk cloud*; Tidak untuk lokal SQL Server 2005; SQL Server 2022 (16.x) dan versi yang lebih baru (cloud); Azure SQL
Konektor PolyBase CREATE EXTERNAL TABLE dengan CREATE EXTERNAL DATA SOURCE menggunakan sqlserver://, oracle://, teradata://, mongodb://, odbc:// Sumber SQL Server Jarak Jauh, Oracle, Teradata, MongoDB, ODBC Autentikasi SQL saja Yes SQL Server 2019 (15.x) dan versi yang lebih baru (Windows); SQL Server 2025 (17.x) dan versi yang lebih baru (Linux)

Layanan PolyBase diperlukan untuk akses file cloud di SQL Server 2019 (15.x) dan SQL Server 2022 (16.x). SQL Server 2025 (17.x) dan versi yang lebih baru memiliki dukungan file cloud asli dan tidak lagi memerlukan PolyBase untuk CSV, Parquet, atau Delta.

Kapan menggunakan setiap pendekatan

Gunakan OLE DB OPENROWSET untuk:

  • Kueri ad hoc satu kali yang cepat tanpa membuat objek persisten
  • ID Microsoft Entra atau autentikasi Identitas Terkelola (melalui MSOLEDBSQL)
  • Menghindari dependensi layanan PolyBase
  • Menyambungkan ke sumber data apa pun dengan penyedia OLE DB

Gunakan File OPENROWSET(BULK) untuk:

  • Eksplorasi file ad hoc dan penemuan skema
  • Transformasi dan pratinjau cepat sebelum berkomitmen pada definisi tabel
  • Transformasi kolom fleksibel sebaris (pengubahan jenis data, pemfilteran, kolom terhitung)
  • Data yang tidak sering berubah, dan tidak memerlukan metadata persisten

Gunakan konektor PolyBase dengan CREATE EXTERNAL TABLE untuk:

  • Definisi tabel yang persisten dan dapat digunakan kembali yang diakses oleh beberapa pengguna atau aplikasi
  • Beban kerja produksi yang memerlukan statistik dan pengoptimalan rencana kueri
  • Komputasi pushdown ke sumber daya jarak jauh (filter didorong ke Oracle, SQL Server, dll.)
  • Tata kelola dan keamanan bersama (setelah dibuat, pengguna hanya memerlukan SELECT izin)
  • Ketika Anda memiliki autentikasi SQL yang tersedia untuk sumber jarak jauh

OPENROWSET (OLE DB) - kueri jarak jauh ad hoc (tidak diperlukan layanan PolyBase)

Bentuk OPENROWSET OLE DB terhubung ke sumber data jarak jauh melalui penyedia OLE DB, menjalankan kueri pass-through, dan mengembalikan hasilnya sebagai set baris. Ini adalah alternatif ad hoc sekali pakai untuk server tertaut. Tidak ada metadata persisten yang dibuat. Sintaks ini tidak memerlukan layanan PolyBase, dan tidak mendukung file cloud atau sumber data eksternal.

Contoh kueri ini tersambung ke SQL Server jarak jauh melalui OLE DB (bukan PolyBase).

SELECT *
FROM OPENROWSET (
    'MSOLEDBSQL',
    'Server=remote-server;Database=AdventureWorks;Trusted_Connection=yes;',
    'SELECT TOP 10 * FROM AdventureWorks.Sales.SalesOrderHeader'
);

OPENROWSET(BULK) - kueri berbasis file (PolyBase)

Bentuk BULK dari OPENROWSET membaca data langsung dari berkas-berkas. Pada SQL Server 2019 (15.x) dan versi yang lebih lama, SQL Server membaca dari jalur file lokal atau UNC dan memerlukan file format. Di SQL Server 2022 (16.x) dan versi yang lebih baru, Anda dapat membaca dari penyimpanan cloud menggunakan parameter DATA_SOURCE dan FORMAT. Pendekatan ini adalah versi terintegrasi PolyBase yang digunakan untuk virtualisasi data.

Dalam konteks PolyBase dan virtualisasi data, ketika panduan ini mengacu pada OPENROWSET, itu berarti sintaks OPENROWSET(BULK ...) dengan klausa FORMAT untuk menjalankan kueri pada file eksternal.

contoh :

Contoh kueri ini membaca file Parquet dari Azure Blob Storage (SQL Server 2022 dan versi yang lebih baru).

SELECT TOP 10 *
FROM OPENROWSET (
    BULK 'data/sales/*.parquet',
    DATA_SOURCE = 'MyAzureStorage',
    FORMAT = 'PARQUET'
) AS [result];

Contoh kueri ini membaca file Parquet dengan jalur sebaris (Azure SQL Database, Azure SQL Managed Instance).

SELECT TOP 10 *
FROM OPENROWSET (
    BULK 'abs://mycontainer@mystorageaccount.blob.core.windows.net/data/sales/*.parquet',
    FORMAT = 'PARQUET'
) AS [result];

Kapan menggunakan OPENROWSET vs. tabel eksternal

Baik OPENROWSET(BULK ...) maupun tabel eksternal memungkinkan Anda untuk mengkueri data eksternal dengan T-SQL, namun keduanya dirancang untuk kasus penggunaan yang berbeda. Tabel berikut ini meringkas perbedaan utama untuk membantu Anda memutuskan pendekatan mana yang sesuai dengan skenario Anda.

Kemampuan OPENROWSET(BULK ...) Tabel eksternal
Purpose Eksplorasi langsung dan kueri sekali pakai Definisi tabel yang persisten dan dapat digunakan kembali
Metadata disimpan dalam database Tidak. Tidak ada yang disimpan setelah kueri dijalankan Ya. Definisi tabel, sumber data, dan format file disimpan sebagai objek database
Definisi skema Disimpulkan secara otomatis dari file (Parquet) atau ditentukan secara langsung dengan klausa WITH Ditentukan secara eksplisit dalam CREATE EXTERNAL TABLE pernyataan
Permissions ADMINISTER BULK OPERATIONS Memerlukan ADMINISTER DATABASE BULK OPERATIONS atau ADMINISTER BULK OPERATIONS Setelah dibuat, izin standar SELECT pada tabel sudah cukup
Kolom terhitung Ya. Tambahkan ekspresi dan kolom komputasi dalam SELECT daftar; fungsi metadata seperti filename() dan filepath() hanya tersedia di sini. Tidak. Daftar kolom tetap; melakukan transformasi dalam tampilan atau dalam kueri yang membaca tabel eksternal
Statistik Azure SQL: statistik kolom tunggal manual melalui sys.sp_create_openrowset_statistics; SQL Server 2022 (16.x) dan versi yang lebih baru: buat statistik secara otomatis pada predikat (tidak ada statistik manual di SQL Server). Lihat Statistik manual OPENROWSET. Dukungan penuh CREATE STATISTICS pada semua platform, ditambah pembuatan otomatis di SQL Server 2022 (16.x) dan versi yang lebih baru. Lihat Membuat statistik manual tabel eksternal.
Pushdown Dukungan terbatas. Mesin mungkin mendorong filter ke bawah ke pemindaian file tetapi tidak ada pushdown ke sumber RDBMS jarak jauh Ya. Mendukung komputasi pushdown untuk konektor RDBMS (SQL Server, Oracle, Teradata, MongoDB)
Pilihan terbaik untuk Eksplorasi data, penemuan skema, kueri prototipe, pemuatan data satu kali, transformasi fleksibel Beban kerja produksi, kueri berulang, akses bersama untuk semua pengguna, dasbor, dan pelaporan

Gunakan OPENROWSET saat Anda membutuhkan fleksibilitas

Gunakan OPENROWSET untuk menjelajahi file, menguji skema yang berbeda, atau menambahkan kolom dan transformasi komputasi tanpa membuat objek persisten apa pun. Misalnya, Anda dapat mengekstrak jalur file sebagai kolom, mentransmisikan jenis data sebaris, atau memfilter ekspresi komputasi dalam satu kueri.

Contoh kueri ini mencakup kolom dan transformasi komputasi:

SELECT result.filename() AS [FileName],
       result.filepath(1) AS [Year],
       result.filepath(2) AS [Month],
       CAST (OrderDate AS DATE) AS OrderDate,
       Amount,
       OrderDate
FROM OPENROWSET (
    BULK 'abs://mycontainer@mystorageaccount.blob.core.windows.net/data/sales/*/*/*/*.parquet',
    FORMAT = 'PARQUET'
) AS result
WHERE result.filepath(1) = '2025';

Petunjuk / Saran

Fungsi filepath() dan filename() tersedia di Azure SQL Database, Azure SQL Managed Instance, dan SQL Server 2022 (16.x) dan versi yang lebih baru. Mereka memungkinkan Anda memfilter pada bagian jalur file (eliminasi partisi) dan mengekspos nama file sumber sebagai kolom, yang tidak secara langsung dimungkinkan dengan tabel eksternal.

Menggunakan tabel eksternal saat Anda memerlukan persistensi dan tata kelola

Gunakan tabel eksternal saat beberapa pengguna atau aplikasi perlu mengkueri data eksternal yang sama berulang kali. Anda menentukan skema, sumber data, dan kredensial sekali dan menyimpannya di database. Konsumen hanya memerlukan SELECT izin akses terhadap tabel.

Tabel eksternal juga mendukung statistik, yang digunakan pengoptimal kueri untuk membangun rencana eksekusi yang lebih baik. Anda dapat membuat statistik secara manual atau membiarkan mesin membuatnya secara otomatis (SQL Server 2022 (16.x) dan versi yang lebih baru).

Contoh kueri ini membuat statistik pada tabel eksternal untuk rencana kueri yang lebih baik.

CREATE STATISTICS Stats_OrderDate
ON dbo.SalesExternal(OrderDate)
WITH FULLSCAN;

Untuk informasi selengkapnya tentang statistik untuk kedua pendekatan, lihat Pertimbangan performa PolyBase - Statistik.

SISIPAN BULK vs. OPENROWSET(BULK): Mana yang harus saya gunakan?

Baik BULK INSERT dan OPENROWSET(BULK ...) mengimpor data dari file ke SQL Server dengan menggunakan mesin pemuatan massal yang sama dan mendasar. Namun, mereka berbeda dalam sintaksis, fleksibilitas, dan apa yang dapat Anda lakukan dengan hasilnya. Tabel berikut ini meringkas perbedaan utama:

Nota

BULK INSERT tidak tersedia dalam database SQL di Fabric. Untuk Fabric, gunakan OPENROWSET(BULK ...) terhadap OneLake.

Kemampuan BULK INSERT OPENROWSET(BULK ...)
Tujuan dasar Memuat data dari file langsung ke tabel target Mengembalikan kumpulan baris yang Anda gunakan dalam pernyataan SELECT atau INSERT ... SELECT
Pola penggunaan Pernyataan mandiri: BULK INSERT <table> FROM '<file>' Harus digunakan di dalam kueri: SELECT * FROM OPENROWSET(BULK ...) atau INSERT INTO <table> SELECT * FROM OPENROWSET(BULK ...)
Membutuhkan tabel target? Ya. Selalu menulis langsung ke tabel Tidak. Anda dapat SELECT darinya tanpa menyisipkannya ke tempat mana pun, atau menyisipkannya ke dalam tabel atau tabel sementara.
Transformasi kolom selama pemuatan Dukungan terbatas. Data mengalir dari file ke tabel seperti adanya (pemetaan dikontrol oleh format file atau urutan kolom) Dukungan penuh. Anda dapat menambahkan ekspresi, CASTWHERE filter, JOIN tabel lain, dan kolom komputasi di sekitarnyaSELECT
Petunjuk tabel Klausul WITH ini mencakup dukungan untuk BATCHSIZE, , CHECK_CONSTRAINTSFIRE_TRIGGERS, KEEPIDENTITY, KEEPNULLS, TABLOCK, dan lainnya Mendukung petunjuk tabel melalui INSERT ... SELECT * FROM OPENROWSET(BULK ...) WITH (TABLOCK, IGNORE_CONSTRAINTS, ...) sintaks
Impor nilai tunggal objek besar (LOB) Tidak didukung Ya. SINGLE_BLOBMendukung , SINGLE_CLOB, SINGLE_NCLOB untuk mengimpor seluruh file sebagai satu nilai varbinary(max), varchar(max), atau nvarchar(max)
Format file Ya. Didukung melalui (XML dan non-XML) Ya. Didukung (XML dan non-XML)
Akses file cloud (Azure Blob Storage, ADLS Gen2, S3) Ya. Didukung melalui DATA_SOURCE parameter (SQL Server 2017 (14.x) dan versi yang lebih baru, Azure SQL) Ya. Didukung melalui parameter DATA_SOURCE atau URL di dalam baris dengan klausul FORMAT (SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL)
File Parquet atau Delta Tidak didukung. Hanya teks CSV/dibatasi Ya. Didukung dengan FORMAT = 'PARQUET' atau FORMAT = 'DELTA' (SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL)
Izin diperlukan ADMINISTER BULK OPERATIONS atau ADMINISTER DATABASE BULK OPERATIONS, plus INSERT pada tabel target ADMINISTER BULK OPERATIONS atau ADMINISTER DATABASE BULK OPERATIONS
Pengelogan minimal Ya. Didukung di bawah model pemulihan sederhana atau dicatat massal dengan TABLOCK Ya. Didukung saat digunakan dengan INSERT ... SELECT dan TABLOCK

Kapan memilih SISIPAN MASSAL

Gunakan BULK INSERT saat Anda memiliki beban file-ke-tabel yang mudah dan tidak perlu mengubah, memfilter, atau menggabungkan data selama impor. Ini menggunakan sintaks yang lebih sederhana untuk CSV atau file berbatas lainnya:

Contoh kueri ini memuat file CSV dari Azure Blob Storage langsung ke dalam tabel.

BULK INSERT Sales.Invoices
FROM 'invoices/inv-2025-01.csv'
WITH (
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);

Contoh kueri ini memuat file lokal dengan file format untuk pemetaan kolom.

BULK INSERT dbo.Products
FROM 'C:\Data\products.csv'
WITH (
    FORMATFILE = 'C:\Data\products.fmt',
    FIRSTROW = 2,
    TABLOCK
);

Kapan memilih OPENROWSET(BULK)

Gunakan OPENROWSET(BULK ...) saat Anda memerlukan satu atau beberapa kondisi berikut:

  • Kueri atau pratinjau data file tanpa membuat tabel terlebih dahulu.
  • Mengubah, memfilter, atau menggabungkan data selama impor.
  • Muat file Parquet atau Delta (hanya OPENROWSET mendukung format ini).
  • Impor seluruh file sebagai nilai LOB tunggal (SINGLE_BLOB, , SINGLE_CLOBSINGLE_NCLOB).

Contoh kueri ini mempratinjau file CSV dari Azure Blob Storage tanpa menyisipkan data di mana saja.

SELECT TOP 10 *
FROM OPENROWSET (
    BULK 'invoices/inv-2025-01.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDTERMINATOR = ','
) AS src;

Contoh kueri ini menyisipkan data dengan transformasi dan pemfilteran.

INSERT INTO Sales.Invoices (InvoiceDate, Amount, Customer)
SELECT CAST (InvoiceDate AS DATE),
       Amount * 1.1, -- Apply a 10% markup
       UPPER(Customer)
FROM OPENROWSET (
    BULK 'invoices/inv-2025-01.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FIRSTROW = 2
) WITH (
    InvoiceDate VARCHAR (10),
    Amount DECIMAL (18, 2),
    Customer VARCHAR (100)
) AS src
WHERE Amount IS NOT NULL;

Contoh kueri ini memuat file Parquet (tidak dimungkinkan dengan BULK INSERT).

INSERT INTO Sales.Invoices
SELECT *
FROM OPENROWSET (
    BULK 'data/invoices/*.parquet',
    DATA_SOURCE = 'MyAzureStorage',
    FORMAT = 'PARQUET') AS src;

Contoh kueri ini mengimpor seluruh file XML sebagai nilai varbinary(max) tunggal.

INSERT INTO dbo.XmlDocuments (DocContent)
SELECT BulkColumn
FROM OPENROWSET (
    BULK 'C:\Data\catalog.xml',
    SINGLE_BLOB
) AS x;

Petunjuk / Saran

Salah satu pendekatannya adalah memulai dengan OPENROWSET(BULK ...) di SELECT untuk menjelajahi dan memvalidasi data file, lalu beralih ke BULK INSERT untuk beban produksi akhir jika Anda tidak memerlukan transformasi. Jika Anda memerlukan dukungan Parquet, Delta, atau pemfilteran sebaris, gunakan OPENROWSET.

Untuk informasi selengkapnya, lihat panduan terkait berikut ini:

Fungsi metadata yang berguna

Saat Anda mengkueri file eksternal dengan OPENROWSET atau tabel eksternal, Anda dapat menggunakan beberapa fungsi dan prosedur bawaan untuk memeriksa metadata file, menemukan skema, dan menerapkan kueri yang sadar partisi.

filepath() dan filename()

Fungsi filepath() dan filename() mengembalikan bagian dari jalur file atau nama file untuk setiap baris dalam tataan hasil. Mereka sangat berguna untuk:

  • Penghapusan partisi: Filter pada segmen folder (misalnya, partisi tahun/bulan/hari) sehingga mesin hanya membaca file yang cocok alih-alih memindai semuanya.

  • Mengekspos metadata sumber: Sertakan nama atau jalur file asal sebagai kolom dalam hasil kueri, yang berguna untuk audit atau penelusuran kesalahan.

Fungsi Pengembalian Barang Example
filename() Nama file (termasuk ekstensi) file sumber untuk setiap baris sales_2025_01.parquet
filepath(N) Segment folder ke-N dari wildcard (*) di jalur BULK, di mana N dimulai dari 1 Untuk jalur sales/2025/01/*.parquet, filepath(1) mengembalikan 2025, filepath(2) mengembalikan 01

Berlaku untuk: Azure SQL Database, Azure SQL Managed Instance, SQL Server 2022 (16.x) dan versi yang lebih baru, database SQL di Fabric.

Contoh kueri ini menggunakan filepath() untuk penghapusan partisi dan filename() untuk mengidentifikasi file sumber. Ini hanya membaca file di dalam folder /2025/, dan hanya membaca file di dalam subfolder /06/.

SELECT result.filename() AS SourceFile,
       result.filepath(1) AS [Year],
       result.filepath(2) AS [Month],
       *
FROM OPENROWSET (
    BULK 'abs://mycontainer@mystorageaccount.blob.core.windows.net/data/sales/*/*/*.parquet',
    FORMAT = 'PARQUET'
) AS result
WHERE result.filepath(1) = '2025' 
      AND result.filepath(2) = '06';

Petunjuk / Saran

Letakkan filepath() pemfilteran dalam WHERE klausa daripada dalam subkueri atau CTE. Ketika filter berada dalam WHERE klausul, mesin dapat melakukan eliminasi partisi pada tingkat pemindaian file, yang secara signifikan mengurangi I/O.

sp_describe_first_result_set - cermati tipe kolom pada OPENROWSET

Saat Anda menggunakan OPENROWSET dengan file Parquet, mesin menyimpulkan jenis data kolom secara otomatis (inferensi skema). Jenis yang disimpulkan mungkin lebih besar dari yang diperlukan. Misalnya, kolom karakter sering disimpulkan sebagai varchar(8000) karena metadata Parquet tidak menyertakan panjang maksimum. Pilihan ini dapat menurunkan performa dan mengonsumsi lebih banyak memori.

Gunakan sp_describe_first_result_set untuk memeriksa skema yang disimpulkan sebelum Anda menyelesaikan kueri Anda. Setelah Anda melihat jenis yang disimpulkan, tentukan jenis yang lebih sempit pada bagian WITH untuk meningkatkan kinerja.

  • Langkah 1: Periksa skema yang disimpulkan.

    EXECUTE sp_describe_first_result_set N'
SELECT *
FROM OPENROWSET(
    BULK ''abs://mycontainer@mystorageaccount.blob.core.windows.net/data/sales/*.parquet'',
    FORMAT = ''PARQUET''
) AS result';

    Output menunjukkan nama setiap kolom, jenis data yang disimpulkan, panjang maksimum, presisi, dan skala. Jika Anda melihat varchar(8000) di mana varchar(100) sudah cukup, ambil alih:

  • Langkah 2: Gunakan jenis eksplisit untuk performa yang lebih baik.

    SELECT TOP 100 *
FROM OPENROWSET (
    BULK 'abs://mycontainer@mystorageaccount.blob.core.windows.net/data/sales/*.parquet',
    FORMAT = 'PARQUET'
) WITH (
    OrderId INT,
    OrderDate DATE,
    Amount DECIMAL (18, 2),
    Customer VARCHAR (100) -- much narrower than the inferred varchar(8000)
) AS result;

Inferensi skema hanya berfungsi dengan file Parquet. Untuk file CSV, selalu tentukan definisi kolom baik dalam WITH klausa (untuk OPENROWSET) atau dalam CREATE EXTERNAL TABLE pernyataan. sp_describe_first_result_set adalah prosedur umum untuk SQL Server dan Azure SQL, namun sangat berguna untuk kueri OPENROWSET. Untuk informasi selengkapnya, lihat sp_describe_first_result_set.

Performa, pemecahan masalah, dan praktik terbaik

Setelah Anda menerapkan virtualisasi data, gunakan panduan ini untuk mengoptimalkan performa, mendiagnosis masalah, dan memastikan kesiapan produksi:

Wilayah Artikel Rincian
Kinerja PolyBase Pertimbangan performa di PolyBase untuk SQL Server Statistik, pushdown, paralelisme, dan pengelolaan memori
Komputasi tumpukan Komputasi pushdown di PolyBase Menentukan operasi mana yang mengunggah ke sumber jarak jauh
Cara mengetahui apakah pushdown terjadi Cara untuk mengetahui apakah terjadi dorongan eksternal Rencana kueri dan DMV
Pemecahan masalah Memantau dan memecahkan masalah PolyBase Kesalahan dan resolusi umum
Konektivitas Kerberos Memecahkan masalah konektivitas PolyBase Kerberos
FAQ Pertanyaan yang sering diajukan polyBase
Kesalahan dan solusi Kesalahan PolyBase dan kemungkinan solusi

Konten terkait

  • Last updated on