Sunday, March 11, 2018

Regexp_extract example syntax explained in Hive

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select '${hivevar:str1}';
+-----------------------+--+
|          _c0          |
+-----------------------+--+
| adfsdf1231asdf123123  |
+-----------------------+--+
1 row selected (0.115 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select regexp_extract('${hivevar:str1}','(\\d+)\\D*(\\d+)',1) part1,regexp_extract('${hivevar:str1}','(\\d+)\\D*(\\d+)',2) part2;
+--------+---------+--+
| part1  |  part2  |
+--------+---------+--+
| 1231   | 123123  |
+--------+---------+--+
1 row selected (0.067 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> desc function regexp_extract;
+----------------------------------------------------+--+
|                      tab_name                      |
+----------------------------------------------------+--+
| regexp_extract(str, regexp[, idx]) - extracts a group that matches regexp |
+----------------------------------------------------+--+
1 row selected (0.029 seconds)