Aracılığıyla paylaş

Azure SQL Veritabanı ile veri sanallaştırma (Önizleme)

Şunlar için geçerlidir:Azure SQL Veritabanı

Azure SQL Veritabanı'nın veri sanallaştırma özelliği, CSV (Sınırlandırılmış Metin), Parquet ve Delta (1.0) gibi ortak veri biçimlerinde veri depolayan dosyalarda Transact-SQL (T-SQL) sorguları yürütmenizi sağlar. Bu verileri Azure Data Lake Storage 2. Nesil veya Azure Blob Depolama'da sorgulayabilir ve birleştirmeleri kullanarak yerel olarak depolanan ilişkisel verilerle birleştirebilirsiniz. Bu şekilde, veri sanallaştırma olarak da bilinen özgün biçiminde ve konumunda tutarken dış verilere (salt okunur modda) saydam bir şekilde erişebilirsiniz.

Genel Bakış

Veri sanallaştırma, farklı senaryo kümelerine yönelik dosyaları sorgulamanın iki yolunu sağlar:

  • OPENROWSET söz dizimi – dosyaların geçici olarak sorgulanması için iyileştirilmiştir. Genellikle yeni bir dosya kümesinin içeriğini ve yapısını hızla keşfetmek için kullanılır.
  • CREATE EXTERNAL TABLE söz dizimi – veriler veritabanında yerel olarak depolanmış gibi aynı söz dizimi kullanılarak dosyaların tekrar tekrar sorgulanması için iyileştirilmiştir. Dış tablolar OPENROWSET söz dizimine kıyasla birkaç hazırlık adımı gerektirir, ancak veri erişimi üzerinde daha fazla denetime olanak sağlar. Dış tablolar genellikle analitik iş yükleri ve raporlama için kullanılır.

Her iki durumda da, bu makalede gösterildiği gibi CREATE EXTERNAL DATA SOURCE T-SQL söz dizimi kullanılarak bir dış veri kaynağı oluşturulmalıdır.

Dosya biçimleri

Parquet ve sınırlandırılmış metin (CSV) dosya biçimleri doğrudan desteklenir. JSON dosya biçimi, sorguların her belgeyi ayrı bir satır olarak döndürdüğü CSV dosya biçimi belirtilerek dolaylı olarak desteklenir. ve JSON_VALUEkullanarak OPENJSON satırları daha fazla ayrıştırabilirsiniz.

Depolama türleri

Dosyalar Azure Data Lake Storage 2. Nesil veya Azure Blob Depolama depolanabilir. Dosyaları sorgulamak için konumu belirli bir biçimde sağlamanız ve aşağıdaki örnekler gibi dış kaynak ve uç nokta/protokol türüne karşılık gelen konum türü ön ekini kullanmanız gerekir:

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

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

Önemli

Uç noktaya özgü ön ekleri her zaman kullanın. Sağlanan Konum türü ön eki, iletişim için en uygun protokolü seçmek ve belirli bir depolama türü tarafından sunulan gelişmiş özelliklerden yararlanmak için kullanılır.

Genel https:// öneki yalnızca BULK INSERT için desteklenir, ancak OPENROWSET veya EXTERNAL TABLE gibi diğer kullanım durumları için desteklenmez.

Başlayın

Veri sanallaştırmayı yeni kullanmaya başladıysanız ve işlevleri hızla test etmek istiyorsanız, anonim erişime izin veren Bing COVID-19 veri kümesi gibi Azure Açık Veri Kümelerinde kullanılabilen genel veri kümelerini sorgulayarak işe başlayın.

Bing COVID-19 veri kümelerini sorgulamak için aşağıdaki uç noktaları kullanın:

  • Parke: 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

Hızlı başlangıç için bu basit T-SQL sorgusunu çalıştırarak veri kümesiyle ilgili ilk içgörüleri alın. Bu sorgu, genel kullanıma açık bir depolama hesabında depolanan bir dosyayı sorgulamak için OPENROWSET kullanır:

--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;

Veri kümesi keşfine, ilk sorgunun sonuç kümesini temel alarak WHERE, GROUP BY ve diğer yan tümcelerini ekleyerek devam edebilirsiniz.

Genel veri kümelerini sorgulama hakkında bilgi edindikten sonra, kimlik bilgileri sağlamayı, erişim hakları vermeyi ve güvenlik duvarı kurallarını yapılandırmayı gerektiren abonelik dışı veri kümelerine geçmeyi göz önünde bulundurun. Birçok gerçek dünya senaryosunda öncelikli olarak özel veri kümeleriyle çalışacaksınız.

Abonelik dışı depolama hesaplarına erişim

Azure SQL Veritabanı'nda oturum açmış bir kullanıcının genel olmayan depolama hesaplarında depolanan dosyalara erişme ve dosyaları sorgulama yetkisine sahip olması gerekir. Yetkilendirme adımları, Azure SQL Veritabanı'nın depolamada kimlik doğrulamasına bağlıdır. Kimlik doğrulaması türleri ve ilgili parametreler doğrudan her sorguyla birlikte sağlanmaz. Bunlar, kullanıcı veritabanında depolanan veritabanı kapsamlı kimlik bilgisi nesnesinde kapsüllenir. Kimlik bilgisi, sorgu yürütülürken depolama hesabına erişmek için veritabanı tarafından kullanılır.

Azure SQL Veritabanı aşağıdaki kimlik doğrulama türlerini destekler:

  • Paylaşılan erişim imzası (SAS)
  • İdare edilen kimlik
  • Kullanıcı Kimliği aracılığıyla Microsoft Entra doğrudan kimlik doğrulaması

Paylaşılan erişim imzası (SAS), depolama hesabındaki dosyalara temsilci erişimi sağlar. SAS, geçerlilik aralığı, verilen izinler ve kabul edilebilir IP adresi aralığı dahil olmak üzere, sağladığınız erişim türü üzerinde ayrıntılı denetim sağlar. SAS belirteci oluşturulduktan sonra iptal edilemez veya silinemez ve geçerlilik süresi dolana kadar erişime izin verir.

  1. SAS belirtecini birden çok yolla alabilirsiniz:

  2. Dış verilere erişmek için SAS aracılığıyla Okuma ve Listeleme izinleri verin. Şu anda Azure SQL Veritabanı ile veri sanallaştırması yalnızca okuma modundadır.

  3. Azure SQL Veritabanı'nda veritabanı kapsamlı bir kimlik bilgisi oluşturmak için, veritabanı ana anahtarını (henüz yoksa) oluşturmanız gerekir. Kimlik bilgisi gerektirdiğinde bir veritabanı ana anahtarı gereklidir SECRET.

    -- Create MASTER KEY if it doesn't exist in the database:
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>';

  4. SAS belirteci oluşturulduğunda, belirtecin başında bir soru işareti (?) içerir. Belirteci kullanmak için, kimlik bilgisi oluştururken soru işaretini (?) kaldırmanız gerekir. Örneğin:

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=secret string here';

Anonim hesaplar aracılığıyla genel depolamaya erişim

İstenen veri kümesi genel erişime izin veriyorsa (anonim erişim olarak da bilinir), Azure Depolama düzgün yapılandırıldığı sürece kimlik bilgisi gerekmez. Bkz. Kapsayıcılar ve bloblar için anonim okuma erişimini yapılandırma.

Dış veri kaynağı

Dış veri kaynağı, bir dosya konumunun birden çok sorguda kolayca başvurulabilmesini sağlayan bir soyutlamadır. Genel konumları sorgulamak için dış veri kaynağı oluştururken belirtmeniz gereken tek şey dosya konumudur:

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

Konumla birlikte, abonelik dışı depolama hesaplarına erişirken, kapsüllenmiş kimlik doğrulama parametreleriyle veritabanı kapsamlı bir kimlik bilgilerine de başvurmanız gerekir. Aşağıdaki betik, dosya yoluna işaret eden ve veritabanı kapsamlı bir kimlik bilgilerine başvuran bir dış veri kaynağı oluşturur.

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       CREDENTIAL = [MyCredential]
);

OPENROWSET kullanarak veri kaynaklarını sorgulama

OPENROWSET söz dizimi, anlık geçici sorgulamayı etkinleştirirken yalnızca gerekli veritabanı nesnelerinin en az sayıda oluşturulmasını sağlar.

OPENROWSETdış dosya biçimini ve dış tablonun kendisini gerektiren dış tablo yaklaşımının aksine yalnızca dış veri kaynağının (ve muhtemelen kimlik bilgilerinin) oluşturulmasını gerektirir.

Parametre DATA_SOURCE değeri, dosyanın tam yolunu oluşturmak için bulk parametresine otomatik olarak eklenir.

kullanırken OPENROWSET , tek bir dosyayı sorgulayan aşağıdaki örnek gibi dosyanın biçimini sağlayın:

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

Birden çok dosya ve klasörü sorgulama

Komutu, OPENROWSET BULK yolunda joker karakterler kullanarak birden çok dosya veya klasörün sorgulanmasına da olanak tanır.

Aşağıdaki örnekte NYC sarı taksi yolculuğu kayıtları açık veri kümesi kullanılır.

İlk olarak dış veri kaynağını oluşturun:

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

Artık klasörlerdeki .parquet uzantısına sahip tüm dosyaları sorgulayabiliriz. Örneğin, burada yalnızca ad deseni ile eşleşen dosyaları sorgulayacağız:

--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;

Birden çok dosya veya klasör sorgulanırken, tek OPENROWSET ile erişilen tüm dosyaların aynı yapıya (aynı sayıda sütun ve veri türü gibi) sahip olması gerekir. Klasörler özyinelemeli olarak geçirilemiyor.

Şema çıkarımı

Otomatik şema çıkarımı, dosya şemalarını bilmediğiniz durumlarda hızlı bir şekilde sorgu yazmanıza ve verileri keşfetmenize yardımcı olur. Şema çıkarımı yalnızca parquet dosyalarıyla çalışır.

Uygun olsa da, kaynak dosyalarda uygun veri türünün kullanıldığından emin olmak için yeterli bilgi olabileceğinden, çıkarım yapılan veri türleri gerçek veri türlerinden daha büyük olabilir. Bu da düşük sorgu performansına yol açar. Örneğin, parquet dosyaları maksimum karakter sütunu uzunluğuyla ilgili meta veriler içermez, bu nedenle örnek bunu varchar(8000) olarak çıkarsar.

Aşağıdaki örnekte gösterildiği gibi, sorgunuzun sonucunda elde edilen veri türlerini denetlemek için sp_describe_first_results_set saklı yordamını kullanın:

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

Veri türlerini bildiğinizde, performansı geliştirmek için yan tümcesini WITH kullanarak bunları belirtebilirsiniz:

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;

CSV dosyalarının şeması otomatik olarak belirlenemediğinden sütunların her zaman şu yan tümcesi WITH kullanılarak belirtilmesi gerekir:

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;

Dosya meta veri işlevleri

Birden çok dosya veya klasörü sorgularken, dosya meta verilerini okumak ve sonuç kümesindeki satırın kaynaklandığı dosyanın yolunun veya tam yolunun ve adının bir kısmını almak için ve filepath() işlevlerini kullanabilirsinizfilename():

--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;

Parametresiz çağrıldığında işlev, filepath() satırın kaynaklandığı dosya yolunu döndürür. DATA_SOURCE içinde OPENROWSETkullanıldığında, yoluna göre DATA_SOURCEyolunu döndürür, aksi takdirde tam dosya yolunu döndürür.

Parametresiyle çağrıldığında, parametresinde belirtilen konumda joker karakterle eşleşen yolun bir bölümünü döndürür. Örneğin, parametre değeri 1, yolun ilk joker karakterle eşleşen bir bölümünü döndürür.

İşlev, filepath() satırları filtrelemek ve toplamak için de kullanılabilir:

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;

OPENROWSET'in üzerinde görünüm oluşturma

Temel alınan sorguyu kolayca yeniden kullanabilmek için OPENROWSET sorgularını sarmak için görünümler oluşturabilir ve kullanabilirsiniz:

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

Daha kolay ve daha performanslı filtreleme için işlevini kullanarak dosya konumu verilerini içeren sütunları bir görünüme filepath() eklemek de kullanışlıdır. Görünümlerin kullanılması, bu sütunlardan herhangi birine göre filtrelendiğinde, görünümün üstündeki sorgunun okuması ve işlemesi gereken dosya sayısını ve veri miktarını azaltabilir:

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;

Görünümler, Power BI gibi raporlama ve analiz araçlarının sonuçlarını kullanmasına OPENROWSETda olanak tanır.

Dış tablolar

Dış tablolar dosyalara erişimi kapsüller ve bu da sorgulama deneyiminin kullanıcı tablolarında depolanan yerel ilişkisel verileri sorgulamayla neredeyse aynı olmasını sağlar. Dış tablo oluşturmak için dış veri kaynağı ve dış dosya biçimi nesnelerinin mevcut olması gerekir:

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

--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
);

Dış tablo oluşturulduktan sonra, bunu diğer tüm tablolarda olduğu gibi sorgulayabilirsiniz:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Aynı gibi OPENROWSET, dış tablolar da joker karakterler kullanarak birden çok dosya ve klasörün sorgulanmasına izin verir. Şema çıkarımı dış tablolarda desteklenmez.

Performansla ilgili dikkat edilmesi gerekenler

Dosya sayısı veya sorgulanabilecek veri miktarıyla ilgili sabit bir sınır yoktur, ancak sorgu performansı veri miktarına, veri biçimine, verilerin düzenlenme biçimine ve sorguların ve birleştirmelerin karmaşıklığına bağlıdır.

Bölümlenmiş verileri sorgulama

Veriler genellikle bölümler olarak da adlandırılan alt klasörlerde düzenlenir. Sorguya yalnızca belirli klasörleri ve dosyaları okumasını sağlayabilirsiniz. Bunun yapılması, dosya sayısını ve sorgunun okuması ve işlemesi gereken veri miktarını azaltarak daha iyi performans sağlar. Bu tür bir sorgu iyileştirmesi, bölüm ayıklama veya bölüm eleme olarak bilinir. Sorgunun yan tümcesindeki filepath() meta veri işlevini WHERE kullanarak bölümleri sorgu yürütmeden kaldırabilirsiniz.

Aşağıdaki örnek sorgu, NYC Yellow Taxi veri dosyalarını yalnızca 2017'nin son üç ayı için okur:

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;

Depolanan verileriniz bölümlenmemişse, sorgu performansını geliştirmek için bu verileri bölümlendirebilirsiniz.

Eğer dış tablolar kullanıyorsanız, filepath() ve filename() işlevleri desteklenir ancak WHERE cümlesinde desteklenmez.

Sorun giderme

Sorgu yürütmeyle ilgili sorunlar genellikle Azure SQL Veritabanı'nın dosya konumuna erişemediğinden kaynaklanmaktadır. İlgili hata iletileri yetersiz erişim hakları, var olmayan konum veya dosya yolu, başka bir işlem tarafından kullanılan dosya veya bu dizin listelenemez. Çoğu durumda bu, dosyalara erişimin ağ trafiği denetim ilkeleri tarafından veya erişim haklarının olmaması nedeniyle engellendiğini gösterir. Denetlenmesi gereken budur:

  • Yanlış veya yazım hatası içeren konum yolu.
  • SAS anahtarı geçerliliği: Soru işaretiyle başlayarak yazım hatası içeren süresi dolmuş olabilir.
  • İzin verilen SAS anahtarı izinleri: En az okuma ve joker karakterler kullanılıyorsa listeleme .
  • Depolama hesabında gelen trafik engellendi. Azure Depolama için sanal ağ kurallarını yönetme'ye bakın.
  • Yönetilen Kimlik erişim hakları: Azure SQL Veritabanı'nın yönetilen kimliğine depolama hesabı için erişim hakları verildiğinden emin olun.
  • Veri sanallaştırma sorgularının çalışması için veritabanının uyumluluk düzeyi 130 veya daha yüksek olmalıdır.

Sınırlamalar

  • Şu anda Azure SQL Veritabanı'nda dış tablolarla ilgili istatistikler desteklenmemektedir.
  • CREATE EXTERNAL TABLE AS SELECT Şu anda Azure SQL Veritabanı'nda kullanılamıyor.
  • Satır düzeyi güvenlik özelliği dış tablolarda desteklenmez.
  • Dış tablodaki bir sütun için dinamik veri maskeleme kuralı tanımlanamaz.
  • Yönetilen Kimlik, kiracılar arası senaryoları desteklemez. Azure Depolama Hesabı farklı bir kiracıdaysa Paylaşılan erişim imzası desteklenen yöntemdir.

Bilinen sorunlar

  • SQL Server Management Studio'da (SSMS) Always Encrypted parametresi etkinleştirildiğinde veri sanallaştırma sorguları hata iletisiyle Incorrect syntax near 'PUSHDOWN' başarısız olur.

İlgili içerik

  • Last updated on