ElasticSearch via sql4es (JDBC)

[Charuta Rajopadhye]

Hi Team,

I am trying to connect to ElasticSearch via Drill. Came across this
, but the work seems to
be in progress. So i tried using JDBC storage plugin for ElasticSearch, and
used sql4es jar  for the same.
But to no avail.
I keep getting the error: Please retry: error (unable to create/ update
storage)
Following is the configuration i used:
{
  "type": "jdbc",
  "driver": "nl.anchormen.sql4es.jdbc.ESDriver",
  "url": "jdbc:sql4es://localhost:9200/48FxIll?cluster.name=elasticsearch",
  "username": "",
  "password": "",
  "enabled": true
}
and a few permutations of the same like for example changing url to just :
jdbc:sql4es://localhost:9200 etc.
I am pretty sure i don't need credentials, for i have not installed the
X-Pack extension for ElasticSearch. But i am unaware if still some default
thing needs to be used.
Can somebody please guide me in this regard?

Thanks,
Charuta

Re: Newbie: Join queries in MySQL.

[Charuta Rajopadhye]

I can try that, thanks a lot!

On Fri, Oct 13, 2017 at 11:06 PM, Kunal Khatua <kkha...@mapr.com> wrote:

> You could try creating simple views (with different column names) on those
> tables. That way, you don’t have to try changing the column names in the
> table itself.
>
> -Original Message-
> From: Charuta Rajopadhye [mailto:charuta...@gmail.com]
> Sent: Thursday, October 12, 2017 11:28 PM
> To: dev@drill.apache.org
> Subject: Re: Newbie: Join queries in MySQL.
>
> Hi Timothy,
>
> Thank you very much for your reply.
>
> Regards,
> Charuta
>
> On Fri, Oct 13, 2017 at 9:53 AM, Timothy Farkas <tfar...@mapr.com> wrote:
>
> > Hi Charuta,
> >
> > This is a known issue that other people have been bumping into as
> > well. As of now there is no work around other than changing the column
> > names in your tables. I've filed these tickets regarding the issue but
> > haven't gotten around to working on them yet:
> >
> > https://issues.apache.org/jira/browse/DRILL-5713
> > https://issues.apache.org/jira/browse/DRILL-4211
> >
> > Maybe someone else has some bandwidth to pick them up?
> >
> > Thanks,
> > Tim
> >
> > 
> > From: Charuta Rajopadhye <charuta...@gmail.com>
> > Sent: Thursday, October 12, 2017 2:16:10 AM
> > To: dev@drill.apache.org
> > Subject: Newbie: Join queries in MySQL.
> >
> > Hi Team,
> >
> >
> > I have created a configuration for JDBC storage plugin in Drill, for a
> > MySQL database.
> >
> > I am trying to fire join query on 2 tables. Both my tables have a
> > column called 'id'/
> >
> > Table defination:
> >
> > CREATE TABLE `simpleone` (
> >
> >  `id` int(11) NOT NULL AUTO_INCREMENT,
> >
> >  `name` text NOT NULL,
> >
> >  PRIMARY KEY (`id`)
> >
> > ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
> >
> > CREATE TABLE `simpletwo` (
> >
> >  `id` int(11) NOT NULL AUTO_INCREMENT,
> >
> >  `city` text NOT NULL,
> >
> >  PRIMARY KEY (`id`)
> >
> > ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
> >
> >
> > because of the redundant ‘id’ column, i get values from one ‘id’
> > column as NULL.
> >
> > I tried using a few permutations to fire my queries:
> >
> > 0: jdbc:drill:zk=local>* select * from
> > **pluginjdbc**.drillTest.simpleone,
> > *
> > *pluginjdbc**.drillTest.simpletwo where simpleone.id
> > <http://simpleone.id> = simpletwo.id <http://simpletwo.id>;*
> >
> > +-+---+---++
> >
> > | id  |   name|  *id0*  |  city  |
> >
> > +-+---+---++
> >
> > | 1   | J. D Salinger | *null*  | New York City  |
> >
> > | 2   | Charlotte Bronte  | *null*  | Thornton   |
> >
> > +-+---+---++
> >
> > 2 rows selected (0.224 seconds)
> >
> >
> > 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id>, tb2.id
> > <http://tb2.id> from pluginjdbc.drillTest.simpleone as tb1,
> > pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> =
> > tb2.id
> > <http://tb2.id>;*
> >
> > +-+---+
> >
> > | id  |  id0  |
> >
> > +-+---+
> >
> > | 1   | null  |
> >
> > | 2   | null  |
> >
> > +-+---+
> >
> > 2 rows selected (0.368 seconds)
> >
> >
> > 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id> as col1, tb2.id
> > <http://tb2.id> as col2 from pluginjdbc.drillTest.simpleone as tb1,
> > pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> =
> > tb2.id
> > <http://tb2.id>;*
> >
> > +---+---+
> >
> > | col1  | col2  |
> >
> > +---+---+
> >
> > | 1 | null  |
> >
> > | 2 | null  |
> >
> > +---+---+
> >
> > 2 rows selected (0.236 seconds)
> >
> >
> > Could somebody please guide me through this? Please let me know if i
> > am missing something.
> >
> > Thanks,
> >
> > Charuta
> >
>

Resolving Maps in RecordReader

[Charuta Rajopadhye]

Hi Team,

I am trying to implement storage plugin for a custom database. It supports
objects, as datatype. As it uses Postgres JDBC driver, i am using the JDBC
storage plugin for reference.
In the RecordReader, for the datatype JAVA_OBJECT, i convert it into a
string and pass it to the VarChar Copier.
The result set has the entire object ( internally a Map ) though, which i
parse, match the column name with the keys in the map and dispay the value
in the respective column.

But when there are columns with same name in the query or AS is used in the
query, drill renames them/suffixes them with ints, in order to identify
them uniquely. In this case, the column name and key in the map differs, i
cannot string match and display the value.
I tried checking the ResultSetMetaData but getColumnLabel and getColumnName
return the same  string.
I also referred the dynamo db storage plugin
, Objects/Maps seem to be
resolved here, but not to much avail.
Can anybody please suggest a way around this?

Thanks,
Charuta

Resolving object type using ObjectVector

[Charuta Rajopadhye]

Hi Team,

I am trying to implement a storage plugin for my database (it supports
Postgres JDBC driver) and can store compound objects.
I am able to parse primary objects in my RecordReader, but for object types:
I create Copier of ObjectVector type, and have overridden it's copy method:

private class JavaObjectCopier extends Copier {

public JavaObjectCopier(int columnIndex, ResultSet result, Mutator mutator)
{

super(columnIndex, result, mutator);

}


@Override

void copy(int index) throws SQLException {

*// this object is of type java.util.HashMap*

*Object object = result.getObject(columnIndex);*

}

The mutator.setSafe method accepts a long value or an object holder,

so i am trying to set my object into the holder's obj field, and passing
the holder to the method.

I also tried calling the set method directly, and other brute-force ways,
but it throws exception:

> Caused by: java.lang.UnsupportedOperationException: ObjectVector does not
> support this
> at 
> org.apache.drill.exec.vector.ObjectVector.makeTransferPair(ObjectVector.java:159)
> ~[vector-1.11.0.jar:1.11.0]
> at org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.
> setupNewSchema(ProjectRecordBatch.java:441) ~[drill-java-exec-1.11.0.jar:
> 1.11.0]



Can anybody please point out if am missing anything?


Thanks,

Charuta

Custom storage plugin for DB supporting PostgreSQL JDBC driver.

[Charuta Rajopadhye]

Hi Team,

I am trying to develop a storage plugin for a database that supports JDBC
PostgreSQL driver.
Directly using the JDBC storage plugin does not work for my cause because,
my database supports dynamic (objects) datatype, that JDBC storage plugin
does not handle.
In order to remedy this, i think the best way would be to extend JDBC
storage plugin. But a few classes like for instance JdbcTable that i need
to extend have package visiblity.
I’m skeptical of the path i choose to undertake, is extending JDBC the best
way to begin with?
Can someone please provide their thoughts in this regard?

Thanks,
Charuta

Re: Custom extension storage plugin development

[Charuta Rajopadhye]

Thank you very much Muhammad and Paul, I'll go through the pcap plugin and
read the docs again.

Regards,
Charuta


On Tue, Aug 29, 2017 at 11:35 PM, Paul Rogers <prog...@mapr.com> wrote:

> Also look at the recently-added pcap format plugin. Seems the developers
> there found a minimum-effort path to doing all the necessary steps. The
> steps are more complex than we’d like, but pcap is as a good example as any
> for how to work your way through those steps.
>
> - Paul
>
> > On Aug 29, 2017, at 4:23 AM, Muhammad Gelbana <m.gelb...@gmail.com>
> wrote:
> >
> > I assume this page
> > <https://github.com/paul-rogers/drill/wiki/Storage-
> Plugin-Runtime-Interface>
> > can give you the overview you are looking for ? May be you can start with
> > asking specific questions.
> >
> > Thanks,
> > Gelbana
> >
> > On Tue, Aug 29, 2017 at 12:26 PM, Charuta Rajopadhye <
> charuta...@gmail.com>
> > wrote:
> >
> >> Hi Team,
> >>
> >> I need to develop an extension storage plugin for my data source.
> >> I have been through this
> >> <https://stackoverflow.com/questions/37557388/how-to-
> >> write-custom-storage-plugin-for-apache-drill/37646421#37646421>
> >> and Apache Drill's documentation <https://drill.apache.org/docs/> but
> did
> >> not find helpful references for my cause.
> >> Thanks to the mailing list, i got hold of a few wiki pages:
> >> https://github.com/paul-rogers/drill/wiki/Storage-Plugin-Configuration
> >> *https://github.com/paul-rogers/drill/wiki/Storage-Plugin-Model
> >> <https://github.com/paul-rogers/drill/wiki/Storage-Plugin-Model>*
> >> and other related pages on the same link.
> >> I have downloaded the drill source code, cross referenced the storage
> >> plugin implementations in there and the information on the wiki pages,
> >> learnt about the interfaces to implement, classes to use, configs to
> set up
> >> etc.
> >> This has made my understanding a smidgen better, but i am still unable
> to
> >> get a clear picture of the workflow.
> >> Can someone please guide me in this regard or provide a few more
> pointers?
> >>
>
>

Custom extension storage plugin development

[Charuta Rajopadhye]

Hi Team,

I need to develop an extension storage plugin for my data source.
I have been through this

and Apache Drill's documentation  but did
not find helpful references for my cause.
Thanks to the mailing list, i got hold of a few wiki pages:
https://github.com/paul-rogers/drill/wiki/Storage-Plugin-Configuration
*https://github.com/paul-rogers/drill/wiki/Storage-Plugin-Model
*
and other related pages on the same link.
I have downloaded the drill source code, cross referenced the storage
plugin implementations in there and the information on the wiki pages,
learnt about the interfaces to implement, classes to use, configs to set up
etc.
This has made my understanding a smidgen better, but i am still unable to
get a clear picture of the workflow.
Can someone please guide me in this regard or provide a few more pointers?

crateDB via Apache Drill

[Charuta Rajopadhye]

Hi,

I am trying to connect to crate database via Apache Drill. Went through
Drill docs, but was unable to find pertinent information.

I could connect to my local RDBMS successfully, using the following steps: (
https://drill.apache.org/docs/plugin-configuration-basics/)

1. Visit http://localhost:8047/storage to create new storage plugin.

2. Enter configuration from
https://drill.apache.org/docs/rdbms-storage-plugin/ for mysql

3. Add mysql connector jar in jars/3rdparty

4. In drill-override.conf, add: drill.exec.sys.store.provider.local.path =
"mysql-connector-java-5.1.37-bin.jar"

Performing the exact same steps for crate does not work.

The configuration i used is:

1.

{

  "type": "jdbc",

  "driver": "io.crate.client.jdbc.CrateDriver",

  "url": "crate://localhost:5432/",

  "enabled": false

}

2. Tried this with

crate-jdbc-1.9.1.jar and crate-jdbc-standalone-2.2.0.jar

Please refer: https://crate.io/docs/clients/jdbc/#crate-jdbc-properties

Please let me know if something is amiss. The error fired is not verbose
enough. ( Please retry: error (unable to create/ update storage) )

Please let me know if i am on the right track or it has to be something
else altogether.


Thanks and Regards,

Charuta Rajopadhye