Defining Tables for Physical Stores

This section explains how to define data tables to store data handled by WSO2 Stream Processor in physical databases. The @store annotation syntax for defining these tables differ based on the database type as well as where the properties are defined. 

The store properties(such as URL, username and password) can be defined in the following ways:

• Inline definition: The data store can be defined within the Siddhi application as shown in the example below:

  @Store(type='hbase', hbase.zookeeper.quorum='localhost')
  @primaryKey('name')
  define table SweetProductionTable (name string, amount double);

  This method is not recommended in a production environment because is less secure compared to the other methods.

• As references in the deployment file: In order to do this, the store configuration needs to be defined for the relevant deployment environment in the <SP_HOME>/conf/<PROFILE>/deployment.yamlfile as a ref (i.e., in a separate section siddhi: and subsection refs:) as shown in the example below. 
  

  The database connection is started when a Siddhi application is deployed, and disconnected when the Siddhi application is undeployed. Therefore, this metho is not recommended if the same database is used across multiple Siddhi applications.

   

  siddhi:   
    refs:
      -
         ref:
           name: 'store1'
           type: 'rdbms'
           properties:
             jdbc.url: 'jdbc:h2:./repository/database/ANALYTICS_EVENT_STORE'
             username: 'root'
             password: ${sec:store1.password}
             field.length='currentTime:100'
             jdbc.driver.name: 'org.h2.Driver'

  
  Then you need to refer to that store via the@store annotation as in the Siddhi application as shown in the example below. 

  @Store(ref='store1')
  @PrimaryKey('id')
  @Index('houseId')
  define table SmartHomeTable (id string, value float, property bool, plugId int, householdId int, houseId int, currentTime string);

• Using WSO2 data sources configuration: Once a data source defined in the wso2.datasources section of the file, <SP_HOME>/conf/<PROFILE>/deployment.yaml, the same connection can be used across different Siddhi applications. This is done by specifying the data source to which you need to connect via the @store annotation in the following format.

  @Store(type='<DATABASE_TYPE>', datasource=’<carbon.datasource.name>’)

  The database connection pool is initialized at server startup, and destroyed at server shut down.

  
  

  This is further illustrated by the following example.

  @Store(type='rdbms', datasource=’SweetFactoryDB’)@PrimaryKey("symbol")
  define table FooTable (symbol string, price float, volume long);

  For more information about definig datasources, see Configuring Datasources.

The following database types are currently supported for WSO2 SP.

RDBMS

The RDBMS database types that are currently supported are as follows:

• H2
• MySQL
• Oracle database
• My SQL Server
• PostgreSQL
• IBM DB2

Query syntax

The following is the syntax for an RDBMS event table configuration:

@store(type="rdbms", jdbc.url="<jdbc.url>", username="<username>", password="<password>",pool.properties="<prop1>:<val1>,<prop2>:<val2>")
@PrimaryKey("col1")
@IndexBy("col3")
define table <table_name>(col1 datatype1, col2 datatype2, col3 datatype3);

Parameters

The following parameters are configured in the definition of an RDBMS event table.

In addition to the above parameters, you can add the @primary and @index annotations in the RDBMS table configuration.

• @primary : This specifies a list of comma-separated values to be treated as unique fields in the table. Each record in the table must have a unique combination of values for the fields specified here.
• @index : This specifies the fields that must be indexed at the database level. You can specify multiple values as a come-separated list.

Example

The following is an example of an RDBMS table definition:

@Store(type="rdbms", jdbc.url="jdbc:h2:repository/database/ANALYTICS_EVENT_STORE", username="root", password="root",field.length="symbol:254")
@PrimaryKey("symbol")
define table FooTable (symbol string, price float, volume long);

Apache HBase

Query syntax

The query syntax to define an HBase table is as follows.

@Store(type="hbase", any.hbase.property="<STRING>", table.name="<STRING>", column.family.name="<STRING>")
@PrimaryKey("PRIMARY_KEY")
@Index("INDEX")

Parameters

The following parameters are configured in the definition of an HBase event table:

In addition, the following annotations are used in the HBase definition.

• @primary: This specifies a list of comma-separated values to be treated as unique fields in the table. Each record in the table must have a unique combination of values for the fields specified here. Primary keys allow you to fetch records in a table by providing a unique reference for each record. Therefore, if you do not include one or more primary keys in the table definition, it is not possible to perform table operations such as searching, updating, deleting and joining. For more information about table operations, see Managing Stored Data via Streams and Managing Stored Data via REST APIs.
• @index: This specifies the fields that must be indexed at the database level. You can specify multiple values as a comma separated list.

Example

@Store(type=”hbase”, table.name=”SomeTestTable”, column.family.name=”SomeCF”, hbase.zookeeper.quorum=”localhost”, hbase.zookeeper.property.clientPort=”2181”)
@PrimaryKey(symbol)
define table FooTable (symbol string, price float, volume long);

Apache Solr

Query syntax

The query syntax to define an SOLR table is as follows.

@PrimaryKey("id")
@store(type=“solr”, url=<solr-cloud-zookeeper-url>, collection=<solr-collection-name>, base.config=<config-name>, shards=<no-of-shards>, replicas=<no-of-replicas>, schema=<schema-definition>, commit.async=true|false)
define table Footable (time long, date string);

Parameters

The following parameters are configured in an SOLR table definition.

Example

This query defines an SOLR table named FooTable in which a schema that consists of the two attributes time (of long type) and date (of the string type) is maintained. The values for both attributes are stored.

@store(type='solr', url='localhost:9983', collection='TEST1', base.config='gettingstarted', " +
"shards='2', replicas='2', schema='time long stored, date string stored', commit.async='true') " +
"define table Footable(time long, date string);

MongoDB

Query syntax

The following is the query syntax to define a MongoDB event table.

@Store(type="mongodb", mongodb.uri="<MONGODB CONNECTION URI>")
@PrimaryKey("ATTRIBUTE_NAME")
@IndexBy("<ATTRIBUTE_NAME> <SORTING ORDER> <INDEX OPTIONS>")
define table <TABLE_NME> (<ATTRIBUTE1_NAME> <ATTRIBUTE1_TYPE>, <ATTRIBUTE2_NAME> <ATTRIBUTE2_TYPE>, <ATTRIBUTE3_NAME> <ATTRIBUTE3_TYPE>, ...);

The mongodb.uri parameter specifies the URI via which MongoDB user store is accessed.

In addition, the following annotations are used in the MongoDB definition.

• @primary : This specifies a list of comma-separated values to be treated as unique fields in the table. Each record in the table must have a unique combination of values for the fields specified here.
• @index : This specifies the fields that must be indexed at the database level. You can specify multiple values as a comma separated list.

Example

The following query defines a MongoDB table named FooTable with the symbol, price, and volume attributes. The symbol attribute is considered the primary key and it is also indexed.

@Store(type="mongodb", mongodb.uri="mongodb://admin:admin@localhost:27017/Foo?ssl=true")
@PrimaryKey("symbol")
@IndexBy("symbol 1 {background:true}")
define table FooTable (symbol string, price float, volume long);