Bagikan melalui

Menggunakan tabel eksternal dengan Synapse SQL

  • Artikel

Tabel eksternal mengarah ke data yang terletak di Hadoop, blob Azure Storage, atau Azure Data Lake Storage. Anda dapat menggunakan tabel eksternal untuk membaca data dari file atau menulis data ke file di Azure Storage.

Dengan Synapse SQL, Anda dapat menggunakan tabel eksternal untuk membaca data eksternal menggunakan kumpulan SQL khusus atau kumpulan SQL tanpa server.

Bergantung pada jenis sumber data eksternalnya, Anda dapat menggunakan dua jenis tabel eksternal:

  • Tabel eksternal Hadoop yang dapat Anda gunakan untuk membaca dan mengekspor data dalam berbagai format data seperti CSV, Parquet, dan ORC. Tabel eksternal Hadoop tersedia di kumpulan SQL khusus, tetapi tidak tersedia di kumpulan SQL tanpa server.
  • Tabel eksternal asli yang dapat Anda gunakan untuk membaca dan mengekspor data dalam berbagai format data seperti CSV dan Parquet. Tabel eksternal asli tersedia di kumpulan SQL tanpa server. Saat ini, tabel tersebut sedang dalam pratinjau publik di kumpulan SQL khusus. Menulis/mengekspor data menggunakan CETAS dan tabel eksternal asli hanya tersedia di kumpulan SQL tanpa server, tetapi tidak di kumpulan SQL khusus.

Perbedaan utama antara Hadoop dan tabel eksternal asli:

Jenis tabel eksternal Hadoop Asli
Kumpulan SQL khusus Tersedia Hanya tabel Parquet yang tersedia dalam pratinjau publik.
Kumpulan SQL tanpa server Tidak tersedia Tersedia
Format yang didukung Dibatasi/CSV, Parquet, ORC, Hive RC, dan RC Kumpulan SQL tanpa server: Dibatasi/CSV, Parquet, dan Delta Lake
Kumpulan SQL khusus: Parquet (pratinjau)
Penghapusan partisi folder Tidak Penghapusan partisi hanya tersedia dalam tabel yang dipartisi yang dibuat pada format Parquet atau CSV yang disinkronkan dari kumpulan Apache Spark. Anda mungkin membuat tabel eksternal pada folder yang dipartisi Parquet, tetapi kolom partisi tidak dapat diakses dan diabaikan, sementara eliminasi partisi tidak akan diterapkan. Jangan membuat tabel eksternal di folder Delta Lake karena tidak didukung. Gunakan tampilan yang dipartisi Delta jika Anda perlu mengkueri data Delta Lake yang dipartisi.
Penghapusan file (pushdown predikat) Tidak Ya, di kumpulan SQL tanpa server. Untuk pushdown string, Anda perlu menggunakan kolase Latin1_General_100_BIN2_UTF8 pada kolom VARCHAR untuk mengaktifkan pushdown. Untuk informasi selengkapnya tentang kolase, lihat Jenis kolase yang didukung untuk Synapse SQL.
Format kustom untuk lokasi Tidak Ya, gunakan wildcard seperti /year=*/month=*/day=* untuk format Parquet atau CSV. Jalur folder kustom tidak tersedia di Delta Lake. Di kumpulan SQL tanpa server, Anda juga dapat menggunakan wildcard /logs/** rekursif untuk mereferensikan file Parquet atau CSV di sub-folder apa pun di bawah folder yang direferensikan.
Pemindaian folder rekursif Ya Ya. Di kumpulan SQL tanpa server harus ditentukan /** di akhir jalur lokasi. Di Kumpulan khusus, folder selalu dipindai secara rekursif.
Autentikasi penyimpanan Kunci Akses Penyimpanan (SAK), Passthrough Microsoft Entra, Identitas terkelola, identitas Microsoft Entra aplikasi kustom Tanda Tangan Akses Bersama(SAS), Passthrough Microsoft Entra, Identitas terkelola, Identitas Microsoft Entra aplikasi kustom.
Pemetaan kolom Ordinal - kolom dalam definisi tabel eksternal dipetakan ke kolom dalam file Parquet yang mendasarinya berdasarkan posisi. Kumpulan tanpa server: berdasarkan nama. Kolom dalam definisi tabel eksternal dipetakan ke kolom dalam file Parquet yang mendasarinya berdasarkan pencocokan nama kolom.
Kumpulan khusus: pencocokan ordinal. Kolom dalam definisi tabel eksternal dipetakan ke kolom dalam file Parquet yang mendasarinya berdasarkan posisi.
CETAS (ekspor/transformasi) Ya CETAS dengan tabel asli sebagai target hanya berfungsi di kumpulan SQL tanpa server. Anda tidak dapat menggunakan kumpulan SQL khusus untuk mengekspor data menggunakan tabel asli.

Catatan

Tabel eksternal asli adalah solusi yang direkomendasikan di kumpulan yang umumnya tersedia. Jika Anda perlu mengakses data eksternal, selalu gunakan tabel asli di kumpulan tanpa server. Di kumpulan khusus, Anda harus beralih ke tabel asli untuk membaca file Parquet setelah berada di GA. Gunakan tabel Hadoop hanya jika Anda perlu mengakses beberapa jenis yang tidak didukung dalam tabel eksternal asli (misalnya - ORC, RC), atau jika versi asli tidak tersedia.

Tabel eksternal dalam kumpulan SQL khusus dan kumpulan SQL tanpa server

Anda dapat menggunakan tabel eksternal untuk:

  • Mengkueri ke Blob Azure Storage dan Azure Data Lake Gen2 dengan pernyataan T-SQL.
  • Simpan hasil kueri ke file di Azure Blob Storage atau Azure Data Lake Storage dengan menggunakan CETAS.
  • Mengimpor data dari Blob Azure Storage dan Azure Data Lake Storage dan menyimpannya di kumpulan SQL khusus (hanya tabel Hadoop di kumpulan khusus).

Catatan

Saat digunakan bersama pernyataan CREATE TABLE AS SELECT, data akan diimpor ke tabel dalam kumpulan SQL khusus jika Anda memilih dari tabel eksternal.

Jika performa tabel eksternal Hadoop di kumpulan khusus tidak memenuhi sasaran performa Anda, pertimbangkan untuk memuat data eksternal ke dalam tabel Datawarehouse menggunakan pernyataan COPY.

Untuk tutorial pemuatan, lihat Menggunakan PolyBase untuk memuat data dari Blob Azure Storage.

Anda dapat membuat tabel eksternal di kumpulan Synapse SQL melalui langkah-langkah berikut:

  1. CREATE EXTERNAL DATA SOURCE untuk mereferensikan penyimpanan Azure eksternal dan menentukan info masuk yang akan digunakan untuk mengakses penyimpanan.
  2. CREATE EXTERNAL FILE FORMAT untuk mendeskripsikan format file CSV atau Parquet.
  3. CREATE EXTERNAL TABLE di atas file yang ditempatkan di sumber data dengan format file yang sama.

Penghapusan partisi folder

Tabel eksternal asli di kumpulan Synapse dapat mengabaikan file yang ditempatkan di folder yang tidak relevan untuk kueri. Jika file Anda disimpan dalam hierarki folder (misalnya - /year=2020/month=03/day=16) dan nilai untuk year, month, dan day diekspos sebagai kolom, kueri yang berisi filter seperti year=2020 akan membaca file hanya dari subfolder yang ditempatkan dalam year=2020 folder. File dan folder yang ditempatkan di folder lain (year=2021 atau year=2022) akan diabaikan dalam kueri ini. Eliminasi ini dikenal sebagai eliminasi partisi.

Penghapusan partisi folder tersedia di tabel eksternal asli yang disinkronkan dari kumpulan Synapse Spark. Jika Anda memiliki kumpulan data yang dipartisi dan ingin memanfaatkan penghapusan partisi dengan tabel eksternal yang Anda buat, gunakan tampilan yang dipartisi bukan tabel eksternal.

Penghapusan file

Beberapa format data seperti Parquet dan Delta berisi statistik file untuk setiap kolom (misalnya, nilai min/maks untuk setiap kolom). Kueri yang memfilter data tidak akan membaca file di mana nilai kolom yang diperlukan tidak ada. Kueri pertama-tama akan menjelajahi nilai min/maks untuk kolom yang digunakan dalam predikat kueri untuk menemukan file yang tidak berisi data yang diperlukan. File-file ini akan diabaikan dan dihapus dari rencana kueri. Teknik ini juga dikenal sebagai filter predikat pushdown dan dapat meningkatkan performa kueri Anda. Filter pushdown tersedia di kumpulan SQL tanpa server pada format Parquet dan Delta. Untuk memanfaatkan filter pushdown untuk jenis string, gunakan jenis VARCHAR dengan kolase Latin1_General_100_BIN2_UTF8. Untuk informasi selengkapnya tentang kolase, lihat Jenis kolase yang didukung untuk Synapse SQL.

Keamanan

Pengguna harus memiliki izin SELECT pada tabel eksternal untuk membaca data. Tabel eksternal mengakses penyimpanan Azure yang mendasarinya menggunakan info masuk lingkup database yang ditentukan dalam sumber data menggunakan aturan berikut:

  • Sumber data tanpa info masuk memungkinkan tabel eksternal mengakses file yang tersedia untuk umum di penyimpanan Azure.
  • Sumber data dapat memiliki info masuk yang memungkinkan tabel eksternal hanya mengakses file di penyimpanan Azure menggunakan Identitas Terkelola ruang kerja atau token SAS. Misalnya, lihat artikel Mengembangkan file penyimpanan untuk mengontrol akses penyimpanan.

Contoh untuk CREATE EXTERNAL DATA SOURCE

Berikut contoh membuat sumber data eksternal Hadoop di kumpulan SQL khusus untuk Azure Data Lake Gen2 yang mengarah ke himpunan data New York:

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
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'
GO
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

Berikut contoh membuat sumber data eksternal untuk Azure Data Lake Gen2 yang mengarah ke himpunan data New York yang tersedia untuk umum:

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

Contoh untuk CREATE EXTERNAL FILE FORMAT

Berikut contoh membuat format file eksternal untuk file sensus:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

Contoh CREATE EXTERNAL TABLE

Berikut contoh membuat tabel eksternal. Ini menampilkan baris pertama:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Membuat dan mengkueri tabel eksternal dari file di Azure Data Lake

Menggunakan kemampuan eksplorasi Data Lake dari Synapse Studio, Anda kini dapat membuat dan mengkueri tabel eksternal menggunakan kumpulan Synapse SQL cukup dengan mengklik kanan file. Gerakan satu klik untuk membuat tabel eksternal dari akun penyimpanan ADLS Gen2 hanya didukung untuk file Parquet.

Prasyarat

  • Anda harus memiliki akses ke ruang kerja dengan setidaknya Storage Blob Data Contributor peran akses ke akun ADLS Gen2 atau Daftar Kontrol Akses (ACL) yang memungkinkan Anda mengkueri file.

  • Anda harus memiliki setidaknya izin untuk membuat tabel eksternal dan mengkueri tabel eksternal pada kumpulan Synapse SQL (khusus atau tanpa server).

Dari panel Data, pilih file yang ingin Anda buat tabel eksternalnya:

externaltable1

Jendela dialog akan terbuka. Pilih kumpulan SQL khusus atau kumpulan SQL tanpa server, beri nama untuk tabel lalu pilih skrip terbuka:

externaltable2

Skrip SQL secara otomatis dibuat dengan menyimpulkan skema dari file:

externaltable3

Jalankan skrip. Skrip akan secara otomatis menjalankan Select Top 100 *.:

externaltable4

Tabel eksternal kini telah dibuat, untuk eksplorasi konten tabel eksternal ini di masa mendatang, pengguna dapat mengkuerinya langsung dari panel Data:

externaltable5

Langkah berikutnya

Lihat artikel CETAS tentang cara menyimpan hasil kueri ke tabel eksternal di Azure Storage. Atau Anda dapat mulai mengkueri Apache Spark untuk tabel eksternal Azure Synapse.