Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How to export data from Oozie Derby database?

avatar

I'm looking to generate reports on workflow performance in Oozie: failures, durations, users, etc... What is the best way to do so if Oozie is currently using DerbyDB and not MySQL? (Prior to moving Oozie to MySQL.)

I stopped the Oozie service and used the Phoenix sqlline tool:

su - oozie -c "/usr/hdp/current/oozie-server/bin/oozie-stop.sh"
java -cp .:/usr/hdp/2.3.2.0-2950/oozie/libserver/derby-10.10.1.1.jar:/usr/hdp/2.3.2.0-2950/phoenix/bin/../phoenix-4.4.0.2.3.2.0-2950-thin-client.jar sqlline.SqlLine -d org.apache.derby.jdbc.EmbeddedDriver -u jdbc:derby:/hadoop/oozie/data/oozie-db -n none -p none --color=true --fastConnect=false --verbose=true --isolation=TRANSACTION_READ_COMMITTED

0: jdbc:derby:/hadoop/oozie/data/oozie-db> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('OOZIE','WF_ACTIONS','WF_ACTIONS.del',null,null,null);
0: jdbc:derby:/hadoop/oozie/data/oozie-db> !outputformat vertical
0: jdbc:derby:/hadoop/oozie/data/oozie-db> !tables
0: jdbc:derby:/hadoop/oozie/data/oozie-db> SELECT STATUS, WF_ID, TYPE, NAME, EXECUTION_PATH, ERROR_MESSAGE FROM OOZIE.WF_ACTIONS;
1 ACCEPTED SOLUTION

avatar
Master Mentor

@Vladimir Zlatkin here's one idea Link. Here are tools in their official page Link derby wiki has a full list Link

View solution in original post

2 REPLIES 2

avatar
Master Mentor

@Vladimir Zlatkin here's one idea Link. Here are tools in their official page Link derby wiki has a full list Link

avatar
Master Guru

Since Oozie is stopped you can use ij, Derby interactive sql tool.