Synapse SQL ile dış tabloları kullanma

Dış tablo Hadoop, Azure Depolama blobu veya Azure Data Lake Storage'da (ADLS) bulunan verileri gösterir.

Azure Depolama'daki dosyalardan veri okumak veya dosyalara veri yazmak için dış tabloları kullanabilirsiniz. Azure Synapse SQL ile dış tabloları kullanarak ayrılmış SQL havuzu veya sunucusuz SQL havuzu kullanarak dış verileri okuyabilirsiniz.

Dış veri kaynağının türüne bağlı olarak, iki tür dış tablo kullanabilirsiniz:

• CSV, Parquet ve ORC gibi çeşitli veri biçimlerindeki verileri okumak ve dışarı aktarmak için kullanabileceğiniz Hadoop dış tabloları . Hadoop dış tabloları ayrılmış SQL havuzlarında kullanılabilir, ancak sunucusuz SQL havuzlarında kullanılamaz.
• CSV ve Parquet gibi çeşitli veri biçimlerinde verileri okumak ve dışarı aktarmak için kullanabileceğiniz yerel dış tablolar . Yerel dış tablolar sunucusuz SQL havuzlarında ve ayrılmış SQL havuzlarında kullanılabilir. CETAS ve yerel dış tabloları kullanarak veri yazma/dışarı aktarma yalnızca sunucusuz SQL havuzunda kullanılabilir, ancak ayrılmış SQL havuzlarında kullanılamaz.

Hadoop ile yerel dış tablolar arasındaki temel farklar:

Dış tablo türü	Hadoop	Yerli
Ayrılmış SQL havuzu	Mevcut	Yalnızca Parquet
Sunucusuz SQL havuzu	Kullanılamaz	Mevcut
Desteklenen biçimler	Sınırlandırılmış/CSV, Parquet, ORC, Hive RC ve RC	Sunucusuz SQL havuzu: Sınırlandırılmış/CSV, Parquet ve Delta Lake Ayrılmış SQL havuzu: Parquet
Klasör bölme eleme	Hayır	Bölüm kaldırma yalnızca Apache Spark havuzları ile senkronize edilen Parquet veya CSV biçimlerinde oluşturulan bölümlenmiş tablolarda kullanılabilir. Parquet ile bölümlendirilmiş klasörlerde dış tablolar oluşturabilirsiniz, ancak bölümleme sütunlarına erişilemez ve yoksayılır, ayrıca bölüm eleme uygulanmaz. Delta Lake klasörlerinde dış tablolar oluşturmayın çünkü bunlar desteklenmez. Bölümlenmiş Delta Lake verilerini sorgulamanız gerekiyorsa Delta bölümlenmiş görünümlerini kullanın.
Dosya eleme (koşul indirme)	Hayır	Sunucusuz SQL havuzunda evet. Dizenin aşağı itilmesi için, aşağı itme özelliğini etkinleştirmek için sütunlarda Latin1_General_100_BIN2_UTF8 harmanlama kullanmanız VARCHAR gerekir. Harmanlamalar hakkında daha fazla bilgi için bkz . Azure Synapse Analytics'te Synapse SQL için veritabanı harmanlama desteği.
Konum için özel biçim	Hayır	Evet, Parquet veya CSV biçimleri için /year=*/month=*/day=* gibi joker karakterler kullanmak. Delta Lake'te özel klasör yolları kullanılamaz. Sunucusuz SQL havuzunda, referans verilen klasörün altındaki herhangi bir alt klasördeki Parquet veya CSV dosyalarına başvurmak için özyinelemeli joker karakterleri /logs/** kullanabilirsiniz.
Özyinelemeli klasör taraması	Yes	Evet. Sunucusuz SQL havuzlarında konum yolunun sonunda belirtilmelidir /** . Ayrılmış havuzda klasörler her zaman yenilenerek taranır.
Veri depolama kimlik doğrulaması	Depolama Erişim Anahtarı(SAK), Microsoft Entra geçişi, Yönetilen kimlik, özel uygulama Microsoft Entra kimliği	Paylaşılan Erişim İmzası (SAS), Microsoft Entra geçişi, Yönetilen kimlik, Özel uygulama Microsoft Entra kimliği.
Sütun eşlemesi	Sıra - Dış tablo tanımındaki sütunlar, temel alınan Parquet dosyalarındaki sütunlara konuma göre eşlenir.	Sunucusuz havuz: isme göre. Dış tablo tanımındaki sütunlar, temel alınan Parquet dosyalarındaki sütunlara sütun adı eşleşmesine göre eşlenir. Ayrılmış havuz: sıra tabanlı eşleştirme. Dış tablo tanımındaki sütunlar, temel alınan Parquet dosyalarındaki sütunlara konuma göre eşlenir.
CETAS (dışarı aktarma/dönüştürme)	Yes	Hedef olarak yerel tablolara sahip CETAS yalnızca sunucusuz SQL havuzunda çalışır. Yerel tabloları kullanarak verileri dışarı aktarmak için ayrılmış SQL havuzlarını kullanamazsınız.

Not

Yerel dış tablolar, genel kullanıma sunuldukları havuzlarda önerilen çözümlerdir. Dış verilere erişmeniz gerekiyorsa, her zaman sunucusuz veya ayrılmış havuzlardaki yerel tabloları kullanın. Hadoop tablolarını yalnızca yerel dış tablolarda desteklenmeyen bazı türlere (örneğin - ORC, RC) erişmeniz gerekiyorsa veya yerel sürüm kullanılamıyorsa kullanın.

Ayrılmış SQL havuzu ve sunucusuz SQL havuzundaki dış tablolar

Dış tabloları kullanarak şunları yapabilirsiniz:

• Transact-SQL deyimleriyle Azure Blob Depolama ve ADLS 2. Nesil'i sorgula.
• Synapse SQL ile CETAS kullanarak sorgu sonuçlarını Azure Blob Depolama veya Azure Data Lake Storage'da depolayın.
• Azure Blob Depolama ve Azure Data Lake Storage'dan verileri içeri aktarıp ayrılmış bir SQL havuzunda depolayın (yalnızca ayrılmış havuzdaki Hadoop tabloları).

Not

CREATE TABLE AS SELECT deyimiyle kullanıldığında, dış tablodan seçim yapıldığında veriler ayrılmış SQL havuzu içindeki bir tabloya aktarılır.

Ayrılmış havuzlardaki Hadoop dış tablolarının performansı performans hedeflerinizi karşılamıyorsa COPY deyimini kullanarak dış verileri Veri ambarı tablolarına yüklemeyi göz önünde bulundurun.

Yükleme konusunda daha fazla bilgi için Azure Blob Depolama'dan veri yüklemek için PolyBase kullanma öğreticisini okuyun.

Aşağıdaki adımları izleyerek Synapse SQL havuzlarında dış tablolar oluşturabilirsiniz:

1. Dış Azure depolama alanına başvurmak ve depolama alanına erişmek için kullanılacak kimlik bilgilerini belirtmek için EXTERNAL DATA SOURCE OLUŞTURUN.
2. CSV veya Parquet dosyalarının biçimini açıklamak için EXTERNAL FILE FORMAT OLUŞTURUN.
3. Aynı dosya biçimine sahip veri kaynağına yerleştirilen dosyaların üzerinde EXTERNAL TABLE OLUŞTURUN.

Klasör bölümleme kaldırma

Synapse havuzlarındaki yerel dış tablolar, sorgular için uygun olmayan klasörlere yerleştirilen dosyaları yoksayabiliyor. Dosyalarınız bir klasör hiyerarşisinde (örneğin - /year=2020/month=03/day=16) depolanıyorsa ve değerleri year, monthve day sütunları olarak gösteriliyorsa, gibi year=2020 filtreler içeren sorgular dosyaları yalnızca klasöre year=2020 yerleştirilen alt klasörlerden okur. Diğer klasörlere (year=2021 veya year=2022) yerleştirilen dosya ve klasörler bu sorguda yoksayılır. Bu eleme, bölüm eleme olarak bilinir.

Klasör bölümü kaldırma, Synapse Spark havuzlarından eşitlenmiş yerel dış tablolarda mevcuttur. Bölümlenmiş veri kümeniz varsa ve oluşturduğunuz dış tablolarla bölüm eleme özelliğini kullanmak istiyorsanız, dış tablolar yerine bölümlenmiş görünümleri kullanın.

Dosya silme

Parquet ve Delta gibi bazı veri biçimleri her sütun için dosya istatistikleri içerir (örneğin, her sütun için en küçük/en yüksek değerler). Verileri filtreleyen sorgular, gerekli sütun değerlerinin mevcut olmadığı dosyaları okumaz. Sorgu, gerekli verileri içermeyen dosyaları bulmak için önce sorgu koşulunda kullanılan sütunların en küçük/en yüksek değerlerini keşfeder. Bu dosyalar görmezden gelinir ve sorgu planından kaldırılır. Bu teknik, filtre koşulu itmesi olarak da bilinir ve sorgularınızın daha hızlı çalışmasını sağlar. Filtre gönderimi Parquet ve Delta biçimlerindeki sunucusuz SQL havuzlarında kullanılabilir. Dize türleri için filtre itme uygulamak amacıyla Latin1_General_100_BIN2_UTF8 harmanlama ile VARCHAR türünü kullanın. Harmanlamalar hakkında daha fazla bilgi için bkz . Azure Synapse Analytics'te Synapse SQL için veritabanı harmanlama desteği.

Güvenlik

Kullanıcının verileri okuyabilmesi için bir dış tablo üzerinde SELECT iznine sahip olması gerekir. Dış tablolar, veri kaynağında tanımlanan veritabanı kapsamlı kimlik bilgilerini kullanarak, aşağıdaki kurallara göre temel alınan Azure depolamaya erişir:

• Kimlik bilgisi olmayan veri kaynağı, dış tabloların Azure depolamada genel kullanıma açık dosyalara erişmesini sağlar.
• Veri kaynağı, dış tabloların SAS belirteci veya çalışma alanı Yönetilen Kimlik kullanarak yalnızca Azure depolamadaki dosyalara erişmesini sağlayan kimlik bilgilerine sahip olabilir - Örnekler için, Depolama dosyalarının erişim denetimi için geliştirme makalesine bakın.

Açıklamalar

Güvenilir sorgu yürütmeyi sağlamak için, dış tablolar tarafından başvuruda bulunan kaynak dosya ve klasörlerin işlem süresi boyunca değişmeden kalması gerekir.

• Sorgu çalışırken başvuruda bulunan dosya veya klasörlerin değiştirilmesi, silinmesi veya değiştirilmesi hatalara neden olabilir veya tutarsız sonuçlara yol açabilir.
• Ayrılmış sql havuzundaki dış tabloları sorgulamadan önce, tüm kaynak verilerin kararlı olduğunu ve yürütme sırasında değiştirilmeyeceğini doğrulayın.

"CREATE EXTERNAL DATA SOURCE" örneği

Hadoop

Aşağıdaki örnek, ADLS 2. Nesil için ayrılmış SQL havuzunda genel New York veri kümesine işaret eden bir Hadoop dış veri kaynağı oluşturur:

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2022-11-02&ss=b&srt=co&sp=rl&se=2042-11-26T17:40:55Z&st=2024-11-24T09:40:55Z&spr=https&sig=DKZDuSeZhuCWP9IytWLQwu9shcI5pTJ%2Fw5Crw6fD%2BC8%3D'
GO
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

Aşağıdaki örnek, ADLS 2. Nesil için genel kullanıma açık New York veri kümesine işaret eden bir dış veri kaynağı oluşturur:

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

Yerli

Aşağıdaki örnek, ADLS 2. Nesil için sunucusuz veya ayrılmış SQL havuzunda SAS kimlik bilgileri kullanılarak erişilebilen bir dış veri kaynağı oluşturur:

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2022-11-02&ss=b&srt=co&sp=rl&se=2042-11-26T17:40:55Z&st=2024-11-24T09:40:55Z&spr=https&sig=DKZDuSeZhuCWP9IytWLQwu9shcI5pTJ%2Fw5Crw6fD%2BC8%3D'
GO
CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

Not

SQL kullanıcılarının Azure Synapse Analytics Sunucusuz SQL Havuzu'ndaki veri kaynağına erişmek için veritabanı kapsamlı kimlik bilgileri üzerinde uygun izinlere sahip olması gerekir. Azure Synapse Analytics'te sunucusuz SQL havuzunu kullanarak dış depolamaya erişin.

Aşağıdaki örnek, ADLS 2. Nesil için genel kullanıma açık New York veri kümesine işaret eden bir dış veri kaynağı oluşturur:

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

CREATE EXTERNAL FILE FORMAT için örnek

Aşağıdaki örnek, census dosyaları için bir dış dosya biçimi oluşturur:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

Örnek CREATE EXTERNAL TABLE

Aşağıdaki örnek bir dış tablo oluşturur. İlk satırı döndürür:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Azure Data Lake'te bir dosyadan dış tablolar oluşturma ve sorgulama

Synapse Studio'nun Data Lake keşif özelliklerini kullanarak artık dosyaya sağ tıklayarak Synapse SQL havuzunu kullanarak bir dış tablo oluşturabilir ve sorgulayabilirsiniz. ADLS 2. Nesil depolama hesabından dış tablo oluşturmak için tek tıklama hareketi yalnızca Parquet dosyaları için desteklenir.

Önkoşullar

• ADLS 2. Nesil hesabına veya dosyaları sorgulamanızı sağlayan Erişim Denetim Listelerine (ACL) erişebilmek için, en az Storage Blob Data Contributor rolüne sahip olmanız gereken çalışma alanına erişiminiz olmalıdır.

• Synapse SQL havuzunda (ayrılmış veya sunucusuz) dış tablo oluşturmak ve dış tabloları sorgulamak için en azından izinleriniz olmalıdır.

Veri panelinde dış tabloyu oluşturmak istediğiniz dosyayı seçin:

Bir iletişim kutusu penceresi açılır. Ayrılmış SQL havuzu veya sunucusuz SQL havuzu'nu seçin, tabloya bir ad verin ve betiği aç'ı seçin:

SQL Betiği, şema dosyasından çıkarılarak otomatik olarak oluşturulur:

Betiği çalıştırın. Betik otomatik olarak bir SELECT TOP 100 * çalıştırır.

Dış tablo artık oluşturuldu. Artık dış tabloyu doğrudan Veri bölmesinden sorgulayabilirsiniz.

İlgili içerik

Sorgu sonuçlarını Azure Depolama'daki bir dış tabloya kaydetme hakkında CETAS makalesine bakın. İsterseniz Azure Synapse dış tabloları için Apache Spark'ı sorgulamaya da başlayabilirsiniz.