Projektowanie strategii ładowania danych programu PolyBase dla dedykowanej puli SQL
Tradycyjne magazyny danych systemu wieloprocesorowego (SMP) symetrycznego używają procesu wyodrębniania, przekształcania i ładowania (ETL) do ładowania danych. Pula Azure SQL to architektura masowego przetwarzania równoległego (MPP), która wykorzystuje skalowalność i elastyczność zasobów obliczeniowych i magazynowych.
Natomiast proces wyodrębniania, ładowania i przekształcania (ELT) może wykorzystać wbudowane możliwości przetwarzania zapytań rozproszonych i wyeliminować zasoby potrzebne do przekształcenia danych przed załadowaniem.
Pula SQL obsługuje wiele metod ładowania, w tym opcje inne niż Polybase, takie jak program kopiowania zbiorczego (bcp) i interfejs API SQL BulkCopy, najszybszym i najbardziej skalowalnym sposobem ładowania danych jest technologia PolyBase. PolyBase to technologia, która uzyskuje dostęp do danych zewnętrznych przechowywanych w usłudze Azure Blob Storage lub Azure Data Lake Storage za pośrednictwem języka Transact-SQL (T-SQL).
Implementowanie technologii Polybase 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 magazynu danych, a następnie przekształcane.
Podstawowe kroki implementowania technologii PolyBase ELT dla dedykowanej puli SQL to:
- Wyodrębnij dane źródłowe do plików tekstowych.
- Przejmij dane do usługi Azure Blob Storage lub Azure Data Lake Storage.
- Przygotuj dane do załadowania.
- Załaduj dane do dedykowanych tabel przejściowych puli SQL przy użyciu technologii PolyBase.
- Przekształć dane.
- Wstaw dane do tabel produkcyjnych.
Aby zapoznać się z samouczkiem dotyczącym ładowania, zobacz Load the New York Taxicab dataset (Ładowanie zestawu danych Taxicab w Nowym Jorku).
Aby uzyskać więcej informacji, zobacz Ładowanie wzorców i strategii.
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 plików tekstowych obsługiwanych przez program PolyBase.
Formaty plików zewnętrznych programu PolyBase
Program PolyBase ładuje dane z zakodowanych plików tekstowych utF-8 i UTF-16. Program PolyBase jest również ładowany z plików Usługi Hadoop w formatach RC File, ORC i Parquet. Program PolyBase może również ładować dane ze skompresowanych plików Gzip i Snappy. Technologia PolyBase obecnie nie obsługuje rozszerzonych formatów ASCII, stałych szerokości ani zagnieżdżonych formatów, takich jak WinZip, JSON i XML.
Jeśli eksportujesz z programu SQL Server, możesz użyć narzędzia wiersza polecenia bcp, aby wyeksportować dane do rozdzielanych plików tekstowych. W poniższej tabeli wymieniono typy danych Parquet mapowane na usługę Azure Synapse Analytics.
| Typ danych Parquet | Typ danych SQL |
|---|---|
| tinyint | tinyint |
| smallint | smallint |
| int | int |
| bigint | bigint |
| boolean | bitowe |
| double | liczba zmiennoprzecinkowa |
| liczba zmiennoprzecinkowa | rzeczywiste |
| double | pieniędzy |
| double | smallmoney |
| string | nchar |
| string | nvarchar |
| string | char |
| string | varchar |
| dane binarne | dane binarne |
| dane binarne | varbinary |
| timestamp | data |
| timestamp | smalldatetime |
| timestamp | datetime2 |
| timestamp | datetime |
| timestamp | time |
| data | data |
| decimal | decimal |
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 Storage. W obu lokalizacjach dane powinny być przechowywane w plikach tekstowych. Program PolyBase może ładować się z jednej z tych lokalizacji.
Do przenoszenia danych do usługi Azure Storage można użyć następujących narzędzi i usług:
- 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 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 dedykowaną pulą SQL, zobacz Ładowanie danych do usługi Azure Synapse Analytics.
Przygotowywanie danych do załadowania
Może być konieczne przygotowanie i wyczyszczenie danych na koncie magazynu przed załadowaniem ich do dedykowanej puli SQL. Przygotowywanie danych można wykonać, gdy dane są w źródle, podczas eksportowania danych do plików tekstowych lub po tym, jak dane są przechowywane w usłudze Azure Storage. Najłatwiej jest pracować z danymi tak szybko, jak to możliwe.
Definiowanie tabel zewnętrznych
Przed załadowaniem danych należy zdefiniować tabele zewnętrzne w magazynie danych. 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 magazynem danych.
Definiowanie tabel zewnętrznych obejmuje określenie źródła danych, formatu plików tekstowych i definicji tabeli. Poniżej przedstawiono tematy dotyczące składni języka T-SQL, które są potrzebne:
Formatowanie plików tekstowych
Po zdefiniowaniu obiektów zewnętrznych należy 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ą z nierelacyjnego źródła, 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 puli SQL. Niezgodność między typami danych w plikach tekstowych zewnętrznych a tabelą magazynu danych 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.
Ładowanie danych do dedykowanych tabel przejściowych puli SQL przy użyciu technologii PolyBase
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ż korzystanie z wbudowanych funkcji przetwarzania zapytań rozproszonych w puli SQL na potrzeby przekształceń danych przed wstawieniem danych do tabel produkcyjnych.
Opcje ładowania za pomocą technologii PolyBase
Aby załadować dane za pomocą technologii PolyBase, możesz użyć dowolnej z następujących opcji ładowania:
- Ładowanie danych zewnętrznych przy użyciu identyfikatora Entra firmy Microsoft.
- Ładowanie danych zewnętrznych przy użyciu tożsamości zarządzanej.
- Technologia PolyBase z językiem T-SQL działa dobrze, gdy dane są w usłudze Azure Blob Storage lub Azure Data Lake Storage. Zapewnia ona największą kontrolę nad procesem ładowania, ale także wymaga zdefiniowania obiektów danych zewnętrznych. Inne metody definiują te obiekty w tle podczas mapowania tabel źródłowych na tabele docelowe. Aby zorganizować obciążenia języka T-SQL, możesz użyć usługi Azure Data Factory, SSIS lub Azure Functions.
- Technologia PolyBase z usługami SQL Server Integration Services (SSIS) działa dobrze, gdy dane źródłowe są w programie SQL Server. Usługa SSIS definiuje mapowania tabel typu source-to-destination, 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.
- Technologia PolyBase z usługą Azure Data Factory to inne narzędzie orkiestracji. Definiuje potok i planuje zadania.
- Program PolyBase z usługą Azure Databricks przesyła dane z tabeli usługi Azure Synapse Analytics do ramki danych usługi Databricks i/lub zapisuje dane z ramki danych usługi Databricks do tabeli usługi Azure Synapse Analytics przy użyciu technologii PolyBase.
Opcje ładowania nienależące do programu PolyBase
Jeśli dane nie są zgodne z technologią PolyBase, możesz użyć narzędzia bcp lub interfejsu API SQLBulkCopy. Usługa BCP ładuje się bezpośrednio do dedykowanej puli SQL bez przechodzenia przez usługę Azure Blob Storage i jest przeznaczona tylko dla małych obciążeń. Należy pamiętać, że wydajność ładowania tych opcji jest niższa niż technologia PolyBase.
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.
Wstawianie danych do tabel produkcyjnych
Instrukcja INSERT INTO ... 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ń.