Bagikan melalui

Membuat dan menggunakan tabel eksternal native menggunakan kumpulan SQL di Azure Synapse Analytics

Di bagian ini, Anda akan mempelajari cara membuat dan menggunakan tabel eksternal asli di kumpulan Synapse SQL. Tabel eksternal native memiliki kinerja yang lebih baik jika dibandingkan tabel eksternal dengan TYPE=HADOOP dalam definisi sumber data eksternal mereka. Ini karena tabel eksternal native menggunakan kode native untuk mengakses data eksternal.

Tabel eksternal berguna jika Anda ingin mengontrol akses ke data eksternal di kumpulan Synapse SQL. Tabel eksternal juga berguna jika Anda ingin menggunakan alat, seperti Power BI, bersama dengan kumpulan Synapse SQL. Tabel eksternal dapat mengakses dua jenis penyimpanan:

  • Penyimpanan publik tempat pengguna mengakses file penyimpanan publik.
  • Penyimpanan terlindungi di mana pengguna mengakses file penyimpanan menggunakan kredensial SAS, identitas Microsoft Entra, atau Identitas Terkelola ruang kerja Synapse.

Catatan

Di kumpulan SQL khusus, Anda hanya dapat menggunakan tabel eksternal asli dengan jenis file Parquet, dan fitur ini ada di pratinjau publik. Jika Anda ingin menggunakan fungsionalitas pembaca Parquet yang tersedia secara umum di kumpulan SQL khusus, atau jika Anda perlu mengakses file CSV atau ORC, gunakan tabel eksternal Hadoop. Tabel eksternal native umumnya tersedia di kumpulan SQL tanpa server. Pelajari selengkapnya tentang perbedaan antara tabel eksternal asli dan Hadoop di Menggunakan tabel eksternal dengan Synapse SQL.

Tabel berikut mencantumkan format data yang didukung:

Format data (Tabel eksternal asli) Kumpulan SQL tanpa server Kumpulan SQL khusus
Lantai kayu parket Ya (Ketersediaan Umum) Ya (pratinjau umum)
CSV Ya Tidak (Atau, gunakan tabel eksternal Hadoop)
delta Ya Tidak
Spark Ya Tidak
Dataverse Ya Tidak
Format data Azure Cosmos DB (JSON, BSON, dan lain-lain) Tidak (Atau, buat tampilan) Tidak

Prasyarat

Langkah pertama Anda adalah membuat database tempat tabel akan dibuat. Database harus memiliki kunci master untuk melindungi kredensial. Untuk informasi selengkapnya tentang ini, lihat MEMBUAT KUNCI MASTER (Transact-SQL). Kemudian buat objek berikut yang digunakan dalam sampel ini:

  • SUMBER DATA EKSTERNAL sqlondemanddemo yang mengacu pada akun penyimpanan demo publik, dan SUMBER DATA EKSTERNAL nyctlc yang mengacu pada akun penyimpanan Azure yang dapat diakses oleh publik di lokasi https://azureopendatastorage.blob.core.windows.net/nyctlc/.

    CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (LOCATION = 'https://fabrictutorialdata.blob.core.windows.net/sampledata/Synapse');
GO
CREATE EXTERNAL DATA SOURCE nyctlc
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
GO
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( location = 'https://fabrictutorialdata.blob.core.windows.net/sampledata/Synapse/delta-lake' );

  • Format file QuotedCSVWithHeaderFormat dan ParquetFormat yang menggambarkan jenis file CSV dan parquet.

    CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
GO

Kueri dalam artikel ini akan dieksekusi pada database sampel Anda dan menggunakan objek-objek ini.

Tabel eksternal di dalam file

Anda dapat membuat tabel eksternal yang mengakses data di akun penyimpanan Azure yang memungkinkan akses ke pengguna dengan beberapa identitas Microsoft Entra atau kunci SAS. Anda dapat membuat tabel eksternal dengan cara yang sama seperti Anda membuat tabel eksternal SQL Server biasa.

Kueri berikut membuat tabel eksternal yang membaca file population.csv dari akun penyimpanan Azure demo SynapseSQL yang dirujuk menggunakan sqlondemanddemo sumber data.

Catatan

Ubah baris pertama dalam kueri, yaitu, [mydbname], sehingga Anda menggunakan database yang Anda buat.

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

Tabel CSV native saat ini hanya tersedia di kumpulan SQL tanpa server.

Tabel eksternal pada sekumpulan file

Anda bisa membuat tabel eksternal yang membaca data dari sekumpulan file yang ditempatkan di penyimpanan Azure:

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

Anda dapat menentukan pola yang harus dipenuhi file agar dapat direferensikan oleh tabel eksternal. Pola ini hanya diperlukan untuk tabel Parquet dan CSV. Jika Anda menggunakan format Delta Lake, Anda perlu menentukan folder akar saja, dan tabel eksternal akan menemukan pola secara otomatis.

Catatan

Tabel dibuat pada struktur folder yang dipartisi, tetapi Anda tidak dapat memanfaatkan fungsi eliminasi partisi tertentu. Jika Anda ingin mendapatkan performa yang lebih baik dengan melewatkan file yang tidak memenuhi beberapa kriteria (seperti tahun atau bulan tertentu dalam hal ini), gunakan tampilan pada data eksternal.

Tabel eksternal pada file yang dapat ditambahkan

File yang dirujuk oleh tabel eksternal tidak boleh diubah saat kueri sedang berjalan. Dalam kueri yang berjalan lama, kumpulan SQL dapat mencoba kembali membaca, membaca bagian file, atau bahkan membaca file beberapa kali. Perubahan konten file akan menyebabkan hasil yang salah. Oleh karena itu, kumpulan SQL menggagalkan kueri jika mendeteksi bahwa waktu modifikasi file apa pun diubah selama eksekusi kueri. Dalam beberapa skenario, Anda mungkin ingin membuat tabel pada file yang terus ditambahkan. Untuk menghindari kegagalan kueri karena file yang terus ditambahkan, Anda dapat menentukan bahwa tabel eksternal harus mengabaikan pembacaan yang berpotensi tidak konsisten menggunakan pengaturan TABLE_OPTIONS.

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

Opsi ALLOW_INCONSISTENT_READS baca menonaktifkan pemeriksaan waktu modifikasi file selama proses kueri dan membaca apa pun yang tersedia dalam file yang ditunjuk oleh tabel eksternal. Dalam file yang dapat ditambahkan, konten yang ada tidak diperbarui, dan hanya baris baru yang ditambahkan. Oleh karena itu, probabilitas hasil yang salah diminimalisir dibandingkan dengan file yang dapat diperbarui. Opsi ini dapat memungkinkan Anda membaca file yang sering ditambahkan tanpa menangani kesalahan.

Opsi ini hanya tersedia di tabel eksternal yang dibuat pada format file CSV.

Catatan

Seperti namanya, pembuat tabel menerima risiko bahwa hasilnya mungkin tidak konsisten. Dalam file yang dapat ditambahkan, Anda mungkin mendapatkan hasil yang salah jika Anda memaksa beberapa kali pembacaan file yang mendasarinya dengan melakukan self-join pada tabel. Di sebagian besar kueri "klasik", tabel eksternal hanya akan mengabaikan beberapa baris yang ditambahkan saat kueri berjalan.

Tabel eksternal Delta Lake

Tabel eksternal dapat dibuat di atas folder Delta Lake. Satu-satunya perbedaan antara tabel eksternal yang dibuat pada satu file atau kumpulan file dan tabel eksternal yang dibuat pada format Delta Lake adalah bahwa dalam tabel eksternal Delta Lake Anda perlu mereferensikan folder yang berisi struktur Delta Lake.

Folder ECDC COVID-19 Delta Lake

Contoh definisi tabel yang dibuat pada folder Delta Lake adalah:

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

Tabel eksternal tidak dapat dibuat pada folder yang dipartisi. Tinjau masalah lain yang diketahui di halaman bantuan mandiri kumpulan SQL tanpa server Synapse.

Tabel Delta di folder yang dipartisi

Tabel eksternal dalam kumpulan SQL tanpa server tidak mendukung partisi pada format Delta Lake. Gunakan tampilan yang dipartisi Delta alih-alih tabel jika Anda telah mempartisi himpunan data Delta Lake.

Penting

Jangan membuat tabel eksternal pada folder Delta Lake yang dipartisi meskipun Anda melihat bahwa tabel tersebut mungkin berfungsi dalam beberapa kasus. Menggunakan fitur yang tidak didukung seperti tabel eksternal pada folder delta yang dipartisi dapat menyebabkan masalah atau ketidakstabilan kumpulan tanpa server. Dukungan Azure tidak akan dapat mengatasi masalah apa pun jika menggunakan tabel pada folder yang dipartisi. Anda akan diminta untuk beralih ke tampilan yang dipartisi Delta dan menulis ulang kode Anda untuk hanya menggunakan fitur yang didukung sebelum melanjutkan penyelesaian masalah.

Menggunakan tabel eksternal

Anda bisa menggunakan tabel eksternal dalam kueri Anda dengan cara yang sama seperti Anda menggunakannya dalam kueri SQL Server.

Kueri berikut menunjukkan ini menggunakan tabel eksternal populasi yang kita buat di bagian sebelumnya. Ini mengembalikan nama negara/wilayah dengan populasi mereka pada tahun 2019 dalam urutan menurun.

Catatan

Ubah baris pertama dalam kueri, yaitu, [mydbname], sehingga Anda menggunakan database yang Anda buat.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

Kinerja kueri ini mungkin berbeda-beda, bergantung pada wilayah. Ruang kerja Anda mungkin tidak ditempatkan di wilayah yang sama dengan akun penyimpanan Azure yang digunakan dalam sampel ini. Untuk beban kerja produksi, tempatkan ruang kerja Synapse dan penyimpanan Azure Anda di wilayah yang sama.

Langkah selanjutnya

Menyimpan hasil kueri ke penyimpanan

  • Last updated on