#335: reduce DataBuilder dependencies to i2b2 platform: ant, JVM, JDBC, jboss
--------------------------+------------------------
Reporter: dconnolly | Owner:
Type: enhancement | Status: closed
Priority: major | Milestone: gpc2sites
Component: data-sharing | Resolution: fixed
Keywords: | Blocked By:
Blocking: |
--------------------------+------------------------
Comment (by gkowalski):
Nope , "KEY" being a reserved word is not the issue. Defining a variable
in the select and using it in the select was the issue. Changed the
~/sql-ora/variable_extract.sql to read this works :
{{{
4 select QUERY_INSTANCE_ID job_id
5 , v.result_instance_id variable_id
6 , v.description variable
7 , v.path
8 , substr(v.path, instr(v.path, '\', 3)) path1
9 from qt_query_result_instance v
10 where v.QUERY_INSTANCE_ID = ${job_id}
11 order by result_instance_id
12 ;
}}}
produces a variable.csv file when run now.
Running it again and it gets further :
{{{
./run_mcw.sh
Buildfile: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/build.xml
builder-job.run:
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/job_extract.sql
[sql] 1 of 1 SQL statements executed successfully
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/variable_extract.sql
[sql] 1 of 1 SQL statements executed successfully
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/patient_dimension_extract.sql
[sql] 1 of 1 SQL statements executed successfully
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/concept_dimension_extract.sql
[sql] Failed to execute: with v as ( select substr(key,
instr(v.path, '\', 3)) variable_path from qt_query_result_instance v where
v.QUERY_INSTANCE_ID = 16833 ) select cd.* from v join concept_dimension cd
on cd.concept_path like (v.variable_path || '%')
BUILD FAILED
/Users/gkowalsk/Projects/seine-minibuilder-0.5-f08eb078cb97/build-tasks
/builder-job.xml:70: The following error occurred while executing this
line:
/Users/gkowalsk/Projects/seine-minibuilder-0.5-f08eb078cb97/build-tasks
/sql-script.xml:46: java.sql.SQLSyntaxErrorException: ORA-00904: "KEY":
invalid identifier
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
at
oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at
oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)
at
oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)
at
oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
at org.apache.tools.ant.taskdefs.SQLExec.execSQL(SQLExec.java:773)
at
org.apache.tools.ant.taskdefs.SQLExec.runStatements(SQLExec.java:743)
at
org.apache.tools.ant.taskdefs.SQLExec$Transaction.runTransaction(SQLExec.java:1053)
at
org.apache.tools.ant.taskdefs.SQLExec$Transaction.access$000(SQLExec.java:983)
at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:651)
at
org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:292)
at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at
org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
at org.apache.tools.ant.Task.perform(Task.java:348)
at org.apache.tools.ant.Target.execute(Target.java:435)
at org.apache.tools.ant.Target.performTasks(Target.java:456)
at
org.apache.tools.ant.Project.executeSortedTargets(Project.java:1393)
at
org.apache.tools.ant.helper.SingleCheckExecutor.executeTargets(SingleCheckExecutor.java:38)
at org.apache.tools.ant.Project.executeTargets(Project.java:1248)
at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:440)
at
org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:105)
at
org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:292)
at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at
org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
at org.apache.tools.ant.Task.perform(Task.java:348)
at org.apache.tools.ant.Target.execute(Target.java:435)
at org.apache.tools.ant.Target.performTasks(Target.java:456)
at
org.apache.tools.ant.Project.executeSortedTargets(Project.java:1393)
at org.apache.tools.ant.Project.executeTarget(Project.java:1364)
at
org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
at org.apache.tools.ant.Project.executeTargets(Project.java:1248)
at org.apache.tools.ant.Main.runBuild(Main.java:851)
at org.apache.tools.ant.Main.startAnt(Main.java:235)
at org.apache.tools.ant.launch.Launcher.run(Launcher.java:280)
at org.apache.tools.ant.launch.Launcher.main(Launcher.java:109)
Total time: 2 seconds
}}}
again with the KEY ! This time that's not even defined . So I'm guessing
lets change it to v.path like the last time in
concept_dimension_extract.sql
{{{
4 with v as (
5 -- \\table_cd\f1\f2\leaf\ -> \f1\f2\leaf\
6 select substr(v.path, instr(v.path, '\', 3)) variable_path
7 from
8 qt_query_result_instance v
9 where v.QUERY_INSTANCE_ID = ${job_id}
10 )
11 select cd.*
12 from v
13 join concept_dimension cd on cd.concept_path like (v.variable_path ||
'%')
14 ;
}}}
OK , got by that and the next error on KEY again :
{{{
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/observation_fact_extract.sql
[sql] Failed to execute: with job as ( select
job.QUERY_INSTANCE_ID job_id , job.patient_set , job.date_lo , job.date_hi
from qt_query_instance job where job.QUERY_INSTANCE_ID = 16833 ), v as (
select v.result_instance_id variable_id , v.description variable ,
substr(key, instr(v.path, '\', 3)) variable_path from
qt_query_result_instance v, job where v.QUERY_INSTANCE_ID = job.job_id ),
pat as ( select pset.patient_num from job join qt_patient_set_collection
pset on pset.result_instance_id = job.patient_set ), cd as ( select
distinct concept_cd from job cross join v join concept_dimension cd on
cd.concept_path like (v.variable_path || '%') ) select obs.* from job
cross join observation_fact obs join pat on pat.patient_num =
obs.patient_num join cd on cd.concept_cd = obs.concept_cd where
(job.date_lo is null or obs.start_date >= job.date_lo) and (job.date_hi is
null or obs.start_date <= job.date_hi)
BUILD FAILED
/Users/gkowalsk/Projects/seine-minibuilder-0.5-f08eb078cb97/build-tasks
/builder-job.xml:74: The following error occurred while executing this
line:
/Users/gkowalsk/Projects/seine-minibuilder-0.5-f08eb078cb97/build-tasks
/sql-script.xml:46: java.sql.SQLSyntaxErrorException: ORA-00904: "KEY":
invalid identifier
}}}
same mod to the observation_fact_extract.sql script changing the KEY to
v.path and ...
Finally , works !
{{{
./run_mcw.sh
Buildfile: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/build.xml
builder-job.run:
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/job_extract.sql
[sql] 1 of 1 SQL statements executed successfully
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/variable_extract.sql
[sql] 1 of 1 SQL statements executed successfully
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/patient_dimension_extract.sql
[sql] 1 of 1 SQL statements executed successfully
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/concept_dimension_extract.sql
[sql] 1 of 1 SQL statements executed successfully
sql-script.run:
[echo]
[echo] running as FH_CDWDEMODATA_DEV in
jdbc:oracle:thin:@fhoradev.ctsi.mcw.edu:1521:cdwdev1 ...
[echo]
[sql] Executing resource: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/sql-ora/observation_fact_extract.sql
[sql] 1 of 1 SQL statements executed successfully
[copy] Copying 3 files to /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/mcwOutput
[zip] Building zip: /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/mcwOutput.zip
[delete] Deleting directory /Users/gkowalsk/Projects/seine-
minibuilder-0.5-f08eb078cb97/mcwOutput
BUILD SUCCESSFUL
Total time: 23 seconds
CTSI-iMac-gkowalski:seine-minibuilder-0.5-f08eb078cb97 gkowalsk$
}}}
--
Ticket URL:
<http://informatics.gpcnetwork.org/trac/Project/ticket/335#comment:38>
gpc-informatics <http://informatics.gpcnetwork.org/>
Greater Plains Network - Informatics
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
