W pierwszej części omówiliśmy logowanie i interfejs web GUI Snowflake oraz utworzenie pierwszych obiektów, tj. bazy danych, schematu, tabeli oraz hurtowni. W tej części rozpoczniemy od:
- ładowania danych i przejdziemy do
- połączenia Snowflake z narzędziem BI na przykładzie Tableau, następnie pokażemy
- zasilanie danymi, oraz
- eksportowanie danych ze Snowflake.
5 sposobów na ładowanie danych do Snowflake
Jest wiele sposobów załadowania danych do Snowflake z różnych lokalizacji. Możemy skorzystać z funkcjonalności wbudowanych w platformę lub skorzystać z zewnętrznych narzędzi. Wszystko zależy od dostępnych w danych warunkach technologii, umiejętności i oczekiwanego stopnia automatyzacji oraz kontroli nad procesem.
Podstawowe sposoby na zasilenie hurtowni danymi
- Ładowanie danych z plików płaskich poprzez web GUI – najprostszy ale też całkowicie manualny proces pozwalający na załadowanie pojedynczych plików
- Załadowanie danych z repozytorium (storage) do stage i później do tabeli poprzez komendę COPY INTO – pozwala na załadowanie wielu plików i „małą” automatyzację
- Automatyczne ładowanie danych ze stage poprzez Snowpipe
- Ładowanie danych skryptem Python – rozwiązanie pozwalające na tanią automatyzację, której poświęcimy osobny artykuł
- Narzędzia ETL/ELT – w bardzo szybki i niezawodny sposób możemy utworzyć korytarz danych do naszej hurtowni używając Alteryx lub platformę ELT Fivetran.
Ładowanie danych do Snowflake poprzez CLI
Artykuł jest poświęcony opcji nr 2, ponieważ w wersji próbnej robimy to najczęściej ręcznie z użyciem komendy Copy oraz korzystamy ze źródła danych na testowym stage zewnętrznym umieszczony na S3, przygotowanym przez Snowflake.
W warunkach produkcyjnych dane będziemy ładować w sposób zautomatyzowany lub z użyciem narzędzi ETL. Więcej na ten temat w sekcji „Zasilanie danymi”.
W ogólnym przypadku załadowanie danych wymaga następujących kroków:
Krok 1
Utworzenie lub wskazanie istniejącej hurtowni Warehouse. Kolejne kroki możemy jeszcze wykonać w web GUI lub przeprowadzić je w SnowSQL poprzez wiersz poleceń. Instalacja SnowSQL jest intuicyjna. Odpalamy wiersz poleceń i wpisujemy snowsql. Program nawiąże połączenie z kontem jeśli wcześniej poprawnie zaktualizowaliśmy plik konfiguracyjny.
Operowanie na danych w Snowflake wymaga określenia i dostosowania wymaganego potencjału obliczeniowego hurtowni Warehouse. To właśnie użycie Warehouse generuje podstawowe koszty użytkowania Snowflake. Możliwe jest dynamiczne dostosowywanie rozmiaru Warehouse do zmieniających się potrzeb. Istnieje możliwość automatyzacji wykorzystania Warehouse m.in. poprzez opcję Auto-suspend (koszt nie jest naliczany po określonym czasie nieużywania) i Auto-resume (Warehouse aktywuje się w momencie, kiedy jest potrzebny).
Możemy stworzyć swoją własną hurtownię o zadanych parametrach i dowolnie eksperymentować z rozmiarem, co będzie bezpośrednio przekładać się na zużywanie kredytów. Koszt 1 kredytu określony jest jako podstawowy koszt funkcjonowania (tj. sam fakt pozostawania w stanie aktywnym) hurtowni o rozmiarze 1 węzła (node) X-Small przez 1 godzinę. Cena kredytu to ok 2 USD. Zwiększanie rozmiaru hurtowni (wielokrotności X-Small: 2 x X-Small = Small, 2x Small itd. aż do Extra-Large i łączenia ich w klastry) proporcjonalnie zwiększa koszt podstawowy. Na wzrost kosztów ma także wpływ wolumen danych i złożoność zapytań. Koszty naliczane są w kroku sekundowym, z tym, że za każde aktywowanie ze stanu zawieszenia danej hurtowni zapłacimy za minimum 60s, nawet, jeśli czas potrzebny na zrealizowanie zadania będzie krótszy. To jedna ze wskazówek, że opcja Auto-Suspend powinna być dostosowana do naszego faktycznego cyklu operacji z danymi i możemy ją dostosowywać wraz ze zmiennym charakterem tych operacji, dla każdej hurtowni indywidualnie. W wersji próbnej domyślny czas 5 min jest wystarczający. Możemy go skrócić, z tym, że w web GUI minimum wynosi 5 minut, ale z użyciem SQL możemy ustawić dowolnie małą wartość wyrażoną w sekundach (z domyślnych 300s), jednak należy pamiętać o opłacalności ze względu na limit 60s.
Szczegółowe informacje nt. kosztów.
Krok 2
Wskazanie źródła danych. Aby załadować dane do tabel w Snowflake, możemy pobierać je z różnych źródeł zewnętrznych (External Stages), jak np. rozwiązań chmurowych Amazon(S3), Microsoft Azure czy Google (GCP) lub też wykorzystując wewnętrzny stage (Internal Stage) Snowflake.
Utworzenie wewnętrznego Stage w Snowflake możliwe jest jedną z 3 metod: kliknięcie pozycji Create w zakładce Stage web GUI, użycie komendy CREATE Stage w zewnętrznym edytorze SQL (np. Snowsql) lub też w oknie poleceń obszaru roboczego w zakładce Worksheets. W wersji próbnej dane są dla nas przygotowane w zewnętrznym STAGE w automatycznie przypisanym regionie AWS.
Krok 3
Określenie typu danych. Snowflake wspiera praktycznie wszystkie podstawowe typy danych SQL lokowanych w kolumnach, zmiennych lokalnych, wyrażeniach, parametrach i innych odpowiednich strukturach. Gdzie tylko jest to niezbędne i możliwe, Snowflake automatycznie wymusza odpowiedni typ danych.
Jedyne typy danych, których Snowflake nie wspiera bezpośrednio to LOB (Large Object) oraz dane typu ENUM i specyficzne typy definiowane przez użytkownika. Przy czym w przypadku LOB: BLOB może być zastąpiony przez typ BINARY z maximum 8,388,608 bajtami, zaś CLOB może być zastąpiony przez typ VARCHAR z maksymalnie 16,777,216 bajtami (na single-byte).
Formaty pliku danych
Wspierane formaty plików to CSV, JSON, XML, Avro, ORC oraz Parquet.
W menu Database w zakładce File Formats klikamy Create i możemy wybrać dowolny z predefiniowanych formatów, a także określić szczegółowe parametry specyficzne dla danego formatu. Pliki mogą być też skompresowane i można wskazać format kompresji, przy czym opcja auto powoduje, że Snowflake sam rozpozna dowolny ze znanych mu formatów kompresji.
Krok 4
Mając powyższe na uwadze, możemy dokonać pierwszego załadowania danych w sposób opisany w przykładzie, tj. z zewnętrznego stage na S3 AWS. Alternatywnie, możemy wykorzystać wewnętrzny stage Snowflake i załadować dane z pliku na komputerze. Korzystając z komendy PUT ładujemy dane do utworzonego wcześniej stage wewnętrznego. Wcześniej jednak, musimy upewnić się, że działamy w odpowiednim kontekście:
Uwaga: dokumentacja Snowflake mówi, że dla systemu Windows do podania ścieżki dostępu do pliku należy używać back-slash, czyli znaku „\”, zaś dla systemów Linux i MacOS zwykłych „forward-slash”, czyli znaku „/”. Jeśli jednak w ścieżce dostępu występują np. spacje w nazwie folderu lub pliku danych, należy całość ująć w apostrofy (‘ ‘), z tym, że dla systemu Windows należy wtedy zmienić każdy back-slash na forward-slash. Proponuję więc, zawsze używać forward-slash „/” i zawsze ujmować całość w cudzysłów, tak jak zapisałem w przykładzie powyżej.
Krok 5
Przed załadowaniem danych do tabeli musimy utworzyć szczegółowy format pliku, stosownie do faktycznego formatu naszego pliku, z którego dane chcemy umieścić w tabeli. Czyli jeśli jest to zwykły .csv z polami rozdzielonymi przecinkiem (lub innym znakiem) oraz zawiera w pierwszym wierszu nazwy kolumn, które chcemy opuścić, to utworzymy format pod nazwą np. „CB_CSV”:
Krok 6
Możemy teraz załadować dane z naszego stage do tabeli, korzystając z komendy copy into:
Jeśli z jakiegoś powodu w naszym stage mielibyśmy także inne załadowane pliki, możemy wskazać konkretnie, o który plik nam chodzi wpisując jego pełną nazwę, ale najczęściej wykorzystamy możliwość podania wzoru nazwy pliku (pattern), co jest szczególnie przydatne przy ładowaniu wielu plików z danymi (np.: „przykładowy plik danych1.csv”, „plik danych 2.csv”, „kolejny plik danych3.csv”, itd.):
co załaduje nam wszystkie pliki zawierające w nazwie ciąg znaków „plik danych” umieszczone w stage.
Uwaga: Jeśli w dalszej pracy ze Snowflake utworzymy kilka baz danych i/lub kilka schematów to można pominąć każdorazową zmianę kontekstu, jednak konieczne jest wtedy dokładne opisywanie obiektów ze wskazaniem bazy danych (tutaj CITIBIKE) i schematu (tutaj PUBLIC), czyli powyższy przykład wyglądałby następująco:
Klonowanie tabel
Utworzone tabele możemy klonować, co fizycznie powieli te struktury (podobnie jak klonowanie bazy danych czy schematu, których klony zawierać będą wszystkie struktury niższego rzędu odpowiednio) i będzie można na nich pracować niezależnie. Korzystając z web GUI w menu Databses>CITIBIKE klikamy „Clone”. W oknie wybieramy tabelę źródłową oraz nadajemy nazwę nowo tworzonej tabeli-klonowi (lub odpowiednio bazie danych, czy schematowi).
W przypadku tabel, jeśli nie chcemy duplikować danych, a zależy nam tylko na stworzeniu nowej tabeli o takiej samej strukturze jak już istniejąca, korzystamy z pozycji „Create like…”.
Tworzenie widoków (Views)
Poza operowaniem na fizycznych tabelach, Snowflake daje możliwość utworzenia widoków (Views), które możemy wykorzystać tak, jak fizycznie istniejące tabele. Widok istnieje jako zapis metadanych, jednak nie powoduje kopiowania/powielania danych.
Zgodnie z przykładem utworzymy widok z danych załadowanych w formacie JSON. Powtarzając kroki jak powyżej, tworzymy bazę danych WEATHER, w niej schemat PUBLIC i tabelę json_weather_data. Przygotowane dane na stage w AWS S3 są w formacie JSON. Snowflake daje możliwość załadowania danych z plików w formacie JSON, Parquet, czy Avro bez konieczności ich wcześniejszej transformacji. W tym celu utworzymy tabelę z jedną kolumną i określamy typ danych jako „variant”, dzięki czemu cały plik JSON załadujemy do pojedynczej kolumny. Możemy skorzystać z webGUI, albo wykonać to w SQL. Dane umieszczone są w stage zewnętrznym na S3 AWS, który musimy wskazać:
Podgląd załadowanych danych
Jeśli podejrzymy załadowane dane, czy to poprzez „select * from json_weather_data limit 10;”, czy korzystając z webGUI (w widoku Worksheets klikając na tabelę JSON_WEATHER_DATA > Preview Data), to ujrzymy zawartość pliku danych JSON załadowaną do pojedynczej kolumny „V” (jeśli nie nadaliśmy jej innej nazwy).
Aby móc zobaczyć dane w bardziej przyjaznej postaci, utworzymy widok (create view), w którym przypiszemy nazwy i formaty poszczególnym kolumnom. Podglądając dane widoku, ujrzymy spodziewaną strukturę tabeli:
Tak utworzony widok wykorzystamy jak fizyczną tabelę. Sprawdzimy, jak kształtowała się liczba przejazdów w zależności od pogody. Dane o ilości przejazdów wybierzemy z tabeli TRIPS w bazie CITIBIKE, natomiast dane pogodowe z widoku json_weather_data_view w bazie WEATHER. Połączymy tabele polem zawierającym czas, czyli z jednej strony czas obserwacji (observation_time) warunków pogodowych, a z drugiej czas rozpoczęcia przejazdu (starttime). Aby dane mogły być właściwie skorelowane, bo przecież czas obserwacji był całkowicie niezależny od czas wypożyczenia roweru i odwrotnie, ustalimy granulację do poziomu godziny. Wykonamy to wpisując komendy SQL w Worksheet. Ponieważ kontekst mamy ustawiony na bazę WEATHER, odwołując się do tabeli TRIPS musimy przywołać jej kontekst, czyli bazę danych (CITIBIKE) i schemat (PUBLIC).
Wykonujemy operację JOIN, tak jak w przypadku tabel fizycznych, pomimo, iż jednym z obiektów jest widok. Wynik operacji składa się z dwóch kolumn i tylu wierszy, Ile różnych wartości w polu Weather zawiera widok, czyli dziewięciu.
Podłączenie Snowflake do narzędzia BI na przykładzie Tableau
Zgodnie z dokumentacją Tableau oraz Snowflake, do ich właściwej komunikacji służy sterownik ODBC, do którego link umieszczony jest w menu Help, w sekcji Downloads.
Po zainstalowaniu sterownika ODBC w systemie, uruchamiamy Tableau. Na stronie startowej w menu Connect znajdziemy Snowflake w sekcji To a Server. W oknie logowania podajemy dane logowania naszego konta Snowflake. Na tym etapie możemy podać (opcjonalnie) rolę, co określi uprawnienia dla danego połączenia Tableau ze Snowflake stosownie z ustawieniami naszego konta w Snowflake ustalonymi przez administratora konta (ACCOUNTADMIN).
Po zalogowaniu musimy wskazać hurtownię (wybieramy naszą COMPUTE_WH), bazę danych (WEATHER), schemat (PUBLIC) oraz tabelę (tutaj wskazujemy widok json_weather_data_view).
Ponieważ tabela TRIPS znajduje się w innej bazie danych, musimy wybrać drugie źródło danych, czyli ponownie wskazać Snowflake i dokonać logowania. Kolejno ustawiamy kontekst dla tabeli Trips, czyli ponownie wskazujemy hurtownię COMPUTE_WH, bazę danych CITIBIKE i schemat PUBLIC.
Wybieramy widok json_weather_data_view jako pierwszą tabelę, otwieramy dwuklikiem. Następnie przeciągamy tabelę Trips tworząc JOIN. Zgodnie z przykładem będzie to left-join a polami łączącymi będą pola czasowe na poziomie godziny.
Połączenie Tableau widziane i rozliczane jest przez Snowflake podobnie do operacji prowadzonych przez użytkownika zalogowanego w interfejsie Snowflake. W menu History, w kolumnie Client Info wskazany będzie sterownik ODBC:
Po ustaleniu relacji możliwe jest w Tableau praca w trybie Live lub wyłączenie połączenia Live i przełączenie się na ekstrakt danych. Spowoduje to jednorazowy odczyt danych z obu tabel jednak w ogólnym przypadku, posługując się ekstraktem możemy w sposób dowolny, ograniczony tylko ogromnymi możliwościami Tableau, eksplorować te dane tworząc dowolne analizy, bez operacji na danych w Snowflake. Fakt, iż jedna z tabel nie jest tabelą fizyczną a widokiem w Snowflake, nie stanowi dla Tableau żadnego ograniczenia.
Ponieważ w tabeli Trips nie ma bezpośrednio informacji o ilości przejazdów a liczona jest ilość wierszy w Snowflake/SQL: count(*) as num_trips, w Tableau tworzymy pole kalkulowane Trips Count: COUNT( [STARTTIME]) .
Wybierając pole Wheater do półki kolumn, a pole kalkulowane Trips Count do wierszy, otrzymujemy zobrazowanie ilości przejazdów w funkcji pogody:
Automatyczne zasilanie danymi – Snowpipe
Poza „ręcznym” sposobem opisanym powyżej, możliwe jest automatyczne zasilanie Snowflake danymi poprzez tzw. pipes. Snowflake zapewnia własne Snowpipe, Snowflake konektor dla Kafka oraz umożliwia komunikację z innymi narzędziami integracji danych.
Snowpipe automatycznie ładuje dane, które pojawiają się w zdefiniowanym Stage do tabel docelowych. Możliwe jest to w dwóch trybach: wykorzystanie automatycznego powiadamiania przez platformę chmurową, na której hostowane są konta Snowflake, lub też w oparciu o zapytania aplikacji klienckich na REST endpoint.
Snowflake Kafka connector działa w klastrze (Kafka Connect cluster) odczytując tematy Kafka i zapisując dane do tabel Snowflake.
Ogólny schemat przepływu danych poprzez Snowflake pipes:
Eksportowanie danych ze Snowflake
Proces masowego pobierania danych jest podobny do ładowania, lecz wykonany w przeciwnym kierunku. Jeśli dane chcemy pobrać do stage to proces możemy sprowadzić do następujących kroków.
Krok 1
Z użyciem komendy copy into z tabeli w bazie danych Snowflake do pliku lub plików w Snowflake lub na stage zewnętrznym;
Krok 2
Pobranie ze stage:
- z użyciem komendy get, żeby pobrać pliki danych ze stage Snowflake
- z użyciem interfejsu/narzędzi Amazon S3, żeby pobrać pliki danych z S3
- z użyciem interfejsu/narzędzi Microsoft Azure, żeby pobrać pliki danych z Azure
Masowe pobranie z użyciem zapytań:
- Snowflake umożliwia użycie komendy SELECT w miejsce tabeli w komendzie copy into. Wyniki zapytania są zapisywane do jednego lub wielu plików, jakie wskazano w komendzie w wyszczególnionej lokalizacji (wewnętrznej lub zewnętrznej). Używając JOIN można pobierać dane z wielu tabel.
- Masowe pobieranie do pojedynczego lub wielu plików: komenda copy into posiada opcję zapisu do pojedynczego lub do wielu plików. Pozwala ustawić maksymalny rozmiar dla każdego tworzonego pliku, automatycznie nadając unikalne nazwy.
- Ustawianie zadań pobierania danych z użyciem komendy copy: do Snowflake Stage, Amazon S3, Google CP and Microsoft Azure.
Przykład pobierania danych ze Snowflake do AWS S3:
Utworzenie zewnętrznego stage w S3:
Gdzie dostęp do przykładowego mybucket na S3 następuje poprzez referencyjny integrator magazynu Snowflake, „s3_int”. Referencyjny integrator magazynu (Reference storage integrator) jest obiektem Snowflake, który przechowuje wygenerowaną jednostkę zarządzania tożsamości i dostępu (Identity and access management – IAM) dla zewnętrznych magazynów chmurowych, wraz z opcjonalnym zbiorem dozwolonych lub zabronionych lokalizacji magazynów (Amazon S3, Google CS lub Microsoft Azure). Administratorzy usług chmurowych w danej organizacji nadają uprawnienia do lokalizacji magazynów dla danej jednostki integratora magazynu. Pozwala to na unikanie przesyłania przez użytkowników danych uwierzytelniających każdorazowo przy ładowaniu lub pobieraniu danych.
Podsumowanie
Dla osób posługujących się biegle SQL, Snowflake jest łatwo dostępną platformą pozwalającą na integrację wielu usług i zarządzania przepływem danych w środowisku chmur danych. Istotą optymalizacji wykorzystania Snowflake jest reaktywne i kreatywne zarządzanie potencjałem obliczeniowym dostarczanym przez Snowflake.
Wydaje się, że obecnie Snowflake zapewnia najszerszy potencjał jednocześnie w zakresie: przechowywania w chmurze, hurtowni danych, potencjału obliczeniowego, wchłaniania danych z różnorodnych źródeł, przetwarzania ogromnych wolumenów danych oraz integracji zewnętrznych narzędzi ETL/ELT, jak np. Alteryx, czy Fivetran, analizy i wizualizacji danych, jak np. Tableau czy Power BI, a to wszystko przy minimalnych wymaganiach konfiguracyjnych środowiska użytkownika i w bardzo przyjazny użytkownikowi sposób.
