X

Welcome to All Things Warehouse Builder

  • ETL
    August 14, 2007

SQL and OWB - Accelerated Map Construction? Part 2

David Allan
Architect

Regarding reverse engineering OWB maps from SQL (see earlier post here), I've uploaded the expert as stands, it is a demo remember with no support contract:) It will be useful for people to see a range of OMB related to map construction as well as illustrating some of the SQL to OWB potential. Some of the demo SQL below requires the OWB 10.2.0.3 patch since there were improvements and fixes in the aggregator operator that allow you to use the CUBE/ROLLUP functions and more.

Installation Notes:
To run the expert you'll have to do the following steps;

  1. import MDL (build_mapping_from_sql.mdl)

    into your repository. This will create a global expert in Global

    Explorer under Public Experts/SQL_ACCELERATORS/BUILD_MAP_FROM_SQL
  2. Download the SQL parser from

    here. There is a little bit of work here...unzip the archive,

    change into the Zql/classes directory. Create a java archive of the Zql

    classes for the expert  by executing the following (on Windows) 'jar cvf

    zql.jar Zql\*.class' or (on UNIX the following) 'jar cvf zql.jar Zql/*.class' You must have a JDK bin  directory on your

    path (OWB software home has one under the oracle home under jdk/bin).

    ZQL claims 'Zql is not a commercial product: feel free to use it, but we

    provide no warranty.'. For this demo it was really useful for illustrating these capabilities, the API is simple to use and provided an easy way to extend the function support.
  3. Copy the JAR file (zql.jar)  to your OWB client install under

    owb/lib/ext    You will have to restart your OWB client.
  4. In the main tree add the expert as a shortcut to a map. Right click

    map, select Add/Remove experts enable check box for build map from SQL,

    click OK. The expert should now appear on the menu.


Notes:


You are now ready to go. You can use any tables and SQL you have. Here are some adhoc notes:
  • SQL Terminator: SQL must be terminated by a semi-colon (;)
  • Name Resolution: Names are resolved either

    from the context in which the map is being created or if the table

    name is prefixed with the schema the table must exist in an OWB module

    with that name. So if I create a map for select ENAME from MYSCOTT.EMP;

    then the table EMP will be bound to the table EMP in OWB Oracle module

    MYSCOTT.
  • Function support is restricted to ANSI SQL and Oracle functions, if you have custom functions you will have to add an entry in the expert - see the tcl function addOracleFunctions (in the expert code) for examples, this is in the procedure declaration property of the expert.
  • Some Limitations:


  1. INSERT and SELECT statements only..on tables (for now, it is a

    demo).
  2. ANSI SQL so no Oracle specific syntax supported (join syntax etc..)
  3. Limited subquery support

  4. The Oracle function library has been added so it can be parsed,

    but not all functions tested.
  5. Literals must be named (ie select 1 a, 'hello' b from dual; will

    work,

    select 1 from dual will fail)
  6. Datatype analysis of literals is limited (NUMBER/VARCHAR2

    supported)
  7.  +++ TBD


Demo SQL


Some demonstration SQL that illustrates what has been covered. Import the tables from the sample SH schema into an OWB module named

SH and HR schema into an OWB module named HR.





1. Now run the expert from the mappings node in SH module, enter the

following SQL. If you add a target table and map from EXPR1.OUTGRP1 to

the new tables input group you can generate the OWB code. This example

illustrates the OWB aggregator operator setting up the group by clause

and the output expressions.



SQL:


SELECT
  DECODE(GROUPING(channel_desc), 1, 'Multi-channel sum',

channel_desc) AS Channel,
  DECODE (GROUPING (country_iso_code), 1,

'Multi-country sum', country_iso_code) AS Country,
  TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
FROM sales,

customers, times, channels, countries
WHERE sales.time_id=times.time_id

AND sales.cust_id=customers.cust_id AND customers.country_id =

countries.country_id AND sales.channel_id= channels.channel_id AND

channels.channel_desc IN ('Direct Sales', 'Internet') AND

times.calendar_month_desc= '2000-09' AND country_iso_code IN ('GB',

'US')
GROUP BY CUBE(channel_desc, country_iso_code);

Generated Map:
SQL Example1:



2. This example illustrates using a target table TGT (it will be

defined by the expert) and again it uses aggregation. It depends on the

SCOTT EMP table (and must be run from SCOTT module);



SQL:


INSERT INTO TGT (sal,deptno)
 SELECT sum(sal) salary, deptno deptno
 from

emp p
group by deptno
having comm>1;



Generated Map:

SQL Example2:






3. This example illustrates using the Oracle UPPER function and simple

datatype analysis of inputs;



SQL:


select 'MY_VAR' stringcol, 1 num_col, SYSDATE today, upper(ename) uname
from emp;



Generated Map:

SQL Example3:


4. Set operations are also supported, the example below illustrates a

compound statement with aliases for tables, sorting, deduplication,

filtering (run from SCOTT mappings);



SQL:


INSERT INTO TGT (ename, deptno, code )
SELECT  distinct e.ename en, 5

dn, upper('ggg') x FROM emp e, dual d where e.deptno>1
union
select   v.ename en, 999 dnx, '45000' xx FROM emp v  where

v.deptno>10000
minus
select  p.ename pn,111 fff, '3333' yy from emp p

order by en DESC;



Generated Map:

SQL Example4:


5. Illustrating some Oracle functions let's see XML extractValue (I

added an alias for the column for it to work, it should be run from HR

depending on PURCHASEORDER table);





SELECT extractValue(OBJECT_VALUE,

'/PurchaseOrder/LineItems/LineItem[Part/@Id="715515011020"]/Description')

ev from purchaseorder;





6. Illustrating some more XML (I

changed alias for the column and removed the double quotes around

"Reference" for it to work, it should be run from HR

depending on PURCHASEORDER/EMPLOYEES table);





SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') Reference

FROM purchaseorder, hr.employees e WHERE extractValue(OBJECT_VALUE,

'/PurchaseOrder/User') = e.email AND e.employee_id = 100;







7. An aggregation operator with a subquery (this should be run from HR

mappings node), the subquery is embedded in having expression;



SQL:


SELECT department_id, manager_id
FROM employees
GROUP BY department_id,

manager_id
HAVING (department_id, manager_id) IN   (SELECT

department_id, manager_id FROM employees x WHERE x.department_id =

employees.department_id) ORDER BY department_id;

Generated Map:

SQL Example7:

Summary
The experts and demo SQL provide a real taster for the accelerated map construction from SQL as mentioned this is not a polished product but an example of expert technology that can be a real time saver.

Join the discussion

Comments ( 12 )
  • yandi Monday, August 20, 2007
    it's really a cool stuff, salute to you..
    but when i try i had problem, in build map from sql got error :
    EXP00008: The following error was encountoured when running the expert :
    unknown class "Zql.ZqlParser"
    while executing
    ..
    ..
    ..
    (Procedure "BUILD_MAP_FROM_SQL_main"line 24)
    anybody can help me?
    note:
    in step 2 after extract at my place found *.class not in Zql/classes, but in Zql/classes/Zql
    Query insert:
    select city from temp_area;
    thx before.
  • David Allan Monday, August 20, 2007
    OK So your classes are in Zql/classes/Zql - in step 2 change directory to Zql/classes and do 'jar cvf zql.jar Zql/*.class' to create the archive, then copy the archive to owb/lib/int. Your archive is not good that is why you are getting class not found.
    Cheers
    David
  • Steve Friday, July 18, 2008
    David,
    Is the SQL parser still available for download? I am unable to establish a connection using the link above.
    Thanks in advance,
    Steve
    Hi Steve
    I have updated the blog post with the new url;
    http://www.gibello.com/code/zql/Zql.tar
    Cheers
    David
  • Mark Friday, April 16, 2010
    Hi David,
    Another great post. Would the "reverse engineering" mappings from SQL to OWB work for OWB 11.2 as well?
  • David Allan Monday, April 19, 2010
    Hi Mark
    This should work fine in OWB 11gR2 as well, I have tried it at various points, if you find any issues let me know.
    Cheers
    David
  • kamal Monday, January 24, 2011
    Hi,
    I have tried this but faced prob while creating a map from the expert...dont know what went wrong
    It has trown
    EXP0008: The following error has encountered while executing the expert
    unknown class "zql.zql parser"
    Help me in this regard.
  • David Allan Tuesday, January 25, 2011
    Hi Kamal
    Did you add the code for the ZQL parser in the correct place?
    The post indicates a few steps;
    1. Download the SQL parser.
    2. unzip the archive, change into the Zql/classes directory. Create a java archive of the Zql
    classes for the expert by executing the following (on Windows) 'jar cvf
    zql.jar Zql\*.class' or (on UNIX the following) 'jar cvf zql.jar Zql/*.class'
    You must have a JDK bin directory on your path (OWB software home has one under the oracle home under jdk/bin).
    3. Copy the JAR file (zql.jar) to your OWB client install under owb/lib/ext You will have to restart your OWB client.
    Cheers
    David
  • kamal Monday, February 7, 2011

    Hi David,
    I have tried to create the map from sql after performing all the steps mentioned by you .
    I have placed the created zql.jar file in E:\OraHome_1\owb\lib\ext
    and restarted the system.
    It throws the same error for every attempt and i have no idea where to find the rootcause.
    Pls help me in this regard as this can save my time in creating mappings.
  • guest Monday, June 6, 2011
    Solution and Work Around for java class not found
    1 download zql.jar with "source code" from soureforge then extract it
    (There are many directory after extract file such as classes, doc , parser , src )
    2 check there are many file as following
    2.1 *.java extension under src directory (need javac to compile)
    2.2 *.jj extension under parser directory ( need javacc to compile )
    3 remove the line package org.gibello.zql; from *.java
    list of java file is :
    ZAliasedName.java
    ZConstant.java
    ZDelete.java
    ZExp.java
    ZExpression.java
    ZFromItem.java
    ZGroupBy.java
    ZInsert.java
    ZLockTable.java
    ZOrderBy.java
    ZQuery.java
    ZSelectItem.java
    ZStatement.java
    ZTransactStmt.java
    ZUpdate.java
    ZUtils.java
    ZqlParser.java
    \data\ZEval.java
    \data\ZTuple.java
    4 compile all of java file ( use command like javac *.java ) then you will get you will got *.class extension
    5 find ombplus directory and copy all of *.class extension to ombplus directory
    6 open ombplus and try this command
    set p [java::new ZqlParser]
    the class sould be recognize by ombplus
    OMB+> set p [java::new ZqlParser]
    java0x1
    OMB+>
    ***PS very important
    you may need javacc program to compile file *.jj under directory zql/parser
    issue command like
    javacc ZqlJJParser.jj
    this is to generate some *.java file such
    token.java , parseexception.java, SimpleCharStream.java that is required to compile above Z*.java file
    don't remember to delete the line
    package org.gibello.zql;
    in file token.java , parseexception.java, SimpleCharStream.java
    before compile
    Good Luck !!!
  • Pierre-Yves Gibello Thursday, September 15, 2011

    Sorry Zql has evolved a bit... and may not be retro-compatible with the tooling described here.

    It has moved, as well, to sourceforge (zql.sourceforge.net), and became OSS (GPL license).

    Don't know what OWB is, but it doesn't seem to be open-source ?

    If it is, note the main modification in Zql concerns package refactoring (so it should be quite easy to fix the tool).

    If it is not... well, the comment above ("Posted by guest on June 06, 2011 at 02:28 AM") seems to provide a workaround (= remove package names in Zql code and rebuild the jar...).

    Sorry for the inconvenience - but I'm not committed to long-term supporting non-OSS tools I never heard of before ;)

    Regards,

    Pierre-Yves Gibello - author of Zql.


  • David Thursday, September 15, 2011

    Hi Pierre-Yves

    Ah now I see, there is a bunch of repackaging... this blog post illustrates a sample/demo program that happens to use the java Zql parser. I did this post as a weekend hobby, if I get time I'll pick up the latest Zql...but it's soccer season here in US:-)

    Cheers

    David


  • guest Wednesday, February 11, 2015

    Hi!

    I'm working on a similar project that helps you generate OWB mappings from SQL code.

    I'm looking for beta testers now.

    If you are interested, check out: www.sql2owb.com


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.