vithalsamp

create or replace function udf_sql_bignum (float, float)
 returns float
stable
as $$
 select case when $1 > $2 then $1
 else $2
 end
$$ language sql;

--You can just call UDF as if you are calling built-in functions:

vithalsamp

create or replace function udf_domain (email varchar(max))
 returns varchar(max)
 stable as $$
 if not email:
 return None
 return email.split('@')[-1]
 $$ language plpythonu;
 
 --Call User defined function

vithalsamp

create or replace function udf_py_bignum(x float, y float)
 returns float
stable
as $$
 if x > y:
 return x
 return y
$$ language plpythonu;

--Call Created function

vithalsamp

$ beeline -u jdbc:hive2://192.168.0.41:10000/test_db -n username -p password
Connecting to jdbc:hive2://192.168.0.41:10000/test_db
Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.1000.2.5.0.0-1245 by Apache Hive

0: jdbc:hive2://192.168.0.41:10000/test_db> show tables;
+----------------------------------------+--+
| tab_name |

vithalsamp

beeline> !connect jdbc:hive2://
Connecting to jdbc:hive2://
Enter username for jdbc:hive2://: username
Enter password for jdbc:hive2://: *******
18/01/03 10:51:03 [main]: WARN service.CompositeService: Unable to create operation log root directory: /tmp/hive/operation_logs
Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://>

vithalsamp

$ beeline -u jdbc:hive2://
Connecting to jdbc:hive2://
18/01/03 10:47:45 [main]: WARN service.CompositeService: Unable to create operation log root directory: /tmp/hive/operation_logs
Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.1000.2.5.0.0-1245 by Apache Hive
0: jdbc:hive2://> 

0: jdbc:hive2://> show tables;

vithalsamp

hive> create table t1 (x int, s string);
hive> insert into t1 values (1, "one"), (3, "three"), (2, "two"), (1, "one"),(1, "four"), (3, "five"), (2, "six"), (7, "seven");
hive> SELECT x,count(*) as num_of_rows, concat_ws(',' , collect_set(s)) as group_con from t1 group by x;
Query ID = impadmin_20180104103104_98e64351-d108-4c1b-97ec-ba5e5860aeec
Total jobs = 1
…
OK
x num_of_rows group_con
1    3                 one,four
2    2                 two,six

vithalsamp

$ beeline -u jdbc:hive2://192.168.0.41:10000/test_db -n username -p password --hivevar dep_id=200 -f test_file.sql
Connecting to jdbc:hive2://192.168.0.41:10000/test_db
Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://192.168.0.41:10000/test_db_> select * from test_db.var_sub_demo
0: jdbc:hive2://192.168.0.41:10000/test_db_> where dept = ${dep_id}
0: jdbc:hive2://192.168.0.41:10000/test_db_> +------------------+--------------------+--------------------+--+
| var_sub_demo.id | var_sub_demo.name | var_sub_demo.dept |
+------------------+--------------------+--------------------+--+

vithalsamp

$ beeline -u jdbc:hive2://192.168.0.41:10000/test_db -n user1 -p password --outputformat=dsv --delimiterForDSV=',' -e "select * from t1";
Connecting to jdbc:hive2://192.168.0.41:10000/test_db
Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
t1.x,t1.s
1,one
3,three
2,two
1,one

vithalsamp

$ beeline -u jdbc:hive2://192.168.0.41:10000/test_db -n user1 -p password --outputformat=csv2 -e "select * from t1";
Connecting to jdbc:hive2://192.168.0.41:10000/test_db
Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
t1.x,t1.s
1,one
3,three
2,two
1,one