Aracılığıyla paylaş

SQL Server 2022'de PolyBase'i kullanmaya başlama

Şunlar için geçerlidir: Windows üzerinde SQL Server 2016 (13.x) ve sonraki sürümler Linux üzerinde SQL Server 2017 (14.x) ve sonraki sürümler

Bu makale, SQL Server 2022'de (16.x) PolyBase ile birden çok klasör ve dosyayla çalışma öğreticisinde size yol gösterir. Bu öğretici sorguları kümesi PolyBase'in çeşitli özelliklerini gösterir.

SQL Server'da PolyBase ile veri sanallaştırma , birden çok klasör, dosya sorgulamak veya klasör eleme gerçekleştirmek için meta veri dosyası işlevlerinden yararlanmanızı sağlar. Şema bulma ile klasör ve dosya elemenin birleşimi, SQL'in herhangi bir Azure Depolama Hesabından veya S3 uyumlu nesne depolama çözümünden yalnızca gerekli verileri getirmesine olanak tanıyan güçlü bir özelliktir.

Önkoşullar

Bu öğreticide PolyBase'i kullanmadan önce şunları kullanmanız gerekir:

  1. Windows'a PolyBase'iveya Linux'a PolyBase'i yükleyin.
  2. Gerekirse sp_configure'de PolyBase'i etkinleştirin.
  3. Dış ağların pandemicdatalake.blob.core.windows.net ve azureopendatastorage.blob.core.windows.net'de genel kullanıma açık Azure Blob depolamaya erişmesine izin verin.

Örnek veri kümeleri

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:

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;

İlk sorgunun sonuç kümesini temel alarak , WHERE ve diğer T-SQL yan tümcelerini ekleyerek GROUP BYveri kümesi keşfine devam edebilirsiniz.

SQL Server örneğinizde ilk sorgu başarısız olursa, genel Azure depolama hesabına ağ erişimi engellenir. Sorgulamaya devam etmeden önce erişimi etkinleştirmek için ağ uzmanınızla görüşün.

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.

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

Uyarı

46530 hata iletisi alırsanız SQL External data sources are not supported with type GENERIC, Server örneğinizdeki yapılandırma seçeneğini PolyBase Enabled denetleyin. Olmalıdır 1.

SQL Server örneğinizde PolyBase'i etkinleştirmek için aşağıdakileri çalıştırın:

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

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://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
        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:

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.

sys.sp_describe_first_results_set Aşağıdaki örnek gibi sorgunuzun elde edilen veri türlerini denetlemek için saklı yordamı 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, filepath() ve filename() fonksiyonlarını kullanarak dosya meta verilerini okuyabilir ve sonuç kümesindeki satırın kaynaklandığı dosyanın yolunun bir kısmını veya tam yolunu ve adını alabilirsiniz. Aşağıdaki örnekte, her satır için tüm dosyaları ve proje dosya yolunu ve dosya adı bilgilerini sorgula:

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

  • 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.

  • Bir parametreyle çağrıldığında işlev, parametrede filepath() belirtilen konumda joker karakterle eşleşen yolun bir bölümünü döndürür. Örneğin, ilk parametre değeri 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

Sorguları sarmalayan görünümler oluşturabilirsiniz, böylece temel alınan sorguyu kolayca yeniden kullanabilirsiniz. Görünümler, Power BI gibi raporlama ve analiz araçlarının OPENROWSET sonuçlarını kullanmasına da olanak tanır.

Örneğin, bir OPENROWSET komuta göre aşağıdaki görünümü göz önünde bulundurun:

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;

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

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

SELECT TOP 10 * 
FROM tbl_TaxiRides;

OpenROWSET'te olduğu gibi dış tablolar da joker karakterler kullanarak birden çok dosya ve klasörün sorgulanmasına olanak tanır. Şema çıkarımı dış tablolarda desteklenmez.

Dış veri kaynakları

Çeşitli veri kaynakları için dış veri kaynakları ve tablolar oluşturma hakkında daha fazla öğretici için bkz. PolyBase Transact-SQL başvurusu.

Çeşitli dış veri kaynakları hakkında daha fazla eğitim için şunları inceleyin:

İlgili içerik

  • Last updated on