Jak uruchomić i załadować hurtownię danych w usłudze Azure SQL Data Warehouse – instrukcja krok po kroku

W poprzednim artykule omawiałem teorię hurtowni danych, a także opisywałem niektóre z funkcjonalności Azure SQL Data Warehouse. Tym razem dowiemy się jak uruchomić instancję Azure SQL Data Warehouse, a także załadować ją z wykorzystaniem mechanizmu Polybase.

Konfiguracja instancji Azure SQL Data Warehouse

Na początku warto zaznaczyć, że w polskiej wersji portalu Azure usługa Azure SQL Data Warehouse nosi nazwę Magazyn danych SQL 🙂 Sam proces tworzenia nowej instancji Azure SQL Data Warehouse będzie bardzo zbliżony do tego, który widzieliśmy w przypadku Azure SQL Database. Zaczynajmy!

Pierwszą czynnością, jaką musimy wykonać, jest zalogowanie się do portalu Azure. Następnie wyszukujemy usługę Magazyn danych SQL lub – jeżeli korzystamy z angielskiej wersji portalu – SQL Data Warehouse.

Następnie musimy dodać nową instancję, w tym celu klikamy na opcję dodaj.

Kolejnym krokiem jest konfiguracja. Zasadniczo wygląda ona identycznie jak w przypadku bazy Azure SQL Database, dlatego nie będę tego opisywał (szczegóły znajdziesz tutaj). Aby rezultat naszych prac był widoczny szybciej, posłużymy się przykładową bazą danych AdventureWorksDW, która posiada zdefiniowane już tabelki z załadowanymi danymi.

Podobnie jak w przypadku Azure SQL Database, tutaj również możemy skonfigurować poziom wydajności usługi, tym razem posługując się jednostkami cDWU (Data Warehouse Units). Jest to jednostka wydajności, która zawiera w sobie takie elementy, jak moc obliczeniowa procesora czy liczba odczytów i zapisów do pamięci.

Rzeczą, o której należy pamiętać, jest relatywnie wysoki koszt. W najniższej konfiguracji trzeba będzie zapłacić 1,28€ za godzinę działania. Należy jednak pamiętać, że przez tę godzinę działać będzie dla nas 60 instancji Azure SQL Database, dzięki czemu możemy liczyć na naprawdę dużą wydajność. Pamiętajmy także, że taka usługa nie musi pracować przez 24 godziny na dobę. W przeciwieństwie do klasycznych rozwiązań SQL Server, tutaj w każdej chwili możemy zatrzymać działanie usługi. W praktyce nasza hurtownia może być dostępna na przykład tylko w godzinach pracy naszej organizacji.

Ostatnim krokiem jest kliknięcie utwórz i gotowe!

Pozostaje nam sprawdzić, czy nasza usługa działa. W tym celu wykorzystamy SQL Server Management Studio. Pamiętajmy, aby korzystać z najnowszej dostępnej wersji. Wklejamy adres serwera, oraz wpisujemy login i hasło administratora.

Jeśli nasze połączenie zakończyło się powodzeniem, powinniśmy zobaczyć tabele bazy AdventureWorksDW. Zachęcam, aby popatrzeć na różne tabele faktów i wymiarów, i zobaczyć, jakie algorytmy dystrybucji (o których pisałem ostatnio) zostały użyte.

Po zakończonej pracy wstrzymajmy usługę Azure SQL Data Warehouse, aby nie ponosić niepotrzebnie kosztów.

Ładowanie hurtowni danych

Z uwagi na to, że utworzyliśmy hurtownię wykorzystując przykładową bazę AdventuresWorks, mamy tam również testowe dane. Myślę jednak, że warto zaprezentować w jaki sposób możemy załadować dane do hurtowni wykorzystując jedną z lepszych funkcjonalności tej usługi – a konkretnie Polybase. Jest to mechanizm, który pozwala na równoległe ładowanie plików płaskich do hurtowni, dzięki czemu możemy znacznie przyspieszyć ten proces.

Na początku zajmiemy się załadowaniem danych z pliku płaskiego. Za przykład posłużą nam dane z rejestru umów dotacyjnych zawieranych przez Ministerstwo sportu i turystyki, które są dostępne tutaj. Plik został przeze mnie nieco zmieniony i jest dostępny tutaj. Na storage account wrzuciłem 100 kopii tego pliku, aby zwiększyć liczbę rekordów do załadowania.

Pierwszym krokiem, jaki musimy teraz wykonać, jest pobranie pliku i wykonanie pewnych modyfikacji, do których należy powielenie liczby wierszy i usunięcie pierwszego, który jest opisem pliku. Dodatkowo zmieniamy nazwy kolumn, aby nie zawierały spacji i polskich znaków. Niestety mechanizm jest „wybredny” i struktura pliku musi być jednolita :).

Tworzenie konta magazynu do przechowywania plików

Aby możliwe było załadowanie pliku na serwery Azure potrzebne nam będzie konto magazynu. Jest to po prostu miejsce, w którym można składować pliki, w zasadzie dowolnego typu. W naszym przypadku utworzymy takie konto w najprostszy możliwy sposób. Nie będziemy tutaj omawiać dokładnie wszystkich opcji, ponieważ ich nazwy są dosyć intuicyjne.

Następnie tworzymy nowe konto magazynu – w wyszukiwarkę wpisujemy Storage Account.

Konfigurujemy ustawienia konta zgodnie z poniższymi obrazkami:

Tworzymy kontener, czyli swego rodzaju „folder” na nasze pliki.

Wrzucamy plik do magazynu, klikamy na ikonę folderu i wybieramy plik (pliki) z dysku, a następnie wybieramy opcję przekaż.

Aby możliwe było odczytanie pliku przez mechanizm Polybase, musimy zmienić ustawienia dostępności na takie, jak w poniższym obrazku. Takie ustawienia zastosowałem tylko do celów demonstracyjnych. W przypadku poufnych danych należałoby rozważyć inne opcje (na przykład SAS lub konto aplikacyjne, pamiętajmy, nie udostępniamy nigdy poufnych plików).

Po skonfigurowaniu konta magazynu i wrzuceniu pliku musimy pamiętać jeszcze o kilku rzeczach po stronie Azure SQL Data Warehouse.

Tworzenie obiektów i ładowanie danych z wykorzystaniem Polybase

Na początku zalogujmy się do usługi przez SQL Server Management Studio. Pierwszym krokiem będzie utworzenie schematu, w którym będziemy przechowywali dane z zewnętrznych źródeł (nazwa może być oczywiście inna).

Teraz tworzymy zewnętrzne źródło danych. Ta konstrukcja jest dostępna właśnie dla plików płaskich z zewnętrznego źródła (ale nie tylko). Wskazujemy typ (nazwa hadoop tyczy się właśnie pliku płaskiego ­­– może to być mylące) i podajemy jego lokalizację.

Kolejnym krokiem jest utworzenie formatu pliku. Określamy, że będzie to plik płaski, oddzielony średnikami, z kodowaniem UTF8. Zwróćmy uwagę na to, że zaczynamy czytać dane od 2 wiersza, bo pierwszy jest nagłówkiem (są tam nazwy kolumn).

Teraz czas na utworzenie tabeli zewnętrznej (ma ona taką strukturę, jak plik płaski), określamy typy danych, lokalizację (w naszym przypadku będzie to cały folder – znak „/”), źródło danych i format pliku.

Ostatnim już krokiem jest załadowanie tabeli docelowej z wykorzystaniem mechanizmu CTAS (Create Table As Select). Ta konstrukcja tworzy tabele i ładuje do niej dane. Określamy sposób dystrybucji i typ indeksu, z którego chcemy skorzystać.

Jak widać, ładowanie 100 plików CSV trwało 2 sekundy, nie trzeba było korzystać z żadnego procesu ETL, wystarczyło kilka poleceń SQL. Skrypt z opisywanych operacji jest dostępny tutaj.

Zalety i wady Azure SQL Data Warehouse

Samo uruchomienie instancji Azure SQL Data Warehouse przebiega w niemalże taki sam sposób, jak w przypadku Azure SQL Database. Podłączenie za pośrednictwem SSMS również wygląda identycznie. Swego rodzaju nowością jest ładowanie bezpośrednio plików płaskich z zewnętrznego źródła, jakim jest magazyn blob. Co więcej, dzięki Polybase takie ładowanie jest bardzo wydajne, co ma znaczenie w przypadku większej liczby dużych plików.

Oczywiście nie ma róży bez kolców – podczas pracy z mechanizmem Polybase często napotkamy błędy, z których nic nie wynika i musimy chwilę pomyśleć, o co chodzi. Najczęściej konieczne jest sprawdzenie czy struktura pliku i typy danych są zgodne. Nie jest to problem nie do rozwiązania, ale takie sytuacje bywają irytujące 🙂 . Dodatkowo Visual Studio nie obsługuje póki co projektów Azure SQL Data Warehouse. Taka funkcjonalność jest dostępna tylko w private preview, do programu można dołączyć wypełniając formularz. Jeżeli planujemy przenoszenie danych o dużej objętości warto zainteresować się formatem pliku Apache Parquet, który zapewnia dużą kompresję, i który wykorzystać przy ładowaniu mechanizmem Polybase.