Najlepsze rozwiązania dotyczące ładowania danych do dedykowanej puli SQL w usłudze Azure Synapse Analytics

  • Artykuł

W tym artykule znajdziesz zalecenia i optymalizacje wydajności ładowania danych.

Przygotowywanie danych w usłudze Azure Storage

Aby zminimalizować opóźnienie, colokuj warstwę magazynu i dedykowaną pulę SQL.

Podczas eksportowania danych do formatu plików ORC mogą pojawić się błędy braku pamięci Java, jeśli w danych znajdują się duże kolumny tekstu. Aby obejść to ograniczenie, można wyeksportować tylko podzestaw wszystkich kolumn.

Program PolyBase nie może załadować wierszy zawierających więcej niż 1000 000 bajtów danych. Dane umieszczane w plikach tekstowych w usłudze Azure Blob Storage lub Azure Data Lake Store muszą zawierać mniej niż 1 000 000 bajtów danych. Ograniczenie liczby bajtów jest niezależne od zdefiniowanego schematu tabeli.

Różne typy plików mają różną charakterystykę wydajności. Najszybsze ładowanie zapewnia użycie skompresowanych rozdzielanych plików tekstowych. Różnica w wydajności między kodowaniem UTF-8 a UTF-16 jest minimalna.

Duże pliki skompresowane można podzielić na mniejsze.

Uruchamianie obciążeń przy użyciu wystarczającej ilości zasobów obliczeniowych

Aby uzyskać większą szybkość ładowania, uruchamiaj tylko jedno zadanie ładowania naraz. Jeśli nie jest to możliwe, uruchamiaj jak najmniejszą liczbę zadań ładowania jednocześnie. Jeśli oczekujesz dużego zadania ładowania, rozważ skalowanie w górę dedykowanej puli SQL przed obciążeniem.

Aby uruchamiać zadania ładowania przy użyciu odpowiednich zasobów obliczeniowych, utwórz użytkowników ładujących na potrzeby uruchamiania ładowania. Przypisz każdego użytkownika ładujący do określonej klasy zasobów lub grupy obciążeń. Aby uruchomić ładowanie, zaloguj się jako jeden z użytkowników ładujący, a następnie uruchom obciążenie. Ładowanie zostanie uruchomione przy użyciu klasy zasobów przypisanej do użytkownika. Jest to prostsza metoda niż zmienianie klasy zasobów użytkownika odpowiednio do bieżących potrzeb.

Tworzenie użytkownika ładujący

W tym przykładzie jest tworzony użytkownik ładujący sklasyfikowany do określonej grupy obciążeń. Pierwszym krokiem jest nawiązanie połączenia z główną bazą danych i utworzenie nazwy logowania.

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

Połącz się z dedykowaną pulą SQL i utwórz użytkownika. W poniższym kodzie założono, że masz połączenie z bazą danych o nazwie mySampleDataWarehouse. Pokazano w nim, jak utworzyć użytkownika o nazwie loader i przyznać użytkownikowi uprawnienia do tworzenia tabel i ładowania przy użyciu instrukcji COPY. Następnie klasyfikuje użytkownika do grupy obciążeń DataLoads z maksymalnymi zasobami.

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



Ważne

Jest to skrajny przykład przydzielenia 100% zasobów puli SQL do pojedynczego obciążenia. Zapewni to maksymalną współbieżność wynoszącą 1. Należy pamiętać, że powinno to być używane tylko w przypadku początkowego obciążenia, w którym należy utworzyć dodatkowe grupy obciążeń z ich własnymi konfiguracjami, aby zrównoważyć zasoby w obciążeniach.

Aby uruchomić obciążenie z zasobami dla grupy obciążeń ładowania, zaloguj się jako moduł ładujący i uruchom obciążenie.

Zezwalaj wielu użytkownikom na ładowanie

Często istnieje potrzeba umożliwienia ładowania danych do magazynu danych wielu użytkownikom. Ładowanie przy użyciu polecenia CREATE TABLE AS SELECT (Transact-SQL) wymaga uprawnień CONTROL bazy danych. Uprawnienia kontrolne (CONTROL) zapewniają dostęp z prawem kontroli do wszystkich schematów. Być może nie chcesz, aby wszyscy użytkownicy, którzy wykonują zadania ładowania, mieli dostęp z prawem kontroli do wszystkich schematów. Aby ograniczyć uprawnienia, użyj instrukcji DENY CONTROL.

Rozważmy na przykład dwa schematy bazy danych, schema_A dla działu A i schema_B dla działu B. Załóżmy, że użytkownicy bazy danych o nazwie user_A i user_B są użytkownikami ładującymi technologii PolyBase w działach A i B. Obaj użytkownicy otrzymali uprawnienia CONTROL do bazy danych. Osoby, które utworzyły schematy A i B, teraz blokują swoje schematy przy użyciu instrukcji DENY:

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

User_A i user_B są teraz zablokowane ze schematu innego działu.

Ładowanie do tabeli przejściowej

Aby osiągnąć największą szybkość ładowania dla przenoszenie danych do tabeli magazynu danych, załaduj dane do tabeli przejściowej. Zdefiniuj tabelę przejściową jako stertę i użyj opcji dystrybucji z działaniem okrężnym.

Pamiętaj, że ładowanie to zwykle proces dwuetapowy, w którym dane są najpierw ładowane do tabeli przejściowej, a następnie wstawianie do tabeli produkcyjnej magazynu danych. Jeśli tabela produkcyjna korzysta z dystrybucji skrótów, łączny czas ładowania i wstawiania może być krótszy w przypadku zdefiniowania tabeli przejściowej za pomocą dystrybucji skrótów. Ładowanie do tabeli przejściowej trwa dłużej, ale drugi etap wstawiania wierszy do tabeli produkcyjnej nie powoduje przenoszenia danych między dystrybucjami.

Ładowanie do indeksu magazynu kolumn

Indeksy magazynu kolumn wymagają dużej ilości pamięci w celu skompresowania danych w grupy wierszy wysokiej jakości. Najlepsza wydajność kompresji i indeksu występuje wówczas, gdy indeks magazynu kolumn kompresuje maksymalnie 1 048 576 wierszy w każdej grupie wierszy. Jeśli pamięci jest za mało, indeks magazynu kolumn może nie osiągać maksymalnej szybkości kompresji. Ma to wpływ na wydajność zapytań. Aby zapoznać się ze szczegółowymi informacjami, zobacz Columnstore memory optimizations (Optymalizowanie pamięci na potrzeby magazynu kolumn).

  • Aby mieć pewność, że użytkownik ładujący ma wystarczająco dużo pamięci do uzyskania maksymalnej szybkości kompresji, korzystaj z użytkowników ładujących będących członkami średnich lub dużych klas zasobów.
  • Załaduj wystarczającą liczbę wierszy, aby całkowicie wypełnić nowe grupy wierszy. Podczas ładowania zbiorczego co 1048 576 wierszy jest kompresowanych bezpośrednio do magazynu kolumn jako pełnej grupy wierszy. W przypadku zadań ładowania z liczbą wierszy mniejszą niż 102 400 wiersze są przekazywane do magazynu delta, gdzie są przechowywane w indeksie b-drzewa. W przypadku załadowania zbyt małej liczby wierszy wszystkie wiersze mogą zostać przekazane do magazynu delta, a nie natychmiast skompresowane do formatu magazynu kolumn.

Zwiększanie rozmiaru partii w przypadku korzystania z interfejsu API SQLBulkCopy lub narzędzia BCP

Ładowanie za pomocą instrukcji COPY zapewni najwyższą przepływność dedykowanych pul SQL. Jeśli nie możesz użyć funkcji COPY do załadowania i musisz użyć interfejsu API SqLBulkCopy lub narzędzia bcp, rozważ zwiększenie rozmiaru partii w celu uzyskania lepszej przepływności.

Porada

Rozmiar partii z zakresu od 100 K do 1 mln wierszy jest zalecanym punktem odniesienia do określenia optymalnej pojemności rozmiaru partii.

Zarządzanie błędami ładowania

Ładowanie z użyciem tabeli zewnętrznej może zakończyć się wystąpieniem błędu Zapytanie zostało przerwane — osiągnięto maksymalny próg odrzuceń podczas odczytu ze źródła zewnętrznego. Ten komunikat wskazuje, że dane zewnętrzne zawierają zanieczyszczone rekordy. Rekord danych jest uznawany za zanieczyszczony, jeśli typy danych i liczba kolumn nie odpowiadają definicjom kolumn w tabeli zewnętrznej lub jeśli dane nie są zgodne z określonym zewnętrznym formatem pliku.

Aby poprawić zanieczyszczone rekordy, upewnij się, że definicje tabeli zewnętrznej i zewnętrznego formatu pliku są prawidłowe, a dane zewnętrzne są zgodne z tymi definicjami. W przypadku, gdy podzbiór rekordów danych zewnętrznych jest zanieczyszczony, możesz odrzucić te rekordy dla zapytań przy użyciu opcji odrzucania w obszarze "CREATE EXTERNAL TABLE".

Wstawianie danych do tabeli produkcyjnej

Jednorazowe ładowanie do małej tabeli za pomocą instrukcji INSERT lub nawet okresowe ponowne ładowanie wyszukiwania to strategie, które mogą sprawdzić się w przypadku instrukcji takich jak INSERT INTO MyLookup VALUES (1, 'Type 1'). Jednak operacje wstawiania jednotonowego nie są tak wydajne, jak wykonywanie obciążenia zbiorczego.

Jeśli w ciągu dnia wykonujesz kilka tysięcy lub więcej wstawień, utwórz partię wstawień i załaduj je zbiorczo. Opracuj proces dołączania pojedynczych wstawień do pliku, a następnie kolejny proces okresowo ładujący ten plik.

Tworzenie statystyk po załadowaniu

Aby poprawić wydajność zapytań, ważne jest utworzenie statystyk dla wszystkich kolumn wszystkich tabel po pierwszym załadowaniu lub wystąpienia poważnych zmian w danych. Tworzenie statystyk można wykonać ręcznie lub włączyć statystykę automatycznego tworzenia.

Aby zapoznać się ze szczegółowym opisem statystyk, zobacz temat Statystyki. W poniższym przykładzie pokazano, jak ręcznie utworzyć statystyki dla pięciu kolumn tabeli 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]);

Rotacja kluczy magazynu

Zalecanym rozwiązaniem w zakresie zabezpieczeń jest regularne zmienianie klucza dostępu do usługi Blob Storage. Istnieją dwa klucze magazynu dla konta usługi Blob Storage, co pozwala na przenoszenie tych kluczy.

Aby przeprowadzić rotację kluczy konta usługi Azure Storage:

Dla każdego konta magazynu, którego klucz został zmieniony, wydaj poświadczenie ALTER DATABASE SCOPED CREDENTIAL.

Przykład:

Został utworzony klucz oryginalny

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

Zamień klucz 1 na klucz 2

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

Nie są potrzebne żadne inne zmiany podstawowych zewnętrznych źródeł danych.

Następne kroki