Import Data Connection to an SQL Database

You can create a connection that allows you to import data from an SQL database.

Prerequisites

You have completed one of the following setup options:
  1. Performed setup using the SAP Analytics Cloud Agent Simple Deployment Kit. For more information, see SAP Analytics Cloud Agent Simple Deployment Kit.
    Note
    The SAP Java Connector (JCo) and JDBC drivers must be installed using the instructions in the Post-Setup Guide included in the kit.
  2. Performed a manual setup of the following required components:
    1. The cloud connector is installed. For more information, see Installing the SAPCP Cloud Connector.
    2. The SAP Analytics Cloud agent is installed. For more information, see Installing SAP Analytics Cloud Agent.
    3. The SAP JCo must be installed. For more information, see Installing the SAP Java Connector (JCo).
    4. The cloud connector is configured. For more information, see Configuring the SAPCP Cloud Connector.
    5. The SAP Analytics Cloud agent is configured in SAP Analytics Cloud. For more information, see Configuring SAP Analytics Cloud Agent.
    6. You have installed a JDBC driver. If you haven't already installed a JDBC driver, follow these steps:
      1. Download JDBC drivers for the databases that you want to connect to, and then place the downloaded .jar file(s) in an appropriate folder. The .jar files can be in any location on your machine as long as the .properties file includes the correct file locations (see the next step). For information about supported databases, see System Requirements and Technical Prerequisites.
      2. Create a .properties file that specifies the paths to JDBC drivers you want to use, and place it in the same file system where the SAP Analytics Cloud agent is installed. The properties file can have any name.

        Example .properties file:
        #Amazon EMR 5.6 (Hive 2.1)=
        #Amazon EMR Hive 0.11=
        #Amazon EMR Hive 0.13=
        #Amazon Redshift=
        #Apache Hadoop HIVE=
        #Apache Hadoop Hive 0.10=
        #Apache Hadoop Hive 0.12=
        #Apache Hadoop Hive 0.13 HiveServer2=
        #Apache Hadoop Hive 0.14 HiveServer2=
        #Apache Hadoop Hive 0.7=
        #Apache Hadoop Hive 0.8=
        #Apache Hadoop Hive 0.9=
        #Apache Hadoop Hive 0.x HiveServer1=
        #Apache Hadoop Hive 0.x HiveServer2=
        #Apache Hadoop Hive 1.0 HiveServer2=
        #Apache Hadoop Hive 1.x HiveServer2=
        #Apache Hadoop Hive 2.x HiveServer2=
        #Apache Spark 1.0=
        #Apache Spark 2.0=
        #BusinessObjects Data Federator Server XI R3=
        #BusinessObjects Data Federator Server XI R4=
        #Cloudera Impala 1.0=
        #Cloudera Impala 2.0=
        #DB2 10 for LUW=C:\path_to_DB2_v10_driver\db2jcc4.jar
        #DB2 10 for z/OS=
        #DB2 10.5 for LUW=C:\path_to_DB2_v10.5_driver\db2jcc4.jar
        #DB2 11 for LUW=
        #DB2 UDB v5=
        #DB2 UDB v6=
        #DB2 UDB v7=
        #DB2 UDB v8=
        #DB2 for z/OS v11=
        #DB2 for z/OS v12=
        #DB2 v9=
        #Data Federator Server=
        #Data Federator Server XI R3=
        #Data Federator Server XI R4=
        #Generic JDBC datasource=
        #GreenPlum 3=
        #GreenPlum 4=
        #HP Vertica 6.1=
        #HP Vertica 7.1=
        #HP Vertica 8=
        #Hortonworks Data Platform 2.3=
        #Hortonworks Data Platform 2.5=
        #IBM Puredata (Netezza)=
        #IBM Puredata (Netezza) Server 7=
        #Informix Dynamic Server 10=
        #Informix Dynamic Server 11=
        #Informix Dynamic Server 12=
        #Ingres Database 10=
        #Ingres Database 9=
        #MS Parallel Data Warehouse=
        #MS SQL Server=
        #MS SQL Server 2000=
        #MS SQL Server 2005=
        MS SQL Server 2008=C:\jdbcDrivers\sqljdbc.jar
        #MS SQL Server 2012=
        #MS SQL Server 2014=
        #MS SQL Server 2016=
        #MS SQL Server 6.5=
        #MS SQL Server 7.x=
        #MaxDB 7.7=
        #MaxDB 7.9=
        #MySQL=
        #MySQL 5=
        #Netezza Server=
        #Netezza Server 4=
        #Netezza Server 5=
        #Netezza Server 6=
        #Netezza Server 7=
        #Oracle 10=
        #Oracle 11=
        #Oracle 12=
        #Oracle 12c Release 2=
        #Oracle 7.3=
        #Oracle 8=
        #Oracle 8.0=
        #Oracle 8.1=
        #Oracle 9=
        #Oracle Exadata=
        #Oracle Exadata 11=
        #Oracle Exadata 12=
        #PostgreSQL 8=
        #PostgreSQL 9=
        #Progress OpenEdge 10=
        #Progress OpenEdge 11=C:\path_to_driver\openedgewp.jar
        #Sybase ASIQ 12=
        #Sybase Adaptive Server 11=
        #Sybase Adaptive Server 12=
        #Sybase Adaptive Server 15=
        #Sybase Adaptive Server Enterprise 15=
        #Sybase Adaptive Server Enterprise 15.5=
        #Sybase Adaptive Server Enterprise 15.7=
        #Sybase Adaptive Server Enterprise 16.0=
        #Sybase IQ 15=
        #Sybase IQ 16=
        #Sybase SQL Anywhere 10=
        #Sybase SQL Anywhere 11=
        #Sybase SQL Anywhere 12=
        #Sybase SQL Anywhere 16=
        #Sybase SQL Anywhere 17=
        #Sybase SQLServer 11=
        #Teradata 12=
        #Teradata 13=
        #Teradata 14=
        #Teradata 15=
        #Teradata 16=
        #Teradata V2 R=
        #Teradata V2 R6=
        
      3. Modify your .properties file by un-commenting the lines of the databases you want to connect to, and enter the path to the driver as the property value. If the driver requires more than one .jar file, the paths can be separated by a semicolon.
        Caution
        The names of databases in your properties file must EXACTLY match the names shown in this example. If you change the names, the SQL connections will fail.
      4. Restart the agent, using either the Java option -DSAP_CLOUD_AGENT_PROPERTIES_PATH or an environment variable SAP_CLOUD_AGENT_PROPERTIES_PATH to specify the complete path up to and including the properties file. Example path: C:\<full path to file>\DriverConfig.properties.
        • If you choose to use a Java option, restart the agent via the command line by navigating to the tomcat/bin directory and doing the following:
          1. Run the shutdown.bat or shutdown.sh script.
          2. Open the catalina.bat folder in the tomcat/bin directory and find the line where Java options are set. It should look similar to this: set "JAVA_OPTS=%JAVA_OPTS% %LOGGING_CONFIG% -Xms1024m -Xmx10246m -XX:NewSize=256m -XX:MaxNewSize=356m -XX:PermSize=256m -XX:MaxPermSize=4096m"
          3. Modify this line so that the -DSAP_CLOUD_AGENT_PROPERTIES_PATH option is included and points to your .properties file.

            Example: set "JAVA_OPTS=%JAVA_OPTS% %LOGGING_CONFIG% -Xms1024m -Xmx10246m -XX:NewSize=256m -XX:MaxNewSize=356m -XX:PermSize=256m -XX:MaxPermSize=4096m -DSAP_CLOUD_AGENT_PROPERTIES_PATH=C:\<path to driver config file> \DriverConfig.properties"

          4. Run the startup.bat or startup.sh script.
        • If you choose to use an environment variable, restart the agent as follows:
          • If you're running Tomcat via command line: Shut down the existing Tomcat process, then add the environment variable, and then start Tomcat from a new command line window.
          • If you're running Tomcat as a Windows service: Restart the Tomcat service by using the Tomcat configuration manager.

Procedure

  1. In Modeler, while creating a new SQL Database model or importing data into an existing SQL Database model, choose Create New Connection.
  2. Enter the connection details and your login credentials.
  3. Choose Create.
    The new connection is added to the list of connections on the Connections screen.
  4. If you want to create a model based on this connection, see Importing SQL Data to a New or Existing Model.