hive进阶学习

拼搏现实的明天。 2022-01-14 14:35 283阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

### 创建数据库 ###

create database db_hive_01 ;
     create database if not exists db_hive_02 ;
     #指定数据的存贮位置
     create database if not exists db_hive_03 location '/user/hive/warehouse/db_hive_03.db' ;
     #http://hadoop.jianxin.com:50070/explorer.html#/user/hive/warehouse/db_hive_03.db浏览器打开可看到创建的数据库

### 查看数据库 ###

show databases ;
    #查看这一类数据库
    show databases like 'db_hive*' ;
    desc database db_hive_03;
    #  db_name comment location        parameters  
    #  db_hive_03              hdfs://hadoop.jianxin.com:9000/user/hive/warehouse/db_hive_03.db        root
    
    desc database extended db_hive_03 ;#查看的信息更加全面

### hive数据表的创建 ###

create table IF NOT EXISTS default.jianxin_log_20170413(
    ip string COMMENT 'remote ip address' ,
    user string ,
    req_url string COMMENT 'user request url')
    COMMENT 'Jianxin Web Access Logs'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
    STORED AS TEXTFILE ;
    
    create table IF NOT EXISTS default.jianxin_log_20170415
    AS select ip,req_url from default.jianxin_log_20170413 ;
    
    create table if not exists default.dept_cats
    as
    select * from dept ;

### hive表的类型 ###

在Hive中表的类型 管理表（内部表） 托管表（外部表）  
内部表数据默认存储在/user/hive/warehouse  
可以通过location进行执行  
删除表的时候会删除表数据及元数据

外部表创建表的时候一般必须指定路径  
外部表删除表的时候不会删除表数据，只会删除元数据

*  创建外部表

create EXTERNAL table IF NOT EXISTS default.emp_ext2(
    empno int,
    ename string,
    job string,
    mgr int,
    hiredate string,
    sal double,
    comm double,
    deptno int
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    location '/user/jianxin/hive/warehouse/emp_ext2';

*  创建分区表

create EXTERNAL table IF NOT EXISTS default.emp_partition4(
    empno int,
    ename string,
    job string,
    mgr int,
    hiredate string,
    sal double,
    comm double,
    deptno int
    )
    partitioned by (month string,day string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
    location '/user/jianxin/hive/warehouse/emp_partition4';

*  导入分区表数据

LOAD DATA [LOCAL] INPATH 'filepath' 
    [OVERWRITE] INTO TABLE tablename 
    [PARTITION (partcol1=val1, partcol2=val2 ...)]
    
    #第一种
    load data local inpath '/opt/modules/data/emp.txt' into table default.emp_partition4 partition (month='201804',day='12') ;
    load data local inpath '/opt/modules/data/emp.txt' into table default.emp_partition4 partition (month='201804',day='11') ;
    hive (default)> msck repair table emp_partition4 ;
    #第二种
    dfs -mkdir -p /user/jianxin/hive/warehouse/emp_partition4/month=201804/day=03 ;
    dfs -put /opt/modules/data/emp.txt /user/jianxin/hive/warehouse/emp_partition4/month=201804/day=03 ;
    hive (default)> msck repair table emp_partition4 ;
    hive (default)>alter table default.emp_partition4(month='201804',day='03')

*  查询分区表数据

select * from emp_partition where month = '201804' and day = '12' ;
    select * from emp_partition where month = '201804';

*  展示表的分区

show partitions emp_partition4 ;

### 导入数据 ###

load data [local] inpath 'filepath' [overwrite] into table tablename [partition (partcol1=val1,...)];
    
    - 原始文件存储的位置
    	- 本地 local
    	- hdfs
    - 对表的数据是否覆盖
    	- 覆盖 overwrite
    	- 追加
    - 分区表加载，特殊性(修复分区表)
    	partition (partcol1=val1,...)
    load data local inpath '/opt/modules/data/emp.txt' overwrite into table   emp_ext2;

### hive数据导出 ###

#导出的路径指的是hdfs中的路径，不是本地路径
    
    EXPORT TABLE default.emp TO '/user/jianxin/hive/export/emp_exp' ;
    
    hive (default)> export table emp to '/user/jianxin/hive/export/emp_exp'
                  > ;
    Copying data from file:/tmp/root/hive_2018-04-09_21-16-02_852_1347531230491859372-1/-local-10000/_metadata
    Copying file: file:/tmp/root/hive_2018-04-09_21-16-02_852_1347531230491859372-1/-local-10000/_metadata
    Copying data from hdfs://hadoop.jianxin.com:9000/user/hive/warehouse/emp
    Copying file: hdfs://hadoop.jianxin.com:9000/user/hive/warehouse/emp/emp.txt
    
    
    hive (default)> dfs -ls -R  /user/jianxin/
                  > ;
    drwxr-xr-x   - root supergroup          0 2018-04-09 21:16 /user/jianxin/hive
    drwxr-xr-x   - root supergroup          0 2018-04-09 21:16 /user/jianxin/hive/export
    drwxr-xr-x   - root supergroup          0 2018-04-09 21:16 /user/jianxin/hive/export/emp_exp
    -rw-r--r--   1 root supergroup       1580 2018-04-09 21:16 /user/jianxin/hive/export/emp_exp/_metadata
    drwxr-xr-x   - root supergroup          0 2018-04-09 21:16 /user/jianxin/hive/export/emp_exp/data
    -rw-r--r--   1 root supergroup        656 2018-04-09 21:16 /user/jianxin/hive/export/emp_exp/data/emp.txt

### hive常用的操作 ###

*  order by  
    对全局数据的一个排序，仅仅只有一个reduce

select * from emp order by empno desc ;

*  sort by  
    对每一个reduce内部数据进行排序的，全局结果集来说不是排序

set mapreduce.job.reduces= 3;  
    select * from emp sort by empno asc ;  
    insert overwrite local directory '/opt/datas/sortby-res' select * from emp sort by empno asc ;

*  distribute by  
    分区partition  
    类似于MapReduce中分区partition,对数据进行分区，结合sort by进行使用

insert overwrite local directory '/opt/datas/distby-res' select * from emp distribute by deptno sort by empno asc ;

*  cluster by  
    当distribute by和sort by 字段相同时，可以使用cluster by ;

insert overwrite local directory '/opt/datas/cluster-res' select * from emp cluster by empno asc ;

### hive UDF编程 ###

继承org.apache.hadoop.hive.ql.exec.UDF 需要实现evaluate函数，函数支持重载。（函数或者方法有同样的名称，但是参数列表不同的情形）

package com.jianxin.senior.hive.udf;
    import org.apache.hadoop.hive.ql.exec.UDF;
    import org.apache.hadoop.io.Text;
    
    /**
     * 1. Implement one or more methods named
     * "evaluate" which will be called by Hive.
     * 
     * 2. "evaluate" should never be a void method. 
     * However it can return "null" if needed.
     * @author XuanYu
     *
     */
    public class LowerUDF extends UDF {
    	
    	public Text evaluate(Text str){
    		// validate 
    		if(null == str.toString()){
    			return null ;
    		}
    		// lower
    		return new Text (str.toString().toLowerCase())  ;
    	}
    	
    	public static void main(String[] args) {
    		System.out.println(new LowerUDF().evaluate(new Text("HIVE")));
    	}
    
    }
    
    
    add jar /opt/datas/hiveudf.jar ;
    create temporary function my_lower as "com.jianxin.senior.hive.udf.LowerUDF" ;
    select ename, my_lower(ename) lowername from emp limit 5 ;
    
    dfs -put  /opt/datas/hiveudf.jar /user/jianxin/hive/jars/hiveudf.jar
    CREATE FUNCTION self_lower AS 'com.jianxin.senior.hive.udf.LowerUDF' USING JAR 'hdfs://hadoop.jianxin.com:9000/user/beifeng/hive/jars/hiveudf.jar';
    select ename, self_lower(ename) lowername from emp limit 5 ;

### hive展示函数 ###

show functions;
    #查看某个函数的功能
    describe function year;
    #结果如下  
    tab_name
    year(date) - Returns the year of date
    Time taken: 0.031 seconds, Fetched: 1 row(s)
    
    describe  function extended year;
    #结果如下  
    tab_name
    year(date) - Returns the year of date
    date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'.
    Example:
       > SELECT year('2009-30-07', 1) FROM src LIMIT 1;
      2009
    Time taken: 0.057 seconds, Fetched: 5 row(s)

转载于:https://my.oschina.net/jiansin/blog/1795914

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/1127ce0b4f734c59aea42bc6ecc96575.png