Parameterizing Azure Databricks when using SELECT query

[adrianPadurariu]

Hi,

I am trying to parameterize my Azure Databricks connection to create a deployment pipeline. I managed to set parameters for ServerName, HTTP path, and Catalog, but what about a native SELECT query like this:

= Value.NativeQuery(Databricks.Catalogs(ServerNameParameter, HTTPparameter, [Catalog=CatalogParameter, Database=null, EnableAutomaticProxyDiscovery=null]){[Name=CatalogParameter,Kind="Database"]}[Data], "SELECT accountName, mainAccount, entity, entityId, Status, channel, COUNT(messageId) AS totalMessagesCount ,errorGroup#(lf),yyyy,mm,dd#(lf), countryName, countryPrefix#(lf), networkName, originalMCC, originalMNC, originalMCCMNC#(lf)FROM gds_dev.gds_slv_infobip.operation_messages#(lf)#(lf)GROUP BY accountName, mainAccount, entity, entityId, Status, channel, errorGroup, yyyy, mm, dd, countryName, countryPrefix, networkName, originalMCC, originalMNC, originalMCCMNC", null, [EnableFolding=true])

Thank you!

[adrianPadurariu · Accepted solution]

= let
DatabaseName = DatabaseNameParameter,
SQLQuery = "SELECT accountName, mainAccount, entity, entityId, Status, channel,
COUNT(messageId) AS totalMessagesCount, errorGroup, yyyy, mm, dd,
countryName, countryPrefix, networkName, originalMCC, originalMNC, originalMCCMNC
FROM " & DatabaseName & ".gds_slv_infobip.operation_messages
GROUP BY accountName, mainAccount, entity, entityId, Status, channel, errorGroup,
yyyy, mm, dd, countryName, countryPrefix, networkName, originalMCC,
originalMNC, originalMCCMNC",

Data = Value.NativeQuery(
Databricks.Catalogs(ServerNameParameter, HTTPparameter,
[Catalog=CatalogParameter, Database=null, EnableAutomaticProxyDiscovery=null]
){[Name=CatalogParameter,Kind="Database"]}[Data],
SQLQuery,
null,
[EnableFolding=true]
)
in
Data