Teiid - Simplifies Data Virtualization

Teiid - Simplifies Data Virtualization

Preface:
This blog contains only theoretical discussion about Teiid, its Use cases and how to solve them. I am writing this blog for the introduction of Teiid and readers to get high level understanding of Teiid project. I'll write another blog on Teiid project in which I will cover up how to solve below mentioned use cases in practical.

What is Data Virtualization?
Before talking about Teiid and it's use cases, Let's take a glance at What it means Data Virtualization? by simply googling it we can get lots of theoretical details, definitions, explanation about Data Virtualization, but How it can be define in technical terms or in terms of usage(w.r.t. teiid)? So here is the quick explanation.
Using Teiid or its API'S we can execute SQL queries having same syntax across the multiple databases no matter whether is it SQL database or NoSQL databases. In SQL database there are various types e.g MySQL, ORACLE, SQLServer etc..  and this list can contain another few more databases, in the range of NoSQL databases mongoDB, Cacendra etc.. are there. No matter what is your database, their technology. You can use same syntax across the databases without worrying about its structure and technology. Teiid uses ANSI SQL syntax to provider seamless accesses to databases.
List is not only limited for SQL or NoSQL databases but Teiid does support flat files, web service etc.. for now we only focus SQL databases.

Teiid Architecture:


For demonstrating the above statements lets take an example
To insert date field in oracle database table a typical query look like
INSERT INTO table_name (date_field) values (TO_DATE('2017/05/03', 'yyyy/mm/dd'));

Simlar for mysql it is
INSERT INTO table_name (date_field) VALUE ('2017/05/03');

A major difference we can observer here is TO_DATE function, it is  absent in case of MySQL query.

So question comes here is How will be a Teiid query?
Answer is : INSERT INTO table_name (date_field) VALUE ('2017/05/03');

it is same as MySQL query but its an ANSI SQL which is adopted by MySQL. ORACLE database has its own syntax.
So As I stated before we can use this query for ORACLE as well, Off-Course we need help of Teiid to do the same.

Teiid documentation covers lot more about these type of details, It is just a small introduction from my end about Teiid.

Let us talk about some use cases and How to solve it using Teiid.

Use Case # 1:
Design a module which will create and inserts data in multiple databases. database platform can be heterogeneous.
Solution: Use Teiid data virtualization platform, so that a module/code contains uniform query syntax for heterogeneous databases as explained above.

Use Case # 2:
Connect to multiple databases homogeneous or heterogeneous at a same type which also supports transaction management.
Solution: This feature is not the part of Teiid project but we can achieve it through using JBoss WildFly Server XA data-source. Support for an XA data-source is really depends on the database technology we are using e.g. MySQL and Oracle supports XA data-source whereas Netezza doesn't.

Use Case # 3:
Migrate/Copy data from one database technology to other, take an example where user needs to copy data from Oracle database to MySQL database or vice versa.
Solution: Now we all are familiar with XA data-source(at least in theory), we can solve this use case with the help of XA data-source. Let's go step by step.

1. Create two XA data-source one for MySQL and another one for Oracle.
2. Deploy VDB each for MySQL database and Oracle database, here one question might crossed your mind that What is  VDB? Let's keep it simple for now VDB is a virtual database i.e. we have a two physical databases which resides on MySQL server and Oracle server when using Teiid we are not going to hit (rather we don't need to) those physical databases, we need to use VDBs instead. Still not getting???
In simple term VDB is a logical copy of physical database which resides on our Teiid server, that's it... And reference of physical database it imports from the data-source. More information about VDB is @ http://teiid.jboss.org/basics/virtualdatabases/
3. Copy data using query :

INSERT INTO oracleConn.oracleSID.table2
SELECT * FROM mysqlConn.mySQLDB.table1

here I am assuming table structure of table2 and table1 are same columns datatypes are compatible. table2 can be from Oracle and table1 from MySQL database. What about datatype conversion? Don't worry Teiid will takes of it. orcaleConn and mysqlConn that we will cover later.

Simple right?  Here we have saved considerable amount of time and efforts. Otherwise we have to dump data of source table somewhere in file then again we need to upload the file to the target table, for that we may need to use certain licensed tools or command that import data from file to database usually such command we don't remember easily.

Use Case # 4:
Write a function such that while retrieving data it should be transformed to some other data.
Solution: Let's understand the problem first, it asks user to write a function that takes an input data transforms it and returns transformed data. So e.g if data contains ABC string then replace ABC by XYZ.

If we think to write a function or procedure at database level then it may work for that particular database where it is written or installed but for other database we need to write same function with different syntax. Here Teiid provides an awesome solution, just write a method in java which takes an argument String check if it contains ABC and replace ABC with XYZ and return the output. Knowledge of writing procedure or function is not required.


Wrap up:
These are few use cases that I came across while development. We can see some examples of it practically i.e. we solve these use cases with Code in next blog post.

Thanks,
Kulbhushan Chaskar

Comments

Post a Comment

Popular posts from this blog

What is jquery Deferred and Promise?