hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dudu Markovitz (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-16496) Enhance asterisk expression (as in "select *") with EXCLUDE clause
Date Fri, 21 Apr 2017 06:20:04 GMT

     [ https://issues.apache.org/jira/browse/HIVE-16496?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Dudu Markovitz updated HIVE-16496:
----------------------------------
    Description: 
support the following syntax:

> select * exclude (a,b,e) from t

which for a table t with columns a,b,c,d,e would be equal to:

> select c,d from t

A common use case would be:

> select t1.* exclude (x), t2.* from t1 join t2 on t1.x=t2.x;

This supplies a very clean way to select all columns without getting "Ambiguous column reference"
and without the need to specify all the columns of at least one of the tables.
 
Currently, without this enhancement, the query would look something like this:

> select a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,y,z,t2.* from t1 join t2 on t1.x=t2.x;

An extension to this enhancement would be supporting positional column notation, e.g.:

> select * exclude (${1,2,5})    from t    -- exclude columns 1, 2 and 5
> select * exclude (${1-3})       from t    -- exclude columns 1 to 3
> select * exclude (${1,3-5,7}) from t    -- exclude columns 1,3 to 5 and 7
> select * exclude (-${1,2})      from t    -- exclude last 2 columns
> select * exclude (-${1-3,7})   from t    -- exclude last 3 columns and the 7th column
from the end

A complex example would look like:

> select * exclude (${1-4,5,7},x,y,-${1-3})      from t  

exclude:
- first 4 columns
- 5th and 7th columns
- x and y 
- last 3 columns


  was:
support the following syntax:

> select * exclude (a,b,e) from t

which for a table t with columns a,b,c,d,e would be equal to:

> select c,d from t

A common use case would be:

> select t1.* exclude (x), t2.* from t1 join t2 on t1.x=t2.x;

This supplies a very clean way to select all columns without getting "Ambiguous column reference"
and without the need to specify all the columns of at least one of the tables.
 
Currently, without this enhancement, the query would look something like this:

> select a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,y,z,t2.* from t1 join t2 on t1.x=t2.x;

An extension to this enhancement would be supporting positional column notation, e.g.:

> select * exclude ($1,$2,$5) from t    -- exclude columns 1, 2 and 5
> select * exclude (-$1,-$2)   from t    -- exclude last 2 columns
> select * exclude ($1-3)       from t    -- exclude first 3 columns 
> select * exclude (-$1-3)      from t    -- exclude last 3 columns

A complex example would look like:

> select * exclude ($1-2,x,y,-$1-3)      from t  

exclude:
- first 2 columns
- x and y 
- last 3 columns



> Enhance asterisk expression (as in "select *") with EXCLUDE clause
> ------------------------------------------------------------------
>
>                 Key: HIVE-16496
>                 URL: https://issues.apache.org/jira/browse/HIVE-16496
>             Project: Hive
>          Issue Type: Wish
>          Components: Parser
>            Reporter: Dudu Markovitz
>
> support the following syntax:
> > select * exclude (a,b,e) from t
> which for a table t with columns a,b,c,d,e would be equal to:
> > select c,d from t
> A common use case would be:
> > select t1.* exclude (x), t2.* from t1 join t2 on t1.x=t2.x;
> This supplies a very clean way to select all columns without getting "Ambiguous column
reference" and without the need to specify all the columns of at least one of the tables.
>  
> Currently, without this enhancement, the query would look something like this:
> > select a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,y,z,t2.* from t1 join t2 on
t1.x=t2.x;
> An extension to this enhancement would be supporting positional column notation, e.g.:
> > select * exclude (${1,2,5})    from t    -- exclude columns 1, 2 and 5
> > select * exclude (${1-3})       from t    -- exclude columns 1 to 3
> > select * exclude (${1,3-5,7}) from t    -- exclude columns 1,3 to 5 and 7
> > select * exclude (-${1,2})      from t    -- exclude last 2 columns
> > select * exclude (-${1-3,7})   from t    -- exclude last 3 columns and the 7th column
from the end
> A complex example would look like:
> > select * exclude (${1-4,5,7},x,y,-${1-3})      from t  
> exclude:
> - first 4 columns
> - 5th and 7th columns
> - x and y 
> - last 3 columns



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Mime
View raw message