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

Tip

Microsoft Fabric Data Warehouse to magazyn relacyjny w skali przedsiębiorstwa na podstawie bazy danych data lake z architekturą gotową do użycia w przyszłości, wbudowaną sztuczną inteligencją i nowymi funkcjami. Jeśli dopiero zaczynasz korzystać z magazynowania danych, zacznij od Fabric Data Warehouse. Istniejące obciążenia dedykowanej puli SQL mogą zostać zaktualizowane do Fabric, aby uzyskać dostęp do nowych możliwości w zakresie nauki o danych, analizy w czasie rzeczywistym i raportowania.

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

Przygotowywanie danych w usłudze Azure Storage

Aby zminimalizować opóźnienia, lokuj warstwę pamięci i dedykowaną pulę SQL razem.

Podczas eksportowania danych do formatu pliku ORC mogą wystąpić błędy związane z brakiem pamięci w Javie, gdy występują obszerne kolumny tekstowe. Aby obejść to ograniczenie, wyeksportuj tylko podzbiór kolumn.

Program PolyBase nie może załadować wierszy zawierających więcej niż 1000 000 bajtów danych. Gdy dane są umieszczane w plikach tekstowych w usłudze Azure Blob Storage lub Azure Data Lake Store, muszą mieć mniej niż 1000 000 bajtów danych. To ograniczenie bajtów jest prawdziwe niezależnie od schematu tabeli.

Wszystkie formaty plików mają różne cechy wydajności. Aby uzyskać najszybsze ładowanie, użyj skompresowanych plików tekstowych z separatorami. Różnica między wydajnością UTF-8 i UTF-16 jest minimalna.

Podziel duże skompresowane pliki na mniejsze skompresowane pliki.

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

Aby uzyskać najszybszą szybkość ładowania, uruchom tylko jedno zadanie ładowania naraz. Jeśli nie jest to możliwe, uruchom minimalną liczbę obciążeń jednocześnie. Jeśli oczekujesz dużego zadania ładowania danych, rozważ zwiększenie zasobów dedykowanej puli SQL przed rozpoczęciem obciążenia.

Aby uruchomić obciążenia z odpowiednimi zasobami obliczeniowymi, utwórz użytkowników przeznaczonych do obsługi obciążeń. Przypisz każdego użytkownika ładujący do określonej klasy zasobów lub grupy obciążeń. Aby uruchomić ładunek, zaloguj się jako jeden z użytkowników ładujących, a następnie uruchom ładunek. Obciążenie działa z klasą zasobów przypisaną użytkownikowi. Ta metoda jest prostsza niż próba zmiany klasy zasobów użytkownika w celu dopasowania bieżącej wymaganej klasy zasobów.

Tworzenie użytkownika ładującego

W tym przykładzie tworzony jest użytkownik ładujący sklasyfikowany do określonej grupy obciążeń. Pierwszym krokiem jest nawiązanie połączenia z serwerem głównym 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. Poniższy kod zakłada, ż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 ekstremalny przykład przydzielania 100% zasobów puli SQL do jednego zadania. To zapewni współbieżność maksymalną na poziomie 1. Należy pamiętać, że powinno być stosowane tylko do początkowego ładowania, gdzie trzeba będzie utworzyć inne grupy obciążeń z ich własnymi konfiguracjami, aby zrównoważyć zasoby między waszymi obciążeniami.

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

Zezwalanie wielu użytkownikom na ładowanie

Często istnieje potrzeba załadowania danych przez wielu użytkowników do magazynu danych. Ładowanie przy użyciu polecenia CREATE TABLE AS SELECT (Transact-SQL) wymaga uprawnień CONTROL w bazie danych. Uprawnienie CONTROL zapewnia kontrolę dostępu do wszystkich schematów. Możesz nie chcieć, aby wszyscy użytkownicy ładujący mieli dostęp do wszystkich schematów. Aby ograniczyć uprawnienia, użyj instrukcji DENY CONTROL.

Rozważmy na przykład schematy bazy danych, schema_A dla działu A i schema_B dla działu B. Niech użytkownicy bazy danych, user_A i user_B, będą użytkownikami do ładowania za pomocą PolyBase odpowiednio w dziale A i B. Obaj otrzymali uprawnienia do bazy danych CONTROL. Twórcy schematu A i B teraz blokują swoje schematy przy użyciu metody 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 blokowane ze schematu innego działu.

Ładowanie do tabeli przejściowej

Aby osiągnąć najszybszą szybkość ładowania danych do tabeli magazynu danych, załaduj dane do tabeli przejściowej. Zdefiniuj tabelę przejściową jako stertę i użyj działania okrężnego dla opcji dystrybucji.

Należy wziąć pod uwagę, że ładowanie jest zwykle procesem dwuetapowym, w którym najpierw ładujemy do tabeli przejściowej, a następnie wstawiamy dane do produkcyjnej tabeli magazynu danych. Jeśli tabela produkcyjna używa rozkładu skrótów, łączny czas ładowania i wstawiania może być szybszy, jeśli zdefiniujesz tabelę przejściową z rozkładem skrótów. Ładowanie do tabeli przejściowej trwa dłużej, ale drugi krok wstawiania wierszy do tabeli produkcyjnej nie powoduje przenoszenia danych między dystrybucjami.

Ładowanie do indeksu magazynu kolumn

Indeksy columnstore wymagają znacznej ilości pamięci do skompresowania danych do grup wierszy o wysokiej jakości. Aby uzyskać najlepszą kompresję i wydajność indeksu, indeks magazynu kolumn musi skompresować maksymalnie 1 048 576 wierszy w każdej grupie wierszy. Jeśli występuje presja pamięci, indeks magazynu kolumn może nie zdołać osiągnąć maksymalnego poziomu kompresji. Ma to wpływ na wydajność zapytań. Aby uzyskać szczegółowe informacje, zobacz Optymalizacje pamięci magazynu kolumn.

  • Aby upewnić się, że użytkownik ładujący ma wystarczającą ilość pamięci do osiągnięcia maksymalnej szybkości kompresji, używaj użytkowników należących do średniej lub dużej klasy zasobów.
  • Załaduj wystarczającą liczbę wierszy, aby całkowicie wypełnić nowe grupy wierszy. Podczas ładowania zbiorczego każde 1 048 576 wierszy jest kompresowanych bezpośrednio do magazynu kolumn jako pełna grupa wierszy. Obciążenia, które zawierają mniej niż 102 400 wierszy, wysyłają wiersze do deltastore, gdzie są one przechowywane w indeksie drzewa B. Jeśli załadujesz zbyt mało wierszy, mogą one wszystkie trafić do deltastore i nie zostaną natychmiast skompresowane do formatu kolumnowego.

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

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

Tip

Rozmiar partii w zakresie od 100 tys. do 1 mln wierszy jest zalecaną wartością bazową do określenia optymalnej pojemności rozmiaru partii.

Zarządzanie błędami ładowania

Ładowanie przy użyciu tabeli zewnętrznej może zakończyć się niepowodzeniem z powodu błędu "Zapytanie przerwane — osiągnięto maksymalny próg odrzucenia podczas odczytywania 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 są zgodne z definicjami kolumn tabeli zewnętrznej lub jeśli dane nie są zgodne z określonym formatem pliku zewnętrznego.

Aby naprawić brudne rekordy, upewnij się, że zewnętrzne definicje tabel i formatów plików zewnętrznych są poprawne, 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 odrzucenia w obszarze "CREATE EXTERNAL TABLE" (UTWÓRZ TABELĘ ZEWNĘTRZNą).

Wstawianie danych do tabeli produkcyjnej

Jednorazowe ładowanie do małej tabeli za pomocą instrukcji INSERT, a nawet okresowe ponowne ładowanie danych referencyjnych może działać wystarczająco dobrze za pomocą instrukcji takiej jak INSERT INTO MyLookup VALUES (1, 'Type 1'). Jednak operacje wstawiania jednostkowego nie są tak wydajne, jak ładowanie zbiorcze.

Jeśli masz tysiące lub więcej pojedynczych wstawień przez cały dzień, grupuj wstawienia, aby można je było załadować zbiorczo. Utwórz procesy, aby dołączyć pojedyncze wstawki do pliku, a następnie utworzyć inny proces, który okresowo ładuje plik.

Tworzenie statystyk po załadowaniu

Aby zwiększyć wydajność zapytań, ważne jest, aby utworzyć statystyki dotyczące wszystkich kolumn wszystkich tabel po pierwszym załadowaniu lub w danych występują poważne zmiany. Statystyki można tworzyć ręcznie lub włączyć automatyczne tworzenie statystyk.

Aby uzyskać szczegółowe wyjaśnienie statystyk, zobacz 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 przechowywania

Dobrą praktyką z zakresu bezpieczeństwa jest regularne zmienianie klucza dostępu do magazynu obiektów blob. Masz dwa klucze do przechowywania w swoim koncie magazynu obiektów blob, co umożliwia zarządzanie kluczami.

Aby rotować klucze kont do Azure Storage:

Dla każdego konta magazynu, którego klucz uległ zmianie, należy wydać ALTER DATABASE SCOPED CREDENTIAL.

Przykład:

Tworzony jest oryginalny klucz

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

Obracanie klucza z klucza 1 na klucz 2

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

Nie są potrzebne żadne inne zmiany w zewnętrznych źródłach danych.

Powiązana zawartość

  • Last updated on