Re: Drill JDBC connection on windows in embedded node

[Andries Engelbrecht]

Try jdbc:drill:drillbit=localhost

Since embedded mode doesn’t use ZK you need to connect directly to the drillbit 
itself.

--Andries

On 8/6/17, 7:37 PM, "Divya Gehlot"  wrote:

Hi,
I followed the this link
 to
connect to drill in JDBC mode .

*Have installed drill 1.11 on windows 10 in embedded mode .*

Configurations which I used :

*JDBC Driver* :

apache-drill-1.11.0.tar\apache-drill-1.11.0\jars\jdbc-driver\drill-jdbc-all-1.11.0.jar

JDBC URL options which I tried :

*Option1 :* jdbc:drill:zk=localhost:2181/drill/drillbits1
*Option 2* :   jdbc:drill:zk=localhost:2181
*Option 3 *: jdbc:drill:zk=:2181

I am getting below error with all the  the options

*ERROR* :
Unexpected Error occurred attempting to open an SQL connection.
class java.io.IOException: Failure to connect to the zookeeper cluster
service within the allotted time of 1 milliseconds

Appreciate the help .


Thanks ,
Divya

RE: Drill JDBC connection on windows in embedded node

[Kunal Khatua]

+1 to Andries' comment. 

When you start Drill in Embedded mode, you are bringing up a single standalone 
Drillbit without any Zookeeper that would otherwise allow discovery of other 
Drillbits in the cluster. 

Starting in embedded mode is done via SQLLine because in a practical usage, a 
single Drillbit doesn't accomplish much, beyond just trying out things (Drill 
or data exploration). 
Once you have that running, then if a JDBC client needs to connect to that, you 
need to tell your JDBC client where to connect. In this case, since there is no 
Zookeeper (that, typically, listens on port 2181) that has knowledge about your 
Drillbit running in embedded-mode, we need to explicitly tell it to connect to 
the Drillbit server. 

Hence, your JDBC URL needs to be 
" jdbc:drill:drillbit=" 

Hope that helps

-Original Message-
From: Andries Engelbrecht [mailto:aengelbre...@mapr.com] 
Sent: Monday, August 07, 2017 8:17 AM
To: user@drill.apache.org
Subject: Re: Drill JDBC connection on windows in embedded node

Try jdbc:drill:drillbit=localhost

Since embedded mode doesn’t use ZK you need to connect directly to the drillbit 
itself.

--Andries

On 8/6/17, 7:37 PM, "Divya Gehlot"  wrote:

Hi,
I followed the this link
 to
connect to drill in JDBC mode .

*Have installed drill 1.11 on windows 10 in embedded mode .*

Configurations which I used :

*JDBC Driver* :

apache-drill-1.11.0.tar\apache-drill-1.11.0\jars\jdbc-driver\drill-jdbc-all-1.11.0.jar

JDBC URL options which I tried :

*Option1 :* jdbc:drill:zk=localhost:2181/drill/drillbits1
*Option 2* :   jdbc:drill:zk=localhost:2181
*Option 3 *: jdbc:drill:zk=:2181

I am getting below error with all the  the options

*ERROR* :
Unexpected Error occurred attempting to open an SQL connection.
class java.io.IOException: Failure to connect to the zookeeper cluster
service within the allotted time of 1 milliseconds

Appreciate the help .


Thanks ,
Divya

RE: unable to run drill sql in shell script

[Kunal Khatua]

You seem to be missing the jars in the classpath. 

Notice the line before the error... "Calculating Drill classpath"

What you probably need to do is to navigate to your installation's bin 
directory and launch from there. 

-Original Message-
From: Divya Gehlot [mailto:divya.htco...@gmail.com] 
Sent: Sunday, August 06, 2017 6:45 PM
To: user@drill.apache.org
Subject: Re: unable to run drill sql in shell script

yes thats the first step I tried  and when I start drill using thatI get below 
error :
*Command *:
apache-drill-1.11.0/bin/sqlline.bat -u \"jdbc:drill:zk=local\""

*Error :*

$ sh drilltest.sh
> Running query from bash
> DRILL_ARGS - " -u \"jdbc:drill:zk localhost:2181\"""
> Calculating HADOOP_CLASSPATH ...
> HBASE_HOME not detected...
> Calculating Drill classpath...
> Error: Could not find or load main class sqlline.SqlLine


Is it possible to run through git bash installed in embedded mode on windows 
machine ?


Thanks,
Divya


On 3 August 2017 at 12:18, Padma Penumarthy  wrote:

> Did you look at this link ?
>
> https://drill.apache.org/docs/starting-drill-on-windows/
>
>
> It says, to start drill on windows, do this:
> sqlline.bat -u "jdbc:drill:zk=local"
>
>
> Try that and see.
>
> Thanks,
> Padma
>
> 
> From: Divya Gehlot 
> Sent: Wednesday, August 2, 2017 8:50 PM
> To: user@drill.apache.org
> Subject: unable to run drill sql in shell script
>
> Hi,
> I have installed Drill on windows Embedded mode.
> Have a git Bash installed and I am trying to start the sqlline through 
> the shell script Below is script which I am using :
>
> > SQLLINE="/c/ApacheDrill/apache-drill-1.11.0.tar/
> apache-drill-1.11.0/bin/drill-localhost
> > -u \"jdbc:drill:zk=localhost:2181\"";
> >  $SQLLINE
>
>
> When I run above shell script it gives me below error :
>
> > Running query from bash
> > Error: Could not find or load main class sqlline.SqlLine Java 
> > HotSpot(TM) 64-Bit Server VM warning: ignoring option 
> > MaxPermSize=512M; support was removed in 8.0
>
>
> Options which I have tried are :
> 1.sqlline
> 2.drill-localhost
> 3.drill-embedded
>
> All gives the same error as above
>
> Appreciate the help.
>
>
> Thanks,
> Divya
>

RE: pass parameters to Drill query in file

[Kunal Khatua]

Drill doesn't support that, but you're welcome to implement that and contribute 
to Drill. 

A workaround would be to create your own script that takes parameters and 
writes a SQL file that will then be used by SQLLine to run.

-Original Message-
From: Divya Gehlot [mailto:divya.htco...@gmail.com] 
Sent: Sunday, August 06, 2017 6:47 PM
To: user@drill.apache.org
Subject: Re: pass parameters to Drill query in file

I am talking about the parameters in the query itself like

passing the tablename to the query or the directory parameters where the 
parquet file should be created

Thanks,
Divya

On 3 August 2017 at 12:30, Kunal Khatua  wrote:

> If you just want to run the query with the parameters like "alter 
> session", you can write those session-altering SQL as the initial set 
> of queries before the actual query in the same file.
>
> Then run something like this:
>
> /opt /drill/apache-drill-1.11.0/bin/sqlline -u "jdbc:drill:zk=local"
> --force true -f 
>
> This opens a session, and runs all the queries (separated by a semi-colon).
>
> The force flag is meant for ensuring that in the event a query within 
> the file fails, it will still continue to execute the following 
> queries in the file.
>
>
> -Original Message-
> From: Divya Gehlot [mailto:divya.htco...@gmail.com]
> Sent: Wednesday, August 02, 2017 7:30 PM
> To: user@drill.apache.org
> Subject: pass parameters to Drill query in file
>
> Hi,
> how to  pass parameters to Drill query in file?
>
> Thanks,
> Divya
>

RE: delimiter in column values

[Kunal Khatua]

You'd probably want to look at this to understand how to manage your CSV files:
https://drill.apache.org/docs/text-files-csv-tsv-psv/

The table functions I'm referring to are these and has an example that I think 
addresses your needs
https://drill.apache.org/docs/plugin-configuration-basics/#using-the-formats-attributes-as-table-function-parameters
See the preceeding table ( 
https://drill.apache.org/docs/plugin-configuration-basics/#using-the-formats-attributes-as-table-function-parameters
 ) to see other options beyond the example.



-Original Message-
From: Divya Gehlot [mailto:divya.htco...@gmail.com] 
Sent: Sunday, August 06, 2017 7:41 PM
To: user@drill.apache.org
Subject: Re: delimiter in column values

Hi,

Please find the response inline :

1. Your delimiter is a pipe in this example, and not a comma as originally seen 
in the attached file. For such seminars, either we modify the storage plugin, 
or rename the extension to 'psv' so that drill understands what the delimiter 
is.
 Delimeter the file is comma not pipe its the same file I query  in drill 
console which I shared in earlier email messages .

2. Can you try Drill-1.11.0 ?
I am using Drill 1.11.0
3. There are table functions in Drill that guide it with additional inputs on 
how to manage the preparation of the table.
Can you please share the link ?


Thanks,
Divya

On 3 August 2017 at 21:06, Kunal Khatua  wrote:

> A couple of things...
>
> 1. Your delimiter is a pipe in this example, and not a comma as 
> originally seen in the attached file. For such seminars, either we 
> modify the storage plugin, or rename the extension to 'psv' so that 
> drill understands what the delimiter is.
>
> 2. Can you try Drill-1.11.0 ?
>
> 3. There are table functions in Drill that guide it with additional 
> inputs on how to manage the preparation of the table.
>
> I'll try this in a Windows machine in the meanwhile.
>
>
> 
> From: Divya Gehlot 
> Sent: Wednesday, August 2, 2017 11:14:56 PM
> To: user@drill.apache.org
> Subject: Re: delimiter in column values
>
> Hi ,
> This is my output when run in sqlline on Windows Embedded mode
>
> 0: jdbc:drill:zk=local> select * from
> `dfs`.`installedsoftwares/ApacheDrill/apache-drill-1.10.
> 0.tar/apache-drill-1.10.0/sample-data/sample_data.csv`;
> ++---++-
> ---++
> |  col_Column1   |  Column2  |Column3 |Column4 |
>  Column5 |
> ++---++-
> ---++
> | "colonedata1"  | "coltwodata1" | "-35.924476"   | "138.5987123"  | ""
> |
> | "colonedata2"  | "coltwodata2" | "-27.4372536"  | "153.0304583"  |
> "137"  |
> | "colonedata3"  | "coltwodata3" | "-35.2793885"  | "149.1233503"  |
> "134"  |
> | "colonedata4"  | "coltwodata4" | "-33.8724176"  | "151.2067579"  | ""
> |
> | "colonedata5"  | "coltwodata5" | "" | "" | ""
> |
> | "This  |  col6 data"   | "coltwodata6"  | "-33.869732"   |
> "151.203"  |
> | "This  |  col7 data yes."  | "coltwodata7"  | "1.2845045"|
> "103.8482739"  |
> | "Chifley"  | "coltwodata5" | "" | "" | ""
> |
> ++---++-
> ---++
> 8 rows selected (0.147 seconds)
> 0: jdbc:drill:zk=local> select `col_Column1` from 
> `dfs`.`installedsoftwares/ApacheDrill/apache-drill-1.10.
> 0.tar/apache-drill-1.10.0/sample-data/sample_data.csv`;
> ++
> |  col_Column1   |
> ++
> | "colonedata1"  |
> | "colonedata2"  |
> | "colonedata3"  |
> | "colonedata4"  |
> | "colonedata5"  |
> | "This  |
> | "This  |
> | "Chifley"  |
> ++
> 8 rows selected (0.1 seconds)
>
>
> The query returning the different results due to host operating system?
>
>
> Thanks,
> Divya
>
> On 3 August 2017 at 12:45, Kunal Khatua  wrote:
>
> > Based on your sample data, which contains this:
> > Column1,Column2,Column3,Column4,Column5
> > "colonedata1","coltwodata1","-35.924476","138.5987123",""
> > "colonedata2","coltwodata2","-27.4372536","153.0304583","137"
> > colonedata3","coltwodata3","-35.2793885","149.1233503","134"
> > "colonedata4","coltwodata4","-33.8724176","151.2067579",""
> > "colonedata5","coltwodata5","","",""
> > "This, col6 data","coltwodata6","-33.869732","151.203","351"
> > "This, col7 data yes.","coltwodata7","1.2845045","103.8482739","80"
> > "Chifley","coltwodata5","","",""
> >
> > I got this and it looks like this...
> >
> >
> > 0: jdbc:drill:schema=dfs.root> select * from `sample_data.csv`;
> > +---
> -+
> > |columns
>  |
> > +---
> -+

Re: Drill JDBC connection on windows in embedded node

[Divya Gehlot]

Thanks a lot Kunal and Andries.
It worked :)

On 8 August 2017 at 02:26, Kunal Khatua  wrote:

> +1 to Andries' comment.
>
> When you start Drill in Embedded mode, you are bringing up a single
> standalone Drillbit without any Zookeeper that would otherwise allow
> discovery of other Drillbits in the cluster.
>
> Starting in embedded mode is done via SQLLine because in a practical
> usage, a single Drillbit doesn't accomplish much, beyond just trying out
> things (Drill or data exploration).
> Once you have that running, then if a JDBC client needs to connect to
> that, you need to tell your JDBC client where to connect. In this case,
> since there is no Zookeeper (that, typically, listens on port 2181) that
> has knowledge about your Drillbit running in embedded-mode, we need to
> explicitly tell it to connect to the Drillbit server.
>
> Hence, your JDBC URL needs to be
> " jdbc:drill:drillbit="
>
> Hope that helps
>
> -Original Message-
> From: Andries Engelbrecht [mailto:aengelbre...@mapr.com]
> Sent: Monday, August 07, 2017 8:17 AM
> To: user@drill.apache.org
> Subject: Re: Drill JDBC connection on windows in embedded node
>
> Try jdbc:drill:drillbit=localhost
>
> Since embedded mode doesn’t use ZK you need to connect directly to the
> drillbit itself.
>
> --Andries
>
> On 8/6/17, 7:37 PM, "Divya Gehlot"  wrote:
>
> Hi,
> I followed the this link
> 
> to
> connect to drill in JDBC mode .
>
> *Have installed drill 1.11 on windows 10 in embedded mode .*
>
> Configurations which I used :
>
> *JDBC Driver* :
> apache-drill-1.11.0.tar\apache-drill-1.11.0\jars\jdbc-
> driver\drill-jdbc-all-1.11.0.jar
>
> JDBC URL options which I tried :
>
> *Option1 :* jdbc:drill:zk=localhost:2181/drill/drillbits1
> *Option 2* :   jdbc:drill:zk=localhost:2181
> *Option 3 *: jdbc:drill:zk=:2181
>
> I am getting below error with all the  the options
>
> *ERROR* :
> Unexpected Error occurred attempting to open an SQL connection.
> class java.io.IOException: Failure to connect to the zookeeper cluster
> service within the allotted time of 1 milliseconds
>
> Appreciate the help .
>
>
> Thanks ,
> Divya
>
>
>

[ERROR]:unable to start drill in distributed mode in windows server 2012

[Divya Gehlot]

Hi,
I have installed Drill 1.11 in dustributed mode in windows server 2012

*Below are confiiguration of my drill-overide.conf file :*

drill.exec: {
>   cluster-id: "drilldistmode",
>   zk.connect: "NameNode:2181,DataNode1:2181,DataNode2:2181"
> }


*Start Drill *
*Start mode* : git bash command line
*command* : sh drillbit.sh start

*Tested :*
Zookeeper running fine

*Error in drillbit.out :*
Error: Could not find or load main class
org.apache.drill.exec.server.Drillbit



Thanks,
Divya