Connect to Database Data Sources

You can create, edit, and delete database connections and use the connections to create data sets from databases.

Create Database Connections

You can create connections to databases and use the connections to access data.

  1. On the Home page, click Create, then click Connection.
  2. In the Create Connection dialog, click the icon for the connection type that you want to create a connection for (for example Oracle Database).
  3. Enter a unique name for the new connection, and specify the required connection information for the database, such as the hostname, port, credentials, service name and so on.
     

    If you’re creating an SSL connection to an Oracle Database, in the Client Wallet field, click Select to browse for the cwallet.sso file. Ask your administrator for the location of the cwallet.sso file.

  4. (Optional) When you connect to some database types (for example, Oracle Talent Acquisition Cloud), you might have to specify the following authentication options on the Create Connection and Inspect dialogs:
    • Select Always use these credentials, so that the login name and password you provide for the connection are always used and users aren’t prompted to log in.
    • Select Require users to enter their own credentials when you want to prompt users to enter their own user name and password for the data source. Users are required to log in see only the data that they have the permissions, privileges, and role assignments to see.
  5. If you're connecting to an on-premises database, click Use Remote Data Connectivity.
    Check with your administrator that you can access the on-premises database.
  6. Click Save.
    You can now begin creating data sets from the connection.

    You can't use remote connections to save a data set from a Data Flow.

Create the ZIP File Needed for Database Connections with Kerberos Authentication

You need a ZIP file that contains specific configuration files to create an SSL connection that uses Kerberos authentication.

The zip file must contain the following files:
  • krb5.conf
  • oac.keytab
  • service_details.json
  1. Get the Kerberos configuration files from your database administrator (for example, to connect to Apache Hive).

    You may need to create or modify the files.

  2. Create a folder to contain the Kerberos configuration files.
  3. Copy the krb5.conf file into the folder that you created.
  4. Ensure the .keytab file is named oac.keytab (rename it if required), and copy the file into the folder you created.
  5. Get or create the service_details.json file and save it in the folder you created.
    The service_details.json file must contain values for Host, Port, and ServicePrincipalName, for example:
    {
     "Host" : "myHost.com",
     "Port" : "10000",
     "ServicePrincipalName" : "hive/myHostDB.com@BDA.COM",
    }

    You must enclose all parameter values in quotation marks ("value").

  6. Create a ZIP file containing the three files that you added to your folder, and provide an appropriate name (for example SSLKerberos.ZIP).

    You can now create a database connection with Kerberos authentication.

Create Database Connections with Kerberos Authentication

You can configure selected database connections to use Kerberos network authentication protocol.

These database connection types support Kerberos authentication:

  • Apache Hive
  • Hortonworks Hive
  • IBM BigInsights Hive
  • MapR Hive
  • Pivotal HD Hive

If this is a remote connection, you can't use it to save a data set from a Data Flow.

  1. On the Home page, click Create, and then click Connection.
  2. In the Create Connection dialog, click the icon for the connection type such as Apache Hive.
  3. Click Authentication Type and select Use Kerberos.
  4. In the Client Credentials field, either drag and drop or click Select to browse for a prepared ZIP or CONF file.
    Do one of the following to get the appropriate configuration files for a SSL or a Non-SSL connection:
  5. If you added a ZIP file, enter the ZIP password in the ZIP Password field.
  6. If you added a krb5.conf file, either drag and drop or click Select to browse for the oac.keytab file in the Keytab field.
    The Host, Port, and Service Principal fields automatically display values taken from the service_details.json file.
  7. If you're connecting to an on-premises database, click Use Remote Data Connectivity.

    Your administrator can enable this checkbox in the Console.

    Check with your administrator that you can access the on-premises database. See Connect to an On-premises Database from Oracle Analytics Cloud.

  8. If you're creating a SSL connection, click Enable SSL to enable the connection to use SSL.
  9. Click Save.

Create Data Sets from Databases

After you create database connections, you can use those connections to create data sets.

You must create the database connection before you can create a data set for it.
  1. On the Home page click Create and click Data Set to open the Create Data Set dialog. In the Create Data Set dialog, select Create Connection and use the Create Connection dialog to create the connection for your data set.
  2. In the Data Set editor, first browse or search for and double-click a schema, and then choose the table that you want to use in the data set. When you double-click to select a table, a list of its columns is displayed.
    You can use breadcrumbs to quickly move back to the table or schema list.
  3. In the column list, browse or search for the columns you want to include in the data set. You can use Shift-click or Ctrl-click to select multiple columns. Click Add Selected to add the columns you selected, or click Add All to include all of the table's columns in the data source.
    Alternatively, you can select the Enter SQL option to view or modify the data source’s SQL statement or to write a SQL statement.
  4. You can also optionally perform the following steps:
    • After you’ve selected columns, you can go to the Step editor at the top of the Data Set editor and click the Filter step to add filters to limit the data in the data set. After you’ve added filters, click Get Preview Data to see how the filters limit the data.

    • Go to the Step editor at the top of the Data Set editor and click the last step in the Step editor to specify a description for the data source.

    • Go to the Step editor at the top of the Data Set editor and click the last step in the Step editor and go to the Refresh field to specify how you want to refresh the data in the data source. Note the following information:

      • Select Live if you want the data source to use data from the database directly rather than copying the data into the cache. Typically database tables are large and shouldn’t be copied to cache.

      • If your table is small, then select Auto and the data is copied into cache if possible. If you select Auto, you must refresh the data when it’s stale.

  5. Click Add. The View Data Source page is displayed.
  6. In the View Data Source page you can optionally view the column properties and specify their formatting. The column type determines the available formatting options.

Edit Database Connections

You can edit the database connection details.

  1. In the Data page, click Connections.
  2. Select the connection you want to edit and click Action menu or right-click, then select Inspect.
  3. In the Inspect dialog, edit the connection details.
  4. Click Save.

If you’re editing an SSL connection to an Oracle Database and you need to use a new cwallet.sso file, in the Client Wallet field, click Select to browse for the cwallet.sso file. Ask your administrator for the location of the cwallet.sso file.

You must provide a unique Connection Name. If a connection with the same name already exists in your system, an error message is displayed. You can’t see or edit the current password for your connection. If you need to change it, you must create a connection that uses the same password.

Delete Database Connections

You can delete a database connection. For example, you must delete a database connection and create a new connection when the database's password has changed.

If the connection contains any data sets, then you must delete the data sets before you can delete the connection.

  1. Go to the Data page and select Connections.
  2. Select the connection that you want to delete and click Actions menu or right-click, then click Delete.
  3. Click Yes.

About Specifying Connections to Databases

Some database types (for example, Oracle Talent Management Cloud) require you to specify additional configuration options.

When you connect to some database types, you might have to specify the following authentication options on the Create Connection and Edit Connection dialogs:

  • Enable Bulk Replication - If you’re loading a data set for a project, then this option should be turned off and you can ignore it. This option is reserved for data analysts and advanced users for replicating data from one database to another database.

  • Authentication

    • Select Always use these credentials, so that the login name and password you provide for the connection are always used and users aren’t prompted to log in.

    • Select Require users to enter their own credentials when you want to prompt users to enter their own user name and password for the data source. Users required to log in see only the data that they have the permissions, privileges, and role assignments to see.