You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Sanjiv Kumar <sa...@gmail.com> on 2017/01/17 11:36:13 UTC
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
<pluginName>.<schemaName>.<tableName>
Can anyone tell the solution.??
--
Thanks & Regards.
Sanjiv Kumar.
Re: Regarding Drill Select query
Posted by Arina Yelchiyeva <ar...@gmail.com>.
I am not quite sure why name column from your db is not returning results.
I see you created Jira that this column returns empty result using REST
API.
When I join two tables where one of them has name column I don't have
problems you are describing.
On Tue, Jan 17, 2017 at 4:37 PM, Sanjiv Kumar <sa...@gmail.com> wrote:
> Hello Arina
> Then what is the solution for that.? Is there any other
> solution for that.??
>
> On Tue, Jan 17, 2017 at 7:11 PM, Sanjiv Kumar <sa...@gmail.com> wrote:
>
> > 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 <pluginName>.<dbName>.<
> schemaName>.<tableName1>
> > AS T2 INNER JOIN <pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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 <pluginName>.<dbName>.<
> schemaName>.<tableName1>
> >> 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
> >> <pluginName>.<dbName>.<schemaName>.<tableName1> AS T2 INNER JOIN
> >> <pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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
> >>> <pluginName>.<schemaName>.<tableName>
> >>>
> >>> Can anyone tell the solution.??
> >>>
> >>> --
> >>> Thanks & Regards.
> >>> Sanjiv Kumar.
> >>>
> >>
> >>
> >>
> >> --
> >> Thanks & Regards.
> >> Sanjiv Kumar.
> >>
> >
> >
> >
> > --
> > Thanks & Regards.
> > Sanjiv Kumar.
> >
>
>
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>
Re: Regarding Drill Select query
Posted by Sanjiv Kumar <sa...@gmail.com>.
Hello Arina
Then what is the solution for that.? Is there any other
solution for that.??
On Tue, Jan 17, 2017 at 7:11 PM, Sanjiv Kumar <sa...@gmail.com> wrote:
> 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 <pluginName>.<dbName>.<schemaName>.<tableName1>
> AS T2 INNER JOIN <pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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 <pluginName>.<dbName>.<schemaName>.<tableName1>
>> 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
>> <pluginName>.<dbName>.<schemaName>.<tableName1> AS T2 INNER JOIN
>> <pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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
>>> <pluginName>.<schemaName>.<tableName>
>>>
>>> Can anyone tell the solution.??
>>>
>>> --
>>> Thanks & Regards.
>>> Sanjiv Kumar.
>>>
>>
>>
>>
>> --
>> Thanks & Regards.
>> Sanjiv Kumar.
>>
>
>
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>
--
Thanks & Regards.
Sanjiv Kumar.
Re: Regarding Drill Select query
Posted by Arina Yelchiyeva <ar...@gmail.com>.
No, analytical functions do not require group by.
On Tue, Jan 17, 2017 at 3:41 PM, Sanjiv Kumar <sa...@gmail.com> wrote:
> 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 <pluginName>.<dbName>.<
> schemaName>.<tableName1>
> AS T2 INNER JOIN <pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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 <pluginName>.<dbName>.<
> schemaName>.<tableName1>
> > 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
> > <pluginName>.<dbName>.<schemaName>.<tableName1> AS T2 INNER JOIN
> > <pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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
> >> <pluginName>.<schemaName>.<tableName>
> >>
> >> Can anyone tell the solution.??
> >>
> >> --
> >> Thanks & Regards.
> >> Sanjiv Kumar.
> >>
> >
> >
> >
> > --
> > Thanks & Regards.
> > Sanjiv Kumar.
> >
>
>
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>
Re: Regarding Drill Select query
Posted by Sanjiv Kumar <sa...@gmail.com>.
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 <pluginName>.<dbName>.<schemaName>.<tableName1>
AS T2 INNER JOIN <pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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 <pluginName>.<dbName>.<schemaName>.<tableName1>
> 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
> <pluginName>.<dbName>.<schemaName>.<tableName1> AS T2 INNER JOIN
> <pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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
>> <pluginName>.<schemaName>.<tableName>
>>
>> Can anyone tell the solution.??
>>
>> --
>> Thanks & Regards.
>> Sanjiv Kumar.
>>
>
>
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>
--
Thanks & Regards.
Sanjiv Kumar.
Re: Regarding Drill Select query
Posted by Arina Yelchiyeva <ar...@gmail.com>.
Issue you are describing is not connected with analytic function.
T3.Name is showing since blank since Drill couldn't find such column.
Please use case indicated in your table, for example, T3.`name` AS `Name`.
Kind regards
Arina
On Tue, Jan 17, 2017 at 2:42 PM, Sanjiv Kumar <sa...@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
> <pluginName>.<dbName>.<schemaName>.<tableName1> 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
> <pluginName>.<dbName>.<schemaName>.<tableName1> AS T2 INNER JOIN
> <pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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
> > <pluginName>.<schemaName>.<tableName>
> >
> > Can anyone tell the solution.??
> >
> > --
> > Thanks & Regards.
> > Sanjiv Kumar.
> >
>
>
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>
Re: Regarding Drill Select query
Posted by Sanjiv Kumar <sa...@gmail.com>.
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
<pluginName>.<dbName>.<schemaName>.<tableName1> 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
<pluginName>.<dbName>.<schemaName>.<tableName1> AS T2 INNER JOIN
<pluginName>.<dbName>.<schemaName>.<tableName2> 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 <sa...@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
> <pluginName>.<schemaName>.<tableName>
>
> Can anyone tell the solution.??
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>
--
Thanks & Regards.
Sanjiv Kumar.
Re: Regarding Drill Select query
Posted by Arina Yelchiyeva <ar...@gmail.com>.
Hi Sanjiv,
you can use analytic function:
*select id, name, salary, count(1) over() as cnt from t* or even *select *,
count(1) over() as cnt from t*
You can you star instead of 1 as well (*select *, count(*) over() as cnt
from t*)
Kind regards
Arina
On Tue, Jan 17, 2017 at 1:36 PM, Sanjiv Kumar <sa...@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
> <pluginName>.<schemaName>.<tableName>
>
> Can anyone tell the solution.??
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>