Strategie ładowania danych dla dedykowanej puli SQL w usłudze Azure Synapse Analytics
Tradycyjne dedykowane pule SQL protokołu SMP używają procesu wyodrębniania, przekształcania i ładowania (ETL) na potrzeby ładowania danych. Usługa Synapse SQL w usłudze Azure Synapse Analytics korzysta z architektury rozproszonego przetwarzania zapytań, która wykorzystuje skalowalność i elastyczność zasobów obliczeniowych i magazynowych.
Korzystanie z procesu wyodrębniania, ładowania i przekształcania (ELT) korzysta z wbudowanych funkcji przetwarzania zapytań rozproszonych i eliminuje zasoby potrzebne do transformacji danych przed załadowaniem.
Chociaż dedykowane pule SQL obsługują wiele metod ładowania, w tym popularne opcje programu 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 uzyskać największą elastyczność podczas ładowania, zalecamy użycie instrukcji COPY.
Co to jest ELT?
Wyodrębnianie, ładowanie i przekształcanie (ELT) to proces, za pomocą którego dane są wyodrębniane z systemu źródłowego, ładowane do dedykowanej puli SQL, a następnie przekształcane.
Podstawowe kroki implementacji ELT to:
- Wyodrębnij dane źródłowe do plików tekstowych.
- Przejmij dane do usługi Azure Blob Storage lub Azure Data Lake Store.
- Przygotuj dane do załadowania.
- Załaduj dane do tabel przejściowych za pomocą programu PolyBase lub polecenia COPY.
- Przekształć dane.
- Wstaw dane do tabel produkcyjnych.
Aby zapoznać się z samouczkiem ł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 plików tekstowych rozdzielanych lub CSV.
Obsługiwane formaty plików
Za pomocą technologii PolyBase i instrukcji COPY można załadować dane z plików TEKSTowych lub CSV zakodowanych w formacie UTF-8 i UTF-16. Oprócz rozdzielanego tekstu lub plików CSV są ładowane z formatów plików Hadoop, takich jak ORC i Parquet. Program PolyBase i instrukcja COPY mogą również ładować dane ze 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 programu 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 przenieść dane do usługi Azure Storage, możesz przenieść je do usługi Azure Blob Storage lub Azure Data Lake Store Gen2. W obu lokalizacjach 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 akceptowalna.
- Usługa 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 wyczyszczenie 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 szybko, jak to możliwe.
Definiowanie tabel
Najpierw zdefiniuj 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. Technologia 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. Artykuły referencyjne dotyczące składni języka T-SQL, których potrzebujesz, to:
Podczas ładowania plików Parquet użyj następującego mapowania typu danych SQL:
| Typ Parquet | Typ logiczny Parquet (adnotacja) | Typ danych SQL |
|---|---|---|
| BOOLOWSKI | bit | |
| BYTE_ARRAY BINARNE / | varbinary | |
| PODWÓJNY | float | |
| SPŁAWIK | prawdziwy | |
| INT32 | int | |
| INT64 | bigint | |
| INT96 | data/godzina2 | |
| FIXED_LEN_BYTE_ARRAY | dwójkowy | |
| DWÓJKOWY | UTF8 |
nvarchar |
| DWÓJKOWY | STRING |
nvarchar |
| DWÓJKOWY | ENUM |
nvarchar |
| DWÓJKOWY | UUID |
uniqueidentifier |
| DWÓJKOWY | DECIMAL |
decimal |
| DWÓJKOWY | JSON |
nvarchar(MAX) |
| DWÓJKOWY | BSON |
varbinary(MAX) |
| FIXED_LEN_BYTE_ARRAY | DECIMAL |
decimal |
| BYTE_ARRAY | INTERVAL |
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 | DECIMAL |
decimal |
| INT32 | TIME (MILLIS) |
Godzina |
| INT64 | INT(64, true) |
bigint |
| INT64 | INT(64, false ) |
dziesiętne (20,0) |
| INT64 | DECIMAL |
decimal |
| INT64 | TIME (MILLIS) |
Godzina |
| INT64 | TIMESTAMP (MILLIS) |
data/godzina2 |
| Typ złożony | LIST |
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.
- Może wystąpić następujący błąd, jeśli typy są niezgodne między parquet i SQL lub jeśli nieobsł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.
- Sformatuj dane w pliku tekstowym, aby dopasować je do kolumn i typów danych w tabeli docelowej. Niezgodność między typami danych w plikach tekstowych zewnętrznych a dedykowaną tabelą puli SQL powoduje odrzucenie wierszy podczas ładowania.
- Rozdziel 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 w formacie CREATE EXTERNAL FILE.
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 tabel produkcyjnych. 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:
- Instrukcja COPY jest zalecanym narzędziem do ładowania, ponieważ umożliwia bezproblemowe i elastyczne ładowanie danych. Instrukcja zawiera wiele dodatkowych możliwości ładowania, których nie zapewnia technologia PolyBase. Zapoznaj się z samouczkiem kopiowania taksówki w nowym jorku, aby uruchomić przykładowy samouczek.
- Technologia PolyBase z językiem T-SQL wymaga zdefiniowania obiektów danych zewnętrznych.
- Instrukcja PolyBase i COPY z usługą Azure Data Factory (ADF) to inne narzędzie orkiestracji. Definiuje potok i planuje zadania.
- Technologia PolyBase z usługą SSIS działa dobrze, gdy dane źródłowe są w programie SQL Server. Usługa SSIS definiuje mapowania tabeli źródłowej na docelową, a także organizuje obciążenie. Jeśli masz już pakiety usług SSIS, możesz zmodyfikować pakiety tak, aby działały z nowym miejscem docelowym magazynu danych.
- Program PolyBase z usługą Azure Databricks przesyła dane z tabeli do ramki danych usługi Databricks i/lub zapisuje dane z ramki danych usługi Databricks do tabeli przy użyciu technologii PolyBase.
Przejrzyj dostępne samouczki:
- Samouczek: ładowanie danych zewnętrznych przy użyciu identyfikatora Entra firmy Microsoft
- Samouczek: ładowanie danych zewnętrznych przy użyciu tożsamości zarządzanej
- Samouczek: ładowanie zestawu danych Taxicab w Nowym Jorku
- Samouczek: ładowanie danych do puli SQL usługi Azure Synapse Analytics
- Ładowanie danych detalicznych firmy Contoso do dedykowanych pul SQL w usłudze Azure Synapse Analytics
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 niższa niż technologia 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
WSTAW DO ... Instrukcja SELECT przenosi dane z tabeli przejściowej do tabeli trwałej.
Podczas projektowania procesu ETL spróbuj uruchomić proces w małym przykładzie testowym. 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 do ładowania partnerów
Wielu naszych partnerów ma rozwiązania ładuwcze. Aby dowiedzieć się więcej, zobacz listę naszych partnerów rozwiązań.