2

EDIT 1: Note, I know some of us will question why not list different parts of information in different attributes, so that I will have a relational database to query. The real case is not like the example I am listing below, the variable names are just used here for convenience.

EDIT 2: To reduce the confusion of database design, I change the variable names in the example.

In Hive query, I am looking for a way to select columns with the same prefix, or the same suffix, or including the same key word in the middle of the variable names from the same table.

Here is an example: I have a list of variables like this:

a_A_1, a_A_2, a_B_1, a_B_2,
b_A_1, b_A_2, b_B_1, b_B_2

Exercise 1 I want to select all the attributes starting with 'a'.

Exercise 2 I want to select all the attributes ending with '1'.

Exercise 3 I want to select all the attributes including 'B'.

Much thanks in advance!

Counter10000
  • 525
  • 1
  • 8
  • 25
  • I would like to point out that that's a poor database design. Hive allows nested data types and arrays. For example, a region struct holds an array of items, and each year should be its own row and one column – OneCricketeer Mar 06 '18 at 15:05
  • @cricket_007 Please see note 1 and 2. The variable names are just made up for convenience. It is not what I have in the database. LOL. – Counter10000 Mar 06 '18 at 15:29
  • @cricket_007 FYI, the answer is posted below. I hope it helps. – Counter10000 Mar 06 '18 at 15:29

1 Answers1

8

Luckily I found a way to do so and I hope it can benefit many others who are looking for the same answer.

First of all, you need to run this setting in your Hive environment:

set hive.support.quoted.identifiers=none;

See solutions below

Exercise 1

select `a.*` from test_table;

Exercise 2

select `.*1$` from test_table;

Exercise 3

select `.*B.*` from test_table;
OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
Counter10000
  • 525
  • 1
  • 8
  • 25