Re: Query Compilation error with 80+ CASE statements

[Arjun kr]

Rahul,

You can try setting system option 'exec.java.compiler.exp_in_method_size' to 
lesser value from the default of 50 if you haven't tried already and see if it 
succeeds.


alter session set `exec.java.compiler.exp_in_method_size` = ;

Thanks,

Arjun


From: Abhishek Girish 
Sent: Thursday, February 28, 2019 5:55 AM
To: user
Subject: Re: Query Compilation error with 80+ CASE statements

Rahul,

Can you please share plans for both queries (one with fewer which succeeds
and one which fails). Also the verbose error.

On Tue, Feb 26, 2019 at 11:33 PM Rahul Raj  wrote:

> Some more update to the mail above:
>
> The query above has a UDF 'checkNull' used . The UDF code is placed inside
> the compiled query code, causing it to fail where there are more case
> statements. The below snippet is from the UDF.
>
> {
> if (input.end - input.start == 0) {
> throw new RuntimeException("IllegalArgumentException : null values
> in non nullable fields");
> } else
> {
> out = input;
> }
> }
>
> Any thoughts on this? Are there any naming conventions while developing a
> UDF?
>
> Regards,
> Rahul
>
>
>
> On Wed, Feb 27, 2019 at 12:14 PM Rahul Raj  wrote:
>
> > Hi,
> >
> > I am getting compilation error on Drill 1.15 when query contains a large
> > number of case statements. I have included the query below. Query works
> > fine when few case statements are removed.
> >
> > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
> > CompileException: File
> >
> 'org.apache.drill.exec.compile.DrillJavaFileObject[ProjectorGen8635.java]',
> > Line 872, Column 9: ProjectorGen8635.java:872: error: cannot assign a
> value
> > to final variable out out = input; ^
> > (compiler.err.cant.assign.val.to.final.var) Fragment 0:0 Please, refer to
> > logs for more information. [Error Id:
> a0d3f054-7c60-4915-9629-55e5dacd8606
> > on jiffydemo:31010]
> >
> > Query is :
> >
> > SELECT
> >  CAST(`A1` AS INT) `A1`
> >, CAST(`A2` AS INT) `A2`
> >,  `A3`
> >,  `A4`
> >,  `A5`
> >, (CASE WHEN (`A6` = '') THEN null ELSE `A6` END) `A6`
> >,  `A7`
> >,  `A8`
> >,  `A9`
> >,  `A10`
> >, CAST(A11 AS INT) `A11`
> >, (CASE WHEN (`A12` = '') THEN null ELSE `A12` END) `A12`
> >, CAST(`checkNull`(`A13`) AS INT) `A13`
> >, CAST(`checkNull`(`A14`) AS INT) `A14`
> >, (CASE WHEN (`A15` = '') THEN null ELSE `A15` END) `A15`
> >, CAST(`checkNull`(`A16`) AS INT) `A16`
> >, CAST(`checkNull`(`A17`) AS INT) `A17`
> >, CAST(`checkNull`(`A18`) AS INT) `A18`
> >, (CASE WHEN (`A19` = '') THEN null ELSE `A19` END) `A19`
> >,  `A20`
> >,  `A21`
> >,  `A22`
> >, (CASE WHEN (`_1` = '') THEN null ELSE `_1` END) `_1`
> >, (CASE WHEN (`_2` = '') THEN null ELSE `_2` END) `_2`
> >, (CASE WHEN (`_3` = '') THEN null ELSE `_3` END) `_3`
> >, (CASE WHEN (`_4` = '') THEN null ELSE `_4` END) `_4`
> >, (CASE WHEN (`_5` = '') THEN null ELSE `_5` END) `_5`
> >, (CASE WHEN (`_6` = '') THEN null ELSE `_6` END) `_6`
> >, (CASE WHEN (`_7` = '') THEN null ELSE `_7` END) `_7`
> >, (CASE WHEN (`_8` = '') THEN null ELSE `_8` END) `_8`
> >, (CASE WHEN (`_9` = '') THEN null ELSE `_9` END) `_9`
> >, (CASE WHEN (`_10` = '') THEN null ELSE `_10` END) `_10`
> >, (CASE WHEN (`_11` = '') THEN null ELSE `_11` END) `_11`
> >, (CASE WHEN (`_12` = '') THEN null ELSE `_12` END) `_12`
> >, (CASE WHEN (`_13` = '') THEN null ELSE `_13` END) `_13`
> >, (CASE WHEN (`_14` = '') THEN null ELSE `_14` END) `_14`
> >, (CASE WHEN (`_15` = '') THEN null ELSE `_15` END) `_15`
> >, (CASE WHEN (`_16` = '') THEN null ELSE `_16` END) `_16`
> >, (CASE WHEN (`_17` = '') THEN null ELSE `_17` END) `_17`
> >, (CASE WHEN (`_18` = '') THEN null ELSE `_18` END) `_18`
> >, (CASE WHEN (`_19` = '') THEN null ELSE `_19` END) `_19`
> >, (CASE WHEN (`_20` = '') THEN null ELSE `_20` END) `_20`
> >, (CASE WHEN (`_21` = '') THEN null ELSE `_21` END) `_21`
> >, (CASE WHEN (`_22` = '') THEN null ELSE `_22` END) `_22`
> >, (CASE WHEN (`_23` = '') THEN null ELSE `_23` END) `_23`
> >, (CASE WHEN (`_24` = '') THEN null ELSE `_24` END) `_24`
> >, (CASE WHEN (`_25` = '') THEN null ELSE `_25` END) `_25`
> >, (CASE WHEN (`_26` = '') THEN null ELSE `_26` END) `_26`
> >, (CASE WHEN (`_27` = '') THEN null ELSE `_27` END) `_27`
> >, (CASE WHEN (`_28` = '') THEN null ELSE `_28` END) `_28`
> >, (CASE WHEN (`_29` = '') THEN null ELSE `_29` END) `_29`
> >, (CASE WHEN (`_30` = '') THEN null ELSE `_30` END) `_30`
> >, (CASE WHEN (`_31` = '') THEN null ELSE `_31` END) `_31`
> >, (CASE WHEN (`_32` = '') THEN null ELSE `_32` END) `_32`
> >, (CASE WHEN (`_33` = '') THEN null ELSE `_33` END) `_33`
> >, (CASE WHEN (`_34` = '') THEN null ELSE `_34` END) `_34`
> >, (CASE WHEN (`_35` = '') THEN null ELSE `_35` END) `_35`
> >, (CASE WHEN (`_36` = '') THEN null ELSE

Re: HDFS storage prefix returning Error: VALIDATION ERROR: null

[Arjun kr]

Just wanted to confirm on the name node URI. Can you verify if 8020 is your 
namenode ipc port? May be you can run 'hadoop fs -ls
hdfs://host18-namenode:8020/tmp' and verify it?

Get Outlook for Android



From: Abhishek Girish
Sent: Tuesday, February 12, 11:37 PM
Subject: Re: HDFS storage prefix returning Error: VALIDATION ERROR: null
To: user@drill.apache.org


I meant for you to run
show files in hdfs.tmp

But it looks like the plugin might not be initialized correctly (check if
the hostname provided in the connection string can be resolved)

Or you may not have used the right user when launching sqlline (user may
not have permissions on the hdfs root dir or somewhere in the file path).

On Tue, Feb 12, 2019 at 10:57 PM Krishnanand Khambadkone
 wrote:

>  The command show files in dfs.tmp does return the right output.
> However when I try to run a simple hdfs query
> select
> s.application_id  from 
> hdfs.`/user/hive/spark_data/dt=2019-01-25/part-4-ae91cbe2-5410-4bec-ad68-10a053fb2b68.json`
> it returns,
>
> Error: VALIDATION ERROR: Schema [[hdfs]] is not valid with respect to
> either root schema or current default schema.
>
>
> On Tuesday, February 12, 2019, 5:10:57 PM PST, Abhishek Girish <
> agir...@apache.org> wrote:
>
>  Can you please share the full error message (please see [1])
>
> Also, can you please see if this works: show files in dfs.tmp; This is to
> check if the DFS plugin is successfully initialized and Drill can see the
> files on HDFS. And if that works, check if simpler queries on the data
> works: select * from hdfs.``
>
> [1] https://drill.apache.org/docs/troubleshooting/#enable-verbose-errors
>
> On Tue, Feb 12, 2019 at 4:38 PM Krishnanand Khambadkone
>  wrote:
>
> >  Here is the hdfs storage definition and query I am using.  Same query
> > runs fine if run off local filesystem with dfs storage prefix.  All I am
> > doing is swapping dfs for hdfs.
> >
> > {
> >
> >  "type": "file",
> >
> >  "connection": "hdfs://host18-namenode:8020/",
> >
> >  "config": null,
> >
> >  "workspaces": {
> >
> >"tmp": {
> >
> >  "location": "/tmp",
> >
> >  "writable": true,
> >
> >  "defaultInputFormat": null,
> >
> >  "allowAccessOutsideWorkspace": false
> >
> >},
> >
> >"root": {
> >
> >  "location": "/",
> >
> >  "writable": false,
> >
> >  "defaultInputFormat": null,
> >
> >  "allowAccessOutsideWorkspace": false
> >
> >}
> >
> >  },
> >
> >  "formats": null,
> >
> >  "enabled": true
> >
> > }
> >
> >
> >
> >
> > select s.application_id,
> > get_spark_attrs(s.spark_event,'spark.executor.memory') as
> spark_attributes
> >  from
> >
> hdfs.`/user/hive/spark_data/dt=2019-01-25/part-4-ae91cbe2-5410-4bec-ad68-10a053fb2b68.json`
> > s where (REGEXP_REPLACE(REGEXP_REPLACE(substr(s.spark_event,11),
> > '[^0-9A-Za-z]"', ''),'(".*)','') = 'SparkListenerEnvironmentUpdate' or
> > REGEXP_REPLACE(REGEXP_REPLACE(substr(s.spark_event,11), '[^0-9A-Za-z]"',
> > ''),'(".*)','') = 'SparkListenerApplicationStart' or
> > REGEXP_REPLACE(REGEXP_REPLACE(substr(s.spark_event,11), '[^0-9A-Za-z]"',
> > ''),'(".*)','') = 'SparkListenerApplicationEnd') group by application_id,
> > spark_attributes  order by application_id;
> >
> >
> >
> >On Tuesday, February 12, 2019, 3:04:40 PM PST, Abhishek Girish <
> > agir...@apache.org> wrote:
> >
> >  This message is eligible for Automatic Cleanup! (agir...@apache.org)
> Add
> > cleanup rule | More info
> >  Hey Krishnanand,
> >
> > As mentioned by other folks in earlier threads, can you make sure to
> > include ALL RELEVANT details in your emails? That includes the query,
> > storage plugin configuration, data format, sample data / description of
> the
> > data, the full log for the query failure? It's necessary if one needs to
> be
> > able to understand the issue or offer help.
> >
> > Regards,
> > Abhishek
> >
> > On Tue, Feb 12, 2019 at 2:37 PM Krishnanand Khambadkone
> >  wrote:
> >
> > > I have defined a hdfs storage type with all the required properties.
> > > However, when I try to use that in the query it returns
> > > Error: VALIDATION ERROR: null
> > >
> >
>

Re: DoY - PAM Auth only reading YARN account.

[Arjun kr]

Is DoY user ( used to start DoY client) part of shadow group incase you are 
using PAM with /etc/passwd for authentication? If not, can you try adding and 
see if it helps?

>From doc:
If you use PAM with /etc/passwd for authentication, verify that the users with 
permission to start the Drill process are part of the shadow user group on all 
nodes in the cluster. This enables Drill to read the /etc/shadow file for 
authentication.

Thanks,
Arjun

Get Outlook for Android


From: Michael Dennis Uanang 
Sent: Thursday, January 24, 2019 8:37:29 PM
To: user@drill.apache.org
Subject: DoY - PAM Auth only reading YARN account.

Hi

I have successfully setup Drill-on-YARN along side with my hadoop cluster. Now, 
I enabled HTTP drill security by using PAM authentication method.
My problem is, every time i tried to login using SSH accounts, it's always 
giving me `invalid user name or password` while using the correct credentials.

Also filed ticket for this... https://issues.apache.org/jira/browse/DRILL-6983

---
Best regards,

MD

Re: Drill connection using jdbc

[Arjun kr]

Yes. I believe its a necessary parameter if non default cluster I'd is used and 
given in the connection url. You can check if it works with directory specified.

Get Outlook for Android<https://aka.ms/ghei36>


From: Tushar Pathare 
Sent: Tuesday, January 8, 2019 11:40:22 PM
To: user@drill.apache.org
Subject: Re: Drill connection using jdbc

Is it necessary because I thought it is optional including the cluster ID


Tushar Bhalchandra Pathare
Bigdata,GPFS,CSXF – Research Department
t. +974 4003 7443
m. +974 74793547
PO BOX 26999 Doha, Qatar
sidra.org <http://www.sidra.org/>


On 1/9/19, 10:39 AM, "Arjun kr"  wrote:

Are you missing drill zk directory in connection url?
By default, it's value is drill.

jdbc:drill:zk=[:][,[:]...
/;[schema=]



https://drill.apache.org/docs/using-the-<https://drill.apache.org/docs/using-the-jdbc-driver/>jdbc<https://drill.apache.org/docs/using-the-jdbc-driver/>-driver/<https://drill.apache.org/docs/using-the-jdbc-driver/>

Thanks,

Arjun

Get Outlook for Android<https://aka.ms/ghei36>


From: Khurram Faraaz 
Sent: Tuesday, January 8, 2019 11:17:58 PM
To: user@drill.apache.org
Subject: Re: Drill connection using jdbc

Hi Tushar,

This example works just fine for me, you may want to use the template

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.sql.*;
import java.io.*;
import java.lang.*;
import java.util.*;
import java.util.Scanner;

public class TestQuery {

public static void main(String s[]) throws Exception {

Connection conn = null;
PreparedStatement prdstmnt = null;
try {
// Please replace test1302-145 with your hostname, name of host
where Drillbit is up and running
final String URL_STRING =
"jdbc:drill:schema=dfs.tmp;drillbit=test1302-145";
Class.forName("org.apache.drill.jdbc.Driver").newInstance();

// Please replace username and password with your username and
password
conn = DriverManager.getConnection(URL_STRING,"mapr","mapr");
prdstmnt = conn.prepareStatement("SELECT * FROM
cp.`employee.json`");
prdstmnt.executeQuery();
prdstmnt.close();
} catch (Exception e) {
   System.out.println(e.getMessage());
   e.printStackTrace();
} finally {
if(conn != null)
try { conn.close(); } catch(SQLException sqlexcpn){
sqlexcpn.printStackTrace(); }
if(prdstmnt != null)
try { prdstmnt.close(); } catch(SQLException sqlexcpn){
sqlexcpn.printStackTrace(); }
}
}
}

Regards,
Khurram

On Tue, Jan 8, 2019 at 11:10 PM Tushar Pathare  wrote:

> Hello Team,
>
> We have installed drill 1.12.0 and trying to connect
> using a client to the drill cluster
>
> Our config for connection is
>
>
>
> Drill class is : org.apache.drill.jdbc.Driver
>
> Driver location is
> 
/Users/tpathare/Downloads/apache-drill-1.12.0/jars/jdbc-driver/drill-jdbc-all-1.12.0.jar
>
> Jdbc url is : jdbc:drill:zk=172.32.63.64:2181,172.32.63.65:2181,
> 172.32.63.67:2181
>
>
>
> When I hit connect I see the following error.Though the cluster is up and
> running and ZK is also good.
>
>
>
> *ERROR: An error occurred while trying to make a connection to *
>
> *the database:  *
>
>
>
> *JDBC URL: *
>
> *jdbc:drill:zk=172.32.63.64:2181
> <http://172.32.63.64:2181>,172.32.63.65:2181
> <http://172.32.63.65:2181>,172.32.63.67:2181/sidra-drillbit
> <http://172.32.63.67:2181/sidra-drillbit> *
>
>
>
> *Failure in connecting to Drill: *
>
> *oadd.org.apache.drill.exec.rpc.RpcException: Failure setting up *
>
> *ZK for client.*
>
>
>
>
>
>
>
> [image: cid:image001.png@01D3B9E9.23323D90]
>
> *Tushar Bhalchandra Pathare*
>
> Bigdata,GPFS,CSXF – Research Department
>
> *t. *+974 4003 7443
>
> *m.* +974 74793547
>
> PO BOX 26999 Doha, Qatar
>
> sidra.org
> 
<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.sidra.org_&d=DwMGaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=H5JEl9vb-mBIjic10QAbDD2vkUUKAxjO6wZO322RtdI&m=xCohfm7_vub0ExT6cMTEbB4Wbjd

Re: Drill connection using jdbc

[Arjun kr]

Are you missing drill zk directory in connection url?
By default, it's value is drill.

jdbc:drill:zk=[:][,[:]...
/;[schema=]


https://drill.apache.org/docs/using-the-jdbc-driver/

Thanks,

Arjun

Get Outlook for Android


From: Khurram Faraaz 
Sent: Tuesday, January 8, 2019 11:17:58 PM
To: user@drill.apache.org
Subject: Re: Drill connection using jdbc

Hi Tushar,

This example works just fine for me, you may want to use the template

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.sql.*;
import java.io.*;
import java.lang.*;
import java.util.*;
import java.util.Scanner;

public class TestQuery {

public static void main(String s[]) throws Exception {

Connection conn = null;
PreparedStatement prdstmnt = null;
try {
// Please replace test1302-145 with your hostname, name of host
where Drillbit is up and running
final String URL_STRING =
"jdbc:drill:schema=dfs.tmp;drillbit=test1302-145";
Class.forName("org.apache.drill.jdbc.Driver").newInstance();

// Please replace username and password with your username and
password
conn = DriverManager.getConnection(URL_STRING,"mapr","mapr");
prdstmnt = conn.prepareStatement("SELECT * FROM
cp.`employee.json`");
prdstmnt.executeQuery();
prdstmnt.close();
} catch (Exception e) {
   System.out.println(e.getMessage());
   e.printStackTrace();
} finally {
if(conn != null)
try { conn.close(); } catch(SQLException sqlexcpn){
sqlexcpn.printStackTrace(); }
if(prdstmnt != null)
try { prdstmnt.close(); } catch(SQLException sqlexcpn){
sqlexcpn.printStackTrace(); }
}
}
}

Regards,
Khurram

On Tue, Jan 8, 2019 at 11:10 PM Tushar Pathare  wrote:

> Hello Team,
>
> We have installed drill 1.12.0 and trying to connect
> using a client to the drill cluster
>
> Our config for connection is
>
>
>
> Drill class is : org.apache.drill.jdbc.Driver
>
> Driver location is
> /Users/tpathare/Downloads/apache-drill-1.12.0/jars/jdbc-driver/drill-jdbc-all-1.12.0.jar
>
> Jdbc url is : jdbc:drill:zk=172.32.63.64:2181,172.32.63.65:2181,
> 172.32.63.67:2181
>
>
>
> When I hit connect I see the following error.Though the cluster is up and
> running and ZK is also good.
>
>
>
> *ERROR: An error occurred while trying to make a connection to *
>
> *the database:  *
>
>
>
> *JDBC URL: *
>
> *jdbc:drill:zk=172.32.63.64:2181
> ,172.32.63.65:2181
> ,172.32.63.67:2181/sidra-drillbit
>  *
>
>
>
> *Failure in connecting to Drill: *
>
> *oadd.org.apache.drill.exec.rpc.RpcException: Failure setting up *
>
> *ZK for client.*
>
>
>
>
>
>
>
> [image: cid:image001.png@01D3B9E9.23323D90]
>
> *Tushar Bhalchandra Pathare*
>
> Bigdata,GPFS,CSXF – Research Department
>
> *t. *+974 4003 7443
>
> *m.* +974 74793547
>
> PO BOX 26999 Doha, Qatar
>
> sidra.org
> 
>
>
> Disclaimer: This email and its attachments may be confidential and are
> intended solely for the use of the individual to whom it is addressed. If
> you are not the intended recipient, any reading, printing, storage,
> disclosure, copying or any other action taken in respect of this e-mail is
> prohibited and may be unlawful. If you are not the intended recipient,
> please notify the sender immediately by using the reply function and then
> permanently delete what you have received. Any views or opinions expressed
> are solely those of the author and do not necessarily represent those of
> Sidra Medical and Research Center.
>

Re: how to set drill planner parameter in drill-override.conf

[Arjun kr]

This option can be set at system or session level using alter system/session 
set command.The sys.options table lists options that you can set at the system 
or session level.

https://drill.apache.org/docs/planning-and-execution-options/

Thanks,

Arjun

Get Outlook for Android


From: 王亮 
Sent: Friday, November 9, 2018 10:30:15 PM
To: user@drill.apache.org
Subject: how to set drill planner parameter in drill-override.conf

I want to change the planner parameter according to
https://drill.apache.org/docs/configuration-options-introduction/

for example ,set  planner.slice_target = 1

I add one line in drill-override.conf

planner.slice_target = 1

or
planner : {
  slice_target:1000
}

I run the query:
select * from sys.options where name in (''planner.slice_target');

but I find this parameter is still the default value 10.

So how to change this value in  drill-override.conf?

Re: Join tables from different databases returns empty result

[Arjun kr]

Does it return any result if you query on individual tables with the 
corresponding filter applied in Drill?

Thanks,

Arjun

From: Khurram Faraaz 
Sent: Wednesday, November 7, 2018 5:34 AM
To: user@drill.apache.org
Cc: om...@intertrust.com
Subject: Re: Join tables from different databases returns empty result

Hi,

You will have to create a new storage plugin from Drill's Web UI, you can
find the details on the below documentation link, to query MySQL tables
from Apache Drill.
https://drill.apache.org/docs/rdbms-storage-plugin/

Also please share the query plan if you can generate one using EXPLAIN FOR
command, for that join query from Drill.

Thanks,
Khurram

On Tue, Nov 6, 2018 at 3:58 PM Prisdha Dharma 
wrote:

> Hi,
>
> I'm using Apache Drill to join 2 tables from different databases on MySql
> server
> When running this query:
> SELECT a.name, b.url
> FROM pg_authservice.partner a
> INNER JOIN pg-edge.graph_apis b
> N a.id = b.pid
> WHEN a.status = 1 && b.job_status = “COMPLETED”;
>
> I see that Drill returns empty result, even though the join using MySQL
> returns non-empty data.
> Could you please advise.
>
> Prisdha Dharma
>
>
>

Re: Hbase tables in apache drill not showing up

[Arjun kr]

Do you have Zookeeper service running on Drillbit nodes? Try below command from 
the drillbit node.

echo ruok | nc localhost 2181

Please make sure Zookeeper connection settings in storage plugin definition are 
in sync with the settings in hbase-site.xml used by HBase services.

Thanks,

Arjun






From: Khurram Faraaz 
Sent: Monday, October 29, 2018 11:04 PM
To: user@drill.apache.org
Subject: Re: Hbase tables in apache drill not showing up

Hi,

What is the version of Drill and HBase that you are using ?, Do you see any
errors in drillbit.log file ?

You will have to enable the HBase storage plugin on Drill web UI, and
ensure it is enabled

 {
  "type": "hbase",
  "config": {
"hbase.zookeeper.quorum": "1.2.3.12",
"hbase.zookeeper.property.clientPort": "2181"
  },
  "size.calculator.enabled": false,
  "enabled": true
}


The to query HBase tables you will have to specify the schema in sqlline,
like

sqlline> use hbase;
sqlline> SELECT * FROM ;

Thanks,
Khurram

On Mon, Oct 29, 2018 at 9:46 AM Schwendimann Thomas I.BSCWI.1701 <
thomas.schwendim...@stud.hslu.ch> wrote:

> Dear Sir or Madam,
>
> I am trying to use apache drill to query an hbase table. I uploaded a
> table "users" through pig. When checking through the hbase shell, I can
> confirm that the table "users" is there and filled with my data. However,
> on apache drill there is no hbase table when using the query "show
> databases". All my hive and mongoDB tables show up however.
>
> These are my configurations:
>
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__i.imgur.com_147RcUz.png&d=DwIFAg&c=cskdkSMqhcnjZxdQVpwTXg&r=H5JEl9vb-mBIjic10QAbDD2vkUUKAxjO6wZO322RtdI&m=PgdrMUUqChHV8C7nJXcgmZkgP5bOp-e5FzhToQSnWEM&s=7c-Sdd-CwRsDuo5TCE4PyH8LPHquQ5wMhGScGbk8wgw&e=
>
> If you can help me out here it's greatly appreciated.
>
> Thanks in advance.
>
> Thomas
>

Re: Login Failure investigation

[Arjun kr]

Do you see any authentication failure message in
/var/log/secure file?

Also you may use pamtester utility to check if you see similar behavior.

# to check sudo profile for user 'user1'
pamtester -v sudo 'user1' 'authenticate'

http://pamtester.sourceforge.net


Get Outlook for Android



From: Divya Gehlot
Sent: Tuesday, August 7, 8:27 PM
Subject: Login Failure investigation
To: user@drill.apache.org


Hi , Appreciate the help on login failure issues . which login configuration 
should I enable to get the RCA of some of the users login failures ? Thanks, 
Divya On Tue, 7 Aug 2018 at 16:41, Divya Gehlot wrote: > Hi, > I have 
distributed drill (6 nodes) connected to AD and some of the users > are able to 
connect to drill after multiple tries whereas some are able to > connect in 
first attempt itself . > Can anybody please share the best practice to 
investigate this kind of > issue ? > > > Thanks, > Divya >

Re: difference between cast as timestamp and to_timestamp

[Arjun kr]

TO_TIMESTAMP function accepts epoch timestamp in seconds. Whereas cast to 
Timestamp seems to be expecting value in milliseconds.



0: jdbc:drill:> select TO_TIMESTAMP(1530601200049/1000) from (values(1));
++
| EXPR$0 |
++
| 2018-07-03 00:00:00.0  |
++
1 row selected (0.297 seconds)
0: jdbc:drill:>

Thanks,

Arjun


From: Divya Gehlot 
Sent: Tuesday, July 3, 2018 8:51 PM
To: user@drill.apache.org
Subject: difference between cast as timestamp and to_timestamp

Hi,
Below gives me different values
Query 1:
select CAST(1530601200049 AS TimeStamp) from (values(1));
EXPR$0
2018-07-03T07:00:00.049-05:00

Query 2:
select TO_TIMESTAMP(1530601200049) from (values(1));
Apache Drill
50472-10-26T11:00:49.000-05:00

Query 3 :
select TO_TIMESTAMP(1530601200049,'-MM-dd HH:mm:ss') from (values(1));
Error :
Apache Drill
org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
IllegalArgumentException: Invalid format: "1530601200049" is malformed at
"0049" [Error Id: 03ac470f-e959-41c4-beba-5f3827815709 on :31010]

Why get I different values?

Thanks,
Divya

Re: How to dynamically add months in Apache drill

[Arjun kr]

One way to do this is given below. This requires interval expression to be 
passed to the DATE_ADD function.

0: jdbc:drill:> select DATE_ADD(date '2015-05-15', interval '1' month) 
with_literal,  DATE_ADD(date '2015-05-15',cast(concat('P',val,'M') as interval 
month)) with_column_value  from (select 1 as val from (values(1))) a;
+++
|  with_literal  |   with_column_value|
+++
| 2015-06-15 00:00:00.0  | 2015-06-15 00:00:00.0  |
+++
1 row selected (0.21 seconds)
0: jdbc:drill:>


https://drill.apache.org/docs/data-type-conversion/#casting-intervals

https://drill.apache.org/docs/date-time-and-timestamp/#intervalyear-and-intervalday

https://drill.apache.org/docs/date-time-functions-and-arithmetic/#date_add

Thanks,

Arjun



From: Bharani Manickam 
Sent: Tuesday, July 3, 2018 8:27 PM
To: user@drill.apache.org
Subject: How to dynamically add months in Apache drill

Hello,

DATE_ADD function doesn't support a column as the interval argument in drill 
queries.

We have a requirement to pass a column as Interval Month to derive a forecasted 
date. Do you have any work around for this please?

The requirement is something like this -


Query that works
select
  a,
  DATE_ADD(date '2015-05-15', interval '1' month)
from
(
select '1' a, INSERTED_AT from dfs.data.bi_interaction limit 3
);

Query that doesn't work

select

  a,

  DATE_ADD(date '2015-05-15', interval a month)

from

(

 select '1' a, INSERTED_AT from dfs.data.bi_interaction limit 3

);



Thanks
Bharani

Re: How to Start Drill Service From Linux Non-root user

[Arjun kr]

If it's a multi node drillbit cluster, do you have this path/mount available on 
all drillbit nodes ?

Get Outlook for Android



From: Surneni Tilak
Sent: Thursday, May 31, 7:06 AM
Subject: RE: How to Start Drill Service From Linux Non-root user
To: user@drill.apache.org


I am able to create table in tmp work space. Best regards, 
_ Tilak Surneni -Original Message- From: Divya 
Gehlot [mailto:divya.htco...@gmail.com] Sent: Thursday, May 31, 2018 3:58 PM 
To: user@drill.apache.org Subject: Re: How to Start Drill Service From Linux 
Non-root user can you try creating table in tmp workspace ? On 31 May 2018 at 
20:51, Surneni Tilak wrote: > Hi Divya, > > I am getting below error message. > 
> org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > 
IOException: Mkdirs failed to create /mapr/nscprod01.ericsson.com/ > 
NSC-TEST/stk/tpcdsView/web_returns (exists=false, > 
cwd=file:/usr/local/drill/bin) > > > Best regards, > _ 
> Tilak Surneni > > -Original Message- > From: Divya Gehlot 
[mailto:divya.htco...@gmail.com] > Sent: Thursday, May 31, 2018 12:45 PM > To: 
user@drill.apache.org > Subject: Re: How to Start Drill Service From Linux 
Non-root user > > It worked for me as I am also non root user and login drill 
through AD . > And able to write whereever I would like to on data disk just to 
need > give permission to that directory . > It’s sounds strange that it 
doesn’t work for you . > > Thanks, > Divya > > On Thu, 31 May 2018 at 6:03 PM, 
Surneni Tilak > > wrote: > > > Hi Divya, > > > > I am writing to local file 
system, So I should use file:/// ; But I > > tried the option that you have 
mentioned it's not working. > > > > > > > > Best regards, > > 
_ > > Tilak Surneni > > > > -Original Message- 
> > From: Divya Gehlot [mailto:divya.htco...@gmail.com] > > Sent: Thursday, May 
31, 2018 11:15 AM > > To: user@drill.apache.org > > Subject: Re: How to Start 
Drill Service From Linux Non-root user > > > > From the plugin information it 
looks like you are using mapr cluster . > > Can you change below : > > 
"connection": "maprfs:///", > > > > Everything else looks good. > > > > > > 
Thanks, > > Divya > > > > > > On 31 May 2018 at 16:42, Surneni Tilak > wrote: > 
> > > > Hi Sorabh, > > > > > > Please find the attached storage plugin 
information. I am changing > > > my workspace correctly which I have verified 
from web UI before > > > running my query. > > > I am using the workspace 
tpcdsView as mentioned in the storage > > > plugin; where I would like to 
create the table and the Query user > > > has write permissions to this path. 
Here I am trying to write to > > > NFS > mount path. > > > > > > Query that I 
am running: > > > > > > create table stk.tpcdsView.`web_returns` as select * 
from > > > stk.`/mapr/ > > > 
nscprod01.ericsson.com/NSC-TEST/stk/tpcdsView/web_returns_csv1000` > > > v1 > > 
000> > > > limit 20; > > > > > > Best regards, > > > _ 
> > > Tilak Surneni > > > > > > > > > -Original Message- > > > From: 
Sorabh Hamirwasia [mailto:shamirwa...@mapr.com] > > > Sent: Wednesday, May 30, 
2018 10:47 PM > > > To: user@drill.apache.org > > > Subject: Re: How to Start 
Drill Service From Linux Non-root user > > > > > > Hi Surneni, > > > > > > Can 
you please share few informations: > > > > > > 1) Please share the query and 
filesystem storage plugin configuration. > > > > > > 2) What is your current 
workspace while executing the query ? > > > > > > > > > Thanks, > > > Sorabh > 
> > > > >  > > > From: Surneni Tilak > > > 
Sent: Wednesday, May 30, 2018 2:05:22 AM > > > To: user@drill.apache.org > > > 
Subject: RE: How to Start Drill Service From Linux Non-root user > > > > > > Hi 
Sorabh, > > > > > > Thanks for your response. I am asking about running queries 
as > > > Query user and I have enabled user impersonation. But when I am > > > 
trying to create a table from query user in the path where the > > > query user 
has full > > permissions > > > it's not getting created and facing the below 
error. So I would like > to > > > know while creating tables will drill try to 
create the tables as > > > process user or logged in user ? > > > > > > Error 
Message While creating Table: > > > > > > 
org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > > > 
IOException: Mkdirs failed to create > > > 
/mapr/my.dev.cluster/data/stk/tpcdsParquet/web_returns > > > (exists=false, 
cwd=file:/usr/local/drill/bin) > > > > > > When I browsed regarding the above 
error message I found other > > > people got similar kind of error message 
because of space issue, > > > So I tried to write only few records even then 
same error is repeating. > Please help. > > > > > > > > > > > > Best regards, > 
> > _ > > > Tilak Surneni > > > > > > > > > 
-Original Message- > > > From: So

Re: About Drill Timezone Parse

[Arjun kr]

Hi Alican,


Please see if below syntax helps. Looks like hour format is in 24-hour format 
(HH instead of hh).


0: jdbc:drill:schema=dfs> select TO_TIMESTAMP('Mon June 04 12:09:56 EET 2001', 
'E MMM dd HH:mm:ss ZZZ ') from (values(1));
++
| EXPR$0 |
++
| 2001-06-04 12:09:56.0  |
++
1 row selected (0.285 seconds)


Thanks,

Arjun




From: Alican ERTURK 
Sent: Friday, April 6, 2018 12:35 PM
To: user@drill.apache.org
Subject: About Drill Timezone Parse


Hi,



I try to parse timestamp field in Apache Drill .



Our timestamp field format is :  'Wed June 04 12:09:56 EET 2001'



We use this parse function  -->  TO_TIMESTAMP('Wed June 04 12:09:56 EET 2001', 
'E  dd hh:mm:ss z ')  does not work.  We get an error as below :



--- org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: 
IllegalArgumentException: Invalid format: "Wed June 04 12:09:56 EET 2001" is 
malformed at "EET 2001" ---



However we change the timezone part EET to EST - TO_TIMESTAMP('Wed June 04 
12:09:56 EST 2001', 'E  dd hh:mm:ss z ')   - it works. It parses the 
timestamp field.



How can we parse EET timezone format in Apache Drill ?



Can you help us ?



Best regards,







ALİCAN ERTÜRK
İŞ ZEKASI UZMANI
MİMARİ VE İŞ ZEKASI MÜDÜRLÜĞÜ
ENERJİSA ENERJİ A.Ş.



[Açıklama: cid:image005.png@01D1292C.496C01D0]








Bu ileti hukuken korunmuş, gizli veya ifşa edilmemesi gereken bilgiler 
içerebilir. Şayet mesajın gönderildiği kişi değilseniz, bu iletiyi çoğaltmak ve 
dağıtmak yasaktır. Bu mesajı yanlışlıkla alan kişi, bu durumu derhal gönderene 
telefonla ya da e-posta ile bildirmeli ve bilgisayarından silmelidir. Bu 
iletinin içeriğinden yalnızca iletiyi gönderen kişi sorumludur.

This communication may contain information that is legally privileged, 
confidential or exempt from disclosure. If you are not the intended recipient, 
please note that any dissemination, distribution, or copying of this 
communication is strictly prohibited. Anyone who receives this message in error 
should notify the sender immediately by telephone or by return communication 
and delete it from his or her computer. Only the person who has sent this 
message is responsible for its content.

Re: JDBC Driver

[Arjun kr]

Hi Ravi,


Looking at the AWS documentation, it seems it can be specified in connection 
URL. I would suggest to try it using a standalone java application before 
trying it with Drill in case you have not tried it. As per doc, below are the 
steps involved. You may try it as given below (I have not tried it though).


1) Add Amazon RDS root CA certificate to truststore -  You can add this 
truststore used by Drill process. Add to cacerts if custom truststore is not 
being used.


2) Set connection string as given below - Try setting connection URL in storage 
plugin as given below. Not sure if there could be any parsing error. You may 
pass user and password in the connection URL if separate properties for 
username and password in plugin definition does not work.


final String connectionString = String.format( 
"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=%s)(PORT=%d))" +

"(CONNECT_DATA=(SID=%s))" +

"(SECURITY = (SSL_SERVER_CERT_DN = 
\"C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=%s\")))", DB_SERVER_NAME, 
SSL_PORT, DB_SID, DB_SERVER_NAME);


https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.SSL.html#Appendix.Oracle.Options.SSL.JDBC


Thanks,


Arjun



From: Sorabh Hamirwasia 
Sent: Tuesday, April 3, 2018 12:21 AM
To: user@drill.apache.org
Subject: Re: JDBC Driver

Hi Ravi,

I just want to confirm that you are using Drill's JDBC storage plugin to 
connect to RDS on Aws for Oracle ? You can look into [1] to see the usage.

Currently JDBC plugin only supports four parameters: 
driver/url/username/password, there doesn't seem to be support for any separate 
parameters for SSL configuration. But having said that, probably you can check 
the JDBC driver which you are using if it allows to pass these SSL parameters 
as key-value pair within URL or not.


Also not sure what you mean by ODBC driver not having the option to enable 
this, since Drill doesn't support any ODBC storage plugin.


[1] : https://drill.apache.org/docs/rdbms-storage-plugin/
RDBMS Storage Plugin - Apache 
Drill
drill.apache.org
Apache Drill supports querying a number of RDBMS instances. This allows you to 
connect your traditional databases to your Drill cluster so you can have a 
single view of both your relational and NoSQL datasources in a single system. 
As with any source, Drill supports joins within and between all ...





P.S. Please post these questions on users group only not on dev group, since 
there you will have wider audience which can answer it. For now I have removed 
dev group from To list.


Thanks,
Sorabh



From: Ravi Venugopal (C) 
Sent: Friday, March 30, 2018 8:01 AM
To: d...@drill.apache.org; user@drill.apache.org
Subject: JDBC Driver

Hi

I am trying to POC drill for a customer and I am working on connecting the JDBC 
driver to RDS on Aws for Oracle.

Here is the Certificate of security on the TNS names, I do not see a syntx / 
kvp for the json to add this cert path (Cert info hidden)


(SECURITY = (SSL_SERVER_CERT_DN = 
"C=US,ST=Somewhere,L=Cityname,O=Amazon.com,OU=RDS,CN=.y.us-ABCD-1.rds.amazonaws.com")))


PS: ODBC is not having the option to enable this as well.

Can someone help please.

This e-mail and any attachments are for the sole use of the intended 
recipient(s) and may contain information that is legally privileged and/or 
confidential information. If you are not the intended recipient(s) and have 
received this e-mail in error, please immediately notify the sender by return 
e-mail and delete this e-mail from your computer. Any distribution, disclosure 
or the taking of any other action by anyone other than the named recipient is 
strictly prohibited.

Re: hive connection as generic jdbc

[Arjun kr]

Hi Asim,


You can refer to section 'Modifying and Submitting a Physical Plan to Drill' in 
https://drill.apache.org/docs/query-plans/.



explain plan for select * from `hivejdbc`.`testdb`.`test`

{ "head" : { "version" : 1, "generator" : { "type" : "ExplainHandler", "info" : 
"" }, "type" : "APACHE_DRILL_PHYSICAL", "options" : [ ], "queue" : 0, 
"hasResourcePlan" : false, "resultMode" : "EXEC" }, "graph" : [ { "pop" : 
"jdbc-scan", "@id" : 2, "sql" : "SELECT *\nFROM.testdb.test", "config" : { 
"type" : "jdbc", "driver" : "org.apache.hive.jdbc.HiveDriver", "url" : 
"jdbc:hive2://localhost:1", "username" : "arjun", "password" : "arjun", 
"enabled" : true }, "userName" : "", "cost" : 100.0 }, { "pop" : "project", 
"@id" : 1, "exprs" : [ { "ref" : "`id`", "expr" : "`id`" }, { "ref" : "`name`", 
"expr" : "`name`" } ], "child" : 2, "initialAllocation" : 100, 
"maxAllocation" : 100, "cost" : 100.0 }, { "pop" : "screen", "@id" : 0, 
"child" : 1, "initialAllocation" : 100, "maxAllocation" : 100, 
"cost" : 100.0 } ] }

You can remove the dot ("sql" contents) and execute it as described in the 
above link for testing purpose.

The example picture in the link seems to be wrong.It shows text content being 
executed instead of json.

Thanks,

Arjun


From: Asim Kanungo 
Sent: Friday, March 16, 2018 9:57 PM
To: user@drill.apache.org
Subject: Re: hive connection as generic jdbc

Hi Kunal/Arjun,

Yeah I tried from view as well but no luck.
I need to learn how to execute the physical plan, if any easy way please
let me know else I can go through documentation.

Thanks to both of you for solving the connection issue though.
It will be great if you can update in the same thread once you hear from
the experts about the DOT issue described earlier.

Thanks
Asim


On Thu, Mar 15, 2018 at 11:40 PM, Kunal Khatua  wrote:

> Not sure, but you could try exposing the table as a view, and then query
> against that.
>
> On Thu, Mar 15, 2018 at 8:35 PM, Arjun kr  wrote:
>
> >
> > Yeah, I'm also getting the same error.The tables are getting listed
> > though. The plan generates table name as '.db.table'. If I remove this
> > extra dot in the physical plan and execute, using physical plan option,
> it
> > runs successfully. I would let Drill experts to comment on any possible
> > solution for this.
> >
> > Thanks,
> >
> > Arjun
> >
> >
> >
> > 
> > From: Asim Kanungo 
> > Sent: Friday, March 16, 2018 4:51 AM
> > To: user@drill.apache.org
> > Subject: Re: hive connection as generic jdbc
> >
> > " as Thanks Arjun
> > I am able to use this and got a success message for adding the storage
> > plugin.
> >
> > But while querying, I am getting a strange error. For example, if I do
> > SELECT * FROM myhivetest.testschema.`testtable`; I am finding DRILL is
> > submitting the query as :-
> >
> > sql SELECT *
> > FROM.testschema.testtable
> > plugin BIGRED
> > Fragment 0:0
> >
> > If you see there is one extra DOT (.) before the schema name and when it
> > going to my hive environment from DRILL it is failing for the extra DOT.
> >   (org.apache.hive.service.cli.HiveSQLException) Error while compiling
> > statement: FAILED: ParseException line 2:4 cannot recognize input near
> '.'
> > 'testschema' '.' in join source
> >
> > Also when I am running count(*) DRILL is assigning "$f0" as the alias and
> > failing.
> >
> > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup
> > the SQL query.
> >
> > sql SELECT COUNT(*) AS CNT
> > FROM (SELECT 0 AS $f0
> > FROM. testschema . testtable ) AS t
> > plugin myhivetest
> > Fragment 0:0
> >
> > [Error Id: d6a2fdf6-7979-4415-8d08-afbcd3667bde on
> > rs-master.redstack.com:31010] (state=,code=0)
> >
> > Please try from your side, and let me know if you are facing the same
> > issue.
> >
> >
> > On Thu, Mar 15, 2018 at 1:49 AM, Arjun kr  wrote:
> >
> > > Hi Asim,
> >

Re: hive connection as generic jdbc

[Arjun kr]

Yeah, I'm also getting the same error.The tables are getting listed though. The 
plan generates table name as '.db.table'. If I remove this extra dot in the 
physical plan and execute, using physical plan option, it runs successfully. I 
would let Drill experts to comment on any possible solution for this.

Thanks,

Arjun




From: Asim Kanungo 
Sent: Friday, March 16, 2018 4:51 AM
To: user@drill.apache.org
Subject: Re: hive connection as generic jdbc

" as Thanks Arjun
I am able to use this and got a success message for adding the storage
plugin.

But while querying, I am getting a strange error. For example, if I do
SELECT * FROM myhivetest.testschema.`testtable`; I am finding DRILL is
submitting the query as :-

sql SELECT *
FROM.testschema.testtable
plugin BIGRED
Fragment 0:0

If you see there is one extra DOT (.) before the schema name and when it
going to my hive environment from DRILL it is failing for the extra DOT.
  (org.apache.hive.service.cli.HiveSQLException) Error while compiling
statement: FAILED: ParseException line 2:4 cannot recognize input near '.'
'testschema' '.' in join source

Also when I am running count(*) DRILL is assigning "$f0" as the alias and
failing.

Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup
the SQL query.

sql SELECT COUNT(*) AS CNT
FROM (SELECT 0 AS $f0
FROM. testschema . testtable ) AS t
plugin myhivetest
Fragment 0:0

[Error Id: d6a2fdf6-7979-4415-8d08-afbcd3667bde on
rs-master.redstack.com:31010] (state=,code=0)

Please try from your side, and let me know if you are facing the same issue.


On Thu, Mar 15, 2018 at 1:49 AM, Arjun kr  wrote:

> Hi Asim,
>
> I was able to connect to Hive 1.2 using jdbc storage plugin with two below
> jars. You may give it a try with these jars.
>
> http://central.maven.org/maven2/org/apache/hive/hive-
> service/1.1.1/hive-service-1.1.1.jar
>
> http://central.maven.org/maven2/org/apache/hive/hive-
> jdbc/1.1.1/hive-jdbc-1.1.1.jar
>
> Thanks,
>
> Arjun
>
>
>
> 
> From: Arjun kr
> Sent: Wednesday, March 14, 2018 1:05 PM
> To: user@drill.apache.org
> Subject: Re: hive connection as generic jdbc
>
>
> Looks like hive-jdbc-1.1.1-standalone.jar has 'slf4j-log4j' bundled.You
> may try cloning below repo for hive uber jar that tried earlier. It does
> not include above the jar. You can try removing 
> 'org.apache.httpcomponents:httpclient'
> and 'httpcore' from artifact include list and build new jar.
>
>
> https://github.com/timveil/hive-jdbc-uber-jar
>
>
> https://github.com/timveil/hive-jdbc-uber-jar/blob/master/pom.xml#L168
>
>
> Also, see if hive 0.14 jdbc jar works.
>
>
> https://github.com/timveil/hive-jdbc-uber-jar/releases/
> download/v1.0-2.2.4.2/hive-jdbc-uber-2.2.4.2.jar'
>
>
> Thanks,
>
>
> Arjun
>
>
> 
> From: Asim Kanungo 
> Sent: Wednesday, March 14, 2018 9:06 AM
> To: user@drill.apache.org
> Subject: Re: hive connection as generic jdbc
>
> Hi Arjun,
>
> With that I am getting error while starting the drill bit.
> SLF4J: Class path contains multiple SLF4J bindings.
> SLF4J: Found binding in
> [jar:file:/opt/apache-drill-1.12.0/jars/3rdparty/hive-jdbc-
> 1.1.1-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in
> [jar:file:/opt/apache-drill-1.12.0/jars/classb/logback-
> classic-1.0.13.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
> SLF4J Error Codes<http://www.slf4j.org/codes.html#multiple_bindings>
> www.slf4j.org<http://www.slf4j.org>
> SLF4J warning or error messages and their meanings No SLF4J providers were
> found. This warning, i.e. not an error, message is reported when no SLF4J
> ...
>
>
>
> explanation.
> SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
> SLF4J: Detected both log4j-over-slf4j.jar AND slf4j-log4j12.jar on the
> class path, preempting StackOverflowError.
> SLF4J: See also http://www.slf4j.org/codes.html#log4jDelegationLoop for
> more details.
> Exception in thread "main" java.lang.ExceptionInInitializerError
> at org.apache.log4j.LogManager.getLogger(LogManager.java:44)
> at
> org.slf4j.impl.Log4jLoggerFactory.getLogger(Log4jLoggerFactory.java:66)
> at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:277)
> at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:288)
> at org.apache.drill.exec.server.Drillbit.(Drillbit.
> java:61)
> Caused by: java.lang.IllegalStateException: Detected both
> log4j-over-slf4j.jar AND slf4j-log4j12.j

Re: hive connection as generic jdbc

[Arjun kr]

Hi Asim,

I was able to connect to Hive 1.2 using jdbc storage plugin with two below 
jars. You may give it a try with these jars.

http://central.maven.org/maven2/org/apache/hive/hive-service/1.1.1/hive-service-1.1.1.jar

http://central.maven.org/maven2/org/apache/hive/hive-jdbc/1.1.1/hive-jdbc-1.1.1.jar

Thanks,

Arjun




From: Arjun kr
Sent: Wednesday, March 14, 2018 1:05 PM
To: user@drill.apache.org
Subject: Re: hive connection as generic jdbc


Looks like hive-jdbc-1.1.1-standalone.jar has 'slf4j-log4j' bundled.You may try 
cloning below repo for hive uber jar that tried earlier. It does not include 
above the jar. You can try removing 'org.apache.httpcomponents:httpclient' and 
'httpcore' from artifact include list and build new jar.


https://github.com/timveil/hive-jdbc-uber-jar


https://github.com/timveil/hive-jdbc-uber-jar/blob/master/pom.xml#L168


Also, see if hive 0.14 jdbc jar works.


https://github.com/timveil/hive-jdbc-uber-jar/releases/download/v1.0-2.2.4.2/hive-jdbc-uber-2.2.4.2.jar'


Thanks,


Arjun



From: Asim Kanungo 
Sent: Wednesday, March 14, 2018 9:06 AM
To: user@drill.apache.org
Subject: Re: hive connection as generic jdbc

Hi Arjun,

With that I am getting error while starting the drill bit.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in
[jar:file:/opt/apache-drill-1.12.0/jars/3rdparty/hive-jdbc-1.1.1-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in
[jar:file:/opt/apache-drill-1.12.0/jars/classb/logback-classic-1.0.13.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
SLF4J Error Codes<http://www.slf4j.org/codes.html#multiple_bindings>
www.slf4j.org
SLF4J warning or error messages and their meanings No SLF4J providers were 
found. This warning, i.e. not an error, message is reported when no SLF4J ...



explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
SLF4J: Detected both log4j-over-slf4j.jar AND slf4j-log4j12.jar on the
class path, preempting StackOverflowError.
SLF4J: See also http://www.slf4j.org/codes.html#log4jDelegationLoop for
more details.
Exception in thread "main" java.lang.ExceptionInInitializerError
at org.apache.log4j.LogManager.getLogger(LogManager.java:44)
at
org.slf4j.impl.Log4jLoggerFactory.getLogger(Log4jLoggerFactory.java:66)
at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:277)
at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:288)
at org.apache.drill.exec.server.Drillbit.(Drillbit.java:61)
Caused by: java.lang.IllegalStateException: Detected both
log4j-over-slf4j.jar AND slf4j-log4j12.jar on the class path, preempting
StackOverflowError. See also
http://www.slf4j.org/codes.html#log4jDelegationLoop for more details.
at
org.apache.log4j.Log4jLoggerFactory.(Log4jLoggerFactory.java:51)
... 5 more

I have done some googling and found can be resolved by adding the
exclusion. But not sure which POM file has to be edited.
Please guide, if there is any other jar file I should use and/or if any
changes required.

On Tue, Mar 13, 2018 at 1:22 PM, Arjun kr  wrote:

> Hi Asim,
>
>
> Can you try using below jar? It looks like hive 1.2 onwards, Hive uses
> httpclient version 4.4. The previous versions of Hive uses httpclient
> version 4.2.5. You may try with hive 1.1.1 standalone jar to see if it
> helps.
>
>
> http://central.maven.org/maven2/org/apache/hive/hive-
> jdbc/1.1.1/hive-jdbc-1.1.1-standalone.jar
>
>
> Thanks,
>
> Arjun
>
> 
> From: Asim Kanungo 
> Sent: Tuesday, March 13, 2018 11:47 AM
> To: user@drill.apache.org
> Subject: Re: hive connection as generic jdbc
>
> Hi Arjun,
>
> I have tried it, but no luck. I am still getting the INSTANCE error (Caused
> by: java.lang.NoSuchFieldError: INSTANCE).
> I am assuming it is happening for some version mismatch, I am poor in Java
> but found an article given in the below link.
> Can you please suggest if we can do any changes to the script. I can
> recompile the code after the change and deploy the jar file for the test.
>
> https://github.com/qubole/streamx/issues/32
> [https://avatars0.githubusercontent.com/u/1134147?s=400&v=4] github.com/qubole/streamx/issues/32>
>
> java.lang.NoSuchFieldError: INSTANCE exception, caused by ...<
> https://github.com/qubole/streamx/issues/32>
> github.com
> streamx - kafka-connect-s3 : Ingest data from Kafka to Object Stores(s3)
>
>
>
>
>
> On Tue, Mar 13, 2018 at 12:30 AM, Arjun kr  wrote:
>
> > Hi Asim,
> >
> >
> > You may give it a shot by adding this uber jar to Drill 3rd party
> > directory (Remove previously copied jars). For

Re: hive connection as generic jdbc

[Arjun kr]

Looks like hive-jdbc-1.1.1-standalone.jar has 'slf4j-log4j' bundled.You may try 
cloning below repo for hive uber jar that tried earlier. It does not include 
above the jar. You can try removing 'org.apache.httpcomponents:httpclient' and 
'httpcore' from artifact include list and build new jar.


https://github.com/timveil/hive-jdbc-uber-jar


https://github.com/timveil/hive-jdbc-uber-jar/blob/master/pom.xml#L168


Also, see if hive 0.14 jdbc jar works.


https://github.com/timveil/hive-jdbc-uber-jar/releases/download/v1.0-2.2.4.2/hive-jdbc-uber-2.2.4.2.jar'


Thanks,


Arjun



From: Asim Kanungo 
Sent: Wednesday, March 14, 2018 9:06 AM
To: user@drill.apache.org
Subject: Re: hive connection as generic jdbc

Hi Arjun,

With that I am getting error while starting the drill bit.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in
[jar:file:/opt/apache-drill-1.12.0/jars/3rdparty/hive-jdbc-1.1.1-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in
[jar:file:/opt/apache-drill-1.12.0/jars/classb/logback-classic-1.0.13.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
SLF4J Error Codes<http://www.slf4j.org/codes.html#multiple_bindings>
www.slf4j.org
SLF4J warning or error messages and their meanings No SLF4J providers were 
found. This warning, i.e. not an error, message is reported when no SLF4J ...



explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
SLF4J: Detected both log4j-over-slf4j.jar AND slf4j-log4j12.jar on the
class path, preempting StackOverflowError.
SLF4J: See also http://www.slf4j.org/codes.html#log4jDelegationLoop for
more details.
Exception in thread "main" java.lang.ExceptionInInitializerError
at org.apache.log4j.LogManager.getLogger(LogManager.java:44)
at
org.slf4j.impl.Log4jLoggerFactory.getLogger(Log4jLoggerFactory.java:66)
at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:277)
at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:288)
at org.apache.drill.exec.server.Drillbit.(Drillbit.java:61)
Caused by: java.lang.IllegalStateException: Detected both
log4j-over-slf4j.jar AND slf4j-log4j12.jar on the class path, preempting
StackOverflowError. See also
http://www.slf4j.org/codes.html#log4jDelegationLoop for more details.
at
org.apache.log4j.Log4jLoggerFactory.(Log4jLoggerFactory.java:51)
... 5 more

I have done some googling and found can be resolved by adding the
exclusion. But not sure which POM file has to be edited.
Please guide, if there is any other jar file I should use and/or if any
changes required.

On Tue, Mar 13, 2018 at 1:22 PM, Arjun kr  wrote:

> Hi Asim,
>
>
> Can you try using below jar? It looks like hive 1.2 onwards, Hive uses
> httpclient version 4.4. The previous versions of Hive uses httpclient
> version 4.2.5. You may try with hive 1.1.1 standalone jar to see if it
> helps.
>
>
> http://central.maven.org/maven2/org/apache/hive/hive-
> jdbc/1.1.1/hive-jdbc-1.1.1-standalone.jar
>
>
> Thanks,
>
> Arjun
>
> 
> From: Asim Kanungo 
> Sent: Tuesday, March 13, 2018 11:47 AM
> To: user@drill.apache.org
> Subject: Re: hive connection as generic jdbc
>
> Hi Arjun,
>
> I have tried it, but no luck. I am still getting the INSTANCE error (Caused
> by: java.lang.NoSuchFieldError: INSTANCE).
> I am assuming it is happening for some version mismatch, I am poor in Java
> but found an article given in the below link.
> Can you please suggest if we can do any changes to the script. I can
> recompile the code after the change and deploy the jar file for the test.
>
> https://github.com/qubole/streamx/issues/32
> [https://avatars0.githubusercontent.com/u/1134147?s=400&v=4] github.com/qubole/streamx/issues/32>
>
> java.lang.NoSuchFieldError: INSTANCE exception, caused by ...<
> https://github.com/qubole/streamx/issues/32>
> github.com
> streamx - kafka-connect-s3 : Ingest data from Kafka to Object Stores(s3)
>
>
>
>
>
> On Tue, Mar 13, 2018 at 12:30 AM, Arjun kr  wrote:
>
> > Hi Asim,
> >
> >
> > You may give it a shot by adding this uber jar to Drill 3rd party
> > directory (Remove previously copied jars). For truststore, try giving
> > absolute path. The test was to validate if hive uber jar works with your
> > Hive setup.
> >
> >
> > Thanks,
> >
> >
> > Arjun
> >
> >
> > 
> > From: Asim Kanungo 
> > Sent: Tuesday, March 13, 2018 10:48 AM
> > To: user@drill.apache.org
> > Subject: Re: hive connection as generic jdbc
> >
> > Hi Arjun,
> >
> > I 

Re: hive connection as generic jdbc

[Arjun kr]

Hi Asim,


Can you try using below jar? It looks like hive 1.2 onwards, Hive uses 
httpclient version 4.4. The previous versions of Hive uses httpclient version 
4.2.5. You may try with hive 1.1.1 standalone jar to see if it helps.


http://central.maven.org/maven2/org/apache/hive/hive-jdbc/1.1.1/hive-jdbc-1.1.1-standalone.jar


Thanks,

Arjun


From: Asim Kanungo 
Sent: Tuesday, March 13, 2018 11:47 AM
To: user@drill.apache.org
Subject: Re: hive connection as generic jdbc

Hi Arjun,

I have tried it, but no luck. I am still getting the INSTANCE error (Caused
by: java.lang.NoSuchFieldError: INSTANCE).
I am assuming it is happening for some version mismatch, I am poor in Java
but found an article given in the below link.
Can you please suggest if we can do any changes to the script. I can
recompile the code after the change and deploy the jar file for the test.

https://github.com/qubole/streamx/issues/32
[https://avatars0.githubusercontent.com/u/1134147?s=400&v=4]<https://github.com/qubole/streamx/issues/32>

java.lang.NoSuchFieldError: INSTANCE exception, caused by 
...<https://github.com/qubole/streamx/issues/32>
github.com
streamx - kafka-connect-s3 : Ingest data from Kafka to Object Stores(s3)





On Tue, Mar 13, 2018 at 12:30 AM, Arjun kr  wrote:

> Hi Asim,
>
>
> You may give it a shot by adding this uber jar to Drill 3rd party
> directory (Remove previously copied jars). For truststore, try giving
> absolute path. The test was to validate if hive uber jar works with your
> Hive setup.
>
>
> Thanks,
>
>
> Arjun
>
>
> 
> From: Asim Kanungo 
> Sent: Tuesday, March 13, 2018 10:48 AM
> To: user@drill.apache.org
> Subject: Re: hive connection as generic jdbc
>
> Hi Arjun,
>
> I have tried with the hive jdbc uber jar and able to make a successful
> connection.
> java -cp
> "hive-jdbc-uber-2.6.3.0-235.jar:sqlline-1.1.9-drill-r7.jar:jline-2.10.jar"
> sqlline.SqlLine -d org.apache.hive.jdbc.HiveDriver -u "jdbc:hive2://knox
> server name:port/default;ssl=true;sslTrustStore=location and filename of
> jks file;trustStorePassword=;transportMode=http;httpPath=path " -n
> username-p  -e "show tables;"
>
> As we have SSL enabled system so I have to give the extra details in the
> URL and it worked. Does that mean it should work for adding it as a generic
> JDBC.How is this test related to my issue ?
>
> Thanks
> Asim
>
>
>
>
>
> On Mon, Mar 12, 2018 at 10:36 PM, Arjun kr  wrote:
>
> > Hi Asim,
> >
> >
> > You may try using hive uber jar in case you have not tried it. See if
> > below link helps.
> >
> >
> > https://github.com/timveil/hive-jdbc-uber-jar/releases
> [https://avatars0.githubusercontent.com/u/3260845?s=400&v=4] github.com/timveil/hive-jdbc-uber-jar/releases>
>
> Releases · timveil/hive-jdbc-uber-jar · GitHub<https://github.com/
> timveil/hive-jdbc-uber-jar/releases>
> github.com
> hive-jdbc-uber-jar - Hive JDBC "uber" or "standalone" jar based on the
> latest Hortonworks Data Platform (HDP)
>
>
>
> >
> >
> > It would be ideal to test this uber jar with a sample JDBC application
> > before trying with Drill.
> >
> >
> > java -cp "hive-jdbc-uber-2.6.3.0-235.jar:$DRILL_HOME/jars/3rdparty/
> > sqlline-1.1.9-drill-r7.jar:$DRILL_HOME/jars/3rdparty/jline-2.10.jar"
> > sqlline.SqlLine -d org.apache.hive.jdbc.HiveDriver -u "" -n
> >  -p  -e "show tables;"
> >
> >
> > Thanks,
> >
> >
> > Arjun
> >
> >
> >
> > 
> > From: Asim Kanungo 
> > Sent: Tuesday, March 13, 2018 8:37 AM
> > To: user@drill.apache.org
> > Subject: Re: hive connection as generic jdbc
> >
> > Thanks Kunal...
> >
> > Here are the details.
> > {
> >   "type": "jdbc",
> >   "driver": "org.apache.hive.jdbc.HiveDriver",
> >   "url": "jdbc:hive2://knox
> > address:port/default?ssl=true&transportMode=http&httpPath=
> > pathdetail&sslTrustStore=mytruststore.jks&trustStorePassword=**",
> >   "username": "XXX",
> >   "password": "**",
> >   "enabled": true
> > }
> >
> > Please note that we have a SSL enabled system, so have used the
> Truststore
> > settings.
> > Please let me know if you need any more details.
> >
> > Thanks
> > Asim
> >
> >
> > On Sun, Mar 11, 2018 at 1

Re: hive connection as generic jdbc

[Arjun kr]

Hi Asim,


You may give it a shot by adding this uber jar to Drill 3rd party directory 
(Remove previously copied jars). For truststore, try giving absolute path. The 
test was to validate if hive uber jar works with your Hive setup.


Thanks,


Arjun



From: Asim Kanungo 
Sent: Tuesday, March 13, 2018 10:48 AM
To: user@drill.apache.org
Subject: Re: hive connection as generic jdbc

Hi Arjun,

I have tried with the hive jdbc uber jar and able to make a successful
connection.
java -cp
"hive-jdbc-uber-2.6.3.0-235.jar:sqlline-1.1.9-drill-r7.jar:jline-2.10.jar"
sqlline.SqlLine -d org.apache.hive.jdbc.HiveDriver -u "jdbc:hive2://knox
server name:port/default;ssl=true;sslTrustStore=location and filename of
jks file;trustStorePassword=;transportMode=http;httpPath=path " -n
username-p  -e "show tables;"

As we have SSL enabled system so I have to give the extra details in the
URL and it worked. Does that mean it should work for adding it as a generic
JDBC.How is this test related to my issue ?

Thanks
Asim





On Mon, Mar 12, 2018 at 10:36 PM, Arjun kr  wrote:

> Hi Asim,
>
>
> You may try using hive uber jar in case you have not tried it. See if
> below link helps.
>
>
> https://github.com/timveil/hive-jdbc-uber-jar/releases
[https://avatars0.githubusercontent.com/u/3260845?s=400&v=4]<https://github.com/timveil/hive-jdbc-uber-jar/releases>

Releases · timveil/hive-jdbc-uber-jar · 
GitHub<https://github.com/timveil/hive-jdbc-uber-jar/releases>
github.com
hive-jdbc-uber-jar - Hive JDBC "uber" or "standalone" jar based on the latest 
Hortonworks Data Platform (HDP)



>
>
> It would be ideal to test this uber jar with a sample JDBC application
> before trying with Drill.
>
>
> java -cp "hive-jdbc-uber-2.6.3.0-235.jar:$DRILL_HOME/jars/3rdparty/
> sqlline-1.1.9-drill-r7.jar:$DRILL_HOME/jars/3rdparty/jline-2.10.jar"
> sqlline.SqlLine -d org.apache.hive.jdbc.HiveDriver -u "" -n
>  -p  -e "show tables;"
>
>
> Thanks,
>
>
> Arjun
>
>
>
> 
> From: Asim Kanungo 
> Sent: Tuesday, March 13, 2018 8:37 AM
> To: user@drill.apache.org
> Subject: Re: hive connection as generic jdbc
>
> Thanks Kunal...
>
> Here are the details.
> {
>   "type": "jdbc",
>   "driver": "org.apache.hive.jdbc.HiveDriver",
>   "url": "jdbc:hive2://knox
> address:port/default?ssl=true&transportMode=http&httpPath=
> pathdetail&sslTrustStore=mytruststore.jks&trustStorePassword=**",
>   "username": "XXX",
>   "password": "**",
>   "enabled": true
> }
>
> Please note that we have a SSL enabled system, so have used the Truststore
> settings.
> Please let me know if you need any more details.
>
> Thanks
> Asim
>
>
> On Sun, Mar 11, 2018 at 11:55 PM, Kunal Khatua  wrote:
>
> > I'm not sure how this is resolved since Hive is directly accessed by
> Drill
> > using the Hive storage plugin, instead of via the JDBC storage plugin.
> > Perhaps you can share the parameters of the JDBC storage plugin you used,
> > so that folks more familiar with the JDBC storage plugin can help.
> > I'll see what I can find out in the meanwhile.
> > ~ Kunal
> >
> > On Sat, Mar 10, 2018 at 7:23 PM, Asim Kanungo  wrote:
> >
> > > Hi Kunal,
> > >
> > > I have tried the steps and getting the below error:-
> > >
> > > 2018-03-08 22:39:59,234 [qtp433826182-75] ERROR
> > > o.a.d.e.server.rest.StorageResources - Unable to create/ update
> plugin:
> > > test
> > > org.apache.drill.common.exceptions.ExecutionSetupException: Failure
> > > setting
> > > up new storage plugin configuration for config
> > > org.apache.drill.exec.store.jdbc.JdbcStorageConfig@8ef5d26f
> > > at
> > > org.apache.drill.exec.store.StoragePluginRegistryImpl.create(
> > > StoragePluginRegistryImpl.java:355)
> > > ~[drill-java-exec-1.12.0.jar:1.12.0]
> > > at
> > > org.apache.drill.exec.store.StoragePluginRegistryImpl.createOrUpdate(
> > > StoragePluginRegistryImpl.java:239)
> > > ~[drill-java-exec-1.12.0.jar:1.12.0]
> > > at
> > > org.apache.drill.exec.server.rest.PluginConfigWrapper.
> > > createOrUpdateInStorage(PluginConfigWrapper.java:57)
> > > ~[drill-java-exec-1.12.0.jar:1.12.0]
> > > at
> > > org.apache.drill.exec.server.rest.StorageResources.
> > > createOrUpdatePluginJSON(StorageResources

Re: hive connection as generic jdbc

[Arjun kr]

Hi Asim,


You may try using hive uber jar in case you have not tried it. See if below 
link helps.


https://github.com/timveil/hive-jdbc-uber-jar/releases


It would be ideal to test this uber jar with a sample JDBC application before 
trying with Drill.


java -cp 
"hive-jdbc-uber-2.6.3.0-235.jar:$DRILL_HOME/jars/3rdparty/sqlline-1.1.9-drill-r7.jar:$DRILL_HOME/jars/3rdparty/jline-2.10.jar"
 sqlline.SqlLine -d org.apache.hive.jdbc.HiveDriver -u "" -n  
-p  -e "show tables;"


Thanks,


Arjun




From: Asim Kanungo 
Sent: Tuesday, March 13, 2018 8:37 AM
To: user@drill.apache.org
Subject: Re: hive connection as generic jdbc

Thanks Kunal...

Here are the details.
{
  "type": "jdbc",
  "driver": "org.apache.hive.jdbc.HiveDriver",
  "url": "jdbc:hive2://knox
address:port/default?ssl=true&transportMode=http&httpPath=pathdetail&sslTrustStore=mytruststore.jks&trustStorePassword=**",
  "username": "XXX",
  "password": "**",
  "enabled": true
}

Please note that we have a SSL enabled system, so have used the Truststore
settings.
Please let me know if you need any more details.

Thanks
Asim


On Sun, Mar 11, 2018 at 11:55 PM, Kunal Khatua  wrote:

> I'm not sure how this is resolved since Hive is directly accessed by Drill
> using the Hive storage plugin, instead of via the JDBC storage plugin.
> Perhaps you can share the parameters of the JDBC storage plugin you used,
> so that folks more familiar with the JDBC storage plugin can help.
> I'll see what I can find out in the meanwhile.
> ~ Kunal
>
> On Sat, Mar 10, 2018 at 7:23 PM, Asim Kanungo  wrote:
>
> > Hi Kunal,
> >
> > I have tried the steps and getting the below error:-
> >
> > 2018-03-08 22:39:59,234 [qtp433826182-75] ERROR
> > o.a.d.e.server.rest.StorageResources - Unable to create/ update plugin:
> > test
> > org.apache.drill.common.exceptions.ExecutionSetupException: Failure
> > setting
> > up new storage plugin configuration for config
> > org.apache.drill.exec.store.jdbc.JdbcStorageConfig@8ef5d26f
> > at
> > org.apache.drill.exec.store.StoragePluginRegistryImpl.create(
> > StoragePluginRegistryImpl.java:355)
> > ~[drill-java-exec-1.12.0.jar:1.12.0]
> > at
> > org.apache.drill.exec.store.StoragePluginRegistryImpl.createOrUpdate(
> > StoragePluginRegistryImpl.java:239)
> > ~[drill-java-exec-1.12.0.jar:1.12.0]
> > at
> > org.apache.drill.exec.server.rest.PluginConfigWrapper.
> > createOrUpdateInStorage(PluginConfigWrapper.java:57)
> > ~[drill-java-exec-1.12.0.jar:1.12.0]
> > at
> > org.apache.drill.exec.server.rest.StorageResources.
> > createOrUpdatePluginJSON(StorageResources.java:162)
> > [drill-java-exec-1.12.0.jar:1.12.0]
> > at
> > org.apache.drill.exec.server.rest.StorageResources.createOrUpdatePlugin(
> > StorageResources.java:177)
> > [drill-java-exec-1.12.0.jar:1.12.0]
> > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > ~[na:1.8.0_73]
> > at
> > sun.reflect.NativeMethodAccessorImpl.invoke(
> NativeMethodAccessorImpl.java:
> > 62)
> > ~[na:1.8.0_73]
> > at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(
> > DelegatingMethodAccessorImpl.java:43)
> > ~[na:1.8.0_73]
> > at java.lang.reflect.Method.invoke(Method.java:497)
> ~[na:1.8.0_73]
> > at
> > org.glassfish.jersey.server.model.internal.
> ResourceMethodInvocationHandle
> > rFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
> > [jersey-server-2.8.jar:na]
> > at
> > org.glassfish.jersey.server.model.internal.
> AbstractJavaResourceMethodDisp
> > atcher$1.run(AbstractJavaResourceMethodDispatcher.java:151)
> > [jersey-server-2.8.jar:na]
> > at
> > org.glassfish.jersey.server.model.internal.
> AbstractJavaResourceMethodDisp
> > atcher.invoke(AbstractJavaResourceMethodDispatcher.java:171)
> > [jersey-server-2.8.jar:na]
> > at
> > org.glassfish.jersey.server.model.internal.
> JavaResourceMethodDispatcherPr
> > ovider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherPr
> > ovider.java:195)
> > [jersey-server-2.8.jar:na]
> > at
> > org.glassfish.jersey.server.model.internal.
> AbstractJavaResourceMethodDisp
> > atcher.dispatch(AbstractJavaResourceMethodDispatcher.java:104)
> > [jersey-server-2.8.jar:na]
> > at
> > org.glassfish.jersey.server.model.ResourceMethodInvoker.
> > invoke(ResourceMethodInvoker.java:387)
> > [jersey-server-2.8.jar:na]
> > at
> > org.glassfish.jersey.server.model.ResourceMethodInvoker.
> > apply(ResourceMethodInvoker.java:331)
> > [jersey-server-2.8.jar:na]
> > at
> > org.glassfish.jersey.server.model.ResourceMethodInvoker.
> > apply(ResourceMethodInvoker.java:103)
> > [jersey-server-2.8.jar:na]
> > at
> > org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:269)
> > [jersey-server-2.8.jar:na]
> > at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
> > [jersey-common-2.8.jar:na]
> > at org.glassfish.jersey

Re: Setting up drill to query AWS S3 behind a proxy

[Arjun kr]

Hi Tyler,


The Hadoop-AWS module provides settings for proxy setup. You may try setting 
these configs in $DRILL_CONF/core-site.xml and restart drill-bits. I have not 
tested it though.


https://hadoop.apache.org/docs/r2.7.1/hadoop-aws/tools/hadoop-aws/index.html



  fs.s3a.proxy.host
  Hostname of the (optional) proxy server for S3 
connections.



  fs.s3a.proxy.port
  Proxy server port. If this property is not set
but fs.s3a.proxy.host is, port 80 or 443 is assumed (consistent with
the value of fs.s3a.connection.ssl.enabled).



  fs.s3a.proxy.username
  Username for authenticating with proxy server.



  fs.s3a.proxy.password
  Password for authenticating with proxy server.



  fs.s3a.proxy.domain
  Domain for authenticating with proxy server.



  fs.s3a.proxy.workstation
  Workstation for authenticating with proxy server.




Thanks,

Arjun


From: Padma Penumarthy 
Sent: Tuesday, March 13, 2018 2:56 AM
To: user@drill.apache.org
Subject: Re: Setting up drill to query AWS S3 behind a proxy

Not sure what exactly you mean by proxy settings.
But, here is what you can do to access files on S3.
Enable S3 storage plugin, update the connection string, access key and secret 
key in the config.
If it is able to connect fine, you should see s3.root when you do show 
databases.

Thanks
Padma


> On Mar 12, 2018, at 12:43 PM, Edelman, Tyler  
> wrote:
>
> Hello,
>
> I am currently trying to set up drill locally to query a JSON file in 
> Amazon’s AWS S3. I have not been able to configure proxy settings for drill. 
> Could you send me a configuration example of this?
>
> Thank you,
> Tyler Edelman
> 
>
> The information contained in this e-mail is confidential and/or proprietary 
> to Capital One and/or its affiliates and may only be used solely in 
> performance of work or services for Capital One. The information transmitted 
> herewith is intended only for use by the individual or entity to which it is 
> addressed. If the reader of this message is not the intended recipient, you 
> are hereby notified that any review, retransmission, dissemination, 
> distribution, copying or other use of, or taking of any action in reliance 
> upon this information is strictly prohibited. If you have received this 
> communication in error, please contact the sender and delete the material 
> from your computer.

Re: Way to "pivot"

[Arjun kr]

If each timestamp has only one set of values for (x,y,z) , you can try 
something like below.

select dt ,
 max(case when source='X' THEN `value` else 0.0 end) as X,
 max(case when source='Y' THEN `value` else 0.0 end) as Y,
 max(case when source='Z' THEN `value` else 0.0 end) as Z
from
 
group by dt;

Thanks,

Arjun




From: Andries Engelbrecht 
Sent: Tuesday, March 6, 2018 9:11 PM
To: user@drill.apache.org
Subject: Re: Way to "pivot"

If the X, Y and Z is unique for each timestamp you can perhaps use group by 
(dt, X, Y , Z)  and case to make the X, Y , Z columns. May be worth looking 
into, but is going to be expensive to execute. Just an idea, but have not 
tested it.

--Andries


On 3/6/18, 6:46 AM, "John Omernik"  wrote:

I am not sure if this is the right thing for what I am trying to do, but I
have data in this formate


source   dtvalue
X2018-03-06 11:00 0.31
X2018-03-06 12:00 0.94
X2018-03-06 13:00 0.89
X2018-03-06 14:00 0.01
X2018-03-06 15:00 0.43
Y2018-03-06 11:00 1.43
Y2018-03-06 12:00 0.50
Y2018-03-06 13:00 0.10
Y2018-03-06 14:00 0.42
Y2018-03-06 15:00 0.41
Z2018-03-06 11:00 5.34
Z2018-03-06 12:00 4.32
Z2018-03-06 13:00 4.20
Z2018-03-06 14:00 0.89
Z2018-03-06 15:00 0.01

I'd like to graph it as three lines (X, Y and Z) over time, so the graph
tool I am using asks for it this format:



dt   X Y Z

2018-03-06 11:00 0.31   1.43 5.34
2018-03-06 12:00 0.94   0.50 4.32
2018-03-06 13:00 0.89   0.10 4.20
2018-03-06 14:00 0.01   0.42 0.89
2018-03-06 15:00 0.43   0.41 0.01


So I think that would be a PIVOT like function right (which I don't think
Drill has) Is there a way to "fake" this in Drill using some other built in
functions?

Thanks!

John

Re: Default value for parameter 'planner.width.max_per_node'

[Arjun kr]

Hi Kunal,


Thanks for the response. I have created JIRA 
DRILL-6183<https://issues.apache.org/jira/browse/DRILL-6183> as you mentioned.


Regards,


Arjun


From: Kunal Khatua 
Sent: Thursday, February 22, 2018 12:09 PM
To: user@drill.apache.org
Subject: Re: Default value for parameter 'planner.width.max_per_node'

Looks like DRILL-5547: Linking config options with system option manager ( 
https://github.com/apache/drill/commit/a51c98b8bf210bbe9d3f4018361d937252d1226d 
) introduced a change in the computation, which is based on the number of cores.
[https://camo.githubusercontent.com/ef395746e2290c22e7998b2ded24bc569babaf4f/68747470733a2f2f302e67726176617461722e636f6d2f6176617461722f39653833376539326635303030643735653831376533356230646664373131613f643d68747470732533412532462532466173736574732d63646e2e6769746875622e636f6d253246696d6167657325324667726176617461727325324667726176617461722d757365722d3432302e706e6726723d6726733d323030]<https://github.com/apache/drill/commit/a51c98b8bf210bbe9d3f4018361d937252d1226d>

DRILL-5547: Linking config options with system option manager · 
apache/drill@a51c98b<https://github.com/apache/drill/commit/a51c98b8bf210bbe9d3f4018361d937252d1226d>
github.com
closes #868



https://github.com/apache/drill/blame/6ad983833c3e069b6925d0c3ce3077afd8c3/exec/java-exec/src/main/java/org/apache/drill/exec/server/options/TypeValidators.java#L252
[https://avatars3.githubusercontent.com/u/47359?s=400&v=4]<https://github.com/apache/drill/blame/6ad983833c3e069b6925d0c3ce3077afd8c3/exec/java-exec/src/main/java/org/apache/drill/exec/server/options/TypeValidators.java#L252>

apache/drill<https://github.com/apache/drill/blame/6ad983833c3e069b6925d0c3ce3077afd8c3/exec/java-exec/src/main/java/org/apache/drill/exec/server/options/TypeValidators.java#L252>
github.com
drill - Mirror of Apache Drill




Looks like a non-zero value is not being computed. Setting the value as a 
system variable should persist and correct this. Can you file a JIRA for this? 
The fix should be minor.

On 2/21/2018 8:54:53 PM, Arjun kr  wrote:
Hi Team,


Did default value for configuration parameter 'planner.width.max_per_node' 
change between Drill 1.10 and 1.11 versions? Also, does default value of 70% of 
total cores still hold true?



-- Drill 1.11


0: jdbc:drill:drillbit=localhost> select * from sys.options where name like 
'%planner.width%';
+--+---+---+--+--+--+-+---++
| name | kind | accessibleScopes | optionScope | status | num_val | string_val 
| bool_val | float_val |
+--+---+---+--+--+--+-+---++
| planner.width.max_per_node | LONG | ALL | BOOT | DEFAULT | 0 | null | null | 
null |
| planner.width.max_per_query | LONG | ALL | BOOT | DEFAULT | 1000 | null | 
null | null |
+--+---+---+--+--+--+-+---++
2 rows selected (0.913 seconds)
0: jdbc:drill:drillbit=localhost>

-Drill 1.10

0: jdbc:drill:schema=dfs> select * from sys.options where name like 
'%planner.width%';
+--+---+-+--+--+-+---++
| name | kind | type | status | num_val | string_val | bool_val | float_val |
+--+---+-+--+--+-+---++
| planner.width.max_per_node | LONG | SYSTEM | DEFAULT | 3 | null | null | null 
|
| planner.width.max_per_query | LONG | SYSTEM | DEFAULT | 1000 | null | null | 
null |
+--+---+-+--+--+-+---++
2 rows selected (0.359 seconds)
0: jdbc:drill:schema=dfs>

Thanks,

Arjun

Default value for parameter 'planner.width.max_per_node'

[Arjun kr]

Hi Team,


Did default value for configuration parameter 'planner.width.max_per_node' 
change between Drill 1.10 and 1.11 versions? Also, does default value of 70% of 
total cores still hold true?



-- Drill 1.11


0: jdbc:drill:drillbit=localhost> select * from sys.options where name like 
'%planner.width%';
+--+---+---+--+--+--+-+---++
| name | kind  | accessibleScopes  | optionScope  |  
status  | num_val  | string_val  | bool_val  | float_val  |
+--+---+---+--+--+--+-+---++
| planner.width.max_per_node   | LONG  | ALL   | BOOT | 
DEFAULT  | 0| null| null  | null   |
| planner.width.max_per_query  | LONG  | ALL   | BOOT | 
DEFAULT  | 1000 | null| null  | null   |
+--+---+---+--+--+--+-+---++
2 rows selected (0.913 seconds)
0: jdbc:drill:drillbit=localhost>

-Drill 1.10

0: jdbc:drill:schema=dfs> select * from sys.options where name like 
'%planner.width%';
+--+---+-+--+--+-+---++
| name | kind  |  type   |  status  | num_val  | 
string_val  | bool_val  | float_val  |
+--+---+-+--+--+-+---++
| planner.width.max_per_node   | LONG  | SYSTEM  | DEFAULT  | 3| null   
 | null  | null   |
| planner.width.max_per_query  | LONG  | SYSTEM  | DEFAULT  | 1000 | null   
 | null  | null   |
+--+---+-+--+--+-+---++
2 rows selected (0.359 seconds)
0: jdbc:drill:schema=dfs>

Thanks,

Arjun

Re: Fixed-width files

[Arjun kr]

If you have Hive storage plugin enabled, You can create Hive table with regex 
serde and query the same in Drill.


-- Table contents

$ hadoop fs -cat /tmp/regex_test/*
112123
$

-- Hive DDL with regex '(.{1})(.{2})(.{3})' - column1 of width 1,column2 of 
width 2 and column3 of width 3

CREATE EXTERNAL TABLE `hive_regex_test`(
  `column1` string COMMENT 'from deserializer',
  `column2` string COMMENT 'from deserializer',
  `column3` string COMMENT 'from deserializer')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex'='(.{1})(.{2})(.{3})')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '/tmp/regex_test';

hive>
> select * from hive_regex_test;
OK
hive_regex_test.column1 hive_regex_test.column2 hive_regex_test.column3
1 12 123
Time taken: 0.235 seconds, Fetched: 1 row(s)
hive>


-- Drill

0: jdbc:drill:schema=dfs> select * from `hive_regex_test`;
+--+--+--+
| column1  | column2  | column3  |
+--+--+--+
| 1| 12   | 123  |
+--+--+--+
1 row selected (0.587 seconds)
0: jdbc:drill:schema=dfs>

Thanks,

Arjun


From: Kunal Khatua 
Sent: Wednesday, February 21, 2018 12:37 AM
To: user@drill.apache.org
Subject: RE: Fixed-width files

This might be a better option, since DRILL-6170 will introduce a rigid parsing 
definition. So, different fixed-width files can't leverage the same definition, 
though they might share the same extension.

Thanks, Andries!

-Original Message-
From: Andries Engelbrecht [mailto:aengelbre...@mapr.com]
Sent: Tuesday, February 20, 2018 7:39 AM
To: user@drill.apache.org
Subject: Re: Fixed-width files

You can also try and see if you can just use the CSV plugin to read a line as 
columns[0] and then use the substr function to pull out the fields in the line.

https://urldefense.proofpoint.com/v2/url?u=http-3A__drill.apache.org_docs_string-2Dmanipulation_-23substr&d=DwIGaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=-cT6otg6lpT_XkmYy7yg3A&m=oItppN_rkOKe_pgJb06T71ul6__8GsXmWQzTOQlCvBc&s=u6-Tx7rmfJQDa3_W3hg7YxojXP3Hf60YPLGHMnD8yLg&e=



Here is a simple example



Simple csv file



[test]$ cat test.csv

col1col2col3





jdbc:drill:zk=localhost:5181> select substr(columns[0],1,4), 
substr(columns[0],5,4), substr(columns[0],9,4) from  
dfs.root.`/data/csv/test/test.csv`;

+-+-+-+

| EXPR$0  | EXPR$1  | EXPR$2  |

+-+-+-+

| col1| col2| col3|

+-+-+-+







--Andries









On 2/20/18, 1:17 AM, "Flavio Pompermaier"  wrote:



For the moment I've created an improvement issue about this:


https://urldefense.proofpoint.com/v2/url?u=https-3A__issues.apache.org_jira_browse_DRILL-2D6170&d=DwIBaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=Q3Oz5l4W5TvDHNLpOqMYE2AgtKWFE937v89GEHyOVDU&m=69ohaJkyhIdPzNBy3ZsqNCTa19XysjZzgmn_XPJ2yXQ&s=ajRYDHKrMFgV1AMW2Q8weYDZtzb7-U5CqR9fML7ihno&e=



On Tue, Feb 20, 2018 at 9:23 AM, Flavio Pompermaier 

wrote:



> Thanks Paul for this suggestion, I think I'm going to give it a try.

> Once I've created my EasyFormatPlugin where should I put the produced jar?

> in which folder within jars directory?

>

> On Tue, Feb 20, 2018 at 2:57 AM, Paul Rogers 

> wrote:

>

>> It may be that by "fixed width text", Flavio means a file in which the

>> text columns are of fixed width: kind of like old-school punch cards.

>> Drill has no reader for this use case, but if you are a Java programmer,

>> you can create one. See Drill Pull Request #1114 [1] for one example of a

>> regex reader along with pointers to a second example I'm building for a

>> book. Should be easy to adopt this code to take a list of column widths 
in

>> place of the regex. Actually, you could use the regex with a pattern that

>> just picks out a fixed number of characters.

>> Thanks,

>> - Paul

>>

>> [1]  
https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_drill_pull_1114&d=DwIBaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=Q3Oz5l4W5TvDHNLpOqMYE2AgtKWFE937v89GEHyOVDU&m=69ohaJkyhIdPzNBy3ZsqNCTa19XysjZzgmn_XPJ2yXQ&s=-0LdlBnmAXaipanP87yJezn5HPEHQIQVX5izxnNTYFY&e=

>>

>>

>>

>>

>> On Monday, February 19, 2018, 12:52:42 PM PST, Kunal Khatua <

>> kkha...@mapr.com> wrote:

>>

>>  As long as you have delimiters, you should be able to import it as a

>> regular CSV file. Using views that define the fixed-width nature should

>> help operators downstream work more efficiently.

>>

>> -Original Message-

>> From: Flavio Pompermaier [mailto:pomperma...@okkam.it]

>> Sent: Monday, February 19, 2018 6:50 AM

>> To: user@drill.apache.org

>> Subject: Fixed-width files

>>

>> Hi to all,

Re: Code too large

[Arjun kr]

Hi Anup,


You may try setting configuration option 
'exec.java.compiler.exp_in_method_size' to lower value from default of 50 and 
run the query to see if it helps. Even lowering to a value of 1 doesn't help, 
the query details and stack trace may helpful for analysis as Khurram mentioned.


alter session set `exec.java.compiler.exp_in_method_size` = ;



Refer doc for more details on this configuration property: 
https://drill.apache.org/docs/configuration-options-introduction/


Thanks,


Arjun



From: Khurram Faraaz 
Sent: Thursday, February 15, 2018 12:55 AM
To: user@drill.apache.org
Subject: Re: Code too large

Can you please share the exact query as is (without formatting) here. And the 
error that you see on Drill 1.11.0, so we can try and repro it.


Thanks,

Khurram


From: Anup Tiwari 
Sent: Wednesday, February 14, 2018 3:14:01 AM
To: user@drill.apache.org
Subject: Re: Code too large

Hi Team,
Do we have any updates on this? It is coming even in Drill 1.11.0 and i tried
same query on hive, it is working perfectly.





On Fri, Mar 25, 2016 2:30 AM, Jacques Nadeau jacq...@dremio.com  wrote:
Looking at the trace it looks like Drill is correctly using the JDK

compiler which is good. However, it looks like we're not breaking apart the

expressions into submethods. @Aditya, since you worked on DRILL-1155, do

you have any sense to why we're not subdividing the code in this situation?




If I recall, we said we would only include 100 expressions per project

method. Or are we hitting a limit of fields or some other limit?




We test large projects here:




https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_drill_blob_master_exec_java-2Dexec_src_test_java_org_apache_drill_exec_compile_TestLargeFileCompilation.java&d=DwIFAg&c=cskdkSMqhcnjZxdQVpwTXg&r=H5JEl9vb-mBIjic10QAbDD2vkUUKAxjO6wZO322RtdI&m=7Q3xDEBNDJpTxOv5CZjQjvw57jo8TxbjdDjkojonXp8&s=uiwsx3NwkuLNc6GpAJfcs0KPM0FQcNoAt4OD7TcMr8w&e=
[https://avatars3.githubusercontent.com/u/47359?s=400&v=4]

apache/drill
urldefense.proofpoint.com
drill - Mirror of Apache Drill







--

Jacques Nadeau

CTO and Co-Founder, Dremio




On Thu, Mar 24, 2016 at 11:54 AM, Edmon Begoli  wrote:




> Logs and queries attached.

>

> On Thu, Mar 24, 2016 at 2:22 PM, Edmon Begoli  wrote:

>

>> We are working on it.

>>

>> On Thu, Mar 24, 2016 at 2:07 PM, Ted Dunning 

>> wrote:

>>

>>> Providing the stack traces and logs is a key first step in fixing this.

>>>

>>>

>>>

>>> On Thu, Mar 24, 2016 at 11:02 AM, Nirav Shah 

>>> wrote:

>>>

>>> > I had the same issue but till date even in version 1.6 it's there.

>>> > I checked about JDK also.

>>> >

>>> > it would be great if we can solve this issue.

>>> >

>>> > Regards,

>>> > Nirav

>>> >

>>> > On Thu, Mar 24, 2016 at 11:06 PM, Edmon Begoli 

>>> wrote:

>>> >

>>> > > Here is the query:

>>> > >

>>> > > CREATE TABLE

>>> > > cms.joe.`ALL_IP_HS_FINAL`

>>> > > AS

>>> > > SELECT CASE WHEN columns[0] =3D '' THEN NULL ELSE CAST(columns[0] as

>>> > > DOUBLE=

>>> > > )

>>> > > END as `DSYSRTKY`, CASE WHEN columns[1] =3D '' THEN NULL ELSE

>>> > > CAST(columns[1] as DOUBLE) END as `CLAIMNO`, NULLIF(columns[2],'')

>>> as

>>> > > `PROVIDER`, CASE WHEN columns[3] =3D '' THEN NULL ELSE

>>> > TO_DATE(columns[3],

>>> > > 'MMdd') END as `THRU_DT`, NULLIF(columns[4],'') as `RIC_CD`,

>>> > > NULLIF(columns[5],'') as `CLM_TYPE`, NULLIF(columns[6],'') as

>>> `QUERY_CD`,

>>> > > NULLIF(columns[7],'') as `FAC_TYPE`, NULLIF(columns[8],'') as

>>> `TYPESRVC`,

>>> > > NULLIF(columns[9],'') as `FREQ_CD`, NULLIF(columns[10],'') as

>>> `FI_NUM`,

>>> > > NULLIF(columns[11],'') as `NOPAY_CD`, CASE WHEN columns[12] =3D ''

>>> THEN

>>> > > NUL=

>>> > > L

>>> > > ELSE CAST(columns[12] as DOUBLE) END as `PMT_AMT`, CASE WHEN

>>> > columns[13] =

>>> > > =3D

>>> > > '' THEN NULL ELSE CAST(columns[13] as DOUBLE) END as `PRPAYAMT`,

>>> > > NULLIF(columns[14],'') as `PRPAY_CD`, NULLIF(columns[15],'') as

>>> > `ACTIONCD`,

>>> > > NULLIF(columns[16],'') as `PRSTATE`, NULLIF(columns[17],'') as

>>> > `ORGNPINM`,

>>> > > NULLIF(columns[18],'') as `AT_UPIN`, NULLIF(columns[19],'') as

>>> `AT_NPI`,

>>> > > NULLIF(columns[20],'') as `OP_UPIN`

Re: S3 Connection Issues

[Arjun kr]

If you have 'hadoop-aws-2.9.0.jar' jar in drill classpath, replace it with 
original aws jar that comes with tarball.


The class 'org/apache/hadoop/fs/GlobalStorageStatistics' is not available in 
hadoop common jar - hadoop-common-2.7.1.jar ( this was added in 2.8.0). You can 
try with original tarball installation jars.


Thanks,


Arjun


From: Anup Tiwari 
Sent: Wednesday, February 14, 2018 11:49 AM
To: user@drill.apache.org
Subject: Re: S3 Connection Issues

Hi Arjun,
I tried what you said but its not working and queries are going inENQUEUED
state. Please find below log :-
Error
[drill-executor-1] ERROR o.a.d.exec.server.BootStrapContext -
org.apache.drill.exec.work.foreman.Foreman.run() leaked an exception.
java.lang.NoClassDefFoundError:
org/apache/hadoop/fs/GlobalStorageStatistics$StorageStatisticsProviderat
java.lang.Class.forName0(Native Method) ~[na:1.8.0_72]at
java.lang.Class.forName(Class.java:348) ~[na:1.8.0_72]at
org.apache.hadoop.conf.Configuration.getClassByNameOrNull(Configuration.java:2134)
~[hadoop-common-2.7.1.jar:na]at
org.apache.hadoop.conf.Configuration.getClassByName(Configuration.java:2099)
~[hadoop-common-2.7.1.jar:na]at
org.apache.hadoop.conf.Configuration.getClass(Configuration.java:2193)
~[hadoop-common-2.7.1.jar:na]at
org.apache.hadoop.fs.FileSystem.getFileSystemClass(FileSystem.java:2638)
~[hadoop-common-2.7.1.jar:na]at
org.apache.hadoop.fs.FileSystem.createFileSystem(FileSystem.java:2651)
~[hadoop-common-2.7.1.jar:na]at
org.apache.hadoop.fs.FileSystem.access$200(FileSystem.java:92)
~[hadoop-common-2.7.1.jar:na]at
org.apache.hadoop.fs.FileSystem$Cache.getInternal(FileSystem.java:2687)
~[hadoop-common-2.7.1.jar:na]at
org.apache.hadoop.fs.FileSystem$Cache.get(FileSystem.java:2669)
~[hadoop-common-2.7.1.jar:na]at
org.apache.hadoop.fs.FileSystem.get(FileSystem.java:371)
~[hadoop-common-2.7.1.jar:na]at
org.apache.hadoop.fs.FileSystem.get(FileSystem.java:170)
~[hadoop-common-2.7.1.jar:na]at
org.apache.drill.exec.store.dfs.DrillFileSystem.(DrillFileSystem.java:91)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.util.ImpersonationUtil$2.run(ImpersonationUtil.java:219)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.util.ImpersonationUtil$2.run(ImpersonationUtil.java:216)
~[drill-java-exec-1.11.0.jar:1.11.0]at
java.security.AccessController.doPrivileged(Native Method) ~[na:1.8.0_72]
at javax.security.auth.Subject.doAs(Subject.java:422) ~[na:1.8.0_72]
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)
~[hadoop-common-2.7.1.jar:na]at
org.apache.drill.exec.util.ImpersonationUtil.createFileSystem(ImpersonationUtil.java:216)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.util.ImpersonationUtil.createFileSystem(ImpersonationUtil.java:208)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.store.dfs.WorkspaceSchemaFactory.accessible(WorkspaceSchemaFactory.java:153)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.store.dfs.FileSystemSchemaFactory$FileSystemSchema.(FileSystemSchemaFactory.java:77)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.store.dfs.FileSystemSchemaFactory.registerSchemas(FileSystemSchemaFactory.java:64)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.store.dfs.FileSystemPlugin.registerSchemas(FileSystemPlugin.java:149)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.store.StoragePluginRegistryImpl$DrillSchemaFactory.registerSchemas(StoragePluginRegistryImpl.java:396)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.store.SchemaTreeProvider.createRootSchema(SchemaTreeProvider.java:110)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.store.SchemaTreeProvider.createRootSchema(SchemaTreeProvider.java:99)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.ops.QueryContext.getRootSchema(QueryContext.java:164)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.ops.QueryContext.getRootSchema(QueryContext.java:153)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.ops.QueryContext.getNewDefaultSchema(QueryContext.java:139)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.planner.sql.SqlConverter.(SqlConverter.java:111)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:101)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:79)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:1050)
~[drill-java-exec-1.11.0.jar:1.11.0]at
org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:280)
~

Re: S3 Connection Issues

[Arjun kr]

If you were getting similar exception given below with original AWS jars with 
Drill 1.11, you can set DRILLBIT_JAVA_OPTS as mentioned in the previous mail. 
Earlier, there were regions that support only V2 and setting this parameter may 
be problematic with those regions. Now I see that all the regions support V4. 
So setting this property should be fine.


-- Without 'com.amazonaws.services.s3.enableV4=true' set in dril-env.sh


0: jdbc:drill:schema=dfs> select * from s3.root.`0_0_0.parquet` limit 10;
Error: [MapR][DrillJDBCDriver](500165) Query execution error: SYSTEM ERROR: 
AmazonS3Exception: Status Code: 400, AWS Service: Amazon S3, AWS Request ID: 
1A036C181EA7B8D4, AWS Error Code: null, AWS Error Message: Bad Request
[Error Id: bbe7d74a-b191-4ab2-99d7-10c5092c31d3 on arjun-lab-72:31010] 
(state=HY000,code=500165)
0: jdbc:drill:schema=dfs>


-- With 'com.amazonaws.services.s3.enableV4=true' set in dril-env.sh


"what ever the mind of man can conceive and believe, drill can query"
0: jdbc:drill:schema=dfs> select * from s3.root.`0_0_0.parquet` limit 10;
+-+
| id  |
+-+
| 1   |
+-+
1 row selected (7.975 seconds)
0: jdbc:drill:schema=dfs>

https://docs.aws.amazon.com/general/latest/gr/rande.html



Thanks,


Arjun

________
From: Arjun kr 
Sent: Tuesday, February 13, 2018 8:47 PM
To: user@drill.apache.org
Subject: Re: S3 Connection Issues

Hi Anup,


Please see if below steps help.


1) Add below option $DRILL_HOME/conf/drill-env.sh


 export DRILLBIT_JAVA_OPTS="$DRILLBIT_JAVA_OPTS 
-Dcom.amazonaws.services.s3.enableV4=true"

2) Restart the drillbit service and try querying S4 region.

Thanks,

Arjun

From: Anup Tiwari 
Sent: Tuesday, February 13, 2018 3:16 PM
To: user@drill.apache.org
Subject: Re: S3 Connection Issues

Hi Padma,
As you have mentioned "Last time I tried, using Hadoop 2.8.1 worked for me." so
have you build drill with hadoop 2.8.1 ? If yes then can you provide steps ?
Since i have downloaded tar ball of 1.11.0 and replaced hadoop-aws-2.7.1.jar
with hadoop-aws-2.9.0.jar but still not able to query successfully to s3 bucket;
queries are going in starting state.
We are trying to query : "ap-south-1" region which supports only v4 signature.





On Thu, Oct 19, 2017 9:44 AM, Padma Penumarthy ppenumar...@mapr.com  wrote:
Which AWS region are you trying to connect to ?

We have a problem connecting to regions which support only v4 signature

since the version of hadoop we include in Drill is old.

Last time I tried, using Hadoop 2.8.1 worked for me.



Thanks

Padma





> On Oct 18, 2017, at 8:14 PM, Charles Givre  wrote:

>

> Hello all,

> I’m trying to use Drill to query data in an S3 bucket and running into some
issues which I can’t seem to fix. I followed the various instructions online to
set up Drill with S3, and put my keys in both the conf-site.xml and in the
plugin config, but every time I attempt to do anything I get the following
errors:

>

>

> jdbc:drill:zk=local> show databases;

> Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon
S3, AWS Request ID: 56D1999BD1E62DEB, AWS Error Code: null, AWS Error Message:
Forbidden

>

>

> [Error Id: 65d0bb52-a923-4e98-8ab1-65678169140e on
charless-mbp-2.fios-router.home:31010] (state=,code=0)

> 0: jdbc:drill:zk=local> show databases;

> Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon
S3, AWS Request ID: 4D2CBA8D42A9ECA0, AWS Error Code: null, AWS Error Message:
Forbidden

>

>

> [Error Id: 25a2d008-2f4d-4433-a809-b91ae063e61a on
charless-mbp-2.fios-router.home:31010] (state=,code=0)

> 0: jdbc:drill:zk=local> show files in s3.root;

> Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon
S3, AWS Request ID: 2C635944EDE591F0, AWS Error Code: null, AWS Error Message:
Forbidden

>

>

> [Error Id: 02e136f5-68c0-4b47-9175-a9935bda5e1c on
charless-mbp-2.fios-router.home:31010] (state=,code=0)

> 0: jdbc:drill:zk=local> show schemas;

> Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon
S3, AWS Request ID: 646EB5B2EBCF7CD2, AWS Error Code: null, AWS Error Message:
Forbidden

>

>

> [Error Id: 954aaffe-616a-4f40-9ba5-d4b7c04fe238 on
charless-mbp-2.fios-router.home:31010] (state=,code=0)

>

> I have verified that the keys are correct but using the AWS CLI and downloaded
some of the files, but I’m kind of at a loss as to how to debug. Any
suggestions?

> Thanks in advance,

> — C







Regards,
Anup Tiwari

Sent with Mixmax

Re: S3 Connection Issues

[Arjun kr]

Hi Anup,


Please see if below steps help.


1) Add below option $DRILL_HOME/conf/drill-env.sh


 export DRILLBIT_JAVA_OPTS="$DRILLBIT_JAVA_OPTS 
-Dcom.amazonaws.services.s3.enableV4=true"

2) Restart the drillbit service and try querying S4 region.

Thanks,

Arjun

From: Anup Tiwari 
Sent: Tuesday, February 13, 2018 3:16 PM
To: user@drill.apache.org
Subject: Re: S3 Connection Issues

Hi Padma,
As you have mentioned "Last time I tried, using Hadoop 2.8.1 worked for me." so
have you build drill with hadoop 2.8.1 ? If yes then can you provide steps ?
Since i have downloaded tar ball of 1.11.0 and replaced hadoop-aws-2.7.1.jar
with hadoop-aws-2.9.0.jar but still not able to query successfully to s3 bucket;
queries are going in starting state.
We are trying to query : "ap-south-1" region which supports only v4 signature.





On Thu, Oct 19, 2017 9:44 AM, Padma Penumarthy ppenumar...@mapr.com  wrote:
Which AWS region are you trying to connect to ?

We have a problem connecting to regions which support only v4 signature

since the version of hadoop we include in Drill is old.

Last time I tried, using Hadoop 2.8.1 worked for me.



Thanks

Padma





> On Oct 18, 2017, at 8:14 PM, Charles Givre  wrote:

>

> Hello all,

> I’m trying to use Drill to query data in an S3 bucket and running into some
issues which I can’t seem to fix. I followed the various instructions online to
set up Drill with S3, and put my keys in both the conf-site.xml and in the
plugin config, but every time I attempt to do anything I get the following
errors:

>

>

> jdbc:drill:zk=local> show databases;

> Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon
S3, AWS Request ID: 56D1999BD1E62DEB, AWS Error Code: null, AWS Error Message:
Forbidden

>

>

> [Error Id: 65d0bb52-a923-4e98-8ab1-65678169140e on
charless-mbp-2.fios-router.home:31010] (state=,code=0)

> 0: jdbc:drill:zk=local> show databases;

> Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon
S3, AWS Request ID: 4D2CBA8D42A9ECA0, AWS Error Code: null, AWS Error Message:
Forbidden

>

>

> [Error Id: 25a2d008-2f4d-4433-a809-b91ae063e61a on
charless-mbp-2.fios-router.home:31010] (state=,code=0)

> 0: jdbc:drill:zk=local> show files in s3.root;

> Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon
S3, AWS Request ID: 2C635944EDE591F0, AWS Error Code: null, AWS Error Message:
Forbidden

>

>

> [Error Id: 02e136f5-68c0-4b47-9175-a9935bda5e1c on
charless-mbp-2.fios-router.home:31010] (state=,code=0)

> 0: jdbc:drill:zk=local> show schemas;

> Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon
S3, AWS Request ID: 646EB5B2EBCF7CD2, AWS Error Code: null, AWS Error Message:
Forbidden

>

>

> [Error Id: 954aaffe-616a-4f40-9ba5-d4b7c04fe238 on
charless-mbp-2.fios-router.home:31010] (state=,code=0)

>

> I have verified that the keys are correct but using the AWS CLI and downloaded
some of the files, but I’m kind of at a loss as to how to debug. Any
suggestions?

> Thanks in advance,

> — C







Regards,
Anup Tiwari

Sent with Mixmax

Re: Unable to setup hive plugin in Drill 1.11.0

[Arjun kr]

Hi Anup,


As Sorabh mentioned, you seem to be using hive 2.1.1 jars in Drill classpath 
based on the stack trace. Did you build drill package by customizing hive 
version to 2.1.1 or added hive 2.1.1 jars in Drill classpath manually?  I could 
see that Drill 1.12 (latest released), 1.11  and 1.10 are bundled with hive 
1.2.1 client.


You could try with drill 1.11 tarball that includes hive 1.2.1 client. Also, 
you may check the version of hive client jars in $DRILL_HOME/jars/3rdparty/ 
directory from the Drill 1.10 cluster where it is working fine.


http://archive.apache.org/dist/drill/drill-1.11.0/


# List of hive client jars from Drill 1.11 tarball extracted.


$ ls -1 apache-drill-1.11.0/jars/3rdparty/hive-*
apache-drill-1.11.0/jars/3rdparty/hive-contrib-1.2.1.jar
apache-drill-1.11.0/jars/3rdparty/hive-hbase-handler-1.2.1.jar
apache-drill-1.11.0/jars/3rdparty/hive-metastore-1.2.1.jar
$


Thanks,


Arjun



From: Anup Tiwari 
Sent: Tuesday, February 13, 2018 12:01 PM
To: user@drill.apache.org
Subject: Re: Unable to setup hive plugin in Drill 1.11.0

Also forgot to mention that we are using Drill 1.10 with 2.1 on our one of
clusters and it is working properly. Just the change is in Hadoop Version and we
are using MySQL community version 5.7.16. Please find below details :-
Apache Drill Version : 1.10.0MySQL Community :5.7.16Apache Hive : 2.1.1Apache
Hadoop : 2.7.3
So does this mean the issue is with hadoop version ? As i can seehadoop 2.7.1
related jars in 3rdparty jar of drill.





On Tue, Feb 13, 2018 11:33 AM, Anup Tiwari anup.tiw...@games24x7.com  wrote:
Hi Sorabh,
Thanks for reply. We are using below combination :-
Apache Drill Version : 1.11.0MySQL Percona : 5.6.35-81.0Apache Hive : 2.1.1
Apache Hadoop : 2.8.0
As you have mentioned that Drill 1.11 with hive 2.1 combination is not supported
so do we have any link from where i can get an idea of which combination is
supported? Also does not supported means we can't use this combination(i mean
don't we have any workaround)?





On Tue, Feb 13, 2018 12:57 AM, Sorabh Hamirwasia shamirwa...@mapr.com  wrote:
Hi Anup,




>From the stack trace which you have shared it looks like you are trying to use
Hive2.1 with drill-1.11.0. This s not a supported combination[1]. There is a
PR[2] open for it in 1.13 though.







Also from the stack trace it's pointing towards an invalid field in HiveConf
METASTORE_BATCH_RETRIEVE_OBJECTS_MAX which got introduced in hive-2.1 only.







java.lang.Thread.run(Thread.java:745) [na:1.8.0_72]Caused by:




java.lang.NoSuchFieldError: METASTORE_BATCH_RETRIEVE_OBJECTS_MAX at




org.apache.hadoop.hive.metastore.HiveMetaStoreClient.(HiveMetaStoreClient.java:222)




~[hive-metastore-2.1.1.jar:2.1.1] at




org.apache.hadoop.hive.metastore.HiveMetaStoreClient.(HiveMetaStoreClient.java:210)




~[hive-metastore-2.1.1.jar:2.1.1] at




org.apache.drill.exec.store.hive.DrillHiveMetaStoreClient.(DrillHiveMetaStoreClient.java:140)







Usually any error/exxception should get logged into drillbit.out as well. May be
you can try seeing there.







[1]: https://github.com/apache/drill/blob/1.11.0/pom.xml#L51
[https://avatars3.githubusercontent.com/u/47359?s=400&v=4]

apache/drill
github.com
drill - Mirror of Apache Drill







[2]: https://github.com/apache/drill/pull/







Thanks,

Sorabh









From: Anup Tiwari 

Sent: Monday, February 12, 2018 9:21 AM

To: user@drill.apache.org

Subject: Re: Unable to setup hive plugin in Drill 1.11.0




I don't know where to look for logs as i have tried multiple time creating

hive plugin. But one time i can see below stack is printed in drillbit.log(i

don't know if it came due to plugin creation or not as i have tried multiple

time but nothing got printed in drillbit.log except below which is present in

logs only once) :-

Error

org.apache.drill.common.exceptions.ExecutionSetupException: Failure setting up

new storage plugin configuration for config org.apache.drill.exec.

store.hive.HiveStoragePluginConfig@f149f108 at

org.apache.drill.exec.store.StoragePluginRegistryImpl.create(StoragePluginRegistryImpl.java:354)

~[drill-java-exec-1.11.0.jar:1.11.0] at

org.apache.drill.exec.store.StoragePluginRegistryImpl.createOrUpdate(StoragePluginRegistryImpl.java:238)

~[drill-java-exec-1.11.0.jar:1.11.0] at

org.apache.drill.exec.store.StoragePluginRegistryImpl.getPlugin(StoragePluginRegistryImpl.java:289)

~[drill-java-exec-1.11.0.jar:1.11.0] at

org.apache.drill.exec.server.rest.StorageResources.getStoragePluginJSON(StorageResources.java:101)

[drill-java-exec-1.11.0.jar:1.11.0] at

org.apache.drill.exec.server.rest.StorageResources.getStoragePlugin(StorageResources.java:115)

[drill-java-exec-1.11.0.jar:1.11.0] at

sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_72]

  at

sun.ref

Re: PCAP files with Apache Drill and Sergeant R

[Arjun kr]

Hi Houssem,


You should be able to query it using DFS plugin and S3 storage plugin ( I have 
not tried it with S3 plugin though). You can enable pcap format in storage 
plugin definition as given below.


"formats": {

  ,

  "pcap": { "type": "pcap" }

 }


Also, it would be best to use Drill 1.12 that includes the fix for 
DRILL-5790 as per 
https://drill.apache.org/docs/apache-drill-1-12-0-release-notes/ .


I don't have any clue about using Drill with 'R Sergeant library' library. 
Hopefully, others can throw any lights on this question.


Thanks,


Arjun



From: Houssem Hosni 
Sent: Monday, February 5, 2018 9:18 PM
To: user@drill.apache.org
Subject: PCAP files with Apache Drill and Sergeant R

Hi,
I am sending this mail with a hope to get some help from you.
I am working on making some analysis and prediction models on large pcap
files.
Can Apache Drill with R Sergeant library help me in this context.
Actually the pcap files are so large (MAWI) and they are available on the
web(http://mawi.wide.ad.jp/mawi/samplepoint-F/2018/). I want to access
them via apache Drill and then make some analysis using Sergeant package
(R) that works well with Drill.
Should I bring those large MAWI pcap files on the web to Amazon S3 and
then access them with DRILL or is it possible to access them directly
without amazon storage ?
What steps should I start with ?
Plese HELP
Special THANKS in advance for considering my request.
Best regards,
Houssem Hosni
LIP6 - Sorbonne University
houssem.ho...@lip6.fr
Place Jussieu, 75005 Paris.
Tel: (+0033)0644087200

Re: Apache drill validation error, table not found

[Arjun kr]

Is the failure due to ORC file not being supported by DFS/S3 plugin?  This 
error may come if you are querying on unsupported format or if you don't have 
the format defined in corresponding storage plugin definition.

Below is sample execution for junk format 'thenga' not defined in storage 
plugin definition.

$ hadoop fs -ls /tmp/sample.thenga
-rwxr-x---   3 manga manga 37 2017-10-16 16:31 /tmp/sample.thenga

$sqlline
0: jdbc:drill:schema=dfs> select * from dfs.`/tmp/sample.thenga`;
Error: [MapR][DrillJDBCDriver](500165) Query execution error: VALIDATION ERROR: 
From line 1, column 15 to line 1, column 17: Table 'dfs./tmp/sample.thenga' not 
found

SQL Query null

[Error Id: 154d6187-eea2-4afe-ab64-ac0f287a3433 on drill-node:31010] 
(state=HY000,code=500165)
0: jdbc:drill:schema=dfs>

One option would be to use Hive table built on top of these files and use hive 
storage plugin in Drill ( if hive table on top of S3 ORC files works fine). Or 
Parquet files would be a good choice with Drill.

Thanks,

Arjun


From: Sorabh Hamirwasia 
Sent: Tuesday, February 6, 2018 1:11 PM
To: user@drill.apache.org
Subject: Re: Apache drill validation error, table not found

Hi,

Can you please try following instruction here [1] once.? Also after 
configuration you can try to list tables by "show databases" to make sure setup 
is fine.


And the query should be:

SELECT * FROM s3.`./data/part-r-001` LIMIT 100


[1]: https://drill.apache.org/docs/s3-storage-plugin/
S3 Storage Plugin - Apache 
Drill
drill.apache.org
Drill works with data stored in the cloud. With a few simple steps, you can 
configure the S3 storage plugin for Drill and be off to the races running 
queries.





Thanks,
Sorabh



From: Bo Qiang 
Sent: Monday, February 5, 2018 9:17 PM
To: user@drill.apache.org
Subject: Apache drill validation error, table not found

I have been trying to use Apache drill to query data in s3 buckets. Inside
of Drill Explorer, I met the following errors:

ERROR [HY000] [MapR][Drill] (1040) Drill failed to execute the query:
SELECT * FROM `s3`.`default`.`./data/part-r-001` LIMIT 100
[30034]Query execution error. Details:[
VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Table
's3.default../data/part-r-001' not found

SQL Query null

[Error Id: fa18496f-038f-46b1-9d3f-c40809511768 on 10.23.58.220:31010]
]
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior
behavior)
   at DrillExplorer.DROdbcProvider.GetStatmentColumns(String in_query)

The part-r-001 file are of ORC format.

Is there anything fix? Thanks a lot!

Re: convert epoch time stamp to timestamp

[Arjun kr]

Looks like you are passing epoch timestamp value in milliseconds instead of 
seconds. You can divide by 1000 or remove last three digits to see if you are 
getting the desired result.


# Divide by 1000


SELECT TO_TIMESTAMP(1515545336591/1000) FROM (VALUES(1));
++
| EXPR$0 |
++
| 2018-01-09 19:48:56.0  |
++
1 row selected (0.2 seconds)

# last 3 digits removed.

SELECT TO_TIMESTAMP(1515545336) FROM (VALUES(1));
++
| EXPR$0 |
++
| 2018-01-09 19:48:56.0  |
++
1 row selected (0.461 seconds)


Thanks,

Arjun



From: Divya Gehlot 
Sent: Monday, January 15, 2018 12:46 PM
To: user@drill.apache.org
Subject: Re: convert epoch time stamp to timestamp

When I
SELECT
CAST(FROM_UNIXTIME(t.`timestamp`) AS TIMESTAMP) AS `timestamp`
FROM
path/data/file  t limit 10 ;

49995-02-13T19:51:48.000Z
49995-04-22T15:47:05.000Z
49996-09-06T03:21:25.000Z
49997-03-05T01:20:19.000Z

I get these values .

Thanks,
Divya

On 15 January 2018 at 15:09, Divya Gehlot  wrote:

> Hi ,
> One of field in my data file is  in epoch time stamp .
> The values would be similar like 1515545336591
>
> How can I convert this to '-MM-DD HH:mm:ss' format .
>
> Thanks,
> Divya
>

Re: Illegal Argument Exception while convert unix date format to drill timestamp

[Arjun kr]

Pls see if this works for you.


0: jdbc:drill:schema=dfs> SELECT TO_TIMESTAMP('Sun Apr 1 00:00:01 UTC 2018', 'E 
MMM d HH:mm:ss z ') FROM (VALUES(1));
++
| EXPR$0 |
++
| 2018-04-01 00:00:01.0  |
++
1 row selected (0.165 seconds)
0: jdbc:drill:schema=dfs>



Thanks,

Arjun kr


From: Divya Gehlot 
Sent: Thursday, December 14, 2017 9:12 AM
To: user@drill.apache.org
Subject: Illegal Argument Exception while convert unix date format to drill 
timestamp

Hi,
Does Drill supprts to convert Unix date format to Drill timstamp ?

Unix TimeStamp : Thu Dec 14 03:40:50 UTC 2017
When I Cast to Drill time stamp I get Illegal Argument Exception.


Thanks,
Divya

Re: Drill Capacity

[Arjun kr]

Hi Yun,


Looking at the log shared, You seems to be running below query.


2017-11-06 15:09:37,383 [25ff3e7e-39ef-a175-93e7-e4e62b284add:foreman] INFO  
o.a.drill.exec.work.foreman.Foreman - Query text for query id 
25ff3e7e-39ef-a175-93e7-e4e62b284add: SELECT * FROM 
`dfs`.`Inputs`.`./Compliance.json` LIMIT 100


Below is the exception with query failure.


2017-11-06 15:09:45,852 [25ff3e7e-39ef-a175-93e7-e4e62b284add:frag:0:0] INFO  
o.a.d.e.vector.complex.fn.JsonReader - User Error Occurred: You tried to write 
a VarChar type when you are using a ValueWriter of type NullableBitWriterImpl. 
(You tried to write a VarChar type when you are using a ValueWriter of type 
NullableBitWriterImpl.)^M
org.apache.drill.common.exceptions.UserException: DATA_READ ERROR: You tried to 
write a VarChar type when you are using a ValueWriter of type 
NullableBitWriterImpl.

It could be related to schema change. Can you try setting below session 
parameter if not tried already?


alter session set `store.json.all_text_mode`=true;



Thanks,

Arjun

From: Yun Liu 
Sent: Tuesday, November 7, 2017 1:46 AM
To: user@drill.apache.org
Subject: RE: Drill Capacity

Hi Arjun and Paul,

Yep those are turned and I am reading it from sqlline.log. Only max allocation 
number I am reading is 10,000,000,000. Posted the logs in my Dropbox:
https://www.dropbox.com/sh/5akxrzm078jsabw/AADuD92swH6c9jwijTjkkac_a?dl=0
[https://cfl.dropboxstatic.com/static/images/logo_catalog/glyph...@2x-vfla6ltfz.png]<https://www.dropbox.com/sh/5akxrzm078jsabw/AADuD92swH6c9jwijTjkkac_a?dl=0>

Drill<https://www.dropbox.com/sh/5akxrzm078jsabw/AADuD92swH6c9jwijTjkkac_a?dl=0>
www.dropbox.com
Shared with Dropbox




Thank you!
Yun

-Original Message-
From: Arjun kr [mailto:arjun...@outlook.com]
Sent: Monday, November 6, 2017 1:20 PM
To: user@drill.apache.org
Subject: Re: Drill Capacity

Hi Yun,


Are you running in Drill embedded mode ? If so , the logs will be available in 
sqllline.log and drillbit.log will not be populated. You can enable DEBUG 
logging in logback.xml , run the query and share log file as Paul suggested.


Edit $DRILL_HOME/conf/logback.xml to enable DEBUG level logging.


 


  


Thanks,


Arjun


From: Paul Rogers 
Sent: Monday, November 6, 2017 10:56 PM
To: user@drill.apache.org
Subject: Re: Drill Capacity

Hi Yun,

Sorry, it is a bit confusing. The log will contain two kinds of JSON. One is 
the query profile, which is what you found. The other is the physical plan used 
to run the query. It is the physical plan you want to find; that is the one 
that has the max allocation.

If you can post your logs somewhere, I'll d/l them and take a look.

- Paul

> On Nov 6, 2017, at 7:27 AM, Yun Liu  wrote:
>
> Hi Paul,
>
> I am using Drill v 1.11.0 so I am only seeing sqlline.log and 
> sqlline_queries.log. hopefully the same.
>
> I am following your instructions and I am not seeing any maxAllocation other 
> than 10,000,000,000. No other number (or small number) than this. The query 
> profile reads the following:
>
> {"queryId":"25ff81fc-3b7a-a840-b557-d2194cc6819a","schema":"","queryTe
> xt":"SELECT * FROM `dfs`.`Inputs`.`./ Compliance.json` LIMIT
> 100","start":1509981699406,"finish":1509981707544,"outcome":"FAILED","
> username":"","remoteAddress":"localhost"}
>
> Is this what you're looking for?
>
> Thanks,
> Yun
>
> -Original Message-
> From: Paul Rogers [mailto:prog...@mapr.com]
> Sent: Friday, November 3, 2017 6:45 PM
> To: user@drill.apache.org
> Subject: Re: Drill Capacity
>
> Thanks for the info. Clearly you are way ahead of me.
>
> In issue 1, although you have only four (top level) fields, your example 
> shows that you have many nested fields. It is the total field count (across 
> all maps) that drives total width. And, it is the total amount of data that 
> drives memory consumption.
>
> You mentioned each record is 64KB and 3K rows. That suggests a total size of 
> around 200MB. But, you mention the total file size is 400MB. So, either the 
> rows are twice as large, or there are twice as many. If you have 3K rows of 
> 128MB each, then each batch of data is 400MB, which is pretty large.
>
> If your records are 64K in size, and we read 4K per batch, then the total 
> size is 256MB, which is also large.
>
> So, we are dealing with jumbo records and you really want the "batch size 
> control" feature that we are working on, but have not yet shipped.
>
> Let's work out the math. How many sorts in your query? What other operators 
> does the query include? Let's assume a single sort.
>
> Max query memory is 10 GB. 10 GB / 

Re: Drill Capacity

[Arjun kr]

Hi Yun,


Are you running in Drill embedded mode ? If so , the logs will be available in 
sqllline.log and drillbit.log will not be populated. You can enable DEBUG 
logging in logback.xml , run the query and share log file as Paul suggested.


Edit $DRILL_HOME/conf/logback.xml to enable DEBUG level logging.


 


  


Thanks,


Arjun


From: Paul Rogers 
Sent: Monday, November 6, 2017 10:56 PM
To: user@drill.apache.org
Subject: Re: Drill Capacity

Hi Yun,

Sorry, it is a bit confusing. The log will contain two kinds of JSON. One is 
the query profile, which is what you found. The other is the physical plan used 
to run the query. It is the physical plan you want to find; that is the one 
that has the max allocation.

If you can post your logs somewhere, I’ll d/l them and take a look.

- Paul

> On Nov 6, 2017, at 7:27 AM, Yun Liu  wrote:
>
> Hi Paul,
>
> I am using Drill v 1.11.0 so I am only seeing sqlline.log and 
> sqlline_queries.log. hopefully the same.
>
> I am following your instructions and I am not seeing any maxAllocation other 
> than 10,000,000,000. No other number (or small number) than this. The query 
> profile reads the following:
>
> {"queryId":"25ff81fc-3b7a-a840-b557-d2194cc6819a","schema":"","queryText":"SELECT
>  * FROM `dfs`.`Inputs`.`./ Compliance.json` LIMIT 
> 100","start":1509981699406,"finish":1509981707544,"outcome":"FAILED","username":"","remoteAddress":"localhost"}
>
> Is this what you're looking for?
>
> Thanks,
> Yun
>
> -Original Message-
> From: Paul Rogers [mailto:prog...@mapr.com]
> Sent: Friday, November 3, 2017 6:45 PM
> To: user@drill.apache.org
> Subject: Re: Drill Capacity
>
> Thanks for the info. Clearly you are way ahead of me.
>
> In issue 1, although you have only four (top level) fields, your example 
> shows that you have many nested fields. It is the total field count (across 
> all maps) that drives total width. And, it is the total amount of data that 
> drives memory consumption.
>
> You mentioned each record is 64KB and 3K rows. That suggests a total size of 
> around 200MB. But, you mention the total file size is 400MB. So, either the 
> rows are twice as large, or there are twice as many. If you have 3K rows of 
> 128MB each, then each batch of data is 400MB, which is pretty large.
>
> If your records are 64K in size, and we read 4K per batch, then the total 
> size is 256MB, which is also large.
>
> So, we are dealing with jumbo records and you really want the “batch size 
> control” feature that we are working on, but have not yet shipped.
>
> Let’s work out the math. How many sorts in your query? What other operators 
> does the query include? Let’s assume a single sort.
>
> Max query memory is 10 GB. 10 GB / 1 sort / max width of 5 = 2 GB per sort. 
> Since your batches are ~400 MB, things should work.
>
> Since things don’t work, I suspect that we’re missing something.  (Note that 
> the memory size we just calculated does not match the numbers shown in an 
> earlier post in which the sort got just ~40 MB of memory…)
>
> Try this:
>
> * With your current settings, enable debug-level logging. Run your query.
>
> * Open the Drillbit log. Look for the JSON version of the query plan (there 
> will be two). One will tell you how much memory is given to the sort:
>
> maxAllocation: (some number)
>
> * Ignore the one that says 10,000,000, find the one with a smaller number. 
> What is that number?
>
> * Then, look in the query profile for your query. Look at the peak memory for 
> your JSON reader scan operator. The peak memory more-or-less reflects the 
> batch size. What is that number?
>
> With those, we can tell if the settings and sizes we think we are using are, 
> in fact, correct.
>
> Thanks,
>
> - Paul
>
>> On Nov 3, 2017, at 1:19 PM, Yun Liu  wrote:
>>
>> Hi Paul,
>>
>> Thanks for you detailed explanation. First off- I have 2 issues and I wanted 
>> to clear it out before continuing.
>>
>> Current setting: planner.memory.max_query_memory_per_node = 10GB, HEAP
>> = 12G, Direct memory = 32G, Perm 1024M, and planner.width.max_per_node
>> = 5
>>
>> Issue # 1:
>> When loading a json file with 400MB I keep getting a DATA_READ ERROR.
>> Each record in the file is about 64KB. Since it's a json file, there are 
>> only 4 fields per each record. Not sure how many records this file contains 
>> as it's too large to open with any tools, but I am guessing about 3k rows.
>> With all the recommendations provided by various experts, nothing has worked.
>>
>> Issue 2#:
>> While processing a query with is a join of 2 functional .json files, I am 
>> getting a RESOURCE ERROR: One or more nodes ran out of memory while 
>> executing the query. These 2 json files alone process fine but when joined 
>> together, Drill throws me that error.
>> Json#1 is 11k KB, has 8 fields with 74091 rows
>> Json#2 is 752kb, has 8 fields with 4245 rows
>>
>> Besides breaking them up to smaller files, not sure what else I could do.
>>
>> 

Re: Drill Capacity

[Arjun kr]

I have seen a use-case where query fails for 12 GB single json file having 
structure '‘{ "key":[obj1, obj2, obj3..objn]}’'. Here json file has a key 
element and value is array of json object 'obj'. There were around 175K objects 
in this array and each obj is again complex json object with nested array 
elements. From what I understood, Drill reads entire file content as  single 
json record (which actually is) and fails with DATA_READ ERROR.


The solution was to re organize the data to either of following structure. Also 
to break single file into multiple smaller file for better parallelism.


Structure 2: File has array of json object like below
[ {obj1},{obj2}..,{objn}]

Structure 3:  File has  json objects as below
{obj1}
{obj1}
..
{objn}


I was checking if this is the case here..


Thanks,


Arjun



From: Yun Liu 
Sent: Saturday, November 4, 2017 2:27 AM
To: user@drill.apache.org
Subject: RE: Drill Capacity

Hi Arjun,

Column 4 has the most data and a bit long here. The other 3 columns has maybe a 
word or 2. Thanks for your patience.

[ {
  "type" : "quality-rules",
  "reference" : {
"href" : "",
"name" : "Avoid unreferenced Tables",
"key" : "1634",
"critical" : false
  },
  "result" : {
"grade" : 2,
"violationRatio" : {
  "totalChecks" : 52,
  "failedChecks" : 5,
  "successfulChecks" : 47,
  "ratio" : 0.9038461538461539
},
"evolutionSummary" : {
  "addedCriticalViolations" : 0,
  "removedCriticalViolations" : 0,
  "addedViolations" : 1,
  "removedViolations" : 0
}
  },
  "technologyResults" : [ {
"technology" : "Microsoft T-SQL",
"result" : {
  "grade" : 2.0769230769230775,
  "violationRatio" : {
"totalChecks" : 52,
"failedChecks" : 5,
"successfulChecks" : 47,
"ratio" : 0.9038461538461539
  },
  "evolutionSummary" : {
"addedCriticalViolations" : 0,
"removedCriticalViolations" : 0,
"addedViolations" : 1,
"removedViolations" : 0
  }
}
  } ]
}, {
  "type" : "quality-rules",
  "reference" : {
"href" : "",
"name" : "Namespace naming convention - case control",
"key" : "3550",
"critical" : false
  },
  "result" : {
"grade" : 4.0,
"violationRatio" : {
  "totalChecks" : 31,
  "failedChecks" : 0,
  "successfulChecks" : 31,
  "ratio" : 1.0
},
"evolutionSummary" : {
  "addedCriticalViolations" : 0,
  "removedCriticalViolations" : 0,
  "addedViolations" : 0,
  "removedViolations" : 0
}
  },
  "technologyResults" : [ {
"technology" : ".NET",
"result" : {
  "grade" : 4.0,
  "violationRatio" : {
"totalChecks" : 31,
"failedChecks" : 0,
"successfulChecks" : 31,
"ratio" : 1.0
  },
  "evolutionSummary" : {
"addedCriticalViolations" : 0,
"removedCriticalViolations" : 0,
"addedViolations" : 0,
"removedViolations" : 0
  }
}
  } ]
}, {
  "type" : "quality-rules",
  "reference" : {
"href" : "2",
"name" : "Interface naming convention - case and character set control",
"key" : "3554",
"critical" : false
  },
  "result" : {
"grade" : 4.0,
"violationRatio" : {
  "totalChecks" : 10,
  "failedChecks" : 0,
  "successfulChecks" : 10,
  "ratio" : 1.0
},
"evolutionSummary" : {
  "addedCriticalViolations" : 0,
  "removedCriticalViolations" : 0,
  "addedViolations" : 0,
  "removedViolations" : 0
}
  },
  "technologyResults" : [ {
"technology" : ".NET",
"result" : {
  "grade" : 4.0,
  "violationRatio" : {
"totalChecks" : 10,
"failedChecks" : 0,
"successfulChecks" : 10,
"ratio" : 1.0
  },
  "evolutionSummary" : {
"addedCriticalViolations" : 0,
"removedCriticalViolations" : 0,
"addedViolations" : 0,
"removedViolations" : 0
  }
}
  } ]
}, {
  "type" : "quality-rules",
  "reference" : {
"href" : "",
"name" : "Enumerations naming convention - case and character set control",
"key" : "3558",
"critical" : false
  },
  "result" : {
"grade" : 4.0,
"violationRatio" : {
  "totalChecks" : 7,
  "failedChecks" : 0,
  "successfulChecks" : 7,
  "ratio" : 1.0
},
"evolutionSummary" : {
  "addedCriticalViolations" : 0,
  "removedCriticalViolations" : 0,
  "addedViolations" : 0,
  "removedViolations" : 0
}
  },
  "technologyResults" : [ {
"technology" : ".NET",
"result" : {
  "grade" : 4.0,
  "violationRatio" : {
"totalChecks" : 7,
"failedChecks" : 0,
"successfulChecks" : 7,
"ratio" : 1.0
  },
  "evolutionSummary" : {
"addedCriticalViolations" : 0,
"removedCriticalViolations" : 0,
"addedViolations" : 0,
"removedViolations" : 0
  }
}
  } ]
}, {
  "type" : "quality-rules",
 

Re: Drill Capacity

[Arjun kr]

Hi Yun,


Could you please provide more details on your json data structure for 400 MB 
json file.


Structure 1:


‘{ "key":[obj1, obj2, obj3..objn]}’


Structure 2:
[ {obj1},{obj2}..,{objn}]

Structure 3:
{obj1}
{obj1}
..
{objn}



Thanks,


Arjun



From: Yun Liu 
Sent: Saturday, November 4, 2017 1:49 AM
To: user@drill.apache.org
Subject: RE: Drill Capacity

Hi Paul,

Thanks for you detailed explanation. First off- I have 2 issues and I wanted to 
clear it out before continuing.

Current setting: planner.memory.max_query_memory_per_node = 10GB, HEAP = 12G, 
Direct memory = 32G, Perm 1024M, and planner.width.max_per_node = 5

Issue # 1:
When loading a json file with 400MB I keep getting a DATA_READ ERROR.
Each record in the file is about 64KB. Since it's a json file, there are only 4 
fields per each record. Not sure how many records this file contains as it's 
too large to open with any tools, but I am guessing about 3k rows.
With all the recommendations provided by various experts, nothing has worked.

Issue 2#:
While processing a query with is a join of 2 functional .json files, I am 
getting a RESOURCE ERROR: One or more nodes ran out of memory while executing 
the query. These 2 json files alone process fine but when joined together, 
Drill throws me that error.
Json#1 is 11k KB, has 8 fields with 74091 rows
Json#2 is 752kb, has 8 fields with 4245 rows

Besides breaking them up to smaller files, not sure what else I could do.

Thanks for the help so far!

Yun

-Original Message-
From: Paul Rogers [mailto:prog...@mapr.com]
Sent: Thursday, November 2, 2017 11:06 PM
To: user@drill.apache.org
Subject: Re: Drill Capacity

Hi Yun,

I’m going to give you multiple ways to understand the issue based on the 
information you’ve provided. I generally like to see the full logs to diagnose 
such problems, but we’ll start with what you’ve provided thus far.

How large is each record in your file? How many fields? How many bytes? 
(Alternatively, how big is a single input file and how many records does it 
contain?)

You mention the limit of 64K columns in CSV. This makes me wonder if you have a 
“jumbo” record. If each individual record is large, then there won’t be enough 
space in the sort to take even a single batch of records, and you’ll get the 
sv2 error that you saw.

We can guess the size, however, from the info you provided:

batchGroups.size 1
spilledBatchGroups.size 0
allocated memory 42768000
allocator limit 41943040

This says you have a batch in memory and are trying to allocate some memory 
(the “sv2”). The allocated memory number tells us that each batch size is 
probably ~43 MB. But, the sort only has 42 MB to play with. The sort needs at 
least two batches in memory to make progress, hence the out-of-memory errors.

It would be nice to confirm this from the logs, but unfortunately, Drill does 
not normally log the size of each batch. As it turns out, however, the 
“managed” version that Boaz mentioned added more logging around this problem: 
it will tell you how large it thinks each batch is, and will warn if you have, 
say, a 43 MB batch but only 42 MB in which to sort.

(If you do want to use the “managed” version of the sort, I suggest you try 
Drill 1.12 when it is released as that version contains additional fixes to 
handle constrained memory.)

Also, at present, The JSON record reader loads 4096 records into each batch. If 
your file has at least that many records, then we can guess each record is 
about 43 MB / 4096 =~ 10K in size. (You can confirm, as noted above, by 
dividing total file size by record count.)

We are doing work to handle such large batches, but the work is not yet 
available in a release. Unfortunately, in the meanwhile, we also don’t let you 
control the batch size. But, we can provide another solution.

Let's explain why the message you provided said that the “allocator limit” was 
42 MB. Drill does the following to allocate memory to the sort:

* Take the “max query memory per node” (default of 2 GB regardless of actual 
direct memory),
* Divide by the number of sort operators in the plan (as shown in the 
visualized query profile)
* Divide by the “planner width” which is, by default, 70% of the number of 
cores on your system.

In your case, if you are using the default 2 GB total, but getting 41 MB per 
sort, the divisor is 50. Maybe you have 2 sorts and 32 cores? (2 * 32 * 70% =~ 
45.) Or some other combination.

We can’t reduce the number of sorts; that’s determined by your query. But, we 
can play with the other numbers.

First, we can increase the memory per query:

ALTER SESSION SET `planner.memory.max_query_memory_per_node` = 4,294,967,296

That is, 4 GB. This obviously means you must have at least 6 GB of direct 
memory; more is better.

And/or, we can reduce the number of fragments:

ALTER SESSION SET `planner.width.max_per_node` = 

The value is a bit tricky. Drill normally creates a number of fragments equal 
to 70%

Re: Apache Drill connection issue in tableau

[Arjun kr]

This property is a startup option (boot option) which can be set in 
drill-override.conf ($DRILL_HOME/conf).You can specify it as highlighted below.

drill.exec: {
  cluster-id: "",
  zk.connect: "",
  rpc.user.timeout :60

}

Restart Drill service once changes are made in all drillbit nodes. You can 
verify the value set using below SQL.

select * from sys.boot where name = 'drill.exec.rpc.user.timeout';

The options tab in Web UI lists system options which can be set at 
system/session level.


https://drill.apache.org/docs/start-up-options/


https://drill.apache.org/docs/configuration-options-introduction/



Thanks,

Arjun


From: Divya Gehlot 
Sent: Friday, November 3, 2017 11:51 AM
To: user@drill.apache.org
Subject: Re: Apache Drill connection issue in tableau

Hi Timothy,
Thanks for the help!
Yes you are right I could see below error message in the logs
Where can I set the rpc time out as I couldnt find the rpc timeout config
in when login through web console -> Options
Could you please help me pointing it ?

2017-11-03 04:48:30,413 [BitServer-4] INFO
o.a.drill.exec.work.foreman.Foreman - Failure while trying communicate
query result to initiating client. This would happen if a client is
disconnected before response notice can be sent.
org.apache.drill.exec.rpc.RpcException: Failure sending message.
at org.apache.drill.exec.rpc.RpcBus.send(RpcBus.java:124)
[drill-rpc-1.10.0.jar:1.10.0]
at 
org.apache.drill.exec.rpc.user.UserServer$BitToUserConnection.sendResult(UserServer.java:199)
[drill-java-exec-1.10.0.jar:1.10.0]
at 
org.apache.drill.exec.work.foreman.Foreman$ForemanResult.close(Foreman.java:868)
[drill-java-exec-1.10.0.jar:1.10.0]
at 
org.apache.drill.exec.work.foreman.Foreman.moveToState(Foreman.java:1001)
[drill-java-exec-1.10.0.jar:1.10.0]
at 
org.apache.drill.exec.work.foreman.Foreman.access$2600(Foreman.java:116)
[drill-java-exec-1.10.0.jar:1.10.0]
at 
org.apache.drill.exec.work.foreman.Foreman$StateSwitch.processEvent(Foreman.java:1027)
[drill-java-exec-1.10.0.jar:1.10.0]
at 
org.apache.drill.exec.work.foreman.Foreman$StateSwitch.processEvent(Foreman.java:1020)
[drill-java-exec-1.10.0.jar:1.10.0]
at 
org.apache.drill.common.EventProcessor.processEvents(EventProcessor.java:107)
[drill-common-1.10.0.jar:1.10.0]
at 
org.apache.drill.common.EventProcessor.sendEvent(EventProcessor.java:65)
[drill-common-1.10.0.jar:1.10.0]
at 
org.apache.drill.exec.work.foreman.Foreman$StateSwitch.addEvent(Foreman.java:1022)
[drill-java-exec-1.10.0.jar:1.10.0]
at 
org.apache.drill.exec.work.foreman.Foreman.addToEventQueue(Foreman.java:1040)
[drill-java-exec-1.10.0.jar:1.10.0]
at 
org.apache.drill.exec.work.foreman.QueryManager.nodeComplete(QueryManager.java:506)
[drill-java-exec-1.10.0.jar:1.10.0]


Thanks,
Divya

On 1 November 2017 at 01:08, Timothy Farkas  wrote:

> Can you share the logs from your Drill bit? Without the logs I'm taking a
> shot in the dark but
>
> you can try increasing the timeout between the DrillClient and foreman by
> adding this configuration
>
> to your drill-override.conf
>
>
>
> drill.exec.rpc.user.timeout: 30
>
> Thanks,
> Tim
>
>
> 
> From: Divya Gehlot 
> Sent: Tuesday, October 31, 2017 12:26:55 AM
> To: user@drill.apache.org
> Subject: Apache Drill connection issue in tableau
>
> Hi ,
> I am facing  a strange Drill connection issue.
> I am connecting to Drill through Tableau.
> I have published coupled of reports to tableau server.
> When I connect to Drill thorugh tableau desktop and tableau server at same
> time
> it becomes unresponding.I keeping getting "connection time out error" or in
> tableau "connection to port is lost" .
> But when I close the tableau desktop and just connected to published
> reports to tableau server everything works fine and no time out error.
>
> Not sure if I need to update any drill configurations ?
>
> Appreciate the help !
>
> Thanks,
> Divya
>

Re: Apache drill : Error while creating storage plugin for Oracle DB

[Arjun kr]

Noticed that you are specifying property 
'drill.exec.sys.store.provider.local.path' inside 'drill.exec' section. Can you 
try correcting it ? if it does not work , Please create JIRA with details as 
Kunal suggested.

Shared entry :

3. drill-override.conf file
drill.exec: {
  cluster-id: "drillbits1",
  drill.exec.sys.store.provider.local.path="/u01/bdcsce/drill/
apache-drill-1.11.0",
  zk.connect: "<>:<"
}

Correct it as given below.

drill.exec: {
  cluster-id: "drillbits1",
  sys.store.provider.local.path="/u01/bdcsce/drill/
apache-drill-1.11.0",
  zk.connect: "<>:<"
}

Thanks,

Arjun


From: Arjun kr 
Sent: Wednesday, November 1, 2017 11:20 AM
To: user@drill.apache.org
Subject: Re: Apache drill : Error while creating storage plugin for Oracle DB

Hi Akshay,


Just wondering if the issue is only with Oracle storage plugin. Are you able to 
create storage plugin other than oracle , say dfs or cp ?


Thanks,


Arjun


From: Kunal Khatua 
Sent: Wednesday, November 1, 2017 11:12 AM
To: user@drill.apache.org
Subject: RE: Apache drill : Error while creating storage plugin for Oracle DB

Could you file a JIRA for this? I see the potential sources where the bug 
originated and there doesn’t seem to be sufficient logging, so it gets hard to 
narrow it down.

-Original Message-
From: Akshay Joshi [mailto:joshiakshay0...@gmail.com]
Sent: Tuesday, October 31, 2017 10:04 PM
To: user@drill.apache.org
Subject: Re: Apache drill : Error while creating storage plugin for Oracle DB

Hi Arjun/Kunal,

Just to add to my previous mail I am using *Apache Drill version 1.11.0.*

Thanks,
Akshay

On Wed, Nov 1, 2017 at 10:29 AM, Akshay Joshi 
wrote:

> Hi Arjun/Kunal,
>
> Error message is not getting logged in drillbit.out  as well once "create"
> button is clicked. None of the log files is getting updated in drill UI.
>
> Tried with curl command and getting the same error *(unable to create/
> update storage) *without much stacktrace about error. PFB more details
> about curl command and o/p. HTTPS is not enabled.
> Just to add I am able to connect my oracle DB using sqldeveloper client.
>
> *COMMAND*
> curl -v -X POST -H "Content-Type: application/json" -d
> '{"name":"oracle1",
> "config": {"type": "jdbc", "enabled": true,"driver": "oracle.dbc.driver.
> OracleDriver",
> "url":"jdbc:oracle:thin:/@ server>:/" }}' http://<>:
> 8047/storage/query.json
>
> *CURL O/P*
> * About to connect() to <> port 8047 (#0)
> *   Trying <>... connected
> * Connected to <> (<>) port 8047
> (#0)
> > POST /storage/query.json HTTP/1.1
> > User-Agent: curl/7.19.7 <>
> > Host: <>:8047
> > Accept: */*
> > Content-Type: application/json
> > Content-Length: 179
> >
> < HTTP/1.1 200 OK
> < Content-Type: application/json
> < Content-Length: 59
> < Server: Jetty(9.1.5.v20140505)
> <
> {
>   "result" : "error (unable to create/ update storage)"
> * Connection #0 to host <> left intact
> * Closing connection #0
> }
>
> Kindly let me know if any other thoughts.
>
> Thanks,
> Akshay
>
>
> On Wed, Nov 1, 2017 at 12:20 AM, Arjun kr  wrote:
>
>>
>> Can you try with CURL as well ?
>>
>> curl -v -X POST -H "Content-Type: application/json" -d
>> '{"name":"oracle1", "config": {"type": "jdbc", "enabled": true,"driver":
>> "oracle.jdbc.driver.OracleDriver",
>> "url":"jdbc:oracle:thin:/@> server>:/" }}' http://<
>> drill_server>:8047/storage/oracle1.json
>>
>> Please refer to this blog for curl command if you have HTTPS enabled.
>>
>> http://www.openkb.info/2016/02/how-to-use-rest-api-to-submit-queries.
>> html
>>
>> Also assume that you are able to connect with this connection URL
>> using any other jdbc application.
>>
>> Thanks,
>>
>> Arjun
>> 
>> From: Kunal Khatua 
>> Sent: Tuesday, October 31, 2017 11:57 PM
>> To: user@drill.apache.org
>> Subject: RE: Apache drill : Error while creating storage plugin for
>> Oracle DB
>>
>> There are other logs that might be reporting the error.
>>
>> Look at the other logs in the Drill UI ... one of them that can carry
>> more information would be drillbit.out
>>
>> -Original Message-
>> From: Akshay Joshi [mailto

Re: Apache drill : Error while creating storage plugin for Oracle DB

[Arjun kr]

Hi Akshay,


Just wondering if the issue is only with Oracle storage plugin. Are you able to 
create storage plugin other than oracle , say dfs or cp ?


Thanks,


Arjun


From: Kunal Khatua 
Sent: Wednesday, November 1, 2017 11:12 AM
To: user@drill.apache.org
Subject: RE: Apache drill : Error while creating storage plugin for Oracle DB

Could you file a JIRA for this? I see the potential sources where the bug 
originated and there doesn’t seem to be sufficient logging, so it gets hard to 
narrow it down.

-Original Message-
From: Akshay Joshi [mailto:joshiakshay0...@gmail.com]
Sent: Tuesday, October 31, 2017 10:04 PM
To: user@drill.apache.org
Subject: Re: Apache drill : Error while creating storage plugin for Oracle DB

Hi Arjun/Kunal,

Just to add to my previous mail I am using *Apache Drill version 1.11.0.*

Thanks,
Akshay

On Wed, Nov 1, 2017 at 10:29 AM, Akshay Joshi 
wrote:

> Hi Arjun/Kunal,
>
> Error message is not getting logged in drillbit.out  as well once "create"
> button is clicked. None of the log files is getting updated in drill UI.
>
> Tried with curl command and getting the same error *(unable to create/
> update storage) *without much stacktrace about error. PFB more details
> about curl command and o/p. HTTPS is not enabled.
> Just to add I am able to connect my oracle DB using sqldeveloper client.
>
> *COMMAND*
> curl -v -X POST -H "Content-Type: application/json" -d
> '{"name":"oracle1",
> "config": {"type": "jdbc", "enabled": true,"driver": "oracle.dbc.driver.
> OracleDriver",
> "url":"jdbc:oracle:thin:/@ server>:/" }}' http://<>:
> 8047/storage/query.json
>
> *CURL O/P*
> * About to connect() to <> port 8047 (#0)
> *   Trying <>... connected
> * Connected to <> (<>) port 8047
> (#0)
> > POST /storage/query.json HTTP/1.1
> > User-Agent: curl/7.19.7 <>
> > Host: <>:8047
> > Accept: */*
> > Content-Type: application/json
> > Content-Length: 179
> >
> < HTTP/1.1 200 OK
> < Content-Type: application/json
> < Content-Length: 59
> < Server: Jetty(9.1.5.v20140505)
> <
> {
>   "result" : "error (unable to create/ update storage)"
> * Connection #0 to host <> left intact
> * Closing connection #0
> }
>
> Kindly let me know if any other thoughts.
>
> Thanks,
> Akshay
>
>
> On Wed, Nov 1, 2017 at 12:20 AM, Arjun kr  wrote:
>
>>
>> Can you try with CURL as well ?
>>
>> curl -v -X POST -H "Content-Type: application/json" -d
>> '{"name":"oracle1", "config": {"type": "jdbc", "enabled": true,"driver":
>> "oracle.jdbc.driver.OracleDriver",
>> "url":"jdbc:oracle:thin:/@> server>:/" }}' http://<
>> drill_server>:8047/storage/oracle1.json
>>
>> Please refer to this blog for curl command if you have HTTPS enabled.
>>
>> http://www.openkb.info/2016/02/how-to-use-rest-api-to-submit-queries.
>> html
>>
>> Also assume that you are able to connect with this connection URL
>> using any other jdbc application.
>>
>> Thanks,
>>
>> Arjun
>> 
>> From: Kunal Khatua 
>> Sent: Tuesday, October 31, 2017 11:57 PM
>> To: user@drill.apache.org
>> Subject: RE: Apache drill : Error while creating storage plugin for
>> Oracle DB
>>
>> There are other logs that might be reporting the error.
>>
>> Look at the other logs in the Drill UI ... one of them that can carry
>> more information would be drillbit.out
>>
>> -Original Message-
>> From: Akshay Joshi [mailto:joshiakshay0...@gmail.com]
>> Sent: Tuesday, October 31, 2017 11:06 AM
>> To: user@drill.apache.org
>> Subject: Re: Apache drill : Error while creating storage plugin for
>> Oracle DB
>>
>> Hi Arjun,
>>
>> No error message getting logged in drillbit.log once I click on "create"
>> button.
>>
>> Thanks,
>> Akshay
>>
>> On Tue, Oct 31, 2017 at 11:19 PM, Arjun kr  wrote:
>>
>> >
>> > Do you see any specific error message in drillbit.log ?
>> >
>> > Thanks,
>> >
>> > Arjun
>> >
>> > 
>> > From: Akshay Joshi 
>> > Sent: Tuesday, October 31, 2017 11:10 PM
>> > To: user@drill.apache.org
>> > Subject: Re: Apache drill : Error while creati

Re: Apache drill : Error while creating storage plugin for Oracle DB

[Arjun kr]

Can you try with CURL as well ?

curl -v -X POST -H "Content-Type: application/json" -d '{"name":"oracle1", 
"config": {"type": "jdbc", "enabled": true,"driver": 
"oracle.jdbc.driver.OracleDriver", 
"url":"jdbc:oracle:thin:/@:/" }}' 
http://< drill_server>:8047/storage/oracle1.json

Please refer to this blog for curl command if you have HTTPS enabled.

http://www.openkb.info/2016/02/how-to-use-rest-api-to-submit-queries.html

Also assume that you are able to connect with this connection URL using any 
other jdbc application.

Thanks,

Arjun

From: Kunal Khatua 
Sent: Tuesday, October 31, 2017 11:57 PM
To: user@drill.apache.org
Subject: RE: Apache drill : Error while creating storage plugin for Oracle DB

There are other logs that might be reporting the error.

Look at the other logs in the Drill UI ... one of them that can carry more 
information would be drillbit.out

-Original Message-
From: Akshay Joshi [mailto:joshiakshay0...@gmail.com]
Sent: Tuesday, October 31, 2017 11:06 AM
To: user@drill.apache.org
Subject: Re: Apache drill : Error while creating storage plugin for Oracle DB

Hi Arjun,

No error message getting logged in drillbit.log once I click on "create"
button.

Thanks,
Akshay

On Tue, Oct 31, 2017 at 11:19 PM, Arjun kr  wrote:

>
> Do you see any specific error message in drillbit.log ?
>
> Thanks,
>
> Arjun
>
> 
> From: Akshay Joshi 
> Sent: Tuesday, October 31, 2017 11:10 PM
> To: user@drill.apache.org
> Subject: Re: Apache drill : Error while creating storage plugin for
> Oracle DB
>
> Hi Arjun,
>
> Yes. I copied jdbc driver (ojdbc7.jar) to all the drill nodes and
> restarted the drillbits on all nodes.
>
> Thanks and Regards,
> Akshay
> On Tue, 31 Oct 2017 at 10:30 PM, Arjun kr  wrote:
>
> > Hi Akshay,
> >
> >
> > Did you copy jdbc driver to all the drill nodes and restarted drillbits ?
> >
> >
> > Thanks,
> >
> >
> > Arjun
> >
> >
> > 
> > From: Akshay Joshi 
> > Sent: Tuesday, October 31, 2017 9:46 PM
> > To: user@drill.apache.org
> > Subject: Apache drill : Error while creating storage plugin for
> > Oracle DB
> >
> > Hi Team,
> >
> > I have configured drill on 4 node cluster and getting error while
> creating
> > storage plugin for Oracle DB.
> >
> > Error : Please retry: error (unable to create/ update storage)
> >
> > Oracle DB Version : *12.1.0.2.0*
> >
> > Following steps followed to create storage plugin:
> > 1. copied ojdbc7.jar to jars/3rdparty folder 2. Storage plugin
> > configuration {
> >   "type": "jdbc",
> >   "driver": "oracle.jdbc.driver.OracleDriver",
> >   "url":
> > "jdbc:oracle:thin:<>/<>@<>:<>/
> ORCL",
> >   "enabled": true
> > }
> > 3. drill-override.conf file
> > drill.exec: {
> >   cluster-id: "drillbits1",
> >   drill.exec.sys.store.provider.local.path="/u01/bdcsce/drill/
> > apache-drill-1.11.0",
> >   zk.connect: "<>:<"
> > }
> >
> > Thanks and Regards,
> > Akshay
> >
>

Re: Apache drill : Error while creating storage plugin for Oracle DB

[Arjun kr]

Do you see any specific error message in drillbit.log ?

Thanks,

Arjun


From: Akshay Joshi 
Sent: Tuesday, October 31, 2017 11:10 PM
To: user@drill.apache.org
Subject: Re: Apache drill : Error while creating storage plugin for Oracle DB

Hi Arjun,

Yes. I copied jdbc driver (ojdbc7.jar) to all the drill nodes and restarted
the drillbits on all nodes.

Thanks and Regards,
Akshay
On Tue, 31 Oct 2017 at 10:30 PM, Arjun kr  wrote:

> Hi Akshay,
>
>
> Did you copy jdbc driver to all the drill nodes and restarted drillbits ?
>
>
> Thanks,
>
>
> Arjun
>
>
> 
> From: Akshay Joshi 
> Sent: Tuesday, October 31, 2017 9:46 PM
> To: user@drill.apache.org
> Subject: Apache drill : Error while creating storage plugin for Oracle DB
>
> Hi Team,
>
> I have configured drill on 4 node cluster and getting error while creating
> storage plugin for Oracle DB.
>
> Error : Please retry: error (unable to create/ update storage)
>
> Oracle DB Version : *12.1.0.2.0*
>
> Following steps followed to create storage plugin:
> 1. copied ojdbc7.jar to jars/3rdparty folder
> 2. Storage plugin configuration
> {
>   "type": "jdbc",
>   "driver": "oracle.jdbc.driver.OracleDriver",
>   "url": "jdbc:oracle:thin:<>/<>@<>:<>/ORCL",
>   "enabled": true
> }
> 3. drill-override.conf file
> drill.exec: {
>   cluster-id: "drillbits1",
>   drill.exec.sys.store.provider.local.path="/u01/bdcsce/drill/
> apache-drill-1.11.0",
>   zk.connect: "<>:<"
> }
>
> Thanks and Regards,
> Akshay
>

Re: Apache drill : Error while creating storage plugin for Oracle DB

[Arjun kr]

Hi Akshay,


Did you copy jdbc driver to all the drill nodes and restarted drillbits ?


Thanks,


Arjun



From: Akshay Joshi 
Sent: Tuesday, October 31, 2017 9:46 PM
To: user@drill.apache.org
Subject: Apache drill : Error while creating storage plugin for Oracle DB

Hi Team,

I have configured drill on 4 node cluster and getting error while creating
storage plugin for Oracle DB.

Error : Please retry: error (unable to create/ update storage)

Oracle DB Version : *12.1.0.2.0*

Following steps followed to create storage plugin:
1. copied ojdbc7.jar to jars/3rdparty folder
2. Storage plugin configuration
{
  "type": "jdbc",
  "driver": "oracle.jdbc.driver.OracleDriver",
  "url": "jdbc:oracle:thin:<>/<>@<>:<>/ORCL",
  "enabled": true
}
3. drill-override.conf file
drill.exec: {
  cluster-id: "drillbits1",
  drill.exec.sys.store.provider.local.path="/u01/bdcsce/drill/
apache-drill-1.11.0",
  zk.connect: "<>:<"
}

Thanks and Regards,
Akshay

Re: S3 Connection Issues

[Arjun kr]

Hi Charles,


I'm not aware of any such settings. As Padma mentioned in previous mail, It 
works fine for me by following instructions in 
https://drill.apache.org/docs/s3-storage-plugin/ .


Thanks,


Arjun



From: Charles Givre 
Sent: Friday, October 20, 2017 11:48 PM
To: user@drill.apache.org
Subject: Re: S3 Connection Issues

Hi Arjun,
Thanks for your help.  Are there settings in S3 that would prevent Drill from 
connecting?  I’ll try hdfs shell, but I am able to connect with the CLI tool.   
My hunch is that there is a permission not set correctly on S3 or I’m missing 
some config variable in Drill.
— C


> On Oct 20, 2017, at 14:12, Arjun kr  wrote:
>
> Hi  Charles,
>
>
> Any chance you can test s3 connectivity with other tools like hdfs shell or 
> hive in case you haven't tried already (and these tools available)? This may 
> help to identify if it is Drill specific issue.
>
>
> For connecting via hdfs , you may try below command.
>
>
> hadoop fs -Dfs.s3a.access.key="" -Dfs.s3a.secret.key="Y" -ls 
> s3a:///
>
>
> Enable DEBUG logging if needed.
>
>
> export HADOOP_ROOT_LOGGER=hadoop.root.logger=DEBUG,console
>
>
> Thanks,
>
>
> Arjun
>
>
> 
> From: Padma Penumarthy 
> Sent: Friday, October 20, 2017 3:00 AM
> To: user@drill.apache.org
> Subject: Re: S3 Connection Issues
>
> Hi Charles,
>
> I tried us-west-2 and it worked fine for me with drill built from latest 
> source.
> I did not do anything special.
> Just enabled the S3 plugin and updated the plugin configuration like this.
>
> {
>  "type": "file",
>  "enabled": true,
>  "connection": "s3a://",
>  "config": {
>"fs.s3a.access.key": “",
>"fs.s3a.secret.key": “"
>  },
>
> I am able to do show databases and also can query the parquet files I 
> uploaded to the bucket.
>
> 0: jdbc:drill:zk=local> show databases;
> +-+
> | SCHEMA_NAME |
> +-+
> | INFORMATION_SCHEMA  |
> | cp.default  |
> | dfs.default |
> | dfs.root|
> | dfs.tmp |
> | s3.default  |
> | s3.root |
> | sys |
> +-+
> 8 rows selected (2.892 seconds)
>
>
> Thanks
> Padma
>
> On Oct 18, 2017, at 9:18 PM, Charles Givre 
> mailto:cgi...@gmail.com>> wrote:
>
> Hi Padma,
> The bucket is is us-west-2.  I also discovered that some of the variable 
> names in the documentation on the main Drill site are incorrect.  Do I need 
> to specify the region in the configuration somewhere?
>
> As an update, after discovering that the variable names are incorrect and 
> that I didn’t have Jets3t installed properly, I’m now getting the following 
> error:
>
> jdbc:drill:zk=local> show databases;
> Error: RESOURCE ERROR: Failed to create schema tree.
>
>
> [Error Id: e6012aa2-c775-46b9-b3ee-0af7d0b0871d on 
> charless-mbp-2.fios-router.home:31010]
>
> (org.apache.hadoop.fs.s3.S3Exception) org.jets3t.service.S3ServiceException: 
> Service Error Message. -- ResponseCode: 403, ResponseStatus: Forbidden, XML 
> Error Message:  encoding="UTF-8"?>SignatureDoesNotMatchThe 
> request signature we calculated does not match the signature you provided. 
> Check your key and signing method.
>   org.apache.hadoop.fs.s3.Jets3tFileSystemStore.get():175
>   org.apache.hadoop.fs.s3.Jets3tFileSystemStore.retrieveINode():221
>
> Thanks,
> — C
>
>
> On Oct 19, 2017, at 00:14, Padma Penumarthy 
> mailto:ppenumar...@mapr.com>> wrote:
>
> Which AWS region are you trying to connect to ?
> We have a  problem connecting to regions which support only v4 signature
> since the version of hadoop we include in Drill is old.
> Last time I tried, using Hadoop 2.8.1 worked for me.
>
> Thanks
> Padma
>
>
> On Oct 18, 2017, at 8:14 PM, Charles Givre 
> mailto:cgi...@gmail.com>> wrote:
>
> Hello all,
> I’m trying to use Drill to query data in an S3 bucket and running into some 
> issues which I can’t seem to fix.  I followed the various instructions online 
> to set up Drill with S3, and put my keys in both the conf-site.xml and in the 
> plugin config, but every time I attempt to do anything I get the following 
> errors:
>
>
> jdbc:drill:zk=local> show databases;
> Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon 
> S3, AWS Request ID: 56D1999BD1E62DEB, AWS Error Code: null, AWS Error 
> Message: Forbidden
>
>
> [Error Id: 65d0bb5

Re: S3 Connection Issues

[Arjun kr]

Hi  Charles,


Any chance you can test s3 connectivity with other tools like hdfs shell or 
hive in case you haven't tried already (and these tools available)? This may 
help to identify if it is Drill specific issue.


For connecting via hdfs , you may try below command.


hadoop fs -Dfs.s3a.access.key="" -Dfs.s3a.secret.key="Y" -ls 
s3a:///


Enable DEBUG logging if needed.


export HADOOP_ROOT_LOGGER=hadoop.root.logger=DEBUG,console


Thanks,


Arjun



From: Padma Penumarthy 
Sent: Friday, October 20, 2017 3:00 AM
To: user@drill.apache.org
Subject: Re: S3 Connection Issues

Hi Charles,

I tried us-west-2 and it worked fine for me with drill built from latest source.
I did not do anything special.
Just enabled the S3 plugin and updated the plugin configuration like this.

{
  "type": "file",
  "enabled": true,
  "connection": "s3a://",
  "config": {
"fs.s3a.access.key": “",
"fs.s3a.secret.key": “"
  },

I am able to do show databases and also can query the parquet files I uploaded 
to the bucket.

0: jdbc:drill:zk=local> show databases;
+-+
| SCHEMA_NAME |
+-+
| INFORMATION_SCHEMA  |
| cp.default  |
| dfs.default |
| dfs.root|
| dfs.tmp |
| s3.default  |
| s3.root |
| sys |
+-+
8 rows selected (2.892 seconds)


Thanks
Padma

On Oct 18, 2017, at 9:18 PM, Charles Givre 
mailto:cgi...@gmail.com>> wrote:

Hi Padma,
The bucket is is us-west-2.  I also discovered that some of the variable names 
in the documentation on the main Drill site are incorrect.  Do I need to 
specify the region in the configuration somewhere?

As an update, after discovering that the variable names are incorrect and that 
I didn’t have Jets3t installed properly, I’m now getting the following error:

jdbc:drill:zk=local> show databases;
Error: RESOURCE ERROR: Failed to create schema tree.


[Error Id: e6012aa2-c775-46b9-b3ee-0af7d0b0871d on 
charless-mbp-2.fios-router.home:31010]

 (org.apache.hadoop.fs.s3.S3Exception) org.jets3t.service.S3ServiceException: 
Service Error Message. -- ResponseCode: 403, ResponseStatus: Forbidden, XML 
Error Message: SignatureDoesNotMatchThe request 
signature we calculated does not match the signature you provided. Check your 
key and signing method.
   org.apache.hadoop.fs.s3.Jets3tFileSystemStore.get():175
   org.apache.hadoop.fs.s3.Jets3tFileSystemStore.retrieveINode():221

Thanks,
— C


On Oct 19, 2017, at 00:14, Padma Penumarthy 
mailto:ppenumar...@mapr.com>> wrote:

Which AWS region are you trying to connect to ?
We have a  problem connecting to regions which support only v4 signature
since the version of hadoop we include in Drill is old.
Last time I tried, using Hadoop 2.8.1 worked for me.

Thanks
Padma


On Oct 18, 2017, at 8:14 PM, Charles Givre 
mailto:cgi...@gmail.com>> wrote:

Hello all,
I’m trying to use Drill to query data in an S3 bucket and running into some 
issues which I can’t seem to fix.  I followed the various instructions online 
to set up Drill with S3, and put my keys in both the conf-site.xml and in the 
plugin config, but every time I attempt to do anything I get the following 
errors:


jdbc:drill:zk=local> show databases;
Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon 
S3, AWS Request ID: 56D1999BD1E62DEB, AWS Error Code: null, AWS Error Message: 
Forbidden


[Error Id: 65d0bb52-a923-4e98-8ab1-65678169140e on 
charless-mbp-2.fios-router.home:31010] (state=,code=0)
0: jdbc:drill:zk=local> show databases;
Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon 
S3, AWS Request ID: 4D2CBA8D42A9ECA0, AWS Error Code: null, AWS Error Message: 
Forbidden


[Error Id: 25a2d008-2f4d-4433-a809-b91ae063e61a on 
charless-mbp-2.fios-router.home:31010] (state=,code=0)
0: jdbc:drill:zk=local> show files in s3.root;
Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon 
S3, AWS Request ID: 2C635944EDE591F0, AWS Error Code: null, AWS Error Message: 
Forbidden


[Error Id: 02e136f5-68c0-4b47-9175-a9935bda5e1c on 
charless-mbp-2.fios-router.home:31010] (state=,code=0)
0: jdbc:drill:zk=local> show schemas;
Error: SYSTEM ERROR: AmazonS3Exception: Status Code: 403, AWS Service: Amazon 
S3, AWS Request ID: 646EB5B2EBCF7CD2, AWS Error Code: null, AWS Error Message: 
Forbidden


[Error Id: 954aaffe-616a-4f40-9ba5-d4b7c04fe238 on 
charless-mbp-2.fios-router.home:31010] (state=,code=0)

I have verified that the keys are correct but using the AWS CLI and downloaded 
some of the files, but I’m kind of at a loss as to how to debug.  Any 
suggestions?
Thanks in advance,
— C

Re: S3 with mixed files

[Arjun kr]

Hi Daniel,

This error may occur if you don't have format defined in S3 storage plugin that 
handles ".log" extension.

For eg:

-- I have file input.csv and have csv format defined in s3 storage plugin.

2 rows selected (1.233 seconds)
0: jdbc:drill:schema=dfs> select * from s3.root.`test-dir/input.csv`;
+--+
| columns  |
+--+
| ["\"Pespsi,Pepsi\",\"Pespsi,Pepsi [100.00]",""]  |
| ["Pespsi,Pepsi\",\"Pespsi,Pepsi [100.00]",""]|
| ["Pespsi,Pepsi","Pespsi,Pepsi [100.00]"] |
+--+
3 rows selected (3.418 seconds)

-- Renamed S3 file input.csv to input.log

0: jdbc:drill:schema=dfs> select * from s3.root.`test-dir/input.log`;
Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 16: Table 
's3.root.test-dir/input.log' not found

SQL Query null

[Error Id: 5996db7d-c886-45a8-bddf-99f11159db66 on arjun-lab-73:31010] 
(state=,code=0)
0: jdbc:drill:schema=dfs>

Thanks,

Arjun



From: Divya Gehlot 
Sent: Friday, October 20, 2017 12:50 PM
To: user@drill.apache.org
Subject: Re: S3 with mixed files

Hi Daniel,
Can you try select * from ibios3.root.`./tracking/tracking.log`;
instead of
select * from ibios3.root.`tracking/tracking.log`;

Thanks,
Divya


On 20 October 2017 at 13:13, Daniel McQuillen 
wrote:

> Thanks for your help, Padma!
>
> Just tried the following, per your suggestion:
>
> select * from ibios3.root.`tracking/tracking.log`;
>
> Still getting an error (although as I mentioned before I can do a 'show
> files;' ok so the credentials must be working):
>
>  "org.apache.drill.common.exceptions.UserRemoteException: VALIDATION
> ERROR:
> From line 1, column 15 to line 1, column 20: Table
> 'ibios3.root.tracking/tracking.log' not found SQL Query null [Error Id:
> fbd59cf8-d6ec-4022-b682-9b51d33f8302 on
> ip-10-0-0-24.us-west-1.compute.internal:31010]
>
>
> I tried from both the embedded command line and the web interface. Do you
> have any other suggestions? Thanks in advance.
>
> Best Regards,
>
> Daniel
>
>
>
> On Fri, Oct 20, 2017 at 12:25 PM, Padma Penumarthy 
> wrote:
>
> > From your error log, it seems like you may be specifying the table
> > incorrectly.
> > Instead of 'ibios3.root.tracking/tracking.log’, can you try
> > ibios3.root.`tracking/tracking.log`
> >
> > i.e. for example, select * from ibios3.root.`tracking/tracking.log`
> >
> > Thanks
> > Padma
> >
> >
> > > On Oct 18, 2017, at 7:15 PM, Daniel McQuillen <
> > daniel.mcquil...@gmail.com> wrote:
> > >
> > > Hi,
> > >
> > > Attempting to use Apache Drill to parse Open edX tracking log files I
> > have
> > > stored on S3.
> > >
> > > I've successfully set up an S3 connection and I can see my different
> > > directories in the target S3 bucket when I type `show files;` in
> embedded
> > > drill. Hooray!
> > >
> > > However, I can't seem to do a query. I keep getting a "not found" error
> > >
> > > SEVERE: org.apache.calcite.runtime.CalciteContextException: From line
> 1,
> > > column 15 to line 1, column 20: Table 'ibios3.root.tracking/
> > tracking.log'
> > > not found
> > >
> > > The "tracking" subdirectory has a most recent `tracking.log` file as
> well
> > > as a bunch of  gzipped older files, e.g. `tracking-log-20170518-1234.
> gz`
> > > ... could this be confusing Drill? I've tried querying an individual
> file
> > > (tracking.log) as well as the directory itself, but not luck.
> > >
> > > Thanks for any thoughts!
> > >
> > >
> > > - Daniel
> >
> >
>

Re: Exception while reading parquet data

[Arjun kr]

Can you try disabling async parquet reader to see if problem gets resolved.


alter session set `store.parquet.reader.pagereader.async`=false;

Thanks,

Arjun



From: PROJJWAL SAHA 
Sent: Wednesday, October 11, 2017 2:20 PM
To: user@drill.apache.org
Subject: Exception while reading parquet data

I get below exception when querying parquet data on Oracle Storage Cloud
service.
Any pointers on what does this point to ?

Regards,
Projjwal


ERROR o.a.d.e.u.f.BufferedDirectBufInputStream - Error reading from stream
part-6-25a9ae4b-fd9e-4770-b17e-9a29b270a4c2.parquet. Error was : null
2017-10-09 09:42:18,516 [scan-2] INFO  o.a.d.e.s.p.c.AsyncPageReader - User
Error Occurred: Exception occurred while reading from disk.
(java.lang.IndexOutOfBoundsException)
org.apache.drill.common.exceptions.UserException: DATA_READ ERROR:
Exception occurred while reading from disk.

File:
/data25GB/storereturns/part-6-25a9ae4b-fd9e-4770-b17e-9a29b270a4c2.parquet
Column:  sr_return_time_sk
Row Group Start:  479751

[Error Id: 10680bb8-d1d6-43a1-b5e0-ef15bd8a9406 ]
at
org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:550)
~[drill-common-1.11.0.jar:1.11.0]
at
org.apache.drill.exec.store.parquet.columnreaders.AsyncPageReader.handleAndThrowException(AsyncPageReader.java:185)
[drill-java-exec-1.11.0.jar:1.11.0]
at
org.apache.drill.exec.store.parquet.columnreaders.AsyncPageReader.access$700(AsyncPageReader.java:82)
[drill-java-exec-1.11.0.jar:1.11.0]
at
org.apache.drill.exec.store.parquet.columnreaders.AsyncPageReader$AsyncPageReaderTask.call(AsyncPageReader.java:461)
[drill-java-exec-1.11.0.jar:1.11.0]
at
org.apache.drill.exec.store.parquet.columnreaders.AsyncPageReader$AsyncPageReaderTask.call(AsyncPageReader.java:381)
[drill-java-exec-1.11.0.jar:1.11.0]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_121]
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
[na:1.8.0_121]
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
[na:1.8.0_121]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_121]
Caused by: java.io.IOException: java.lang.IndexOutOfBoundsException
at
org.apache.drill.exec.util.filereader.BufferedDirectBufInputStream.getNextBlock(BufferedDirectBufInputStream.java:185)
~[drill-java-exec-1.11.0.jar:1.11.0]
at
org.apache.drill.exec.util.filereader.BufferedDirectBufInputStream.readInternal(BufferedDirectBufInputStream.java:212)
~[drill-java-exec-1.11.0.jar:1.11.0]
at
org.apache.drill.exec.util.filereader.BufferedDirectBufInputStream.read(BufferedDirectBufInputStream.java:277)
~[drill-java-exec-1.11.0.jar:1.11.0]
at
org.apache.drill.exec.util.filereader.DirectBufInputStream.getNext(DirectBufInputStream.java:111)
~[drill-java-exec-1.11.0.jar:1.11.0]
at
org.apache.drill.exec.store.parquet.columnreaders.AsyncPageReader$AsyncPageReaderTask.call(AsyncPageReader.java:421)
[drill-java-exec-1.11.0.jar:1.11.0]
... 5 common frames omitted
Caused by: java.lang.IndexOutOfBoundsException: null
at java.nio.Buffer.checkBounds(Buffer.java:567) ~[na:1.8.0_121]
at java.nio.ByteBuffer.put(ByteBuffer.java:827) ~[na:1.8.0_121]
at java.nio.DirectByteBuffer.put(DirectByteBuffer.java:379) ~[na:1.8.0_121]
at
org.apache.parquet.hadoop.util.CompatibilityUtil.getBuf(CompatibilityUtil.java:110)
~[parquet-hadoop-1.8.1-drill-r0.jar:1.8.1-drill-r0]
at
org.apache.drill.exec.util.filereader.BufferedDirectBufInputStream.getNextBlock(BufferedDirectBufInputStream.java:182)
~[drill-java-exec-1.11.0.jar:1.11.0]
... 9 common frames omitted
2017-10-09 09:42:20,533 [26248359-2fc8-d177-c3a6-507f6857e0ea:frag:2:3]
INFO  o.a.d.e.w.fragment.FragmentExecutor -
26248359-2fc8-d177-c3a6-507f6857e0ea:2:3: State change requested
AWAITING_ALLOCATION --> RUNNING
2017-10-09 09:42:20,533 [26248359-2fc8-d177-c3a6-507f6857e0ea:frag:2:3]
INFO  o.a.d.e.w.f.FragmentStatusReporter -
26248359-2fc8-d177-c3a6-507f6857e0ea:2:3: State to report: RUNNING
2017-10-09 09:42:20,534 [26248359-2fc8-d177-c3a6-507f6857e0ea:frag:2:3]
INFO  o.a.d.e.w.fragment.FragmentExecutor -
26248359-2fc8-d177-c3a6-507f6857e0ea:2:3: State change requested RUNNING
--> CANCELLATION_REQUESTED
2017-10-09 09:42:20,534 [26248359-2fc8-d177-c3a6-507f6857e0ea:frag:2:3]
INFO  o.a.d.e.w.f.FragmentStatusReporter -
26248359-2fc8-d177-c3a6-507f6857e0ea:2:3: State to report:
CANCELLATION_REQUESTED

Re: Access to Drill 1.9.0

[Arjun kr]

Drill ships with jar 'log4j-over-slf4j.jar'. Even Drillbit fails to start with 
same stacktrace when slf4j-log4j12.jar is added to Drill classpath. You may 
need to look into how log4j-over-slf4j.jar is getting added to your classpath.


As per SLF4J documentation , both these jars cannot be present simultaneously.


https://www.slf4j.org/legacy.html#log4jRecursion

The presence of slf4j-log4j12.jar, that is the log4j binding for SLF4J, will 
force all SLF4J calls to be delegated to log4j. The presence of 
log4j-over-slf4j.jar will in turn delegate all log4j API calls to their SLF4J 
equivalents. If both are present simultaneously, slf4j calls will be delegated 
to log4j, and log4j calls redirected to SLF4j, resulting in an endless 
loop.


https://stackoverflow.com/questions/18320146/java-lang-illegalstateexception-detected-both-log4j-over-slf4j-jar-and-slf4j-lo

Thanks,

Arjun




From: Chetan Kothari 
Sent: Sunday, October 8, 2017 12:14 AM
To: user@drill.apache.org
Subject: RE: Access to Drill 1.9.0

HI Paul

SQL Lines does not work after this warning.
It also gives error -  java.lang.ExceptionInInitializerError after this warning.

Regards
Chetan

-Original Message-
From: Paul Rogers [mailto:prog...@mapr.com]
Sent: Saturday, October 7, 2017 10:42 PM
To: user@drill.apache.org
Subject: Re: Access to Drill 1.9.0

Hi Chetan,The message seems to be saying that there are multiple “Slf4J" 
logging jars found by the “Slf4J” logging mechanism. This may only be a 
warning.Does SqlLine work after you get this warning?Drill provides “dependency 
management” in its build system to avoid such errors. I wonder if someone 
familiar with the Alluxio client might be able to comment on how best to manage 
their dependency…- Paul > On Oct 7, 2017, at 12:00 AM, Chetan Kothari 
 wrote:> > I am getting following error when I am 
trying to integrate Drill with Alluxio.> It was working for me with both Drill 
1.9.0 and Drill 1.11.0 sometime back.> > So thought of cross-checking whether 
issue comes with Drill 1.9.0.> > Any inputs on how to fix following issue?> > > 
Calculating Drill classpath...> SLF4J: Class path contains multiple SLF4J 
bindings.> SLF4J: Found binding in 
[jar:file:/D:/apache-drill-1.11.0/jars/classb/alluxio-core-client-1.4.0-jar-with-dependencies.jar!/org/slf4j/impl/StaticLoggerBinder.class]>
 SLF4J: Found binding in 
[jar:file:/D:/apache-drill-1.11.0/jars/classb/logback-classic-1.0.13.jar!/org/slf4j/impl/StaticLoggerBinder.class]>
 SLF4J: See 
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.slf4j.org_codes.html-23multiple-5Fbindings&d=DwIGaQ&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=XdafK486-x96ZJoTcDF35EpcYc2j9nO0sbpt27_VGCg&m=wCBfMeK4PRbjvU58p7cEOh61BWQ8D9oJ9aQTgjryw58&s=qB2A7uRNGKAcdcPWWTyAcqTAT11t6mCLtxYjJwnahdw&e=
  for an explanation.> SLF4J: Actual binding is of type 
[org.slf4j.impl.Log4jLoggerFactory]> SLF4J: Detected both log4j-over-slf4j.jar 
AND slf4j-log4j12.jar on the class path, preempting StackOverflowError.> SLF4J: 
See also 
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.slf4j.org_codes.html-23log4jDelegationLoop&d=DwIGaQ&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=XdafK486-x96ZJoTcDF35EpcYc2j9nO0sbpt27_VGCg&m=wCBfMeK4PRbjvU58p7cEOh61BWQ8D9oJ9aQTgjryw58&s=CqbFmZ-Kv5MdTCjcAeGGuHTQz7qvBa_LSRnvkyLzKes&e=
  for more details.> java.lang.ExceptionInInitializerError> apache drill 
1.11.0> "start your sql engine"> > -Original Message-> From: Kunal 
Khatua [mailto:kkha...@mapr.com] > Sent: Saturday, October 7, 2017 11:07 AM> 
To: user@drill.apache.org> Subject: RE: Access to Drill 1.9.0> > Just 
curious... any reason why you're looking to try Drill 1.9.0, considering that 
is nearly a year old ?> > > -Original Message-> From: Rob Wu 
[mailto:robw...@gmail.com] > Sent: Friday, October 06, 2017 10:35 PM> To: 
user@drill.apache.org> Subject: Re: Access to Drill 1.9.0> > Hi Chetan,> > You 
can grab those archives here:> 
https://urldefense.proofpoint.com/v2/url?u=http-3A__archive.apache.org_dist_drill_drill-2D1.9.0_&d=DwIFAg&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=XdafK486-x96ZJoTcDF35EpcYc2j9nO0sbpt27_VGCg&m=zUOB0RDFLKJDe0b0M9qP85NUhoxLbCW6QCJBWyJl4L8&s=x7KT9LfHnoi60ZtZnaQFvT9Jv6wdQfsb0CNisqDwswc&e=
  
>
 > Best regards,> > Rob> >> On Oct 6, 2017, at 10:28 PM, Chetan Kothari 
 wrote:>> >> I am not able to download Apache Drill 
1.9.0 from Drill Site.>> 
https://urldefense.proofpoint.com/v2/url?u=https-3A__drill.apache.org_download_&d=DwIFAg&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=XdafK486-x96ZJoTcDF35EpcYc2j9nO0sbpt27_VGCg&m=z

Re: How connect to HIVE with LDAP?

[Arjun kr]

Hi,


Did you try setting it up with other authentication enabled  - say PAM 
authentication. This would help to identify if issue it is related to LDAP 
authentication. In my understanding , Drill does connect to Hive metastore and 
hive server 2 authentication may not be relevant here. BTW , what is your hive 
storage plugin definition ?


Thanks,


Arjun



From: 上官正 
Sent: Wednesday, September 27, 2017 2:01 PM
To: user
Subject: How connect to HIVE with LDAP?

Dear ,

 I tried to configure the drill connect to HIVE with LDAP has been more 
than a month, did not find the relevant blog or video, very desperate, hoping 
to get your help.
HIVE is the use of Cloudera Express 5.7.2, Hive Metastore using MYSQL Based, 
hive.server2.authentication using LDAP, how can I do? Thank you very much.


上官正  数据平台部
宝付网络科技(上海）有限公司
Baofoo Internet Technology (Shanghai) Co., Ltd.
[cid:_Foxmail.1@1d14b03c-36da-801a-600c-714fc3b81786]
TEL:021-6881
FAX:021-68811007
MOB:18816659058
WEB:www.baofoo.com
ADD:上海市浦东新区居里路99号
No.99, Juli Road, Pudong New Area, Shanghai 201203, China

Re: error accessing sqlline in distributed mode

[Arjun kr]

The connection URL should be  jdbc:drill:zk=[:][,[:]

https://drill.apache.org/docs/using-the-jdbc-driver/

Thanks,

Arjun



From: Divya Gehlot 
Sent: Thursday, September 21, 2017 9:18 AM
To: user@drill.apache.org
Subject: error accessing sqlline in distributed mode

Hi,
I have Apache Drill installed and running in  distributed  mode .
I am trying to run queries in sqlline as below  :

> bin/sqlline –u jdbc:drill:zk=,,:5181
> sqlline> SELECT version FROM sys.version;
> No current connection



am I missing anything here ?

Appreciate the help !

Thanks,
Divya

Re: Query Error on PCAP over MapR FS

[Arjun kr]

The setup method for PcapRecordReader uses FileInputStream being passed to 
PackerDecoder. Looks like it supports only local file system. This could be the 
reason why it worked when copied to local FS root directory and using NFS path 
(file:///mapr/cluster3/x.pcap).


Probably Drill experts can confirm on this.



https://github.com/apache/drill/blob/1.11.0/exec/java-exec/src/main/java/org/apache/drill/exec/store/pcap/PcapRecordReader.java


@Override
public void setup(final OperatorContext context, final OutputMutator 
output) throws ExecutionSetupException {
try {

this.output = output;
this.buffer = new byte[10];
this.in = new FileInputStream(inputPath);
this.decoder = new PacketDecoder(in);
this.validBytes = in.read(buffer);
this.projectedCols = getProjectedColsIfItNull();
setColumns(projectedColumns);
} catch (IOException io) {
throw UserException.dataReadError(io)
.addContext("File name:", inputPath)
.build(logger);
}
}

Thanks,

Arjun

From: Ted Dunning 
Sent: Thursday, September 14, 2017 1:10 PM
To: user
Subject: Re: Query Error on PCAP over MapR FS

PCAP shouldn't care at all about the underlying file system.



On Thu, Sep 14, 2017 at 9:38 AM, Takeo Ogawara 
wrote:

> I’m not sure PcapRecordReader supports HDFS/MapR FS.
> Supporting HDFS/MapR FS or not is different by which file format?
> I don’t understand Drill architecture well...
>
> > If you copy the file to local file system root directory , Does query
> work ?
> Yes, query was successful on the local file system.
>
> Thank you.
>
>
> > 2017/09/14 16:24、Arjun kr のメール:
> >
> > The stack trace shared before shows FileInputStream being invoked from
> PcapRecordReader class. Does it work with Hdfs/Mapr FS or  expects only
> local file system ?
> >
> >
> >>>> Caused by: java.io.FileNotFoundException: /x.pcap (No such file or
> directory)
> >>>> at java.io.FileInputStream.open(Native Method) ~[na:1.7.0_141]
> >>>> at java.io.FileInputStream.(FileInputStream.java:146)
> ~[na:1.7.0_141]
> >>>> at java.io.FileInputStream.(FileInputStream.java:101)
> ~[na:1.7.0_141]
> >>>> at 
> >>>> org.apache.drill.exec.store.pcap.PcapRecordReader.setup(PcapRecordReader.java:103)
> [drill-java-exec-1.11.0.jar:1.11.0]
> >
> > If you copy the file to local file system root directory , Does query
> work ?
> >
> > Thanks,
> >
> > Arjun
> >
> > 
> > From: Takeo Ogawara 
> > Sent: Thursday, September 14, 2017 11:50 AM
> > To: user
> > Subject: Re: Query Error on PCAP over MapR FS
> >
> > Yes, that’s right.
> >
> > [drill@node21 ~]$ ps -ef | grep Drillbit
> > drill  955 1  0 Sep13 ?00:02:26 
> > /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java
> -Xms4G -Xmx4G -XX:MaxDirectMemorySize=8G -XX:ReservedCodeCacheSize=1G
> -Ddrill.exec.enable-epoll=false -XX:MaxPermSize=512M
> -XX:+CMSClassUnloadingEnabled -XX:+UseG1GC 
> -Dlog.path=/home/drill/apache-drill-1.11.0/log/drillbit.log
> -Dlog.query.path=/home/drill/apache-drill-1.11.0/log/drillbit_queries.json
> -cp /home/drill/apache-drill-1.11.0/conf:/home/drill/apache-
> drill-1.11.0/jars/*:/home/drill/apache-drill-1.11.0/
> jars/ext/*:/home/drill/apache-drill-1.11.0/jars/3rdparty/*:/
> home/drill/apache-drill-1.11.0/jars/classb/* org.apache.drill.exec.server.
> Drillbit
> > drill23618  4234  0 15:19 pts/400:00:00 grep Drillbit
> >
> > Thank you.
> >
> >> 2017/09/14 15:16、Robert Hou のメール:
> >>
> >> You wrote:
> >>
> >>
> >>  I meant I started Drill from Linux user “drill”.
> >>
> >>
> >> Do you mean that you logged in as user "drill" and started the
> drillbit?  Can you run:
> >>
> >>
> >>  ps -ef | grep Drillbit
> >>
> >>
> >> Thanks.
> >>
> >>
> >> --Robert
> >>
> >>
> >> 
> >> From: Takeo Ogawara 
> >> Sent: Wednesday, September 13, 2017 10:57 PM
> >> To: user@drill.apache.org
> >> Subject: Re: Query Error on PCAP over MapR FS
> >>
> >> I don’t specify the user name in sqlline command.
> >> I meant I started Drill from Linux user “drill”.
> >> [drill@node21 ~]$ ./apache-drill-1.11.0/bin/sqlline -u
> jdbc:drill:zk=node21:5181,node22:5181,node23:5181/drill/cluster3-drillbits
> >> apache drill 1.11.0
> >> "the only truly happy peo

Re: Query Error on PCAP over MapR FS

[Arjun kr]

The stack trace shared before shows FileInputStream being invoked from 
PcapRecordReader class. Does it work with Hdfs/Mapr FS or  expects only local 
file system ?


>>>  Caused by: java.io.FileNotFoundException: /x.pcap (No such file or 
>>> directory)
>>>  at java.io.FileInputStream.open(Native Method) ~[na:1.7.0_141]
>>>  at java.io.FileInputStream.(FileInputStream.java:146) 
>>> ~[na:1.7.0_141]
>>>  at java.io.FileInputStream.(FileInputStream.java:101) 
>>> ~[na:1.7.0_141]
>>>  at 
>>> org.apache.drill.exec.store.pcap.PcapRecordReader.setup(PcapRecordReader.java:103)
>>>  [drill-java-exec-1.11.0.jar:1.11.0]

If you copy the file to local file system root directory , Does query work ?

Thanks,

Arjun


From: Takeo Ogawara 
Sent: Thursday, September 14, 2017 11:50 AM
To: user
Subject: Re: Query Error on PCAP over MapR FS

Yes, that’s right.

[drill@node21 ~]$ ps -ef | grep Drillbit
drill  955 1  0 Sep13 ?00:02:26 
/usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java -Xms4G -Xmx4G 
-XX:MaxDirectMemorySize=8G -XX:ReservedCodeCacheSize=1G 
-Ddrill.exec.enable-epoll=false -XX:MaxPermSize=512M 
-XX:+CMSClassUnloadingEnabled -XX:+UseG1GC 
-Dlog.path=/home/drill/apache-drill-1.11.0/log/drillbit.log 
-Dlog.query.path=/home/drill/apache-drill-1.11.0/log/drillbit_queries.json -cp 
/home/drill/apache-drill-1.11.0/conf:/home/drill/apache-drill-1.11.0/jars/*:/home/drill/apache-drill-1.11.0/jars/ext/*:/home/drill/apache-drill-1.11.0/jars/3rdparty/*:/home/drill/apache-drill-1.11.0/jars/classb/*
 org.apache.drill.exec.server.Drillbit
drill23618  4234  0 15:19 pts/400:00:00 grep Drillbit

Thank you.

> 2017/09/14 15:16、Robert Hou のメール:
>
> You wrote:
>
>
>   I meant I started Drill from Linux user “drill”.
>
>
> Do you mean that you logged in as user "drill" and started the drillbit?  Can 
> you run:
>
>
>   ps -ef | grep Drillbit
>
>
> Thanks.
>
>
> --Robert
>
>
> 
> From: Takeo Ogawara 
> Sent: Wednesday, September 13, 2017 10:57 PM
> To: user@drill.apache.org
> Subject: Re: Query Error on PCAP over MapR FS
>
> I don’t specify the user name in sqlline command.
> I meant I started Drill from Linux user “drill”.
> [drill@node21 ~]$ ./apache-drill-1.11.0/bin/sqlline -u 
> jdbc:drill:zk=node21:5181,node22:5181,node23:5181/drill/cluster3-drillbits
> apache drill 1.11.0
> "the only truly happy people are children, the creative minority and drill 
> users"
> 0: jdbc:drill:zk=node21:5181,node22:5181,node> use dfs;
> +---+--+
> |  ok   | summary  |
> +---+--+
> | true  | Default schema changed to [dfs]  |
> +---+--+
> 1 row selected (0.811 seconds)
> 0: jdbc:drill:zk=node21:5181,node22:5181,node> select * from `x.pcap`;
> Error: DATA_READ ERROR: /x.pcap (No such file or directory)
>
> File name: /x.pcap
> Fragment 0:0
>
> [Error Id: d6c1191a-ff79-4c39-96d3-0ae9e0be3ae9 on node25:31010] 
> (state=,code=0)
> 0: jdbc:drill:zk=node21:5181,node22:5181,node> show files in  `x.pcap`;
> +-+--+-+-+++--++-+
> |  name   | isDirectory  | isFile  | length  | owner  | group  | permissions  
> |   accessTime   |modificationTime |
> +-+--+-+-+++--++-+
> | x.pcap  | false| true| 6083| root   | root   | rw-r--r--
> | 2017-09-13 16:14:52.0  | 2017-09-13 16:14:52.24  |
> +-+--+-+-+++--++-+
> 1 row selected (0.241 seconds)
>
> Drillbits config is as follows.
> drill.exec: {
>  cluster-id: "cluster3-drillbits",
>  zk.connect: "node21:5181,node22:5181,node23:5181”
> }
>
> Storage plugin has config for PCAP.
>"pcap": {
>  "type": "pcap"
>},
>
> Is it better to access via NFS to MapR FS?
> I can access file:///mapr/cluster3/x.pcap in Drill sqlline.
>
> Thank you.
>
>> 2017/09/14 14:27、Robert Hou のメール:
>>
>> You wrote:
>>
>>  IӮm running drill as user ӡdrillӱ.
>>
>>
>> How are you invoking sqllline?  Are you specifying a user "drill"?
>>
>>
>> You should be able to query the file with two steps:
>>
>>
>> 1) use mfs;
>>
>>
>> this invokes the plugin
>>
>>
>> 2) select * from `x.pcap`;
>>
>>
>> Since x.pcap is in the root directory, you don't need to reference mfs again
>>
>>
>>
>> Thanks.
>>
>> --Robert
>>
>> 
>> From: Takeo Ogawara 
>> Sent: Wednesday, September 13, 2017 9:17 PM
>> To: user
>> Subject: Re: Query Error on PCAP over MapR FS
>>
>> I used storage plugin named ”°mfs”± with ”°maprfs:///”°.
>> I modified plugin name from ”°mfs”± to ”°dfs”± and tested a query, but the 
>> result was the same (No such file).

Re: Query Error on PCAP over MapR FS

[Arjun kr]

I have not used pcap storage format before. Doesn't it require specific format 
defined in storage plugin ( as psv format given below)?


"formats": {

"psv": {
  "type": "text",
  "extensions": [
"psv"
  ],
  "delimiter": "|"
},


Thanks,

Arjun



From: Takeo Ogawara 
Sent: Thursday, September 14, 2017 9:47 AM
To: user
Subject: Re: Query Error on PCAP over MapR FS

I used storage plugin named “mfs” with “maprfs:///“.
I modified plugin name from “mfs” to “dfs” and tested a query, but the result 
was the same (No such file).

"Hadoop fs -ls / " can find x.pcap
[drill@node21 log]$ hadoop fs -ls / | grep x.pcap
-rw-r--r--   3 root root 6083 2017-09-13 16:14 /x.pcap

Show files in drill
0: jdbc:drill:drillbit=localhost> show files in dfs.`x.pcap`;
+-+--+-+-+++--++-+
|  name   | isDirectory  | isFile  | length  | owner  | group  | permissions  | 
  accessTime   |modificationTime |
+-+--+-+-+++--++-+
| x.pcap  | false| true| 6083| root   | root   | rw-r--r--| 
2017-09-13 16:14:52.0  | 2017-09-13 16:14:52.24  |
+-+--+-+-+++--++-+
1 row selected (0.328 seconds)

I’m running drill as user “drill”.
Is there something wrong with file permissions?


> For your testing you can just use the default root volume, but with MapR-FS 
> it is a good idea to create volumes for different data/use cases and then 
> mount these volumes on MapR-FS.
> This allows for benefits like topology, quota & security management; also 
> ease of use for enterprise features like mirroring, snapshots, etc in the 
> future to name a few.
> https://maprdocs.mapr.com/home/AdministratorGuide/c_managing_data_with_volumes.html
Managing Data with 
Volumes
maprdocs.mapr.com
MapR provides volumes as a way to organize data and manage cluster performance. 
A volume is a logical unit that allows you to apply policies to a set of files, 
directories, and sub-volumes. A ...




Thank you for the information.
I’ll separate the volume for PCAP from other services.

Thank you.

> 2017/09/13 23:48、Andries Engelbrecht のメ�`ル:
>
> Drill is not seeing the file in the location you pointed it.
>
> What did you name the storage plugin?
> The default is normally dfs for the distributed filesystem.
>
> Also did you place the file in the root directory of the dfs?
> What do you get back if you run Hadoop fs �Cls /
>
> For your testing you can just use the default root volume, but with MapR-FS 
> it is a good idea to create volumes for different data/use cases and then 
> mount these volumes on MapR-FS.
> This allows for benefits like topology, quota & security management; also 
> ease of use for enterprise features like mirroring, snapshots, etc in the 
> future to name a few.
> https://maprdocs.mapr.com/home/AdministratorGuide/c_managing_data_with_volumes.html
Managing Data with 
Volumes
maprdocs.mapr.com
MapR provides volumes as a way to organize data and manage cluster performance. 
A volume is a logical unit that allows you to apply policies to a set of files, 
directories, and sub-volumes. A ...



>
>
> --Andries
>
>
> On 9/13/17, 12:38 AM, "Takeo Ogawara"  wrote:
>
>Hi,
>
>I modified storage config like this.
>
>"type": "file",
> "enabled": true,
> "connection": "maprfs:///",
> "config": null,
> "workspaces": {
>   "root": {
> "location": "/",
> "writable": false,
> "defaultInputFormat": null
>   }
> }
>
>But query like “select * from mfs.`x.pcap`” failed.
>Is there any other configuration I should modify?
>
>This is drillbit.log and it seems java.io.FileInputStream is going to open 
> MapR FS file path.
>
>Thank you.
>
>2017-09-13 16:20:06,123 [264723d8-bcba-6330-c9be-1c9c95dd2aa6:foreman] 
> INFO  o.a.d.c.s.persistence.ScanResult - loading 9 classes for 
> org.apache.drill.exec.store.dfs.FormatPlugin took 0ms
>2017-09-13 16:20:06,124 [264723d8-bcba-6330-c9be-1c9c95dd2aa6:foreman] 
> INFO  o.a.d.c.s.persistence.ScanResult - loading 10 classes for 
> org.apache.drill.common.logical.FormatPluginConfig took 0ms
>2017-09-13 16:20:06,124 [264723d8-bcba-6330-c9be-1c9c95dd2aa6:foreman] 
> INFO  o.a.d.c.s.persistence.ScanResult - loading 10 classes for 
> org.apache.drill.common.logical.FormatPluginConfig took 0ms
>2017-09-13 16:20:06,125 [264723d8-bcba-6330-c9be-1c9c95dd2aa6:foreman] 
> INFO  o.a.d.c.s.persistence.ScanResult - loading 10 classes for 
> org.apache.drill.com