SQL and OWB - Accelerated Map Construction? Part 2

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.

Comments:

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.

Posted by yandi on August 19, 2007 at 10:37 PM PDT #

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

Posted by David Allan on August 20, 2007 at 03:13 AM PDT #

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

Posted by Steve on July 17, 2008 at 11:13 PM PDT #

Hi David, Another great post. Would the "reverse engineering" mappings from SQL to OWB work for OWB 11.2 as well?

Posted by Mark on April 16, 2010 at 09:17 AM PDT #

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

Posted by David Allan on April 19, 2010 at 02:08 AM PDT #

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.

Posted by kamal on January 24, 2011 at 03:56 PM PST #

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

Posted by David Allan on January 25, 2011 at 12:12 AM PST #

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.

Posted by kamal on February 06, 2011 at 04:03 PM PST #

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 !!!

Posted by guest on June 05, 2011 at 09:28 PM PDT #

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.

Posted by Pierre-Yves Gibello on September 15, 2011 at 01:41 AM PDT #

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

Posted by David on September 15, 2011 at 04:11 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today