Azure Synapse Analytics'te SQL havuzlarını kullanarak yerel dış tablolar oluşturma ve kullanma

Makale
10/18/2023

Bu bölümde Synapse SQL havuzlarında yerel dış tablolar oluşturmayı ve kullanmayı öğreneceksiniz. Yerel dış tablolar, dış veri kaynağı tanımında bulunan dış tablolarla TYPE=HADOOP karşılaştırıldığında daha iyi performansa sahiptir. Bunun nedeni, yerel dış tabloların dış verilere erişmek için yerel kod kullanmasıdır.

Dış tablolar, Synapse SQL havuzundaki dış verilere erişimi denetlemek istediğinizde kullanışlıdır. Dış tablolar, Synapse SQL havuzuyla birlikte Power BI gibi araçları kullanmak istediğinizde de kullanışlıdır. Dış tablolar iki tür depolamaya erişebilir:

Kullanıcıların ortak depolama dosyalarına eriştiği genel depolama.
Kullanıcıların SAS kimlik bilgilerini, Microsoft Entra kimliğini veya Synapse çalışma alanının Yönetilen Kimliğini kullanarak depolama dosyalarına eriştiği korumalı depolama.

Dekont

Ayrılmış SQL havuzlarında yalnızca Parquet dosya türüne sahip yerel dış tabloları kullanabilirsiniz ve bu özellik genel önizleme aşamasındadır. Ayrılmış SQL havuzlarında genel kullanıma açık Parquet okuyucu işlevselliğini kullanmak istiyorsanız veya CSV veya ORC dosyalarına erişmeniz gerekiyorsa Hadoop dış tablolarını kullanın. Yerel dış tablolar genellikle sunucusuz SQL havuzlarında kullanılabilir. Synapse SQL ile dış tablolar kullanma bölümünde yerel ve Hadoop dış tabloları arasındaki farklar hakkında daha fazla bilgi edinin.

Aşağıdaki tabloda desteklenen veri biçimleri listelenmektedir:

Veri biçimi (Yerel dış tablolar)	Sunucusuz SQL havuzu	Ayrılmış SQL havuzu
Parquet	Evet (GA)	Evet (genel önizleme)
CSV	Evet	Hayır (Alternatif olarak Hadoop dış tablolarını kullanın)
delta	Evet	Hayır
Spark	Evet	Hayır
Dataverse	Evet	Hayır
Azure Cosmos DB veri biçimleri (JSON, BSON vb.)	Hayır (Alternatif olarak görünüm oluşturma)	Hayır

Ön koşullar

İlk adımınız tabloların oluşturulacağı bir veritabanı oluşturmaktır. Veritabanı kapsamlı bir kimlik bilgisi oluşturmadan önce, veritabanının kimlik bilgilerini korumak için bir ana anahtarı olmalıdır. Bu konuda daha fazla bilgi için bkz . CREATE MASTER KEY (Transact-SQL). Ardından bu örnekte kullanılan aşağıdaki nesneleri oluşturun:

SAS korumalı https://sqlondemandstorage.blob.core.windows.net Azure depolama hesabına erişim sağlayan DATABASE SCOPED CREDENTIALsqlondemand.

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
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'

SAS anahtarıyla korunan demo depolama hesabına başvuran EXTERNAL DATA SOURCE sqlondemanddemo ve konumunda https://azureopendatastorage.blob.core.windows.net/nyctlc/genel kullanıma açık Azure depolama hesabına başvuran EXTERNAL DATA SOURCEnyctlc.

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);
GO
CREATE EXTERNAL DATA SOURCE nyctlc
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
GO
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );

CSV ve ParquetFormat parquet dosya türlerini açıklayan dosya biçimleriQuotedCSVWithHeaderFormat.

CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
GO

Bu makaledeki sorgular örnek veritabanınızda yürütülür ve bu nesneleri kullanır.

Dosyadaki dış tablo

Bazı Microsoft Entra kimliğine veya SAS anahtarına sahip kullanıcılara erişim sağlayan bir Azure depolama hesabındaki verilere erişen dış tablolar oluşturabilirsiniz. Dış tabloları, normal SQL Server dış tablolarını oluşturduğunuz gibi oluşturabilirsiniz.

Aşağıdaki sorgu, veri kaynağı kullanılarak sqlondemanddemo başvurulan ve adlı sqlondemandveritabanı kapsamlı kimlik bilgileriyle korunan SynapseSQL demo Azure depolama hesabından population.csv dosyasını okuyan bir dış tablo oluşturur.

Veri kaynağı ve veritabanı kapsamlı kimlik bilgileri kurulum betiğinde oluşturulur.

Dekont

Oluşturduğunuz veritabanını kullanmak için sorgudaki ilk satırı ([mydbname] gibi) değiştirin.

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

Yerel CSV tabloları şu anda yalnızca sunucusuz SQL havuzlarında kullanılabilir.

Bir dosya kümesindeki dış tablo

Azure depolama alanına yerleştirilen bir dosya kümesinden verileri okuyan dış tablolar oluşturabilirsiniz:

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

Dış tablo tarafından başvurulabilmesi için dosyaların karşılaması gereken düzeni belirtebilirsiniz. Desen yalnızca Parquet ve CSV tabloları için gereklidir. Delta Lake biçimini kullanıyorsanız, yalnızca bir kök klasör belirtmeniz gerekir ve dış tablo deseni otomatik olarak bulur.

Dekont

Tablo bölümlenmiş klasör yapısında oluşturulur, ancak bölüm elemeden yararlanamazsınız. Bazı ölçütleri karşılamayan dosyaları atlayarak daha iyi performans elde etmek istiyorsanız (bu örnekte belirli bir yıl veya ay gibi), dış verilerdeki görünümleri kullanın.

Eklenebilir dosyalarda dış tablo

Dış tablo tarafından başvuruda bulunılan dosyalar, sorgu çalışırken değiştirilmemelidir. Uzun süre çalışan sorguda SQL havuzu okumaları yeniden deneyebilir, dosyaların bölümlerini okuyabilir, hatta dosyayı birden çok kez okuyabilir. Dosya içeriğinde yapılan değişiklikler yanlış sonuçlara neden olabilir. Bu nedenle, sorgu yürütme sırasında herhangi bir dosyanın değişiklik zamanının değiştiğini algılarsa SQL havuzu sorguda başarısız olur. Bazı senaryolarda, sürekli eklenen dosyalarda bir tablo oluşturmak isteyebilirsiniz. Sürekli eklenen dosyalar nedeniyle sorgu hatalarından kaçınmak için, dış tablonun ayarı kullanarak TABLE_OPTIONS tutarsız olabilecek okumaları yoksayması gerektiğini belirtebilirsiniz.

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

ALLOW_INCONSISTENT_READS Okuma seçeneği, sorgu yaşam döngüsü sırasında dosya değiştirme süresi denetimini devre dışı bırakır ve dış tablo tarafından başvuruda bulunulan dosyalarda kullanılabilir olan her şeyi okur. Eklenebilir dosyalarda, mevcut içerik güncelleştirilmez ve yalnızca yeni satırlar eklenir. Bu nedenle, güncelleştirilebilir dosyalara kıyasla yanlış sonuç olasılığı en aza indirilir. Bu seçenek, hataları işlemeden sık eklenen dosyaları okumanızı sağlayabilir.

Bu seçenek yalnızca CSV dosya biçiminde oluşturulan dış tablolarda kullanılabilir.

Dekont

Seçenek adından da anlaşılacağı gibi, tablonun oluşturucusu sonuçların tutarlı olmayabileceği riskini kabul eder. Eklenebilir dosyalarda, tabloyu kendi kendine birleştirerek temel alınan dosyaların birden çok okunmasını zorlarsanız yanlış sonuçlar alabilirsiniz. "Klasik" sorguların çoğunda, dış tablo yalnızca sorgu çalışırken eklenen bazı satırları yoksayar.

Delta Lake dış tablosu

Dış tablolar Delta Lake klasörünün üzerinde oluşturulabilir. Tek bir dosya veya dosya kümesinde oluşturulan dış tablolar ile Delta Lake biçiminde oluşturulan dış tablolar arasındaki tek fark, Delta Lake dış tablosunda Delta Lake yapısını içeren bir klasöre başvurmanız gerektiğidir.

ECDC COVID-19 Delta Lake folder

Delta Lake klasöründe oluşturulan tablo tanımına örnek olarak:

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

Bölümlenmiş bir klasörde dış tablolar oluşturulamaz. Synapse sunucusuz SQL havuzu kendi kendine yardım sayfasındaki diğer bilinen sorunları gözden geçirin.

Bölümlenmiş klasörlerdeki delta tabloları

Sunucusuz SQL havuzlarındaki dış tablolar Delta Lake biçiminde bölümlemesi desteklemez. Delta Lake veri kümelerini bölümlediyseniz tablolar yerine Delta bölümlenmiş görünümlerini kullanın.

Önemli

Bazı durumlarda işe yarayabileceklerini görseniz bile bölümlenmiş Delta Lake klasörlerinde dış tablolar oluşturmayın. Bölümlenmiş delta klasörlerindeki dış tablolar gibi desteklenmeyen özelliklerin kullanılması sunucusuz havuzun sorunlarına veya kararlı olmamasına neden olabilir. bölümlenmiş klasörlerde tablo kullanıyorsa Azure desteği hiçbir sorunu çözemez. Sorun çözme işlemine devam etmeden önce Delta bölümlenmiş görünümlerine geçmeniz ve kodunuzu yalnızca desteklenen özelliği kullanacak şekilde yeniden yazmanız istenir.

Dış tablo kullanma

Dış tabloları sorgularınızda SQL Server sorgularında kullandığınız gibi kullanabilirsiniz.

Aşağıdaki sorgu, önceki bölümde oluşturduğumuz popülasyon dış tablosunu kullanarak bunu gösterir. 2019'da nüfuslarıyla birlikte ülke/bölge adlarını azalan düzende döndürür.

Dekont

Oluşturduğunuz veritabanını kullanmak için sorgudaki ilk satırı ([mydbname] gibi) değiştirin.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

Bu sorgunun performansı bölgeye bağlı olarak değişebilir. Çalışma alanınız, bu örneklerde kullanılan Azure depolama hesaplarıyla aynı bölgeye yerleştirilmeyebilir. Üretim iş yükleri için Synapse çalışma alanınızı ve Azure depolama alanınızı aynı bölgeye yerleştirin.

Sonraki adımlar

Bir sorgunun sonuçlarını depolama alanına depolama hakkında bilgi için Depolama makalesine yönelik Sorgu sonuçlarını depolama makalesine bakın.

Azure Synapse Analytics'te SQL havuzlarını kullanarak yerel dış tablolar oluşturma ve kullanma

Ön koşullar

Dosyadaki dış tablo

Bir dosya kümesindeki dış tablo

Eklenebilir dosyalarda dış tablo

Delta Lake dış tablosu

Bölümlenmiş klasörlerdeki delta tabloları

Dış tablo kullanma

Sonraki adımlar

Ek kaynaklar