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

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.

Note

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
Kıvılcım	Evet	Hayır
Dataverse	Evet	Hayır
Azure Cosmos DB veri biçimleri (JSON, BSON vb.)	Hayır (Alternatif olarak görünümler oluşturun)	Hayır

Önkoşullar

İlk adımınız tabloların oluşturulacağı bir veritabanı oluşturmaktır. Kimlik bilgilerini korumak için veritabanının 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:

• Genel tanıtım depolama hesabına başvuran EXTERNAL DATA SOURCE sqlondemanddemo, ve https://azureopendatastorage.blob.core.windows.net/nyctlc/ konumunda genel kullanıma açık Azure depolama hesabına başvuran EXTERNAL DATA SOURCE nyctlc.

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

• File formats QuotedCSVWithHeaderFormat and ParquetFormat that describe CSV and parquet file types.

  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, sqlondemanddemo veri kaynağı kullanılarak başvuruda bulunulan SynapseSQL demo Azure depolama hesabından population.csv dosyasını okuyan bir dış tablo oluşturur.

Note

Oluşturduğunuz veritabanını kullanmak için sorgudaki ilk satırı ([mydbname] ) 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.

Dosya kümesi üzerinde harici 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.

Note

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.

External table on appendable files

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 meydana gelen sorgu hatalarından kaçınmak için, TABLE_OPTIONS ayarını kullanarak dış tablonun muhtemel tutarsız 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.

Note

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ştirme yoluyla temel alınan dosyaların birden çok kez 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 harici tablo

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.

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

Dış tablolar bölümlenmiş bir klasörde 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ümlendirmeyi 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. Azure desteği bölümlenmiş klasörlerde tablo kullanıyorsa 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 kullan

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.

Note

Oluşturduğunuz veritabanını kullanmak için sorgudaki ilk satırı ([mydbname] ) 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ım

Sorgu sonuçlarını depolama alanına depolama