use test;
--使用${env:varname}的方法读取shell中export的变量
select * from student limit ${env:g_limit_count};
--使用${system:varname}的方法读取系统的变量
select ${system:HOME} as my_home from student;
use test;
add jar /home/users/crazyant/tmp/hive-udf-test-0.0.1-SNAPSHOT.jar;
CREATE TEMPORARY FUNCTION datatoquarter as 'myudf.DateToQuarter.';
select sname, datatoquarter(enter_date) from student;
use test;
DROP TABLE IF EXISTS table1;
create table table1(
student_no bigint comment '学号',
student_name string comment '姓名'
)
COMMENT 'test 学生信息'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
DROP TABLE IF EXISTS table2;
create table table2(
student_no bigint comment '学号',
class_no bigint comment '课程号'
)
COMMENT 'test 学生选课信息'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
load data local inpath 'data_table1.txt' overwrite into table table1;
load data local inpath 'data_table2.txt' overwrite into table table2;
测试数据为：
hive left join测试数据
测试1：left join
语句：
select * from table1 left outer join table2 on(table1.student_no=table2.student_no);
结果：
FAILED: Parse Error: line 1:22 cannot recognize input near ‘left’ ‘join’ ‘table2’ in join type specifier
我用的HIVE版本是0.8，不支持直接的left join写法；
测试2：left outer join
语句：
select * from table1 left outer join table2 on(table1.student_no=table2.student_no);
create temporary function row_number as 'com.blue.hive.udf.RowNumber';select subject,score,student from
(select subject,score,student from score where dt='2012' order by subject,socre desc) order_scorewhere row_number(subject) <= 100;
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
explain select subject,score,student from
(select subject,score,student from score where dt='2012' order by subject,socre desc) order_scorewhere row_number(subject) <= 100;
explain不会执行mapreduce计算，只会显示执行计划。
只要row_number函数在reducer端执行，除了使用order by全局排序配合，也可以使用distribute by + sort by。distribute by可以让相同科目的成绩记录发送到同一个reducer,而sort by可以在reducer端对记录做排序。
而使用order by全局排序，只有一个reducer，未能充分利用资源，相比之下，distribute by + sort by在这里更有性能优势，可以在多个reducer做排序，再做row_number的计算。
sql如下：
create temporary function row_number as 'com.blue.hive.udf.RowNumber';select subject,score,student from
(select subject,score,student from score where dt='2012' distribute by subject sort by subject asc, socre desc) order_scorewhere row_number(subject) <= 100;
如果成绩有学院字段college，要找出学院里,单科成绩前一百的学生，解决方法如下:
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
explain select college,subject,score,student from
(select college,subject,score,student from score where dt='2012' order by college asc,subject asc,socre desc) order_scorewhere row_number(college,subject) <= 100;
如果成绩有学院字段college，要找出学院里,总成绩前一百的学生，解决方法如下:
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
explain select college,totalscore,student from
(select college,student,sum(score) as totalscore from score where dt='2012' group by college,student order by college asc,totalscore desc) order_scorewhere row_number(college) <= 100;
package com.blue.hive.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
public class RowNumber extends UDF {
private static int MAX_VALUE = 50;
private static String comparedColumn[] = new String[MAX_VALUE];
private static int rowNum = 1;
public int evaluate(Object... args) {
String columnValue[] = new String[args.length];
for (int i = 0; i < args.length; i++) {
columnValue[i] = args[i].toString();
}
if (rowNum == 1) {
for (int i = 0; i < columnValue.length; i++)
comparedColumn[i] = columnValue[i];
}
for (int i = 0; i < columnValue.length; i++) {
if (!comparedColumn[i].equals(columnValue[i])) {
for (int j = 0; j < columnValue.length; j++) {
comparedColumn[j] = columnValue[j];
}
rowNum = 1;
return rowNum++;
}
}
return rowNum++;
}
}
编译后，打包成一个jar包，如/usr/local/hive/udf/blueudf.jar
然后在hive shell下使用，如下:
add jar /usr/local/hive/udf/blueudf.jar;create temporary function row_number as 'com.blue.hive.udf.RowNumber';select subject,score,student from
(select subject,score,student from score where dt='2012' order by subject,socre desc) order_scorewhere row_number(subject) <= 100;
