{"id":1424,"date":"2017-12-06T17:51:58","date_gmt":"2017-12-06T22:51:58","guid":{"rendered":"http:\/\/www.xavignu.com\/?p=1424"},"modified":"2017-12-06T17:53:01","modified_gmt":"2017-12-06T22:53:01","slug":"playing-with-functions-in-hive","status":"publish","type":"post","link":"https:\/\/www.xavignu.com\/?p=1424","title":{"rendered":"Playing with functions in hive"},"content":{"rendered":"

Apache Hive<\/a> has built in functions which can be listed with
\n[sql]
\nSHOW FUNCTIONS;
\n[\/sql]
\nto play with concat we will run the following script.
\n[sql]
\n— Use testdb
\nuse testdb;
\n— describe concat function
\nDESC FUNCTION concat;
\n— describe table mytable2
\nDESC mytable2;
\n— Perform select query uniting fname and lname
\nSELECT CONCAT(fname,’ ‘,lname) FROM mytable2;
\n[\/sql]
\nWe can execute with beeline or hive. We will use beeline.<\/p>\n

beeline -u jdbc:hive2:\/\/localhost:10000 -f Documents\/concat.hql --verbose=false --showWarnings=false\r\nscan complete in 8ms\r\nConnecting to jdbc:hive2:\/\/localhost:10000\r\nConnected to: Apache Hive (version 0.13.1-cdh5.2.0)\r\nDriver: Hive JDBC (version 0.13.1-cdh5.2.0)\r\nTransaction isolation: TRANSACTION_REPEATABLE_READ\r\n0: jdbc:hive2:\/\/localhost:10000> use testdb;\r\nNo rows affected (0.104 seconds)\r\n0: jdbc:hive2:\/\/localhost:10000> -- Describe concat function\r\n0: jdbc:hive2:\/\/localhost:10000> DESC FUNCTION concat;\r\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+\r\n|                                                                                          tab_name                                                                                           |\r\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+\r\n| concat(str1, str2, ... strN) - returns the concatenation of str1, str2, ... strN or concat(bin1, bin2, ... binN) - returns the concatenation of bytes in binary data  bin1, bin2, ... binN  |\r\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+\r\n1 row selected (0.162 seconds)\r\n0: jdbc:hive2:\/\/localhost:10000> \r\n0: jdbc:hive2:\/\/localhost:10000> DESC mytable2;\r\n+-----------+------------+----------+--+\r\n| col_name  | data_type  | comment  |\r\n+-----------+------------+----------+--+\r\n| id        | int        |          |\r\n| lname     | string     |          |\r\n| fname     | string     |          |\r\n+-----------+------------+----------+--+\r\n3 rows selected (0.133 seconds)\r\n0: jdbc:hive2:\/\/localhost:10000> \r\n0: jdbc:hive2:\/\/localhost:10000> SELECT CONCAT(fname,' ',lname) FROM mytable2;\r\n+--------------------+--+\r\n|        _c0         |\r\n+--------------------+--+\r\n| John Doe           |\r\n| William Lancaster  |\r\n| Burp Gentoo        |\r\n+--------------------+--+\r\n3 rows selected (18.848 seconds)\r\n0: jdbc:hive2:\/\/localhost:10000> \r\nClosing: 0: jdbc:hive2:\/\/localhost:10000\r\n<\/pre>\n
We can also play with functions from inside hive cli as shown below with the sqrt function.<\/p>\n
\r\nhive> DESC function sqrt;\r\nOK\r\nsqrt(x) - returns the square root of x\r\nTime taken: 0.018 seconds, Fetched: 1 row(s)\r\nhive> SELECT SQRT(64);        \r\nTotal jobs = 1\r\nLaunching Job 1 out of 1\r\nNumber of reduce tasks is set to 0 since there's no reduce operator\r\nStarting Job = job_201710131004_0373, Tracking URL = http:\/\/localhost:50030\/jobdetails.jsp?jobid=job_201710131004_0373\r\nKill Command = \/usr\/lib\/hadoop\/bin\/hadoop job  -kill job_201710131004_0373\r\nHadoop job information for Stage-1: number of mappers: 1; number of reducers: 0\r\n2017-12-06 12:33:16,450 Stage-1 map = 0%,  reduce = 0%\r\n2017-12-06 12:33:23,476 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.58 sec\r\n2017-12-06 12:33:28,497 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.58 sec\r\nMapReduce Total cumulative CPU time: 1 seconds 580 msec\r\nEnded Job = job_201710131004_0373\r\nMapReduce Jobs Launched: \r\nStage-Stage-1: Map: 1   Cumulative CPU: 1.58 sec   HDFS Read: 273 HDFS Write: 4 SUCCESS\r\nTotal MapReduce CPU Time Spent: 1 seconds 580 msec\r\nOK\r\n8.0\r\nTime taken: 18.894 seconds, Fetched: 1 row(s)\r\nhive> \r\n<\/pre>\n
More info about Hive functions here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Apache Hive has built in functions which can be listed with [sql] SHOW FUNCTIONS; [\/sql] to play with concat we will run the following script. [sql] — Use testdb use testdb; — describe concat function DESC FUNCTION concat; — describe table mytable2 DESC mytable2; — Perform select query uniting fname and lname SELECT CONCAT(fname,’ ‘,lname) […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[74],"tags":[20,84],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/pTQgt-mY","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts\/1424"}],"collection":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1424"}],"version-history":[{"count":6,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts\/1424\/revisions"}],"predecessor-version":[{"id":1430,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts\/1424\/revisions\/1430"}],"wp:attachment":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1424"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1424"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1424"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}