Working with environment variables

Environment variables allow developers to build more portable expressions, custom ETLs, and Compose configurations, which is especially useful when working with several environments such as DTAP (Development, Testing, Acceptance and Production). Different environments (for example, development and production) often have environment-specific settings such as database names, schema names, and Replicate task names. Variables allow you to easily move projects between different environments without needing to manually configure the settings for each environment. This is especially useful if many settings are different between environments. For each project, you can use the predefined environment variables or create your own environment variables.

Database and schema name variables are supported with the following objects:

• Reusable transformations
• Custom ETLs (Data warehouse and Data marts)
• Mappings lookups
• Mappings and model expressions
• Data mart settings

Limitations and considerations

• Values for predefined variables will be shown in the Compose user interface, but user-defined variables will be shown in variable format (for example, $$${myVariable}).
• Variable names are case sensitive and can include alphanumeric characters, underscores, and periods only.

• User-defined variable definitions will not be included in projects exported as JSON files or in projects exported as CSV files. However, user-defined variable names will be included in the relevant places in the exported files.

  See also: Exporting and importing projects using the CLI and Migrating objects as CSV files.

• Exported CSV objects that are associated with predefined environment variables (for example, a mapping database and schema) cannot contain user-defined environment variables.

   

  For instance, this is not allowed:

   

  Name	Landing Zone Database	Schema
  MyMapping	$$${MyDatabase}	$$${MySchema}

  See also: Migrating objects as CSV files.

Locations and names of predefined variables

Predefined variables are located in the following places:

Predefined data warehouse variables

The following predefined data warehouse connection variables can be set:

• $$${database.Data Warehouse.connectionInputModeStandard} - The value can be "True" or "False". "False" means that Advanced will be used.
• $$${database.Data Warehouse.serverName}
• $$${database.Data Warehouse.port}

• $$${database.Data Warehouse.authenticationMethodSQLServer} - The value can be "True" or "False".

  Information noteRelevant for Microsoft SQL Server only.
• $$${database.Data Warehouse.userName}
• $$${database.Data Warehouse.encryptedPassword}

• $$${database.Data Warehouse.odbcString}

  Information noteOnly relevant when the Advanced connection option is set.

• $$${database.Data Warehouse.jdbcString}

  Information noteOnly relevant when the Advanced connection option is set.

• $$${database.Data Warehouse.warehouse}

  Information noteRelevant for Snowflake only.
• $$${database.Data Warehouse.database}
• $$${database.Data Warehouse.datawarehouseSchema}
• $$${database.Data Warehouse.datamartSchema}

For an explanation of data warehouse settings, see Setting up a data warehouse connection

Predefined landing zone variables

The following predefined landing zone connection variables can be set:

Information noteAs there can be several landing zones, the middle part of the variable name (landing1 below) is the landing zone name and should be replaced with the actual name of your Landing Zone.

• $$${database.landing1.contentType} - Values can be "Full Load Only", "ChangeProcessing", or "Full Load and Change Processing"

• $$${database.landing1.designatedBy} - The value can be "database" or "schema".
• $$${database.landing1.database}
• $$${database.landing1.schema}
• $$${database.landing1.errorMartSchema}
• $$${database.landing1.afterApplyingChanges} - Values can be "Delete" (the default) or "Archive".

• $$${database.landing1.archiveDatabase}

  Information noteOnly relevant if "Archive" is set.

• $$${database.landing1.archiveSchema}

  Information noteOnly relevant if "Archive" is set.
• $$${database.landing1.isAssociatedWithReplicateTask} - The value can be "True" or "False".

• $$${database.landing1.replicateServer}

  Information noteOnly relevant if the Associate with Replicate task option is set.

• $$${database.landing1.replicateTask}

  Information noteOnly relevant if the Associate with Replicate task option is set.
• $$${database.landing1.source.isSourceDefined} - The value can be "True" or "False".
• $$${database.landing1.source.connectionInputModeStandard} - The value can be "True" or "False". "False" means that Advanced will be used.
• $$${database.landing1.source.serverName}
• $$${database.landing1.source.port}

• $$${database.landing1.source.authenticationMethodSQLServer} - The value can be "True" or "False".

  Information noteRelevant for Microsoft SQL Server only.
• $$${database.landing1.source.userName}
• $$${database.landing1.source.encryptedPassword}

• $$${database.landing1.source.odbcString}

  Information noteOnly relevant when the Advanced connection option is set.

• $$${database.landing1.source.jdbcString}

  Information noteOnly relevant when the Advanced connection option is set.
• $$${database.landing1.source.database}
• $$${database.landing1.source.datawarehouseSchema}

For an explanation of landing zone settings, see Defining landing zones.

Predefined data mart variables

The following predefined data mart connection variables can be set:

Information noteAs there can be several data marts, the middle part of the variable name (datamart1 below) is the data mart name and should be replaced with the actual data mart name.

• $$${datamart.datamart1.databaseDefault} - The value can be "True" or "False".
• $$${datamart.datamart1.database}
• $$${datamart.datamart1.tablesSchemaDefault} - The value can be "True" or "False".
• $$${datamart.datamart1.tablesSchema}
• $$${datamart.datamart1.viewsSchemaDefault} - The value can be "True" or "False".
• $$${datamart.datamart1.viewsSchema}

For an explanation of data mart settings, see Modifying data mart settings.

Predefined lookup variables

The following predefined lookup variables can be set:

Information noteAs there are usually multiple mappings and columns, the middle part of the variable name (mapping1.column1 below) are the mapping and column names and should be replaced with the actual mapping and column name.

• $$${mapping.mapping1.column1.lookup.schema}

For an explanation of lookup settings, see Using lookup tables.

Predefined mappings variables

The following predefined mapping variables can be set:

Information noteAs there are usually multiple mappings, the middle part of the variable name (mapping1 below) is the mapping name and should be replaced with the actual mapping name.

• $$${mapping.mapping1.schema}

For an explanation of mappings settings, see Editing column mappings.

Compose environment_variables CLI guidelines

The procedures below describe how to manage environment variables using the Compose CLI. When using the Compose CLI, make sure to adhere to the following guidelines:

• Before running any environment_variables commands, run the Connect command to establish a connection to the Compose Server. For more information on this command, see Connecting to Qlik Compose server.

• Variable names should be specified in the CLI without dollar signs or curly brackets. So, for example, assuming the name of your landing database is MyLanding, $$${database.MyLanding.database} should be specified as database.MyLanding.database.

• When setting a variable with the Compose CLI, any variable names and values with spaces (both user-defined and predefined) must be specified in quotation marks. This means, for example, that all data warehouse variables must be specified with quotation marks as their names always contain a space (for example, "database.Data Warehouse.serverName").

• When setting a Boolean value, use --boolVal instead of --val. For example, to set database.Data Warehouse.connectionInputModeStandard to "True", specify:

  --var "database.Data Warehouse.connectionInputModeStandard" --boolVal true

Working with predefined variables only

When working with predefined environment variables only, the flow is as follows:

1. In the source environment:
   1. Configure your project as desired.

   2. Run the following command to write the predefined environment variables to a JSON file (replacing projectName with the name of your Compose project and JsonFileLocation with the full path of your JSON file):

      ComposeCli environment_variables --command writePredefined --project projectName --jsonFile JsonFileLocation
   Information noteFor security reasons, Compose never writes encrypted passwords to files.
   

1. 1. Create a deployment package or export the project using the Compose CLI.

      See also: Project deployment and Exporting a project.

2. In the target environment:

   1. Deploy the project if you created a deployment package or import the project if you exported it using the Compose CLI.

      See also: Project deployment and Importing a project.

   2. Copy the JSON file created in the source environment to your preferred location.

   3. Edit the JSON file and replace the variable values with the values you want to propagate to the target environment.

      See also: Working with environment variables.

   4. Run the following command to propagate the JSON file variables to the Compose user interface (replacing projectName with the name of your Compose project and JsonFileLocation with the full path of your edited JSON file):

      ComposeCli environment_variables --command setALL --project projectName --jsonFile JsonFileLocation

      Example:  

      ComposeCli environment_variables --command setALL --project analytics --jsonFile C:\compose\compose-variables.json
      Warning noteRunning the setAll command will remove any existing environment variables that are not included in the JSON file. Therefore, if you want to make sure that such variables are not removed when setAll is run, add them to the JSON file.
      Tip note

      You can also set any variable by running the set command. This is especially useful if you do not want the JSON file to include passwords. In this case, you would need to run the following command (shown as an example):

      ComposeCli environment_variables --command set --project MyProject --var encryptedPassword --val g56g56y563%

      When you set a password with the CLI, Compose encrypts the password first and then sets it.

   5. Run the following command to apply the predefined environment variables and complete the process (replacing projectName with the name of your Compose project):

      ComposeCli environment_variables --command applyPredefined --project projectName

Working with user-defined variables only

When working with user-defined environment variables only, the flow is as follows:

You can use user-defined variables in the following places:

• Expressions - in model, mappings (column expressions, data quality and filters) or data mart
• Lookup conditions and expressions
• Custom ETLs

To set user-defined variables:

1. In the source environment:

   1. In the Compose user interface, include your variable in an object that supports user-defined variables (for example, a Custom ETL in a data warehouse task or an expression), and save your settings.

      Example: User-defined variable in a Custom ETL

      
      UPDATE 
      "ROSIE"."DWH2"."TSTG_Employees" 
      SET 
      "Title" = $$${myVar}
      WHERE "EmployeeID" < 150

   2. For user-defined variables, use one of the following methods:

       

      Method 1:

      Set each variable individually by running the following command (replacing projectName with the name of your Compose project, varName with the variable name, and value with the variable value):

      ComposeCli environment_variables --command set --project projectName --var varName --val value

      Example:  

      ComposeCli environment_variables --command set --project MyProject --var myVar --val Manager

       

      Method 2:

      Add the user-defined variables directly to the JSON file as described in Manually editing the JSON file below.

   3. Generate the task(s) with the user-defined variables.

   4. If you set the user-defined variables with the CLI, run the following command to write the user-defined variables to a JSON file (replacing projectName with the name of your Compose project and JsonFileLocation with the full path of your JSON file):

      ComposeCli environment_variables --command writeCLISet --project projectName --jsonFile JsonFileLocation
      

   5. Create a deployment package or export the project using the Compose CLI.

      See also: Project deployment and Exporting a project.

2. In the target environment:

   1. Deploy the project if you created a deployment package or import the project if you exported it using the Compose CLI.

      See also: Project deployment and Importing a project.

   2. Copy the JSON file created in the source environment to your preferred location.

   3. Edit the JSON file and replace the variable values with the values you want to appear in the target environment.

      See also: Working with environment variables.

   4. Run the following command to propagate the JSON file variables to the Compose user interface (replacing projectName with the name of your Compose project and JsonFileLocation with the full path of your edited JSON file)::

      ComposeCli environment_variables --command setALL --project projectName --jsonFile JsonFileLocation
      Warning noteRunning the setAll command will remove any existing environment variables that are not included in the JSON file. Therefore, if you want to make sure that such variables are not removed when setAll is run, add them to the JSON file.

   5. Generate the relevant tasks as well.

Working with both user-defined and predefined variables

When working with both user-defined and predefined environment variables, the flow is as follows:

1. In the source environment:
   1. Configure your project as desired.

   2. Run the following command to write the predefined environment variables to a JSON file (replacing projectName with the name of your Compose project and predefinedJsonFileLocation with the full path of the JSON file that you want to contain your predefined variables):

      ComposeCli environment_variables --command writePredefined --project projectName --jsonFile predefinedJsonFileLocation
   Information noteFor security reasons, Compose never writes encrypted passwords to files.
   

   3. In the Compose user interface, add user-defined variables to supported objects (for example, a Custom ETL in a data warehouse task), and save your settings.

      Example: User-defined variable in a Custom ETL

      
      UPDATE 
      "ROSIE"."DWH2"."TSTG_Employees" 
      SET 
      "Title" = $$${myVar}
      WHERE "EmployeeID" < 150

   4. For user-defined variables, use one of the following methods:

       

      Method 1:

      Set each variable individually by running the following command (replacing projectName with the name of your Compose project, varName with the variable name, and value with the variable value):

      ComposeCli environment_variables --command set --project projectName --var varName --val value

      Example:  

      ComposeCli environment_variables --command set --project MyProject --var myVar --val Manager

       

      Method 2:

      Add the user-defined variables directly to the JSON file as described in Manually editing the JSON file below.

   5. Generate the task with the user-defined variable(s).

   6. If you set the user-defined variables with the CLI, run the following command to write the user-defined variables to a JSON file (replacing projectName with the name of your Compose project and JsonFileLocation with the full path of the JSON file that you want to contain your user-defined variables):):

      ComposeCli environment_variables --command writeCLISet --project projectName --jsonFile userDefinedJsonFileLocation
      

   7. Create a deployment package or export the project using the Compose CLI.

      See also: Project deployment and Exporting a project.

2. In the target environment:

   1. Deploy the project if you created a deployment package or import the project if you exported it using the Compose CLI.

      See also: Project deployment and Importing a project.

   2. Copy the JSON file created in the source environment to your preferred location.

   3. Edit the JSON file and replace the variable values with the values you want to appear in the target environment.

      See also: Working with environment variables.

   4. Run the following command to propagate the JSON file variables to the Compose user interface (replacing projectName with the name of your Compose project and JsonFileLocation with the full path of your edited JSON file)::

      ComposeCli environment_variables --command setALL --project projectName --jsonFile JsonFileLocation
      Warning noteRunning the setAll command will remove any existing environment variables that are not included in the JSON file. Therefore, if you want to make sure that such variables are not removed when setAll is run, add them to the JSON file.

   5. Generate all tasks with user-defined variables.

   6. Run the following command to apply the predefined environment variables and complete the process (replacing projectName with the name of your Compose project):

      ComposeCli environment_variables --command applyPredefined --project projectName

Removing environment variables

Run the "Remove" command if you set a variable that you no longer want to use in the target environment.

1. Run the following command:

   ComposeCli environment_variables --command remove --var VarName --project projectName

   Example:  

   ComposeCli environment_variables --command remove --var MyVariable --project MyProject

2. Generate any task(s) configured with user-defined variables.

Manually editing the JSON file

Before you propagate the JSON file variables to the Compose user interface in the target environment, you need to edit the file and replace the source variable values with the target variable values. It might also be more convenient to create, edit and maintain the JSON file manually instead of (or in addition to) using the writeCLISet and writePredefined commands described above.

The JSON file is split into two sections: “predefinedVariables” and “userDefinedVariables”. When you edit the JSON file, make sure to put predefined variables in the “predefinedVariables” section and user-defined variables in the “userDefinedVariables” section. In addition, make sure to use standard JSON escaping conventions, as shown in the following example:

{
“predefined”: {
"databases.Data Warehouse.serverName": "myhostname"
},
“userDefined”: {
"variable": "value",
"var2": "val2"
}

Propagating the JSON file variables to the Compose user interface

After you have prepared your JSON file and copied it to your preferred location, perform the following procedure to propagate the variables to the Compose user interface:

1. Run the following command to propagate the JSON file variables to the Compose user interface (replacing projectName with the name of your Compose project and JsonFileLocation with the full path of your edited JSON file):

   ComposeCli environment_variables --command setALL --project projectName --jsonFile JsonFileLocation

   Example:  

   ComposeCli environment_variables --command setALL --project MyProject --jsonFile C:\composeVariables\myVariables.json
   Warning noteRunning the setAll command will remove any existing user-defined environment variables from the user-interface, and reset any modified predefined environment variables that are not included in the JSON file to their previous values.

2. If the JSON file contains predefined environment variables, run the following command (replacing projectName with the name of your Compose project):

   ComposeCli environment_variables --command applyPredefined --project projectName

3. If the JSON file contains user-defined environment variables, generate the associated task(s).