1

I'm trying to select specific fields in a hive table and save the result in a text file. The main problem is that, for one of the fields in the table, I want to add prefix in all the rows of a particular column. Currently, all rows of that column in the hive table are in the form 00yyhhgdbdbd. Now, I want to write my select statement and prefix this row with AB_ . And in my text file, it will be of the form AB_00yyhhgdbdbd). How can I handle this Please? 

AB_00yyhhgdbdbd
AB_00yyhhgdbdbd
AB_00yyhhgdbdbd

Any functions to handle this in hive?

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
Oye
  • 57
  • 2
  • 7

2 Answers2

0

SQL supports column aliases in SELECT statements. For example, say there's a table "people" with the columns "FirstName", "LastName", and "BornOn", and you want to combine the first and last name into a column called "FullName", and don't really like the name "BornOn" so you want to call that "Birthday", you could do that like this:

SELECT
  FirstName + LastName AS FullName,
  BornOn AS Birthday
FROM people
mbrewster
  • 9
  • 2
  • Thanks for that @mbrewster. What I'm actually trying to achieve goes way beyond aliases. In hive, I want to prefix an id column with AB_ so that all the rows will be of that form. I.e, If the first row in the column is previously ooyyhh, I want to prefix it with AB_00yyhh so that all the rows of that column will be in that form and save the output in a text file. I'm trying to achieve that in hive. – Oye Jun 05 '19 at 07:59
0

Use concat() function to concatenate with AB_ in the select

select concat('AB_', id) from your_table;

As of Hive 2.2.0. you can use || operator instead of concat:

select 'AB_'||id from your_table;
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Thanks @leftjoin. select concat('AB_', id) from your_table; works. Really appreciate that. – Oye Jun 05 '19 at 11:12