How to Set Default Schema for PostgreSQL, Greenplum, Snowflake, and Redshift
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
- Channel
chn1
is already created - The target locations are: Snowflake, Redshift, PostgreSQL, and Greenplum
Snowflake
On the Target group, add action Environment with parameters /Name=HVR_SQL_INIT and /Value=”USE SCHEMA PALLAVI2″
Right-click the channel and select New Action.
Select Environment.
In the pop-up dialog, fill the values for /Name and /Value with the environment variable like below:
In this case,
PALLAVI2
is the schema where the burst tables and state tables are created. By default, they were getting created in schema namedPALLAVI
before this action was set.Initialize the channel with Scripts and Jobs and State tables if you are adding this action to the existing running channel.
Redshift, Greenplum, and PostgreSQL
Steps for Redshift, Greenplum and PostgreSQL are the same as for Snowflake.
In this case, p_test
is the schema, where the burst tables and state tables are created.