Using custom schema with Amazon Redshift database

Private eazyBI

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

Database connection setup

Download 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 custom schema in config/schemas/redshift_schema.rb (or use another file name) and create corresponding eazyBI account.

Using example FoodMart database data

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

• Set up your Redshift cluster, create database foodmart and user foodmart.
• Download FoodMart database data (taken from http://wiki.pentaho.com/display/BAD/Amazon+Redshift+Instaview+Template).
• Create S3 bucket and upload foodmart-Redshift/*.txt files in the main directory of this bucket.
• You need some client tool to access 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 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 connection to Redshift in eazybi.toml file as described above. You can use an example schema definition file schemas/foodmart_schema.rb as a value for schema_rb parameter.