Search code examples
hivebackticks

select clause with backticks

Select
    t1.cust_id,
    `(cust_id|as_of_dt|pref.*|interest.*|incentive|currency)?+.+`
from
    TAB1 t1
    left join TAB2 t2 on (t1.key1 = t2.key1) and (t1.key2 = t2.key2);

There is a starting backtick just before (cust_id... and ending backtick after currency)?+.+ Somehow it is not getting displayed here.

What do the backticks mean. Does it mean that all the columns within curly brackets within the backticks have to be ignored while selecting colums from 2nd table?

This query does not work in Hive on Mapr Installation.

Solution

  • These backticks are used to select all columns except of the listed backticks columns.

    To make use of these REGEX column specification we need to set the below property in our hive session

    hive> set hive.support.quoted.identifiers=none;

    What does this back ticks means?

    `(cust_id|as_of_dt|pref.*|interest.*|incentive|currency)?+.+`

    1.In regex "|" means OR operator

    2.Exclude cust_id (or) as_of_dt(or) like 'pref%'(column name starts with pref and match any character after) (or) like 'interest%'(column name starts with interest and match any character after) (or) incentive (or) currency column names from the result set.

    finally your query will result cust_id column from t1 table and all the columns from t1,t2 tables that are not matching with the above requirement.

    For more details refer to this link regarding REGEX column specification in hive.