Teiid 9.1.2 - Configure file datasource

Here are the steps necessary to create a VDB that reads from a CSV file.

It will skip the first line (header).

Creating VDB

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="MyVDB" version="1">
<description>My VDB</description>

<model name="MyCsvData">
<source name="MyCsv-connector" translator-name="file" connection-jndi-name="java:/dataSourceName"/>
</model>

<model name="My" type="VIRTUAL">
<metadata type="DDL"><![CDATA[

CREATE VIEW my_table (
cns varchar(400),
country_code varchar(400)
)
AS
SELECT A.cns,
A.country_code
FROM (EXEC MyCsvData.getTextFiles('My.csv')) AS f,
TEXTTABLE(f.file COLUMNS cns string,
country_code string DELIMITER ';' SKIP 1) AS A;

]]>
</metadata>
</model>
</vdb>

Configuring WildFly

Here are the steps necessary to create a file datasource in Teiid 9.1.2:

Run the following in the server console (in /bin):

./jboss-cli.sh --connect

and run the following commands:

/subsystem=resource-adapters/resource-adapter=adapterName:add(module=org.jboss.teiid.resource-adapter.file)

/subsystem=resource-adapters/resource-adapter=adapterName/connection-definitions=adapterPoolName:add(jndi-name=java:/dataSourceName, class-name=org.teiid.resource.adapter.file.FileManagedConnectionFactory, enabled=true, use-java-context=true)

/subsystem=resource-adapters/resource-adapter=adapterName/connection-definitions=adapterPoolName/config-properties=ParentDirectory:add(value=/path/to/files/)

/subsystem=resource-adapters/resource-adapter=adapterName/connection-definitions=adapterPoolName/config-properties=AllowParentPaths:add(value=true)

/subsystem=resource-adapters/resource-adapter=adapterName:activate

:reload


Where:

• adapterName - Put the name of the adapter that you want, and that will appear in WildFly console (under resource-adapters);
• adapterPoolName - Name that will appear in WildFly console on the details of the resource-adapter;
• dataSourceName - The name of the datasource that will be publicly used.
• /path/to/files/ - where the files to process are.

Teiid 9.1.2 - Create a Materialized View

Here are the necessary steps to create an External Materialization (in MySQL) with Teiid.

Creating Materialized View (external) on VDB

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="MyVDB" version="1">

<description>My VDB</description>

<model name="MyTableMaterialized" type="PHYSICAL">

<property name="importer.useFullSchemaName" value="false"/>

<property name="query-timeout" value="300000"/>

<source name="MyTableMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/dataSourceName" />

</model>

<model name="MyTable" type="VIRTUAL">

<metadata type="DDL"><![CDATA[

CREATE VIEW my_table (

cns varchar(400),

country_code varchar(400)

)

OPTIONS(

MATERIALIZED 'TRUE',

UPDATABLE 'TRUE',

MATERIALIZED_TABLE 'MyTableMaterialized.my_table_cache',

"teiid_rel:MATVIEW_TTL" 300000,

"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',

"teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',

"teiid_rel:MATVIEW_STATUS_TABLE" 'MyTableMaterialized.status',

"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute MyTableMaterialized.native(''truncate table schema.my_table_cache_staging;'');',

"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute MyTableMaterialized.native(''RENAME TABLE schema.my_table_cache TO schema.my_table_cache_temp, schema.my_table_cache_staging TO schema.my_table_cache, schema.my_table_cache_temp TO schema.my_table_cache_staging'');',

"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM MyTableMaterialized.status WHERE Name=''my_table'' AND schemaname = ''MyTable''',

"teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',

"teiid_rel:MATERIALIZED_STAGE_TABLE" 'MyTableMaterialized.my_table_cache_staging',

"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'

)

AS

SELECT A.cns,

A.country_code

FROM (EXEC MyTableCsvData.getTextFiles('MyTable.csv')) AS f,

TEXTTABLE(f.file COLUMNS cns string,

country_code string) AS A;

]]>

</metadata>

</model>

<translator name="mysql-override" type="mysql5">

<property name="SupportsNativeQueries" value="true"/>

</translator>

</vdb>

Note: It is fundamental that mysql-override be present on VDB, or the solution will not work.

Create tables on MySQL

CREATE TABLE my_table_cache_staging (
    cns VARCHAR(400),
    country_code VARCHAR(400)
);

CREATE TABLE my_table_cache (
    cns VARCHAR(400),
    country_code VARCHAR(400)
);

Configuration on WildFly

Here are the steps necessary to create a database datasource in Teiid 9.1.2 (installed on WildFly 10):

Run the following in the server console (in /bin):

./jboss-cli.sh --connect

and run the following commands:

/subsystem=datasources/data-source=adapterName:add(jndi-name=java:/dataSourceName, enabled=true, use-java-context=true, driver-name="mysql", connection-url="jdbc:mysql://mysql_ip:3306/database;useCursorFetch=true",user-name=user_to_use, password=user_to_use_password)

/subsystem=datasources/data-source=adapterName:enable

:reload

Where:

• adapterName - Put the name of the adapter that you want, and that will appear in WildFly console (under resource-adapters);
• dataSourceName - The name of the datasource that will be publicly used;
• database - Database (schema) to connect to

PS: It is assumed that mysql driver is already installed in server (if not see how: http://javari.blogspot.pt/2017/01/add-mysql-driver-to-wildfly-10.html)

Teiid 9.1.2 - Create an UDAF (Teiid User Defined Aggregate Support)

Here are the steps necessary to create an UDAF in Teiid 9.1.2.

Create UDAF

pom.xml

Add this to pom (probably you have to add the jar yourself to your repository. Look for the jar in /modules/system/layers/dv/org/jboss/teiid/api/main/teiid-api-9.1.2.jar. See http://javari.blogspot.pt/2009/10/instalar-um-jar-no-repositorio-maven.html):

<dependencies>
    <dependency>
        <groupId>org.jboss.teiid</groupId>
        <artifactId>teiid-api</artifactId>
        <version>9.1.2</version>
    </dependency>
</dependencies>

Implement UDAF

package my.udaf;

import org.teiid.CommandContext;
import org.teiid.UserDefinedAggregate;


public class MyAggregateFunction implements UserDefinedAggregate{


  public MyAggregateFunction(){

    reset();
  }

  public void addInput(Long val, char separator){

    // Use val (in this case Long) in someway. Example: add it to a list.
  }

  @Override  public void reset(){

    group = new ArrayList<>();
    separator = null;
  }

  @Override  public String getResult(CommandContext commandContext){
    

    // do your work. Use all inputs and return them as you want.
  }
}

Create Artifact

mvn clean install

Create Module

Here are the steps necessary to create a module in Teiid 9.1.2:

Run the following in the server console (in /bin):

./jboss-cli.sh --connect

and run the following commands:

module add --name=module_name --resources=udaf-1.0.jar --dependencies=org.jboss.teiid.api

:reload

Where:

• module_name  - Name of th module. We will use this name in vdb's;
• udaf-1.0.jar - Jar being added
• org.jboss.teiid.api - The udaf jar depends of this module.

Use UDAF in a VDB

<vdb name="MyVdb" version="1">
<description>My VDB</description>


<property name ="lib" value ="module_name "></property>

           <model name="MyAggregateFunction" type="VIRTUAL">
         <metadata type="DDL"><![CDATA[

CREATE VIRTUAL FUNCTION MyAggregateFunction(val long, separator char) RETURNS string OPTIONS (JAVA_CLASS 'my.udaf.MyAggregateFunction', JAVA_METHOD 'addInput', AGGREGATE 'true', "NULL-ON-NULL" 'true', "ALLOWS-DISTINCT" 'true');

]]>
</metadata>
    </model>

   

   

...

<model name="CountryServiceList" type="VIRTUAL"> <metadata type="DDL"><![CDATA[ CREATE VIEW my_view ( column_1 varchar(10), column_2 varchar(10), column_3 varchar(100), ) AS SELECT MyAggregateFunction(DISTINCT column_1, ',') as column_1, column_2 , column_3 FROM my_table GROUP BY column_1 , column_2 ]]> </metadata> </model>

Teiid 9.1.2 Server - Increase Memory

To increase the start and maximum memory that can be used by a Teiid Server (9.1.2) do the following:

1. vi /bin/standalone.conf
2. Search for JAVA_OPTS. Add "-Xms2g -Xmx2g" to the beginning . You should have after editing something like this: JAVA_OPTS="-Xms2g -Xmx2g -XX:MetaspaceSize=128M -XX:MaxMetaspaceSize=512m -Djava.net.preferIPv4Stack=true"

Install Teiid Designer 11 on Eclipse Neon

Install

Here are the steps to install Teiid Designer 11 on Eclipse Neon

1. Download eclipse NEON (Java EE. Eclipse for Java Developer will not work!)
2. Install into Eclipse 4.6 (Neon)
1. Launch the Help > Install New Software action
2. Add... the following update site URLs but DO NOT install them. Various dependent plugins required for Teiid Designer will be installed as needed. Make sure “Contact all update sites during install to find required software” is checked.
   1. http://download.jboss.org/jbosstools/targetplatforms/jbtistarget/4.4.1.CR1/jbtis/REPO
   2. http://download.jboss.org/jbosstools/neon/stable/updates/
3. Install Teiid Designer 11.0 via update site:
   1. http://download.jboss.org/jbosstools/updates/release/neon/integration-stack/teiiddesigner/11.0.0.Final/
4. Click Install button and continue through restarting Eclipse

PS: When configuring a server, before testing the connections don't forget to START the server first!

Install Teiid 9.1.2 on CentOs 7

Install Teiid 9.1.2 on Cent Os 7

Here are the steps necessary to install Teiid 9.1.2 Server on CentOs 7:

1. Download Teiid 9.1.2
2. Extract the zip file to some directory (that will be know from now on as )
3. Go to
4. Run ./add-user.sh
5. Add one user for managment and one user for application
6. Edit standalone.sh and add (replace the SERVER_OPTS by the following line)
1. SERVER_OPTS="-b 0.0.0.0 -bmanagement 0.0.0.0"
2. This will allow access to both frontend and backend from outside the machine where Teiid is installed. If you do not what that management be accessed from outside, then remove the "-bmanagement 0.0.0.0" part.
7. Configure firewall (on a shell write the following to open Teiid default ports to outside).
1. firewall-cmd --permanent --zone=public --add-port 9990/tcp
2. firewall-cmd --permanent --zone=public --add-port 8080/tcp
3. firewall-cmd --permanent --zone=public --add-port 31000/tcp
4. firewall-cmd --reload

Start Teiid

cd /bin 

./standalone.sh -c=standalone-teiid.xml

URLs

Teiid will be available at:

http://127.0.0.1:8080

http://127.0.0.1:9990/console

Add mysql driver to WildFly 10

Take the following steps to install mysql driver on WildFly 10:


1) Copy the jar into ${WILDFLY_HOME}/bin directory

2) Execute ./jboss-cli.sh -- connect

4) Write the following on the shell (adapt to your needs):

module add -–name=com.mysql –-resources=mysql-connector-java-5.1.40.jar –-dependencies=javax.api,javax.transaction.api


5)  Write the following on the shell

/subsystem=datasources/jdbc-driver=mysql:add(driver-name="mysql",driver-module-name="com.mysql",driver-class-name=com.mysql.cj.jdbc.Driver)


6) Write:

:reload