select clause with backticks

Question

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.

Can someone please explain this query? Also if it works on only certain hive installations? — Don Sam, Jul 15 '18 at 00:23
I'm not familiar with Hive, but I think they're used the same way as MySQL: to escape column names when they contain special characters (in SQL Server we use square-brackets `[like this]` and in ANSI SQL it's double-quotes `"like this"`. — Dai, Jul 15 '18 at 00:32

score 2 · Accepted Answer · answered Jul 15 '18 at 03:38

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.

select clause with backticks

1 Answers1