Praktik terbaik untuk memuat data ke kumpulan SQL khusus di Azure Synapse Analytics

  • Artikel

Dalam artikel ini, Anda akan menemukan rekomendasi dan optimasi performa untuk memuat data.

Siapkan data di Azure Storage

Untuk meminimalkan latensi, kolokasikan lapisan penyimpanan Anda dan kumpulan SQL khusus Anda.

Saat mengekspor data ke dalam Format File ORC, Anda mungkin mendapatkan kesalahan Java habis memori saat ada kolom teks besar. Untuk mengatasi batasan ini, hanya ekspor subset kolom.

PolyBase tidak dapat memuat baris yang memiliki lebih dari 1.000.000 byte data. Saat Anda memasukkan data ke dalam file teks di penyimpanan Azure Blob atau Azure Data Lake Store, data tersebut harus memiliki kurang dari 1.000.000 byte data. Batasan byte ini berlaku terlepas dari skema tabel.

Semua format file memiliki karakteristik performa yang berbeda. Untuk pemuatan tercepat, gunakan file teks terkompresi delimitasi. Selisih antara performa UTF-8 dan UTF-16 sangat minim.

Pisahkan file terkompresi besar ke dalam file terkompresi yang lebih kecil.

Jalankan pemuatan dengan komputasi yang memadai

Untuk kecepatan pemuatan tercepat, jalankan hanya satu pemuatan pada satu waktu. Jika tidak mungkin, jalankan jumlah beban minimal secara bersamaan. Jika Anda mengharapkan pekerjaan pemuatan besar, pertimbangkan untuk meningkatkan kumpulan SQL khusus Anda sebelum pemuatan.

Untuk menjalankan pemuatan dengan sumber daya komputasi yang sesuai, buat pengguna pemuatan yang ditunjuk untuk menjalankan pemuatan. Tetapkan setiap pengguna pemuatan ke kelas sumber daya atau grup beban kerja tertentu. Untuk menjalankan pemuatan, masuk sebagai salah satu pengguna pemuatan, lalu jalankan muatan. Pemuatan berjalan dengan kelas sumber daya pengguna. Metode ini lebih sederhana daripada mencoba mengubah kelas sumber daya pengguna agar sesuai dengan kebutuhan kelas sumber daya saat ini.

Buat pengguna pemuatan

Contoh ini membuat pengguna pemuatan yang diklasifikasikan ke grup beban kerja tertentu. Langkah pertama adalah terhubung ke master dan membuat proses masuk.

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

Sambungkan ke kumpulan SQL khusus dan buat pengguna. Kode berikut mengasumsikan bahwa Anda terhubung ke database bernama mySampleDataWarehouse. Ini menunjukkan cara membuat pengguna bernama pemuat dan memberi pengguna izin untuk membuat tabel dan memuat menggunakan pernyataan COPY. Kemudian, ini mengklasifikasikan pengguna ke grup beban kerja DataLoads dengan sumber daya maksimum.

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



Penting

Ini adalah contoh ekstrim pengalokasian 100% sumber daya dari kumpulan SQL untuk beban tunggal. Hal ini akan memberi Anda konkurensi maksimum 1. Perlu diketahui bahwa hal ini harus digunakan hanya untuk beban awal saat Anda harus membuat grup beban kerja tambahan dengan konfigurasinya sendiri untuk menyeimbangkan sumber daya di seluruh beban kerja Anda.

Untuk menjalankan beban dengan sumber daya untuk grup beban kerja pemuatan, masuk sebagai pemuat dan jalankan beban.

Perbolehkan beberapa pengguna memuat bersamaan

Sering kali ada kebutuhan agar beberapa pengguna memuat data ke dalam gudang data. Memuat dengan CREATE TABLE AS SELECT (Transact-SQL) memerlukan izin database CONTROL. Izin CONTROL memberikan akses kontrol ke semua skema. Anda mungkin tidak ingin semua pengguna yang memuat memiliki akses kontrol pada semua skema. Untuk membatasi izin, gunakan pernyataan DENY CONTROL.

Misalnya, pertimbangkan skema database, schema_A untuk dept A, dan schema_B untuk dept B. Biarkan pengguna database user_A dan user_B memuat PolyBase di dept A dan B. Mereka berdua telah diberi izin database CONTROL. Pembuat skema A dan B sekarang mengunci skema mereka menggunakan DENY:

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

User_A dan user_B sekarang terkunci dari skema dept lainnya.

Memuat ke tabel penahapan

Untuk mencapai kecepatan pemuatan tercepat guna memindahkan data ke tabel gudang data, muat data ke dalam tabel penahapan. Tentukan tabel penahapan sebagai tumpukan dan gunakan round-robin untuk opsi distribusi.

Pertimbangkan bahwa pemuatan biasanya berupa proses dua langkah yang mengharuskan Anda untuk terlebih dahulu memuat ke tabel penahapan lalu memasukkan data ke dalam tabel gudang data. Jika tabel produksi menggunakan distribusi hash, total waktu untuk memuat dan menyisipkan mungkin lebih cepat jika Anda menentukan tabel penahapan dengan distribusi hash. Pemuatan ke tabel penahapan membutuhkan waktu lebih lama, tetapi langkah kedua yang menyisipkan baris ke tabel produksi tidak menimbulkan perpindahan data di seluruh distribusi.

Memuat ke indeks penyimpanan kolom

Indeks penyimpan kolom memerlukan memori dalam jumlah besar untuk memadatkan data ke dalam grup baris berkualitas tinggi. Untuk pemadatan terbaik dan efisiensi indeks, indeks penyimpan kolom perlu memadatkan maksimum 1.048.576 baris ke dalam setiap grup baris. Ketika ada tekanan memori, indeks penyimpan kolom mungkin tidak dapat mencapai tingkat pemadatan maksimum. Ini memengaruhi performa kueri. Untuk pemahaman mendalam, lihat Optimasi memori penyimpanan kolom.

  • Untuk memastikan pengguna pemuatan memiliki memori yang cukup untuk mencapai tingkat kompresi maksimum, gunakan pengguna pemuatan yang merupakan anggota kelas sumber daya menengah atau besar.
  • Muat baris yang cukup untuk mengisi grup baris baru sepenuhnya. Selama pemuatan massal, setiap 1.048.576 baris dikompresi langsung ke penyimpanan kolom sebagai grup baris lengkap. Muatan dengan kurang dari 102.400 baris mengirim baris ke deltastore tempat baris ditahan dalam indeks pohon b. Jika Anda memuat terlalu sedikit baris, semua baris ini mungkin dirutekan ke deltastore alih-alih langsung dikompresi ke dalam format penyimpanan kolom.

Perbesar ukuran batch saat menggunakan SqLBulkCopy API atau bcp

Pemuatan dengan pernyataan COPY akan memberikan throughput tertinggi dengan kumpulan SQL khusus. Jika Anda tidak dapat menggunakan COPY untuk memuat dan harus menggunakan SqLBulkCopy API atau bcp, Anda harus mempertimbangkan untuk memperbesar ukuran batch untuk throughput yang lebih baik.

Tip

Ukuran batch antara 100 ribu hingga 1 juta baris adalah garis besar yang direkomendasikan untuk menentukan kapasitas ukuran batch optimal.

Kelola kegagalan pemuatan

Pemuatan menggunakan tabel eksternal bisa gagal disertai kesalahan "Kueri dibatalkan-- ambang batas penolakan maksimum tercapai saat membaca dari sumber eksternal" . Pesan ini menunjukkan bahwa data eksternal Anda berisi rekaman kotor. Rekaman data dianggap kotor jika jenis data dan jumlah kolom tidak cocok dengan definisi kolom tabel eksternal, atau jika data tidak sesuai dengan format file eksternal yang ditentukan.

Untuk memperbaiki rekaman kotor, pastikan bahwa definisi format file eksternal dan tabel eksternal Anda sudah benar dan data eksternal Anda sesuai dengan definisi ini. Jika subset rekaman data eksternal kotor, Anda dapat memilih untuk menolak rekaman tersebut untuk kueri Anda dengan menggunakan opsi tolak di 'CREATE EXTERNAL TABLE' .

Sisipkan data ke dalam tabel produksi

Pemuatan satu kali ke tabel kecil dengan pernyataan INSERT, atau bahkan isi ulang berkala dari pencarian mungkin bekerja cukup baik dengan pernyataan seperti INSERT INTO MyLookup VALUES (1, 'Type 1'). Namun, sisipan tunggal tidak seefisien melakukan pemuatan massal.

Jika Anda memiliki ribuan atau lebih sisipan tunggal sepanjang hari, batch sisipannya sehingga Anda dapat memuatnya secara massal. Kembangkan proses Anda untuk menambahkan satu sisipan ke file, lalu buat proses lain yang secara berkala memuat file.

Buat statistik setelah pemuatan

Untuk meningkatkan performa kueri, statistik harus dibuat di semua kolom semua tabel setelah pemuatan pertama, atau perubahan substansial akan terjadi dalam data. Pembuatan statistik dapat dilakukan secara manual atau Anda dapat mengaktifkan buat otomatis statistik.

Untuk penjelasan mendetail tentang statistik, lihat Statistik. Contoh berikut memperlihatkan cara membuat statistik secara manual pada lima kolom tabel Customer_Speed.

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

Putar kunci penyimpanan

Praktik keamanan yang baik adalah mengubah kunci akses ke penyimpanan blob Anda secara teratur. Anda memiliki dua kunci penyimpanan untuk akun penyimpanan blob Anda, yang memungkinkan Anda untuk transisi kunci.

Untuk memutar kunci akun Azure Storage:

Untuk setiap akun penyimpanan yang kuncinya telah berubah, terbitkan ALTER DATABASE SCOPED CREDENTIAL.

Contoh:

Kunci asli dibuat

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

Putar kunci dari kunci 1 ke kunci 2

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

Tidak diperlukan perubahan lain pada sumber data eksternal yang mendasarinya.

Langkah berikutnya