Preparing Amazon Redshift user data sources for Unica Campaign

Follow these guidelines to use Amazon Redshift as a data source for Unica Campaign.

About this task

Unica Campaign supports Amazon Redshift for customer tables. Redshift is not supported for system tables.

Use the Amazon native ODBC driver for connectivity and configure the PostgreSQL template in Unica Campaign for each datasource. See the Unica Campaign Administrator's Guide for important information about configuring Campaign|partitions|partition[n]|dataSources|PostgreSQL. Some properties do not have default values, so you must supply them. Other properties might need to be adjusted for your specific configuration. For information about supported versions, see theRecommended Software Environments and Minimum System Requirements. For information about Amazon Redshift, see http://aws.amazon.com/redshift/

Procedure

  1. Download and configure the Amazon Redshift ODBC driver for Redshift. Driver Name : Amazon Redshift ODBC driver (version - 1.4.11.1000 –64-bit), Unix ODBC driver manager (Version - 2.3.7). See https://docs.aws.amazon.com/redshift/latest/mgmt/install-odbc-driver-linux.html
    Note: Use unicaODBC driver version 2.3.7 or above.
  2. Validate connection and authentication details: Use isql to test the connection on Linux™. Use the Microsoft™ Windows™ ODBC Driver Manager for Windows™.
  3. Import PostgreSQLTemplate.xml using configTool (a utility supplied with Unica Platform).
  4. Fill in the newly added PostgreSQL template with the user database details.
  5. Configure the Amazon Redshift user database.
  6. Restart the application server and the Unica Campaign listener server.

What to do next

Follow the steps below to test the connection with Amazon Redshift.
Note: See the following link to get the cluster connection string: http://docs.aws.amazon.com/redshift/latest/mgmt/configuring-connections.html#connecting-drivers

On Windows™:

  1. Open Windows™ ODBC Driver Manager.
  2. Add a System DSN and select the Amazon Native driver.
  3. Provide the connection string and other details.
  4. Test the connection using ODBC driver test connection button or the Unica Campaign CxnTest utility.

On Linux™:

  1. Configure LD_LIBRARY_PATH (or LIBPATH on AIX®) to include the location of the driver library files.
  2. Configure ODBCINI to point to the odbc.ini file location.
  3. Configure the odbc.ini file for the Redshift user database. For example:


    [ODBC]
    Trace = yes
    TraceFile = /tmp/OdbcTrace.log
    #[ODBC Data Sources]
    AmazonRedshift=Amazon Redshift

    [AmazonRedshift]
    This key is not necessary and is only to give a description of the data source.
    driver_name=Amazon_Redshift
    Description=Amazon Redshift ODBC Driver (64-bit) DSN

    #[Driver: The location where the ODBC driver is installed to.]
    Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

    Required: These values can also be specified in the connection string.
    Host=<Redshift_Database_Name>
    Port=<Port>
    Database=<database_Name>
    locale=en-US
    BoolsAsChar=0

  4. Test the connection using isql or the Unica Campaign CxnTest and odbctest utility.
  5. Import PostgreSQLTemplate.xmlfor Amazon Redshift and configure it.
  6. For Amazon Redshift loader utility. Please refer the Campaign Administrator guide for more details.
Note: Extract PB fails to run if you select Target Data source as Amazon Redshift . So as recommended by AWS support team we need to use "BoolsAsChar=0" in odbc.ini