Synapse Analytics queryen vanuit Azure Functions

📅2026/01/06👤mark241⏱️5 min

Dit artikel vat samen hoe je queries kunt uitvoeren vanuit Azure Functions met Azure Synapse Analytics.

Stappen

Data transformeren en kopiëren met Data Factory

Bereid eerst de data voor die je wilt analyseren. In dit voorbeeld analyseren we logs die in opslag zijn opgeslagen. Na het omzetten van de data naar een analysevriendelijk formaat, plaatsen we deze in Azure Data Lake Storage Gen2.

Hier gebruiken we een service genaamd Data Factory. Met Data Factory kopiëren we data. Omdat je tijdens het kopiëren het formaat kunt kiezen, zetten we het om naar Parquet.

Hieronder staat een Bicep-voorbeeld. In dit voorbeeld worden wildcards en expressies gebruikt in de padinstellingen, maar pas deze onderdelen aan op je eigen vereisten. Vaak is het duidelijker om het in Data Factory Studio via de UI aan te maken. Pipelines die je in Studio maakt, kun je ook exporteren als Bicep-bestanden.

bicep

1param factoryName string
2param tags object
3@secure()
4param subscriptionId string
5param sourceResourceGroupName string
6param inputStorageAccountName string
7param outputStorageAccountName string
8 
9param triggerStartTime string = utcNow('yyyy-MM-ddTHH:mm:ssZ')
10 
11resource factory 'Microsoft.DataFactory/factories@2018-06-01' = {
12  identity: {
13    type: 'SystemAssigned'
14  }
15  location: resourceGroup().location
16  name: factoryName
17  properties: {
18    publicNetworkAccess: 'Enabled'
19  }
20  tags: tags
21}
22 
23resource pipelines 'Microsoft.DataFactory/factories/pipelines@2018-06-01' = {
24  parent: factory
25  name: 'clicklog-pipeline'
26  dependsOn: [
27    inputDataSet
28    outputDataSet
29  ]
30  properties: {
31    activities: [
32      {
33        dependsOn: [
34          {
35            activity: 'LookupBlob'
36            dependencyConditions: [
37              'Succeeded'
38            ]
39          }
40        ]
41        policy: {
42          timeout: '0.12:00:00'
43          retry: 0
44          retryIntervalInSeconds: 30
45          secureOutput: false
46          secureInput: false
47        }
48        name: 'CopyAccessLog'
49        userProperties: [
50          {
51            name: 'Source'
52            value: 'container-name/<your path>/'
53          }
54          {
55            name: 'Destination'
56            value: 'destination-container-name//'
57          }
58        ]
59        type: 'Copy'
60        inputs: [
61          {
62            referenceName: 'SourceDataset'
63            type: 'DatasetReference'
64            parameters: {}
65          }
66        ]
67        outputs: [
68          {
69            referenceName: 'DestinationDataset'
70            type: 'DatasetReference'
71            parameters: {}
72          }
73        ]
74        typeProperties: {
75          source: {
76            type: 'JsonSource'
77            storeSettings: {
78              type: 'AzureBlobStorageReadSettings'
79              recursive: true
80              wildcardFolderPath: {
81                value: 'your-path/@{concat(\'y=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'yyyy\'))}/@{concat(\'m=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'MM\'))}/@{concat(\'d=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'dd\'))}'
82                type: 'Expression'
83              }
84              wildcardFileName: '*'
85              enablePartitionDiscovery: false
86            }
87            formatSettings: {
88              type: 'JsonReadSettings'
89            }
90          }
91          sink: {
92            type: 'ParquetSink'
93            storeSettings: {
94              type: 'AzureBlobStorageWriteSettings'
95              copyBehavior: 'FlattenHierarchy'
96            }
97            formatSettings: {
98              type: 'ParquetWriteSettings'
99            }
100          }
101          enableStaging: false
102          validateDataConsistency: false
103          translator: {
104            type: 'TabularTranslator'
105            mappings: [ // Define mappings here
106              {
107                source: {
108                  path: '$[\'time\']'
109                }
110                sink: {
111                  name: 'time'
112                  type: 'DateTime'
113                }
114              }
115              // ...
116            ]
117          }
118        }
119      }
120    ]
121    policy: {
122      elapsedTimeMetric: {}
123    }
124  }
125}
126 
127resource inputBlob 'Microsoft.DataFactory/factories/linkedservices@2018-06-01' = {
128  parent: factory
129  name: 'AzureBlobStorageInput'
130  properties: {
131    annotations: []
132    type: 'AzureBlobStorage'
133    typeProperties: {
134      serviceEndpoint: 'https://${inputStorageAccountName}.blob.${environment().suffixes.storage}/'
135      accountKind: 'StorageV2'
136    }
137    // For remaining properties, see LinkedService objects
138  }
139}
140 
141resource outputBlob 'Microsoft.DataFactory/factories/linkedservices@2018-06-01' = {
142  parent: factory
143  name: 'AzureBlobStorageOutput'
144  properties: {
145    annotations: []
146    type: 'AzureBlobStorage'
147    typeProperties: {
148      serviceEndpoint: 'https://${outputStorageAccountName}.blob.${environment().suffixes.storage}/'
149      accountKind: 'StorageV2'
150    }
151    // For remaining properties, see LinkedService objects
152  }
153}
154 
155resource outputDataSet 'Microsoft.DataFactory/factories/datasets@2018-06-01' = {
156  parent: factory
157  name: 'DestinationDataset'
158  dependsOn: [
159    outputBlob
160  ]
161  properties: {
162    annotations: []
163    description: 'string'
164    folder: {
165      name: 'string'
166    }
167    linkedServiceName: {
168      referenceName: 'AzureBlobStorageOutput'
169      type: 'LinkedServiceReference'
170    }
171    typeProperties: {
172      location: {
173        type: 'AzureBlobStorageLocation'
174        folderPath: {
175          value: '@{concat(\'y=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'yyyy\'))}/@{concat(\'m=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'MM\'))}/@{concat(\'d=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'dd\'))}'
176          type: 'Expression'
177        }
178        container: 'container-name'
179      }
180      compressionCodec: 'snappy'
181    }
182    type: 'Parquet'
183    // For remaining properties, see Dataset objects
184  }
185}
186 
187resource inputDataSet 'Microsoft.DataFactory/factories/datasets@2018-06-01' = {
188  parent: factory
189  name: 'SourceDataset'
190  dependsOn: [
191    inputBlob
192  ]
193  properties: {
194    annotations: []
195    description: 'string'
196    folder: {
197      name: 'string'
198    }
199    linkedServiceName: {
200      referenceName: 'AzureBlobStorageInput'
201      type: 'LinkedServiceReference'
202    }
203    typeProperties: {
204      location: {
205        type: 'AzureBlobStorageLocation'
206        folderPath: 'your-path'
207        container: 'container-name'
208      }
209    }
210    type: 'Json'
211    schema: {
212      type: 'object'
213      properties: {
214        time: {
215          type: 'string'
216        }
217        ...
218      }
219    }
220    // For remaining properties, see Dataset objects
221  }
222}
223 
224resource DailyTrigger 'Microsoft.DataFactory/factories/triggers@2018-06-01' = {
225  parent: factory
226  name: 'DailyTrigger'
227  dependsOn: [
228    pipelines
229  ]
230  properties: {
231    annotations: []
232    // runtimeState: 'Started'
233    pipelines: [
234      {
235        pipelineReference: {
236          referenceName: 'your-pipeline-name'
237          type: 'PipelineReference'
238        }
239        parameters: {}
240      }
241    ]
242    type: 'ScheduleTrigger'
243    typeProperties: {
244      recurrence: {
245        frequency: 'Day'
246        interval: 1
247        startTime: triggerStartTime
248        timeZone: 'UTC'
249        schedule: {
250          minutes: [
251            0
252          ]
253          hours: [
254            0
255          ]
256        }
257      }
258    }
259    // For remaining properties, see Trigger objects
260  }
261}
262 
263output managedIdentityPrincipalId string = factory.identity.principalId

De resources die hier voorkomen zijn:

Resource	Uitleg
Microsoft.DataFactory/factories	De Data Factory-instantie
Microsoft.DataFactory/factories/pipelines	Definitie van de pipeline. De pipeline voert onder andere data-kopieeracties uit.
Microsoft.DataFactory/factories/linkedservices	Linked services die input- en output-storage koppelen
Microsoft.DataFactory/factories/datasets	Input- en output-datasets
Microsoft.DataFactory/factories/triggers	Trigger-instellingen. Niet nodig als je geen automatische trigger wilt.

Synapse aanmaken

Vervolgens deployen we Synapse Analytics. Omdat er standaard een ingebouwde serverless SQL-pool is, maken we daar een database aan.

Database aanmaken

Maak een database aan in de ingebouwde serverless SQL-pool.

sql

1CREATE DATABASE [myDB];
2GO
3 
4USE [myDB];
5GO

Externe databron toevoegen

Maak een externe databron aan en verbind deze met de data lake. Maak hierbij WorkspaceIdentity aan en laat de toegang tot deze externe databron verlopen via de rechten van WorkspaceIdentity.

sql

1CREATE MASTER KEY
2ENCRYPTION BY PASSWORD = 'StrongPassword_ChangeThis!';
3 
4CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
5WITH IDENTITY = 'Managed Identity';
6GO
7 
8CREATE EXTERNAL DATA SOURCE MyDataLake
9WITH (
10    LOCATION = 'https://<your storage name>.blob.core.windows.net/<container name>',
11    CREDENTIAL = WorkspaceIdentity
12);
13GO

Externe tabel aanmaken

Maak een externe tabel aan en stel DATA_SOURCE in op de externe databron die je eerder hebt gemaakt. Voor LOCATION geef je het blob-pad op. Wildcards worden ondersteund.

sql

1-- Schema maken
2CREATE SCHEMA ext AUTHORIZATION dbo;
3GO
4 
5CREATE SCHEMA func AUTHORIZATION dbo;
6GO
7 
8-- Bestandsformaat maken
9CREATE EXTERNAL FILE FORMAT [ParquetFormat]
10WITH (FORMAT_TYPE = PARQUET);
11GO
12 
13-- Externe tabel maken
14CREATE EXTERNAL TABLE ext.myTable
15(
16  -- Kolomdefinities
17    time           datetime2      NULL,
18    propertyA     nvarchar(512)  NULL,
19    propertyB     nvarchar(512)  NULL,
20    ...
21)
22WITH
23(
24    LOCATION    = 'y=*/**',
25    DATA_SOURCE = [MyDataLake],
26    FILE_FORMAT = [ParquetFormat]
27);
28GO

View aanmaken

Maak een view aan en beperk de kolommen waar Functions toegang toe heeft.

sql

1CREATE VIEW func.viewOfMyTable
2AS
3SELECT
4  [time],
5  [propertyA],
6  [propertyB],
7  ...
8FROM ext.myTable
9WHERE <voeg hier een voorwaarde toe indien nodig>;
10GO

Rechten geven aan de service principal

Dit is een configuratie om vanuit CI/CD (pipeline) database-rechten toe te voegen. Als je dit niet van plan bent, kun je deze sectie overslaan.

Gebruik de naam van de SPN die door de pipeline wordt gebruikt om een externe gebruiker aan te maken en rechten toe te kennen.

sql

1CREATE USER [pipeline-sp] FROM EXTERNAL PROVIDER;
2GO
3 
4ALTER ROLE db_accessadmin ADD MEMBER [pipeline-sp];
5GO
6 
7ALTER ROLE db_owner ADD MEMBER [pipeline-sp];
8GO

Geef de Directory Readers-rol in Entra ID aan de managed identity van de Synapse-workspace

Als je dynamisch rechten aan Functions wilt geven, kun je de Function-naam opgeven. De workspace zoekt dan automatisch de objectId van de managed identity die bij die Function-naam hoort en kent de rechten toe. Om dit te laten werken, moet de workspace zelf de Directory Readers-rol hebben.

Voeg de rol toe aan de workspace identity via Entra ID > Rollen en beheerders > Directory Readers > Toewijzingen toevoegen. De objectId van de managed identity van de workspace kun je in Synapse Studio vinden onder het beheer-menu Credentials.

Rechten aan Functions toekennen vanuit de pipeline

Wanneer je een nieuwe Function deployt, geef je die Function toegang tot de database. Om dit op Azure Pipelines uit te voeren heb ik de volgende stappen gebruikt.

Een SQL-script voorbereiden
De Function-naam in het script dynamisch vervangen tijdens de pipeline-run
Het SQL-script uitvoeren met SqlAzureDacpacDeployment@1

Hieronder leg ik dit stap voor stap uit.

SQL voorbereiden

Bereid SQL voor zoals hieronder. Het deel FunctionAppMiName wordt later vervangen door de doel-Function-naam.

sql

1USE [myDB];
2 
3IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'FunctionAppMiName')
4BEGIN
5    PRINT 'Creating user [FunctionAppMiName] FROM EXTERNAL PROVIDER...';
6    CREATE USER [FunctionAppMiName] FROM EXTERNAL PROVIDER;
7END
8ELSE
9BEGIN
10    PRINT 'User [FunctionAppMiName] already exists.';
11END;
12 
13IF NOT EXISTS (
14    SELECT 1
15    FROM sys.database_role_members rm
16    JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
17    JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
18    WHERE r.name = N'db_datareader'
19      AND m.name = N'FunctionAppMiName'
20)
21BEGIN
22    PRINT 'Adding [FunctionAppMiName] to db_datareader...';
23    ALTER ROLE db_datareader ADD MEMBER [FunctionAppMiName];
24    GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO [FunctionAppMiName];
25    GRANT SELECT ON OBJECT::func.viewOfMyTable TO [FunctionAppMiName];
26    DENY SELECT ON SCHEMA::ext TO [FunctionAppMiName];
27END
28ELSE
29BEGIN
30    PRINT 'Already in db_datareader.';
31END;

Korte toelichting op de rechten:

db_datareader is nodig om data te kunnen lezen. Ik heb ook referentierechten op WorkspaceIdentity toegevoegd, omdat de externe databron zonder deze rechten niet toegankelijk was. Verder geef ik rechten op de view en wil ik directe queries op externe tabellen voorkomen, dus ik doe een DENY op het schema.

Function-naam invoegen

Ik heb het SQL-bestand herschreven met een PowerShell@2-task zoals hieronder, maar elke methode is prima zolang je de Function-naam in het bestand kunt vervangen.

bash

1- task: PowerShell@2
2    displayName: 'Generate grant-function-mi.sql from template'
3    inputs:
4        targetType: 'inline'
5        pwsh: true
6        script: |
7        $templatePath = "$(System.DefaultWorkingDirectory)/drop/sql/grant-function-mi.sql.template"
8        $outputPath   = "$(System.DefaultWorkingDirectory)/drop/sql/grant-function-mi.sql"
9 
10        if (-not (Test-Path $templatePath)) {
11            throw "Template not found: $templatePath"
12        }
13 
14        # Template inlezen
15        $content = Get-Content $templatePath -Raw
16 
17        # Vervangen (waarden invullen via pipeline-variabelen)
18        $content = $content -replace 'FunctionAppMiName', '$(deploy_resources.functionName)'
19 
20        # Uitvoermap aanmaken als die ontbreekt
21        $dir = Split-Path $outputPath
22        if (-not (Test-Path $dir)) {
23            New-Item -Path $dir -ItemType Directory -Force | Out-Null
24        }
25 
26        # SQL wegschrijven
27        $content | Set-Content $outputPath -Encoding UTF8
28 
29        Write-Host "Generated SQL file:"
30        Get-Content $outputPath

SQL uitvoeren

Voer het herschreven SQL uit. Bijvoorbeeld met een task zoals hieronder.

bash

1- task: SqlAzureDacpacDeployment@1
2    displayName: 'Grant db_datareader to Function MI (Synapse serverless)'
3    inputs:
4      # Azure-verbinding (naam van de service connection)
5      azureSubscription: '${{parameters.azureServiceConnection}}'
6 
7      # Synapse serverless SQL endpoint
8      ServerName: '${{parameters.synapseWorkspaceName}}.sql.azuresynapse.net'
9      DatabaseName: 'myDB'
10 
11      # Authenticatie (met de SPN van de service connection)
12      SqlUserName: ''   # Kan leeg blijven
13      SqlPassword: ''   # Kan leeg blijven
14      AuthenticationType: 'servicePrincipal'
15 
16      # Uitvoermodus: SQL-script (geen DACPAC)
17      deployType: 'SqlTask'
18      SqlFile: '$(System.DefaultWorkingDirectory)/drop/sql/grant-function-mi.sql'  # Pad naar het hierboven aangemaakte .sql-bestand
19 
20      # Opties (indien nodig)
21      IpDetectionMethod: 'AutoDetect'

In mijn geval heb ik SqlAzureDacpacDeployment@1 gebruikt, maar met deze methode kun je de Function-naam niet als parameter doorgeven. Daarom is de aanpak om het SQL vooraf te herschrijven zodat de Function-naam erin staat, en vervolgens de task uit te voeren.

Het belangrijkste is dat je de Function rechten geeft zoals in het SQL-bestand staat; je hoeft niet exact deze procedure te volgen.

Query vanuit Functions

Vanuit Functions stuur je een query naar de view zoals hieronder. In .NET gebruik ik het pakket Microsoft.Data.SqlClient.

csharp

1var queryString = @"
2SELECT TOP (@maxItemCount)
3  [time],
4  [requestUri]
5  -- ...
6FROM func.viewOfMyTable
7WHERE
8  [time] >= @startDate
9  AND [time] <= @endDate
10ORDER BY
11  [time] DESC;
12";

Samenvatting

Om te queryen vanuit Functions moet je rechten toekennen aan de Function. Als je rechten vanuit een CI/CD-pipeline wilt toevoegen, heb je ook een extra permissie-instelling voor de SPN nodig, wat wat ingewikkelder kan zijn. Omdat er zowel managed identity-permissies als database-toegangsrechten meespelen, is het belangrijk om deze niet door elkaar te halen.

In dit artikel hebben we een externe databron en een view gemaakt. Je zou ook direct vanuit Functions met OPENROWSET toegang kunnen krijgen tot de data, maar dat vereist doorgaans bredere rechten voor de Function; daarom heb ik die aanpak hier niet gekozen.