Skip to content

Configuring Data Sources

In the Streaming Integrator, there are datasources specific to both the Streaming Integrator server and Streaming Integrator Tooling. The data sources of each runtime are defined in the <SI_HOME>|<SI_TOOLING_HOME>/conf/server/deployment.yaml file.

e.g., To configure a data source in the server runtime, the relevant configurations need to be added in the <SI_Home>/conf/server/deployment.yaml file.

To view a sample data source configuration for each database type supported, expand the following sections:

Info

If the database driver is not an OSGI bundle, then it should be converted to OSGI (using jartobundle.sh) before placing it in the <SI_HOME>|<SI_TOOLING_HOME>/lib directory. For detailed instructions,see Adding Third Party Non OSGi Libraries.

e.g., sh WSO2_SI_HOME/bin/jartobundle.sh ojdbc6.jar WSO2_SI_HOME/lib/

The database should be tuned to handle the total maxPoolSize (The maximum number of threads that should be reserved at any given time to handle events) that is defined in the deployment.yaml file.

MySQL
    wso2.datasources:
     dataSources:
     description: The datasource used for test database
     jndiConfig:
     definition:
       type: RDBMS
       configuration:
         jdbcUrl: jdbc:mysql://hostname:port/testdb
         username: root
         password: root
         driverClassName: com.mysql.jdbc.Driver
         minIdle: 5
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false
POSTGRES
wso2.datasources:
 dataSources:
     description: The datasource used for test database
     jndiConfig:
     definition:
       type: RDBMS
      configuration:
        jdbcUrl: jdbc:postgresql://hostname:port/testdb
        username: root
        password: root
        driverClassName: org.postgresql.Driver
        minIdle: 5
        maxPoolSize: 50
        idleTimeout: 60000
        connectionTestQuery: SELECT 1
        validationTimeout: 30000
        isAutoCommit: false
Oracle

There are two ways to configure this database type. If you have a System Identifier (SID), use this (older) format:

jdbc:oracle:thin:@[HOST][:PORT]:SID

wso2.datasources:
 dataSources:
     description: The datasource used for test database
     jndiConfig:
     definition:
       type: RDBMS
       configuration:
         jdbcUrl: jdbc:oracle:thin:@hostname:port:SID
         username: testdb
         password: root
         driverClassName: oracle.jdbc.driver.OracleDriver
         minIdle: 5
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

If you have an Oracle service name, use this (newer) format:

jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE

wso2.datasources:
 dataSources:
     description: The datasource used for test database
     jndiConfig:
     definition:
       type: RDBMS
       configuration:
         jdbcUrl: jdbc:oracle:thin:@hostname:port/SERVICE
         username: testdb
         password: root
         driverClassName: oracle.jdbc.driver.OracleDriver
         minIdle: 5
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

The Oracle driver need to be converted to OSGi (using jartobundle.sh) before put into SI_HOME/lib directory. For detailed instructions, see Adding Third Party Non OSGi Libraries.

MSSQL
wso2.datasources:
 dataSources:
     description: The datasource used for test database
     jndiConfig:
     definition:
       type: RDBMS
       configuration:
         jdbcUrl: jdbc:sqlserver://hostname:port;databaseName=testdb
         username: root
         password: root
         driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
         minIdle: 5
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

The following tables explain the default data sources configured in the Streaming Integrator components for different purposes, and how to change them.

RDBMS data provider

Database Access Requirement
The RDBMS provider publishes records from RDBMS tables into generated widgets. It can also be configured to purge records in tables. In order to carry out these actions, this provider requires access to read and delete records in user defined tables of the database. For more information about the RDBMS data provider, see Generating Widgets .
Required/Optional This is required if you select a datasource when generating the widget or use existing widgets that connect to the RDBMS data provider when you run the dashboard server for the Streaming Integrator.
Default Datasource Name SAMPLE_DB
Default Database The default H2 database location is <DASHBOARD_HOME>/wso2/dashboard/database/SAMPLE_DB.
Tables The default database shipped with a sample table named TRANSACTION_TABLE .
Schemas and Queries

The schema for the sample table is TRANSACTIONS_TABLE (creditCardNo VARCHAR(50), country VARCHAR(50), transaction VARCHAR(50), amount INT)

The default queries can be viewed here .

Tested Database Types H2, MySQL, Postgres, Mssql, Oracle 11g

Carbon coordination

Database Access Requirement Carbon coordination supports zookeeper and RDBMS based coordination. In RDBMS coordination, database access is required for updating the heartbeats of the nodes. In addition, database access is required to update the coordinator and the other members in the cluster. For more information, see Configuring Cluster Coordination.
Required/Optional This is required. However, you can also use Zookeeper coordination instead of RDBMS.
Default Datasource Name WSO2_CARBON_DB
Tables LEADER_STATUS_TABLE, MEMBERSHIP_EVENT_TABLE, REMOVED_MEMBERS_TABLE, CLUSTER_NODE_STATUS_TABLE
Schemas and Queries

Information about the default queries and the schema can be viewed here.

Tested Database Types MySQL, Postgres, Mssql, Oracle 11g

Streaming Integrator core - persistence

Database Access Requirement This involves persisting the state of Siddhi Applications periodically in the database. State persistence is enabled by selecting the org.wso2.carbon.stream.processor.core.persistence.DBPersistenceStore class in the state.persistence section of the <SI_Home>/conf/<server>/deployment.yaml file. For more information, see Configuring Database and File System State Persistence.
Required/Optional This is optional. WSO2 is configured to persist the state of Siddhi applications by default.
Default Datasource Name N/A. If state persistence is required, you need to configure the datasource in the <SI_Home>/conf/<server>/deployment.yaml file under state.persistence >config > datasource .
Tables N/A. If state persistence is required, you need to specify the table name to be used when persisting the state in the <SI_Home>/conf/<server>/deployment.yaml file under state.persistence > config > table .
Schemas and Queries

Information about the default queries and schema can be viewed here.

Tested Database Types H2, MySQL, Postgres, Mssql, Oracle 11g

Streaming Integrator - Status Dashboard

Database Access Requirement To display information relating to the status of your Streaming Integrator deployment, the Status Dashboard needs to retrieve carbon metrics data, registered Streaming Integrator server details and authentication details within the cluster from the database. For more information, see Monitoring Stream Processor .
Required/Optional Required
Default Datasource Name WSO2_STATUS_DASHBOARD_DB, WSO2_METRICS_DB
Tables METRIC_COUNTER, METRIC_GAUGE, METRIC_HISTOGRAM, METRIC_METER, METRIC_TIMER, WORKERS_CONFIGURATIONS, WORKERS_DETAILS
Schemas and Queries

Information about the default queries and schema: https://github.com/wso2/carbon-analytics/blob/v2.0.250/components/org.wso2.carbon.status.dashboard.core/src/main/resources/queries.yaml

Tested Database Types H2, MySQL, Mssql, Oracle 11g ( Postgres is tested with Carbon-Metrics only)

Siddhi RDBMS store

Database Access Requirement It gives the capability of creating the tables at the siddhi app runtime and access the existing tablesif a user defined carbon datasource or JNDI property in a siddhi app. Documentation can be found in https://wso2-extensions.github.io/siddhi-store-rdbms/api/4.0.15/
Required/Optional Optional
Default Datasource Name No such default Datasource. User has to create the datasource in the Siddhi app
Tables No such default tables. User has to define the tables
Schemas and Queries

Information about the default queries and schema: https://github.com/wso2-extensions/siddhi-store-rdbms/blob/v4.0.15/component/src/main/resources/rdbms-table-config.xml

Tested Database Types H2, MySQL, Mssql, Oracle 11g, DB2, PostgreSQL

Carbon Dashboards

Database Access Requirement Carbon Dashboard feature uses its datasource to persist the dashboard related information
Required/Optional Optional
Default Datasource Name WSO2_DASHBOARD_DB
Tables DASHBOARD_RESOURCES
Schemas and Queries

https://github.com/wso2/carbon-dashboards/tree/master/features/org.wso2.carbon.dashboards.api.feature/src/main/resources/sql

Tested Database Types H2, MySQL, Postgres

Business Rules

Database Access Requirement Business Rules feature uses database to persist the derived business rules
Required/Optional Mandatory
Default Datasource Name BUSINESS_RULES_DB
Tables BUSINESS_RULES, RULES_TEMPLATES
Schemas and Queries

https://github.com/wso2/carbon-analytics/blob/v2.0.250/components/org.wso2.carbon.business.rules.core/src/main/resources/queries.yaml

Tested Database Types H2, MySQL, Oracle 11g

IdP client

Database Access Requirement IdP client access the DB layer to persist the client id and the client secret of dynamic client registration
Required/Optional Mandatory for external IdP client
Default Datasource Name DB_AUTH_DB
Tables OAUTH_APPS
Schemas and Queries

https://github.com/wso2/carbon-analytics-common/blob/v6.0.52/components/authentication/org.wso2.carbon.analytics.idp.client/src/main/resources/queries.yaml

Tested Database Types H2, MySQL, Oracle 11g

Permission  provider

Database Access Requirement Permission provider will access the DB to p ersist permissions and role - permission mappings.
Required/Optional Mandatory, default is in H2
Default Datasource Name PERMISSIONS_DB
Tables PERMISSIONS, ROLE_PERMISSIONS
Schemas and Queries

https://github.com/wso2/carbon-analytics-common/blob/v6.0.52/components/permission-provider/org.wso2.carbon.analytics.permissions/src/main/resources/queries.yaml

Tested Database Types H2, MySQL, Mssql, Oracle 11g , Postgres
Top