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.

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

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

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.

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.

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.

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.

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.

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.