Snowflake Cloud Data Warehouse V2 Connector > Migrating a mapping > Use a different object path for the migrated mapping
  

Use a different object path for the migrated mapping

After you migrate the mapping, you can use a different object path to run the mapping from the new environment.
In this scenario, before you migrate the mapping, you can change the object metadata, runtime attributes, or the connection attributes to reflect the object path in the migrated environment. You do not have to edit or update the mapping in the new environment.
As a rule, when you specify the database, schema, or table in the advanced properties, connection, or object properties, Data Integration honors the attributes in the following order of precedence:
  1. 1. Runtime advanced attributes. The advanced properties such as database, schema, table, and role in the Source, Target, or Lookup transformation in a mapping.
  2. 2. Connection attributes. Attributes such as database and schema set in the Additional JDBC URL Parameters in the connection properties.
  3. 3. Object metadata. The object selected in the Source, Target, or Lookup transformation in a mapping.

Migration options

When you migrate, you can choose from one of the following options to update the object path:

Option 1. Update the connection properties to reference the new object

When you import the mapping into Org 2, in the Review Connections section, you can change the existing connection to map to the connection that has access to the specified database, schema, and table in Org 2.

Option 2. Override the properties from the advanced properties in the mapping or elastic mapping

Before the migration, specify the required database, schema, table name, and role for the object from Org 2 in the advanced properties of the Org 1 mapping.
After the migration, when you run the mapping, the Secure Agent uses the configured advanced parameters to override the object specified in the mapping imported from Org 1.

Option 3. Parameterize the properties in the mapping or elastic mapping

You can choose to parameterize the advanced attributes, such as the database, schema, table name, and role before the migration. You can configure input parameters, in-out parameters, and parameter files in the mapping. When you use a parameter file, you can save the parameter file on a local machine or in a cloud-hosted directory. After you migrate the mapping, do not edit or update the mapping. If you have used in-out parameters for the advanced attributes such as for the database, schema, table name, and role, you can update these from the parameter file.
Parameterizing only the advanced properties, but not the object in the mapping or elastic mapping
If you want to parameterize only the advanced properties and use them at runtime, select a placeholder object in the object properties in the mapping and then specify an override to this placeholder object from the advanced properties. Ensure that the placeholder object contains the same metadata as the corresponding table that you specify as an override. When you run the mapping, the value specified in the advanced property overrides the placeholder object.
Parameterizing both the object and the advanced properties
If you want to keep both the Snowflake object type and the advanced fields parameterized, you must leave the Allow parameter to be overridden at runtime option unselected in the input parameter window while adding the parameters, and then select the required object at the task level. When you run the task, the values specified in the advanced properties take precedence.

Parameterization rules

Consider the following rules to parameterize the object and advanced properties:

General rules and guidelines

Applicable object types, transformations, and fields

The following table lists the object types and the fields in the advanced properties of a mapping that you can retain when you migrate to the new environment:
Object Type
Supported advanced properties
Single object
Database, schema, role, table
Multiple objects
Database, schema, role
Query
SQL override, role
Note: The SQL override and custom query must be fully qualified.

Multiple sources

Consider the following guidelines:

Advanced filter and table name override combination

If the mapping contains an advanced filter for the object, for example, <Table2>, in the query options section and an override to the table name in the advanced properties, consider the following rules to override the table:
In mappings, consider one of the following options:
In elastic mappings, use the table name specified as an override in the advanced properties directly in the advanced filter condition. For example, <Table2>.id >= 1

SQL override and custom query combination

In the mapping, when you specify an SQL override and the object type used is a custom query, ensure that the table you specified in the custom query and the SQL override contains the same metadata. The custom query and SQL override must be fully qualified. For example, Select * from DB1.Schema.Table1
When the mapping contains an SQL override and custom query combination, do not specify the database or schema in the advanced source properties in the mapping and in the Additional JDBC URL Parameters field in the Snowflake connection If you do, the mapping fails.
When you configure a partially parameterized custom query, you must specify the default value for the parameters. For example, when you specify Select * from $$DB1.$$Schema.$$Table1, you must add a placeholder value as the default value while creating the in-out parameters for DB1, Schema1, and Table1.
If you have selected parameter as the source type in the mapping, you must ensure to not select the Allow parameter to be overridden at runtime option. You can then select the custom query in the mapping task.
For more information about the supported overrides in combination with an SQL override from the advanced properties, see SQL override scenarios from the advanced properties.

Schema change handling

You cannot dynamically refresh the data object schema at runtime. You must maintain the same metadata for the table selected in the source, target, or lookup transformations and the corresponding advanced field overrides, as schema change handling is not applicable.