You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Carter Shanklin (JIRA)" <ji...@apache.org> on 2017/06/29 16:22:00 UTC
[jira] [Commented] (HIVE-16496) Enhance asterisk expression (as in
"select *") with EXCLUDE clause
[ https://issues.apache.org/jira/browse/HIVE-16496?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16068556#comment-16068556 ]
Carter Shanklin commented on HIVE-16496:
----------------------------------------
Ran across this today:
https://stackoverflow.com/questions/34056485/select-all-columns-except-some-in-google-bigquery
Google BigQuery added syntax like:
SELECT * EXCEPT (order_id) REPLACE ("widget" AS item_name)
which might be interesting to align with.
> 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:
> {code}
> select * exclude (a,b,e) from t
> {code}
> which for a table t with columns a,b,c,d,e would be equal to:
> {code}
> select c,d from t
> {code}
> Please note that the EXCLUDE clause relates directly to its preceding asterisk.
> Here are some useful use cases:
> h3. use-case 1: join
> {code}
> select t1.* exclude (x), t2.* from t1 join t2 on t1.x=t2.x;
> {code}
> 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:
> {code}
> 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;
> {code}
> Considering a table may hold hundreds or even thousands of column, this can be come very ugly and error prone.
> Often this require some scripting work.
> h3. use-case 2: view
> Creating views with all the tables columns except for some technical columns
>
> {code}
> create myview as select * exclude (cre_ts,upd_ts) from t;
> {code}
> h3. use-case 3: row_number
> Remove computational columns that are not needed in the final row-set, e.g. -
> retrieve the last record for each customer
> {code}
> select * exclude (rn)
> from (select t.*
> ,row_number() over (partition by customer_id order by ts desc) as rn
> from t
> ) t
>
> where rn = 1
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)