// Here we are going to play with String and Date Functions in Hive
hive> create table cust as select customer_fname as firstname, customer_lname as lastname, customer_city as city from ohm.customers;
hive> select * from cust limit 10;
OK
Richard Hernandez Brownsville
Mary Barrett Littleton
Ann Smith Caguas
Mary Jones San Marcos
Robert Hudson Caguas
Mary Smith Passaic
Melissa Wilcox Caguas
Megan Smith Lawrence
Mary Perez Caguas
Melissa Smith Stafford
hive> describe cust;
OK
firstname string
lastname string
city string
Time taken: 0.041 seconds, Fetched: 3 row(s)
customer_city string
Time taken: 0.06 seconds, Fetched: 3 row(s)
hive> alter table cust change customer_city city string;
OK
Time taken: 0.308 seconds
// Concat function
hive> select concat(firstname , "-", lastname, "-" , city ) from cust limit 2;
OK
Richard-Hernandez-Brownsville
Mary-Barrett-Littleton
hive> select concat(firstname , "|", lastname, "|" , city ) from cust limit 2;
OK
Richard|Hernandez|Brownsville
Mary|Barrett|Littleton
hive> select concat(firstname , "\t", lastname, "\t" , city ) from cust limit 2;
OK
Richard Hernandez Brownsville
Mary Barrett Littleton
hive> select concat_ws("\t",firstname,lastname,city ) from cust limit 2;
OK
Richard Hernandez Brownsville
Mary Barrett Littleton
Time taken: 0.138 seconds, Fetched: 2 row(s)
hive> select concat_ws("$",firstname,lastname,city ) from cust limit 2;
OK
Richard$Hernandez$Brownsville
Mary$Barrett$Littleton
Time taken: 0.057 seconds, Fetched: 2 row(s)
hive> select concat_ws("|",firstname,lastname,city ) from cust limit 2;
OK
Richard|Hernandez|Brownsville
Mary|Barrett|Littleton
Time taken: 0.04 seconds, Fetched: 2 row(s)
hive> select concat(firstname , "\t", lastname, "\t" , city ) from cust limit 10;
OK
Richard Hernandez Brownsville
Mary Barrett Littleton
Ann Smith Caguas
Mary Jones San Marcos
Robert Hudson Caguas
Mary Smith Passaic
Melissa Wilcox Caguas
Megan Smith Lawrence
Mary Perez Caguas
Melissa Smith Stafford
hive> select concat(firstname , "|", lastname, "|" , city ) from cust limit 10;
OK
Richard|Hernandez|Brownsville
Mary|Barrett|Littleton
Ann|Smith|Caguas
Mary|Jones|San Marcos
Robert|Hudson|Caguas
Mary|Smith|Passaic
Melissa|Wilcox|Caguas
Megan|Smith|Lawrence
Mary|Perez|Caguas
Melissa|Smith|Stafford
hive> select length(firstname) as Lenn , firstname from cust where length(firstname) > 8 limit 5;
OK
11 Christopher
9 Katherine
9 Stephanie
9 Katherine
9 Alexander
hive> select length("Raja Ram Mohan Rai"), "Raja Ram Mohan Rai";
OK
18 Raja Ram Mohan Rai
hive> select concat (length("S/o. Mahalakshmi") ,". ", "S/o. Mahalakshmi");
OK
16. S/o. Mahalakshmi
hive> select concat_ws(":","Kadavul","Amaithu","Vaitha","Medai");
OK
Kadavul:Amaithu:Vaitha:Medai
hive> select concat_ws("|","Kadavul","Amaithu","Vaitha","Medai");
OK
Kadavul|Amaithu|Vaitha|Medai
hive> select concat(firstname , " " , lastname) from cust where length(firstname) > 10 limit 5;
OK
Christopher Smith
Christopher Prince
Christopher Smith
Christopher Curtis
Christopher Johnson
Time taken: 0.044 seconds, Fetched: 5 row(s)
hive> select concat(firstname , " " , lastname) from cust where length(concat(firstname , " " , lastname)) > 20 limit 5;
OK
Christopher Richardson
Christopher Villarreal
Christopher Alexander
Time taken: 0.094 seconds, Fetched: 3 row(s)
/// Case conversion
hive> select UPPER(firstname), LOWER(lastname), upper(city) from cust limit 2;
OK
RICHARD hernandez BROWNSVILLE
MARY barrett LITTLETON
Time taken: 0.343 seconds, Fetched: 2 row(s)
/Padding Left, Right
hive> select lpad(firstname,12,"*"), rpad(lastname,12,"*") from cust limit 2;
OK
*****Richard Hernandez***
********Mary Barrett*****
hive> select upper("I love India"), lower("I love India");
OK
I LOVE INDIA i love india
Time taken: 0.031 seconds, Fetched: 1 row(s)
// Split function
hive> select split("Arun Raja Kama Raj"," ");
OK
["Arun","Raja","Kama","Raj"]
hive> select substr("Raja Raja Chozhan",11);
OK
Chozhan
hive> select substr(firstname,1,3), " ", firstname from cust limit 5;
OK
Ric Richard
Mar Mary
Ann Ann
Mar Mary
Rob Robert
hive> select regexp_replace("Raja Raja Chozhan","Raja","King");
OK
King King Chozhan
hive> select firstname ," ", regexp_replace(firstname,"Mary","Laila"), " " from cust limit 5;
OK
Richard Richard
Mary Laila
Ann Ann
Mary Laila
Robert Robert
hive> select current_date(), year(current_date()), month(current_date()) , date(current_date()), day(current_date()) ;
OK
2020-08-07 2020 8 2020-08-07 7
Time taken: 0.04 seconds, Fetched: 1 row(s)
hive> select current_timestamp(), hour(current_timestamp()), minute(current_timestamp()), second(current_timestamp());
OK
2020-08-07 08:32:41.383 8 32 41
hive> select datediff(current_date(), "1976-04-20");
OK
16180
hive> select datediff(current_date(), "2020-01-01");
OK
219
hive> select date_add(current_date(),100), date_sub(current_date(),219);
OK
2020-11-15 2020-01-01
hive> select months_between(current_date(),"2020-01-01");
OK
7.19354839
hive> select add_months("2020-01-01",7);
OK
2020-08-01
hive> select current_date(), current_timestamp();
OK
2020-08-07 2020-08-07 08:38:28.5
hive> select current_date(), current_timestamp(), unix_timestamp();
unix_timestamp(void) is deprecated. Use current_timestamp instead.
OK
2020-08-07 2020-08-07 08:38:53.775 1596814733
hive> select unix_timestamp(current_timestamp);
OK
1596814766
No comments:
Post a Comment