Cara menggunakan OPENROWSET menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics
Di bagian ini, Anda akan mempelajari cara membuat dan menggunakan tampilan untuk menyelesaikan kueri kumpulan SQL tanpa server. Tampilan akan memungkinkan Anda untuk menggunakan kembali kueri tersebut. Tampilan juga diperlukan jika Anda ingin menggunakan alat, seperti Power BI, bersama dengan kumpulan SQL tanpa server.
Prasyarat
Langkah pertama Anda adalah membuat database di mana tampilan akan dibuat dan menginisialisasi objek yang diperlukan untuk mengautentikasi penyimpanan Azure dengan mengeksekusi skrip pengaturan pada database tersebut. Semua kueri dalam artikel ini akan dieksekusi pada database sampel Anda.
Tampilan melalui data eksternal
Anda dapat membuat tampilan dengan cara yang sama seperti Anda membuat tampilan SQL Server reguler. Kueri berikut ini membuat tampilan yang membaca file population.csv.
Catatan
Ubah baris pertama dalam kueri, yaitu, [mydbname], untuk menggunakan database yang Anda buat.
USE [mydbname];
GO
DROP VIEW IF EXISTS populationView;
GO
CREATE VIEW populationView AS
SELECT *
FROM OPENROWSET(
BULK 'csv/population/population.csv',
DATA_SOURCE = 'SqlOnDemandDemo',
FORMAT = 'CSV',
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n'
)
WITH (
[country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
[country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
[year] smallint,
[population] bigint
) AS [r];
Tampilan menggunakan URL EXTERNAL DATA SOURCE akar penyimpanan Anda, sebagai DATA_SOURCE dan menambahkan jalur file relatif ke file.
Tampilan Delta Lake
Jika Anda membuat tampilan di atas folder Delta Lake, Anda perlu menentukan lokasi ke folder root setelah BULK opsi alih-alih menentukan jalur file.
Fungsi OPENROWSET yang membaca data dari folder Delta Lake akan memeriksa struktur folder dan secara otomatis mengidentifikasi lokasi file.
create or alter view CovidDeltaLake
as
select *
from openrowset(
bulk 'covid',
data_source = 'DeltaLakeStorage',
format = 'delta'
) with (
date_rep date,
cases int,
geo_id varchar(6)
) as rows
Untuk informasi selengkapnya, tinjau halaman bantuan mandiri kumpulan SQL tanpa server Synapse dan masalah Azure Synapse Analytics yang diketahui.
Tampilan terpartisi
Jika Anda memiliki sekumpulan file yang dipartisi dalam struktur folder hierarki, Anda dapat menjelaskan pola partisi menggunakan wildcard di jalur file. Gunakan fungsi FILEPATH untuk mengekspos bagian jalur folder sebagai kolom pemartisi.
CREATE VIEW TaxiView
AS SELECT *, nyc.filepath(1) AS [year], nyc.filepath(2) AS [month]
FROM
OPENROWSET(
BULK 'parquet/taxi/year=*/month=*/*.parquet',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT='PARQUET'
) AS nyc
Tampilan yang dipartisi dapat meningkatkan performa kueri Anda dengan melakukan eliminasi partisi saat Anda mengkuerinya dengan filter pada kolom partisi. Namun, tidak semua kueri mendukung eliminasi partisi, jadi penting untuk mengikuti beberapa praktik terbaik.
Untuk memastikan eliminasi partisi, hindari menggunakan subkueri dalam filter, karena dapat mengganggu kemampuan untuk menghilangkan partisi. Sebagai gantinya, teruskan hasil subkueri sebagai variabel ke filter.
Saat menggunakan JOIN dalam kueri SQL, nyatakan predikat filter sebagai NVARCHAR untuk mengurangi kompleksitas rencana kueri dan meningkatkan probabilitas penghapusan partisi yang benar. Kolom partisi biasanya disimpulkan sebagai NVARCHAR(1024), jadi menggunakan jenis yang sama untuk predikat menghindari kebutuhan akan transmisi implisit, yang dapat meningkatkan kompleksitas rencana kueri.
Tampilan Delta Lake terpartisi
Jika Anda membuat tampilan terpartisi di atas penyimpanan Delta Lake, Anda dapat menentukan hanya folder Delta Lake root dan tidak perlu secara eksplisit mengekspos kolom partisi menggunakan FILEPATH fungsi:
CREATE OR ALTER VIEW YellowTaxiView
AS SELECT *
FROM
OPENROWSET(
BULK 'yellow',
DATA_SOURCE = 'DeltaLakeStorage',
FORMAT='DELTA'
) nyc
Fungsi OPENROWSET ini akan memeriksa struktur folder Delta Lake yang mendasarinya dan secara otomatis mengidentifikasi dan mengekspos kolom partisi. Penghapusan partisi akan dilakukan secara otomatis jika Anda meletakkan kolom partisi dalam WHERE klausa kueri.
Nama folder dalam OPENROWSET fungsi (dalam yellow contoh ini) yang digabungkan dengan LOCATION URI yang ditentukan dalam sumber data DeltaLakeStorage harus mereferensikan folder Delta Lake root yang berisi subfolder yang disebut _delta_log.
Untuk informasi selengkapnya, tinjau halaman bantuan mandiri kumpulan SQL tanpa server Synapse dan masalah Azure Synapse Analytics yang diketahui.
Tampilan JSON
Tampilan adalah pilihan yang baik jika Anda perlu melakukan beberapa pemrosesan tambahan di atas set hasil yang diambil dari file. Salah satu contohnya mungkin mengurai file JSON di mana kita perlu menerapkan fungsi JSON untuk mengekstrak nilai dari dokumen JSON:
CREATE OR ALTER VIEW CovidCases
AS
select
*
from openrowset(
bulk 'latest/ecdc_cases.jsonl',
data_source = 'covid',
format = 'csv',
fieldterminator ='0x0b',
fieldquote = '0x0b'
) with (doc nvarchar(max)) as rows
cross apply openjson (doc)
with ( date_rep datetime2,
cases int,
fatal int '$.deaths',
country varchar(100) '$.countries_and_territories')
Fungsi OPENJSON mengurai setiap baris dari file JSONL yang berisi satu dokumen JSON per baris dalam format tekstual.
Tampilan Azure Cosmos DB pada kontainer
Tampilan dapat dibuat di atas kontainer Azure Cosmos DB jika penyimpanan analitik Azure Cosmos DB diaktifkan pada kontainer. Nama akun Azure Cosmos DB, nama database, dan nama kontainer harus ditambahkan sebagai bagian dari tampilan, dan kunci akses baca-saja harus ditempatkan dalam kredensial lingkup database yang direferensikan tampilan.
CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
PROVIDER = 'CosmosDB',
CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
OBJECT = 'Ecdc',
CREDENTIAL = 'MyCosmosDbAccountCredential'
) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows
Untuk informasi selengkapnya, lihat Kueri data Azure Cosmos DB dengan kumpulan SQL tanpa server di Azure Synapse Link.
Menggunakan tampilan
Anda bisa menggunakan tampilan dalam kueri Anda dengan cara yang sama seperti Anda menggunakan tampilan dalam kueri SQL Server.
Kueri berikut ini menunjukkan menggunakan population_csv yang kami buat di Buat tampilan. Ini mengembalikan nama negara/wilayah dengan populasi mereka pada tahun 2019 dalam urutan menurun.
Catatan
Ubah baris pertama dalam kueri, yaitu, [mydbname], untuk menggunakan database yang Anda buat.
USE [mydbname];
GO
SELECT
country_name, population
FROM populationView
WHERE
[year] = 2019
ORDER BY
[population] DESC;
Saat Anda mengkueri tampilan, Anda mungkin mengalami kesalahan atau hasil yang tidak terduga. Ini mungkin berarti bahwa tampilan mereferensikan kolom atau objek yang dimodifikasi atau tidak ada lagi. Anda perlu menyesuaikan definisi tampilan secara manual untuk menyelaraskan dengan perubahan skema yang mendasar.
Langkah berikutnya
Untuk informasi tentang cara mengkueri Jenis file yang berbeda, lihat file CSV tunggal Kueri,file Parquet Kuery, dan artikel file Kueri JSON.