Strategie ładowania danych dla dedykowanej puli SQL w usłudze Azure Synapse Analytics

  • Artykuł

Tradycyjne dedykowane pule SQL SMP używają procesu wyodrębniania, przekształcania i ładowania (ETL) na potrzeby ładowania danych. Usługa Synapse SQL w ramach usługi Azure Synapse Analytics używa rozproszonej architektury przetwarzania zapytań, która wykorzystuje skalowalność i elastyczność zasobów obliczeniowych i magazynowych.

Korzystanie z procesu wyodrębniania, ładowania i przekształcania (ELT) wykorzystuje wbudowane funkcje przetwarzania zapytań rozproszonych i eliminuje zasoby potrzebne do transformacji danych przed załadowaniem.

Podczas gdy dedykowane pule SQL obsługują wiele metod ładowania, w tym popularne opcje SQL Server, takie jak bcp i interfejs API SqlBulkCopy, najszybszym i najbardziej skalowalnym sposobem ładowania danych jest użycie tabel zewnętrznych polyBase i instrukcji COPY.

Za pomocą technologii PolyBase i instrukcji COPY można uzyskać dostęp do danych zewnętrznych przechowywanych w usłudze Azure Blob Storage lub Azure Data Lake Store za pośrednictwem języka T-SQL. Aby zapewnić największą elastyczność podczas ładowania, zalecamy użycie instrukcji COPY.

Co to jest ELT?

Wyodrębnianie, ładowanie i przekształcanie (ELT) to proces wyodrębniania danych z systemu źródłowego, ładowany do dedykowanej puli SQL, a następnie przekształcany.

Podstawowe kroki implementacji ELT to:

  1. Wyodrębnij dane źródłowe do plików tekstowych.
  2. Ląduj dane w usłudze Azure Blob Storage lub Azure Data Lake Store.
  3. Przygotuj dane do załadowania.
  4. Załaduj dane do tabel przejściowych za pomocą technologii PolyBase lub polecenia COPY.
  5. Przekształć dane.
  6. Wstaw dane do tabel produkcyjnych.

Aby zapoznać się z samouczkiem dotyczącym ładowania, zobacz ładowanie danych z usługi Azure Blob Storage.

1. Wyodrębnianie danych źródłowych do plików tekstowych

Pobieranie danych z systemu źródłowego zależy od lokalizacji magazynu. Celem jest przeniesienie danych do obsługiwanych rozdzielanych plików tekstowych lub CSV.

Obsługiwane formaty plików

Za pomocą technologii PolyBase i instrukcji COPY można załadować dane z zakodowanych w formacie UTF-8 i UTF-16 rozdzielonych tekstami lub plikami CSV. Oprócz rozdzielanego tekstu lub plików CSV ładuje się z formatów plików Hadoop, takich jak ORC i Parquet. Program PolyBase i instrukcja COPY mogą również ładować dane z skompresowanych plików Gzip i Snappy.

Rozszerzone formaty ASCII, format o stałej szerokości i zagnieżdżone, takie jak WinZip lub XML, nie są obsługiwane. Jeśli eksportujesz z SQL Server, możesz użyć narzędzia wiersza polecenia bcp, aby wyeksportować dane do rozdzielanych plików tekstowych.

2. Lądowanie danych w usłudze Azure Blob Storage lub Azure Data Lake Store

Aby wylądować dane w usłudze Azure Storage, możesz przenieść je do usługi Azure Blob Storage lub Azure Data Lake Store Gen2. W każdej lokalizacji dane powinny być przechowywane w plikach tekstowych. Program PolyBase i instrukcja COPY mogą ładować się z jednej z tych lokalizacji.

Narzędzia i usługi, których można użyć do przenoszenia danych do usługi Azure Storage:

  • Usługa Azure ExpressRoute zwiększa przepływność sieci, wydajność i przewidywalność. ExpressRoute to usługa, która kieruje dane za pośrednictwem dedykowanego połączenia prywatnego z platformą Azure. Połączenia usługi ExpressRoute nie kierują danych za pośrednictwem publicznego Internetu. Połączenia oferują większą niezawodność, szybsze szybkości, mniejsze opóźnienia i wyższe zabezpieczenia niż typowe połączenia za pośrednictwem publicznego Internetu.
  • Narzędzie AzCopy przenosi dane do usługi Azure Storage za pośrednictwem publicznego Internetu. To działa, jeśli rozmiary danych są mniejsze niż 10 TB. Aby regularnie wykonywać obciążenia za pomocą narzędzia AzCopy, przetestuj szybkość sieci, aby sprawdzić, czy jest to dopuszczalne.
  • Azure Data Factory (ADF) ma bramę, którą można zainstalować na serwerze lokalnym. Następnie możesz utworzyć potok, aby przenieść dane z serwera lokalnego do usługi Azure Storage. Aby użyć usługi Data Factory z dedykowanymi pulami SQL, zobacz Ładowanie danych dla dedykowanych pul SQL.

3. Przygotowanie danych do załadowania

Przed załadowaniem może być konieczne przygotowanie i oczyszczenie danych na koncie magazynu. Przygotowywanie danych można wykonać, gdy dane są w źródle, podczas eksportowania danych do plików tekstowych lub po tym, jak dane są w usłudze Azure Storage. Najłatwiej jest pracować z danymi tak wcześnie, jak to możliwe.

Definiowanie tabel

Należy najpierw zdefiniować tabele ładowane do w dedykowanej puli SQL podczas korzystania z instrukcji COPY.

Jeśli używasz technologii PolyBase, przed załadowaniem należy zdefiniować tabele zewnętrzne w dedykowanej puli SQL. Program PolyBase używa tabel zewnętrznych do definiowania i uzyskiwania dostępu do danych w usłudze Azure Storage. Tabela zewnętrzna jest podobna do widoku bazy danych. Tabela zewnętrzna zawiera schemat tabeli i wskazuje dane przechowywane poza dedykowaną pulą SQL.

Definiowanie tabel zewnętrznych obejmuje określenie źródła danych, formatu plików tekstowych i definicji tabeli. Potrzebne są artykuły referencyjne dotyczące składni języka T-SQL:

Podczas ładowania plików Parquet użyj następującego mapowania typu danych SQL:

Typ Parquet Typ logiczny Parquet (adnotacja) Typ danych SQL
BOOLEAN bit
PLIK BINARNY/BYTE_ARRAY varbinary
PODWÓJNE float
FLOAT liczba rzeczywista
INT32 int
INT64 bigint
INT96 datetime2
FIXED_LEN_BYTE_ARRAY binarny
BINARNYM UTF8 nvarchar
BINARNYM CIĄG nvarchar
BINARNYM ENUM nvarchar
BINARNYM UUID uniqueidentifier
BINARNYM DZIESIĘTNYCH decimal
BINARNYM JSON nvarchar(MAX)
BINARNYM BSON Varbinary(max)
FIXED_LEN_BYTE_ARRAY DZIESIĘTNYCH decimal
BYTE_ARRAY INTERWAŁ Varchar(max)
INT32 INT(8, true) smallint
INT32 INT(16, true) smallint
INT32 INT(32, true) int
INT32 INT(8, false) tinyint
INT32 INT(16, false) int
INT32 INT(32, false) bigint
INT32 DATE data
INT32 DZIESIĘTNYCH decimal
INT32 TIME (MILLIS ) time
INT64 INT(64, true) bigint
INT64 INT(64, false ) dziesiętna (20 0)
INT64 DZIESIĘTNYCH decimal
INT64 TIME (MILLIS) time
INT64 SYGNATURA CZASOWA (MILLIS) datetime2
Typ złożony LISTY varchar(max)
Typ złożony MAPĘ varchar(max)

Ważne

  • Dedykowane pule SQL nie obsługują obecnie typów danych Parquet z dokładnością MIKROS i NANOS.
  • Jeśli typy są niezgodne między formatami Parquet i SQL, może wystąpić następujący błąd lub jeśli nie są obsługiwane typy danych Parquet: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:...
  • Ładowanie wartości poza zakresem od 0 do 127 do małej kolumny dla formatu plików Parquet i ORC nie jest obsługiwane.

Aby zapoznać się z przykładem tworzenia obiektów zewnętrznych, zobacz Tworzenie tabel zewnętrznych.

Formatowanie plików tekstowych

Jeśli używasz technologii PolyBase, zdefiniowane obiekty zewnętrzne muszą wyrównać wiersze plików tekstowych do zewnętrznej tabeli i definicji formatu pliku. Dane w każdym wierszu pliku tekstowego muszą być zgodne z definicją tabeli. Aby sformatować pliki tekstowe:

  • Jeśli dane pochodzą ze źródła nierelacyjnego, musisz przekształcić je w wiersze i kolumny. Niezależnie od tego, czy dane pochodzą ze źródła relacyjnego, czy nierelacyjnego, dane muszą zostać przekształcone w celu dostosowania ich do definicji kolumn dla tabeli, w której planujesz załadować dane.
  • Formatuj dane w pliku tekstowym, aby były zgodne z kolumnami i typami danych w tabeli docelowej. Niezgodność między typami danych w plikach tekstowych zewnętrznych a dedykowaną tabelą puli SQL powoduje odrzucenie wierszy podczas ładowania.
  • Oddzielaj pola w pliku tekstowym terminatorem. Pamiętaj, aby użyć znaku lub sekwencji znaków, która nie znajduje się w danych źródłowych. Użyj terminatora określonego za pomocą polecenia CREATE EXTERNAL FILE FORMAT.

4. Załaduj dane przy użyciu technologii PolyBase lub instrukcji COPY

Najlepszym rozwiązaniem jest załadowanie danych do tabeli przejściowej. Tabele przejściowe umożliwiają obsługę błędów bez zakłócania pracy z tabelami produkcyjnymi. Tabela przejściowa umożliwia również użycie dedykowanej architektury przetwarzania równoległego puli SQL na potrzeby przekształceń danych przed wstawieniem danych do tabel produkcyjnych.

Opcje ładowania

Aby załadować dane, możesz użyć dowolnej z następujących opcji ładowania:

Inne opcje ładowania

Oprócz technologii PolyBase i instrukcji COPY można użyć narzędzia bcp lub interfejsu API SqlBulkCopy. Narzędzie bcp ładuje się bezpośrednio do bazy danych bez przechodzenia przez usługę Azure Blob Storage i jest przeznaczone tylko dla małych obciążeń.

Uwaga

Wydajność ładowania tych opcji jest wolniejsza niż program PolyBase i instrukcja COPY.

5. Przekształcanie danych

Podczas gdy dane są w tabeli przejściowej, wykonaj przekształcenia wymagane przez obciążenie. Następnie przenieś dane do tabeli produkcyjnej.

6. Wstawianie danych do tabel produkcyjnych

INSERT INTO ... Instrukcja SELECT przenosi dane z tabeli przejściowej do tabeli trwałej.

Podczas projektowania procesu ETL spróbuj uruchomić proces w małej próbce testowej. Spróbuj wyodrębnić 1000 wierszy z tabeli do pliku, przenieść go na platformę Azure, a następnie spróbować załadować go do tabeli przejściowej.

Rozwiązania ładujący partnerów

Wielu naszych partnerów ma rozwiązania ładujący. Aby dowiedzieć się więcej, zobacz listę naszych partnerów rozwiązań.

Następne kroki

Aby uzyskać wskazówki dotyczące ładowania, zobacz Najlepsze rozwiązania dotyczące ładowania danych.