Odpytywanie Azure SQL z poziomu Databricks

Istnieje wiele scenariuszy gdzie z poziomu jednego narzędzia chcemy tworzyć zapytania federacyjne tzn. takie które łączą dane z kilku źródeł. W różnych narzędziach można ten cel osiągnąć na wiele różnych sposobów. W SQL Server szeroko stosowana była funkcjonalność Linked Server (link), w Azure SQL możemy odpytywać dane z różnych baz poprzez Elastic Query (link). Ja natomiast w niniejszym artykule postaram się zobrazować na prostym przykładzie jak z poziomu Azure Databricks można łączyć się do bazy Azure SQL i ją odpytywać.

Do naszego testu wykorzystam bazę Azure SQL która została stworzona na mojej subskrypcji i działa w standardowym trybie z poziomem wydajności ustawionym na Standard S0 (10DTUs). Same ustawienia nie mają większego znaczenia i powyższy tryb wydajności w 100% wystarczy do wykonania zakładanego przez nas testu. W celach ćwiczeniowych powołałem również instancję Databricks w chmurze Azure.

Zanim przejdziemy do pisania kodu potrzebnego do połączenia z ASQL musimy wykonać kilka dodatkowych kroków. Pierwszym z nich będziemy instalacja na klastrze bibiloteki ADAL, która pozwoli nam uwierzytelnić się po Azure Active Directory. Instalacja raczej nie powinna przysporzyć problemów i sprowadza się do wykonania następujących czynności:

1. Z menu workspace wybieramy Compute aby wyświetlić nasze klastry:

2. Dalej przechodzimy na zakładkę Libraries:

3. Tam z kolei klikamy Install New:

4. Wybieramy interesujące nas repozytorium, którym tutaj jest PyPI i tam jako nazwę pakietu wpisujemy adal i klikamy Install:

Po krótkim czasie biblioteka powinna być do naszej dyspozycji. Kolejnym krokiem będzie rejestracja Service Principala czyli tożsamości jaką możemy użyć do uwierzytelnienia z bazą. Aby to zrobić na portalu Azure wyszukujemy App registrations i wybieramy App registration:

Następnie nadajemy nazwę i wybieramy kto może używać tworzony SP – w moim przypadku umożliwiam dostęp jedynie wewnątrz mojego tenanta:

Po stworzeniu samego Service Principala możemy dodać sekret:

Po przejściu dalej wygenerowany zostanie dla nas dosyć długi i bezpieczny ciąg znaków, który należy zachować w bezpiecznym miejscu bo to właśnie on umożliwia uwierzytelnienie po stworzonym przez nas Service Principal:

W przedstawianym przypadku sekret naszego Service Principala będziemy przechowywać po stronie Databricks. Zarządzanie sekretami w tej usłudze jest stosunkowo proste i wymaga databricks-cli czyli databricksowej linii komend dlatego też mając zainstalowanego lokalnie pythona należy wykonać poniższą komendę:

python -m pip install databricks-cli

Po instalacji musimy databricks-cli połączyć z instancją databricks na Azure dlatego wywołujemy następującą komendę:

databricks configure --token

Powyższe polecenie pozwala podać tzw. Personal Access Token czyli token uwierzytelniający, który generujemy np. z poziomu interfejsu graficznego Databricks wybierając Settings -> User settings:

Tam w sekcji Access Tokens mamy gotowy przycisk Generate New Token:

Mając token wracamy do linii komend gdzie najpierw musimy podać adres hosta databricks czyli kopiujemy z url z przeglądarki wszystko do azuredatabricks.net np.:

https://adb-5771132183006214.14.azuredatabricks.net

potem linia komend poprosi nas o podanie samego tokenu dlatego też wklejamy go zgodnie z tym co otrzymaliśmy krok wyżej z interfejsu graficznego. Jak wszystko poszło zgodnie z planem tworzymy tzw. secret scope czyli element grupujący nasze sekrety:

databricks secrets create-scope --scope azuresql

w dalszej kolejności tworzymy sam sekret:

databricks secrets put --scope azuresql --key serviceprincipalsecret

Po wykonaniu powyższej komendy wyskoczy nam okno z notatnikiem gdzie w pierwszej linii możemy podać nasz sekret. Po podaniu sekretu plik należy zapisać i zamknąć edytor:

Jeśli wszystko poszło zgodnie z planem sekret jest już dla nas dostępny z poziomu notebooka databricks gdzie za pomocą komendy dbutils.secrets.get możemy się do niego dostać:

secret = dbutils.secrets.get(scope=”azuresql”,key=”serviceprincipalsecret”)
print(secret)
print(secret[0:4])

Na powyższym rysunku widać jedną ważną rzecz, chodzi mianowicie o to, że jeśli spróbujemy wyświetlić nasz sekret to zostanie nam wyświetlone słowo REDACTED jednak bardzo łatwo obejść to ograniczenie i odczytywać porcjami. Dlatego też należy pamiętać, że jeżeli ktoś ma dostęp do odczytania sekretu to tak naprawdę jest w stanie podejrzeć zawarte tam hasło.

Stworzony przez nas service principal musi mieć możliwość odpytywania bazy danych dlatego też należy go tam dodać (oczywiście nadawajcie tylko te uprawnienia, które są niezbędne – ja użyłem db_owner jedynie dla uproszczenia):

CREATE USER [app-adb2asql] FROM EXTERNAL PROVIDER

sp_addrolemember 'db_owner','app-adb2asql'

Przechodząc do meritum i kodu łączącego nas do Azure SQL na samym początku w naszych notebooku powołamy do życia szereg zmiennych w których będziemy przechowywać poszczególne elementy potrzebne do nawiązania połączenia:

import adal
resource_app_id_url = "https://database.windows.net/"
service_principal_id = "85a9977gc-4b3f-417a-a1sf-6ddsb35570ab"
service_principal_secret = dbutils.secrets.get(scope = "azuresql", key = "serviceprincipalkey")
tenant_id = "a1a21c02-3b64-4ef4-9b59-22e85ee6e7bb"
authority = "https://login.windows.net/" + tenant_id

database_name = "sqldb-seequality"
server_name = "sql-seequality.database.windows.net"

Pierwsza linia nie powinna przysporzyć problemu w interpretacji i odpowiada za import biblioteki adal.

resource_app_id_url – identyfikator zasobu do którego się łączymy czyli Azure SQL,
service_principal_id – identyifkator zarejestrowanego service principala,
service_principal_secret – sekret service principala przechowywany w Secure Store w Databricks,
tenant_id – identyfiaktor tenanta,
authority – identyfikator authority,
database_name – nazwa bazy danych do której się łączymy,
server_name – adres server do którego się łączymy.

Mając już powyższe zmienne tworzymy łańcuch połączeniowy czyli Connection String którego będziemy używać do nawiązania połączenia:

azure_sql_url = f"jdbc:sqlserver://${server_name}:1433;database=${database_name}"

Chcemy komunikować się używając Azure Active Directory tak więc musimy spróbować pobrać token uwierzytelniający właśnie z tego serwisu, zrobimy to przy pomocy poniższego kodu (używając zarejestrowanego wcześniej Service Principala):

context = adal.AuthenticationContext(authority)
token = context.acquire_token_with_client_credentials(resource_app_id_url, service_principal_id, service_principal_secret)
access_token = token[“accessToken”]

W dalszej kolejności mamy dodatkowe właściwości połączenia zdefiniowane w JSONie:

connectionProperties = {
"accessToken" : access_token,
"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

Mając to wszystko zdefiniowane i jeśli wszystko poszło zgodnie z planem możemy testowo odpytać Azure SQL – ja wywołałem komendę zwracającą wersję SQLa, który odpytujemy, a rezultat umieściłem w data frame, który następnie wyświetliłem:

sql_query = "(select @@VERSION AS aer) aer"
df = spark.read.jdbc(url=jdbcUrl, table=sql_query, properties=connectionProperties)
display(df)

Poniżej rezultat możecie zobaczyć rezultat moich działań:

Wszystko powiodło się bez większego problemu zgodnie z tym czego oczekiwaliśmy. Nic nie stoi na przeszkodzie aby teraz pobrać dowolne dane do data frame i łączyć je z danymi pochodzącymi z innych źródeł tak jak mieliśmy to w zamierzeniu. Mimo wszystko aby osiągnąć zamierzony efekt musieliśmy wykonać kilka dodatkowych kroków aczkolwiek efekt jest zadowalający. W podobny sposób możmey połączyć się do innych usług takich jak Synapse SQL czyli Cosmos DB. Polecam potestować gdyż niejednokrotnie opcja tego typu może dać nam dodatkowe możliwości analizy zgromadzonych danych bez konieczności pisania kodu pobierającego dane do Data Lake.