HIVE测试用的记录语句

 create table testhello(name string,val string) ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t' ;
 
 
create EXTERNAL table IF NOT EXISTS   hivelog5 (val1 STRING)  partitioned by (ptdate string)   ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t' STORED AS TEXTFILE LOCATION '/data1/testdata/hive/warehouse/';
  ALTER TABLE   hivelog5  ADD PARTITION (ptdate='20140709') location '/data1/testdata/hive/warehouse/ptdate=20140709/hello.log' ; 
  
  load data local inpath '/home/hadoop/hello.log' overwrite into table  hivelog5  PARTITION(ptdate='20140708')  ;
  
  create table   testtable  (title string COMMENT '备注123') ;
  
  alter table test add partition (ptDate='20121214') location '/data1/testdata/hive/warehouse/20121214';
  
   alter table nlog add partition (ptDate='20120710') location '/data1/testdata/hive/warehouse/ptDate=20120710';
    create table   testhello  (name string ) ;
   create temporary function helloworld as 'com.xxx.hive.udf.HelloUDF';
  
   select helloworld(t.name) from testhello limit 10;
   
   drop temporary function helloworld;
   
 create table udftest(col1 string ,col2 string ,splitstr string,num int ,arrayobjlist array<INT>) ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ':' location '/data1/testdata/hive/udftest' ;
   
 load data local inpath '/home/hadoop/udftest.log' overwrite into table  udftest ;
 
hive>add jar file:///home/hadoop/HelloUDF.jar     
 
hive>create temporary function hello_fun as 'com.xxx.hive.udf.HelloUDF';
hive>select hello_fun(t.col1) from udftest t;
运算结果：
Total MapReduce CPU Time Spent: 1 seconds 470 msec
OK
HelloWorld 123123
HelloWorld 2222
Time taken: 19.979 seconds, Fetched: 2 row(s)




hive>create temporary function avgudaf_fun as 'com.xxx.hive.udf.AvgUDAF';
hive>select avgudaf_fun(t.num) from udftest t;
运算结果： 
Total MapReduce CPU Time Spent: 33 seconds 160 msec
OK
33.0
Time taken: 137.122 seconds, Fetched: 1 row(s)


hive>create temporary function explodeudtf_fun as 'com.xxx.hive.udf.ExplodeUDTF';
hive> select explodeudtf_fun(t.col1) from udftest t;
没测试


 
hive>create temporary function expudtf_fun as 'com.xxx.hive.udf.GenericUDTFPosExplode';
hive> select expudtf_fun(t.arrayobjlist) from udftest t;
运算结果：
Total MapReduce CPU Time Spent: 680 msec
OK
0       1
1       77
2       66
3       55
0       11
1       22
2       33
3       44
Time taken: 89.661 seconds, Fetched: 8 row(s)