When I set the @query_result_separator to either space or comma, it outputs the data in one column, separated by the separator defined. To get it to output the data into its original columns, press TAB keyboard as the value for the @query_result_separator.
Instead of hard coding the credentials in the Notebook, we can reference them in Azure Key Vault (AKV).
I am authenticating my API using header and key, so I created 2 secret keys in AKV to store the API header and API key.
I am accessing my ADLS gen 2 using ADLS access key, so I created another secret in AKV to store the ADLS access key.
To establish a connection from Azure Databricks to ADLS, I use Azure Key Vault-backed scope. A scope is collection of secrets identified by a name.
Steps to configure the Azure Key Vault-backed scope for Azure Databricks:
Go to <a href=”https://https://<databricks-instance>#secrets/createScope. This URL is case sensitive; scope in createScope must be uppercase. To get the databricks-instance, launch the Azure Databricks workspace. Copy the URL as highlighted below, which is the databricks-instance.
Replace the databricks-instance with the URL you have just copied.
Enter the Scope Name and set the Mange Principal to All Users.
Go to Azure Key Vault to get the DNS Name and Resource ID. Enter the DNS Name and Resource ID for Azure Key Vault.
Hit the Create button to create the scope.
To test, open a Notebook in Databricks and then enter the python code below: display(dbutils.secrets.listScopes())
To get the secret from Azure Key Vaults, use the scope to retrieve the secrets. See the python code below in the Notebook: adls_account_key = dbutils.secrets.get(scope = "akv_databricks_secretscope", key = "adlsAccountKey") apiKey = dbutils.secrets.get(scope = "akv_databricks_secretscope", key = "APIUserKey") apiHeader = dbutils.secrets.get(scope = "akv_databricks_secretscope", key = "APIHeader")
headers= {apiHeader: apiKey}
To access ADLS gen 2, use the python code below. Replace the <access key> with adls_account_key.
My API config file in JSON is stored in the bronze container of the ADLS. To read this config file and cache the parsed results, use the python code below:
The API response is in XML form. To get each item in the tree and write to ADLS:
root=ET.fromstring(response_text) #Iterate through each element of the root
for child in root.findall('device'):
id = child.attrib.get('id')
itemid = child.attrib.get('itemid')
name = child.attrib.get('name')
description = child.attrib.get('description')
typeX = child.attrib.get('type')
subtype = child.attrib.get('subtype')
ipaddress = child.attrib.get('ipaddress')
vendor = child.attrib.get('vendor')
enabled = child.attrib.get('enabled')
modified = child.attrib.get('modified')
#Create Spark dataframe from a list of tuples
df = spark.createDataFrame(data, schema)
#Write to ADLS gen 2 as csv
”’
You get multiple files in a folder because spark writes each shuffle partition in-place out to a “part…” file to avoid network I/O. You can use coalesce to bring all the shuffles into a single partition and write it out to a single file but be mindful of the performance implications.
Spark writes split the csv into parts and created _commited, _started and _SUCCESS files and place them all in a folder, as shown below. To prevent it from splitting the csv into parts, use the COALESCE command in your code, as mentioned above.
I set up a build pipeline on my SQL Server database repository and I am using Visual Studio 2022.
My build pipeline failed to run with the error below:
##[error]C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Microsoft\VisualStudio\v16.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(573,5): Error MSB4181: The "SqlBuildTask" task returned false but did not log an error.
It has picked up the MSBuild.exe from the Visual Studio 2019 path instead of from Visual Studio 2022.
To fix this, set the Agent Specification to windows-2022, instead of windows-latest.
If you are using Microsoft-hosted agent windows-latest or windows-2022, with your task, it will use VS2022 by default. If you’d like to use VS2019, change the agent to “Windows-2019”.
” Power BI doesn’t import data over connections that operate in DirectQuery mode. Instead, the semantic model returns results from the underlying data source whenever a report or dashboard queries the semantic model. Power BI transforms and forwards the queries to the data source.
Because Power BI doesn’t import the data, you don’t need to run a data refresh. However, Power BI still performs tile refreshes and possibly report refreshes, as the next section on refresh types explains. A tile is a report visual pinned to a dashboard, and dashboard tile refreshes happen about every hour so that the tiles show recent results. You can change the schedule in the semantic model settings.”
So, it’s important to set up an automatic page refresh on the report itself. To set it, open the pbix file then go to the Format properties, as shown below.
ChinChin's space 