Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-16496

Enhance asterisk expression (as in "select *") with EXCLUDE clause



    • Type: Wish
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Parser
    • Labels:


      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

      Please note that the EXCLUDE clause relates directly to its preceding asterisk.

      Here are some useful use cases:

      use-case 1: join

      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;

      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.

      use-case 2: view

      Creating views with all the tables columns except for some technical columns

      create myview as select * exclude (cre_ts,upd_ts) from t;

      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

      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


          Issue Links



              • Assignee:
                dmarkovitz Dudu Markovitz
              • Votes:
                1 Vote for this issue
                4 Start watching this issue


                • Created: