Apache Drill as JDBC source - Error creating dataset

Downloaded latest BIRT. Tried to create a JDBC source. Tested connection successfully. Next when tried to create data set for query SELECT * FROM cp.`employee.json`.. the designer does not allow me to save or finish. 
Anybody used Apache Drill with BIRT before?

Find more posts tagged with

Comments

san_gan

Clement Wong

In the commercial BIRT Designer, I attempted to use the Apache Drill JDBC with the MapR Sandbox, and was able to see the same behavior. 
 
With the following URL ("jdbc:drill:zk=maprdemo:5181/drill/demo_mapr_com-drillbits"), I was able to Test Connection successfully, but was not able to continue with Finish or Next.
 
After attempting to click on the Finish button, I saw the following in the Eclipse error log :
<pre class="_prettyXprint">
java.lang.NullPointerException
 at org.apache.drill.jdbc.impl.DrillResultSetMetaDataImpl.throwIfClosed(DrillResultSetMetaDataImpl.java:53)
 at org.apache.drill.jdbc.impl.DrillResultSetMetaDataImpl.getColumnCount(DrillResultSetMetaDataImpl.java:86)
 at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingDefaultPolicy(Statement.java:361)
 at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:330)
 at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56)
 at org.eclipse.birt.report.data.oda.jdbc.ui.editors.MetaDataRetriever.<init>(MetaDataRetriever.java:78)
 at org.eclipse.birt.report.data.oda.jdbc.ui.editors.SQLDataSetEditorPage.collectDataSetDesign(SQLDataSetEditorPage.java:715)
 at org.eclipse.datatools.connectivity.oda.design.internal.ui.DataSetWizardPageCore.finishDataSetDesign(DataSetWizardPageCore.java:395)
 at org.eclipse.datatools.connectivity.oda.design.internal.ui.DataSetWizardBase.collectDataSetDesignFromPage(DataSetWizardBase.java:365)
 at org.eclipse.datatools.connectivity.oda.design.internal.ui.DataSetWizardBase.finishDataSetDesign(DataSetWizardBase.java:387)
 at org.eclipse.datatools.connectivity.oda.design.internal.ui.DataSetWizardBase.performFinish(DataSetWizardBase.java:99)
 at org.eclipse.jface.wizard.WizardDialog.finishPressed(WizardDialog.java:827)
 at org.eclipse.jface.wizard.WizardDialog.buttonPressed(WizardDialog.java:432)
 at org.eclipse.jface.dialogs.Dialog$2.widgetSelected(Dialog.java:624)
 at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:248)
 at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
 at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
 at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4169)
 at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3758)
 at org.eclipse.jface.window.Window.runEventLoop(Window.java:825)
 at org.eclipse.jface.window.Window.open(Window.java:801)
 at org.eclipse.birt.report.designer.data.ui.actions.NewDataSetAction.createNewDataSet(NewDataSetAction.java:194)
 at org.eclipse.birt.report.designer.data.ui.actions.NewDataSetAction.run(NewDataSetAction.java:182)
 at org.eclipse.jface.action.Action.runWithEvent(Action.java:498)
 at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:584)
 at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:501)
 at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:411)
 at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
 at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
 at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4169)
 at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3758)
 at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$9.run(PartRenderingEngine.java:1053)
 at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
 at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:942)
 at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:86)
 at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:588)
 at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
 at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:543)
 at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
 at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:124)
 at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
 at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
 at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
 at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:353)
 at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:180)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:629)
 at org.eclipse.equinox.launcher.Main.basicRun(Main.java:584)
 at org.eclipse.equinox.launcher.Main.run(Main.java:1438)</pre>
I'll have to dig into see what is causing this, and will most likely need to file a Bugzilla on it.

san_gan

Thanks Clement..will wait for your reply.. 
Please note the leading competitors such as Microstrategy,Tableau,Qlik have successfully integrated with Apache Drill. This is documented in the Apache Drill web site. Will be definitely an advantage for BIRT / iHub to have this fixed

Clement Wong

In the process of troubleshooting, I tried with Apache Drill JDBC 1.0.0 (downloaded from <a data-ipb='nomediaparse' href='https://archive.apache.org/dist/drill/drill-1.0.0),'>https://archive.apache.org/dist/drill/drill-1.0.0),</a>and I was able to successfully get past the first dialog - "Query". The meta data is returned correctly, and Preview Results work as expected.
 
In the brief tests, I tried with various test queries such as "SELECT * FROM cp.`employee.json` LIMIT 20" and "SELECT * FROM hive.`default`.orders LIMIT 5", and was able to generate reports.
 
So somewhere between Apache Drill JDBC versions 1.0.0 and 1.6.0, Apache changed the handling of retrieving the meta data result set. Maybe between 1.3.0 (<a data-ipb='nomediaparse' href='https://github.com/apache/drill/tree/1.3.0/exec/jdbc/src/main/java/org/apache/drill/jdbc/impl'>https://github.com/apache/drill/tree/1.3.0/exec/jdbc/src/main/java/org/apache/drill/jdbc/impl</a>) and 1.4.0 (<a>https://github.com/apache/drill/tree/1.4.0/exec/jdbc/src/main/java/org/apache/drill/jdbc/impl), s</a>ince we see DrillResultSetMetaDataImpl introduced in the 1.4.0 branch?
 
The current workaround is to use a lower version of the Apache Drill JDBC.

san_gan

Thanks Clement. Squirrel sql client works with drill version 1.6 
Do you think this meta data API has impacted only Actuate.. if then will you raise a ticket with Apache Drill

Clement Wong

After a few more tests, it's just as I suspected. Version 1.3.0 of the Apache Drill JDBC works (<a data-ipb='nomediaparse' href='https://archive.apache.org/dist/drill/drill-1.3.0/'>https://archive.apache.org/dist/drill/drill-1.3.0/</a>), but version 1.4.0 does not work.
 
Still investigating... In 1.4.0 for DrillJdbc41Factory.java, they removed:
 
import net.hydromatic.avatica.AvaticaPreparedStatement; 
import net.hydromatic.avatica.AvaticaResultSetMetaData;
 
 
And added their own implementation from:
<pre>

@Override
public ResultSetMetaData newResultSetMetaData(AvaticaStatement statement,
List<ColumnMetaData> columnMetaDataList) {
return new AvaticaResultSetMetaData(statement, null, columnMetaDataList);
}</pre>
To:
<pre>

@Override
public ResultSetMetaData newResultSetMetaData(AvaticaStatement statement,
List<ColumnMetaData> columnMetaDataList) {
return new DrillResultSetMetaDataImpl(statement, null, columnMetaDataList);
}</pre>
Trying now to understand why in DrillResultSetMetaDataImpl, they have an override of the getColumnCount method that calls throwIfClosed, and what it's expecting.
<pre>
private void throwIfClosed() throws AlreadyClosedSqlException,
SQLException {
// Statement.isClosed() call is to avoid exception from getResultSet().
if (statement.isClosed()
|| statement.getResultSet().isClosed()) {
throw new AlreadyClosedSqlException(
"ResultSetMetaData's ResultSet is already closed." );
}</pre>

san_gan

Hi Clement
 
Any luck?

san_gan

Hi Clement
I believe I found the issue. If stmt.getMetaData().getColumnCount() is performed prior to executeQuery(), the error occurs. if it is done post then no issues. I checked org.eclipse.birt.report.data.oda.jdbc.Statement.java it is doing prior.
 
Error Scenario:
<div>
<div>stmt.getMetaData().getColumnCount();</div>
<div>ResultSet rs = stmt.executeQuery();</div>
</div>
 
Working:
ResultSet rs = stmt.executeQuery();
<div>stmt.getMetaData().getColumnCount();</div>
<div> </div>
<div>Recommended (This always works with all providers including Drill/ORACLE)</div>
<div>ResultSet rs = stmt.executeQuery();</div>
<div>
<div>if (rs != null) {</div>
<div>nCols = rs.getMetaData().getColumnCount();</div>
<div>}</div>
</div>

Clement Wong

Sorry, I have been out of the office for the past week and a half, and just returned today.
 
One of my colleagues, John Ward, did investigate further while I was away.
 
It appears that Apache Drill JDBC has an incomplete JDBC implementation for meta-data retrieval. Drill doesnâ€™t fully implement a mechanism to get a query's meta-data without actually executing the query. (Might not be the most efficient method.) Most RDMS do through a DESC statement or a EXPLAIN PLAN of some sort. However, since Drill isnâ€™t a RDMS, it doesnâ€™t support a statement like that. The JDBC ODA supports this, expecting a SQLException to be thrown, but Drill doesnâ€™t throw that and instead a custom Exception. If it was throwing a SQLException, we would handle that gracefully in our exception handling.
 
He modified the BIRT ODA JDBC and added a quick check to see if the meta-data type is from Apache Drill, and force it to throw a general exception in the BIRT ODA JDBC plugin. This will force the query to execute and retrieve itâ€™s meta-data that way.
 
Just PM me if you want the link to the modified "oda-jdbc.jar". I've tested it with Apache Drill JDBC v1.6 and BIRT is working as expected.

san_gan

Hi Clement, confirm the fix worked. Tried on drill 1.7