Drill logical plan optimization

[Rajkumar Singh]

Hi

I am looking for some measures/params to looked upon to optimize the drill 
logical query plan if i want to resubmit it through the Drill UI, Could you 
please points me some docs so that I can go through it.

Rajkumar Singh
MapR Technologies

Re: what's the differenct between drill and optiq

[Jacques Nadeau]

Andrew,

As others have pointed out there are definitely differences in how each
different community project leverages Calcite (remember, Apache Kylin,
Phoenix and I believe Flink also use it).  Remember, Calcite--at its
core--is a developers toolkit that other applications/systems incorporate.
While an end user could use Calcite, the most common use is as an embedded
library in a broader system.

The great news is that the community is working together collaborate on an
amazing shared library and framework.

-Jacques



On Wed, May 27, 2015 at 10:10 PM, Ted Dunning  wrote:

> Andrew,
>
> Sorry for being cryptic.  Hanifi is more clear.  My point was directed at
> the differences between where Hive may ultimately go and where Drill is
> now.  Hanifi was providing a good summary of where Drill is now.
>
> As he said, Calcite does query parsing and planning.  Ultimately, it will
> do the same for Hive.  Even so, Drill has extended Calcite's planning
> capabilities in ways which are not used by Hive.  These extensions allow
> Calcite to produce plans for the Drill execution engine.  That execution
> engine is what Hanifi meant by flexible distributed columnar execution with
> late binding.
>
> SQL is not normally a late binding language.  Instead, it shows its long
> heritage by being a very statically typed language.  That static typing is
> a problem in the modern world of flexible data and dealing with this
> problem is a key goal of Drill.
>
> The key technological advance in Drill that enables it to address late
> typing problems is something called the ANY type.  This is essentially a
> way for the parser to punt the problem of resolving the type of some value
> until the query is actually running.  At that point, Drill has an empirical
> schema available for each record batch which can be used to do final code
> generation and optimization.  If the empirical schema changes due to
> changes in the data being processed, that code can be regenerated as
> needed.
>
> This is a huge philosophical and design change that is hard to just paste
> onto an existing engine.  Just as it would be next to impossible to modify
> a Pascal or Fortran execution environment to do the type inferencing and
> lazy execution that Scala or Haskell do, it is going to be hard to extend
> Hive's entire execution environment to deal with type dynamism.  Simply
> passing around dynamic types will not give performance anywhere near what
> Drill does because of the inevitable cost of type tag dispatching.
>
> To give just the simplest example, suppose you have data that used a column
> named X to hold an integer for a long while and then switched to using a
> column named Y to hold a floating point number.  To deal with this, you
> might create a view which has a case statement that uses the value of X or
> Y, whichever is non-null.  In conventional SQL engines, the query parser
> and planner would generate code for this case statement and it would
> execute for every record.  With Drill, almost all record batches would have
> *either* X or Y.  Drill would generate different code for those two
> different patterns of data and that code would be generated with the
> knowledge that X is null, or that Y is null.  As such, the optimizer in the
> code generator would actually just completely remove the case statement by
> evaluating it at code generation time.  By pushing that code generation
> time very late in the execution, Drill would have no perceptible penalty
> relative to uniformly typed code, but it would have the ability to deal
> with non-uniform data.
>
>
> My original comment was an indefensible shorthand for all of this.  Things
> should be made as simple as possible, but no simpler, as the great man
> said.
>
>
> On Wed, May 27, 2015 at 8:32 PM, Andrew Brust <
> andrew.br...@bluebadgeinsights.com> wrote:
>
> > That makes sense.  Just having trouble mapping that back on Ted's
> > comment.  But I tend to think that's me and my ignorance.
> >
> > -Original Message-
> > From: Hanifi Gunes [mailto:hgu...@maprtech.com]
> > Sent: Wednesday, May 27, 2015 4:48 PM
> > To: user
> > Subject: Re: what's the differenct between drill and optiq
> >
> > Calcite does parsing & planning of queries. Drill executes in a very
> > flexible distributed columnar fashion with late binding.
> >
> > On Wed, May 27, 2015 at 8:34 AM, Ted Dunning 
> > wrote:
> >
> > > Andrew,
> > >
> > > What Hive does not have is the extensions that Drill has that allow
> > > SQL to be type flexible.  The ALL type and all of the implications
> > > both in terms of implementation and user impact it has are a really big
> > deal.
> > >
> > >
> > >
> > > On Wed, May 27, 2015 at 6:08 AM, Andrew Brust <
> > > andrew.br...@bluebadgeinsights.com> wrote:
> > >
> > > > Thanks!
> > > >
> > > > Sent from my phone
> > > > 
> > > >
> > > > - Reply message -
> > > > From: "PHANI KUMAR YADAVILLI" 
> > > > To: "user@drill.apache.org" 
> > > > Subject: what's the differen

Re: what's the differenct between drill and optiq

[Ted Dunning]

Andrew,

Sorry for being cryptic.  Hanifi is more clear.  My point was directed at
the differences between where Hive may ultimately go and where Drill is
now.  Hanifi was providing a good summary of where Drill is now.

As he said, Calcite does query parsing and planning.  Ultimately, it will
do the same for Hive.  Even so, Drill has extended Calcite's planning
capabilities in ways which are not used by Hive.  These extensions allow
Calcite to produce plans for the Drill execution engine.  That execution
engine is what Hanifi meant by flexible distributed columnar execution with
late binding.

SQL is not normally a late binding language.  Instead, it shows its long
heritage by being a very statically typed language.  That static typing is
a problem in the modern world of flexible data and dealing with this
problem is a key goal of Drill.

The key technological advance in Drill that enables it to address late
typing problems is something called the ANY type.  This is essentially a
way for the parser to punt the problem of resolving the type of some value
until the query is actually running.  At that point, Drill has an empirical
schema available for each record batch which can be used to do final code
generation and optimization.  If the empirical schema changes due to
changes in the data being processed, that code can be regenerated as
needed.

This is a huge philosophical and design change that is hard to just paste
onto an existing engine.  Just as it would be next to impossible to modify
a Pascal or Fortran execution environment to do the type inferencing and
lazy execution that Scala or Haskell do, it is going to be hard to extend
Hive's entire execution environment to deal with type dynamism.  Simply
passing around dynamic types will not give performance anywhere near what
Drill does because of the inevitable cost of type tag dispatching.

To give just the simplest example, suppose you have data that used a column
named X to hold an integer for a long while and then switched to using a
column named Y to hold a floating point number.  To deal with this, you
might create a view which has a case statement that uses the value of X or
Y, whichever is non-null.  In conventional SQL engines, the query parser
and planner would generate code for this case statement and it would
execute for every record.  With Drill, almost all record batches would have
*either* X or Y.  Drill would generate different code for those two
different patterns of data and that code would be generated with the
knowledge that X is null, or that Y is null.  As such, the optimizer in the
code generator would actually just completely remove the case statement by
evaluating it at code generation time.  By pushing that code generation
time very late in the execution, Drill would have no perceptible penalty
relative to uniformly typed code, but it would have the ability to deal
with non-uniform data.


My original comment was an indefensible shorthand for all of this.  Things
should be made as simple as possible, but no simpler, as the great man said.


On Wed, May 27, 2015 at 8:32 PM, Andrew Brust <
andrew.br...@bluebadgeinsights.com> wrote:

> That makes sense.  Just having trouble mapping that back on Ted's
> comment.  But I tend to think that's me and my ignorance.
>
> -Original Message-
> From: Hanifi Gunes [mailto:hgu...@maprtech.com]
> Sent: Wednesday, May 27, 2015 4:48 PM
> To: user
> Subject: Re: what's the differenct between drill and optiq
>
> Calcite does parsing & planning of queries. Drill executes in a very
> flexible distributed columnar fashion with late binding.
>
> On Wed, May 27, 2015 at 8:34 AM, Ted Dunning 
> wrote:
>
> > Andrew,
> >
> > What Hive does not have is the extensions that Drill has that allow
> > SQL to be type flexible.  The ALL type and all of the implications
> > both in terms of implementation and user impact it has are a really big
> deal.
> >
> >
> >
> > On Wed, May 27, 2015 at 6:08 AM, Andrew Brust <
> > andrew.br...@bluebadgeinsights.com> wrote:
> >
> > > Thanks!
> > >
> > > Sent from my phone
> > > 
> > >
> > > - Reply message -
> > > From: "PHANI KUMAR YADAVILLI" 
> > > To: "user@drill.apache.org" 
> > > Subject: what's the differenct between drill and optiq
> > > Date: Wed, May 27, 2015 8:33 AM
> > >
> > > Yes hive uses calcite. You can refer hive documentation.
> > > On May 27, 2015 6:01 PM, "Andrew Brust" <
> > > andrew.br...@bluebadgeinsights.com>
> > > wrote:
> > >
> > > > Folks at Hortonworks told me that Hive now uses Calcite as well.
> > > > Can anyone here confirm or deny that?
> > > >
> > > > -Original Message-
> > > > From: Rajkumar Singh [mailto:rsi...@maprtech.com]
> > > > Sent: Wednesday, May 27, 2015 6:52 AM
> > > > To: user@drill.apache.org
> > > > Subject: Re: what's the differenct between drill and optiq
> > > >
> > > > Optiq(now known as calcite) is an api for query parser,planner and
> > > > optimization, drill uses it for the SQL parsing,validation and

Re: what's the differenct between drill and optiq

[Jinfeng Ni]

Besides the different optimization rule sets and cost model (Calcite is
very extensive in that sense) used by Hive and Drill, I see the following
difference of the way how Calcite is used in these two systems.

1.  Drill supports query against schema-less storage (text, JSON etc).
For Hive or other traditional database systems, each column's data type is
known to query planner during planning time. For Drill, that's not the
case; the column type could be known only during execution time, when the
data is scanned from storage plugin.  That's why Drill uses SQL ANY type (=
ALL type in Ted's response) in Calcite for such use case. At execution,
each column's type would be determined dynamically, and run-time code would
be generated based on type resolution.

This concept is sometime called as late binding.

2.  Select * query support : in Hive or DB system, query planner would
expand * into a list of regular columns. For Drill, since the table
metadata is not available, Calcite could not expand *, which would be
deferred to Drill's execution time. We have to extend Calcite such that the
query planner would keep the semantics of *, and make the query semantics
checking pass through Calcite for select * query.

For instance,

Q1: Select * from T1;

Q2: Select  T1.*, T2.COL_A from T1, T2 where T1.COL1 = T2.COL2;

Q3: Select * from ( SELECT * FROM T1) ORDER BY COL1;

In all the cases, Drill has to make sure Calcite would do name resolution
for * column correctly in planning time, and then expand the * column
properly in execution time.





On Wed, May 27, 2015 at 8:32 PM, Andrew Brust <
andrew.br...@bluebadgeinsights.com> wrote:

> That makes sense.  Just having trouble mapping that back on Ted's
> comment.  But I tend to think that's me and my ignorance.
>
> -Original Message-
> From: Hanifi Gunes [mailto:hgu...@maprtech.com]
> Sent: Wednesday, May 27, 2015 4:48 PM
> To: user
> Subject: Re: what's the differenct between drill and optiq
>
> Calcite does parsing & planning of queries. Drill executes in a very
> flexible distributed columnar fashion with late binding.
>
> On Wed, May 27, 2015 at 8:34 AM, Ted Dunning 
> wrote:
>
> > Andrew,
> >
> > What Hive does not have is the extensions that Drill has that allow
> > SQL to be type flexible.  The ALL type and all of the implications
> > both in terms of implementation and user impact it has are a really big
> deal.
> >
> >
> >
> > On Wed, May 27, 2015 at 6:08 AM, Andrew Brust <
> > andrew.br...@bluebadgeinsights.com> wrote:
> >
> > > Thanks!
> > >
> > > Sent from my phone
> > > 
> > >
> > > - Reply message -
> > > From: "PHANI KUMAR YADAVILLI" 
> > > To: "user@drill.apache.org" 
> > > Subject: what's the differenct between drill and optiq
> > > Date: Wed, May 27, 2015 8:33 AM
> > >
> > > Yes hive uses calcite. You can refer hive documentation.
> > > On May 27, 2015 6:01 PM, "Andrew Brust" <
> > > andrew.br...@bluebadgeinsights.com>
> > > wrote:
> > >
> > > > Folks at Hortonworks told me that Hive now uses Calcite as well.
> > > > Can anyone here confirm or deny that?
> > > >
> > > > -Original Message-
> > > > From: Rajkumar Singh [mailto:rsi...@maprtech.com]
> > > > Sent: Wednesday, May 27, 2015 6:52 AM
> > > > To: user@drill.apache.org
> > > > Subject: Re: what's the differenct between drill and optiq
> > > >
> > > > Optiq(now known as calcite) is an api for query parser,planner and
> > > > optimization, drill uses it for the SQL parsing,validation and
> > > > optimization.Drill query planner applies its own custom planner
> > > > rules
> > to
> > > > build the query logical plan.
> > > >
> > > > Rajkumar Singh
> > > >
> > > >
> > > >
> > > > > On May 27, 2015, at 12:04 PM, 陈礼剑  wrote:
> > > > >
> > > > > Hi:
> > > > >
> > > > > I just want to know the difference between drill and optiq.
> > > > >
> > > > >
> > > > > Is drill just 'extend' optiq to support many other
> > > > > 'stores'(hadoop,
> > > > mongodb, ...)?
> > > > >
> > > > >
> > > > > ---from davy
> > > > > Thanks.
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> >
>

RE: what's the differenct between drill and optiq

[Andrew Brust]

That makes sense.  Just having trouble mapping that back on Ted's comment.  But 
I tend to think that's me and my ignorance.

-Original Message-
From: Hanifi Gunes [mailto:hgu...@maprtech.com] 
Sent: Wednesday, May 27, 2015 4:48 PM
To: user
Subject: Re: what's the differenct between drill and optiq

Calcite does parsing & planning of queries. Drill executes in a very flexible 
distributed columnar fashion with late binding.

On Wed, May 27, 2015 at 8:34 AM, Ted Dunning  wrote:

> Andrew,
>
> What Hive does not have is the extensions that Drill has that allow 
> SQL to be type flexible.  The ALL type and all of the implications 
> both in terms of implementation and user impact it has are a really big deal.
>
>
>
> On Wed, May 27, 2015 at 6:08 AM, Andrew Brust < 
> andrew.br...@bluebadgeinsights.com> wrote:
>
> > Thanks!
> >
> > Sent from my phone
> > 
> >
> > - Reply message -
> > From: "PHANI KUMAR YADAVILLI" 
> > To: "user@drill.apache.org" 
> > Subject: what's the differenct between drill and optiq
> > Date: Wed, May 27, 2015 8:33 AM
> >
> > Yes hive uses calcite. You can refer hive documentation.
> > On May 27, 2015 6:01 PM, "Andrew Brust" < 
> > andrew.br...@bluebadgeinsights.com>
> > wrote:
> >
> > > Folks at Hortonworks told me that Hive now uses Calcite as well.  
> > > Can anyone here confirm or deny that?
> > >
> > > -Original Message-
> > > From: Rajkumar Singh [mailto:rsi...@maprtech.com]
> > > Sent: Wednesday, May 27, 2015 6:52 AM
> > > To: user@drill.apache.org
> > > Subject: Re: what's the differenct between drill and optiq
> > >
> > > Optiq(now known as calcite) is an api for query parser,planner and 
> > > optimization, drill uses it for the SQL parsing,validation and 
> > > optimization.Drill query planner applies its own custom planner 
> > > rules
> to
> > > build the query logical plan.
> > >
> > > Rajkumar Singh
> > >
> > >
> > >
> > > > On May 27, 2015, at 12:04 PM, 陈礼剑  wrote:
> > > >
> > > > Hi:
> > > >
> > > > I just want to know the difference between drill and optiq.
> > > >
> > > >
> > > > Is drill just 'extend' optiq to support many other 
> > > > 'stores'(hadoop,
> > > mongodb, ...)?
> > > >
> > > >
> > > > ---from davy
> > > > Thanks.
> > > >
> > > >
> > > >
> > >
> > >
> >
>

RE: what's the differenct between drill and optiq

[Andrew Brust]

I have to admit that I'm not following your point all that well.  Is there 
something you could link me to so I could read up and better understand without 
using up your time?

-Original Message-
From: Ted Dunning [mailto:ted.dunn...@gmail.com] 
Sent: Wednesday, May 27, 2015 11:35 AM
To: user@drill.apache.org
Subject: Re: what's the differenct between drill and optiq

Andrew,

What Hive does not have is the extensions that Drill has that allow SQL to be 
type flexible.  The ALL type and all of the implications both in terms of 
implementation and user impact it has are a really big deal.



On Wed, May 27, 2015 at 6:08 AM, Andrew Brust < 
andrew.br...@bluebadgeinsights.com> wrote:

> Thanks!
>
> Sent from my phone
> 
>
> - Reply message -
> From: "PHANI KUMAR YADAVILLI" 
> To: "user@drill.apache.org" 
> Subject: what's the differenct between drill and optiq
> Date: Wed, May 27, 2015 8:33 AM
>
> Yes hive uses calcite. You can refer hive documentation.
> On May 27, 2015 6:01 PM, "Andrew Brust" < 
> andrew.br...@bluebadgeinsights.com>
> wrote:
>
> > Folks at Hortonworks told me that Hive now uses Calcite as well.  
> > Can anyone here confirm or deny that?
> >
> > -Original Message-
> > From: Rajkumar Singh [mailto:rsi...@maprtech.com]
> > Sent: Wednesday, May 27, 2015 6:52 AM
> > To: user@drill.apache.org
> > Subject: Re: what's the differenct between drill and optiq
> >
> > Optiq(now known as calcite) is an api for query parser,planner and 
> > optimization, drill uses it for the SQL parsing,validation and 
> > optimization.Drill query planner applies its own custom planner 
> > rules to build the query logical plan.
> >
> > Rajkumar Singh
> >
> >
> >
> > > On May 27, 2015, at 12:04 PM, 陈礼剑  wrote:
> > >
> > > Hi:
> > >
> > > I just want to know the difference between drill and optiq.
> > >
> > >
> > > Is drill just 'extend' optiq to support many other 
> > > 'stores'(hadoop,
> > mongodb, ...)?
> > >
> > >
> > > ---from davy
> > > Thanks.
> > >
> > >
> > >
> >
> >
>

Re: what's the differenct between drill and optiq

[Hanifi Gunes]

Calcite does parsing & planning of queries. Drill executes in a very
flexible distributed columnar fashion with late binding.

On Wed, May 27, 2015 at 8:34 AM, Ted Dunning  wrote:

> Andrew,
>
> What Hive does not have is the extensions that Drill has that allow SQL to
> be type flexible.  The ALL type and all of the implications both in terms
> of implementation and user impact it has are a really big deal.
>
>
>
> On Wed, May 27, 2015 at 6:08 AM, Andrew Brust <
> andrew.br...@bluebadgeinsights.com> wrote:
>
> > Thanks!
> >
> > Sent from my phone
> > 
> >
> > - Reply message -
> > From: "PHANI KUMAR YADAVILLI" 
> > To: "user@drill.apache.org" 
> > Subject: what's the differenct between drill and optiq
> > Date: Wed, May 27, 2015 8:33 AM
> >
> > Yes hive uses calcite. You can refer hive documentation.
> > On May 27, 2015 6:01 PM, "Andrew Brust" <
> > andrew.br...@bluebadgeinsights.com>
> > wrote:
> >
> > > Folks at Hortonworks told me that Hive now uses Calcite as well.  Can
> > > anyone here confirm or deny that?
> > >
> > > -Original Message-
> > > From: Rajkumar Singh [mailto:rsi...@maprtech.com]
> > > Sent: Wednesday, May 27, 2015 6:52 AM
> > > To: user@drill.apache.org
> > > Subject: Re: what's the differenct between drill and optiq
> > >
> > > Optiq(now known as calcite) is an api for query parser,planner and
> > > optimization, drill uses it for the SQL parsing,validation and
> > > optimization.Drill query planner applies its own custom planner rules
> to
> > > build the query logical plan.
> > >
> > > Rajkumar Singh
> > >
> > >
> > >
> > > > On May 27, 2015, at 12:04 PM, 陈礼剑  wrote:
> > > >
> > > > Hi:
> > > >
> > > > I just want to know the difference between drill and optiq.
> > > >
> > > >
> > > > Is drill just 'extend' optiq to support many other 'stores'(hadoop,
> > > mongodb, ...)?
> > > >
> > > >
> > > > ---from davy
> > > > Thanks.
> > > >
> > > >
> > > >
> > >
> > >
> >
>

Monitoring long / stuck CTAS

[Matt]

Attempting to create a Parquet backed table with a CTAS from an 44GB tab 
delimited file in HDFS. The process seemed to be running, as CPU and IO 
was seen on all 4 nodes in this cluster, and .parquet files being 
created in the expected path.


In however in the last two hours or so, all nodes show near zero CPU or 
IO, and the Last Modified date on the .parquet have not changed. Same 
time delay shown in the Last Progress column in the active fragment 
profile.


What approach can I take to determine what is happening (or not)?

Re: DrillBits Communication

[Kristine Hahn]

>
> With respect to user interacting with drillbit, the following documentation
> will give you some idea.
> https://cwiki.apache.org/confluence/display/DRILL/Drill+Interfaces


Correction: The cwiki site is obsolete. Here's the latest Drill interfaces
docs:

http://drill.apache.org/docs/odbc-jdbc-interfaces/

Kristine Hahn
Sr. Technical Writer
415-497-8107 @krishahn


On Wed, May 27, 2015 at 11:04 AM, Neeraja Rentachintala <
nrentachint...@maprtech.com> wrote:

> Sanjeev
> If you are new to Drill, I suggest looking at the high level architecture
> doc below to get started.
> http://drill.apache.org/architecture/
> With respect to user interacting with drillbit, the following documentation
> will give you some idea.
> https://cwiki.apache.org/confluence/display/DRILL/Drill+Interfaces
>
> On Wed, May 27, 2015 at 6:38 AM, Sanjeev Verma 
> wrote:
>
> > Hi
> >
> > Can somebody help me to get some documentation  regarding how drillbit
> > communicate with the other drillbits in the cluster and also how user do
> > interact with the drillbit.
> >
> > Thanks
> > Sanjeev
> >
>

Re: DrillBits Communication

[Neeraja Rentachintala]

Sanjeev
If you are new to Drill, I suggest looking at the high level architecture
doc below to get started.
http://drill.apache.org/architecture/
With respect to user interacting with drillbit, the following documentation
will give you some idea.
https://cwiki.apache.org/confluence/display/DRILL/Drill+Interfaces

On Wed, May 27, 2015 at 6:38 AM, Sanjeev Verma 
wrote:

> Hi
>
> Can somebody help me to get some documentation  regarding how drillbit
> communicate with the other drillbits in the cluster and also how user do
> interact with the drillbit.
>
> Thanks
> Sanjeev
>

RE: JAVA API for Drill

[Norris Lee]

Hi Nishith,

Take a look at the DrillClient.java and .cpp/.hpp classes of the project for 
the  Java and C++ libraries respectively.

Norris

-Original Message-
From: Nishith Maheshwari [mailto:nsh...@gmail.com] 
Sent: Wednesday, May 27, 2015 1:45 AM
To: user@drill.apache.org
Subject: Re: JAVA API for Drill

Thank you Martin and Rajkumar for your prompt responses.

I am actually looking if some API is available which provides this 
functionality. In the documentation it is mentioned in :
https://drill.apache.org/docs/architecture-introduction -

*You can connect to Apache Drill through the following interfaces:*

   - *Drill shell*
   - *Drill Web UI*
   - *ODBC
   
**
   - *JDBC *
   - *C++ API*


and in http://drill.apache.org/faq/ -
*What clients are supported?*

   - *BI tools via the ODBC and JDBC drivers (eg, Tableau, Excel,
   MicroStrategy, Spotfire, QlikView, Business Objects)*
   - *Custom applications via the REST API*
   - *Java and C applications via the dedicated Java and C libraries*


It would be great if you/somebody can point me to the C++ api or the dedicated 
JAVA library or API as mentioned in the documentation.

Thanks and regards,
Nishith Maheshwari



On Wed, May 27, 2015 at 12:44 PM, Rajkumar Singh 
wrote:

> Do you try drill-jdbc driver? I will suggest you to use java jdbc 
> connectivity to query drill using the drill-jdbc driver.I have not 
> tried this to query HBASE using drill but it should work if you have 
> correctly configured the HBase Storage plugin with the DRILL.
>
> Thanks
>
> Rajkumar Singh
>
>
>
> > On May 27, 2015, at 12:09 PM, Nishith Maheshwari 
> wrote:
> >
> > Hi,
> > I wanted to create a java application to connect and query over a 
> > HBase database using Drill, but was unable to find any documentation 
> > regarding this.
> > Is there a JAVA api through which Drill can be accessed? I did see a
> small
> > mention of C++ and JAVA api in the documentation but there was no 
> > other link or information regarding the same.
> >
> > Regards,
> > Nishith Maheshwari
>
>

Re: Query local files on cluster? [Beginner]

[Andries Engelbrecht]

OK, that is the simplest way to get going. And see how the solution works for 
you. It can be a little confusing between local FS and working with a cluster.

I have found that dealing with large data volumes it worked much easier to use 
the NFS on the MapR cluster to directly move data to the DFS and bypass the 
local FS and then to DFS. Skips a step and also a lot more robust and faster to 
get the data directly to the DFS.



On May 27, 2015, at 8:37 AM, Matt  wrote:

>> Drill can process a lot of data quickly, and for best performance and 
>> consistency you will likely find that the sooner you get the data to the DFS 
>> the better.
> 
> Already most of the way there. Initial confusion came from the features to 
> query the local / native filesystem, and how that does not fit a distributed 
> Drill cluster well. In other words its really an embedded / single-node Drill 
> feature.
> 
> Currently using the approach of doing a put from local filsystem into hdfs, 
> then CTAS into Parquet, if only for simplicity in testing (not performance).
> 
> Thanks!
> 
>> On 27 May 2015, at 11:29, Andries Engelbrecht wrote:
>> 
>> You will be better off to use the Drill cluster as a whole vs trying to play 
>> with local vs DFS storage.
>> 
>> A couple of ideas:
>> As previously mentioned you can use the robust NFS on MapR to easily place 
>> the CSV/files on the DFS, and then use Drill with CTAS to convert the files 
>> to Parquet on the DFS.
>> 
>> You can set up a remote NFS server and map the local FS on each node to the 
>> same NFS mount point to the remote NFS server, this will the files will be 
>> consistently available to the Drillbits in the cluster and you can do CTAS 
>> to create parquet file son the DFS. This however will likely be a lot slower 
>> than the first option, as the NFS server BW will become a bottleneck if you 
>> have a number of drillbits in the cluster.
>> 
>> Just copy the files to one node in the cluster and then use hadoop fs to put 
>> the files in the DFS, and then do the CTAS from DFS to parquet DFS.
>> 
>> You can even place the data on S3 and then query and CTAS from there, 
>> however security and bandwidth may be a concern for large data volumes, 
>> pending the use case.
>> 
>> I really think you will find the first option the most robust and fastest in 
>> the long run. You can point Drill at any FS source as long as it is 
>> consistent to all nodes in the cluster, but keep in mind that Drill can 
>> process a lot of data quickly, and for best performance and consistency you 
>> will likely find that the sooner you get the data to the DFS the better.
>> 
>> 
>> 
>> 
>>> On May 26, 2015, at 5:58 PM, Matt  wrote:
>>> 
>>> Thanks, I am incorrectly conflating the file system with data storage.
>>> 
>>> Looking to experiment with the Parquet format, and was looking at CTAS 
>>> queries as an import approach.
>>> 
>>> Are direct queries over local files meant for an embedded drill, where on a 
>>> cluster files should be moved into HDFS first?
>>> 
>>> That would make sense as files on one node would be query bound to that 
>>> local filesystem.
>>> 
 On May 26, 2015, at 8:28 PM, Andries Engelbrecht 
  wrote:
 
 You can use the HDFS shell
 hadoop fs -put
 
 To copy from local file system to HDFS
 
 
 For more robust mechanisms from remote systems you can look at using NFS, 
 MapR has a really robust NFS integration and you can use it with the 
 community edition.
 
 
 
 
> On May 26, 2015, at 5:11 PM, Matt  wrote:
> 
> 
> That might be the end goal, but currently I don't have an HDFS ingest 
> mechanism.
> 
> We are not currently a Hadoop shop - can you suggest simple approaches 
> for bulk loading data from delimited files into HDFS?
> 
> 
> 
> 
>> On May 26, 2015, at 8:04 PM, Andries Engelbrecht 
>>  wrote:
>> 
>> Perhaps I’m missing something here.
>> 
>> Why not create a DFS plug in for HDFS and put the file in HDFS?
>> 
>> 
>> 
>>> On May 26, 2015, at 4:54 PM, Matt  wrote:
>>> 
>>> New installation with Hadoop 2.7 and Drill 1.0 on 4 nodes, it appears 
>>> text files need to be on all nodes in a cluster?
>>> 
>>> Using the dfs config below, I am only able to query if a csv file is on 
>>> all 4 nodes. If the file is only on the local node and not others, I 
>>> get errors in the form of:
>>> 
>>> ~~~
>>> 0: jdbc:drill:zk=es05:2181> select * from 
>>> root.`customer_reviews_1998.csv`;
>>> Error: PARSE ERROR: From line 1, column 15 to line 1, column 18: Table 
>>> 'root.customer_reviews_1998.csv' not found
>>> ~~~
>>> 
>>> ~~~
>>> {
>>> "type": "file",
>>> "enabled": true,
>>> "connection": "file:///",
>>> "workspaces": {
>>> "root": {
>>> "location": "/localdata/hadoop/stage",
>>> "writable": false,
>>> "defaultInputFormat": nu

Re: Query local files on cluster? [Beginner]

[Matt]

Drill can process a lot of data quickly, and for best performance and 
consistency you will likely find that the sooner you get the data to 
the DFS the better.


Already most of the way there. Initial confusion came from the features 
to query the local / native filesystem, and how that does not fit a 
distributed Drill cluster well. In other words its really an embedded / 
single-node Drill feature.


Currently using the approach of doing a put from local filsystem into 
hdfs, then CTAS into Parquet, if only for simplicity in testing (not 
performance).


Thanks!

On 27 May 2015, at 11:29, Andries Engelbrecht wrote:

You will be better off to use the Drill cluster as a whole vs trying 
to play with local vs DFS storage.


A couple of ideas:
As previously mentioned you can use the robust NFS on MapR to easily 
place the CSV/files on the DFS, and then use Drill with CTAS to 
convert the files to Parquet on the DFS.


You can set up a remote NFS server and map the local FS on each node 
to the same NFS mount point to the remote NFS server, this will the 
files will be consistently available to the Drillbits in the cluster 
and you can do CTAS to create parquet file son the DFS. This however 
will likely be a lot slower than the first option, as the NFS server 
BW will become a bottleneck if you have a number of drillbits in the 
cluster.


Just copy the files to one node in the cluster and then use hadoop fs 
to put the files in the DFS, and then do the CTAS from DFS to parquet 
DFS.


You can even place the data on S3 and then query and CTAS from there, 
however security and bandwidth may be a concern for large data 
volumes, pending the use case.


I really think you will find the first option the most robust and 
fastest in the long run. You can point Drill at any FS source as long 
as it is consistent to all nodes in the cluster, but keep in mind that 
Drill can process a lot of data quickly, and for best performance and 
consistency you will likely find that the sooner you get the data to 
the DFS the better.






On May 26, 2015, at 5:58 PM, Matt  wrote:

Thanks, I am incorrectly conflating the file system with data 
storage.


Looking to experiment with the Parquet format, and was looking at 
CTAS queries as an import approach.


Are direct queries over local files meant for an embedded drill, 
where on a cluster files should be moved into HDFS first?


That would make sense as files on one node would be query bound to 
that local filesystem.


On May 26, 2015, at 8:28 PM, Andries Engelbrecht 
 wrote:


You can use the HDFS shell
hadoop fs -put

To copy from local file system to HDFS


For more robust mechanisms from remote systems you can look at using 
NFS, MapR has a really robust NFS integration and you can use it 
with the community edition.






On May 26, 2015, at 5:11 PM, Matt  wrote:


That might be the end goal, but currently I don't have an HDFS 
ingest mechanism.


We are not currently a Hadoop shop - can you suggest simple 
approaches for bulk loading data from delimited files into HDFS?





On May 26, 2015, at 8:04 PM, Andries Engelbrecht 
 wrote:


Perhaps I’m missing something here.

Why not create a DFS plug in for HDFS and put the file in HDFS?




On May 26, 2015, at 4:54 PM, Matt  wrote:

New installation with Hadoop 2.7 and Drill 1.0 on 4 nodes, it 
appears text files need to be on all nodes in a cluster?


Using the dfs config below, I am only able to query if a csv file 
is on all 4 nodes. If the file is only on the local node and not 
others, I get errors in the form of:


~~~
0: jdbc:drill:zk=es05:2181> select * from 
root.`customer_reviews_1998.csv`;
Error: PARSE ERROR: From line 1, column 15 to line 1, column 18: 
Table 'root.customer_reviews_1998.csv' not found

~~~

~~~
{
"type": "file",
"enabled": true,
"connection": "file:///",
"workspaces": {
"root": {
"location": "/localdata/hadoop/stage",
"writable": false,
"defaultInputFormat": null
},
~~~


On 25 May 2015, at 20:39, Kristine Hahn wrote:

The storage plugin "location" needs to be the full path to the 
localdata
directory. This partial storage plugin definition works for the 
user named

mapr:

{
"type": "file",
"enabled": true,
"connection": "file:///",
"workspaces": {
"root": {
"location": "/home/mapr/localdata",
"writable": false,
"defaultInputFormat": null
},
. . .

Here's a working query for the data in localdata:

0: jdbc:drill:> SELECT COLUMNS[0] AS Ngram,
. . . . . . . > COLUMNS[1] AS Publication_Date,
. . . . . . . > COLUMNS[2] AS Frequency
. . . . . . . > FROM dfs.root.`mydata.csv`
. . . . . . . > WHERE ((columns[0] = 'Zoological Journal of the 
Linnean')

. . . . . . . > AND (columns[2] > 250)) LIMIT 10;

An complete example, not yet published on the Drill site, shows 
in detail

the steps involved:
http://tshiran.github.io/drill/docs/querying-plain-text-files/#example-of-querying-a-tsv-file


Kristine Hahn
Sr. Technical Writer
415-497-8107 @krishahn



On Sun, May 24, 2015 at 1:56 PM, Matt  wrote:

I h

DrillBits Communication

[Sanjeev Verma]

Hi

Can somebody help me to get some documentation  regarding how drillbit
communicate with the other drillbits in the cluster and also how user do
interact with the drillbit.

Thanks
Sanjeev

Re: what's the differenct between drill and optiq

[Ted Dunning]

Andrew,

What Hive does not have is the extensions that Drill has that allow SQL to
be type flexible.  The ALL type and all of the implications both in terms
of implementation and user impact it has are a really big deal.



On Wed, May 27, 2015 at 6:08 AM, Andrew Brust <
andrew.br...@bluebadgeinsights.com> wrote:

> Thanks!
>
> Sent from my phone
> 
>
> - Reply message -
> From: "PHANI KUMAR YADAVILLI" 
> To: "user@drill.apache.org" 
> Subject: what's the differenct between drill and optiq
> Date: Wed, May 27, 2015 8:33 AM
>
> Yes hive uses calcite. You can refer hive documentation.
> On May 27, 2015 6:01 PM, "Andrew Brust" <
> andrew.br...@bluebadgeinsights.com>
> wrote:
>
> > Folks at Hortonworks told me that Hive now uses Calcite as well.  Can
> > anyone here confirm or deny that?
> >
> > -Original Message-
> > From: Rajkumar Singh [mailto:rsi...@maprtech.com]
> > Sent: Wednesday, May 27, 2015 6:52 AM
> > To: user@drill.apache.org
> > Subject: Re: what's the differenct between drill and optiq
> >
> > Optiq(now known as calcite) is an api for query parser,planner and
> > optimization, drill uses it for the SQL parsing,validation and
> > optimization.Drill query planner applies its own custom planner rules to
> > build the query logical plan.
> >
> > Rajkumar Singh
> >
> >
> >
> > > On May 27, 2015, at 12:04 PM, 陈礼剑  wrote:
> > >
> > > Hi:
> > >
> > > I just want to know the difference between drill and optiq.
> > >
> > >
> > > Is drill just 'extend' optiq to support many other 'stores'(hadoop,
> > mongodb, ...)?
> > >
> > >
> > > ---from davy
> > > Thanks.
> > >
> > >
> > >
> >
> >
>

Re: Query local files on cluster? [Beginner]

[Andries Engelbrecht]

You will be better off to use the Drill cluster as a whole vs trying to play 
with local vs DFS storage.

A couple of ideas:
As previously mentioned you can use the robust NFS on MapR to easily place the 
CSV/files on the DFS, and then use Drill with CTAS to convert the files to 
Parquet on the DFS.

You can set up a remote NFS server and map the local FS on each node to the 
same NFS mount point to the remote NFS server, this will the files will be 
consistently available to the Drillbits in the cluster and you can do CTAS to 
create parquet file son the DFS. This however will likely be a lot slower than 
the first option, as the NFS server BW will become a bottleneck if you have a 
number of drillbits in the cluster.

Just copy the files to one node in the cluster and then use hadoop fs to put 
the files in the DFS, and then do the CTAS from DFS to parquet DFS.

You can even place the data on S3 and then query and CTAS from there, however 
security and bandwidth may be a concern for large data volumes, pending the use 
case.

I really think you will find the first option the most robust and fastest in 
the long run. You can point Drill at any FS source as long as it is consistent 
to all nodes in the cluster, but keep in mind that Drill can process a lot of 
data quickly, and for best performance and consistency you will likely find 
that the sooner you get the data to the DFS the better. 




> On May 26, 2015, at 5:58 PM, Matt  wrote:
> 
> Thanks, I am incorrectly conflating the file system with data storage. 
> 
> Looking to experiment with the Parquet format, and was looking at CTAS 
> queries as an import approach.
> 
> Are direct queries over local files meant for an embedded drill, where on a 
> cluster files should be moved into HDFS first?
> 
> That would make sense as files on one node would be query bound to that local 
> filesystem. 
> 
>> On May 26, 2015, at 8:28 PM, Andries Engelbrecht  
>> wrote:
>> 
>> You can use the HDFS shell
>> hadoop fs -put
>> 
>> To copy from local file system to HDFS
>> 
>> 
>> For more robust mechanisms from remote systems you can look at using NFS, 
>> MapR has a really robust NFS integration and you can use it with the 
>> community edition.
>> 
>> 
>> 
>> 
>>> On May 26, 2015, at 5:11 PM, Matt  wrote:
>>> 
>>> 
>>> That might be the end goal, but currently I don't have an HDFS ingest 
>>> mechanism. 
>>> 
>>> We are not currently a Hadoop shop - can you suggest simple approaches for 
>>> bulk loading data from delimited files into HDFS?
>>> 
>>> 
>>> 
>>> 
 On May 26, 2015, at 8:04 PM, Andries Engelbrecht 
  wrote:
 
 Perhaps I’m missing something here.
 
 Why not create a DFS plug in for HDFS and put the file in HDFS?
 
 
 
> On May 26, 2015, at 4:54 PM, Matt  wrote:
> 
> New installation with Hadoop 2.7 and Drill 1.0 on 4 nodes, it appears 
> text files need to be on all nodes in a cluster?
> 
> Using the dfs config below, I am only able to query if a csv file is on 
> all 4 nodes. If the file is only on the local node and not others, I get 
> errors in the form of:
> 
> ~~~
> 0: jdbc:drill:zk=es05:2181> select * from 
> root.`customer_reviews_1998.csv`;
> Error: PARSE ERROR: From line 1, column 15 to line 1, column 18: Table 
> 'root.customer_reviews_1998.csv' not found
> ~~~
> 
> ~~~
> {
> "type": "file",
> "enabled": true,
> "connection": "file:///",
> "workspaces": {
> "root": {
>  "location": "/localdata/hadoop/stage",
>  "writable": false,
>  "defaultInputFormat": null
> },
> ~~~
> 
>> On 25 May 2015, at 20:39, Kristine Hahn wrote:
>> 
>> The storage plugin "location" needs to be the full path to the localdata
>> directory. This partial storage plugin definition works for the user 
>> named
>> mapr:
>> 
>> {
>> "type": "file",
>> "enabled": true,
>> "connection": "file:///",
>> "workspaces": {
>> "root": {
>> "location": "/home/mapr/localdata",
>> "writable": false,
>> "defaultInputFormat": null
>> },
>> . . .
>> 
>> Here's a working query for the data in localdata:
>> 
>> 0: jdbc:drill:> SELECT COLUMNS[0] AS Ngram,
>> . . . . . . . > COLUMNS[1] AS Publication_Date,
>> . . . . . . . > COLUMNS[2] AS Frequency
>> . . . . . . . > FROM dfs.root.`mydata.csv`
>> . . . . . . . > WHERE ((columns[0] = 'Zoological Journal of the Linnean')
>> . . . . . . . > AND (columns[2] > 250)) LIMIT 10;
>> 
>> An complete example, not yet published on the Drill site, shows in detail
>> the steps involved:
>> http://tshiran.github.io/drill/docs/querying-plain-text-files/#example-of-querying-a-tsv-file
>> 
>> 
>> Kristine Hahn
>> Sr. Technical Writer
>> 415-497-8107 @krishahn
>> 
>> 
>>> On Sun, May 24, 2015 at 1:56 PM, Matt  wrote:
>>> 
>>> I have used a single node

Re: DrillBits Communication

[Rajkumar Singh]

Bitcom allows one drillbit to communicate with the other, it basically decide 
client and server drillbits based on the who  has initiated the 
connection.Please start looking at BitCom.java and BitComImpl.java to know more 
about it.

Rajkumar Singh
MapR Technologies


> On May 27, 2015, at 7:08 PM, Sanjeev Verma  wrote:
> 
> Hi
> 
> Can somebody help me to get some documentation  regarding how drillbit
> communicate with the other drillbits in the cluster and also how user do
> interact with the drillbit.
> 
> Thanks
> Sanjeev

Re: what's the differenct between drill and optiq

[Andrew Brust]

Thanks!

Sent from my phone


- Reply message -
From: "PHANI KUMAR YADAVILLI" 
To: "user@drill.apache.org" 
Subject: what's the differenct between drill and optiq
Date: Wed, May 27, 2015 8:33 AM

Yes hive uses calcite. You can refer hive documentation.
On May 27, 2015 6:01 PM, "Andrew Brust" 
wrote:

> Folks at Hortonworks told me that Hive now uses Calcite as well.  Can
> anyone here confirm or deny that?
>
> -Original Message-
> From: Rajkumar Singh [mailto:rsi...@maprtech.com]
> Sent: Wednesday, May 27, 2015 6:52 AM
> To: user@drill.apache.org
> Subject: Re: what's the differenct between drill and optiq
>
> Optiq(now known as calcite) is an api for query parser,planner and
> optimization, drill uses it for the SQL parsing,validation and
> optimization.Drill query planner applies its own custom planner rules to
> build the query logical plan.
>
> Rajkumar Singh
>
>
>
> > On May 27, 2015, at 12:04 PM, 陈礼剑  wrote:
> >
> > Hi:
> >
> > I just want to know the difference between drill and optiq.
> >
> >
> > Is drill just 'extend' optiq to support many other 'stores'(hadoop,
> mongodb, ...)?
> >
> >
> > ---from davy
> > Thanks.
> >
> >
> >
>
>

RE: what's the differenct between drill and optiq

[PHANI KUMAR YADAVILLI]

Yes hive uses calcite. You can refer hive documentation.
On May 27, 2015 6:01 PM, "Andrew Brust" 
wrote:

> Folks at Hortonworks told me that Hive now uses Calcite as well.  Can
> anyone here confirm or deny that?
>
> -Original Message-
> From: Rajkumar Singh [mailto:rsi...@maprtech.com]
> Sent: Wednesday, May 27, 2015 6:52 AM
> To: user@drill.apache.org
> Subject: Re: what's the differenct between drill and optiq
>
> Optiq(now known as calcite) is an api for query parser,planner and
> optimization, drill uses it for the SQL parsing,validation and
> optimization.Drill query planner applies its own custom planner rules to
> build the query logical plan.
>
> Rajkumar Singh
>
>
>
> > On May 27, 2015, at 12:04 PM, 陈礼剑  wrote:
> >
> > Hi:
> >
> > I just want to know the difference between drill and optiq.
> >
> >
> > Is drill just 'extend' optiq to support many other 'stores'(hadoop,
> mongodb, ...)?
> >
> >
> > ---from davy
> > Thanks.
> >
> >
> >
>
>

RE: what's the differenct between drill and optiq

[Andrew Brust]

Folks at Hortonworks told me that Hive now uses Calcite as well.  Can anyone 
here confirm or deny that?

-Original Message-
From: Rajkumar Singh [mailto:rsi...@maprtech.com] 
Sent: Wednesday, May 27, 2015 6:52 AM
To: user@drill.apache.org
Subject: Re: what's the differenct between drill and optiq

Optiq(now known as calcite) is an api for query parser,planner and 
optimization, drill uses it for the SQL parsing,validation and 
optimization.Drill query planner applies its own custom planner rules to build 
the query logical plan.

Rajkumar Singh



> On May 27, 2015, at 12:04 PM, 陈礼剑  wrote:
> 
> Hi:
> 
> I just want to know the difference between drill and optiq. 
> 
> 
> Is drill just 'extend' optiq to support many other 'stores'(hadoop, mongodb, 
> ...)?
> 
> 
> ---from davy
> Thanks.
> 
> 
> 

Re: what's the differenct between drill and optiq

[Rajkumar Singh]

Optiq(now known as calcite) is an api for query parser,planner and 
optimization, drill uses it for the SQL parsing,validation and 
optimization.Drill query planner applies its own custom planner rules to build 
the query logical plan.

Rajkumar Singh



> On May 27, 2015, at 12:04 PM, 陈礼剑  wrote:
> 
> Hi:
> 
> I just want to know the difference between drill and optiq. 
> 
> 
> Is drill just 'extend' optiq to support many other 'stores'(hadoop, mongodb, 
> ...)?
> 
> 
> ---from davy
> Thanks.
> 
> 
> 

what's the differenct between drill and optiq

[陈礼剑]

Hi:

I just want to know the difference between drill and optiq. 


Is drill just 'extend' optiq to support many other 'stores'(hadoop, mongodb, 
...)?


---from davy
Thanks.

Re: JAVA API for Drill

[Nishith Maheshwari]

Thank you Martin and Rajkumar for your prompt responses.

I am actually looking if some API is available which provides this
functionality. In the documentation it is mentioned in :
https://drill.apache.org/docs/architecture-introduction -

*You can connect to Apache Drill through the following interfaces:*

   - *Drill shell*
   - *Drill Web UI*
   - *ODBC
   
**
   - *JDBC *
   - *C++ API*


and in http://drill.apache.org/faq/ -
*What clients are supported?*

   - *BI tools via the ODBC and JDBC drivers (eg, Tableau, Excel,
   MicroStrategy, Spotfire, QlikView, Business Objects)*
   - *Custom applications via the REST API*
   - *Java and C applications via the dedicated Java and C libraries*


It would be great if you/somebody can point me to the C++ api or the
dedicated JAVA library or API as mentioned in the documentation.

Thanks and regards,
Nishith Maheshwari



On Wed, May 27, 2015 at 12:44 PM, Rajkumar Singh 
wrote:

> Do you try drill-jdbc driver? I will suggest you to use java jdbc
> connectivity to query drill using the drill-jdbc driver.I have not tried
> this to query HBASE using drill but it should work if you have correctly
> configured the HBase Storage plugin with the DRILL.
>
> Thanks
>
> Rajkumar Singh
>
>
>
> > On May 27, 2015, at 12:09 PM, Nishith Maheshwari 
> wrote:
> >
> > Hi,
> > I wanted to create a java application to connect and query over a HBase
> > database using Drill, but was unable to find any documentation regarding
> > this.
> > Is there a JAVA api through which Drill can be accessed? I did see a
> small
> > mention of C++ and JAVA api in the documentation but there was no other
> > link or information regarding the same.
> >
> > Regards,
> > Nishith Maheshwari
>
>

Re: JAVA API for Drill

[Rajkumar Singh]

Do you try drill-jdbc driver? I will suggest you to use java jdbc connectivity 
to query drill using the drill-jdbc driver.I have not tried this to query HBASE 
using drill but it should work if you have correctly
configured the HBase Storage plugin with the DRILL.

Thanks

Rajkumar Singh



> On May 27, 2015, at 12:09 PM, Nishith Maheshwari  wrote:
> 
> Hi,
> I wanted to create a java application to connect and query over a HBase
> database using Drill, but was unable to find any documentation regarding
> this.
> Is there a JAVA api through which Drill can be accessed? I did see a small
> mention of C++ and JAVA api in the documentation but there was no other
> link or information regarding the same.
> 
> Regards,
> Nishith Maheshwari