How to Set Default Schema for PostgreSQL, Greenplum, Snowflake, and Redshift

Question

How can I set a default schema for PostgreSQL, Greenplum, Snowflake, and Redshift?

Environment

HVR 5

Answer

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″

1. Right-click the channel and select New Action.

2. Select Environment.

   

3. 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 named PALLAVI before this action was set.

4. 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.