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)

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