The Generated Artifacts zip file contains files with placeholders for the target environment parameters.
You should replace all the placeholders to deploy your Databricks Stage JDBC Target System.
Replace the Placeholders
A toolkit is provided in the generated artifacts to replace the placeholders.
It is composed of a Powershell script to execute: replace_placeholders.ps1.
For executing commands with Powershell later in this article, please use at least version 7 provided by Microsoft. If you would like to install the latest version, please check here.
The process to follow is:
- Update the replacement_config.json file with your values:
- Insert the value to replace into the value node
- The database_directory placeholder should contain the path to create the Target Parquet files.
- If you are using our Databricks environment example, the path is the one inside the Storage Account chosen to store the Target Data Lake
abfss://claire-datalake@bgdatabricksdatalake1.dfs.core.windows.net/stage/- claire-datalake is the name of the folder we created inside the Storage Account
- bgdatabricksdatalake1 is the name of the Storage Account itself
- If you are using our Databricks environment example, the path is the one inside the Storage Account chosen to store the Target Data Lake
- The database_directory placeholder should contain the path to create the Target Parquet files.
- Insert the value to replace into the value node
-
-
- The jdbc-url placeholder should contain the JDBC URL to connect to the source through JDBC:
- If you are using our Databricks environment example, the JDBC URL is the following:
jdbc:sqlserver://bgsqlserver.database.windows.net:1433;encrypt=true;databaseName=AdventureWorks2019;trustServerCertificate=true;
- bgsqlserver is the name of the Azure SQL Server
- AdventureWorks2019 is the name of the Azure database
- If you are using our Databricks environment example, the JDBC URL is the following:
- The jdbc-user and jdbc-password placeholders should have credentials to connect to the JDBC source.
- You can enter the user and password directly in plain text, for example:
- The jdbc-url placeholder should contain the JDBC URL to connect to the source through JDBC:
-
{
"name": "databricksXXXXXXstagejdbc#adventureworks#jdbc_user",
"value": "'user_name'",
"secretname": ""
},
{
"name": "databricksXXXXXXstagejdbc#adventureworks#jdbc_password",
"value": "'password'",
"secretname": ""
}
Putting the user and password inside quotes 'user_name' and 'password' is essential.
- Or you can use KeyVault to store a secret. In this case, the syntax should be:
- dbutils.secrets.get(scope='jdbc', key='<name of the secret>')
The placeholder for database_name is already filled. Feel free to change it according to your needs.
The placeholder schema_name should be left empty if you are not using Unity Catalog (See Unity Catalog (Project) Property). Fill it in if you are configured to use the Unity catalog inside Databricks.
All the paths should have a slash appended at the end: abfss://claire-datalake@bgdatabricksdatalake1.dfs.core.windows.net/stage/
If you are using Airflow as a Load Control environment, please update the following placeholders:
- databricks_job#databricks_job#user_name: it must be your databricks user name
- databricks_job#databricks_job#notebook_task_path: it must be the location of the Jupyter Notebooks into Databricks
- In our example: /Workspace/Users/<username>/Artifacts_DVDM/
- databricks_job#databricks_job#existing_cluster_id: it must be the ID of the cluster to use. To find it:
- Open Databricks
- Click on the Compute menu on the left-hand-side:
- Open the Cluster created in your Databricks environment
- Click on the More ... button:
- Select the View JSON option
- The Cluster ID is available on the top of the JSON:
Replace the placeholders in the files:
- Open Windows Powershell (or equivalent) in the replace_placeholders.ps1 location:
- Execute the following command :
-
.\replace_placeholders.ps1
- You should have a similar result:
- The configured values in all generated artifacts replaced the placeholders
- You can now use these files and deploy your Target system
Some parameters can be added to the replace_placeholders.ps1 command.
All are described and available by executing:
.\replace_placeholders.ps1 - help
The -ReplacementConfigPath parameter mainly permits using a replacement_config.json file in another path. It is beneficial when you are in a development mode for your project.
Example of usage:
.\replace_placeholders.ps1 -ReplacementConfigPath "C:\TEMP\Replacement config files\replacement_config_
If you run into any issues, check our Troubleshooting section for Generate Artifacts: