Qlik Replicate and Databricks Lakehouse (Delta) target endpoint: manipulate multiple databases within single connection

[john_wang]

While using Databricks Lakehouse (Delta) as a target endpoint, it would be highly beneficial for users to have the option to write data to multiple databases within a single target endpoint connection.

Some specific business use cases involve managing more than 30 databases under a single catalogue. Creating over 30 separate target endpoint connections in Qlik Replicate to handle these databases is a big challenge.

In this article, we will explain the solutions to how we can manage multiple databases within a single connection. The same approach can be used in other common databases, including Databricks (Cloud Storage), Microsoft SQL Server, PostgreSQL, and other similar databases.

Resolution

Changing the Target table schema is the equivalent of changing the specified database name in the endpoint settings.

How to manipulate multiple databases within single connection

In the endpoint definition, specify the "Database" name, this is the default database. In our screenshot example, the default database is named "APACDB".

We may use "schema" to identify the non-default database name, which will write different tables of data to different databases within a single connection.

There are two options, Table level, and Global level:

1. In Table Settings (see Table Settings for details):
   1. Open General
   2. Select Table Schema
2. In Global Rules (see Defining global rules for details):
   1. Click New rule
   2. Go to Transformation
   3. Rename schema 
      
      Define a rule which database should be used for the given table(s). For example, apply a global transformation rule to rename schemas from %.% to $AR_M_SOURCE_SCHEMA:
      
      
      

Environment

Qlik Replicate  all versions
Databricks Lakehouse (Delta) all versions
Databricks (Cloud Storage) all versions

Internal Investigation ID(s)

Support case #00122351