This document describes the steps to connect Amazon Redshift data warehouse (cloud services) using generic JDBC connection.

Step 1:

To connect with AWS RedShift using JDBC, you need to have redshift JDBC drivers or supporting drivers from vendor.

You can download JDBC drivers from amazon portal based on version.

http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html

Here is the direct link to download .Jar file which supports to connect from BI4.1 direct tools.

https://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC4-1.1.1.0001.jar

Step 2:

Place the downloaded .JAR file where business objects installed (client/server).

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java

Step 3:

Open ‘jdbc.sbo’ file under C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc in notepad/xml editor and add below code to the file:

<ClassPath>

                <Path>C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\RedshiftJDBC4-1.1.1.0001.jar</Path>

</ClassPath>

This looks like below:

<?xml version=”1.0″ encoding=”UTF-8″?><DriverConfiguration xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:noNamespaceSchemaLocation=”../sbo.xsd”>

    <Defaults>

        <Class JARFile=”dbd_jdbc”>com.sap.connectivity.cs.java.drivers.jdbc.JDBCDriver</Class>

<JDBCDriver>

<ClassPath>

<Path>$ROOT$/drivers/java/dbd_jdbcwrapper.jar</Path>

</ClassPath>

<Parameter Name=”JDBC Wrapper”>com.sap.connectivity.cs.java.drivers.jdbc.wrapper.JDBCWrapper</Parameter>

</JDBCDriver>

        <Parameter Name=”Family”>Generic</Parameter>

        <Parameter Name=”SQL External File”>jdbc</Parameter>

        <Parameter Name=”SQL Parameter File”>jdbc</Parameter>

        <Parameter Name=”Description File”>generic_jdbc</Parameter>

        <Parameter Name=”Driver Capabilities”>Query</Parameter>

        <Parameter Name=”Extensions”>jdbc</Parameter>

        <Parameter Name=”Connection Shareable”>Yes</Parameter>

        <Parameter Name=”Shared Connection”>No</Parameter>

        <Parameter Name=”Array Fetch Available”>True</Parameter>

        <!–  define &quot;magic&quot; value 0 for Array Fetch Size to use default JDBC Fetch Size –>

        <Parameter Name=”Array Fetch Size”>0</Parameter>

        <Parameter Name=”Transactional Available”>No</Parameter>

        <Parameter Name=”Array Bind Available”>False</Parameter>

        <Parameter Name=”Max Rows Available”>No</Parameter>

        <Parameter Name=”Query TimeOut Available”>No</Parameter>

        <Parameter Name=”Optimize Execute”>True</Parameter>

    </Defaults>

    <DataBases>

        <DataBase Active=”Yes” Name=”Generic JDBC datasource”>

<JDBCDriver>

<ClassPath>

                               <Path>C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\postgresql-9.2-1004.jdbc3.jar</Path>  — If you use postgresql drivers

                                <Path>C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\RedshiftJDBC4-1.1.1.0001.jar</Path>

                      </ClassPath>

<Parameter Name=”JDBC Class”>$JDBCCLASS$</Parameter>

<Parameter Name=”URL Format”>$DATASOURCE$</Parameter>

</JDBCDriver>

<Parameter Name=”Array Fetch Size”>10</Parameter>

</DataBase>

    </DataBases>

</DriverConfiguration>

Step 4:

Create universe connection in IDT/UDT.

New > Relational connection

Provide name and click on next.

Select type of connection and click next.

Select_Driver.png

Provide User Name, Password, JDBC URL and JDBC Class Details here.

Note: You JDBC URL should include the database name

JDBC URL: jdbc:redshift://xxxxx.redshift.amazonaws.com:0000/database

JDBC Class: com.amazon.redshift.jdbc4.Driver

Now test connection.

 
Test_Connection.png

Click Finish to create connection.

Note: You can use other drivers like postgresql/Greenplum also to connect redshift.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply