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

Artikel
05/12/2023

Di bagian ini, Anda akan mempelajari cara membuat dan menggunakan tabel eksternal native 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 sedang dalam 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 native 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
Parquet	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, dll.)	Tidak (Atau, buat tampilan)	Tidak

Prasyarat

Langkah pertama Anda adalah membuat database tempat tabel akan dibuat. Sebelum membuat kredensial lingkup database, 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:

DATABASE SCOPED CREDENTIAL sqlondemand yang memungkinkan akses ke akun penyimpanan Azure https://sqlondemandstorage.blob.core.windows.net yang dilindungi SAS.

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'

SUMBER DATA EKSTERNAL sqlondemanddemo yang merujuk ke akun penyimpanan demo yang dilindungi dengan kunci SAS, dan SUMBER DATA EKSTERNAL nyctlc yang merujuk ke akun penyimpanan Azure yang tersedia untuk umum di lokasi https://azureopendatastorage.blob.core.windows.net/nyctlc/.

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

Format file QuotedCSVWithHeaderFormat dan ParquetFormat yang menunjukkan 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.

Tabel eksternal pada 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 ini membuat tabel eksternal yang bertuliskan filepopulation.csv dari akun penyimpanan Azure demo SynapseSQL yang dirujuk menggunakan sumber data sqlondemanddemo dan dilindungi dengan kredensial lingkup database yang disebut sqlondemand.

Sumber data dan kredensial lingkup database dibuat dalam skrip penyiapan.

Catatan

Mengubah 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 polanya secara otomatis.

Catatan

Tabel dibuat pada struktur folder yang dipartisi, tetapi Anda tidak dapat memanfaatkan beberapa eliminasi partisi. Jika Anda ingin mendapatkan kinerja 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 berjalan. Dalam kueri yang sudah berjalan lama, kumpulan SQL dapat mencoba membaca kembali, membaca sebagian 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-menerus 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 baca ALLOW_INCONSISTENT_READS akan menonaktifkan pemeriksaan waktu modifikasi file selama siklus hidup kueri dan membaca apa pun yang tersedia dalam file yang dirujuk oleh tabel eksternal. Pada 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 pembacaan file yang mendasarinya dengan swa-gabung 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 sekumpulan file dan tabel eksternal yang dibuat dalam format Delta Lake adalah bahwa di tabel eksternal Delta Lake Anda perlu merujuk ke folder yang berisi struktur Delta Lake.

ECDC COVID-19 Delta Lake folder

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 yang diketahui di Halaman bantuan mandiri terkait kumpulan SQL synapse tanpa server.

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 bukan tabel jika Anda telah mempartisi himpunan data Delta Lake.

Penting

Jangan membuat tabel eksternal pada folder Delta Lake yang dipartisi meskipun jika 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 agar 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

Mengubah 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 berikutnya

Untuk informasi tentang cara menyimpan hasil kueri ke penyimpanan, lihat artikel Menyimpan hasil kueri ke penyimpanan.

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

Prasyarat

Tabel eksternal pada file

Tabel eksternal pada sekumpulan file

Tabel eksternal pada file yang dapat ditambahkan

Tabel eksternal Delta Lake

Tabel Delta di folder yang dipartisi

Menggunakan tabel eksternal

Langkah berikutnya

Sumber Daya Tambahan: