Azure Synapse Analytics'te ayrılmış bir SQL havuzuna veri yüklemek için en iyi yöntemler

  • Makale

Bu makalede, verileri yüklemeye yönelik öneriler ve performans iyileştirmeleri bulacaksınız.

Azure Depolama'da verileri hazırlama

Gecikme süresini en aza indirmek için depolama katmanınızı ve ayrılmış SQL havuzunuzu birlikte yazın.

Verileri ORC Dosya Biçimi’ne aktarırken, verilerde büyük metin sütunları varsa Java belleği yetersiz hatası gibi hatalar alabilirsiniz. Bu sınırlama için bir geçici çözüm olarak, sütunların yalnızca bir alt kümesini dışarı aktarın.

PolyBase, 1.000.000 bayttan fazla veri içeren satırları yükleyemez. Azure Blob depolama veya Azure Data Lake Store’da verileri metin dosyalarına yerleştirdiğinizde, dosyaların 1.000.000 bayttan daha az veri içermesi gerekir. Bu bayt sınırlaması, tablo şemasından bağımsız olarak geçerlidir.

Tüm dosya biçimleri farklı performans özelliklerine sahiptir. En hızlı yükleme için, sıkıştırılan sınırlandırılmış metin dosyaları kullanın. UTF-8 ve UTF-16 arasındaki performans farkı azdır.

Büyük sıkıştırılmış dosyaları daha küçük sıkıştırılmış dosyalara bölün.

Yeterli işlemle yük çalıştırma

En yüksek yükleme hızı için aynı anda yalnızca bir yük işi çalıştırın. Bunu yapmak uygun değilse, en az sayıda yükü eşzamanlı olarak çalıştırın. Büyük bir yükleme işi bekliyorsanız, yüklemeden önce ayrılmış SQL havuzunuzun ölçeğini artırmayı göz önünde bulundurun.

Yükleri uygun işlem kaynaklarıyla çalıştırmak için, yükleri çalıştırmaya ayrılmış yükleme kullanıcıları oluşturun. Her yükleme kullanıcısını belirli bir kaynak sınıfına veya iş yükü grubuna atayın. Yük çalıştırmak için, yükleme kullanıcılarından biri olarak oturum açın ve yükü çalıştırın. Yük, kullanıcının kaynak sınıfıyla çalıştırılır. Bu yöntem bir kullanıcının kaynak sınıfını geçerli kaynak sınıfının ihtiyacına uygun olarak değiştirmeye çalışmaktan daha basittir.

Yükleme kullanıcısı oluşturma

Bu örnek, belirli bir iş yükü grubuna sınıflandırılmış bir yükleme kullanıcısı oluşturur. İlk adım, ana öğeye bağlanmak ve oturum açma bilgisi oluşturmaktır.

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

Ayrılmış SQL havuzuna bağlanın ve bir kullanıcı oluşturun. Aşağıdaki kod, mySampleDataWarehouse adlı veritabanına bağlı olduğunuzu varsayar. Yükleyici adlı bir kullanıcının nasıl oluşturulacağını gösterir ve kullanıcıya COPY deyimini kullanarak tablo oluşturma ve yükleme izinleri verir. Ardından kullanıcıyı en fazla kaynak içeren DataLoads iş yükü grubuna sınıflandırır.

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );



Önemli

Bu, SQL havuzunun %100 kaynağını tek bir yüke ayırmanın aşırı bir örneğidir. Bu size en fazla 1 eşzamanlılık verir. Bunun yalnızca iş yüklerinizdeki kaynakları dengelemek için kendi yapılandırmalarına sahip ek iş yükü grupları oluşturmanız gereken ilk yük için kullanılması gerektiğini unutmayın.

Yük yükleme iş yükü grubunun kaynaklarıyla yük çalıştırmak için yükleyici olarak oturum açın ve yükü çalıştırın.

Birden çok kullanıcının yüklenmesine izin ver

Genellikle bir veri ambarına veri yükleyebilen birden çok kullanıcı olması gerekir. CREATE TABLE AS SELECT (Transact-SQL) ile yüklemek için veritabanının CONTROL izinleri gerekir. CONTROL izinleri tüm şemalara denetim erişimi verir. Tüm yükleme kullanıcılarının tüm şemalarda denetim erişimine sahip olmasını istemeyebilirsiniz. İzinleri sınırlandırmak için, DENY CONTROL deyimini kullanabilirsiniz.

Örneğin, A departmanı için schema_A ve B departmanı için schema_B adında veritabanı şemaları olduğunu düşünelim. user_A ve user_B adlı veritabanı kullanıcıları sırayla A ve B departmanları için PolyBase yükleme kullanıcıları olsun. Her ikisine de CONTROL veritabanı izinleri verilmiştir. A ve B şemalarını oluşturanlar DENY kullanarak bu şemaları kilitler:

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

User_A ve user_B artık diğer departmanın şemasından kilitlenmiş durumda.

Hazırlama tablosuna yükleme

Bir veri ambarı tablosuna verileri taşımak için en yüksek yükleme hızını elde etmek üzere verileri bir hazırlama tablosuna yükleyin. Hazırlama tablosunu bir yığın olarak tanımlayın ve dağıtım seçeneği için hepsine bir kez yöntemini kullanın.

Yüklemenin genellikle ilk olarak bir hazırlama tablosuna yüklediğiniz, daha sonra verileri bir üretim veri ambarı tablosuna eklediğiniz iki adımlı bir işlem olduğunu göz önünde bulundurun. Üretim tablosu bir karma dağıtım kullanıyorsa, hazırlama tablosunu karma dağıtımla tanımlamanız durumunda toplam yükleme ve ekleme süresi daha hızlı olabilir. Hazırlama tablosuna yükleme işlemi daha uzun sürer, ancak satırları üretim tablosuna eklemeyi içeren ikinci adım, dağıtımlar arasında veri hareketi oluşturmaz.

Columnstore dizinine yükleme

Columnstore dizinleri, verileri yüksek kaliteli satır grupları olarak sıkıştırmak için yüksek miktarlarda bellek gerektirir. En iyi sıkıştırma ve dizin verimliliği için, columnstore dizininin her satır grubunda en fazla 1.048.576 satırı sıkıştırması gerekir. Bellek baskısı olduğunda, columnstore dizini en yüksek sıkıştırma oranlarına ulaşamayabilir. Bu, sorgu performansını etkiler. Derinlemesine bir bakış için, bkz. Columnstore bellek iyileştirmeleri.

  • Yükleme kullanıcısının en yüksek sıkıştırma oranlarına ulaşmak için yeterli belleğe sahip olduğundan emin olmak için, orta veya büyük bir kaynak sınıfının üyesi olan yükleme kullanıcılarını kullanın.
  • Yeni satır gruplarını tamamen doldurmak için yeterli satır yükleyin. Toplu yükleme sırasında, her 1.048.576 satır tam bir satır grubu olarak doğrudan columnstore'na sıkıştırılır. 102.400’den daha az satır içeren yükler, satırları bir b ağacı dizininde tutulduğu deltastore’a gönderir. Çok az sayıda satır yüklerseniz, hepsi deltastore’a gönderilerek hemen columnstore biçiminde sıkıştırılmayabilir.

SQLBulkCopy API veya BCP kullanırken toplu iş boyutunu artırma

COPY deyimiyle yüklemek, ayrılmış SQL havuzlarıyla en yüksek aktarım hızını sağlar. Yüklemek için COPY'yi kullanamıyorsanız ve SqLBulkCopy API'sini veya bcp'yi kullanmanız gerekiyorsa, daha iyi aktarım hızı için toplu iş boyutunu artırmayı göz önünde bulundurmalısınız.

İpucu

100 K ile 1M satır arasında bir toplu iş boyutu, en uygun toplu iş boyutu kapasitesini belirlemek için önerilen temeldir.

Yükleme hatalarını yönetme

Bir dış tablo kullanan bir yük "Sorgu iptal edildi-- dış bir kaynaktan okunurken en yüksek reddedilme sayısına ulaşıldı" hatasıyla başarısız olabilir. Bu ileti, dış verilerinizin kirli kayıtlar içerdiğini gösterir. Veri türleri ve sütun sayısı dış tablonun sütun tanımlarıyla eşleşmiyorsa veya veriler belirtilen dış dosya biçimine uymuyorsa veri kaydı kirli olarak değerlendirilir.

Kirli kayıtları düzeltmek için dış tablo ve dış dosya biçimlerinizin doğru olduğundan ve dış verilerinizin bu tanımlara uyduğundan emin olun. Dış veri kayıtlarının bir alt kümesinin kirli olması durumunda, 'CREATE EXTERNAL TABLE' içindeki reddetme seçeneklerini kullanarak sorgularınız için bu kayıtları reddetmeyi seçebilirsiniz.

Üretim tablosuna veri ekleme

Küçük bir tabloya bir INSERT deyimiyle tek seferlik yükleme yapmak veya INSERT INTO MyLookup VALUES (1, 'Type 1') gibi bir deyimle bir aramanın düzenli aralıklarla yeniden yüklenmesi yeterlidir. Ancak, tekil eklemeler toplu yükleme gerçekleştirmek kadar verimli değildir.

Gün boyunca binlerce ekleme yapmanız gerekiyorsa, eklemeleri toplu olarak yüklemek için toplu iş haline getirin. Bir dosyaya tekli eklemeleri eklemek için işlemlerinizi geliştirin ve ardından dosyayı düzenli olarak yükleyen başka bir işlem oluşturun.

Yüklemeden sonra istatistik oluşturma

Sorgu performansını geliştirmek için ilk yüklemeden sonra tüm tabloların tüm sütunlarında istatistikler oluşturmak veya verilerde büyük değişiklikler yapmak önemlidir. İstatistik oluşturma işlemi el ile yapılabilir veya otomatik oluşturma istatistiklerini etkinleştirebilirsiniz.

İstatistiklerin ayrıntılı bir açıklaması için bkz. İstatistikler. Aşağıdaki örnekte, Customer_Speed tablosunun beş sütunu üzerinde el ile istatistiklerin nasıl oluşturulacağı gösterilmektedir.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

Depolama anahtarlarını döndürme

Blob depolamanızın erişim anahtarlarını düzenli olarak değiştirmek iyi bir güvenlik uygulamasıdır. Blob depolama hesabınız için anahtarları geçirmenizi sağlayan iki depolama anahtarınız bulunur.

Azure Depolama hesabı anahtarlarını döndürmek için:

Anahtarı değişen her depolama hesabı için, VERİTABANI KAPSAMLI KİMLİK BİLGİSİNİ DEĞİŞTİR yazın.

Örnek:

Özgün anahtar oluşturuldu

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

1. anahtardan 2. anahtara geçin

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

Temel dış veri kaynaklarında başka bir değişiklik yapılması gerekmez.

Sonraki adımlar