Define a Target System

workflow diagram

This section explains how to define the target system where the data warehouse and on-target sources will reside, and how to set up a connection between Yotilla and the target system.

This operation must be done using TinML. For more information, see also DEFINE TARGET SYSTEM and DEFINE CONNECTION in the TinML reference documentation.

You can only define one target system for a data warehouse.

Prerequisites

  • A target database running on one of the supported technologies (Exasol, Amazon Redshift, or Snowflake).

  • Roles and users with adequate privileges must exist on the target database.

    For more information, see Prepare the Target Database.

  • Yotilla must have network access to the target system.

    For information about how to set up access and allow traffic over the network, refer to the documentation for the target system.

Step 1: Define the Target System

In the TinML editor, use the following statement to define the target system:

DEFINE TARGET SYSTEM <name> USING TECHNOLOGY <technology>;

Parameter Description
<name> A name for the new target system (can be set freely)
<technology> The technology platform of the target system. Currently allowed values are EXASOL, SNOWFLAKE, and REDSHIFT.

Examples

DEFINE TARGET SYSTEM "MY_DWH" USING TECHNOLOGY EXASOL; 

Expected Result

Target System "MY_DWH" defined.

Step 2: Define the Target System Connection

In the TinML editor, use the following statement to define the connection to the target system:

DEFINE CONNECTION 
    FOR TARGET SYSTEM <name>
    IN <environment> 
    TECHNOLOGY VERSION <technology version>
    ACCESS TYPE JDBC
    URI <connection string>
    USER <user> IDENTIFIED BY <password>
;
Placeholder Description
<name> The target system name (as defined in the previous step).
<environment> The Yotilla environment to use. For more information, see Environments.
<technology version>

A version identifier of the target database technology platform.

This parameter is used internally and does not represent the actual release version of the target database.

Currently allowed values are EXASOL_7, SNOWFLAKE_1, and REDSHIFT_1.
<connection string> The JDBC connection string to use to connect to the target system.
<user>, <password> The credentials to use for authentication on the target system.

Example - Exasol

DEFINE CONNECTION 
    FOR TARGET SYSTEM "MY_DWH" IN DEVELOPMENT
    TECHNOLOGY VERSION EXASOL_7
    ACCESS TYPE JDBC
    URI "jdbc:exa:my_host:8563"
    USER "my_user" IDENTIFIED BY "my_password"
;

// If you cannot use TLS and you trust the server, you can include the fingerprint in the URL:

DEFINE CONNECTION 
    FOR TARGET SYSTEM "MY_DWH" IN DEVELOPMENT
    TECHNOLOGY VERSION EXASOL_7
    ACCESS TYPE JDBC
    URI "jdbc:exa:my_host/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563"
    USER "my_user" IDENTIFIED BY "my_password"
;

Example - Snowflake

DEFINE CONNECTION
  FOR TARGET SYSTEM "MY_DWH"
  IN DEVELOPMENT
  TECHNOLOGY VERSION SNOWFLAKE_1
  ACCESS TYPE JDBC
URI "jdbc:snowflake://myorg_myaccount.snowflakecomputing.com?warehouse=my_wh&db=my_dwh&role=yotilla_execute_templates_role"
  USER "my_user" IDENTIFIED BY "my_password"
;

  • The warehouse parameter in the JDBC connection string is only required if a default warehouse for the user has not been defined in Snowflake.

  • The role parameter in the connection string is required if a default role for the user has not been defined, or if the defined default role for the user is not yotilla_execute_templates_role (see Database Preparation - Snowflake).

Example - Amazon Redshift

DEFINE CONNECTION 
    FOR TARGET SYSTEM "MY_DWH" IN DEVELOPMENT
    TECHNOLOGY VERSION REDSHIFT_1
    ACCESS TYPE JDBC
    URI "jdbc:redshift://my_redshift_endpoint.eu-central-1.redshift.amazonaws.com:5439/my_dwh"
    USER "my_user" IDENTIFIED BY "my_password"
;

Expected Result

Target System Connection for Target System "MY_DWH" in Environment "DEVELOPMENT" defined.

Verification

To verify that a target system connection has been defined, use the TinML command SHOW TARGET SYSTEM CONNECTIONS.

SHOW TARGET SYSTEM CONNECTIONS;

Next step: Define Source Systems