Virtualisasi data dengan Azure SQL Managed Instance

Artikel
01/17/2024

Berlaku untuk: Azure SQL Managed Instance

Fitur virtualisasi data Azure SQL Managed Instance memungkinkan Anda menjalankan kueri Transact-SQL (T-SQL) pada file yang menyimpan data dalam format data umum di Azure Data Lake Storage Gen2 atau Azure Blob Storage, dan menggabungkannya dengan data relasional yang disimpan secara lokal menggunakan gabungan. Dengan cara ini Anda dapat secara transparan mengakses data eksternal (dalam mode baca-saja) sambil menyimpannya dalam format dan lokasi aslinya - juga dikenal sebagai virtualisasi data.

Gambaran Umum

Virtualisasi data menyediakan dua cara untuk mengkueri file yang ditujukan untuk serangkaian skenario yang berbeda:

Sintaks OPENROWSET – dioptimalkan untuk kueri ad hoc file. Biasanya digunakan untuk menjelajahi konten dan struktur set file baru dengan cepat.
SINTAKS CREATE EXTERNAL TABLE – dioptimalkan untuk kueri file berulang menggunakan sintaksis yang identik seolah-olah data disimpan secara lokal dalam database. Tabel eksternal memerlukan beberapa langkah persiapan dibandingkan dengan sintaks OPENROWSET, tetapi memberikan kontrol yang lebih besar atas akses data. Tabel eksternal biasanya digunakan untuk beban kerja analitis dan pelaporan.

Dalam kedua kasus, sumber data eksternal harus dibuat menggunakan sintaksIS CREATE EXTERNAL DATA SOURCE T-SQL, seperti yang ditunjukkan dalam artikel ini.

Juga tersedia sintaks CREATE EXTERNAL TABLE AS SELECT untuk Azure SQL Managed Instance, untuk mengekspor hasil pernyataan T-SQL SELECT ke dalam file Parquet atau CSV di Azure Blob Storage atau Azure Data Lake Storage (ADLS) Gen 2 dan membuat tabel eksternal di atas file tersebut.

Format file

Format file parquet dan teks berbatas (CSV) didukung secara langsung. Format file JSON secara tidak langsung didukung dengan menentukan format file CSV di mana kueri mengembalikan setiap dokumen sebagai baris terpisah. Anda dapat mengurai baris lebih lanjut menggunakan JSON_VALUE dan OPENJSON.

Tipe penyimpanan

File dapat disimpan di Azure Data Lake Storage Gen2 atau Azure Blob Storage. Untuk mengkueri file, Anda perlu menyediakan lokasi dalam format tertentu dan menggunakan awalan jenis lokasi yang sesuai dengan jenis sumber eksternal dan titik akhir/protokol, seperti contoh berikut:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet

Penting

Awalan jenis Lokasi yang disediakan digunakan untuk memilih protokol optimal untuk komunikasi dan untuk memanfaatkan kemampuan lanjutan yang ditawarkan oleh jenis penyimpanan tertentu. Menggunakan awalan generik https:// dinonaktifkan. Selalu gunakan awalan khusus titik akhir.

Memulai

Jika Anda baru menggunakan virtualisasi data dan ingin menguji fungsionalitas dengan cepat, mulailah dengan mengkueri himpunan data publik yang tersedia di Azure Open Datasets, seperti himpunan data Bing COVID-19 yang memungkinkan akses anonim.

Gunakan titik akhir berikut untuk mengkuerikan himpunan data COVID-19 Bing:

Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Untuk memulai cepat, jalankan kueri T-SQL sederhana ini untuk mendapatkan wawasan pertama tentang himpunan data. Kueri ini menggunakan OPENROWSET untuk mengkueri file yang disimpan di akun penyimpanan yang tersedia untuk umum:

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows

Anda dapat melanjutkan eksplorasi himpunan data dengan menambahkan WHERE, GROUP BY, dan klausa lainnya berdasarkan kumpulan hasil kueri pertama.

Jika kueri pertama gagal pada instans terkelola Anda, instans tersebut kemungkinan telah membatasi akses ke akun penyimpanan Azure, dan Anda harus berbicara dengan pakar jaringan Anda untuk mengaktifkan akses sebelum Anda dapat melanjutkan kueri.

Setelah Anda terbiasa mengkueri himpunan data publik, pertimbangkan untuk beralih ke himpunan data nonpublik yang memerlukan penyediaan kredensial, memberikan hak akses dan mengonfigurasi aturan firewall. Dalam banyak skenario dunia nyata Anda akan beroperasi terutama dengan himpunan data privat.

Akses ke akun penyimpanan nonpublik

Pengguna yang masuk ke instans terkelola harus berwenang untuk mengakses dan mengkueri file yang disimpan di akun penyimpanan nonpublik. Langkah-langkah otorisasi bergantung pada cara instans terkelola mengautentikasi ke penyimpanan. Jenis autentikasi dan parameter terkait tidak disediakan langsung dengan setiap kueri. Mereka dienkapsulasi dalam objek kredensial lingkup database yang disimpan dalam database pengguna. Kredensial digunakan oleh database untuk mengakses akun penyimpanan kapan saja kueri dijalankan. Azure SQL Managed Instance mendukung jenis autentikasi berikut:

Identitas terkelola
Tanda tangan akses bersama

Identitas terkelola adalah fitur ID Microsoft Entra (sebelumnya Azure Active Directory) yang menyediakan layanan Azure - seperti Azure SQL Managed Instance - dengan identitas yang dikelola di ID Microsoft Entra. Identitas ini dapat digunakan untuk mengotorisasi permintaan akses data di akun penyimpanan nonpublik. Layanan seperti Azure SQL Managed Instance memiliki identitas terkelola yang ditetapkan sistem, dan juga dapat memiliki satu atau beberapa identitas terkelola yang ditetapkan pengguna. Anda dapat menggunakan identitas terkelola yang ditetapkan sistem atau identitas terkelola yang ditetapkan pengguna untuk virtualisasi data dengan Azure SQL Managed Instance.

Administrator penyimpanan Azure harus terlebih dahulu memberikan izin ke identitas terkelola untuk mengakses data. Berikan izin ke identitas terkelola yang ditetapkan sistem dari instans terkelola dengan cara yang sama seperti izin yang diberikan kepada pengguna Microsoft Entra lainnya. Contohnya:

Di portal Azure, di halaman Kontrol Akses (IAM) akun penyimpanan, pilih Tambahkan penetapan peran.
Pilih peran Azure RBAC bawaan Pembaca Data Blob Penyimpanan. Ini menyediakan akses baca ke identitas terkelola untuk kontainer Azure Blob Storage yang diperlukan.
- Alih-alih memberikan identitas terkelola peran Storage Blob Data Reader Azure RBAC, Anda juga dapat memberikan izin yang lebih terperinci pada subset file. Semua pengguna yang memerlukan akses ke File individual Baca beberapa data dalam kontainer ini juga harus memiliki izin Jalankan pada semua folder induk hingga root (kontainer). Pelajari selengkapnya tentang cara mengatur ACL di Azure Data Lake Storage Gen2.
Pada halaman berikutnya, pilih Tetapkan akses ke Identitas terkelola. + Pilih anggota, dan di bawah daftar drop-down Identitas terkelola, pilih identitas terkelola yang diinginkan. Untuk informasi selengkapnya, lihat Menetapkan peran Azure menggunakan portal Microsoft Azure.
Kemudian, membuat kredensial cakupan database untuk autentikasi identitas terkelola sederhana. Perhatikan dalam contoh berikut yang 'Managed Identity' merupakan string yang dikodekan secara permanen.

-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'

Tanda tangan akses bersama (SAS) menyediakan akses yang didelegasikan ke file di akun penyimpanan. SAS memberi Anda kontrol terperinci atas jenis akses yang Anda berikan, termasuk interval validitas, izin yang diberikan, dan rentang alamat IP yang dapat diterima. Setelah token SAS dibuat, token tersebut tidak dapat dicabut atau dihapus dan memungkinkan akses hingga periode validitasnya kedaluwarsa.

Anda bisa mendapatkan token SAS dengan beberapa cara:
- Navigasi ke portal Azure -><Your_Storage_Account> -> Tanda tangan akses bersama -> Konfigurasikan izin -> Hasilkan SAS dan string koneksi. Untuk informasi selengkapnya, lihat Membuat tanda tangan akses bersama. - Membuat dan mengonfigurasi SAS dengan Azure Storage Explorer. - Anda dapat membuat SAS secara terprogram melalui PowerShell, Azure CLI, .NET, dan REST API. Untuk informasi selengkapnya, lihat Berikan akses terbatas ke sumber daya Azure Storage menggunakan tanda tangan akses bersama (SAS).

Berikan izin Baca dan Daftar melalui SAS untuk mengakses data eksternal. Saat ini, virtualisasi data dengan Azure SQL Managed Instance bersifat baca-saja.

Ketika token SAS dihasilkan, token tersebut menyertakan tanda tanya (?) di awal token. Untuk menggunakan token, Anda harus menghapus tanda tanya (?) saat membuat kredensial. Contohnya:

-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-04-18T20:42:12Z&st=2019-04-18T12:42:12Z&spr=https&sig=lQHczNvrk1KoYLCpFdSsMANd0ef9BrIPBNJ3VYEIq78%3D'

Sumber data eksternal

Sumber data eksternal adalah abstraksi yang memungkinkan referensi lokasi file yang mudah di beberapa kueri. Untuk mengkueri lokasi publik, yang perlu Anda tentukan saat membuat sumber data eksternal adalah lokasi file:

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
)

Saat mengakses akun penyimpanan nonpublik, bersama dengan lokasi, Anda juga perlu mereferensikan kredensial cakupan database dengan parameter autentikasi yang dienkapsulasi. Skrip berikut membuat sumber data eksternal yang menunjuk ke jalur file, dan merujuk kredensial cakupan database.

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
        CREDENTIAL = [MyCredential];
)

Sumber data kueri menggunakan OPENROWSET

Sintaks OPENROWSET memungkinkan kueri ad hoc instan sambil hanya membuat jumlah minimal objek database yang diperlukan.

OPENROWSEThanya memerlukan pembuatan sumber data eksternal (dan mungkin kredensial) dibandingkan dengan pendekatan tabel eksternal, yang memerlukan format file eksternal dan tabel eksternal itu sendiri.

Nilai parameter DATA_SOURCE secara otomatis ditambahkan sebelumnya ke parameter BULK untuk membentuk jalur penuh ke file.

Saat menggunakan OPENROWSET, berikan format file, seperti contoh berikut, yang mengkuerikan satu file:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Mengkueri beberapa file dan folder

Perintah OPENROWSET juga memungkinkan pengkuerian beberapa file atau folder dengan menggunakan wildcard di jalur BULK.

Contoh berikut menggunakan rekaman perjalanan taksi kuning NYC membuka himpunan data.

Pertama, buat sumber data eksternal:

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Sekarang kita dapat mengkueri semua file dengan ekstensi .parquet di folder. Misalnya, di sini kita hanya akan mengkueri file-file yang cocok dengan pola nama:

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Saat mengkueri beberapa file atau folder, semua file yang diakses dengan satu OPENROWSET harus memiliki struktur yang sama (seperti jumlah kolom dan jenis data yang sama). Folder tidak dapat dilalui secara rekursif.

Inferensi skema

Inferensi skema otomatis membantu Anda menulis kueri dengan cepat dan menjelajahi data tanpa mengetahui skema file. Inferensi skema hanya berfungsi dengan file parket.

Meskipun nyaman, jenis data yang disimpulkan mungkin lebih besar dari jenis data aktual karena mungkin ada cukup informasi dalam file sumber untuk memastikan jenis data yang sesuai digunakan. Hal ini dapat menyebabkan kinerja kueri memburuk. Misalnya, file parquet tidak berisi metadata tentang panjang kolom karakter maksimum, sehingga instans menyimpulkannya sebagai varchar(8000).

Gunakan prosedur tersimpan sp_describe_first_results_set untuk memeriksa hasil jenis data kueri Anda, seperti contoh berikut:

EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

Setelah mengetahui jenis data, Anda kemudian dapat menentukannya menggunakan klausa WITH untuk meningkatkan performa:

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

Karena skema file CSV tidak dapat ditentukan secara otomatis, kolom harus selalu ditentukan menggunakan WITH klausa:

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

Fungsi metadata file

Saat mengkueri beberapa file atau folder, Anda dapat menggunakan fungsi filepath() dan filename() untuk membaca metadata file dan mendapatkan bagian dari jalur atau jalur lengkap dan nama file yang merupakan asal baris dalam kumpulan hasil:

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

Ketika dipanggil tanpa parameter, fungsi filepath() menampilkan jalur file yang merupakan asal dari baris tersebut. Ketika DATA_SOURCE digunakan dalam OPENROWSET, ia menampilkan jalur relatif terhadap DATA_SOURCE, atau mengembalikan jalur file lengkap.

Ketika dipicu dengan parameter, fungsi ini menampilkan bagian dari jalur yang cocok dengan wildcard pada posisi yang ditentukan dalam parameter. Misalnya, parameter value 1 akan menampilkan bagian dari jalur yang cocok dengan wildcard pertama.

Fungsi filepath() juga dapat digunakan untuk memfilter dan menggabungkan baris:

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

Membuat tampilan di atas OPENROWSET

Anda dapat membuat dan menggunakan tampilan untuk membungkus kueri OPENROWSET, sehingga Anda dapat dengan mudah menggunakan kembali kueri yang mendasarinya:

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows

Bisa juga menambahkan kolom yang berisi data lokasi file ke tampilan menggunakan fungsi filepath() untuk pemfilteran yang lebih mudah dan lebih efisien. Penggunaan tampilan dapat mengurangi jumlah file dan jumlah data yang perlu dibaca dan diproses oleh kueri di atas tampilan saat difilter oleh salah satu kolom tersebut:

CREATE VIEW TaxiRides AS
SELECT *
 , filerows.filepath(1) AS [year]
 , filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows

Tampilan juga memungkinkan pelaporan dan alat analitik seperti Power BI untuk memakai hasil OPENROWSET.

Tabel eksternal

Tabel eksternal merangkum akses ke file yang menjadikan pengalaman kueri hampir identik dengan pengkuerian data relasional lokal yang disimpan dalam tabel pengguna. Pembuatan tabel eksternal memerlukan adanya sumber data eksternal dan objek format file eksternal:

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
)
GO

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

Setelah tabel eksternal dibuat, Anda dapat mengkuerikannya seperti tabel lainnya:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Sama seperti OPENROWSET, tabel eksternal memungkinkan pengkuerian beberapa file dan folder menggunakan wildcard. Inferensi skema tidak didukung dengan tabel eksternal.

Pertimbangan performa

Tidak ada batasan keras untuk jumlah file atau jumlah data yang dapat dikueri, tetapi performa kueri tergantung pada jumlah data, format data, cara data diatur, dan kompleksitas kueri dan gabungan.

Data yang dipartisi kueri

Data sering diatur dalam subfolder yang juga disebut partisi. Anda dapat menginstruksikan instans terkelola untuk hanya mengkueri folder dan file tertentu. Melakukannya mengurangi jumlah file dan jumlah data yang perlu dibaca dan diproses kueri, sehingga menghasilkan performa yang lebih baik. Jenis pengoptimalan kueri ini dikenal sebagai pemangkasan partisi atau eliminasi partisi. Anda dapat menghilangkan partisi dari eksekusi kueri dengan menggunakan fungsi filepath() metadata dalam klausa WHERE kueri.

Contoh kueri berikut membaca file data Taksi Kuning NYC hanya selama tiga bulan terakhir 2017:

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Jika data tersimpan Anda tidak dipartisi, pertimbangkan untuk mempartisinya untuk meningkatkan performa kueri.

Jika Anda menggunakan tabel eksternal, filepath() dan filename() fungsi didukung tetapi tidak dalam klausa WHERE. Anda masih dapat memfilter menurut filename atau filepath jika Anda menggunakannya dalam kolom komputasi. Contoh berikut menunjukkan hal berikut:

CREATE EXTERNAL TABLE tbl_TaxiRides (
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT,
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

SELECT *
      FROM tbl_TaxiRides
WHERE
      [year]=2017            
      AND [month] in (10,11,12);

Jika data tersimpan Anda tidak dipartisi, pertimbangkan untuk mempartisinya untuk meningkatkan performa kueri.

Statistik

Mengumpulkan statistik pada data Anda merupakan salah satu hal paling penting yang dapat Anda lakukan untuk optimasi kueri. Semakin banyak yang diketahui instans tentang data Anda, semakin cepat ia dapat menjalankan kueri. Pengoptimal kueri mesin SQL adalah pengoptimal berbasis biaya. Pengoptimal membandingkan biaya paket kueri yang bermacam, dan kemudian memilih paket dengan biaya terendah. Dalam kebanyakan kasus, ia memilih rencana yang menjalankan yang tercepat.

Pembuatan statistik otomatis

Azure SQL Managed Instance menganalisis kueri pengguna masuk untuk statistik yang hilang. Jika statistik hilang, pengoptimal kueri secara otomatis membuat statistik pada kolom individual dalam predikat kueri atau kondisi gabungan untuk meningkatkan perkiraan kardinalitas untuk rencana kueri. Pembuatan statistik otomatis dilakukan secara sinkron sehingga Anda mungkin mengalami sedikit penurunan performa kueri jika kolom Anda kehilangan statistik. Waktu untuk membuat statistik untuk satu kolom tergantung pada ukuran file yang ditargetkan.

Statistik manual OPENROWSET

Statistik kolom tunggal untuk jalur OPENROWSET dapat dibuat menggunakan prosedur sys.sp_create_openrowset_statistics yang tersimpan, dengan meneruskan kueri pilihan dengan satu kolom sebagai parameter:

EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

Secara default, instans tersebut menggunakan 100% data yang disediakan dalam himpunan data untuk membuat statistik. Anda dapat secara opsional menentukan ukuran sampel sebagai persentase menggunakan opsi TABLESAMPLE. Untuk membuat statistik kolom tunggal untuk beberapa kolom, jalankan sys.sp_create_openrowset_statistics untuk setiap kolom. Anda tidak dapat membuat statistik multi-kolom untuk jalur OPENROWSET.

Untuk memperbarui statistik yang ada, letakkan terlebih dahulu menggunakan prosedur sys.sp_drop_openrowset_statistics yang disimpan, lalu buat ulang menggunakan sys.sp_create_openrowset_statistics:

EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

Statistik manual tabel eksternal

Sintaks untuk membuat statistik pada tabel eksternal menyerupai yang digunakan untuk tabel pengguna biasa. Untuk membuat statistik pada kolom, berikan nama untuk objek statistik dan nama kolom:

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendorID)
WITH FULLSCAN, NORECOMPUTE;

Opsi WITH bersifat wajib, dan untuk ukuran sampel, opsi yang diizinkan adalah FULLSCAN dan SAMPLE n persen.

Untuk membuat statistik kolom tunggal untuk beberapa kolom, jalankan CREATE STATISTICS untuk setiap kolom.
Statistik multi-kolom tidak didukung.

Pecahkan masalah

Masalah dengan eksekusi kueri biasanya disebabkan oleh instans terkelola yang tidak dapat mengakses lokasi file. Pesan kesalahan terkait mungkin melaporkan hak akses yang tidak mencukupi, lokasi atau jalur file yang tidak ada, file yang digunakan oleh proses lain, atau direktori tersebut tidak dapat dicantumkan. Dalam sebagian besar kasus, masalah ini menunjukkan bahwa akses ke file diblokir oleh kebijakan kontrol lalu lintas jaringan atau karena kurangnya hak akses. Hal inilah yang harus diperiksa:

Jalur lokasi yang salah atau salah ketik.
Validitas kunci SAS: dapat kedaluwarsa, berisi kesalahan ketik, dimulai dengan tanda tanya.
Izin kunci SAS diizinkan: Baca minimal, dan Daftar apakah wildcard digunakan.
Lalu lintas masuk yang diblokir pada akun penyimpanan. Periksa Mengelola aturan jaringan virtual untuk Azure Storage untuk mengetahui detail lebih lanjut dan pastikan akses dari VNet instans terkelola diizinkan.
Lalu lintas keluar diblokir pada instans terkelola menggunakan kebijakan titik akhir penyimpanan. Izinkan lalu lintas keluar ke akun penyimpanan.
Hak akses Identitas Terkelola: pastikan identitas terkelola instans diberikan hak akses ke akun penyimpanan.
Tingkat kompatibilitas database harus 130 atau lebih tinggi agar kueri virtualisasi data berfungsi.

BUAT TABEL EKSTERNAL SEBAGAI PILIH` (CETAS)

CREATE EXTERNAL TABLE AS SELECT (CETAS) memungkinkan Anda mengekspor data dari instans terkelola SQL Anda ke akun penyimpanan eksternal. Anda dapat menggunakan CETAS untuk membuat tabel eksternal di atas file Parquet atau CSV penyimpanan Azure Blob atau Azure Data Lake Storage (ADLS) Gen2. CETAS juga dapat mengekspor, secara paralel, hasil pernyataan T-SQL SELECT ke dalam tabel eksternal yang dibuat. Ada potensi risiko penyelundupan data dengan kemampuan ini, sehingga CETAS dinonaktifkan secara default untuk Azure SQL Managed Instance. Untuk mengaktifkan, lihat CREATE EXTERNAL TABLE AS SELECT (CETAS).

Batasan

Fitur keamanan tingkat baris tidak didukung dengan tabel eksternal.
Aturan masking data dinamis tidak dapat ditentukan untuk kolom dalam tabel eksternal.

Masalah umum

Saat parameterisasi untuk Always Encrypted diaktifkan di SQL Server Management Studio (SSMS), kueri virtualisasi data gagal dengan Incorrect syntax near 'PUSHDOWN' pesan kesalahan.

Bagikan melalui

Virtualisasi data dengan Azure SQL Managed Instance

Gambaran Umum

Format file

Tipe penyimpanan

Memulai

Akses ke akun penyimpanan nonpublik

Sumber data eksternal

Sumber data kueri menggunakan OPENROWSET

Mengkueri beberapa file dan folder

Inferensi skema

Fungsi metadata file

Membuat tampilan di atas OPENROWSET

Tabel eksternal

Pertimbangan performa

Data yang dipartisi kueri

Statistik

Pembuatan statistik otomatis

Statistik manual OPENROWSET

Statistik manual tabel eksternal

Pecahkan masalah

BUAT TABEL EKSTERNAL SEBAGAI PILIH` (CETAS)

Batasan

Masalah umum

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

Virtualisasi data dengan Azure SQL Managed Instance

Gambaran Umum

Format file

Tipe penyimpanan

Memulai

Akses ke akun penyimpanan nonpublik

Sumber data eksternal

Sumber data kueri menggunakan OPENROWSET

Mengkueri beberapa file dan folder

Inferensi skema

Fungsi metadata file

Membuat tampilan di atas OPENROWSET

Tabel eksternal

Pertimbangan performa

Data yang dipartisi kueri

Statistik

Pembuatan statistik otomatis

Statistik manual OPENROWSET

Statistik manual tabel eksternal

Pecahkan masalah

BUAT TABEL EKSTERNAL SEBAGAI PILIH` (CETAS)

Batasan

Masalah umum

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: