Teiid Designer File Connector Example Project

The following instructions provide a tutorial for creating and testing a VDB designed to query data from a text file.

Background

Teiid as JDBC source
Project Contents

Step 1- Create Source Model
Step 2 - Create Connection Profile

Step 3 - Set Source Connection Profile
Step 4 - Create Teiid Instance
Step 5 - Test Source Model
Step 6 - Create View Model
Step 7 - Test View Model
Step 8 - Create Data Source
Step 9 - Create VDB
Step 10 - Test VDB

Teiid as JDBC Source

Teiid provides a mechanism to expose your data via a Virtual DataBase (VDB) through a JDBC connection where user queries can be written against this VDB using all the sources defined as if they are in single source.

Teiid Designer is the tool used to create/edit/manage your VDBs and the models contained in them. Building your models and VDBs will probably be an interative process and Teiid Designer allows you to perform tests to insure the data access functionality is performing to your expectations.

You can test your data by using either either the following:

Preview Data feature - which does not require you to create and deploy a VDB

Execute VDB feature - which will represent the artifact you will eventually deploy and use to expose your data in your real environment.

Project Contents

This project contains 2 folders (data and readme)s:

The data folder contains 1 data file. This folder will be used as your data source schema folder which will be described later. The "EmployeeData.txt" is a data file containing CSV formatted ficticious data. This file will be your Database.

The readme folder, of course, contains the html backing this page.

STEP 1 - Create Source Model

Text file connections are treated differently than JDBC connections and require a simplified source model containing specific relational procedures.

These procedures, documented in the Teiid Reference guide, can be created either by hand or using an option in the New Model Wizard.

Create File Translator Source Model

Select "New > Teiid Metadata Model" action
Enter "EmployeeData" as name.
Ensure "Relational" Model Class and "Source" Model Type are selected
Choose the "Generate File Translator Procedures" option and click "Next>"
Choose "Finish"

New relational model, EmployeeData.xmi, is created containing 3 relational procedures. This example will utilize the getTextFiles() procedure.

STEP 2 - Create Connection Profile

Ultimately, your data source model will need to be backed by actual data so it can be queried through Data Preview or through a JDBC connection to your deployed VDB or Virtual Data Base.

To set up a connection profile targeted to

Create Connection Profile to Text File

Open the "Database Development" perspective
In the Data Source Explorer, select a "New Connection Profile" button in toolbar and select the "Flat File Data Source" type. Provide unique name.

You can also select "Flat File Data Source" folder under ODA Data Sources and right-click select "New...". Provide unique name.

Select a Connection Profile Type, enter a valid name (i.e. EmployeeTextConnection for instance)
Click "Next>" and browse/select the "data" folder under this project on your file system.
Test your connection and click Finish to create the connection profile

STEP 3 - Set Source Connection Profiles

In order for Teiid Designer to preview data for a table or procedure, each source model has to be associated with a connection profile.

To can create this association open Teiid Designer perspective, right-click on a source model (i.e. EmployeeData.xmi) in the Model Explorer, select the "Modeling > Set Connection Profile" action and select the EmployeeTextConnection that you created in Step 2.

The results of this action injects Teiid-specific connection properties into your source model. (Note: You can change this association at any time by performing the same action and selecting a different connection profile)

STEP 4 - Create Teiid Instance

The Teiid Designer perspectives a Teiid view which allows for the definition of multiple Teiid server instances and their deployed VDBs and Data Sources. Data Sources are the “JCA-compatible Connection Factory” xml configuration components required for Teiid to create the necessary connections to query data at run-time.

Right-click in Teiid view and select “New Teiid Instance” action (or click the same action on the toolbar)
In the New Teiid Instance dialog provide:

Admin Host: default is localhost"
Admin Port number: default is "31443"

Note: Check your console log during start-up for your exact admin URL values. It should look like: 15:17:51,150 INFO [RUNTIME] Teiid Admin mms://localhost:31443

Admin user name: default is “admin”

Admin password: default is “admin”

JDBC Host: default is "localhost"
JDBC Port number: default is "31000"

Note: Check your console log during start-up for your exact admin URL values. It should look like: 15:17:51,150 INFO [RUNTIME] Teiid JDBC mm://localhost:31000

JDBC user name: default is “user”
JDBC password: default is “user”

Click "Finish"

STEP 5 - Test Source Model

Preview Data

Previewing data within Teiid Designer provides a quick method for verifying your data sources and for validating SQL results defined in your view (i.e. virtual) tables and procedures.

Basically the Preview Data feature is equivalent to deploying a VDB containing your metadata models, connecting to that VDB via a Teiid JDBC Connection Profile and querying a single table or procedure utilizing a canned simple SQL statement. (See below)

The Preview Data action (

) will be enabled if a previewable table, procedure or web-service operation is selected in the Model Explorer or in a diagram.

To preview data for your getTextFiles() procedure

Expand EmployeeData in the Model Explorer and select getTextFiles procedure
Click the Preview Data toolbar button
This procedure has an input parameter which expects the full name of the text file containing the data you wish to query. You'll be prompted to enter "EmployeeData.txt", the name of the text file under your "data" folder.
The query results will be displayed in Data Tools SQL Results view
At this point you've successfully queried your text file but the return type is a CLOB.

Also note when editing Virtual tables in the Transformation Editor, there is a special Preview Virtual Data toolbar button

which allows you to edit/save/test your SQL changes repeatedly without switching to the Model Explorer or having to select the virtual table and right-click select Modeling > Preview Data action.

STEP 6 - Create View Model

You've tested the basic procedure which returns a CLOB but you wish to extract table data from that information. You can accomplish this by performing a more complex query based on the Teiid TEXTTABLE() function. (See Teiid Reference guide)

You can also embed this query in the transformation of a View model as shown below.

Select "New > Teiid Metadata Model" action.
Enter "EmployeeViews" as the model name
Ensure "Relational" Model Class and select "View" Model Type
Select "Finish" to create an empty relational view model.
Select the EmployeeViews.xmi model in the Model Explorer
Right-click and select "New Child > Base Table" action
Name the table "EmployeeInfo"
In the transformation diagram, double-click the transformation (left yellow arrow) to open the transformation editor.
Enter the following SQL and select the "Validate"

SELECT EMP.lastName, EMP.firstName, EMP.middleName, EMP.empId, EMP.department, EMP.annualSalary, EMP.title, EMP.homePhone, EMP.mgrId, EMP.street, EMP.city, EMP.state, EMP.ZipCode FROM
(EXEC EmployeeData.getTextFiles('EmployeeData.txt')) AS f, TEXTTABLE(file COLUMNS lastName string, firstName string, middleName string, empId biginteger, department string, annualSalary double, title string, homePhone string, mgrId biginteger, street string, city string, state string, ZipCode string HEADER ) AS emp

The view will now contain the columns defined in the SQL you just entered.
Save ALL

STEP 7 - Test View Model

Preview Data

To preview data for your view table:

Click the Preview Virtual Data toolbar button in the Tranformation Editor
The tabular query results will also be displayed in Data Tools SQL Results view
At this point you've successfully queried your text file through a virtual layer

STEP 8 - Create Data Source

Each Teiid VDB requires at least one source model. For each source model, additional model properties are required by the Teiid server to identify and connect to the back-end data source. This is accomplished by specifying a JNDI Data Source name in the VDB Editor. In addition, a translator name is required for most data-source types.

By setting the connection profile on each model, you can generate a compatible Teiid Data Source and deploy it to your default Teiid instance. To create the data source:

Select your EmployeeData.xmi source model in the Model Explorer
Right-click select "Modeling > Create Teiid Data Source" action
In the Create Data Source select Finish

Note: The only Teiid related property is the ParentDirectory value you specified in your connection profile during Step 2 above.

Step 9 - Create VDB

To create a VDB, select the EmployeeViews.xmi model in the Model Explorer, right-click select "New > Teiid VDB", enter a unique name (i.e. EmployeeVDB )and click Finish.

The new VDB is opened in a VDB Editor page, the view model is added as well as the dependent (EmployeeData.xmi) source models. In addition the translator name is set to "file" based on source model connection profile information and the JNDI name (EmployeeData) is generated from the model name (as described earlier).

Step 10 - Test VDB

Once your VDB is saved, you can simply select it in your Model Explorer and right-click select "Modeling > Execute VDB" action.

This action will deploy your VDB to your running Teiid instance, open the Data Tools' Database Development perspective, create and connect to a JDBC Teiid connection profile.

From here, you can select the Parts -Teiid Connection, right-click select "Open SQL Scrapbook", enter SQL such as "SELECT * FROM EmployeeInfo Where annualSalary > 75000".

Query results will be displayed in the same SQL Results view as shown in Preview Data example.

See http://www.jboss.org/teiiddesigner for latest on Teiid Designer project.