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:

  • Performed setup using the SAP Analytics Cloud Agent Simple Deployment Kit. For more information, see SAP Analytics Cloud Agent Simple Deployment Kit.
  • 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. JDBC drivers must be installed.
    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 must restart the SAP Analytics Cloud 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.
    7. 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=
        #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.

Procedure

  1. In the Modeler, while creating a new model or importing data into an existing SQL Database model, choose Get data from a datasource.
  2. Under Acquire data, select SQL Databases.

    The Create Model from SQL Databases dialog will appear.

  3. Select the Location of your cloud connector from the list.
  4. Select the Connection Type.
  5. Under Connection, select Create New Connection.

    The new connection dialog will appear.

  6. A name and a description for the connection.
  7. In the Datasource Configuation section, select the Location of your cloud connector from the list.
  8. Add your Server information.
    The server name must be used. If you use a named installation of MS SQL Server, the instance name is also required. If the SQL Browser service is running, the port number is not required.
  9. Enter your User Name and Password.
    You must use an SQL Server authenticated user.
  10. Choose Create.
    The new connection is added to the list of connections on the Connections screen.
  11. If you want to create a model based on this connection, continue to Importing SQL Data to a New or Existing Model.