Using custom schema with Amazon Redshift database

Private eazyBI

You can use Private eazyBI custom schema with a data warehouse in the Amazon Redshift database. It is recommended to use the Redshift database only if you are running Private eazyBI on an Amazon EC2 instance in the same Amazon region as it is necessary to have high-speed network connection between the Private eazyBI application server and Redshift database servers.

Database connection setup

Download the Redshift JDBC 4.1 driver and copy this RedshiftJDBC41-*.jar  file to eazybi_private/lib directory.

In eazybi.toml configure which eazyBI account should use Redshift connection (in this example the account with a name "Redshift account"). Replace redshift-* placeholders with corresponding host, database, username and password values.

# Settings for accounts with custom schemas
[accounts."Redshift account"]
driver = "jdbc"
jdbc_driver = "com.amazon.redshift.jdbc41.Driver"
jdbc_url = "jdbc:redshift://your-host-name.redshift.amazonaws.com:5439/redshift-database"
username = "redshift-user"
password = "redshift-user-password"
schema_rb = "schemas/redshift_schema.rb"

After that create the custom schema in config/schemas/redshift_schema.rb (or use another file name) and create the corresponding eazyBI account.

Using example FoodMart database data

If you would like to test the Redshift database then you can import example FoodMart database data:

• Set up your Redshift cluster, create the database foodmart and user foodmart.
• Download FoodMart database data (taken from http://wiki.pentaho.com/display/BAD/Amazon+Redshift+Instaview+Template).
• Create an S3 bucket and upload foodmart-Redshift/*.txt files in the main directory of this bucket.
• You need some client tool to access the Redshift database. For example, you can use SQL Workbench/J as recommended in http://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-prereq.html
• Execute foodmart-Redshift/FoodmartDDL_Redshift.sql in your Redshift foodmart database to create tables.
• To avoid using PDI for data import you can use the attached FoodmartCopy_Redshift.sql file.  In this file replace <S3_Bucket_Name>, <aws_access_key_id>, <aws_secret_access_key_id> with corresponding access credentials.
• Execute this file in your Redshift foodmart database to load data in tables.

After that configure the connection to Redshift in the eazybi.toml file as described above. You can use an example schema definition file schemas/foodmart_schema.rb as a value for the schema_rb parameter.