Skip to content

Latest commit

 

History

History
683 lines (531 loc) · 23.6 KB

datasources.adoc

File metadata and controls

683 lines (531 loc) · 23.6 KB
Raw
Table of Contents

Data Source Support

In this section defines all the data sources that Operator Supports and how to configure them and any dependencies it may require.

Please note any property that you want to configure, you can define reference to the value from a secret than providing a value directly. For example, the Password properties need to be secured, those can defined from secret as below.

- name: SPRING_DATASOURCE_SAMPLEDB_PASSWORD
  valueFrom:
     secretKeyRef:
       name: postgresql
       key: password
Note
 When defining a environment property, characters like . are not allowed, but you can replace them with character _ and application will convert them automatically at runtime.
Note
 If any configured property in a ConfigMap or Secret is missing, the Operator will not start the build until such configuration value is supplied. Also if the configuration changes after the service is running, the service will be automatically redeployed with new values.
Note
 All the data sources below in addition to what is defined here support "importer.xxxxx" properties are defined on their translator page for further customization. Pleas refer to that documentation on available properties and their possible values and descriptions.

Configuring Relational Database

For configuring the translator name in VDB, the following translators are supported

  • amazon-athena

    jdbc-driver-link : https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html
jdbc-driver-class : com.simba.athena.jdbc.Driver
jdbc-url : jdbc:awsathena://User={AccessKey};Password={SecretKey};S3OutputLocation={Output};{Property1}={Value1};{Property2}={Value2};

  • actian-vector

    maven-dependancy : 'com.ingres.jdbc:iijdbc:jar:10.0-4.0.5'
jdbc-driver-class : com.ingres.jdbc.IngresDriver
jdbc-url : jdbc:ingres://{host}:AH7/{database-name}

  • db2

    maven-dependancy : 'com.ibm.db2:jcc:jar:11.1.4.4'
jdbc-driver-class : com.ibm.db2.jcc.DB2Driver
jdbc-url : jdbc:db2://{host}:50000/{database-name}

  • derby

    maven-dependancy : 'org.apache.derby:derbyclient:jar:10.15.1.3' (dependancy automatically provided)
jdbc-driver-class : org.apache.derby.jdbc.ClientDriver
jdbc-url : jdbc:derby://{host}:1527/{database-name}

  • exasol

    maven-dependancy : 'com.exasol:exasol-jdbc:jar:6.2.5'
jdbc-driver-class : com.exasol.jdbc.EXADriver
jdbc-url : jdbc:exa:{host}:{port}[;{prop_1}={value_1}]...[;{prop_n}={value_n}]

  • h2

    maven-dependancy : 'com.h2database:h2:jar:1.4.200' (dependancy automatically provided)
jdbc-driver-class : org.h2.Driver
jdbc-url : jdbc:h2:{file:} {path} {database-name}

  • hana (SAP Hana using the JDBC driver)

    maven-dependancy : 'com.sap.cloud.db.jdbc:ngdbc:pom:2.4.76'
jdbc-driver-class : com.sap.db.jdbc.Driver
jdbc-url : jdbc:sap://{host}:{port}[/?{options}]

  • hive (Hive using the Hive JDBC driver)

    maven-dependancy : 'org.apache.hive:hive-jdbc:jar:3.1.2'
jdbc-driver-class : org.apache.hive.jdbc.HiveDriver
jdbc-url : jdbc:hive2://{host}:10000/default

  • hsql

    maven-dependancy : 'org.hsqldb:hsqldb:jar:2.3.2'
jdbc-driver-class : org.hsqldb.jdbc.JDBCDriver
jdbc-url : jdbc:hsqldb:http://{host}[:{port}][/{alias}][{key-value-pairs}]'

  • impala (Cloudera Impala)

    maven-dependancy : 'com.cloudera.impala:jdbc:jar:2.5.5.1007'
jdbc-driver-class : com.cloudera.impala.jdbc.Driver
jdbc-url : jdbc:impala://{host}:{port}

  • informix

    maven-dependancy : 'com.ibm.informix:jdbc:jar:4.50.3'
jdbc-driver-class : com.informix.jdbc.IfxDriver
jdbc-url : jdbc:informix-sqli://{host}:{port}[/{database-name}: INFORMIXSERVER={server-name}

  • ingres

    maven-dependancy : 'com.ingres.jdbc:iijdbc:jar:10.2-4.1.10'
jdbc-driver-class : com.ingres.jdbc.IngresDriver
jdbc-url : jdbc:ingres://{host}:{port}:21071/{database-name}

  • jdbc-simple

  • jdbc-ansi

  • jtds-sqlserver (Connect Microsoft SQLServer using JTDS drivers)

    maven-dependancy : 'net.sourceforge.jtds:jtds:jar:1.3.1' (dependancy automatically provided)
jdbc-driver-class : net.sourceforge.jtds.jdbc.Driver
jdbc-url : jdbc:jtds:sqlserver://{host}:1433/{database-name}

  • ms-sqlserver (Microsoft SQLServer using the Microsoft driver)

    maven-dependancy : 'com.microsoft.sqlserver:sqljdbc4:jar:4.0' (dependancy automatically provided)
jdbc-driver-class : com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc-url : jdbc:microsoft:sqlserver://{host}:1433

  • mysql

    maven-dependancy : 'mysql:mysql-connector-java:jar:8.0.20' (dependancy automatically provided)
jdbc-driver-class : com.mysql.jdbc.Driver
jdbc-url : jdbc:mysql://{host}:3306/{database-name}

  • netezza

    maven-dependancy : 'org.netezza:nzjdbc:jar:1.0'
jdbc-driver-class : org.netezza.Driver.
jdbc-url : jdbc:netezza://{host}:{port}/{database-name}

  • oracle

    maven-dependancy : 'com.oracle:ojdbc14:jar:10.2.0.4.0'
jdbc-driver-class : oracle.jdbc.driver.OracleDriver
jdbc-url : jdbc:oracle:thin:{host}:1521:orcl

  • osisoft-pi (OSISOFT’s PI Database using osisoft’s JDBC Driver)

    jdbc-driver-link : https://livelibrary.osisoft.com/LiveLibrary/content/en/jdbc-v4/
jdbc-driver-class : com.osisoft.jdbc.Driver
jdbc-url : jdbc:pioledbent://{das-name}/Data Source={af-name}; Integrated Security=SSPI

  • phoenix (Apache Phoenix using the Phoenix JDBC Driver)

    maven-dependancy : 'org.apache.phoenix:phoenix-queryserver-client:jar:5.0.0-HBase-2.0'
jdbc-driver-class : org.apache.phoenix.jdbc.PhoenixDriver
jdbc-url : jdbc:phoenix:{host}:{port}:/{datbase-name}

  • postgresql

    maven-dependancy : 'org.postgresql:postgresql:jar:42.2.5' (dependancy automatically provided)
jdbc-driver-class : org.postgresql.Driver
jdbc-url : jdbc:postgresql://{host}:5432/{database-name}

  • prestodb (PrestoDB connecting using JDBC Driver)

    maven-dependancy : 'com.facebook.presto:presto-jdbc:jar:0.236'
jdbc-driver-class : com.facebook.presto.jdbc.PrestoDriver
jdbc-url : jdbc:presto://{host}:{port};

  • redshift (Amazon Red Shift)

    maven-dependancy : 'com.amazon.redshift:redshift-jdbc42:jar:1.2.1.1001'
jdbc-driver-class : com.amazon.redshift
jdbc-url : jdbc:redshift://endpoint:{port}/{database-name}

  • sap-iq

    jdbc-driver-link : http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01776.1602/doc/html/san1357754912881.html
jdbc-driver-class : com.sybase.jdbc4.jdbc.SybDriver
jdbc-url : jdbc:sybase:Tds:{host}:{port}?ServiceName={database-name}

  • sqlserver (Same as ms-sqlserver)

    maven-dependancy : 'com.microsoft.sqlserver:sqljdbc4:jar:4.0'
jdbc-driver-class : com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc-url : jdbc:microsoft:sqlserver://{host}:1433

  • sybase

    maven-dependancy : 'com.esen.jdbc:sybase-jconn3:jar:6.0'
jdbc-driver-class : com.sybase.jdbc4.jdbc.SybDriver
jdbc-url : jdbc:sybase:Tds:{host}:{port}[?ServiceName={database-name}]

  • teradata

    maven-dependancy : 'com.teradata.jdbc:terajdbc4:jar:16.20.00.10'
jdbc-driver-class : com.ncr.teradata.TeraDriver
jdbc-url : jdbc:teradata://{host}:{port}/{database-name}

  • teiid (Connecting to another Teiid instance using its JDBC driver)

    maven-dependancy : dependancy automatically provided
jdbc-driver-class : org.teiid.jdbc.TeiidDriver
jdbc-url : jdbc:teiid:{vdb}@mm://{host}:31000

  • vertica

    jdbc-driver-link : https://www.vertica.com/client-drivers/
jdbc-driver-class : com.vertica.jdbc.Driver
jdbc-url : jdbc:vertica:{host}:5433/{database-name}

For configuring the relational database like Oracle, MS-SQLServer, PostgreSQL etc, the following properties are required.

Property Name Description Required Default value

jdbc-url

URL For the connection

Yes

n/a

username

User Name

Yes

n/a

Pasword

Yes

n/a

n/a

driver-class-name

Driver Name

No

n/a

importer.schemaName

Schema Name for import

Yes

n/a

A sample configuration showing the properties for a sample postgresql database

A sample configuration
datasources:
  - name: sampledb
    type: postgresql
    properties:
      - name: username
        value: postgres
      - name: password
        value: postgres
      - name: jdbc-url
        value: jdbc:postgresql://database/postgres

or

A sample configuration using secrets
datasources:
  - name: sampledb
    type: postgresql
    properties:
      - name: username
        valueFrom:
          secretKeyRef:
            name: sampledb-secret
            key: username
      - name: password
        valueFrom:
          secretKeyRef:
            name: sampledb-secret
            key: password
      - name: jdbc-url
        value: jdbc:postgresql://database/postgres
Note
 where {NAME} denotes the custom name given by the user, that defines the server in the VDB’s DDL. For example, CREATE SERVER sampledb FOREIGN DATA WRAPPER postgresql in your DDL where names match EXACTLY to above.

You define many more properties to further tune the JDBC translator and schema import behavior. Please check Teiid translator documentation.

The user is also required to provide a maven dependency for JDBC driver class for the above data source in spec/dependencies line in the CR. It is expected that the maven repository is reachable by the Operator. Please see how configure a private maven repository.

Configuring MongoDB database

The translator name to use in the VDB’s DDL is mongodb with its Server definition.

For configuring the MongoDB database the following properties are needed

Property Name

Description

Required

Default value

remoteServerList

List of MongoDb servers ex:(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

User does not need to add any dependencies. The currently supported version is version 3 of the MongoDB.

A sample configuration
datasources:
  - name: sampledb
    type: mongodb
    properties:
      - name: user
        value: user
      - name: password
        value: pass
      - name: remoteServerList
        value: localhost:27012
      - name: database
        value: test

Configuring SOAP web service data source

The translator name to use in the VDB’s DDL is soap or ws with its Server definition.

For configuring the SOAP webservice the following properties are needed

Property Name applies to Required Default Value Description

EndPoint

HTTP & SOAP

false

n/a

URL for HTTP, Service Endpoint for SOAP. Not required if using HTTP invoke procedures that specify absolute URLs. Will be used as the base URL if an invoke procedure uses a relative URL.

SecurityType

HTTP & SOAP

false

none

Type of Authentication to used with the web service. Allowed values ["None","HTTPBasic"]

AuthUserName

HTTP & SOAP

false

n/a

Name value for authentication, used in HTTPBasic and WsSecurity

AuthPassword

HTTP & SOAP

false

n/a

Password value for authentication, used in HTTPBasic and WsSecurity

ConfigFile

HTTP & SOAP

false

n/a

CXF client configuration File or URL

EndPointName

HTTP & SOAP

false

teiid

Local part of the end point QName to use with this connection, needs to match 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 & SOAP

false

n/a

Timeout for request

ConnectTimeout

HTTP & SOAP

false

n/a

Timeout for connection

Wsdl

SOAP

false

n/a

WSDL file or URL for the web service

User does not need to add any dependencies.

A sample configuration
  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

Configuring S3 as Source

The translator name to use in the VDB’s DDL is s3 with its Server definition.

For configuring the S3 the following properties are needed

Property Name

Description

Required

Default value

endpoint

The S3 service endpoint

no

https://s3.amazonaws.com

region

S3 region ex: us-east-2

no

US-EAST-1

bucket

Bucket Name

Yes

n/a

accesskey

Access Key

Yes

n/a

secretkey

Secret key

Yes

n/a

When S3 is setup, one will need to create a bucket and will also can find out the the access-key and secret-key for access.

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

Configuring Ceph/MinIO as Source using S3

To access a source like these use the the translator name in the VDB’s DDL is s3 with its Server definition.

For example, define like below in DDL

CREATE SERVER cephstore FOREIGN DATA WRAPPER "s3";
A sample ceph configuration
datasources:
  - name: cephstore
    type: amazon-s3
    properties:
      - name: endpoint
        value: http://rook-ceph-rgw-my-store-rook-ceph.apps.dv-44-a.openshift-aws.rhocf-dev.com
      - name: bucket
        value: mybucket
      - name: accesskey
        value: xxxxxxxx
      - name: secretkey
        value: xxxxxx

Configuring Infinispan as source

The translator name to use in the VDB’s DDL is infinispan-hotrod with its Server definition.

For configuring the Infinispan the following properties are needed

Property Name

Description

Required

Default 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

User does not need to add any dependencies, as the required client libraries are already provided.

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

Configuring Salesforce

The translator name to use in the VDB’s DDL is salesforce with its Server definition.

For connecting to Salesforce and importing/querying all SObject, the user fist needs to create OAuth based connection in the Salesforce. Log into your Salesforce account, go to SetUp → Create → Apps. Then, create a new "Connected App" and provide details there. When you are done, should get a client-id and client-secret values. Then to use this application from Teiid to connect to Salesforce by configuring the below properties.

Property Name

Description

Required

Default value

url

URL of the salesforce

No

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

username

User 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

No

n/a
Note
 if user does not want to connect using the username and password, then the module also supports using the refreshToken. So for above you can provide refreshToken property and remove username and password properties. Documenting obtaining of the refresh token from is beyond scope of this document.

No further dependencies from user is required for this.The below showing simple user name and password based login.

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

clientId and clientSecret can be obtained from Salesforce when you create your Salesforce application.

Configuring OData Based Source

The translator name to use in the VDB’s DDL is odata with its Server definition for any OData V2 services. For OData V4 service use odata4

As per the connection underneath, follow the same properties as REST based connection

The below 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

Configuring SAP Gateway Based Source

The translator name to use in the VDB’s DDL is sap-gateway with its Server definition. This uses OData V2 translator underneath to connect to SAP Gateway. Only Http Basic auth has been verified.

As per the connection underneath, follow the same properties as REST based connection

A sample configuration
datasources:
  - name: sampledb
    type: sap-gateway
    properties:
      - name: userName
        value: user
      - name: password
        value: pass

Configuring FTP Based Source

The translator name to use in the VDB’s DDL is ftp with its Server definition.

Note
 The secure FTPS is supported, however to support this user needs to provide trust certificates to system. See the section how to use certificates in the OpenShift/Kubernetes with Virtual Database here

The following properties can be configured for creating the FTP based source.

Property Name

Description

Required

Default value

host

Host name of the FTP server

yes

n/a

port

Port of the FTP server

No

21

username

User name for remote server login

Yes

n/a

password

Password for remote server login

Yes

n/a

parentDirectory

Directory where file located in remote server

Yes

n/a

isFtps

Ftp Security

No

false
A sample configuration
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

Configuring OpenAPI Based Source

The translator name to use in the VDB’s DDL is openapi with its Server definition. When this translator is used, the source will look for endpoint /openapi files and build the source model based on it. However if the API document endpoint is defined as something else, one could configure that in the VDB’s DDL on schema statement as

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

alternatively, one can define a ENVIRONMENT property instead for the same

A sample configuration
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, security aspects of the service are not understood automatically, only the API document and its responses are understood by the translator. Those should be defined using REST properties below

As per the connection underneath, follow the same properties as REST based connection

Configuring REST Based Source

For rest based that are using the rest directly can use rest as the translator name. Note, for sources like OData and SAP there are separate translators based on same configuration here. When used directly the user is provided with invokeHttp as defined in the Teiid documents to use to execute any endpoint using SQL.

The REST API based connection uses the following properties

Property Name

Description

Required

Default value

endpoint

Endpoint for the service

Yes

n/a

securityType

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

No

no security

if the security type is defined as http_basic the following properties are needed

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 the following properties are needed

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 the openid-connect you can also supply 'refreshToken' property and avoid the userName and password properties, however describing on how to obtaining a refresh token is beyond this document as it is different for different services.

Note
 when using a endpoint that is Http(S) then you might have to configure the truststore for the third party endpoint. To do that, one would need to configure a custom truststore as defined here

Configuring Google Sheets as Source

The translator name to use in the VDB’s DDL is google-spreadsheet with its Server definition. This will represent each Sheet on a google spread sheet as a table in Teiid.

Before you can access the Google Spreadsheet using Teiid, one needs to provide OAuth2 specific credentials to Teiid. To provide the access, use instructions from https://developers.google.com/identity/protocols/OAuth2InstalledApp and create project ad select the "Google Spreadsheets API" as library you want to access. Then create a "OAuth Client ID" Credential using the UI. At the end of this exercise, you will have a "Client ID" and "Client Secret". Then using the Google’s OAuth2.0 Playground https://developers.google.com/oauthplayground/ select the "Google Sheet API v4" option and select "drive" and "spreadsheets" endpoints as allowable scopes. Then Authorize APIs, you will end up with a "RefreshToken". Then use all these properties for further configuration.

Property Name

Description

Required

Default value

spreadSheetName

Name of the Google Spreadsheet

Yes

n/a

spreadSheetId

Spread Sheet Id

Yes

Sheet Id, Look in URL of the Spreadsheet. For more info see https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id

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