Show Menu
TOPICS×

Specific configurations by database type

Depending on the external databases that you want to be able to access from Adobe Campaign, you will need to carry out certain specific configurations. These configurations essentially involve installing drivers and declaring environment variables that belong to each RDBMS on the Adobe Campaign server.
As a general rule, you need to install the corresponding client layer on the external database on the Adobe Campaign server.
Compatible versions are listed in Campaign Compatibility Matrix .

Configure access to Snowflake

Snowflake connector is available for hosted and on-premise deployments. For more on this, refer to this page .

Snowflake on CentOS

  1. Download the ODBC drivers for Snowflake. Drivers for Snowflake can be found here .
  2. You then need to install the ODBC drivers on CentOs with the following command:
    rpm -Uvh unixodbc
    rpm -Uvh snowflake-odbc-2.20.2.x86_64.rpm
    
    
  3. After downloading and installing the ODBC drivers, you need to restart Campaign Classic. To do so, run the following command:
    /etc/init.d/nlserver6 stop
    /etc/init.d/nlserver6 start
    
    
  4. In Campaign Classic, configure your Snowflake external account in Campaign Classic. From the Explorer , unfold the Administration menu.
  5. Unfold the Platform menu and click External accounts .
  6. Select the out-of-the box Snowflake external account.
  7. To configure the Snowflake external account:
    • Server
      URL of the Snowflake server.
    • Account
      Name of the user.
    • Password
      User account password.
    • Database
      Name of your database.
  8. Click the Parameters tab then the Deploy function button to create functions.
The connector supports the following options:
Option
Value
Description
workschema
Database schema to use for work tables
warehouse
Name of the default warehouse to use. It will override the user's default.
TimeZoneName
By default empty, which means that the system time zone of the Campaign Classic app server is used. The option can be used to force the TIMEZONE session parameter.
For more on this, refer to this page .
WeekStart
0, 1-7
By default set to 0. (WEEK_START session parameter)
For more on this, refer to this page .
UseCachedResult
TRUE/FALSE
By default set to TRUE. This option can be used to disable Snowflake cached results (USE_CACHED_RESULTS session parameter)
For more on this, refer to this page .

Snowflake on Debian

  1. Download the ODBC drivers for Snowflake. Drivers for Snowflake can be found here .
  2. You then need to install the ODBC drivers on Debian with the following command:
    apt-get install unixodbc
    apt-get install snowflake-odbc-x.xx.x.x86_64.deb
    
    
  3. After downloading and installing the ODBC drivers, you need to restart Campaign Classic. To do so, run the following command:
    systemctl stop nlserver.service
    systemctl start nlserver.service
    
    
  4. In Campaign Classic, configure your Snowflake external account in Campaign Classic. From the Explorer , unfold the Administration menu.
  5. Unfold the Platform menu and click External accounts .
  6. Select the out-of-the box Snowflake external account.
  7. To configure the Snowflake external account:
    • Server
      URL of the Snowflake server.
    • Account
      Name of the user.
    • Password
      User account password.
    • Database
      Name of your database
  8. Click the Parameters tab then the Deploy function button to create functions.
The connector supports the following options:
Option
Value
Description
workschema
 
Database schema to use for work tables
warehouse
 
Name of the default warehouse to use. It will override the user's default.
TimeZoneName
 
By default empty, which means that the system time zone of the Campaign Classic app server is used. The option can be used to force the TIMEZONE session parameter.
For more on this, refer to this page .
WeekStart
0, 1-7
By default set to 0. (WEEK_START session parameter)
For more on this, refer to this page .
UseCachedResult
TRUE/FALSE
By default set to TRUE. This option can be used to disable Snowflake cached results (USE_CACHED_RESULTS session parameter)
For more on this, refer to this page .

Snowflake on Windows

  1. Download the ODBC driver for Windows . Note that you need administrator-level privileges to install the driver. For more on this, refer to this page
  2. Configure the ODBC driver. For more on this, refer to this page
  3. Once the ODBC driver has been installed and configured, you need to configure your Snowflake external account in Campaign Classic. From the Explorer , unfold the Administration menu.
  4. Unfold the Platform menu and click External accounts .
  5. Select the out-of-the box Snowflake external account.
  6. To configure the Snowflake external account:
    • Server
      URL of the Snowflake server.
    • Account
      Name of the user.
    • Password
      User account password.
    • Database
      Name of your database
  7. Click the Parameters tab then the Deploy function button to create functions.
The connector supports the following options:
Option
Value
Description
workschema
 
Database schema to use for work tables
warehouse
 
Name of the default warehouse to use. It will override the user's default.
TimeZoneName
 
By default empty, which means that the system time zone of the Campaign Classic app server is used. The option can be used to force the TIMEZONE session parameter.
For more on this, refer to this page .
WeekStart
0, 1-7
By default set to 0. (WEEK_START session parameter)
For more on this, refer to this page .
UseCachedResult
TRUE/FALSE
By default set to TRUE. This option can be used to disable Snowflake cached results (USE_CACHED_RESULTS session parameter)
For more on this, refer to this page .

Configure access to Hadoop 3.0

Connecting to a Hadoop external database in FDA requires the following configurations on the Adobe Campaign server. Note that this configuration is available for both Windows and Linux.
  1. Download the ODBC drivers for Hadoop depending on your OS version. Drivers can be found on this page .
  2. You then need to install the ODBC drivers and create a DSN for your Hive connection. Instruction can be found here
  3. After downloading and installing the ODBC drivers, you need to restart Campaign Classic. To do so, run the following command:
    systemctl stop nlserver.service
    systemctl start nlserver.service
    
    
  4. In Campaign Classic, configure your Hadoop external account in Campaign Classic. From the Explorer , unfold the Administration menu.
  5. Unfold the Platform menu and click External accounts .
  6. Click Create and select External database as Account type.
  7. To configure the Hadoop external account:
    • Type
      ODBC (Sybase ASE, Sybase IQ)
    • Server
      Name of the DNS.
    • Account
      Name of the user.
    • Password
      User account password.
    • Database
      Name of your database if not specified in DSN. It can be left empty if specified in the DSN.
    • Time zone
      Server time zone
The connector supports the following ODBC options:
Name
Value
ODBCMgr
iODBC
warehouse
1/2/4
The connector also supports the following Hive options:
Name
Value
Description
bulkKey
Azure blob or DataLake access key
For wasb:// or wasbs:// bulk loaders (i.e. if the bulk load tool starts with wasb:// or wasbs://).
It is the access key for blob or DataLake bucket for bulk load.
hdfsPort
port number
set by default to 8020
For HDFS bulk load (i.e. if the bulk load tool starts with webhdfs:// or webhdfss://).
bucketsNumber
20
Number of buckets when creating a clustered table.
fileFormat
PARQUET
Default file format for work tables.

Configure access to Hadoop 2.1

For more information on how to configure your Hadoop external database in FDA, refer to this article .

For Windows

  1. Install ODBC and Azure HD Insight drivers for Windows.
  2. Create the DSN (Data Source Name) by running the ODBC DataSource Adminstrator tool. A System DSN sample for Hive is provided for you to modify.
    Description: vorac (or any name you like)
    Host: vorac.azurehdinsight.net
    Port: 443
    Database: sm_tst611 (or your database name)
    Mechanism: Azure HDInsight Service
    User/Password: admin/<your password here>
    
    
  3. Create the Hadoop external account, as detailed in the Creating a shared connection section.

For Linux

  1. Install unixodbc for Linux.
    apt-get install unixodbc
    
    
  2. Download and install ODBC drivers for Apache Hive from HortonWorks: https://www.hortonworks.com/downloads/ .
    dpkg -i hive-odbc-native_2.1.10.1014-2_amd64.deb
    
    
  3. Check ODBC files location.
    root@campadpac71:/tmp# odbcinst -j
    unixODBC 2.3.1
    DRIVERS............: /etc/odbcinst.ini
    SYSTEM DATA SOURCES: /etc/odbc.ini
    FILE DATA SOURCES..: /etc/ODBCDataSources
    USER DATA SOURCES..: /root/.odbc.ini
    SQLULEN Size.......: 8
    SQLLEN Size........: 8
    SQLSETPOSIROW Size.: 8
    
    
  4. Create the DSN (Data Source Name) and edit the odbc.ini file. Then, create a DSN for your Hive connection.
    Here is an example for HDInsight to setup a connection called "viral":
    [ODBC Data Sources]
    vorac 
    
    [vorac]
    Driver=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so
    HOST=vorac.azurehdinsight.net
    PORT=443
    Schema=sm_tst611
    HiveServerType=2
    AuthMech=6
    UID=admin
    PWD=<your password here>
    HTTPPath=
    UseNativeQuery=1
    
    
    The UseNativeQuery parameter here is very important. Campaign is Hive-aware and will not work correctly unless UseNativeQuery is set. Typically, the driver or Hive SQL Connector will rewrite queries and tamper the column ordering.
    The authentication setup depends on the Hive/Hadoop configuration. For instance, for HD Insight, use AuthMech=6 for user/password authentication, as described here .
  5. Export the variables.
    export ODBCINI=/etc/myodbc.ini
    export ODBCSYSINI=/etc/myodbcinst.ini
    
    
  6. Setup Hortonworks drivers via /usr/lib/hive/lib/native/Linux-amd64-64/hortonworks.hiveodbc.ini.
    You have to use UTF-16 to be able to connect with Campaign and unix-odbc (libodbcinst).
    [Driver]
    
    DriverManagerEncoding=UTF-16
    ErrorMessagesPath=/usr/lib/hive/lib/native/hiveodbc/ErrorMessages/
    LogLevel=0
    LogPath=/tmp/hive
    SwapFilePath=/tmp
    
    ODBCInstLib=libodbcinst.so
    
    
  7. You can now test your connection using isql.
    isql vorac
    isql vorac -v
    
    
  8. Create the Hadoop external account, as detailed in the Creating a shared connection section.

Configure access to Netezza

Connecting to a Netezza external database in FDA requires additional configurations below on the Adobe Campaign server:
  1. Install the ODBC drivers for Netezza, according to the operating system that you use:
    • nz-linuxclient-v7.2.0.0.tar.gz for Linux. Select the folder that corresponds to your operating system (linux or linux64) and start the unpack command. You can leave the installation to be carried out in the repository that is suggested by default: "/usr/local/nz".
    • nz-winclient-v7.2.0.0.zip for Windows. Unzip the file and start the executable script that corresponds to your operating system: nzodbcsetup.exe or nzodbcsetup64.exe. Follow the wizard instructions to finish installing the drivers.
  2. Configure the ODBC driver. The configuration can be carried out in the standard files: /etc/odbc.ini for general parameters and /etc/odbcinst.ini for declaring drivers.
    • /etc/odbc.ini
      [ODBC]
      InstallDir=/etc/
      
      
      "InstallDir" corresponds to the location of the odbcinst.ini file.
    • /etc/odbcinst.ini
      [ODBC Drivers]
      NetezzaSQL = Installed
      
      [NetezzaSQL]
      Driver           = /usr/local/nz/lib/libnzsqlodbc3.so
      Setup            = /usr/local/nz/lib/libnzsqlodbc3.so
      APILevel         = 1
      ConnectFunctions = YYN
      Description      = Netezza ODBC driver
      DriverODBCVer    = 03.51
      DebugLogging     = false
      LogPath          = /tmp
      UnicodeTranslationOption = utf8
      CharacterTranslationOption = all
      PreFetch         = 256
      Socket           = 16384
      
      
  3. Specify the environment variables of the Adobe Campaign server:
    • LD_LIBRARY_PATH : /usr/local/nz/lib and /usr/local/nz/lib64. "/usr/local/nz" corresponds to the installation repository offered by default when installing the drivers. Here you need to specify the repository that you have selected for the installation.
    • ODBCINI : location of the odbc.ini file (for example /etc/odbc.ini).
    • NZ_ODBC_INI_PATH : location of the odbc.ini file. Netezza also requires this second variable for using the odbc.ini file.
  4. Create the Netezza external account, as detailed in the Creating a shared connection section.
Operations on schemas containing automatically generated primary keys are not taken into account.
The table will be using the Organize on clause on the first index defined in the schema. As this clause is limited to 1 to 4 columns with Netezza, this index cannot contain more than 4 columns.

Configure access to Oracle

Connecting to an Oracle external database in FDA requires additional configurations below on the Adobe Campaign server.

For Linux

  1. Install the Oracle full client corresponding to your version of Oracle.
  2. Add your TNS definitions to your installation. To do this, specify them in a tnsnames.ora file in the /etc/oracle repository. If this repository does not exist, create it.
    Then create a new TNS_ADMIN environment variable: export TNS_ADMIN=/etc/oracle and restart the machine.
  3. Integrate Oracle into your Adobe Campaign server (nlserver). To do this, check that the customer.sh file is present in the "nl6" folder of the Adobe Campaign server tree structure and that it includes the links to the Oracle libraries.
    For example, for a client in 11.2:
    export ORACLE_HOME=/usr/lib/oracle/11.2
    export TNS_ADMIN=/etc/oracle
    export LD_LIBRARY_PATH=$ORACLE_HOME/client64/lib:$LD_LIBRARY_PATH
    
    
    These values (particularly ORACLE_HOME), depends on your installation repositories. Make sure to check your tree structure before referencing these values.
  4. Install the libraries necessary for Oracle:
    • libclntsh.so
      cd /usr/lib/oracle/<version>/client<architecture>/lib
      ln -s libclntsh.so.<version> libclntsh.so
      
      
    • libaio1
      aptitude install libaio1
      or
      yum install libaio1
      
      

For Windows

  1. Install the Oracle client.
  2. In the C:Oracle folder, create a tnsnames.ora file containing your TNS definition.
    Add a TNS_ADMIN environment variable with C:Oracle as value and restart the machine.

Configure access to Sybase IQ

Connecting to a Sybase IQ external database in FDA requires additional configurations below on the Adobe Campaign server:
  1. Make sure the unixodbc package is on the server.
  2. Install iq_odbc . An error can occur at the end of the installation. This error can be ignored.
  3. Install iq_client_common . A Java error can occur at the end of the installation. This error can be ignored.
  4. Configure the ODBC driver. The configuration can be carried out in the standard files: /etc/odbc.ini for general parameters and /etc/odbcinst.ini for declaring drivers:
    • /etc/odbc.ini (replace values like <server_alias> characters by your own):
      [ODBC Data Sources]
      <server_alias>=libdbodbc.so
      
      [<server_alias>]
      Driver=/opt/sybase/IQ-16_0/lib64/libdbodbc16.so
      Description=<description>
      Username=<username>
      Password=<password>
      ServerName=<server_name>
      CommLinks=tcpip(host=<host>)
      
      
    • /etc/odbcinst.ini
      [ODBC DRIVERS]
      SAP SybaseIQ=Installed
      
      [SAP SybaseIQ]
      Driver=/opt/sybase/IQ-16_0/lib64/libdbodbc16.so
      
      
  5. Add the path for the new libodbc16.so library in the LD_LIBRARY_PATH variable. To do that:
    • If you are using a customer.sh file to declare your path: add the path /opt/sybase/IQ-16_0/lib64 for the LD_LIBRARY_PATH variable.
    • Otherwise, use a Unix command.
  6. Create a new FDA external account, as described in the Creating a shared connection section. For Sybase IQ, the server name corresponds to the ODBC connection ( <server_alias> ) defined in step 5. It is not necessarily the name of the server itself.
For Windows, you must install the Sybase IQ client on the Adobe Campaign server and create an ODBC connection. Make sure you create a system data source when the Adobe Campaign server (nlserver) is running as a service in Windows.

Configure access to Teradata

Connecting to a Teradata external database in FDA requires certain additional configurations on the Adobe Campaign server. For more information on how to configure your Teradata database, refer to this article .
  1. It is made up of three packages that can be installed on Red Hat (or CentOS)/Suse in the following order:
    • TeraGSS
    • tdicu1510 (install it using setup_wrapper.sh)
    • tdodbc1510 (install it using setup_wrapper.sh)
  2. Configure the ODBC driver. The configuration can be carried out in the standard files: /etc/odbc.ini for general parameters and /etc/odbcinst.ini for declaring drivers:
    • /etc/odbc.ini
      [ODBC]
      InstallDir=/etc/
      
      
      "InstallDir" corresponds to the location of the odbcinst.ini file.
    • /etc/odbcinst.ini
      [ODBC DRIVERS]
      teradata=Installed
      
      [teradata]
      Driver=/opt/teradata/client/15.10/lib64/tdata.so
      APILevel=CORE
      ConnectFunctions=YYY
      DriverODBCVer=3.51
      SQLLevel=1
      
      
  3. Specify the environment variables of the Adobe Campaign server:
    • LD_LIBRARY_PATH : /opt/teradata/client/15.10/lib64 and /opt/teradata/client/15.10/odbc_64/lib.
    • ODBCINI : location of the odbc.ini file (for example /etc/odbc.ini).
    • NLSPATH : location of the opermsgs.cat file (/opt/teradata/client/15.10/msg/opermsgs.cat)

Configure access to SAP HANA

Connecting to an SAP HANA external database in FDA requires certain additional configurations on the Adobe Campaign server:
  1. Install the ODBC drivers for SAP HANA, according to the operating system that you use:
    • hdb_client_linux.tgz for Linux. Once unzipped, launch the hdbinst command and follow the instructions to finish installing the drivers.
    • hdb_client_windows.zip for Windows. Unzip the file and start the executable: hdbinst.exe . Follow the wizard instructions to finish installing the drivers.
  2. Configure the ODBC driver. The configuration can be carried out in the standard files: /etc/odbc.ini for general parameters and /etc/odbcinst.ini for declaring drivers.
    • /etc/odbc.ini
      [ODBC]
      InstallDir=/etc/
      
      [HDB]
      Driver=HDBODBC
      servernode=localhost:39013 (this value depend of your server)
      User:SYSTEM
      
      
      "InstallDir" corresponds to the location of the odbcinst.ini file.
    • /etc/odbcinst.ini
      [HDBODBC]
      Description = "SmartCloudPT HANA"
      Driver = /usr/sap/hdbclient/libodbcHDB.so
      
      
  3. Specify the environment variables of the Adobe Campaign server:
    • LD_LIBRARY_PATH : It should include the link to your SAP Hana client (/usr/sap/hdbclient/libodbcHDB.so) by default).
    • ODBCINI : location of the odbc.ini file (for example /etc/odbc.ini).
  4. Create the SAP Hana external account, as detailed in the Creating a shared connection section.