Query Data Models Remotely Using JDBC

You can query Oracle Analytics Cloud data models from an external client tool using a JDBC connection.

Overview to Querying Oracle Analytics Cloud Data Models Remotely

Java Data Base Connectivity (JDBC) is an industry standard API for accessing data sources. Use JDBC-compliant client tools to access data models defined in Oracle Analytics Cloud so that you can take advantage of its analytics engine and data abstraction features.

JDBC uses OAuth to secure access to Oracle Analytics Cloud. OAuth 2.0 is an authorization framework that enables an application to obtain limited access to a protected HTTP resource. Using OAuth, the applications are called clients; they access protected resources by presenting an access token to the HTTP resource.

To learn more about how Oracle Cloud services use OAuth, see Introduction to OAuth in Oracle Cloud.

Typical Workflow to Query Oracle Analytics Cloud Data Models Remotely

If you’re querying Oracle Analytics Cloud data models remotely for the first time, follow these tasks as a guide.

Task Description More Information
Decide how you want to secure your JDBC connection Depending on your security requirements, choose either Resource Owner (recommended) or JSON Web Tokens (JWT) as the assertion type. Choosing an Assertion Type for Your JDBC Connection

Register the BIJDBC application

Register the BIJDBC application in Oracle Identity Cloud Service to authenticate your JDBC connection.

(Recommended) Use Resource Owner assertion, see Register the BIJDBC Application Using Resource Owner Assertion.

Alternatively, use JWT assertion:

Enable refresh security token generation

Set up refresh security token generation in Oracle Identity Cloud Service.

Set Up Refresh Security Token Generation

Download the JDBC driver

Download the JDBC driver for Oracle Analytics Cloud.

Download the JDBC Driver

Connect to Oracle Analytics Cloud

Connect to Oracle Analytics Cloud remotely using JDBC.

Refer to the example as a guide, see Example: Connect to a Data Model Remotely Using SQuirrel.

Connect to Oracle Analytics Cloud Using a JDBC URL

Choosing an Assertion Type for Your JDBC Connection

When you register a BIJDBC application in Oracle Identity Cloud Service , you specify an assertion type that best secures your connection.

Here's some guidance on choosing an assertion type using the Allowed Grant Types option on the Add Confidential Application dialog.

Assertion Type Use this option when: Considerations

(Recommended) Resource Owner

Your application has access to the user name and password of the end user connecting to Oracle Analytics Cloud.

You want to create a 'gateway' connection to query data from the BI Server. Gateway accounts always use one single user name and password.

We recommend using this assertion type. It's simpler to configure.

JWT

You don't have the password and you need to connect to Oracle Analytics Cloud as different users.

This assertion type is more complex to configure. It also allows you to impersonate any BI user in the system, therefore you must ensure that the keys you generate are secured appropriately.

Register the BIJDBC Application Using Resource Owner Assertion

You register the BIJDBC application in Oracle Identity Cloud Service using Resource Owner assertion to authenticate your public JDBC connections.

  1. Sign-in to Oracle Identity Cloud Service as an administrator.
  2. Navigate to the Applications tab and click Add.
  3. In the Add Application dialog, click Confidential Application.
  4. Specify a Name (for example, bi-jdbc-connection) , a Description, and then click Next.
  5. Select Configure this application as a client now.
  6. In Allowed Grant Types, click Resource Owner.

    Figure 3-1 Register BIJDBC application in IDCS

    Register BIJDBC application in IDCS
  7. In the Token Issuance Policy section:
    1. Under Authorized Resources, select Specific.
    2. Click Add Scope.
    3. Select the Oracle Analytics Cloud instance you want to connect to (for example, select AUTOANALYTICSINST_<my_instance_ID>).
    4. Click Add.
  8. Click Next and then Finish to display an Application Added window.
  9. Copy the Client ID and Client Secret to use later.
  10. Close the Application Added page.
  11. Click Activate, then click Activate Application.
  12. Click Save to display a confirmation message.

Generate the Client Private Key and Client Certificate File

If you decide to secure your JDBC connection using the JWT assertion type, you generate a private key and certificate to authenticate the connection.

Note: You don't need a private key and certificate file if you're securing your JDBC connection using the Resource Owner assertion type.
  1. Generate a key pair and key store.
    From a command prompt, issue a keytool command, using the command format:
    keytool -genkeypair -v -keystore <keystore name> -storetype <store type i.e PKCS12> -storepass <store pass> -keyalg <key algorithm> -keysize <key size> -sigalg <sig algorithm> -validity <validity days> -alias <alias name> -keypass <key pass>
    For example:
    keytool -genkeypair -v -keystore bijdbckeystore.jks -storetype PKCS12 -storepass password -keyalg RSA -keysize 2048 -sigalg SHA256withRSA -validity 3600 -alias bijdbcclientalias -keypass password
  2. Generate a public certificate.
    From a command prompt, issue a keytool command, using the command format:
    keytool -exportcert -v -alias <alias name> -keystore <keystore name> -storetype <store type, such as PKCS12> -storepass <store pass> -file <certificate file> -rfc
    For example:
    keytool -exportcert -v -alias bijdbcclientalias -keystore bijdbckeystore.jks -storetype PKCS12 -storepass password -file bijdbcclient.cert -rfc
  3. Use OpenSS to extract the private key, in PKCS8 format, from the keystore file.
    Use the command format:
    openssl pkcs12 -in <keystore file name> -passin pass:<keystore password> -nodes -nocerts -nomacver > <PKCS8 key file path>
    For example:
    openssl pkcs12 -in bijdbckeystore.jks -passin pass:password -nodes -nocerts -nomacver |sed -n '/BEGIN PRIVATE KEY/,$p' > bijdbcclient.pem
  4. Save the generated key and certificates in a location accessible to your client machine.

Register the BIJDBC Application using JWT Assertion

You register the BIJDBC application in Oracle Identity Cloud Service using JWT assertion to authenticate your public JDBC connections.

Before you start, generate a Client Private Key and Client Certificate File as specified in the previous step.
  1. Sign-in to Oracle Identity Cloud Service as an administrator.
  2. Navigate to the Applications tab and click Add.
  3. In the Add Application dialog, click Confidential Application.
  4. Specify a Name (for example, bi-jdbc-connection) , a Description, and then click Next.
  5. Select Configure this application as a client now.
  6. In Allowed Grant Types, click JWT Assertion.
  7. For Security:
    1. Select Trusted Client.
    2. Click Import , enter a Certificate Alias, and then upload your client certificate file.
    Add Confidential Application
  8. In the Token Issuance Policy section:
    1. Under Authorized Resources, select Specific.
    2. Click Add Scope.
    3. Select the Oracle Analytics Cloud instance you want to connect to (for example, select AUTOANALYTICSINST_<my_instance_ID>).
    4. Click Add.
  9. Click Next and then Finish to display an Application Added window.
  10. Copy the Client ID and Client Secret to use later.
  11. Close the Application Added page.
  12. Click Activate, then click Activate Application.
  13. Click Save to display a confirmation message.

Set Up Refresh Security Token Generation

Oracle Analytics Cloud instances that you created before Oracle Analytics Cloud 5.6.x (before 12th May 2020) don't automatically support refresh token generation. Oracle recommends that you enable this feature to improve the performance of queries. If you don't enable the refresh token feature, your queries might run slowly.

  1. Verify that the Oracle Analytics Cloud instance you want to connect to is version 5.6 or later. If you're not sure, contact your Oracle representative.
  2. In Oracle Identity Cloud Service, enable the Refresh Token option for the BIJDBC application that you created earlier.
    1. In Oracle Identity Cloud Service Console, navigate to Applications, and click the name of the BIJDBC application that you created earlier.
    2. Click Configuration, then Client Configuration.
    3. Select Refresh Token, and click Save.
      Refresh Token option highlighted
  3. In Oracle Identity Cloud Service Console, navigate to the application associated with Oracle Analytics Cloud you want to connect to, and make a note of the following information:
    • Oracle Identity Cloud Service HostnameIDCS Host name

    • Application ID (IDCS application associated with Oracle Analytics Cloud)IDCS Application ID

    • Client ID and Client Secret (IDCS application associated with Oracle Analytics Cloud)Client ID and Client Secret

  4. Use the Oracle Identity Cloud Service REST API to generate the access token using client credentials.
    Use this REST API command format:
    curl --insecure -i -u '<Client-ID>:<Client-Secret>' -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" --request POST https://<IDCS-Host>/oauth2/v1/token -d "grant_type=client_credentials&scope=urn:opc:idm:__myscopes__"
    Parameters:
    • Client-ID: Client ID of the Oracle Identity Cloud Service application associated with your Oracle Analytics Cloud instance.

    • Client-Secret: Client secret for the Oracle Identity Cloud Service application associated with your Oracle Analytics Cloud instance.

    • IDCS-Host: Host name of your Oracle Identity Cloud Service instance.

  5. Use the Oracle Identity Cloud Service REST API to update the flag.
    Use this REST API command format:
    curl --location --request PATCH 'https://<IDCS-Host>/admin/v1/Apps/<Application-Id>' \ --header 'Authorization: Bearer <Access-token>' \ --header 'Content-Type: application/json' \ --data-raw '{ "schemas": [ "urn:ietf:params:scim:api:messages:2.0:PatchOp" ], "Operations": [{ "op": "replace", "path": "allowOffline", "value": true }] }'
    Parameters:
    • IDCS-Host: Host name of your Oracle Identity Cloud Service instance.

    • Application-Id: Application ID of the Oracle Identity Cloud Service application associated with your Oracle Analytics Cloud instance.

    • Access-token: Access token value you generated earlier.

Download the JDBC Driver

Obtain the JDBC driver JAR file (bijdbc-all.jar) from an Oracle Analytics Cloud Client Tools installation on a Windows machine. If you don't already have Oracle Analytics Cloud Client Tools installed, then download and install it first.

  1. On a Windows machine, navigate to the Oracle Technology Network download page for Oracle Analytics Cloud.
  2. Click Oracle Analytics Client Tools <latest version>.
  3. Accept the Oracle license agreement, and click the download link to save the ZIP file to your local machine.
  4. From the downloaded ZIP file, extract the installer EXE file.
  5. Use the installer EXE file to install Oracle Analytics Cloud Client Tools on your Windows machine.
  6. From the installation folder, copy the JDBC driver file <OH>/bi/bifoundation/jdbc/bijdbc-all.jar.

Connect to Oracle Analytics Cloud Using a JDBC URL

Determine the JDBC URL required to connect to your Oracle Analytics Cloud instance and test the connection.

  1. In Oracle Identity Cloud Service Console, make a note of the following information:
    • Oracle Identity Cloud Service host name

      IDCS Host name
    • Client ID (BIJDBC application)

      Client ID
    • Client Scope (BIJDBC application)

      Client Scope
  2. Create a bijdbc.properties file for OAuth authentication and authorization and add credentials for your Oracle Analytics Cloud instance.
    Use the following format for your bijdbc.properties file:
    idcsEndpointUrl=https://<IDCS_hostname> 
    idcsClientId=<ID string>
    idcsClientScope=<ID string>
    certificateFile=<name and location of cert file .CERT>
    privateKeyFile=<name and location of key file .PEM>
    user=<firstname.lastname@example.com>

    For example:

    idcsEndpointUrl=https://idcs-1a2bc345678901d2e34fgh56789j0ke.identity.c9abc1.oc9def.com
    idcsClientId=12a000dc9ef345678000ghij2kl8a34   
    idcsClientScope=https://<host>.com:443urn:opc:resource:consumer::all
    certificateFile=D:\\jdbc\\bijdbcclient.cert
    privateKeyFile=D:\\jdbc\\bijdbcclient.pem
    user=myuser@office.com
  3. Determine the URL required to connect to your Oracle Analytics Cloud instance. The format you use depends on when and how the instance was deployed.
    To connect to an instance deployed on Create date

    Oracle Cloud Infrastructure (Gen 2)

    Any

    Oracle Cloud Infrastructure

    12th May 2020 or later

    Use this URL format with OAuth:

    jdbc:oraclebi:https://<host>:<port>/api/jdbc?BIJDBC_PROPERTIES_FILE=<fully qualified location and name of properties file>

    For example:

    jdbc:oraclebi:https://<host>:443/api/jdbc?BIJDBC_PROPERTIES_FILE=D:\\Workspace\\bijdbc\\bijdbc.properties
    To connect to an instance deployed on Create date

    Oracle Cloud Infrastructure

    Before 12th May 2020

    Use this URL format with OAuth:

    jdbc:oraclebi:https://<host>:<port>/bimodeler/api/jdbc?BIJDBC_PROPERTIES_FILE=<fully qualified location and name of properties file>

    For example:

    jdbc:oraclebi:https://abcdefghi123-jklmnopqrs4t-je.analytics.ocp.oraclecloud.com:443/bimodeler/api/jdbc?BIJDBC_PROPERTIES_FILE=D:\\Workspace\\bijdbc\\bijdbc.properties
  4. Test the connection to the target Oracle Analytics Cloud instance.
    Use your favorite SQL command tool to connect to Oracle Analytics Cloud with the appropriate JBDC URL. For example:
    jdbc:oraclebi:https://https://abcdefghi123-jklmnopqrs4t-je.analytics.ocp.oraclecloud.com:443/api/jdbc?BIJDBC_PROPERTIES_FILE=D:\\Workspace\\bijdbc\\bijdbc.properties

Example: Connect to a Data Model Remotely Using SQuirrel

This example shows how to connect to a Oracle Analytics Cloud data model using JDBC with SQuirrel SQL Client tool.

  1. Register the JDBC driver.
    1. In SQuirrel SQL Client, under Drivers, click Create a New Driver.
    2. In the Example URL field, specify the BIJDBC application URL with a fully qualified properties file.
      For example: jdbc:oraclebi:https://abcdefghi123-jklmnopqrs4t-je.analytics.ocp.oraclecloud.com:443/bimodeler/api/jdbc?BIJDBC_PROPERTIES_FILE=D:\\Workspace\\bijdbc\\bijdbc.properties
    3. In the Extra Class Path tab, select the BIJDBC driver (JAR file) you downloaded from Client Installer.
    4. Click List Drivers, and under Class Name select oracle.bi.jdbc.AnaJdbcDriver, then save the details.
    Add driver information
  2. Create a connection or (alias).
    1. Under Aliases, click Create a New Alias.
    2. In the Driver option, select bijdbc.
    3. Edit the URL, specify credentials (if required), then click Test.

      If credentials are provided in the property file, you don't need to specify User Name or Password.

    4. Validate the connection by connecting to the Alias and exploring the metadata in the Objects section.
    Add connection details
  3. In the SQL tab, enter a sample logical SQL query and click the Run button.

    For more information, see Logical SQL Reference Guide.

    If the connection is working, the Results tab shows the results of your query.

    Test connection
  4. Check the Results tab to verify the rows returned by the query.