Power BI Write-back do Azure SQL: Nativní řešení pomocí Fabric User Data Functions

Vojtěch Šíma
25 minutes ago
10 min read

tl;dr Fabric User Data Functions ti umožní připojení k položkám mimo prostředí Fabric, pokud máš připravenou síť a credentials. Pro zprovoznění write-backu do Azure SQL Database potřebuješ Private Endpoint, Service Principal a Azure Key Vault. Mrkni do článku, jak všechno implementovat.

Může Fabric User Data Function interagovat s položkami mimo Fabric?

Ano. Azure SQL Database, SharePoint nebo v podstatě jakékoli API můžeš spustit z Fabric User Data Function (UDF), pokud dokážeš napsat logiku v Pythonu a nakonfigurovat potřebnou síť nebo přihlašovací údaje. Nutno podotknout, že ty poslední dvě věci můžou být docela výzva.

Pro ty, co to čtou poprvé: User Data Function (UDF) je položka v Microsoft Fabric, která ti umožňuje definovat Python skript běžící na vyžádání (on demand). Ideálně se spouští přímo z Power BI reportu (nebo je to alespoň ten nejzajímavější use case). Zatímco UDF umožňují velmi snadnou interakci s ostatními položkami ve Fabricu (jako jsou Fabric SQL Databases, Lakehouses nebo Warehouses), s dostatkem odhodlání je můžeš ohnout přesně podle svých specifických potřeb.

Chceš-li se o základech UDF dozvědět víc, mrkni na můj úvodní článek, kde pokrývám jednodušší scénář (zápis do Fabric SQL Database) a najdeš tam návod krok za krokem, jak nastavit vše od nuly (UDF, Power BI a governance).

Write-back do Azure SQL Database

Proč by ses chtěl namáhat s writebackem do Azure SQL Database, když můžeš dělat všechno nativně s Fabric SQL Database?

Hlavním důvodem je cena. Pokud jsi na malé kapacitě (jako je F2), každá CU (Capacity Unit) se počítá. Pokud chceš instantní write-back (a proč bys nechtěl), použití Fabric SQL tě může stát peníze navíc, protože musíš platit za udržování aktivní kapacity pro SQL endpoint jen pro případ, že by někdo provedl write-back v 1:00 ráno. A obecně platí, že provozovat databázi ve Fabricu bude dražší než v Azure.

Azure SQL Database může být naproti tomu technicky zdarma. Pro scénář write-backu, který budu popisovat, plně postačuje nabídka Azure SQL Database 'try for free'. Ačkoliv má svá omezení, zápis několika stovek řádků je hravě v limitu. Navíc ti to umožní:

Používat databázi zcela mimo Fabric (např. pro jiné provozní reporty).
Udržet vše uklizené na jednom místě, pokud už je tvůj ekosystém silně postavený na Azure.

Vím, že cílem Fabricu je mít vše na jednom místě, ale pokud nezačínáš na zelené louce a už žiješ v Azure ekosystému, můžeš si to tímto způsobem usnadnit.

Ještě než začneme, nastavení může působit složitě, ale není to tak hrozné. Budeš ale potřebovat trpělivost, zvláště pokud nejsi Azure Portal admin.

Prerekvizity

Aby nám to fungovalo, budeme potřebovat specifické nastavení. Pokud nemáš práva provádět v Azure Portálu „čachry machry“, sežeň si kamaráda, který je má.

Service principal
Azure SQL Server & konfigurace
Dodatečné Azure nastavení
Nový workspace s Fabric kapacitou (jakoukoli)
Managed private endpoint
Azure Key Vault (volitelné)
Variable library (volitelné)

Service principal

Začněme tím nejjednodušším. Service Principal bude entita, která bude zapisovat do naší databáze. Kromě udělení práv pro čtení/zápis v databázi (což uděláme později přes SQL) tady nemusíš dělat nic složitého.

V Azure Portal jdi na App registrations.
Klikni na New registration, vyplň název, zvol „Single Tenant“ (obvykle) a klikni na Register.

Jakmile je registrace hotová, jdi na Certificates & secrets.
Klikni na New client secret, zadej popis, zvol expiraci a klikni na Add.

Důležité: Okamžitě zkopíruj Value a dočasně si ji ulož (později ji přesuneme do Azure Key Vault). Jakmile tuhle stránku opustíš, tenhle secret už nikdy neuvidíš.

Nakonec jdi na záložku Overview a zkopíruj si Application (client) ID a Directory (tenant) ID.

Azure SQL Server & konfigurace

Budu předpokládat, že už máš Azure SQL Server. Pokud ne, tady je návod, jak získat verzi zdarma.

V Azure Portal jdi na Services -> Azure SQL.
V přehledu sjeď na Azure SQL Database a klikni na Try for free.

Poskytni detaily (Subscription, Resource Group, Database Name).
Pro server vyber region, kde máš ostatní zdroje. (Poznámka: Měl jsem problémy s některými evropskými regiony, takže jsem použil West US 2).

Poté budeš přesměrován na Deployment; to by mělo být automatické. Pokud během nasazení narazíš na problémy, je to velmi pravděpodobně špatným regionem nebo nedostatečnými právy.

Network Configuration: Jakmile je nasazeno, otevři svůj Server resource, jdi na Security -> Networking a ujisti se, že Public Access je nastaveno na „Selected networks“.

Nakonec zaškrtni políčko Allow Azure services and resources to access this server.

Pokaždé, když se připojíš ze zařízení, dostaneš chybu firewallu a budeš se sem muset přidat.

Přístup v Azure SQL Database

Jakmile máš přístup k databázi, vytvoř novou tabulku nebo uprav existující a přidej db_datawriter a db_datareader pro náš service principal účet.

CREATE USER [article-demo-sql-writer] FROM EXTERNAL PROVIDER;

Jméno musí být identické s názvem service principal. Poté uděl práva pro čtení/zápis:

ALTER ROLE db_datareader ADD MEMBER [article-demo-sql-writer];
ALTER ROLE db_datawriter ADD MEMBER [article-demo-sql-writer];

Tyto role by měly existovat automaticky.

Dodatečné Azure nastavení

V závislosti na stavu tvého Azure možná budeš muset trochu „poladit“ nastavení. Přirozeně musíš mít práva k úpravě subscription, Contributor by měl stačit, ale pokud nemáš vlastní firmu nebo nejsi Azure admin, pravděpodobně budeš muset někoho požádat.

Nejtěžší částí bude nastavení sítě. Sám jsem bojoval s chybějící konfigurací v samotné subscription, konkrétně v Resource providers.

Jdi do své Subscription, klikni na Settings, poté Resource providers.
Zkontroluj Microsoft.Network, Microsoft.Fabric a Microsoft.ManagedNetworkFabric.
Ujisti se, že status je nastaven na Registered. Je pravděpodobné, že nebude. Zaregistruj je kliknutím na tři tečky a volbou registrace.

Nový workspace s Fabric Kapacitou (jakoukoli)

Proč nový workspace? Budeme nastavovat private endpoint a to s sebou nese jisté problémy. Pokud například používáš notebooky, protože poběžíš na privátním subnetu, získání nového poolu pro tvůj PySpark nebo čistý Python bude značně ovlivněno. Startovací čas pro každý (i non-Spark) může trvat několik minut. To je docela otravné, zvláště pokud používáš non-Spark notebooky a jsi zvyklý na instantní starty sessions, nebo používáš Starter Pool, který je také optimalizován pro rychlý start.

Toto je jeden z problémů, které Private Endpoint může přinést, takže jak v roce 2020, doporučuji izolaci. Naštěstí UDF žije ve zcela odlišném prostředí a starty sessions ho neovlivňují, takže i s tímto můžeš dosáhnout okamžitého write-backu.

Přirozeně, UDF je Fabric item, stejně tak Managed Private Endpoints, takže potřebuješ Fabric kapacitu. Dobrou zprávou je, že aktuálně by to mělo fungovat i na Fabric F2 a trial kapacitách.

Protože vytváříš nový workspace, ujisti se, že máš povolená nastavení jako Users can create Fabric Items v Power BI Admin Portal atd.

Managed private endpoint

Jakmile máš workspace, jdi do Workspace settings a najdi Outbound networking.

Zde vytvoř New managed private endpoint.

Vyplň pole odpovídajícím způsobem.

Create managed private endpoint — Create a managed private endpoint

Název a request message jsou na tobě.
Resource identifier je SQL Server. Cesta ke zdroji musí být přesná a je v tomto formátu:

/subscriptions/{subscriptions-guid}/resourceGroups/{display-name}/providers/Microsoft.Sql/servers/{display-name}

Pro snadné získání cesty: v Azure Portal jdi na svůj SQL Server resource, podívej se na URL v prohlížeči a zkopíruj zmíněnou část. Ujisti se, že začíná úvodním lomítkem.

Jakmile to vyplníš, v reálném čase se ti zpřístupní Target sub-resource a můžeš vybrat „Azure SQL Database“. Pokud jsi vše udělal správně, zazelená se ti tlačítko Create a můžeš ho zmáčknout.

Pokud se ti všechno povedelo, uvidíš takovýto obrázek:

Pro schválení:

Jdi na Azure SQL Server resource v Azure Portal.
Jdi na Security, Networking, poté Private Access.
Měl bys vidět svůj požadavek.
Schval jej (Approve).

Poté se vrať do nastavení workspace a obnov požadavek; měl by být schválený (Approved).

Azure Key Vault (volitelné)

Azure Key Vault je čistě volitelný, ale vysoce doporučený. Umožní ti to vyhnout se hardcodování hodnot do kódu UDF a jen se připojit k Azure Key Vault a získat je bezpečněji. Pokud máš informace o service principal stále ve schránce, můžeme jít stavět; jinak si tyto informace nejprve obstarej.

V Azure Portal jdi na Key Vaults, vyber svůj key vault nebo vytvoř nový (potřebuješ ten nejzákladnější).
Poté jdi na Objects, Secrets a zvol Generate/Import pro tři secrety.
Pokud je tato akce zakázána, chybí ti práva. Tvůj účet (tvůj osobní účet, který bude spouštět UDF) potřebuje Key Vault Contributor (nebo alespoň Key Vault Secrets User), pokud secrety vytvoří někdo jiný za tebe.
Vytvoř tři secrety:
- tenant-id
- article-demo-sql-writer-secret
- article-demo-sql-writer-client-id

Pochopitelně, název je na tobě.

Variable library (volitelné)

Pokud se dnes cítíš jako fajnšmekr, můžeš udělat celé nastavení lépe spravovatelným pomocí Variable library. Můžeme uložit URL Azure SQL Serveru, název databáze a URL Azure Key Vaultu.

Důležitá poznámka: V mém testování způsobilo použití Variable Library ve spojení s Managed Private Endpoints značnou latenci. Je tam pravděpodobně minutová prodleva, která nakonec selže, a pak proces rychle pokračuje. V tuto chvíli jsem nenašel elegantní workaround, který by nebyl „overkill“. Proto ti ukážu i kód, kde informace o proměnných natvrdo vypíšeš (hardcode); nejedná se však o bezpečnostní riziko, protože jde pouze o konfigurační data. Celé volání UDF by mělo trvat do 5 sekund (latence při použití variable library je jedna minuta).

Ve svém workspace klikni na New item, Variable library.

Klikni na New variable.

Vytvořme tři:

azure-key-vault-url
writeback-azure-sql-url
writeback-azure-sql-database-name

Všechny tyto informace najdeš v příslušných resources na záložce Overview, pod Vault URI, Server name a názvy dostupných databází.

Klikni na Save

A to je k prerekvizitám vše. Pojďme postavit samotnou User Data Function.

Write-back do Azure SQL Database User Data Function

Konečně to, kvůli čemu jsi přišel. Celkové nastavení bude vypadat takto: pokaždé, když spustíme tuto funkci, vložíme nový řádek s informacemi od uživatele o ID nějaké entity a komentář. To je v podstatě vše.

Pro technický stack použijeme specifické knihovny:

pyodbc
azure-identity
azure-keyvault-secrets

Ve svém workspace vytvoř novou položku, jmenuje se User Data Functions.

Create user data funtions — Create user data functions

Klikni na New function. Poté jdi do Library management a naimportujme knihovny, které potřebujeme.

Klikni na Add from PyPI a vyber následující knihovny. Verze, kterou použiješ, je na tobě; toto je veřejná knihovna a není prověřena samotným Microsoftem, pokud nejsou poskytovatelem. Takže tahle část je na tvoji zodpovědnost. (nicméně 2 z nich jsou od Microsoftu, a tu třetí Microsoft doporučuje ve svých návodech, takže asi cajk).

Poté musíme knihovny publikovat a nainstalovat. Klikni na Publish. To může trvat pár minut.

Jakmile je publikováno, ujisti se, že jsi obnovil prohlížeč, a pak můžeš začít používat nainstalované knihovny.

Pro použití Variable library uživatelsky přívětivým způsobem (díky aktualizaci z listopadu 2025) můžeme tuto fabric položku přidat pomocí Manage connections. Takže udělej následující:

Klikni na Manage connection.
Klikni na Add connection a zvol Variable Library, kterou jsi vytvořil o pár kroků zpět.

Poté klikni vpravo na Edit a dej jí hezčí alias. Protože lze použít pouze písmena, dejme třeba 'varlib'.

Potom zkopíruj kód (jednu z variant, já osobně jdu bez Variable library) a klikni Publish.

User Data Function volání s Variable library

import fabric.functions as fn
import pyodbc
import struct
from azure.keyvault.secrets import SecretClient
from azure.identity import ClientSecretCredential

udf = fn.UserDataFunctions()


@udf.connection(alias="varlib", argName="varLib")
@udf.generic_connection(argName="keyVaultClient", audienceType="KeyVault")
@udf.function()
def azure_sql_serer_writeback(varLib: fn.FabricVariablesClient, keyVaultClient: fn.FabricItem, itemId: str, comment: str)-> str:
    
    key_vault_url = varLib.getVariables().get("azure-key-vault-url")
    azure_sql_server_name = varLib.getVariables().get("writeback-azure-sql-url")
    azure_sql_database_name = varLib.getVariables().get("writeback-azure-sql-database-name")       

    try:        
        key_vault_credentials = keyVaultClient.get_access_token() 
        client = SecretClient(vault_url=key_vault_url, credential=key_vault_credentials)
        
        tenant_id = client.get_secret("tenant-id").value
        article_demo_sql_writer_client_id = client.get_secret("article-demo-sql-writer-client-id").value
        article_demo_sql_writer_secret = client.get_secret("article-demo-sql-writer-secret").value
        
        
        sql_cred = ClientSecretCredential(tenant_id, article_demo_sql_writer_client_id, article_demo_sql_writer_secret)
        token_obj = sql_cred.get_token("https://database.windows.net/.default")
        token_bytes = token_obj.token.encode("UTF-16-LE")
        token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
        
        conn_str = (
            "DRIVER={ODBC Driver 18 for SQL Server};"
            f"SERVER={azure_sql_server_name};DATABASE={azure_sql_database_name};"
            "Encrypt=yes;TrustServerCertificate=no;ConnectRetryCount=3;"
        )
        
        with pyodbc.connect(conn_str, attrs_before={1256: token_struct}) as conn:
            conn.timeout = 5
            cursor = conn.cursor()
            
            cursor.execute(
                f"""
                INSERT INTO [schema].[table] ([column1], [column2], [column3])
                VALUES (?, ?, GETDATE())
                """,
                (itemId, comment)
            )
            return f"OK"

    except Exception as e:
        return f" ERROR: {str(e)}"

User Data Function volání bez Variable library

import fabric.functions as fn
import pyodbc
import struct
from azure.keyvault.secrets import SecretClient
from azure.identity import ClientSecretCredential

udf = fn.UserDataFunctions()


@udf.generic_connection(argName="keyVaultClient", audienceType="KeyVault")
@udf.function()
def azure_sql_serer_writeback(keyVaultClient: fn.FabricItem, itemId: str, comment: str)-> str:
    
    key_vault_url = "https://yours.vault.azure.net/"
    azure_sql_server_name = "azure-sql.database.windows.net"
    azure_sql_database_name = "database-name"   

    try:        
        key_vault_credentials = keyVaultClient.get_access_token() 
        client = SecretClient(vault_url=key_vault_url, credential=key_vault_credentials)
        
        tenant_id = client.get_secret("tenant-id").value
        article_demo_sql_writer_client_id = client.get_secret("article-demo-sql-writer-client-id").value
        article_demo_sql_writer_secret = client.get_secret("article-demo-sql-writer-secret").value
        
        
        
        sql_cred = ClientSecretCredential(tenant_id, article_demo_sql_writer_client_id, article_demo_sql_writer_secret)
        token_obj = sql_cred.get_token("https://database.windows.net/.default")
        token_bytes = token_obj.token.encode("UTF-16-LE")
        token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
        
        
        conn_str = (
            "DRIVER={ODBC Driver 18 for SQL Server};"
            f"SERVER={azure_sql_server_name};DATABASE={azure_sql_database_name};"
            "Encrypt=yes;TrustServerCertificate=no;ConnectRetryCount=3;"
        )
        
        with pyodbc.connect(conn_str, attrs_before={1256: token_struct}) as conn:
            conn.timeout = 5
            cursor = conn.cursor()
            
            cursor.execute(
                f"""
                INSERT INTO [schema].[table] ([column1], [column2], [column3])
                VALUES (?, ?, GETDATE())
                """,
                (itemId, comment)
            )
            return f"OK"

    except Exception as e:
        return f"ERROR: {str(e)}"

Rychlý přehled kódu

Předtím, než projdu kód, velký shout-out pro @Riccardo Perico a jeho blog post, díky kterému jsem zjistil, že user identity je nyní dostupná, nebo přesněji, že nyní můžeme přistupovat k Azure Key Vault v User Data Functions a nemusíme hardcodovat secrety v kódu nebo je předávat jako variables.

Definice funkcí & Připojení

@udf.connection(alias="varlib", argName="varLib")
@udf.generic_connection(argName="keyVaultClient", audienceType="KeyVault")
@udf.function()
def azure_sql_server_writeback(varLib: fn.FabricVariablesClient, keyVaultClient: fn.FabricItem, itemId: str, comment: str) -> str:

Tato část je nejdůležitější. Dekorátory zde odedřou těžkou práci s autentizací a sítěmi.

Řádek 1 (@udf.connection): Navazuje spojení s naší Variable Library pomocí aliasu varlib (který jsme definovali v „Manage connections“ UI). Toto spojení vkládá do funkce jako argument varLib.
Řádek 2 (@udf.generic_connection): Toto je ta „žhavá novinka“, která nám umožňuje připojení k Azure Key Vault. Parametr audienceType="KeyVault" je klíčový; říká Fabricu, aby vygeneroval token specificky pro přístup ke Key Vaultu.
Řádek 4 (Funkce): Definujeme samotnou Python funkci. Všimni si, že názvy argumentů (varLib, keyVaultClient) musí odpovídat parametrům argName v dekorátorech výše.

Pokud chceš verzi bez Variable Library, jednoduše odstraň první řádek s dekorátorem a parametr varLib z definice funkce

Získání konfigurace

Následně načteme detaily infrastruktury.

key_vault_url = varLib.getVariables().get("azure-key-vault-url")
    azure_sql_server_name = varLib.getVariables().get("writeback-azure-sql-url")
    azure_sql_database_name = varLib.getVariables().get("writeback-azure-sql-database-name")

Zde definujeme lokální proměnné, které drží hodnoty získané z Variable Library.

Pro hardcoded verzi: Pokud jsi přeskočil Variable Library, nahradíš řádky výše natvrdo zapsanými řetězci. Od tohoto bodu jsou obě verze identické.

Tahání secretů z Key Vaultu

Nyní vyřešíme bezpečnost. Použijeme identitu UDF (uživatele nebo vlastníka) k odemčení Key Vaultu a následně načteme credentials pro Service Principal.

key_vault_credentials = keyVaultClient.get_access_token() 
client = SecretClient(vault_url=key_vault_url, credential=key_vault_credentials)

tenant_id = client.get_secret("tenant-id").value
article_demo_sql_writer_client_id = client.get_secret("article-demo-sql-writer-client-id").value
article_demo_sql_writer_secret = client.get_secret("article-demo-sql-writer-secret").value

Nejprve získáme access token z keyVaultClient. Poté inicializujeme standardní Azure SecretClient s tímto tokenem. Nakonec stáhneme tři konkrétní secrety, které jsme uložili dříve (Tenant ID, Client ID a Client Secret).

Ověření (Authentication) do SQL Serveru

sql_cred = ClientSecretCredential(tenant_id, article_demo_sql_writer_client_id, article_demo_sql_writer_secret)
token_obj = sql_cred.get_token("https://database.windows.net/.default")
token_bytes = token_obj.token.encode("UTF-16-LE")
token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)

Použijeme secrety Service Principala k vyžádání přístupového tokenu pro Azure SQL Database. Nicméně, SQL driver je trochu vybíravý; nemůže vzít token jen jako prostý text, jak to dělá Python. Vyžaduje specifický bytový formát. Poslední dva řádky řeší tento překlad („přebalení“ tokenu), aby driver mohl přečíst naše credentials správně a bez chyb.

Samotná exekuce Write-backu

conn_str = (
        "DRIVER={ODBC Driver 18 for SQL Server};"
        f"SERVER={azure_sql_server_name};DATABASE={azure_sql_database_name};"
        "Encrypt=yes;TrustServerCertificate=no;ConnectRetryCount=3;"
    )
    
    with pyodbc.connect(conn_str, attrs_before={1256: token_struct}) as conn:
        conn.timeout = 5
        cursor = conn.cursor()
        
        cursor.execute(
            f"""
            INSERT INTO [schema].[table] ([column1], [column2], [column3])
            VALUES (?, ?, GETDATE())
            """,
            (itemId, comment)
        )
        
        return "OK"

except Exception as e:
    return f"ERROR: {str(e)}"

Zde nejprve navážeme spojení pomocí ovladače pyodbc. Jakmile ho máme, můžeme začít posílat SQL dotazy, například výše uvedený insert snippet.

Přirozeně, pokud to chceš mít víc „chatty“, můžeš změnit return na něco konkrétnějšího. Mohl by ses dotázat na data zpět a zobrazit potvrzovací zprávu jako „ID 1234 saved successfully.“

Pokud jde vše podle plánu, vrátíme jednoduše text „OK“. Pokud dojde k chybě, dostaneme díky Exception handlingu přesný popis toho, co se pokazilo.

Power BI / Translytical Task Flows nastavení

Jakmile naše UDF publikujeme, můžeme ho implementovat v Power BI. Protože jsem detaily vysvětlil už zde, odkážu tě tam.

Co se týče architektury, samotné Power BI sémantické modely nemusí být na Fabric kapacitě a měl bys použít DirectQuery, když se připojuješ k Azure SQL zdroji.