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:
I] 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.
II] 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.