Bagikan melalui

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

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

Menyiapkan 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 di luar memori ketika terdapat kolom teks besar. Untuk mengatasi batasan ini, ekspor hanya sebagian 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 benar terlepas dari skema tabel.

Semua format file memiliki karakteristik performa yang berbeda. Untuk beban tercepat, gunakan file teks berbatas terkompresi. Perbedaan antara performa UTF-8 dan UTF-16 sangat minim.

Pisahkan file terkompresi besar menjadi file terkompresi yang lebih kecil.

Jalankan beban kerja dengan kekuatan komputasi yang memadai

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

Untuk menjalankan beban dengan sumber daya komputasi yang sesuai, buat pengguna pemuatan yang ditunjuk untuk menjalankan beban. Tetapkan setiap pengguna pemuatan ke kelas sumber daya atau grup beban kerja tertentu. Untuk menjalankan beban, masuk sebagai salah satu pengguna pemuatan, lalu jalankan beban. Proses beban dijalankan 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.

Membuat pengguna dalam proses pemuatan

Contoh ini membuat pengguna yang mengelola pemuatan diklasifikasikan ke grup beban kerja tertentu. Langkah pertama adalah menyambungkan ke master dan membuat login.

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

Sambungkan ke kumpulan SQL khusus dan buat pengguna. Kode berikut mengasumsikan Anda tersambung ke database yang disebut mySampleDataWarehouse. Ini menunjukkan cara membuat pengguna bernama loader dan memberi pengguna izin untuk membuat tabel dan memuat menggunakan pernyataan COPY. Kemudian 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 ekstrem untuk mengalokasikan 100% sumber daya dari kumpulan SQL untuk satu tugas. Ini akan memberi Anda konkurensi maksimum 1. Ketahuilah bahwa ini harus digunakan hanya untuk beban awal di mana Anda harus membuat grup beban kerja lain dengan konfigurasi mereka sendiri untuk menyeimbangkan sumber daya di seluruh beban kerja Anda.

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

Izinkan beberapa pengguna memuat

Sering kali ada kebutuhan untuk memiliki beberapa pengguna memuat data ke gudang data. Memuat dengan CREATE TABLE AS SELECT (Transact-SQL) memerlukan izin CONTROL pada database. Izin CONTROL memberikan akses kontrol ke semua skema. Anda mungkin tidak ingin semua pengguna pemuatan 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 menjadi pengguna untuk pemuatan PolyBase di dept A dan B. Keduanya telah diberikan izin database CONTROL. Pembuat skema A dan B sekarang mengamankan 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 tidak dapat mengakses skema departemen lain.

Memuat ke tabel penahapan

Untuk mencapai kecepatan pemuatan tercepat untuk memindahkan data ke dalam 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 merupakan proses dua langkah di mana Anda pertama kali memuat ke tabel penahapan lalu menyisipkan data ke dalam tabel gudang data produksi. Jika tabel produksi menggunakan distribusi hash, mungkin total waktu untuk memuat dan menyisipkan akan lebih cepat jika Anda mendefinisikan tabel penahapan dengan distribusi hash. Memuat ke tabel penahapan membutuhkan waktu lebih lama, tetapi langkah kedua yaitu menyisipkan baris ke tabel produksi tidak menyebabkan pergerakan data di seluruh segmen distribusi.

Memuat ke indeks penyimpanan kolom

Indeks penyimpan kolom memerlukan memori dalam jumlah besar untuk mengompresi data ke dalam grup baris berkualitas tinggi. Untuk kompresi 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 kompresi maksimum. Ini mempengaruhi performa kueri. Untuk penyelaman mendalam, lihat Pengoptimalan memori penyimpan kolom.

  • Untuk memastikan pengguna pemuatan memiliki memori yang cukup untuk mencapai tingkat kompresi maksimum, gunakan memuat pengguna yang merupakan anggota kelas sumber daya sedang 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 penuh. Muatan yang memiliki kurang dari 102.400 baris mengirimkannya ke deltastore, di mana baris-baris disimpan dalam indeks pohon b. Jika Anda memuat terlalu sedikit baris, semua baris mungkin akan masuk ke deltastore dan tidak langsung dikompresi ke dalam format kolomstore.

Meningkatkan ukuran batch saat menggunakan SQLBulkCopy API atau BCP

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

Petunjuk / Saran

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

Mengelola kegagalan pemuatan

Beban kerja yang menggunakan tabel eksternal bisa gagal dengan 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 tabel eksternal dan definisi format file eksternal Anda sudah benar dan data eksternal Anda sesuai dengan definisi ini. Jika subset rekaman data eksternal kotor, Anda dapat memilih untuk menolak rekaman ini untuk kueri Anda dengan menggunakan opsi tolak di 'CREATE EXTERNAL TABLE' .

Menyisipkan data ke dalam tabel produksi

Pemrosesan satu kali ke tabel kecil dengan pernyataan INSERT, atau bahkan pemrosesan ulang berkala pengambilan data mungkin berkinerja cukup baik dengan pernyataan seperti INSERT INTO MyLookup VALUES (1, 'Type 1'). Namun, sisipan singleton tidak seefisien melakukan pemuatan massal.

Jika Anda memiliki ribuan atau lebih penyisipan tunggal sepanjang hari, kelompokkan penyisipan tersebut sehingga Anda dapat memasukkannya secara massal. Kembangkan proses Anda untuk menambahkan sisipan tunggal ke file, lalu buat proses lain yang secara berkala memuat file.

Membuat statistik setelah pemuatan

Untuk meningkatkan performa kueri, penting untuk membuat statistik pada semua kolom semua tabel setelah pemuatan pertama, atau perubahan besar terjadi dalam data. Buat statistik dapat dilakukan secara manual atau Anda dapat mengaktifkan buat otomatis statistik.

Untuk penjelasan terperinci tentang statistik, lihat Statistik. Contoh berikut menunjukkan 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]);

Memutar kunci penyimpanan

Praktik keamanan yang baik untuk mengubah kunci akses ke penyimpanan blob Anda secara teratur. Anda memiliki dua kunci penyimpanan untuk akun penyimpanan blob Anda, yang memungkinkan Anda untuk mengganti 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 mendasar.

Konten terkait

  • Last updated on