How to: Use HVR_SQL_INIT environment variable to set a default schema for postgres, greenplum, snowflake and redshift?

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #22300
    Pallavik
    Keymaster

    How to: Use HVR_SQL_INIT environment variable to set a default schema for postgres, greenplum, snowflake and redshift?

     

    Description:

    Every user has a dedicated default schema in greenplum, redshift and snowflake but sometimes users have a need to change this default schema to something else. By default the burst tables and HVR state tables are created in default schema for the user hvr connects to the database as. In this article we will see how we can use environment variable HVR_SQL_INIT to change the default schema.

    Pre-requisites

    1.Channel chn1 is already created

    2.The Target locations are

    a]Snowflake

    b]Redshift

    c]Postgres

    d]Greenplum

    Steps:

    A] Snowflake

    On the Target group add action like below

    /Name=HVR_SQL_INIT /Value=”USE SCHEMA PALLAVI2″

    Step 1] Right click on the channel ->select New Action-> Select Environment

    Step 2] In the next pop up fill the values for Name and Value of the environment variable like below

    In this case PALLAVI2 is the schema where I want my burst tables and state tables created. By default they were getting created in schema named PALLAVI before this action was set.

    Step 3] Initialize the channel with ‘Scripts and Jobs’ and ‘State table’ if you are adding this action to existing running channel.

     

    B] Redshift, Greenplum and Postgres

    Steps for Redshift, Greenplum and Postgres are same.

    Step 1] Same as Step A1].

    Step 2] In the next pop up fill the values for Name and Value of the environment variable like below

    In this case p_test is the schema where I want my burst tables and state tables to be created.

    Step 3] Initialize the channel with ‘Scripts and Jobs’ and ‘State table’ if you are adding this action to existing running channel.

Viewing 1 post (of 1 total)
  • The forum ‘How to’ is closed to new topics and replies.

© 2020 HVR

Test drive Contact us