Chapter 5. Data source configuration

You can configure external services as data sources for a virtual database. To describe a data source, you add properties to a custom resource (CR) file. Some properties are common to multiple data sources. Other properties are specific to a particular data source. Property names are case-sensitive.

For every data source, you must provide the name of a translator that can interpret the commands and data that pass between the virtual database and the data source. For example, the web service translator converts SQL procedures executed to a virtual database to an HTTP call to send to a web service. Similarly, the translator can convert a JSON response to tabular results.

Translators can also include optional configurable properties that you can use to manage the behavior of the translator. Execution properties control how data is retrieved. Import settings determine the metadata that is the virtual database reads and imports.

Additional resources

The sections that follow describe the specific properties to set in the custom resource files to add data sources to virtual databases. Details about how to create the DDL to define the database structure are not covered.

5.1. Configuration properties for S3 and Ceph as data sources

You can configure an Amazon Simple Storage Service (S3) as a data source for a virtual database. Using a similar configuration, you can also use the Ceph storage platform as a data source.

Translator setting

The DDL for the virtual database defines a translator, or FOREIGN DATA WRAPPER. For both S3 and Ceph, set the translator to amazon-s3. A corresponding SERVER definition in the DDL represents the external data source server, and associates the translator with the external server.

Data source information

The custom resource that you use to create a virtual database from S3 or Ceph requires information about the service, For example, you must provide the access key and secret key that you use to sign request to AWS.

Dependencies

S3 and Ceph do not require you to specify any build dependencies.

The following table shows the data source information that is required in the data source properties of the custom resource:

Table 5.1. Data source properties for S3/Ceph

Property NameDescriptionRequiredDefault value

region

S3 region. For example, us-east-2 [a]

Yes

n/a

bucket

The name of the S3 bucket.

Yes

n/a

accesskey

Access key ID for signing requests to AWS services.

Yes

n/a

secretkey

Secret access key for signing requests.

Yes

n/a

[a] The region property is not required for Ceph data sources.

Example: Excerpt from an S3 custom resource, showing the format for setting key properties

datasources:
  - name: sampledb
    type: amazon-s3
    properties:
      - name: region
        value: us-east-2
      - name: bucket
        value: mybucket
      - name: accesskey
        value: xxxxxxxx
      - name: secretkey
        value: xxxxxx

5.2. Settings to connect to Google Sheets as a data source

You can configure Google Sheets as a data source for a virtual database.

Translator setting

The DDL for the virtual database defines a translator, or FOREIGN DATA WRAPPER. For Google Sheets sources, set the translator to google-spreadsheet. A corresponding SERVER definition in the DDL represents the external data source server, and associates the translator with the external server. Each Sheet in a Google Sheets spreadsheet becomes available as a table in the virtual database.

For a virtual database to connect to Google Sheets, you must register your data virtualization service as a Google client application. During registration, you enable Google Sheets APIs and create credentials that the virtual database uses to access the APIs.

For information about how to register data virtualization as a Google client application, see the Google OAuth documentation.

The following table shows the data source information that is required in the data source properties of the custom resource:

Table 5.2. Data source properties for Google Sheets

Property NameDescriptionRequiredDefault value

spreadSheetName

Name of the Google Sheets spreadsheet

Yes

n/a

spreadSheetId

Spreadsheet ID

Yes

Sheet ID in the URL of the spreadsheet. For more info see the Google Sheets API documentation.

clientId

OAuth2 client ID for Google Sheets

Yes

n/a

clientSecret

OAuth2 client secret for Google Sheets

Yes

n/a

refreshToken

OAuth2 refreshToken for Google Sheets

Yes

n/a

5.3. Configuration properties for Red Hat Data Grid (Infinispan) as a data source

You can configure Red Hat Data Grid (Infinispan) as a data source for a virtual database.

Translator setting

The DDL for the virtual database defines a translator, or FOREIGN DATA WRAPPER. For Data Grid or Infinispan sources, set the translator to infinispan-hotrod. A corresponding SERVER definition in the DDL represents the external data source server, and associates the translator with the external server.

Dependencies

Data Grid or Infinispan provide the client libraries that you need. You do not have to specify any build dependencies.

The following table shows the information that is required in the data source properties of the custom resource:

Table 5.3. Data source properties for Data Grid

Property NameDescriptionRequiredDefault value

url

URL to connect to Infinispan

Yes

n/a

username

User name

Yes

n/a

password

Password

Yes

n/a

cacheName

Default cache name

No

n/a

authenticationRealm

Auth Realm

No

n/a

authenticationServerName

Auth Server

No

n/a

Example: Excerpt from an Infinispan custom resource, showing the format for setting key properties

datasources:
  - name: sampledb
    type: infinispan-hotrod
    properties:
      - name: url
        value: localhost:11222
      - name: user
        value: user
      - name: password
        value: pass
      - name: cacheName
        value: test

5.4. Configuration properties for MongoDB as a data source

You can configure MongoDB as a data source for a virtual database.

Translator setting

The DDL for the virtual database defines a translator, or FOREIGN DATA WRAPPER. For MongoDB sources, set the translator to mongodb. A corresponding SERVER definition in the DDL represents the external data source server, and associates the translator with the external server.

Dependencies

MongoDB does not require you to specify any build dependencies.

The following tables list the properties that are required in the CR to create a virtual database that is based on a MongoDB database

Table 5.4. Data source properties for MongoDB

Property NameDescriptionRequiredDefault value

remoteServerList

List of MongoDb servers, for example: (localhost:27012).

Yes

n/a

user

User name.

Yes

n/a

password

Password.

Yes

n/a

database

Database name to connect to.

Yes

n/a

authDatabase

Database name for authorization.

No

n/a

ssl

Use SSL Connection?

No

n/a

Example: Excerpt from an MongoDB custom resource, showing the format for setting key properties

datasources:
  - name: sampledb
    type: mongodb
    properties:
      - name: user
        value: USER_NAME
      - name: password
        value: PASSWORD
      - name: remoteServerList
        value: localhost:27012
      - name: database
        value: DATABASE_NAME

For a complete list of the properties that you can set to control how data is translated between MongoDB and a virtual database, see the Data Virtualization Reference.

5.5. Relational databases data sources configuration

To configure a virtual database to connect to a relational database for reading or writing tables, you provide information about the database by specifying a common set of properties in the custom resource, as shown in the following table:

Table 5.5. Data source properties for relational databases

Property nameDescriptionRequiredDefault value

jdbc-url

URL for the connection

Yes

n/a

username

User name

Yes

n/a

Password

Yes

n/a

n/a

jdbcDriverClass [a]

Driver class name

No

n/a

importer.schemaName

Schema name for import

Yes

n/a

[a] Depending on the database, multiple JDBC drivers might be available. To ensure that the build uses a suitable driver, some database types require you to specify a driver class.

You must also specify the Maven coordinates for the JDBC driver. For more information, see JDBC drivers.

JDBC drivers

To use a relational database as a source for a virtual database, you must provide a JDBC driver to manage the connection to the database. For some database types, such as postgreSQL and SQL Server, the JDBC driver is provided automatically during the virtual database build. However, for other databases, you must specify the driver to retrieve from the public Maven repository, or, if there is no publicly available driver, you must download the driver manually.

For downloaded drivers to be available to the build, you must add them to a private Maven repository, and then reference the repository in the virtual database CR.

If the source database requires that you specify build dependencies for the JDBC driver class, you specify these in the build.source.dependencies element in the CR. For most databases it is not necessary to define the driver class.

The following example shows an excerpt from a CR that defines the data source configuration for a sample postgreSQL database.

Example: Custom resource that defines data source properties in-line

spec:
  datasources: 1
    - name: sampledb 2
      type: postgresql 3
      properties:
        - name: username
          value: postgres
        - name: password
          value: postgres
        - name: jdbc-url 4
          value: jdbc:postgresql://database/postgres
        - name: jdbcDriverClass 5
          value: org.postgres.jdbc.Driver
  build:
     source:
       dependencies: 6
         - org.postgresql:postgresql:42.1.4

1
The datasources section lists the properties that define the connections to your data sources.
2
The custom name assigned to the source database.
3
The data source type. The type must match the translator that you specify in the ddl section of the CR.
4
The URL for the source database. The URL uses the format jdbc:xxxx where xxxx is the name of the data source. Requirements for specifying the full URL string vary by database vendor. Values in the string are not required to match names for the data source our translator.
5
Specifies the driver class for the JDBC driver.
6
Specifies a list of Maven dependency JAR files in GAV format (groupId:artifactid:version). These files define the JDBC driver files and any custom dependencies for the data source. For some database types the Data Virtualization Operator automatically adds the required JDBC libraries, and it is not necessary to specify the dependencies.

Property values defined as secrets

As an alternative to defining values for properties directly in the CR, you can define references to values in a secret object. This is especially important for securing sensitive data such as Password properties. For more information, see Section 7.2, “Using secrets to store data source credentials”.

To further tune the JDBC translator and schema import behavior, you can define additional properties. For more information see the Data Virtualization Reference.

Any Maven repository that you list must be available to the Data Virtualization Operator when it builds the virtual database. To provide the Operator with access to Maven resources that are not available from the public Maven Central repository, you can configure one or more private repositories. For more information, see Section 4.3, “Private Maven repositories”.

5.5.1. Configuration properties for Amazon Athena as a data source

You can configure an Amazon Athena query service as a data source for a virtual database.

Set the translator in the DDL for the virtual database to amazon-athena or jdbc-ansi with a matching Server definition.

The custom resource that you use to create a virtual database from Athena is the same as the CR for a standard JDBC source.

For more information, see Section 5.5, “Relational databases data sources configuration”.

The following table lists specific properties to use when you create a virtual database that is based on an Amazon Athena database.

Table 5.6. Property settings for using Amazon Athena as a data source

JDBC driver dependencyjdbc-url (Source database URL)jdbc-driver-class nameJDBC driver download link

Based on downloaded driver [a]

jdbc:awsathena://User=ACCESS_KEY;Password=SECRET_KEY;S3OutputLocation=OUTPUT;PROPERTY1=VALUE;PROPERTY2=VALUE2;

com.simba.athena.jdbc.Driver [b]

https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html[c]

[a] Obtain the driver from the link in the Driver link column of this table, and define a driver dependency that is based on the driver name in the build/source/dependencies section of the CR.
[b] Specify the driver class to ensure that the Data Virtualization Operator retrieves the correct driver from the JAR file.
[c] When you create a virtual database from an Amazon Athena source, the build does not automatically include the Athena JDBC driver. To supply the necessary driver, download it from the specified link, and add it to a Maven repository that the Data Virtualization Operator can access when it runs the OpenShift Source-To-Image (S2I) build.

For an example that shows how properties are defined in the custom resource for a virtual database that uses a relational database as its source, see Section 5.5, “Relational databases data sources configuration”.

5.5.2. Configuration properties for Amazon Redshift data sources

You can configure Amazon Redshift as a data source for a virtual database.

Set the translator in the DDL for the virtual database to redshift with a matching SERVER definition.

The custom resource that you use to create a virtual database from a Redshift source is the same as the CR for a standard JDBC source.

For more information, see Section 5.5, “Relational databases data sources configuration”.

The following table lists specific properties to use when you create a virtual database that is based on an Amazon Redshift database.

Table 5.7. Property settings for using Amazon Redshift as a data source

JDBC driver dependencyjdbc-url (URL for the source database)jdbc-driver-class nameJDBC driver download link

'com.amazon.redshift:redshift-jdbc42:jar:1.2.1.1001' [a]

jdbc:awsathena://User=ACCESS_KEY;Password=SECRET_KEY;S3OutputLocation=OUTPUT;PROPERTY1=VALUE;PROPERTY2=VALUE2;

com.amazon.redshift [b]

N/A

[a] When you create the custom resource for the virtual database, define a driver dependency with this value in the build/source/dependencies section.
[b] Specify the driver class to ensure that the Data Virtualization Operator retrieves the correct driver from the JAR file.

For an example that shows how properties are defined in the custom resource for a virtual database that uses a relational database as its source, see Section 5.5, “Relational databases data sources configuration”.

5.5.3. Configuration properties for Db2 as a data source

You can configure Db2 as a data source for a virtual database.

Set the translator in the DDL for the virtual database to db2 with a matching Server definition.

The custom resource that you use to create a virtual database from Db2 is the same as the CR for a standard JDBC source.

For more information, see Section 5.5, “Relational databases data sources configuration”.

The following table lists specific properties to use when you create a virtual database that is based on a Db2 database.

Table 5.8. Property settings for using Db2 as a data source

JDBC driver dependencyjdbc-url (URL for the source database)jdbc-driver-class nameJDBC driver download link

com.ibm.db2:jcc:jar:11.1.4.4 [a]

jdbc:db2://HOST:50000/DATABASE_NAME

com.ibm.db2.jcc.DB2Driver [b]

N/A

[a] When you create the custom resource for the virtual database, define a driver dependency with this value in the build/source/dependencies section.
[b] Specify the driver class to ensure that the Data Virtualization Operator retrieves the correct driver from the JAR file.

For an example that shows how properties are defined in the custom resource for a virtual database that uses a relational database as its source, see Section 5.5, “Relational databases data sources configuration”.

5.5.4. Configuration properties for Microsoft SQL Server as a data source

You can configure Microsoft SQL Servers as a data source for a virtual database.

Set the translator in the DDL for the virtual database to sqlserver or ms-sqlserver with a matching SERVER definition.

The custom resource that you use to create a virtual database from a SQL Server source is the same as the CR for a standard JDBC source.

For more information, see Section 5.5, “Relational databases data sources configuration”.

The following table lists specific properties to use when you create a virtual database that is based on a SQL Server database.

Table 5.9. Property settings for using SQL Server as a data source

JDBC driver dependencyjdbc-url (URL for the source database)jdbc-driver-class nameJDBC driver download link

com.microsoft.sqlserver:sqljdbc4:jar:4.0 (Optional) [a]

jdbc:microsoft:sqlserver://HOST:1433

com.microsoft.sqlserver.jdbc.SQLServerDriver (Optional)

N/A

[a] When you run the Data Virtualization Operator to build a virtual database that uses a SQL Server source, the build process automatically retrieves the required JDBC driver. It is not required to also define the driver dependency in the custom resource.

For an example that shows how properties are defined in the custom resource for a virtual database that uses a relational database as its source, see Section 5.5, “Relational databases data sources configuration”.

5.5.5. Configuration properties for MySQL as a data source

You can configure MySQL as a data source for a virtual database.

Set the translator in the DDL for the virtual database to mysql with a matching Server definition.

The custom resource that you use to create a virtual database from MySQL is the same as the CR for a standard JDBC source.

For more information, see Section 5.5, “Relational databases data sources configuration”.

The following table lists specific properties to use when you create a virtual database that is based on a MySQL database.

Table 5.10. Property settings for using MySQL as a data source

JDBC driver dependencyjdbc-url (URL for the source database)jdbc-driver-class nameJDBC driver download link

mysql:mysql-connector-java:jar:8.0.20 (Optional) [a]

jdbc:mysql://HOST:3306/DATABASE_NAME

com.mysql.jdbc.Driver (Optional)

N/A

[a] When you run the Data Virtualization Operator to build a virtual database that uses a MySQL source, the build process automatically retrieves the required JDBC driver. It is not required to also define the driver dependency in the custom resource.

For an example that shows how properties are defined in the custom resource for a virtual database that uses a relational database as its source, see Section 5.5, “Relational databases data sources configuration”.

5.5.6. Configuration properties for Oracle Database as a data source

You can configure Oracle Database as a data source for a virtual database.

Set the translator in the DDL for the virtual database to oracle with a matching SERVER definition.

The custom resource that you use to create a virtual database from an Oracle Database source is the same as the CR for a standard JDBC source.

For more information, see Section 5.5, “Relational databases data sources configuration”.

The following table lists specific properties to use when you create a virtual database that is based on Oracle Database.

Table 5.11. Property settings for using Oracle Database as a data source

JDBC driver dependencyjdbc-url (URL for the source database)jdbc-driver-class nameJDBC driver download link

com.oracle:ojdbc14:jar:10.2.0.4.0 [a]

jdbc:oracle:thin:HOST:1521:orcl

oracle.jdbc.driver.OracleDriver [b]

N/A

[a] When you create the custom resource for the virtual database, define a driver dependency with this value in the build/source/dependencies section.
[b] Specify the driver class to ensure that the Data Virtualization Operator retrieves the correct driver from the JAR file.

For an example that shows how properties are defined in the custom resource for a virtual database that uses a relational database as its source, see Section 5.5, “Relational databases data sources configuration”.

5.5.7. Configuration properties for postgreSQL as a data source

You can configure postgreSQL as a data source for a virtual database.

Set the translator in the DDL for the virtual database to postgresql with a matching SERVER definition.

The custom resource that you use to create a virtual database from a postgreSQL source is the same as the CR for a standard JDBC source. For more information, see Section 5.5, “Relational databases data sources configuration”.

The following table lists specific properties to use when you create a virtual database that is based on a postgreSQL database.

Table 5.12. Property settings for using postgreSQL as a data source

JDBC driver dependencyjdbc-url (URL for the source database)jdbc-driver-class nameJDBC driver download link

org.postgresql:postgresql:jar:42.2.5 (Optional) [a]

jdbc:postgresql://HOST:5432/DATABASE_NAME

org.postgresql.Driver (Optional)

N/A

[a] When you run the Data Virtualization Operator to build a virtual database that uses a PostgreSQL source, the build process automatically retrieves the required JDBC driver. It is not required to also define the driver dependency in the CR.

For an example that shows how these properties are specified in a CR, see Section 5.5, “Relational databases data sources configuration”.

5.6. Configuration properties for using a REST service as a data source

You can configure a REST service as a data source for a virtual database.

A common set of data source connection properties is required for all REST-based data sources. In addition to the common properties, services that are based on specific REST-based standards, such as OData or OpenAPI, require specific translators.

By default, translators are unable to parse the security configuration of a secured API. To enable translators to access data for a secured API, the CR must specify the security properties for the API.

Translator setting

For generic services that use REST directly, and that are not based on particular specifications, set the translator in the DDL for the virtual database to rest with a matching SERVER definition. Generic REST-based services lack built-in mechanisms for passing SQL query conditions to a REST API endpoint. As a result, the data virtualization service cannot automatically convert query criteria for these services into query parameters.

To pass SQL queries as XML or JSON payloads to the endpoints of these services, you must use the invokeHttp procedure, and use it to specify your query strings and headers.

Some REST-based data sources, such as OData, OpenAPI, and SOAP have specific translators that are based on the REST configuration.

For more information, see Rest translator in the Data Virtualization Reference.

The following tables show the data source information that is required in the data source properties of the custom resource:

Table 5.13. Data source properties for REST

Property Name

Description

Required

Default value

endpoint

Endpoint for the service.

Yes

n/a

securityType

Security type to use. Available options are http-basic, openid-connect or empty.

No

no security

If the security type is defined as http_basic you must also set the following properties:

Table 5.14. HTTP basic properties for REST data sources

Property name

Description

Required

Default value

userName

User name

Yes

n/a

password

Password

Yes

n/a

If the security type is defined as openid-connect, you must set the following properties:

Table 5.15. OpenID Connect properties for REST data sources

Property Name

Description

Required

Default value

userName

User name

Yes

n/a

password

Password

Yes

n/a

clientId

ClientId from connected app.

Yes

n/a

clientSecret

clientSecret from connected app.

Yes

n/a

authorizeUrl

clientSecret from connected app.

Yes

n/a

accessTokenUrl

clientSecret from connected app.

Yes

n/a

scope

clientSecret from connected app.

No

n/a

Alternatively, for openid-connect you can specify the refreshToken property and avoid using the userName and password properties. The process obtaining a refresh token differs for different services. Describing how to obtain refresh tokens is beyond the scope of this document.

Note

To enable communications with REST data source endpoints over secure HTTP (HTTPS), you must have a truststore configured for the endpoint.

For information about configuring a custom TLS certificates, see xref:

For a complete list of the properties that you can set to control how data is translated between REST-based services and a virtual database, see the OData, OData V4, OpenAPI, and Web Services translator sections in the Data Virtualization Reference.

5.7. Configuration properties for OData as a data source

You can configure OData as a data source for a virtual database.

Translator setting

The DDL for the virtual database defines a translator, or FOREIGN DATA WRAPPER. For OData sources, set the translator to odata. For an OData V4 service, use odata4 A corresponding SERVER definition in the DDL represents the external data source server, and associates the translator with the external server.

Because OData services are based on REST, they follow the same properties model as REST-based connections.

The following configuration showing openid_connect security type with a OData service

A sample configuration

datasources:
  - name: sampledb
    type: odata4
    properties:
      - name: endpoint
        value: https://dv-customer-myproject.apps-crc.testing/odata/accounts/customer
      - name: securityType
        value: openid-connect
      - name: clientId
        value: dv
      - name: clientSecret
        value: xxxxxxxxxxx
      - name: authorizeUrl
        value: https://keycloak-myproject.apps-crc.testing/auth/realms/master/protocol/openid-connect/auth
      - name: accessTokenUrl
        value: https://keycloak-myproject.apps-crc.testing/auth/realms/master/protocol/openid-connect/token

5.8. Configuration properties for OpenAPI as a data source

You can configure an OpenAPI service as a data source for a virtual database.

Because OpenAPI services are based on REST, they follow the same properties model as REST-based connections.

Translator setting

The DDL for the virtual database defines a translator, or FOREIGN DATA WRAPPER. For OpenAPI sources, set the translator to openapi. A corresponding SERVER definition in the DDL represents the external data source server, and associates the translator with the external server.

The openapi translator assumes that the endpoint in the API document is set to the target location /openapi, and it builds a source model that is based on that assumption.

If the API endpoint is set to a different target, a configuration setting must be specified so that the translator can locate the endpoint and import data correctly. The following examples show a DDL SCHEMA statement and an environment variable that you can set to specify the non-standard endpoint, /swagger.

DDL SCHEMA statement for defining a non-standard OpenAPI endpoint

CREATE SCHEMA sourceModel SERVER oService OPTIONS ("importer.metadataUrl" '/swagger.json');

Example: Sample configuration that defines a non-standard OpenAPI endpoint

datasources:
  - name: sampledb
    type: openapi
    properties:
      - name: userName
        value: user
      - name: password
        value: pass
      - name: importer.metadataUrl
        value: /swagger.json

If the API is secured, the translator is unable to process the security configuration of the service automatically. The translator understands only the API document and its responses. To process security settings properly you must define them as REST properties, as described in Section 5.6, “Configuration properties for using a REST service as a data source”.

5.9. Configuration properties for Salesforce as a data source

You can configure Salesforce as a data source for a virtual database.

Salesforce uses OAuth 2.0 for authentication and authorization. Before you can set up a virtual database to import and query Salesforce data, you must obtain OAuth credentials for the virtual database from Salesforce. For information about how to set up OAuth, see Section 5.9.1, “Setting up an OAuth connection to Salesforce”

Translator setting

The DDL for the virtual database defines a translator, or FOREIGN DATA WRAPPER. For Salesforce sources, set the translator to salesforce. A corresponding SERVER definition in the DDL represents the external data source server, and associates the translator with the external server.

Dependencies

Salesforce does not require you to specify any build dependencies.

The following tables list the properties that are required in the custom resource to create a virtual database that is based on a Salesforce database:

Table 5.16. Data source properties for Salesforce

Property NameDescriptionRequiredDefault value

url

URL for salesforce.

No

https://login.salesforce.com/services/Soap/u/45.0

username

User account for salesforce.com.

Yes

n/a

password

Password for salesforce.com.

Yes

n/a

clientId

ClientId from connected app.

Yes

n/a

clientSecret

clientSecret from connected app.

No

n/a

refreshToken

Refresh Token [a]

No

n/a

[a] If your connected app uses refresh tokens to authenticate, rather than name and password, you must define the refreshToken property in the CR, in place of the user name and password properties. Information about obtaining refresh tokens is beyond the scope of this document. For information about how to obtain a refresh token for your connected app, see the Salesforce documentation.

The following example shows a configuration that uses simple user name and password login.

Example: Excerpt from a virtual database custom resource that connects to Salesforce by using name and password authentication

datasources:
  - name: sampledb
    type: salesforce
    properties:
      - name: userName
        value: user
      - name: password
        value: pass

You can obtain the clientId and clientSecret from Salesforce when you create your Salesforce application.

For a complete list of the properties that you can set to control how data is translated between Salesforce and a virtual database, see the Data Virtualization Reference.

5.9.1. Setting up an OAuth connection to Salesforce

Before the data virtualization service can retrieve data from a Salesforce database, you must enable configure it as a connected app in Salesforce that is OAuth-enabled. After you configure OAuth, Salesforce generates a client ID and client secret that you must add to the CR file that defines the connection from the virtual database to Salesforce.

To configure OAuth you create a connected app in Salesforce that can request access to Salesforce data on behalf of the data virtualization service. In the settings for the connected app, you enable integration with the Salesforce API by using the OAuth 2.0.

Prerequisites

  • You have a Salesforce.com account that has access to the data that you want to integrate in a virtual database.
Note

The following steps are based on Salesforce Classic. If you use a different version of Salesforce, you might use a different procedure. For more information about creating connected apps in Salesforce, see the Salesforce documentation.

Procedure

  1. From Salesforce, log into your account.
  2. Click SetUp in the profile menu.
  3. In the Build section of the navigation sidebar, expand Create, and then click Apps.
  4. In the Connected Apps section, click New.
  5. Complete the required fields.
  6. In the section API (Enable OAuth Settings), select Enable OAuth Settings to display the OAuth settings.

  7. Complete the required OAuth fields. In the OAuth Scopes field, you must select the following scopes:

    • Access and manage your data (api).
    • Access your basic information (id, profile, email, address, phone).
    • Allow access to your unique identifier (openid).
    • Full access (full).
    • Perform requests on your behalf at any time (refresh_token, offline_access).
  8. Select Require Secret for Web Server Flow.
  9. Click Save and then click Continue.
  10. Make a note of the values in the Consumer Key and Consumer Secret fields. These values are required for properties in the CR that specifies how the virtual database connects to Salesforce.

5.10. Configuration properties for using FTP/SFTP as a data source

Translator setting

The DDL for the virtual database defines a translator, or FOREIGN DATA WRAPPER. For FTP sources, set the translator to ftp. A corresponding SERVER definition in the DDL represents the external data source server, and associates the translator with the external server.

Note

To enable secure transmission over SFTP you must provide a TLS certificate. For more information about how to use certificates with data virtualization on OpenShift, see Section 7.1, “Certificates and data virtualization”

The following table shows the information that is required in the data source properties of the custom resource:

Table 5.17. Data source properties for SFTP

Property NameDescriptionRequiredDefault value

host

Host name of the FTP server.

yes

n/a

port

Port of the FTP server.

No

21

username

User for remote server login

Yes

n/a

password

Password for remote server login.

Yes

n/a

parentDirectory

Directory that contains file data.

Yes

n/a

isFtps

FTP security.

No

false

Example: Excerpt from an FTP/SFTP custom resource, showing the format for setting key properties

datasources:
  - name: sampleftp
    type: ftp
    properties:
      - name: host
        value: localhost
      - name: parent-directory
        value: /path/to/file/
      - name: username
        value: user
      - name: password
        value: pass

5.11. Configuration properties for SOAP as a data source

You can configure SOAP as a data source for a virtual database.

Translator setting

The DDL for the virtual database defines a translator, or FOREIGN DATA WRAPPER. For SOAP sources, set the translator to soap or ws. A corresponding SERVER definition in the DDL represents the external data source server, and associates the translator with the external server.

The Web services or SOAP translator exposes stored procedures for calling web or SOAP services. Results from this translator are typically used with the TEXTTABLE or XMLTABLE table functions to process data formatted in CSV or XML.

Dependencies

SOAP data sources do not require you to specify any build dependencies.

The following table shows the data source information that is required in the data source properties of the custom resource:

Table 5.18. Data source properties for SOAP

Property NameApplies toRequiredDefault ValueDescription

EndPoint

HTTP and SOAP

false

n/a

URL for HTTP; service endpoint for SOAP. Not required if using HTTP to invoke procedures that specify absolute URLs. Used as the base URL if an invoked procedure uses a relative URL.

SecurityType

HTTP and SOAP

false

none

Type of authentication to use with the web service. Allowed values [None,HTTPBasic]

AuthUserName

HTTP and SOAP

false

n/a

Name value for authentication, used in HTTPBasic and WsSecurity.

AuthPassword

HTTP and SOAP

false

n/a

Password value for authentication, used in HTTPBasic and WsSecurity.

ConfigFile

HTTP and SOAP

false

n/a

CXF client configuration file or URL.

EndPointName

HTTP and SOAP

false

teiid

Local part of the endpoint QName to use with this connection. Must match the one defined in cxf file

ServiceName

SOAP

false

n/a

Local part of the service QName to use with this connection.

NamespaceUri

SOAP

false

http://teiid.org

Namespace URI of the service QName to use with this connection.

RequestTimeout

HTTP and SOAP

false

n/a

Timeout for request.

ConnectTimeout

HTTP and SOAP

false

n/a

Timeout for connection.

Wsdl

SOAP

false

n/a

WSDL file or URL for the web service.

Example: Excerpt from an SOAP custom resource, showing the format for setting key properties

  datasources:
    - name: soapCountry
      type: soap
      properties:
        - name: wsdl
          value: http://www.oorsprong.org/websamples.countryinfo/CountryInfoService.wso?WSDL
        - name: namespaceUri
          value: http://www.oorsprong.org/websamples.countryinfo
        - name: serviceName
          value: CountryInfoService
        - name: endPointName
          value: CountryInfoServiceSoap12