Google Interactive Charts
-- Thanks & Regards * Sanjiv Kumar*
Re: Drill taking more time to show output
Query Profile STATE: CANCELLATION_REQUESTED FOREMAN: X.com TOTAL FRAGMENTS: 1 Major Fragment: 00-xx-xx <http://192.168.10.37:8047/profiles/2755683d-810b-6588-25a3-49e09b9f4d3e#fragment-0> On Fri, Feb 17, 2017 at 5:17 PM, Sanjiv Kumar C <sanj...@ejeeva.com> wrote: > Drill Environment:- Version: 1.9 > Operating System: Linux > Mode: Embedded mode. > > I have table in sql server having 16 lakh of data. While query through > drill having limit 5 but still drill take more time to show output (more > than 15 min) > > Ex:- select * from <pluginName).. limit 5; > > What should i do? > > -- > Thanks & Regards > * Sanjiv Kumar* > -- Thanks & Regards * Sanjiv Kumar*
Drill taking more time to show output
Drill Environment:- Version: 1.9 Operating System: Linux Mode: Embedded mode. I have table in sql server having 16 lakh of data. While query through drill having limit 5 but still drill take more time to show output (more than 15 min) Ex:- select * from <pluginName).. limit 5; What should i do? -- Thanks & Regards * Sanjiv Kumar*
Re: How to avoid case sensitivity in group by
Hello This is my input table in db:- State Price Alaska 75 Texas 80 Alaska 90 Florida 100 florida 70 Sql Server Query:- select State, Sum (Price) from testTable group by State. Sql Server Output:- State Price Alaska 165 Texas 80 Florida 170 Drill Query:- select T1.State, Sum(T1.Price) from . T1 group by T1.State. Drill Output:- State Price Alaska 165 Texas 80 Florida 100 florida 70 I want same output as shown in Sql Server Output. Please help.
how to show decimal upto 2 points
Hi Drill Environment:- OS:- Window 10 Version:- 1.9 Mode:- embedded mode I have query i have set planner.enable_decimal_data_type=true and while firing select command in drill i got output in decimal value but it showing like 11936.3697. I want to display only upto 2 or 3 value after decimal. like (11936.36). Is there any options are there to show upto decimal upto 2 value.? -- Thanks & Regards. Sanjiv Kumar.
Re: How to avoid case sensitivity in group by
lower and upper is not a solution which i am aspecting. I want to display same output as i get through sql server management studio On Wed, Feb 8, 2017 at 5:15 PM, Sanjiv Kumar <sanjiv...@gmail.com> wrote: > Drill Environment:- OS:- Window 10 >Version:- 1.9 >Mode:- embedded mode > > I have a column name 'State' in db, which have data like(e.g:- Florida, > florida,new jersey, etc). > > My problem is while using count(State) and group by in query, > Florida and florida are showing two seperate rows. > > So how to avoid case sensitivity so that both Florida and florida act as > 1 rows while firing count(Sate).? > > > -- > Thanks & Regards. > Sanjiv Kumar. > -- Thanks & Regards. Sanjiv Kumar.
How to avoid case sensitivity in group by
Drill Environment:- OS:- Window 10 Version:- 1.9 Mode:- embedded mode I have a column name 'State' in db, which have data like(e.g:- Florida, florida,new jersey, etc). My problem is while using count(State) and group by in query, Florida and florida are showing two seperate rows. So how to avoid case sensitivity so that both Florida and florida act as 1 rows while firing count(Sate).? -- Thanks & Regards. Sanjiv Kumar.
Re: How to enable decimal value
Hey.. I got my problem, In select query i am using cast. I have number value in database and while querying through drill, i have cast it to bigint, thats why decimal value are not showing in output even after set planner.enable_decimal_data_type=true. But after removing cast, expected output came. Thanks for your reply Khurram. On Wed, Feb 8, 2017 at 3:15 PM, Sanjiv Kumar <sanjiv...@gmail.com> wrote: > Hello I have decimal value in my table. When query through drill data are > rounded off in output. > > I have enable planner.enable_decimal_data_type = true in system options. > But still data are not showing in decimal form. > > Drill Environment:- OS:- Window 10 >Version:- 1.9 >Mode:- embedded mode > > -- > Thanks & Regards. > Sanjiv Kumar. > -- Thanks & Regards. Sanjiv Kumar.
How to enable decimal value
Hello I have decimal value in my table. When query through drill data are rounded off in output. I have enable planner.enable_decimal_data_type = true in system options. But still data are not showing in decimal form. Drill Environment:- OS:- Window 10 Version:- 1.9 Mode:- embedded mode -- Thanks & Regards. Sanjiv Kumar.
Re: Extra Column are showing in output while querying files
Yes I have a jdbc plugin enabled. On Tue, Jan 31, 2017 at 8:42 PM, Sanjiv Kumar <ktrsan...@gmail.com> wrote: > Hello I am using Drill latest version (1.9) in window and in embedded mode. > > I have csv file in my local drive (D://). While querying through csv file > some extra columns are showing in output. > > EXAMPLE:- > > Query:- select * from dfs.`D:\Test1.csv`; > > Output:- Result is showing file but some extra column are also showing > like fqn, filename,filepath, suffix columns are showing. > > Please tell me how to restrict those extra column.? > > > > > > *Thanks & Regards,* > *Sanjiv Kumar* > >- > > > -- *Thanks & Regards,* *Sanjiv Kumar* - *Mob.No: +917411211960*
Extra Column are showing in output while querying files
Hello I am using Drill latest version (1.9) in window and in embedded mode. I have csv file in my local drive (D://). While querying through csv file some extra columns are showing in output. EXAMPLE:- Query:- select * from dfs.`D:\Test1.csv`; Output:- Result is showing file but some extra column are also showing like fqn, filename,filepath, suffix columns are showing. Please tell me how to restrict those extra column.? *Thanks & Regards,* *Sanjiv Kumar* -
Re: Converting Date to Timestand
Hello I am again posting my question in correct format. Hello I am using Drill latest version (1.9) in window in embedded mode. I am trying to convert Date to Timestamp. While Converting I am using following query:- SELECT T5.`Name` AS `Website Name`, T5.`Description` AS `Description`,TO_CHAR((CAST(T5.`CreateOn` AS TIMESTAMP)), 'MM-dd- hh:mm:ss a') AS `CreateOn` FROM .. AS T4 INNER JOIN ..AS T5 ON T5.`ID` = T4.`WebsiteID` NOTE:- IN MY DATABASE CREATEDON DATA IS LIKE This Format:- 2017-01-05 08:09:17.020 O/p:- If I used this query CreatedOn Column Converted Successfully, but My WEBSITENAME Columns showing null values.. Website Name Description CreateOn nullHootonics 01-05-2017 08:09:17 AM nullHootonics 01-05-2017 08:09:17 AM nullHootonics 01-05-2017 08:09:17 AM nullHootonics 01-05-2017 08:09:17 AM nullHootonics 01-05-2017 08:09:17 AM nullHootonics 01-05-2017 08:09:17 AM But IF I remove TO_CHAR((CAST(T5.`CreateOn` AS TIMESTAMP)), 'MM-dd- hh:mm:ss a') AS `CreateOn` Column from Select query Output showing fine. Please Tell me what i am doing wrong? And How to Convert (2017-01-05 08:09:17.020) data to (01-05-2017 08:09:17 AM).??
Stored Procedure & Function in Apache
Hello I want to know how to use stored procedure and function in Apache Drill.? Does Drill Supports Procedure and Function ? & if not Is there any way to run stored procedure and function which are stored in my Local SqlServer through Drill. *Thanks & Regards,* *Sanjiv Kumar* -
Column Name change in output while using over()
Hello I am using Apache Drill latest version (1.9) in embedded mode. My Problem is :- While using over() in my query alias name changes in output. SEE EXAMPLE:- SELECT CAST(T1.`ID` AS BIGINT) AS `ID`, CAST(T1.`GrandTotal` AS BIGINT) AS `GrandTotal`, TO_CHAR((CAST(T1.`CreatedOn` AS TIMESTAMP)), 'MM-dd- hh:mm:ss a') AS `CreatedOn`, count(0) over() AS `ccount` FROM ... AS T1 LIMIT 100 OFFSET 0 If i used this query column name showing like this:- $0 $1 $2w0$o0 1001 2100 10-03-2016 12:00:00 AM 132 1002 2275 10-03-2016 12:00:00 AM 132 1003 15012-05-2016 12:00:00 AM132 1004 3011-03-2016 12:00:00 AM 132 1005 9510-14-2016 12:00:00 AM 132 1006 1510-05-2016 12:00:00 AM 132 But If i remove count(0) AS `ccount` from query, then column name showing fine. SEE Example:- ID GrandTotal CreatedOn 1001 2100 10-03-2016 12:00:00 AM 1002 2275 10-03-2016 12:00:00 AM 1003 15012-05-2016 12:00:00 AM 1004 3011-03-2016 12:00:00 AM 1005 9510-14-2016 12:00:00 AM 1006 1510-05-2016 12:00:00 AM What is the problem with over(). ??What i am doing wrong in this query.? Why my column name not showing ?? Thanks & Regards. Sanjiv Kumar.
Re: Regarding Drill Select query
Hello Arina I have used same name as in my db table, but still name showing null.. NOTE:- One more thing i have to mention if i group by all the table which are used in select query then the T3.Name is showing fine. SEE EXAMPLE:-- SELECT (CAST(T2.`ID` AS BIGINT)) AS `No of Order`,T2.WebsiteID,T3.`Name` AS `Name`,count(*) over() FROM ... AS T2 INNER JOIN ... AS T3 ON T3.`ID` = T2.`WebsiteID` Group By T2.ID, T2.WebsiteID,T3.`Name`; If i used above query (using group by with over()) then the result is showing fine. But my question is we can't predict which column have to group by at runtime (e.g:- through code). Is Group By Required with OVER() ?? On Tue, Jan 17, 2017 at 6:12 PM, Sanjiv Kumar <sanjiv...@gmail.com> wrote: > Hello Arina > > Thanks for reply. I have tried over() it working fine with > single table, but when i used join then the other table column showing null > value. > > See This Example:- > SELECT (CAST(T2.`ID` AS BIGINT)) AS `No of > Order`,T2.WebsiteID,count(*) over() FROM > ... > AS T2; > > This Working fine. > > But If i used Join.. > > See This Example:- > >SELECT (CAST(T2.`ID` AS BIGINT)) AS `No of > Order`,T2.WebsiteID,T3.`Name` AS `Name`,count(*) over() FROM > ... AS T2 INNER JOIN > ... AS T3 ON T3.`ID` = > T2.`WebsiteID`; > > In this query T3.Name from Table2 showing blank. > > > > > > On Tue, Jan 17, 2017 at 5:06 PM, Sanjiv Kumar <sanjiv...@gmail.com> wrote: > >> Hello >> I want to know.. Can i get all column name and column count in >> single select statement?? >> >> Example:- >> I have table having total 10 rows and Id, Name, Salary >> Column. >> >> Now, Can i write single query to get all th column name (Id, Name, >> Salary) and total count of rows(i.e:- 10) ? >> >> ex:- select T1.Id, T1.Name, T1.Salary, Count(*) from >> .. >> >> Can anyone tell the solution.?? >> >> -- >> Thanks & Regards. >> Sanjiv Kumar. >> > > > > -- > Thanks & Regards. > Sanjiv Kumar. > -- Thanks & Regards. Sanjiv Kumar.
Regarding Drill Select query
Hello I want to know.. Can i get all column name and column count in single select statement?? Example:- I have table having total 10 rows and Id, Name, Salary Column. Now, Can i write single query to get all th column name (Id, Name, Salary) and total count of rows(i.e:- 10) ? ex:- select T1.Id, T1.Name, T1.Salary, Count(*) from .. Can anyone tell the solution.?? -- Thanks & Regards. Sanjiv Kumar.
Alias Name changes while sending request through REST API
Hello I am using Drill latest version (1.9) in Linux in embedded mode. I have doubt.. While sending query request through REST API,I have given alias name to each column but Column names are changes to some different name(like ID$0, etc). My query is :- SELECT CAST(T2.`ID` AS BIGINT) AS `ID`, T3.`Name` AS `Name` FROM ... AS T2 INNER JOIN ... AS T3 ON T3.`ID` = T2.`WebsiteID` Output:- Alias Name i.e ID, Name are not showing in output.Some different column name are showing (like: ID$0, etc) What is the problem. Is this a bug?? Thanks & Regards. Sanjiv Kumar.
How to start drill in distributed mode in window os
I am using apache drill in window 10 having latest version (1.9).
I want to start my drill in distributed mode. I have configure zookeeper
zoo.cfg file:-
tickTime=2000
initLimit=10
syncLimit=5
dataDir=F:/zookeepertest/data
clientPort=2181
server.1=192.589.XX.01:2888:3888
server.1=192.565.XX.02:2888:3888
And Drill folder inside drill-override.conf
drill.exec: { cluster-id: "test", zk.connect:
"192.589.XX.01:2181,192.565.XX.02:2181" }
*And my zookeeper is running..*
Now When i trying to start my drill using this command:-
sqlline.bat -u "jdbc:drill:zk=192.589.XX.01:2181"
Its thoughing following error:-
Error: Failure in connecting to Drill:
org.apache.drill.exec.rpc.RpcException: Failure setting up ZK for
client. (state=,code=0)
java.sql.SQLException: Failure in connecting to Drill:
org.apache.drill.exec.rpc.RpcException: Failure setting up ZK for
client.
at
org.apache.drill.jdbc.impl.DrillConnectionImpl.(DrillConnectionImpl.java:161)
at
org.apache.drill.jdbc.impl.DrillJdbc41Factory.newDrillConnection(DrillJdbc41Factory.java:70)
at
org.apache.drill.jdbc.impl.DrillFactory.newConnection(DrillFactory.java:69)
at
org.apache.calcite.avatica.UnregisteredDriver.connect(UnregisteredDriver.java:143)
at org.apache.drill.jdbc.Driver.connect(Driver.java:72)
at sqlline.DatabaseConnection.connect(DatabaseConnection.java:167)
at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:213)
at sqlline.Commands.connect(Commands.java:1083)
at sqlline.Commands.connect(Commands.java:1015)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:36)
at sqlline.SqlLine.dispatch(SqlLine.java:742)
at sqlline.SqlLine.initArgs(SqlLine.java:528)
at sqlline.SqlLine.begin(SqlLine.java:596)
at sqlline.SqlLine.start(SqlLine.java:375)
at sqlline.SqlLine.main(SqlLine.java:268)
Caused by: org.apache.drill.exec.rpc.RpcException: Failure setting up
ZK for client.
at
org.apache.drill.exec.client.DrillClient.connect(DrillClient.java:245)
at
org.apache.drill.jdbc.impl.DrillConnectionImpl.(DrillConnectionImpl.java:154)
... 18 more
Caused by: java.io.IOException: Failure to connect to the zookeeper
cluster service within the allotted time of 1 milliseconds.
at
org.apache.drill.exec.coord.zk.ZKClusterCoordinator.start(ZKClusterCoordinator.java:123)
at
org.apache.drill.exec.client.DrillClient.connect(DrillClient.java:243)
... 19 more
--
-------
Thanks & Regards
Sanjiv Kumar
Re: while running long query drill stopped
Hi Paul My Tables have 14 columns and some column have less width and some column have more width, and total number of rows are 8124042. And My Log is:---> 2017-01-04 08:05:23,992 [2793533c-3fc5-5aed-dd29-691b47d4b0cb:foreman] INFO o.a.drill.exec.work.foreman.Foreman - Query text for query id 2793533c-3fc5-5aed-dd29-691b47d4b0cb: select * from ... 2017-01-04 08:05:24,012 [2793533c-3fc5-5aed-dd29-691b47d4b0cb:foreman] WARN o.a.d.e.store.jdbc.JdbcStoragePlugin - Failure while attempting to load JDBC schema. com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1668) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1655) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1805) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:3581) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:3482) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:3062) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6120) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:797) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:696) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1716) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(SQLServerStatement.java:631) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetFromInternalQueries(SQLServerDatabaseMetaData.java:228) ~[sqljdbc41.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getCatalogs(SQLServerDatabaseMetaData.java:382) ~[sqljdbc41.jar:na] at org.apache.commons.dbcp.DelegatingDatabaseMetaData.getCatalogs(DelegatingDatabaseMetaData.java:190) ~[commons-dbcp-1.4.jar:1.4] at org.apache.drill.exec.store.jdbc.JdbcStoragePlugin$JdbcCatalogSchema.(JdbcStoragePlugin.java:309) ~[drill-jdbc-storage-1.9.0.jar:1.9.0] at org.apache.drill.exec.store.jdbc.JdbcStoragePlugin.registerSchemas(JdbcStoragePlugin.java:430) [drill-jdbc-storage-1.9.0.jar:1.9.0] at org.apache.drill.exec.store.StoragePluginRegistryImpl$DrillSchemaFactory.registerSchemas(StoragePluginRegistryImpl.java:365) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.store.SchemaTreeProvider.createRootSchema(SchemaTreeProvider.java:72) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.store.SchemaTreeProvider.createRootSchema(SchemaTreeProvider.java:61) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.ops.QueryContext.getRootSchema(QueryContext.java:155) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.ops.QueryContext.getRootSchema(QueryContext.java:145) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.ops.QueryContext.getNewDefaultSchema(QueryContext.java:131) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:62) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:1008) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:264) [drill-java-exec-1.9.0.jar:1.9.0] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_111] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_111] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_111] On Tue, Jan 3, 2017 at 8:01 PM, Sanjiv Kumar <sanjiv...@gmail.com> wrote: > Hello >I am using drill latest version (1.9) in embedded mode in Ubuntu > system. > My query is :- >If i run query having less number of records, drill > working fine. > But If i run long query having (8124042 records), drill > stopped without throwing any error. > > NOTE:- My Ubuntu Server memory is 4GB. Is this cause problem? If not then > please tell me the solution. > > > -- Thanks & Regards. Sanjiv Kumar.
How to get multiple row value as a single column
Hello I need help.Suppose I have one table having categoryName, categoryID, customerName. EXAMPLE:- categoryName categoryID customerName C1 10 Cust1 C1 10 Cust3 C1 10 Cust4 C2 11 Cust2 In above table Multiple Customers having same Category Name. How to get customer name which have same category name in a single row using drill query ? Like:- categoryName customerName C1 Cust1,Cust3,Cust4 C2 Cust2 - Thanks & Regards Sanjiv Kumar
while running long query drill stopped
Hello I am using drill latest version (1.9) in embedded mode in Ubuntu system. My query is :- If i run query having less number of records, drill working fine. But If i run long query having (8124042 records), drill stopped without throwing any error. NOTE:- My Ubuntu Server memory is 4GB. Is this cause problem? If not then please tell me the solution.
How to use apache drill using rest api when authentication and HTTPS are enabled
Hello Wish you a happy new year. I have a doubt.. How to access Apache Drill using REST API when authentication and https both are enabled? -- --- Thanks & Regards Sanjiv Kumar
Limit ALL not working with offset
Hello I trying to use limit all with offset, but it not working. My query:- select c.CategoryID from demoPlugin.dbo.Category as c group by c.CategoryID order by 1 desc limit ALL offset 10; While firing this query its showing:- "No result found." But if i remove offset 10, then it working fine. select c.CategoryID from demoPlugin.dbo.Category as c group by c.CategoryID order by 1 desc limit ALL; What is the problem.? Is limit all is not supported with offset? --- Thanks & Regards Sanjiv Kumar
cast not working in adding two columns
Hello I am using drill 1.9 version in embedded mode and in Window 10. My question is when i am adding two integer column, its working fine. e.g:- select P.`costprice` + P.`initialprice` from testplugin.dbo.testtable P; NOTE:- both columns costprice and initialprice are integer. But when i am adding integer column with varchar column, then it working. e.g:- select P.`costprice` + P.`Name` from testplugin.dbo.testtable P; or select P.`costprice` + cast(P.`Name` as int) from testplugin.dbo.testtable P; Both are not working.Please suggest some way how to add two different datatype columns. -- Thanks & Regards. Sanjiv Kumar.
HTTPD storage plugin
Hello I want to know how and where to configure HTTPD Storage plugin. How it working? Can i convert my IP address to location, country using httpd plugin. -- --- Thanks & Regards Sanjiv Kumar
Re: Sql Server Query taking long time to run
Hello Matt Sorry for late reply. I have used JDBC plugin before with other data source like oracle and mysql, but not for such a huge data. i have used for small amount of data and it work fine without taking any time. But while query through sql-server with 1 million of data in a table, it will take more than an hour for showing output. Please tell me how to speed up my drill. Is there any configuration changes required for speeding up drill query.? On Mon, Nov 28, 2016 at 3:18 PM, Sanjiv Kumar <sanjiv...@gmail.com> wrote: > Hello Matt > > > If i run same query in sql server directly, it will take only few seconds. > > But in Drill it will take hours to show output and even some time Connection > Error (user Client) Close unexpectedly showing > > > -- > Thanks & Regards. > Sanjiv Kumar. > -- Thanks & Regards. Sanjiv Kumar.
Accessing remote drill file from local drill storage plugin
Hello
I am using apache drill version 1.9 in window 10 & running in
embedded mode.
Is it possible to access files(csv, tsv, json, etc) of remote system from
my drill.
Suppose, I have two system having same environment (i.e:- window 10,
embedded mode & 1.9 version). In both the system drill is running. System 1
drill IP is 192.XXX.XX.01:8047, and another one has 192.XXX.XX.02:8047.
Second System i.e 192.XXX.XX.02:8047 has some csv file in D:/ drive.
Can i access that csv file of 2nd System Drill (i.e 192.XXX.XX.02:8047)
using 1st System DFS Storage Plugin
I have tried see my storage plugin:-
{
"type": "file",
"enabled": true,
"connection": "http://192.XXX.XX.02:8047;,
"config": null,
"workspaces": {
"root": {
"location": "/",
"writable": true,
"defaultInputFormat": null
}
},
"formats": {
"json": {
"type": "json",
"extensions": [
"json"
]
}
}
}
And fire Select command, but error showing.
Error: SYSTEM ERROR: IOException: No FileSystem for scheme: http
--
..
Thanks & Regards
*Sanjiv Kumar*
Re: Last Column showing blank in csv file
Hello Yes you are right. select * from `tmp.csv` Is Working fine, but if select column then the last column data showing blank. Run this query:- select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, A.class from dfs.tmp.`copydata.csv` as A; If you run this query you will get last column data as blank. On Wed, Nov 30, 2016 at 11:15 AM, Sanjiv Kumar <ktrsan...@gmail.com> wrote: > I am using latest version 1.8 and in window 10 operating system. > > On Tue, Nov 29, 2016 at 11:40 AM, Sanjiv Kumar <ktrsan...@gmail.com> > wrote: > >> I already pasted the csv file. Just copy and save it as csv. I am also >> attaching csv file. And the query is same as i mention above. >> >> select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, A.class >> from dfs.tmp.`copydata.csv` as A; >> >> On Mon, Nov 28, 2016 at 6:30 PM, Sanjiv Kumar <ktrsan...@gmail.com> >> wrote: >> >>> Ya Its working but what if i am using :- >>> select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, >>> A.class from dfs.tmp.`copydata.csv` as A; >>> >>> why my last column data showing blank? >>> And One more thing See my csv file:- >>> >>> sepalen,sepalwidth,patelen,patelwidth,class >>> 5.1,3.5,1.4,Iris-setosa,0.2 >>> 4.9,3,1.4,Iris-setosa,0.2 >>> 4.7,3.2,1.3,Iris-setosa,0.2 >>> 4.6,3.1,1.5,Iris-setosa,0.2 >>> 5,3.6,1.4,Iris-setosa,0.2 >>> 5.4,3.9,1.7,Iris-setosa,0.4 >>> 4.6,3.4,1.4,Iris-setosa,0.3 >>> 5,3.4,1.5,Iris-setosa,0.2 >>> 4.4,2.9,1.4,Iris-setosa,0.2 >>> 4.9,3.1,1.5,Iris-setosa,0.1 >>> 5.4,3.7,1.5,Iris-setosa,0.2 >>> 4.8,3.4,1.6,Iris-setosa,0.2 >>> >>> This is my previous file. >>> Now if i add comma after class Check this new file. >>> >>> sepalen,sepalwidth,patelen,patelwidth,class, >>> 5.1,3.5,1.4,Iris-setosa,0.2 >>> 4.9,3,1.4,Iris-setosa,0.2 >>> 4.7,3.2,1.3,Iris-setosa,0.2 >>> 4.6,3.1,1.5,Iris-setosa,0.2 >>> 5,3.6,1.4,Iris-setosa,0.2 >>> 5.4,3.9,1.7,Iris-setosa,0.4 >>> 4.6,3.4,1.4,Iris-setosa,0.3 >>> 5,3.4,1.5,Iris-setosa,0.2 >>> 4.4,2.9,1.4,Iris-setosa,0.2 >>> 4.9,3.1,1.5,Iris-setosa,0.1 >>> 5.4,3.7,1.5,Iris-setosa,0.2 >>> 4.8,3.4,1.6,Iris-setosa,0.2 >>> >>> >>> And Fire this query:- select A.`sepalen`, A.`sepalwidth`, A.`patelen`, >>> A.`patelwidth`, A.class from dfs.tmp.`copydata.csv` as A; >>> >>> then the output is show fine. but if the comma is not there in then the >>> last column data showing blank. >>> >>> Is this a bug ?? >>> .. >>> Thanks & Regards >>> *Sanjiv Kumar* >>> >> >> >> >> -- >> .. >> Thanks & Regards >> *Sanjiv Kumar* >> > > > > -- > .. > Thanks & Regards > *Sanjiv Kumar* > -- .. Thanks & Regards *Sanjiv Kumar*
Re: Last Column showing blank in csv file
I am using latest version 1.8 and in window 10 operating system. On Tue, Nov 29, 2016 at 11:40 AM, Sanjiv Kumar <ktrsan...@gmail.com> wrote: > I already pasted the csv file. Just copy and save it as csv. I am also > attaching csv file. And the query is same as i mention above. > > select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, A.class > from dfs.tmp.`copydata.csv` as A; > > On Mon, Nov 28, 2016 at 6:30 PM, Sanjiv Kumar <ktrsan...@gmail.com> wrote: > >> Ya Its working but what if i am using :- >> select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, >> A.class from dfs.tmp.`copydata.csv` as A; >> >> why my last column data showing blank? >> And One more thing See my csv file:- >> >> sepalen,sepalwidth,patelen,patelwidth,class >> 5.1,3.5,1.4,Iris-setosa,0.2 >> 4.9,3,1.4,Iris-setosa,0.2 >> 4.7,3.2,1.3,Iris-setosa,0.2 >> 4.6,3.1,1.5,Iris-setosa,0.2 >> 5,3.6,1.4,Iris-setosa,0.2 >> 5.4,3.9,1.7,Iris-setosa,0.4 >> 4.6,3.4,1.4,Iris-setosa,0.3 >> 5,3.4,1.5,Iris-setosa,0.2 >> 4.4,2.9,1.4,Iris-setosa,0.2 >> 4.9,3.1,1.5,Iris-setosa,0.1 >> 5.4,3.7,1.5,Iris-setosa,0.2 >> 4.8,3.4,1.6,Iris-setosa,0.2 >> >> This is my previous file. >> Now if i add comma after class Check this new file. >> >> sepalen,sepalwidth,patelen,patelwidth,class, >> 5.1,3.5,1.4,Iris-setosa,0.2 >> 4.9,3,1.4,Iris-setosa,0.2 >> 4.7,3.2,1.3,Iris-setosa,0.2 >> 4.6,3.1,1.5,Iris-setosa,0.2 >> 5,3.6,1.4,Iris-setosa,0.2 >> 5.4,3.9,1.7,Iris-setosa,0.4 >> 4.6,3.4,1.4,Iris-setosa,0.3 >> 5,3.4,1.5,Iris-setosa,0.2 >> 4.4,2.9,1.4,Iris-setosa,0.2 >> 4.9,3.1,1.5,Iris-setosa,0.1 >> 5.4,3.7,1.5,Iris-setosa,0.2 >> 4.8,3.4,1.6,Iris-setosa,0.2 >> >> >> And Fire this query:- select A.`sepalen`, A.`sepalwidth`, A.`patelen`, >> A.`patelwidth`, A.class from dfs.tmp.`copydata.csv` as A; >> >> then the output is show fine. but if the comma is not there in then the >> last column data showing blank. >> >> Is this a bug ?? >> .. >> Thanks & Regards >> *Sanjiv Kumar* >> > > > > -- > .. > Thanks & Regards > *Sanjiv Kumar* > -- .. Thanks & Regards *Sanjiv Kumar*
Re: Last Column showing blank in csv file
I already pasted the csv file. Just copy and save it as csv. I am also attaching csv file. And the query is same as i mention above. select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, A.class from dfs.tmp.`copydata.csv` as A; On Mon, Nov 28, 2016 at 6:30 PM, Sanjiv Kumar <ktrsan...@gmail.com> wrote: > Ya Its working but what if i am using :- > select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, > A.class from dfs.tmp.`copydata.csv` as A; > > why my last column data showing blank? > And One more thing See my csv file:- > > sepalen,sepalwidth,patelen,patelwidth,class > 5.1,3.5,1.4,Iris-setosa,0.2 > 4.9,3,1.4,Iris-setosa,0.2 > 4.7,3.2,1.3,Iris-setosa,0.2 > 4.6,3.1,1.5,Iris-setosa,0.2 > 5,3.6,1.4,Iris-setosa,0.2 > 5.4,3.9,1.7,Iris-setosa,0.4 > 4.6,3.4,1.4,Iris-setosa,0.3 > 5,3.4,1.5,Iris-setosa,0.2 > 4.4,2.9,1.4,Iris-setosa,0.2 > 4.9,3.1,1.5,Iris-setosa,0.1 > 5.4,3.7,1.5,Iris-setosa,0.2 > 4.8,3.4,1.6,Iris-setosa,0.2 > > This is my previous file. > Now if i add comma after class Check this new file. > > sepalen,sepalwidth,patelen,patelwidth,class, > 5.1,3.5,1.4,Iris-setosa,0.2 > 4.9,3,1.4,Iris-setosa,0.2 > 4.7,3.2,1.3,Iris-setosa,0.2 > 4.6,3.1,1.5,Iris-setosa,0.2 > 5,3.6,1.4,Iris-setosa,0.2 > 5.4,3.9,1.7,Iris-setosa,0.4 > 4.6,3.4,1.4,Iris-setosa,0.3 > 5,3.4,1.5,Iris-setosa,0.2 > 4.4,2.9,1.4,Iris-setosa,0.2 > 4.9,3.1,1.5,Iris-setosa,0.1 > 5.4,3.7,1.5,Iris-setosa,0.2 > 4.8,3.4,1.6,Iris-setosa,0.2 > > > And Fire this query:- select A.`sepalen`, A.`sepalwidth`, A.`patelen`, > A.`patelwidth`, A.class from dfs.tmp.`copydata.csv` as A; > > then the output is show fine. but if the comma is not there in then the > last column data showing blank. > > Is this a bug ?? > .. > Thanks & Regards > *Sanjiv Kumar* > -- .. Thanks & Regards *Sanjiv Kumar* sepalen,sepalwidth,patelen,patelwidth,class, 5.1,3.5,1.4,Iris-setosa,0.2 4.9,3,1.4,Iris-setosa,0.2 4.7,3.2,1.3,Iris-setosa,0.2 4.6,3.1,1.5,Iris-setosa,0.2 5,3.6,1.4,Iris-setosa,0.2 5.4,3.9,1.7,Iris-setosa,0.4 4.6,3.4,1.4,Iris-setosa,0.3 5,3.4,1.5,Iris-setosa,0.2 4.4,2.9,1.4,Iris-setosa,0.2 4.9,3.1,1.5,Iris-setosa,0.1 5.4,3.7,1.5,Iris-setosa,0.2 4.8,3.4,1.6,Iris-setosa,0.2
Re: Sql Server Query taking long time to run
Hello Matt Keranen It will take only few second. -- Thanks & Regards. Sanjiv Kumar.
Re: Sql Server Query taking long time to run
Hello Matt If i run same query in sql server directly, it will take only few seconds. But in Drill it will take hours to show output and even some time Connection Error (user Client) Close unexpectedly showing -- Thanks & Regards. Sanjiv Kumar.
Re: Last Column showing blank in csv file
Ya Its working but what if i am using :- select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, A.class from dfs.tmp.`copydata.csv` as A; why my last column data showing blank? And One more thing See my csv file:- sepalen,sepalwidth,patelen,patelwidth,class 5.1,3.5,1.4,Iris-setosa,0.2 4.9,3,1.4,Iris-setosa,0.2 4.7,3.2,1.3,Iris-setosa,0.2 4.6,3.1,1.5,Iris-setosa,0.2 5,3.6,1.4,Iris-setosa,0.2 5.4,3.9,1.7,Iris-setosa,0.4 4.6,3.4,1.4,Iris-setosa,0.3 5,3.4,1.5,Iris-setosa,0.2 4.4,2.9,1.4,Iris-setosa,0.2 4.9,3.1,1.5,Iris-setosa,0.1 5.4,3.7,1.5,Iris-setosa,0.2 4.8,3.4,1.6,Iris-setosa,0.2 This is my previous file. Now if i add comma after class Check this new file. sepalen,sepalwidth,patelen,patelwidth,class, 5.1,3.5,1.4,Iris-setosa,0.2 4.9,3,1.4,Iris-setosa,0.2 4.7,3.2,1.3,Iris-setosa,0.2 4.6,3.1,1.5,Iris-setosa,0.2 5,3.6,1.4,Iris-setosa,0.2 5.4,3.9,1.7,Iris-setosa,0.4 4.6,3.4,1.4,Iris-setosa,0.3 5,3.4,1.5,Iris-setosa,0.2 4.4,2.9,1.4,Iris-setosa,0.2 4.9,3.1,1.5,Iris-setosa,0.1 5.4,3.7,1.5,Iris-setosa,0.2 4.8,3.4,1.6,Iris-setosa,0.2 And Fire this query:- select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, A.class from dfs.tmp.`copydata.csv` as A; then the output is show fine. but if the comma is not there in then the last column data showing blank. Is this a bug ?? .. Thanks & Regards *Sanjiv Kumar*
Last Column showing blank in csv file
My Hello I have one csv file having data:- sepalen,sepalwidth,patelen,patelwidth,class 5.1,3.5,1.4,Iris-setosa,0.2 4.9,3,1.4,Iris-setosa,0.2 4.7,3.2,1.3,Iris-setosa,0.2 4.6,3.1,1.5,Iris-setosa,0.2 5,3.6,1.4,Iris-setosa,0.2 5.4,3.9,1.7,Iris-setosa,0.4 4.6,3.4,1.4,Iris-setosa,0.3 5,3.4,1.5,Iris-setosa,0.2 4.4,2.9,1.4,Iris-setosa,0.2 4.9,3.1,1.5,Iris-setosa,0.1 5.4,3.7,1.5,Iris-setosa,0.2 4.8,3.4,1.6,Iris-setosa,0.2 While Query through this file using this query:- select A.`sepalen`, A.`sepalwidth`, A.`patelen`, A.`patelwidth`, A.class from dfs.tmp.`copydata.csv` as A; Last Columm Data showing null. What is the problem. Is this a bug.?? My Output is:- class column showing blank. -- .. Thanks & Regards *Sanjiv Kumar*
Sql Server Query taking long time to run
Hello I have sql server databases having 1 million of data. I am using drill in window having 1.8 version. And Its running in embedded mode. While firing select query, it will take almost 1 hour to run the query. Even i have given limit also it will take 1 hour to run. I am executing this query:- select * from demoplugin.dbo.category limit 5; Even this query also take 1 hour to show output. Please tell some solution for that. -- Thanks & Regards. Sanjiv Kumar.
Re: Connecting to Drill Programmatically in embedded mode on Windows
It will work. Check out this link.. https://technologyredirect.blogspot.in/2016/09/how-to-run-apache-drill-programatically.html
Speed Up Drill
Hello I am using Drill in Window 10 in embedded mode having latest version (1.7). I am writing one select Query for Sql Server Database. Query is:- SELECT CAST(T1.`ID` AS BIGINT) AS `ID`, CAST(T1.`AttributeID` AS BIGINT) AS `AttributeID`, T1.`String_Value` AS `String_Value`, CAST(T1.`Numeric_Value` AS BIGINT) AS `Numeric_Value`, T1.`MetaData` AS `MetaData`, T1.`DataType` AS `DataType`, T1.`CreatedTime` AS `CreatedTime`, CAST(T1.`CreatedBy` AS BIGINT) AS `CreatedBy` FROM demoplugin.`dbo`.`ProductValues` AS T1 Result:- It returns 1,113,843 rows in 107.126 Second. My question is is there any way to speed up my drill, so that query execute fast.?
Drill User Authentication Configuration
Hello
I am running Apache Drill in following version:-
Drill Version - 1.7
OS - Window 8.1
I am running drill in embedded mode.
I am trying to configure user authentication in Drill.
I am referring :
https://drill.apache.org/docs/configuring-user-authentication/
I copies libpam.so at /opt/pam (it does not contain any hadoop component)
I made entry in drill-env.sh:
export DRILLBIT_JAVA_OPTS="-Djava.library.path=/opt/pam/"
I modified drill-override.conf:
drill.exec {
security.user.auth {
enabled: true,
packages += "org.apache.drill.exec.rpc.user.security",
impl: "pam",
pam_profiles: [ "sudo", "login" ]
}
}
I am getting following error:-
HADOOP_HOME not detected...
HBASE_HOME not detected...
Calculating Drill classpath...
Error: Failure in starting embedded Drillbit: org.apache.drill.exec.exception.Dr
illbitStartupException: Problem in finding the native library of JPAM (Pluggable
Authenticator Module API). Make sure to set Drillbit JVM option 'java.library.p
ath' to point to the directory where the native JPAM exists.:no jpam in java.lib
rary.path (state=,code=0)
java.sql.SQLException: Failure in starting embedded Drillbit: org.apache.drill.e
xec.exception.DrillbitStartupException: Problem in finding the native library of
JPAM (Pluggable Authenticator Module API). Make sure to set Drillbit JVM option
'java.library.path' to point to the directory where the native JPAM exists.:no
jpam in java.library.path
at org.apache.drill.jdbc.impl.DrillConnectionImpl.(DrillConnection
Impl.java:127)
at org.apache.drill.jdbc.impl.DrillJdbc41Factory.newDrillConnection(Dril
lJdbc41Factory.java:64)
at org.apache.drill.jdbc.impl.DrillFactory.newConnection(DrillFactory.ja
va:69)
at net.hydromatic.avatica.UnregisteredDriver.connect(UnregisteredDriver.
java:126)
at org.apache.drill.jdbc.Driver.connect(Driver.java:72)
at sqlline.DatabaseConnection.connect(DatabaseConnection.java:167)
at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:213)
at sqlline.Commands.connect(Commands.java:1083)
at sqlline.Commands.connect(Commands.java:1015)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.jav
a:36)
at sqlline.SqlLine.dispatch(SqlLine.java:742)
at sqlline.SqlLine.initArgs(SqlLine.java:528)
at sqlline.SqlLine.begin(SqlLine.java:596)
at sqlline.SqlLine.start(SqlLine.java:375)
at sqlline.SqlLine.main(SqlLine.java:268)
Caused by: org.apache.drill.exec.exception.DrillbitStartupException: Problem in
finding the native library of JPAM (Pluggable Authenticator Module API). Make su
re to set Drillbit JVM option 'java.library.path' to point to the directory wher
e the native JPAM exists.:no jpam in java.library.path
at org.apache.drill.exec.rpc.user.security.PamUserAuthenticator.setup(Pa
mUserAuthenticator.java:53)
at org.apache.drill.exec.rpc.user.security.UserAuthenticatorFactory.crea
teAuthenticator(UserAuthenticatorFactory.java:90)
at org.apache.drill.exec.rpc.user.UserServer.(UserServer.java:81)
at org.apache.drill.exec.service.ServiceEngine.(ServiceEngine.java
:78)
at org.apache.drill.exec.server.Drillbit.(Drillbit.java:108)
at org.apache.drill.exec.server.Drillbit.(Drillbit.java:84)
at org.apache.drill.jdbc.impl.DrillConnectionImpl.(DrillConnection
Impl.java:117)
... 18 more
Caused by: java.lang.UnsatisfiedLinkError: no jpam in java.library.path
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.loadLibrary0(Unknown Source)
at java.lang.System.loadLibrary(Unknown Source)
at net.sf.jpam.Pam.(Pam.java:51)
at org.apache.drill.exec.rpc.user.security.PamUserAuthenticator.setup(Pa
mUserAuthenticator.java:47)
... 24 more
apache drill 1.7.0
"start your sql engine"
Please tell me some solution for that.?
..
Thanks & Regards
*Sanjiv*
How drill works internally
How drill runs query internally. I want to know how drill execute query for different data sources.I want to know internal process of drill. .. Thanks & Regards *Sanjiv Kumar*
Re: Enabling & Disabling Storage Plugin Programatically
Hello
When i debug the storage plugin enable or disable button, there they
calling on button function() :- onclick="doEnable('StoragePlugin
Name", true) [function].
I want to know how to call doEnable() [function()] for
enabling or disabling storage plugin.
Is there any way to call that function(doEnable())?
..
Thanks & Regards
*Sanjiv Kumar*
Enabling & Disabling Storage Plugin Programatically
I am Using Apache Drill in Window Operating System having latest version of
drill (1.7).
I want to know how to enable or disable storage plugin pragmatically(C#
code).
When i debug the storage plugin enable or disable button, there they
calling on button function() :- onclick="doEnable('StoragePlugin Name",
true) [function].
I want to know how to call doEnable() [function()], programatically for
enabling or disabling storage plugin.??
..
Thanks & Regards
*Sanjiv Kumar*
Enable or Disable multiple storage plugin at a time
Is it possible to enable or disable multiple storage plugins at a time through web console. OR Is there any other specific way to do so. ?? -- .. Thanks & Regards *Sanjiv Kumar*
Re: Pivot in Apache Drill
I have tables : Sl No Name Status Time Taken 1 Description In Progress 2 2 StockNumber In Progress 3 3 SpecSheet Completed 3 I want to display all the row of NAME column(i.e:- Description. StockNumber, SpecSheet) as Column name in SQL SERVER using Drill. EXAMPLE:- Description StockNumber SpecSheet Would you please tell me how to acheive this output in Apache Drill.(NOTE:- In SQL SERVER)
Issue with join query having same storage plugin name and same datatype
I want to join two table using same storage plugin. But One Of the Column showing null value.I am using drill in window 8 system with latest version of drill(i.e;1.6) . I am using this query:- SELECT T2.ID AS T_ID,T1.ID AS T1_ID ,T1.ProviderID AS ProviderID, T1.Name AS Name, T2.Code AS Code FROM SqlServer.StuentDetails.P21.Class1 AS T1 right outer JOIN SqlServer.StuentDetails.P21.Class2 AS T2 ON T1.ProviderID = T2.ID Here SqlServer is Storage Plugin Name, StuentDetails is Database Name, P21 is a Schema Name, Class1, Class2 are Table Names. While executing this query T_ID showing Null. But If I am using two different storage plugin name with same credential it works properly. Note:- NOT ONLY IN SQL SERVER, I TRIED IN MYSQL, ORACLE DB ALSO.. SAME PROBLEM CAME. I THINK PROBLEM WITH THE SAME STORAGE PLUGIN NAME HAVING SAME DATATYPE, BECAUSE WHEN I TRIED TO GIVE DIFFERENT DATATYPE TO BOTH THE TABLE, OUTPUT COMING FINE. *Is it possible to join two table using same storage plugin name? If yes,then What am I doing wrong in this query?* .. Thanks & Regards *Sanjiv Kumar*
Re: Pivot in Apache Drill
Then, how to convert row data to column using Apache Drill ? .. Thanks & Regards
Pivot in Apache Drill
Does Apache Drill Supports Sql Pivot. I have this:- SQL QUERY for Creating TABLE is:- `create table Attribute (ID int ,Name varchar(250))`. INSERT QUERY is:- `insert into Attribute values(1, 'Description'), (2, 'StockNumber'), (3, 'Price'), (4, 'Logo'), (5, 'Diagram');` I want show row data (i.e:- Description, StockNumber, Price, Logo, Diagram) as column. I have this sql pivot query:- `select * from ( select id,name from Attribute ) d pivot ( max(id) for name in ([Description],[StockNumber],[Price],[Logo],[Diagram] ) piv;` How to convert this sql query in apache drill.? OR Is There any other solution for that.? .. Thanks & Regards
Regarding Apache Drill 1.7 Version Release Date
Hello Would you please tell me when Apache Drill 1.7 Version will going to release?. .. Thanks & Regards Sanjiv Kumar
Re: Custom Authenticator in Window Operating System.
Hello I want to know that what are the required classes and jar files for compiling java file in window environment for custom authentiaction. OR How I will create Custom Authentication in Window. .. Thanks & Regards *Sanjiv Kumar*
Fwd: Drill Issues
Hello I am facing some problem while using drill. I have also posted earlier my problem one by one, but didn't get any proper solution for that. This time again i am posting my problem. 1) Join Query Problem (for details check in April 16 with *subject*:- Regarding Join Query Problem) 2) Multiple Column having same name & same data type problem (for details check in April 16 *Subject*:- Multiple Column having same name & same data type problem) 3) Authentication in Window Operating System (for details check in April 16 *SUBJECT*:- Regarding Drill Authentication) I hope this time i get some proper solution from Drill. .. Thanks & Regards *Sanjiv Kumar* -- .. Thanks & Regards *Sanjiv Kumar*
Drill Issues
Hello I am facing some problem while using drill. I have also posted earlier my problem one by one, but didn't get any proper solution for that. This time again i am posting my problem. 1) Join Query Problem (for details check in April 16 with *subject*:- Regarding Join Query Problem) 2) Multiple Column having same name & same data type problem (for details check in April 16 *Subject*:- Multiple Column having same name & same data type problem) 3) Authentication in Window Operating System (for details check in April 16 *SUBJECT*:- Regarding Drill Authentication) I hope this time i get some proper solution from Drill. .. Thanks & Regards *Sanjiv Kumar*
Re: Regarding Join Query Problem
Hello I have posted my problem, but didn't get any reply from Drill. I have also posted my problem with example as asked. Please tell me its a bug or i am doing anything wrong in it. If its a bug when drill community going to solve this problem. .. Thanks & Regards *Sanjiv Swaraj*
Re: Regarding Drill Authentication
Hello Magnus Pierre, Thanks for reply.Would you please write some custom authenticator for window. .. Thanks & Regards *Sanjiv Kumar*
Web Console Authentication in Window
Hello
I need help regarding Web Console Authentication. I am using Drill
in Window 8.1 with latest version (1.6) of Drill.
I have make changes in drill-override.conf Files. Following Changes i have
made:-
http: {
enabled: true,
ssl_enabled: true,
port: 8047
session_max_idle_secs: 3600
},
Now My Web console is not opening. I want to know that, how can i access
web console now ( by enabling ssl_enabled: true)?
Where to provide username & password to access web console in authenticate
mode?
..
Thanks & Regards
*Sanjiv Kumar*
Regarding Join Query Problem
Hello I have posted my problem which i am facing in join query. AS SUBJECT:- Multiple Column having same name & same data type problem You have replied for that question and asked to send output, I had posted output as well. AS SUBJECT :- RE:Multiple Column having same name & same data type problem. Please check my problem which i had mention as subject (RE:Multiple Column having same name & same data type problem) & Please tell me some solution for that. --- Thanks & Regards Sanjiv Swaraj
RE:Multiple Column having same name & same data type problem
*I am explaining my problem which i have posted earlier:-* I am posting the output which i am getting after executing join query. *My query is:-* *select Reports.ID, Reports.Name, ReportSetting.ID as RP_ID, ReportSetting.Name as RP_Name from MySql.test_mopslive.reports as Reports join MySql.test_mopslive.reportsetting as ReportSetting on Reports.ID = ReportSetting.ID* ID Name RP_ID RP_Name 30 My_Naveen null null 31 My_Sanjiv null null 32 My_Sreekant null null *EXPECTED O/P:* ID Name RP_ID RP_Name 30 My_Naveen 30 My_Naveen 31 My_Sanjiv 31 My_Sanjiv 32 My_Sreekant 32 My_Sreekant I am trying with this example also.it works fine. *QUERY AFTER CASTING:-* *select Reports.ID, Reports.Name, cast(ReportSetting.ID as int), cast(ReportSetting.Name as varchar(25)) from MySql.test_mopslive.reports as Reports join* * MySql.test_mopslive.reportsetting as ReportSetting on Reports.ID = ReportSetting.ID.* I know its not a proper solution. I think its a bug. So Please suggest some appropriate solution for that. *SCENARIO:->* *1*) When two column having same name and different data type:- Its Showing memory leak error.: *ERROR:-* org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: IllegalStateException: Memory was leaked by query. Memory leaked: (40960) Allocator(op:0:0:3:JdbcSubScan) 100/40960/311296/100 (res/actual/peak/limit) Fragment 0:0 2) When two column having different column name and same data type, its working fine. 3) When two column having same name and same datatype, then its not working.
Multiple Column having same name & same data type problem
*I am explaining my problem which i have posted earlier:-* I am posting the output which i am getting after executing join query. *My query is:-* *select Reports.ID, Reports.Name, ReportSetting.ID as RP_ID, ReportSetting.Name as RP_Name from MySql.test_mopslive.reports as Reports join MySql.test_mopslive.reportsetting as ReportSetting on Reports.ID = ReportSetting.ID* ID Name RP_ID RP_Name 30 My_Naveen null null 31 My_Sanjiv null null 32 My_Sreekant null null *EXPECTED O/P:* ID Name RP_ID RP_Name 30 My_Naveen 30 My_Naveen 31 My_Sanjiv 31 My_Sanjiv 32 My_Sreekant 32 My_Sreekant I am trying with this example also.it works fine. *QUERY AFTER CASTING:-* *select Reports.ID, Reports.Name, cast(ReportSetting.ID as int), cast(ReportSetting.Name as varchar(25)) from MySql.test_mopslive.reports as Reports join* * MySql.test_mopslive.reportsetting as ReportSetting on Reports.ID = ReportSetting.ID.* I know its not a proper solution. I think its a bug. So Please suggest some appropriate solution for that. *SCENARIO:->* *1*) When two column having same name and different data type:- Its Showing memory leak error.: *ERROR:-* org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: IllegalStateException: Memory was leaked by query. Memory leaked: (40960) Allocator(op:0:0:3:JdbcSubScan) 100/40960/311296/100 (res/actual/peak/limit) Fragment 0:0 2) When two column having different column name and same data type, its working fine. 3) When two column having same name and same datatype, then its not working.
Multiple Column having same name & same data type problem
Hello I am facing problem in executing join query. My Scenario is :- I am having two tables and I am joining those tables in a join query with same storage plugin. Those two tables has column with same name and same datatype (ID int). And I am selecting both the ID columns from both tables. Now the problem I am facing is only one ID column has data and the second ID column has null value in the output. If I am using different storage plugin for the tables in the same query the output is coming fine. EXAMPLE:- I am posting mysql db query : TABLE 1-> create table Reports (ID bigint primary key, Name varchar(25)); insert into Reports values (29, 'MySqlThree'); insert into Reports values (30, 'MySqlTwo'); insert into Reports values (31, 'MySqlThree'); TABLE 2-> CREATE TABLE ReportSetting ( P_id bigint primary key, Name varchar(25), ID bigint, CONSTRAINT fk_ID FOREIGN KEY (ID) REFERENCES Reports(ID)); **NOTE(Here i am making ID as foreign key)**. insert into ReportSetting values (1,'My_Sreekant1', 29); insert into ReportSetting values (2,'My_Sreekant2', 30); insert into ReportSetting values (3,'My_Sreekant3', 31); JOIN QUERY:- select * from MySql.test_mopslive.reports as Reports join MySql.test_mopslive.reportsetting as ReportSetting on Reports.ID = ReportSetting.ID **(test_mopslive= STORAGE PLUGIN)** Now I want to use same storage plugin for both the tables. So please suggest me the solution for this problem. Thanks in advance.
Regarding Drill Authentication
Hello I am new in Apache Drill.I am running drill in embedded mode with window 8.1 operating system. My question is how to authenticate drill. I want if any one start drill, it will ask for user name and password to start drill. I know that drill use PAM Authentication. But I don't know how to use PAM in window 8.1 operating system & where and how to store username & password. Please Suggest me or if possible give me some example how to restrict user to access drill in window 8.1 operating system. .. Thanks & Regards Sanjiv Swaraj
Regarding Excel Files and Ms Access File.
Please tell me when or in which updated version of Apache Drill, you are going to give storage plugin for query Excel (.xsl) and Ms Access File (.accdb).
Unable to access User Created Schema in SQL Server
*I want to know whether Apache Drill supports only dbo schema?? or it will
support all type of schema (user create schema )?.*
I am running my system in window 8 and with latest version of
Drill(1.5) with embedded mode.
I am trying to search with same storage plugin:
*{*
* "type" : "jdbc",*
* "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",*
* "url" : "jdbc:sqlserver://;databaseName=",*
* "username" : "",*
* "password" : "",*
* "enabled" : true*
*}*
This DB has dbo and other user created schemas like (core, P21, output,
etc). All the schemas have same type of permission. Its work for dbo
schema where core and other user created schema are not working while
querying from drill.
*My DBO Query:*
* select * from SqlServer.dbo.Attribute; (Its working).*
*My CORE Query:*
*select * from SqlServer.core.Users;(Its not working).*
*NOTE: SqlServer is Storage Plugin Name.*
*My Question is whether Drill Supports only dbo schema or all schemas in
DB ?*
because, while firing show databases; query. User created schema is no
showing(e.g: core, P21, output, etc schemas).
--
Thanks & Regards.
Sanjiv Kumar
Accessing of file from other system in drill
Is it possible to access files from other systems in apache drill? If yes then how?
Question Regarding How to search excel file using drill.
Hello I want to know how to query from excel file(.xslx) and ms access file using drill. -- Thanks & Regards * Sanjiv Kumar*
Regarding Apache Drill question
Hello Would you please tell me (examples) how to show data from sql server using apache drill in c# programming. If possible please give some example or demo. Thanks in Advance. -- Thanks & Regards * Sanjiv Kumar*
