You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Paolo Spanevello <pa...@gmail.com> on 2016/01/23 19:47:40 UTC
Drill Explorer: missing the SQL statement
Dear All,
i'm using drill explorer to write the drill. I save them , when I click on
create as. When I close drill explorer and i opened it again it happen two
things:
1. the output of query is come from the query that I saved
2. The text of the SQL query is changed and it is: SELECT * FROM
`dfs`.`tmp`.`provaRider9`
where provaRider9 is the name of the drill saved.
Thansk again for your support.
best,
Paolo
Re: Drill Explorer: missing the SQL statement
Posted by Ted Dunning <te...@gmail.com>.
Can you create a table in this workspace?
On Sun, Feb 7, 2016 at 2:19 AM, Paolo Spanevello <pa...@gmail.com>
wrote:
> Hi Andries,
>
> I set, as you told me, a new workspace but I can not save the view in the
> new one. I have this error:
>
> [07/02/2016 11:15:05] Executing Query: CREATE VIEW
> `Performance_Ride_Analytics.GC`.`prova2` AS select
> concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete,
> tt.ride_date,
> CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as
> `1s_critical_power`
>
> from
> (select RTRIM(t.flat_rides.tags.Athlete,' ') as Athlete,t.flat_rides.`date`
> as ride_date,flatten(t.flat_rides.INTERVALS) as flat_intervals from
> (select flatten(rides) as flat_rides from
> `Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt
> where tt.flat_intervals.name = 'Attivita'' intera '
> [07/02/2016 11:15:05] ERROR [HY000] [MapR][Drill] (1040) Drill failed to
> execute the query: CREATE VIEW `Performance_Ride_Analytics.GC`.`prova2` AS
> select
> concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete,
> tt.ride_date,
> CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as
> `1s_critical_power`
>
> from
> (select RTRIM(t.flat_rides.tags.Athlete,' ') as Athlete,t.flat_rides.`date`
> as ride_date,flatten(t.flat_rides.INTERVALS) as flat_intervals from
> (select flatten(rides) as flat_rides from
> `Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt
> where tt.flat_intervals.name = 'Attivita'' intera '
> [30027]Query execution error. Details:[
> SYSTEM ERROR: PatternSyntaxException: Unexpected internal error near index
> 1
> \
> ^
>
>
> [Error Id: cff767ba-c900-4912-b510-ed7edb01aa9b on 192.168.1.108:31010]
> ]
>
>
> *Is if I save it in the /tmp workspace it works.*
>
> ]
> [07/02/2016 11:15:55] Executing Query: CREATE VIEW
> `Performance_Ride_Analytics.tmp`.`prova2` AS select
> concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete,
> tt.ride_date,
> CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as
> `1s_critical_power`
>
> from
> (select RTRIM(t.flat_rides.tags.Athlete,' ') as Athlete,t.flat_rides.`date`
> as ride_date,flatten(t.flat_rides.INTERVALS) as flat_intervals from
> (select flatten(rides) as flat_rides from
> `Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt
> where tt.flat_intervals.name = 'Attivita'' intera '
> [07/02/2016 11:15:55] SUCCESS: View 'prova2' created successfully in
> 'Performance_Ride_Analytics.tmp' schema
>
>
> Could you please support?
>
> Best,
> Paolo
>
> 2016-01-25 17:09 GMT+01:00 Andries Engelbrecht <aengelbrecht@maprtech.com
> >:
>
> > Paolo,
> >
> > Are you using Drill in Embedded mode?
> > I believe in embedded mode the 'tmp' data is not persistent when shutting
> > Drill down, you probably should use a different workspace (also good
> > practice for distributed mode not to use tmp workspace for persistent
> > objects).
> >
> > When you say write the Drill do you mean creating a table or a view with
> > Drill?
> >
> > --Andries
> >
> >
> > > On Jan 23, 2016, at 10:47 AM, Paolo Spanevello <pa...@gmail.com>
> > wrote:
> > >
> > > Dear All,
> > >
> > > i'm using drill explorer to write the drill. I save them , when I click
> > on
> > > create as. When I close drill explorer and i opened it again it happen
> > two
> > > things:
> > >
> > > 1. the output of query is come from the query that I saved
> > > 2. The text of the SQL query is changed and it is: SELECT * FROM
> > > `dfs`.`tmp`.`provaRider9`
> > >
> > > where provaRider9 is the name of the drill saved.
> > >
> > > Thansk again for your support.
> > >
> > > best,
> > > Paolo
> >
> >
>
Re: Drill Explorer: missing the SQL statement
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Ended up being a file system permission issue. (email thread went private).
--Andries
> On Feb 8, 2016, at 8:14 AM, Andries Engelbrecht <ae...@maprtech.com> wrote:
>
> Make sure when you configured the file system plug in and workspace that you set the workspace as mutable.
>
> By setting this flag to true
>
> "writable": true,
>
>
> Also make sure that the OS user that Drill runs as has write permissions on the file system which the workspace is set to.
> If you use Drill security check the permissions for the users you are using on the file system.
>
>
> --Andries
>
>
>
>
>
>> On Feb 7, 2016, at 2:19 AM, Paolo Spanevello <paolospane@gmail.com <ma...@gmail.com>> wrote:
>>
>> Hi Andries,
>>
>> I set, as you told me, a new workspace but I can not save the view in the new one. I have this error:
>>
>> [07/02/2016 11:15:05] Executing Query: CREATE VIEW `Performance_Ride_Analytics.GC`.`prova2` AS select
>> concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete, tt.ride_date,
>> CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as `1s_critical_power`
>>
>> from
>> (select RTRIM(t.flat_rides.tags.Athlete,' ') as Athlete,t.flat_rides.`date` as ride_date,flatten(t.flat_rides.INTERVALS) as flat_intervals from
>> (select flatten(rides) as flat_rides from `Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt where tt.flat_intervals.name <http://tt.flat_intervals.name/> = 'Attivita'' intera '
>> [07/02/2016 11:15:05] ERROR [HY000] [MapR][Drill] (1040) Drill failed to execute the query: CREATE VIEW `Performance_Ride_Analytics.GC`.`prova2` AS select
>> concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete, tt.ride_date,
>> CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as `1s_critical_power`
>>
>> from
>> (select RTRIM(t.flat_rides.tags.Athlete,' ') as Athlete,t.flat_rides.`date` as ride_date,flatten(t.flat_rides.INTERVALS) as flat_intervals from
>> (select flatten(rides) as flat_rides from `Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt where tt.flat_intervals.name <http://tt.flat_intervals.name/> = 'Attivita'' intera '
>> [30027]Query execution error. Details:[
>> SYSTEM ERROR: PatternSyntaxException: Unexpected internal error near index 1
>> \
>> ^
>>
>>
>> [Error Id: cff767ba-c900-4912-b510-ed7edb01aa9b on 192.168.1.108:31010 <http://192.168.1.108:31010/>]
>> ]
>>
>>
>> Is if I save it in the /tmp workspace it works.
>>
>> ]
>> [07/02/2016 11:15:55] Executing Query: CREATE VIEW `Performance_Ride_Analytics.tmp`.`prova2` AS select
>> concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete, tt.ride_date,
>> CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as `1s_critical_power`
>>
>> from
>> (select RTRIM(t.flat_rides.tags.Athlete,' ') as Athlete,t.flat_rides.`date` as ride_date,flatten(t.flat_rides.INTERVALS) as flat_intervals from
>> (select flatten(rides) as flat_rides from `Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt where tt.flat_intervals.name <http://tt.flat_intervals.name/> = 'Attivita'' intera '
>> [07/02/2016 11:15:55] SUCCESS: View 'prova2' created successfully in 'Performance_Ride_Analytics.tmp' schema
>>
>>
>> Could you please support?
>>
>> Best,
>> Paolo
>>
>> 2016-01-25 17:09 GMT+01:00 Andries Engelbrecht <aengelbrecht@maprtech.com <ma...@maprtech.com>>:
>> Paolo,
>>
>> Are you using Drill in Embedded mode?
>> I believe in embedded mode the 'tmp' data is not persistent when shutting Drill down, you probably should use a different workspace (also good practice for distributed mode not to use tmp workspace for persistent objects).
>>
>> When you say write the Drill do you mean creating a table or a view with Drill?
>>
>> --Andries
>>
>>
>> > On Jan 23, 2016, at 10:47 AM, Paolo Spanevello <paolospane@gmail.com <ma...@gmail.com>> wrote:
>> >
>> > Dear All,
>> >
>> > i'm using drill explorer to write the drill. I save them , when I click on
>> > create as. When I close drill explorer and i opened it again it happen two
>> > things:
>> >
>> > 1. the output of query is come from the query that I saved
>> > 2. The text of the SQL query is changed and it is: SELECT * FROM
>> > `dfs`.`tmp`.`provaRider9`
>> >
>> > where provaRider9 is the name of the drill saved.
>> >
>> > Thansk again for your support.
>> >
>> > best,
>> > Paolo
>>
>>
>
Re: Drill Explorer: missing the SQL statement
Posted by Paolo Spanevello <pa...@gmail.com>.
Dear All,
Above my new workspace. The GC has the set : "writable" : true.
About "Also make sure that the OS user that Drill runs as has write
permissions on the file system which the workspace is set to.
If you use Drill security check the permissions for the users you are using
on the file system. "
How can i check?
Best,
Paolo
{
"type": "file",
"enabled": true,
"connection": "file:///",
"workspaces": {
"root": {
"location": "/",
"writable": false,
"defaultInputFormat": null
},
"tmp": {
"location": "/tmp",
"writable": true,
"defaultInputFormat": null
},
"GC": {
"location": "/Users/**/AppData/Local/***",
"writable": true,
"defaultInputFormat": null
}
},
"formats": {
"psv": {
"type": "text",
"extensions": [
"tbl"
],
"delimiter": "|"
},
"csv": {
"type": "text",
"extensions": [
"csv"
],
"delimiter": ","
},
"tsv": {
"type": "text",
"extensions": [
"tsv"
],
"delimiter": "\t"
},
"parquet": {
"type": "parquet"
},
"json": {
"type": "json"
},
"avro": {
"type": "avro"
},
"sequencefile": {
"type": "sequencefile",
"extensions": [
"seq"
]
},
"csvh": {
"type": "text",
"extensions": [
"csvh"
],
"extractHeader": true,
"delimiter": ","
}
}
}
2016-02-08 17:14 GMT+01:00 Andries Engelbrecht <ae...@maprtech.com>:
> Make sure when you configured the file system plug in and workspace that
> you set the workspace as mutable.
>
> By setting this flag to true
>
> "writable": true,
>
>
> Also make sure that the OS user that Drill runs as has write permissions
> on the file system which the workspace is set to.
> If you use Drill security check the permissions for the users you are
> using on the file system.
>
>
> --Andries
>
>
>
>
>
> On Feb 7, 2016, at 2:19 AM, Paolo Spanevello <pa...@gmail.com> wrote:
>
> Hi Andries,
>
> I set, as you told me, a new workspace but I can not save the view in the
> new one. I have this error:
>
> [07/02/2016 11:15:05] Executing Query: CREATE VIEW
> `Performance_Ride_Analytics.GC`.`prova2` AS select
> concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete,
> tt.ride_date,
> CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as
> `1s_critical_power`
>
> from
> (select RTRIM(t.flat_rides.tags.Athlete,' ') as
> Athlete,t.flat_rides.`date` as ride_date,flatten(t.flat_rides.INTERVALS) as
> flat_intervals from
> (select flatten(rides) as flat_rides from
> `Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt
> where tt.flat_intervals.name = 'Attivita'' intera '
> [07/02/2016 11:15:05] ERROR [HY000] [MapR][Drill] (1040) Drill failed to
> execute the query: CREATE VIEW `Performance_Ride_Analytics.GC`.`prova2` AS
> select
> concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete,
> tt.ride_date,
> CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as
> `1s_critical_power`
>
> from
> (select RTRIM(t.flat_rides.tags.Athlete,' ') as
> Athlete,t.flat_rides.`date` as ride_date,flatten(t.flat_rides.INTERVALS) as
> flat_intervals from
> (select flatten(rides) as flat_rides from
> `Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt
> where tt.flat_intervals.name = 'Attivita'' intera '
> [30027]Query execution error. Details:[
> SYSTEM ERROR: PatternSyntaxException: Unexpected internal error near index
> 1
> \
> ^
>
>
> [Error Id: cff767ba-c900-4912-b510-ed7edb01aa9b on 192.168.1.108:31010]
> ]
>
>
> *Is if I save it in the /tmp workspace it works.*
>
> ]
> [07/02/2016 11:15:55] Executing Query: CREATE VIEW
> `Performance_Ride_Analytics.tmp`.`prova2` AS select
> concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete,
> tt.ride_date,
> CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as
> `1s_critical_power`
>
> from
> (select RTRIM(t.flat_rides.tags.Athlete,' ') as
> Athlete,t.flat_rides.`date` as ride_date,flatten(t.flat_rides.INTERVALS) as
> flat_intervals from
> (select flatten(rides) as flat_rides from
> `Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt
> where tt.flat_intervals.name = 'Attivita'' intera '
> [07/02/2016 11:15:55] SUCCESS: View 'prova2' created successfully in
> 'Performance_Ride_Analytics.tmp' schema
>
>
> Could you please support?
>
> Best,
> Paolo
>
> 2016-01-25 17:09 GMT+01:00 Andries Engelbrecht <ae...@maprtech.com>
> :
>
>> Paolo,
>>
>> Are you using Drill in Embedded mode?
>> I believe in embedded mode the 'tmp' data is not persistent when shutting
>> Drill down, you probably should use a different workspace (also good
>> practice for distributed mode not to use tmp workspace for persistent
>> objects).
>>
>> When you say write the Drill do you mean creating a table or a view with
>> Drill?
>>
>> --Andries
>>
>>
>> > On Jan 23, 2016, at 10:47 AM, Paolo Spanevello <pa...@gmail.com>
>> wrote:
>> >
>> > Dear All,
>> >
>> > i'm using drill explorer to write the drill. I save them , when I click
>> on
>> > create as. When I close drill explorer and i opened it again it happen
>> two
>> > things:
>> >
>> > 1. the output of query is come from the query that I saved
>> > 2. The text of the SQL query is changed and it is: SELECT * FROM
>> > `dfs`.`tmp`.`provaRider9`
>> >
>> > where provaRider9 is the name of the drill saved.
>> >
>> > Thansk again for your support.
>> >
>> > best,
>> > Paolo
>>
>>
>
>
Re: Drill Explorer: missing the SQL statement
Posted by Paolo Spanevello <pa...@gmail.com>.
Hi Andries,
I set, as you told me, a new workspace but I can not save the view in the
new one. I have this error:
[07/02/2016 11:15:05] Executing Query: CREATE VIEW
`Performance_Ride_Analytics.GC`.`prova2` AS select
concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete,
tt.ride_date,
CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as
`1s_critical_power`
from
(select RTRIM(t.flat_rides.tags.Athlete,' ') as Athlete,t.flat_rides.`date`
as ride_date,flatten(t.flat_rides.INTERVALS) as flat_intervals from
(select flatten(rides) as flat_rides from
`Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt
where tt.flat_intervals.name = 'Attivita'' intera '
[07/02/2016 11:15:05] ERROR [HY000] [MapR][Drill] (1040) Drill failed to
execute the query: CREATE VIEW `Performance_Ride_Analytics.GC`.`prova2` AS
select
concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete,
tt.ride_date,
CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as
`1s_critical_power`
from
(select RTRIM(t.flat_rides.tags.Athlete,' ') as Athlete,t.flat_rides.`date`
as ride_date,flatten(t.flat_rides.INTERVALS) as flat_intervals from
(select flatten(rides) as flat_rides from
`Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt
where tt.flat_intervals.name = 'Attivita'' intera '
[30027]Query execution error. Details:[
SYSTEM ERROR: PatternSyntaxException: Unexpected internal error near index 1
\
^
[Error Id: cff767ba-c900-4912-b510-ed7edb01aa9b on 192.168.1.108:31010]
]
*Is if I save it in the /tmp workspace it works.*
]
[07/02/2016 11:15:55] Executing Query: CREATE VIEW
`Performance_Ride_Analytics.tmp`.`prova2` AS select
concat(tt.Athlete,'_',tt.ride_date) as Athlete_Ride_Date, tt.Athlete,
tt.ride_date,
CAST(tt.flat_intervals.metrics.`1s_critical_power` as FLOAT) as
`1s_critical_power`
from
(select RTRIM(t.flat_rides.tags.Athlete,' ') as Athlete,t.flat_rides.`date`
as ride_date,flatten(t.flat_rides.INTERVALS) as flat_intervals from
(select flatten(rides) as flat_rides from
`Performance_Ride_Analytics`.`GC`.`./prova2/cache/rideDB.json`) as t) as tt
where tt.flat_intervals.name = 'Attivita'' intera '
[07/02/2016 11:15:55] SUCCESS: View 'prova2' created successfully in
'Performance_Ride_Analytics.tmp' schema
Could you please support?
Best,
Paolo
2016-01-25 17:09 GMT+01:00 Andries Engelbrecht <ae...@maprtech.com>:
> Paolo,
>
> Are you using Drill in Embedded mode?
> I believe in embedded mode the 'tmp' data is not persistent when shutting
> Drill down, you probably should use a different workspace (also good
> practice for distributed mode not to use tmp workspace for persistent
> objects).
>
> When you say write the Drill do you mean creating a table or a view with
> Drill?
>
> --Andries
>
>
> > On Jan 23, 2016, at 10:47 AM, Paolo Spanevello <pa...@gmail.com>
> wrote:
> >
> > Dear All,
> >
> > i'm using drill explorer to write the drill. I save them , when I click
> on
> > create as. When I close drill explorer and i opened it again it happen
> two
> > things:
> >
> > 1. the output of query is come from the query that I saved
> > 2. The text of the SQL query is changed and it is: SELECT * FROM
> > `dfs`.`tmp`.`provaRider9`
> >
> > where provaRider9 is the name of the drill saved.
> >
> > Thansk again for your support.
> >
> > best,
> > Paolo
>
>
Re: Drill Explorer: missing the SQL statement
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Paolo,
Are you using Drill in Embedded mode?
I believe in embedded mode the 'tmp' data is not persistent when shutting Drill down, you probably should use a different workspace (also good practice for distributed mode not to use tmp workspace for persistent objects).
When you say write the Drill do you mean creating a table or a view with Drill?
--Andries
> On Jan 23, 2016, at 10:47 AM, Paolo Spanevello <pa...@gmail.com> wrote:
>
> Dear All,
>
> i'm using drill explorer to write the drill. I save them , when I click on
> create as. When I close drill explorer and i opened it again it happen two
> things:
>
> 1. the output of query is come from the query that I saved
> 2. The text of the SQL query is changed and it is: SELECT * FROM
> `dfs`.`tmp`.`provaRider9`
>
> where provaRider9 is the name of the drill saved.
>
> Thansk again for your support.
>
> best,
> Paolo