Wczytywanie plików płaskich z BLOB Storage do SQL przy wykorzystaniu OPENROWSET

Ładowanie danych z określonych lokalizacji do bazy danych jest procesem stosunkowo prostym i możliwym do wykonania na niezliczoną liczbę sposobów. Możemy tego dokonać zarówno programistycznie jak i z poziomu gotowych narzędzi dedykowanych do tego typu operacji jak np. Integration Services czy Data Factory. Dziś chciałbym pokazać jak załadować dane z Azure BLOB Storage do bazy wykorzystując język TSQL i komendę BULK INSERT. Serdecznie zapraszam do lektury.

Na samym wstępie powiedzmy sobie bardzo króciutko o tym czym jest Azure BLOB Storage. Jest to jedna z kilku usług jakie oferuje chmura Azure do przechowywania i udostępniania  danych nieustrukturyzowanych. Jako BLOB czyli obiekt przechowywany w tego typu usłudze możemy przetrzymywać absolutnie dowolny typ pliku. Oczywiście nie jest to jedyna usługa którą możemy wykorzystać do tego celu ale dla naszych potrzeb bez wątpienia jest wystarczająca.  W dokumentacji możemy przeczytać, że usługa ta szczególnie nadaje się do następujących zastosowań:

• udostępnianie obrazów i dokumentów do przeglądarki,
• przechowywanie plików na potrzeby rozproszonego dostępu,
• streaming video oraz audio,
• zapis plików loga,
• archiwizacja oraz przechowywanie plików backupu,
• przechowywanie danych do analizy.

Stwórzmy sobie naszego własny BLOB Storage. Jak to bywa w przypadku Azure mamy mnóstwo możliwości aby to dokonać, na ten moment wybierzemy najprostsza metodę czyli wyklikamy wszystko na portalu. Po zalogowaniu się na nasze konto Azure musimy stworzyć Storage Account – najłatwiej znaleźć odpowiednią opcję przy użyciu wyszukiwarki:

Następnie standardowo w górnej części okna wybieramy Add aby dodać nowe konto:

Konfiguracja konta sprowadza się do zdefiniowania kilku właściwości:

• Subscription – nazwa subskrypcji na której chcemy oprzeć tworzony storage,
• Resource group – grupa zasobów dla tworzonego storage, jak sama nazwa wskazuje jest to logiczny obiekt grupujący określone zasoby w ramach subskrypcji np. wyobraźmy sobie, że zdefiniowaliśmy osobne grupy dla celów deweloperskich oraz produkcyjnych,
• Storage account name – nazwa naszego storage, bardzo ważne gdyż to m.in ta nazwa będzie częścią adresu po którym będziemy w stanie zidentyfikować nasz tworzony zasób.
• Location – wskazanie który region chmury Azure chcemy wykorzystać,
• Performance – rodzaj dysku dla naszego storage, Standard czyli standardowe dyski magnetyczne, Premium czyli nieco droższe dyski SSD,
• Account kind – typ naszego storage gdzie możemy wybrać Storage, StorageV2 oraz BLOB. W większości przypadków wybierzemy general-purpose Storage v2 który daje nam możliwość zdefiniowania każdego rodzaju storage w tym również Blob storage, o pozostałych dwóch możemy raczej zapomnieć.
• Replication – ustawienie redundancji danych czyli czy dane mają być redundantne na poziomie lokalnym czy też wyższym. Dla naszych celów wybrałem najniższy poziom czyli LRS – dla zainteresowanych większą ilością informacji odsyłam do dokumentacji (link),
• Access tier (default) – wybór pomiędzy opcją Cool and Hot to jest nic innego jak to czy nasze dane będą rzadko odczytywane (Cool) czy częściej (Hot). Dla celów przechowywania np. danych historycznych można wybrać Cool, a jeśli np. mamy proces który regularnie coś zapisuje/odczytuje z naszego konta warto wybrać Hot. Warto zwrócić uwagę, że koszt przechowywania danych w Cool jest niższy niż Hot ale za to jego odczyt jest już droższy, warto zatem przemyśleć to jak często i w jakich sytuacjach nasze dane mają być zapisywane i odczytywane.

Właściwie to by było na tyle opcji związanych z samym kontem. Dalej przechodzimy do zdefiniowania ustawień sieciowych:

W ty miejscu możemy wybrać przede wszystkim to czy dostęp do naszego storage będzie ograniczony do określonej sieci, endpointa czy też z poziomu całego internetu. Na potrzeby niniejszego artykułu wybrałem opcję najmniej restrykcyjną czyli dostęp z dowolnej sieci (Public Endpoint).

Przechodząc dalej możemy włączyć jedną z zaawansowanych opcji jak np. wymuszenie komunikacji po zabezpieczonym protokole, włączenie obsługi dużych plików, możliwość przywrócenia usuniętych plików czy też Data Lake Storage do obsługi workloadów big data – na ten moment zostawmy wszystko na ustawieniach domyślnych i utwórzmy nasz storage:

 

Po krótkim czasie wszystko powinno być dla nas dostępne. Na zakładce Overview odnajdźmy opcję Containers:

W tym miejscu warto wspomnieć czym jest wspomniany kontener. Jest on niczym innym jak strukturą grupującą bloby, która posiada swoją nazwę. Ogólny schemat działania Blob Storage wygląda tak jak na poniższym obrazku czyli mamy konto w którym są kontenery zawierające określone BLOBy – czyli bardzo prosta struktura hierarchiczna:

 

Zanim będziemy mogli wrzucać nasze pliki musimy najpierw stworzyć kontener i aby tego dokonać mamy do dyspozycji przycisk +Container i proste okno konfiguracyjne.

Dodatkowo w tym miejscu możemy wybrać poziom dostępu – czy ma on być publiczny (do całego kontenera czy też do obiektów wewnątrz) czy też prywatny przy czym prywatny oznacza, że aby uzyskać dostęp do danego zasobu musimy się w jakiś sposób uwierzytelnić. Do celów naszego demo użyjemy dostępu typu Private.

Mając już skonfigurowane konto możemy pomyśleć o wrzuceniu do kontenera jakichś plików, a tego możemy dokonać na wiele sposobów m.in REST API, Powershell, Azure CLI lub przy wykorzystaniu dedykowanych bibliotek języka .NET, Python i wielu innych. Jeśli nie chcemy pisać dodatkowego kodu to możemy ściągnąć darmowe narzędzie o nazwie Azure Storage Explorer, które możecie pobrać tutaj. ASE posiada intuicyjny interfejs graficzny w którym możemy poruszać się po Azure Blob Storage w sposób bardzo zbliżony do poruszania się po systemie plików. Po otwarciu narzędzia należy w dowolny sposób uwierzytelnić się do Azure, najprostszym sposobem będzie dodanie konta poprzez standardowe logowanie:

Po zalogowaniu możemy przeglądać nasze kontenery i pliki:

Z poziomu narzędzia możemy również wrzucać nowe pliki bez napisania nawet jednej linijki kodu, wystarczy wykorzystać opcję Upload dostępną w narzędziu. Okno do uploadu sprowadza się do wskazania określonego pliku oraz typu obiektu BLOB:

Mamy do dyspozycji trzy rodzaje blobów, a mianowicie Block, Append i Page, każdy z nich ma inne zastosowania i właściwości, na ten moment powiedzmy, że plik płaski którego chcemy użyć jest typu Block (więcej informacji na temat poszczególnych rodzajów blobów poczytacie tutaj). Plik płaski jest niczym innym jak eksportem tabeli FactInternetSales z bazy AdventureWorksDW2017 (eksport można wykonać za pomocą Import-Eksport Wizardem, o którym pisałem tutaj).

Celem niniejszego artykułu jest pokazanie jak odczytać plik na Azure BLOB Storage z poziomu TSQL, do tego celu wykorzystamy OPENROWSET. Do odczytania pliku w taki sposób potrzebujemy pliku formatu, o którym napiszę parę słów w osobnym artykule na ten moment wygenerujemy go poprzez następującą komendę bcp:

bcp AdventureWorksDW2017.dbo.FactInternetSales  format nul -c -f c:\temp\FormatFile.fmt -t, -T

Powyżej wskazaliśmy obiekt bazy danych na podstawie, którego generujemy plik formatu. Format nul oznacza format niexmlowy, -T to połączenie po bieżącym użytkowniku oraz -f wskazanie lokalizacji generowanego pliku. Mając już wygenerowany plik możemy go również wrzucić na BLOB Storage.

Pierwsza część zadania za nami. Teraz musimy nadać w jakiś sposób dostęp do naszego kontenera i/lub plików. Przeglądając dokumentację lub na portalu możecie dostrzec coś takiego jak Access Keys:

Użycie tych kluczy powoduje nadanie połączeniu pełnego dostępu do naszego Storage Account czego raczej nie powinniśmy robić w tego typu sytuacjach. Lepszym rozwiązaniem jest uzycie powszechnie stosowananego Shared Access Signature (SAS) i to ich właśnie tego mechanizmu na poziomie kontenera użyjemy. Jak zawsze w przypadku Azure tak i tutaj mamy możliwość wygenerowanie SAS na kilka sposobów – ja wybiorę wspomniany wcześniej Storage Explorer. Klikamy zatem prawym przyciskiem myszy na kontener i z menu kontekstowego wybieramy Get Shared Access Signature:

Okno konfiguracyjne sprowadza się do wybrania od kiedy do kiedy dana sygnatura będzie aktywna oraz jaki poziom dostępu jej nadajemy:

Po utworzeniu dostajemy pełne URI do naszego zasobu oraz Query String zawierające parametry naszego URI:

URI w pełnej okazałości wygląda następująco (podzieliłem każdy z parametrów do osobnej linii – ostatni z nich to nasza sygnatura):

https://seequalitystorage.blob.core.windows.net/importfiles
?
st=2020-02-09T18%3A29%3A59Z&
se=2020-02-10T18%3A29%3A59Z&
sp=rl&
sv=2018-03-28&sr=c&
sig=i3O%2FwJCcbRsrXWxBUsQ1yMwdCpD%2FrhhtTCAo6F8Je28%3D

Przechodzimy do przygotowania mojego lokalnego SQL Server do odpytywania BLOBa. W tym celu najpierw stworzymy master key szyfrowanego hasłem:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'zaq1@WSX';
GO

Następnie tworzymy w wybranej przez nas bazie database scoped credential – jako secret podajemy część URI z parametrami (czyli część URI po znaku zapytania):

CREATE DATABASE SCOPED CREDENTIAL SeequalityAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'st=2020-02-09T18%3A29%3A59Z&se=2020-02-10T18%3A29%3A59Z&sp=rl&sv=2018-03-28&sr=c&sig=i3O%2FwJCcbRsrXWxBUsQ1yMwdCpD%2FrhhtTCAo6F8Je28%3D';

Ostatnim krokiem jest stworzenie zewnętrznego źródła danych i wskazanie powyższego credentiala oraz lokalizacji BLOB Storage jako parametru:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://seequalitystorage.blob.core.windows.net'
          , CREDENTIAL= SeequalityAzureBlobStorageCredential
);

Mając to wszystko skonfigurowane możemy użyć składni OPENROWSET aby odpytać źródło:

SELECT * FROM OPENROWSET(
   BULK  'importfiles/FactInternetSales.txt',
   DATA_SOURCE = 'MyAzureBlobStorage',
   FORMAT='CSV',
   FORMATFILE='importfiles/FormatFile.fmt',
   FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
    ) AS Blob;

W moim przypadku wszystko się powiodło:

Podsumowując możemy powiedzieć, że aby przerzucić dane z BLOB do bazy danych wcale nie potrzebujemy dodatkowych narzędzi, a niemal wszystko jesteśmy w stanie załatwić starym poczciwym TSQL. Oczywiście użycie Integration Services czy Data Factory daje lepsze możliwości debugowania i ewentualnej obsługi błędów ale być może w niektórych przypadkach wystarczy użyć najprostszego podejścia. Kilka kwestii nadal pozostaje otwartych jak np. wymiana ewentualnych sygnatur SAS ale to są jedynie kwestie techniczne, które mogą być załatwione chociażby poprzez Powershell. Na ten moment to by było na tyle – mam nadzieję, że wpis okazał się ciekawy i zainspiruje was do własnych eksperymentów. Pozdrawiam!