In this article we will cover, in detail, how we can connect Amazon Redshift data source using Helical Insight and create reports/dashboard using it.Prerequisites :
- Amazon Redshift Cluster instance on Amazon Web Services.
- Dialect Configuration
- Datasource Creation
- Metadata Creation
- Adhoc Reports
Dialect ConfigurationDialect is used for generating SQL query as per the database selected. In Helical Insight Application, following are the steps to configure dialect :
- Go to
- Open “sqlDialects.properties” file in edit mode using text-editor such as notepad, notepad++.
- Add the below present line at the end of the file in a new line.
- Now, save the file.
MySQL dialectis mapped with the selected database. In result, a sql query will be generated which may or may not support the selected database.
Create DatasourceThis process involves establishing a connection between Helical Insight application and a Source Databases. There are 2 methods available for creating a Datasource in Helical Insight:
- Front End Approach
- Back End Approach
- Login to Helical Insight Application. ( For default credentials, click here )
- Click on “Home” button and select “Adhoc” from the list.
- On entering Adhoc Page, click on “Create” button in Datasources
- Select the “DataSource Type” from the given list. In this case, “Managed Datasource” will be used.
- Then, enter “DataSource Name” say “redshift cluster” ( any name can be assigned ) and select driver from the given list. Here, redshift jdbc driver will be selected with the name “com.amazon.redshift.jdbc.Driver”. Following are the steps to update list :
- Download the latest Amazon Redshift JDBC Driver. Click here to download.
- Once you have downloaded the driver, you need to put that driver in a specific folder and then immediately it will start appearing in the drop down list of Datasource. Click here for more details
- Enter the URL which is a JDBC URL. Like one shown below Sample URL :
- Enter Username which is a Master Username.
- Then, enter Password which is a Mater Password.
- Now, click on “Test Connection” in order to verify entered credentials.
- After clicking Test Connection, if the connection is established then a notification “The connection test is successful” appears.
- Click “Save DataSource”
- Hereon, a connection is established with an intended redshift cluster present on Amazon Server.
- Then, next step involves creation of metadata. After saving datasource, from the same page, click on “Metadata” (at the top right )
- Then, click on “Workflow”.
- Go to “hi-repository” folder where a Helical Insight Application is installed and then open the folder.
- Create a New Folder with any name say “Amazon Redshift” inside “hi-repository” folder.
- Now, open a text-editor like notepad, notepad++ , and so on.
- Then enter the following details as given below :
<EFWD> <DataSources> <Connection id="1" name="Redshift Cluster EFWD" type="sql.jdbc"> <Driver>com.amazon.redshift.jdbc.driver</Driver> <Url>jdbc:redshift://hirdstest.cuqoxvtywysw.us-west-2.redshift.amazonaws.com:5439/hidbtest</Url> <User>hirdsuser</User> <Pass>Hirdsuser1</Pass> </Connection> </DataSources> </EFWD>
- Connection ID : User can assign any ID (numeric and unique)
- name : Name of the Datasource to be shown in the Datasource list
- Url : JDBC URL can be accessed from Cluster Configuration details. Like one shown below.
- User: Database Username
- Password: Database password
Create MetadataFor creating a metadata it is mandatory to have established datasource connection which is explained in create datasource.
- On clicking “Workflow” from “Metadata” tab, “Datasource Type” selection page is displayed.
- Select the “DataSource Type” from the given list.
- For Method 1 : Select DataSource Type: Managed Datasource
- For Method 2 : Select DataSource Type: Plain JDBC Datasource
- For Method 1 : Datasource Name “redshift cluster” is used. Any name can be assigned during configuration of datasource.
- For Method 2 : Datasource Name “Redshift Cluster EFWD” is used. Any name can be assigned during configuration of datasource in efwd file.