hive的基本使用及HQL

 

=====================================================================

1、 创建库：create database if not exists mydb;

        创建库的时候带注释

            create database if not exists dbname comment 'create my db named dbname';

        创建带属性的库

            create database if not exists dbname with dbproperties ('a'='aaa','b'='bbb');

            create database if not exists myhive with dbproperties ('a'='aaa','b'='bbb');

2、 查看库：show databases;

        显示数据库的详细属性信息语法：

            desc database [extended] dbname;

        示例：desc database extended myhive;

        查看当前正在使用的库：

            select current_database();

        查看创建库的详细语句

            show create database mydb;

3、 切换数据库：use mydb;

4、 创建表：create table if not exists t_user(id string, name string)

或 create table t_user(id string, name string) row format delimited fields terminated by ',';

或CTAS    CREATE TABLE mytest AS SELECT name, age FROM test;

或 EXTERNAL 创建外部表，LIKE：允许用户复制现有的表结构，但是不复制数据。PARTITIONED BY 分区。COMMENT：可以为表与字段增加描述。数据需要压缩，使用 STORED AS SEQUENCEFILE。CLUSTERED BY 分桶。LOCATION：指定数据文件存放的 HDFS 目录，不管内部表还是外表，都可以指定。不指定就在默认的仓库路径。

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 

    [(col_name data_type [COMMENT col_comment], ...)] 

    [COMMENT table_comment] 

    [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 

  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]     [ROW FORMAT row_format] 

    [STORED AS file_format] 

    [LOCATION hdfs_path]

 

创建分区外部表：

create external table student_ptn(id int,name string) 

partitioned by (city string) 

row format delimited fields terminated by "," location "/hive/student_ptn/";

添加分区（就是在分区表下面创建一个新的文件夹）

alter table student_ptn add partition(city="beijing");

alter table student_ptn add partition(city="tianjin");

如果某张表是分区表，那么每个分区的定义，就表现为这张表的数据存储目录下的一个子目录

如果是分区表，那么数据文件一定要存储在某个分区中，而不能直接存储在表中。

查看分区：

show partitions student_ptn;

清空分区表数据，但是分区定义还是存在的

清空表数据： truncate table student_ptn_age;

在hive的使用中，分区和普通字段没有任何区别，是完全一样的。只是在定义的时候有区别

创建分桶表

create table student_bck(id int ,name string) 

clustered by ( id sorted by (id asc, name desc) into 4 buckets

row format delimited fields terminated by ",";

注意clustered by只能是表字段的一个或者一部分

可以使用sorted by (id asc, name desc)按照两个字段进行联合排序（也可以不用排序）

into 4 buckets  就是分为4个桶（按照字段id进行分桶）

查看表结构 desc formatted student_bck; 可以看到如下

使用CTAS创建表

作用：就是从一个查询SQL的结果来创建一个表进行存储

create table student_ctas as ....

注意创建的表不用指定字段(后面select语句查询出来的是什么字段和类型就用什么字段和类型），只需要指定表名就行了。as后面的....是select语句

create table student_ctas as select * from student where id <10;

CTAS的原理：第一步把数据计算出来存储在临时目录里面。第二创建表之后再把数据移动到这里面去。

复制表结构

create table student_copy like student;

如果在table的前面没有加external关键字（指的是student_copy表），那么复制出来的新表，无论如何都是内部表

如果在table的前面有加external关键字，那么复制出来的新表，无论如何都是外部表

create external table student_copy_ext like student;

 

示例：

CREATE TABLE page_view (viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRINGCOMMENT 'IP Address of the User') 

COMMENT 'This is the page view table' 

PARTITIONED BY(dt STRING, country STRING) 

CLUSTERED BY(userid) SORTED BY(viewTime) INTO 31 BUCKETS 

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 

COLLECTION ITEMS TERMINATED BY ','

MAP KEYS TERMINATED BY ':'

LINES TERMINATED BY '\n'

STORED AS TEXTFILE

LOCATION '/myhive';

 

5、 查看表列表：

show tables;

show tables in 库名;

select current_database(); 查看当前正在使用的数据库名字

show tables like 'stu*';  查看以stu开头的表

show create table student;  查看建表语句

 

6、 插入数据：

        insert into table t_user values ('1','huangbo'), ('2','xuzheng'), ('3','wangbaoqiang');

        insert into student (id,name,sex,age,department) values (111,'ss','F',12,'NN');

    注意:插入单条数据的时候首先启动MR程序。最终把输入数据当做是一条记录最后生成一条记录，然后把这条记录放在hive那张表的数据文件路径下面

插入数据的时候会生成一张临时表。不要退出cli的情况下show tables;会看到这个临时表。但是退出cli之后再看就看不到了

原理：首先创建一张临时表来保存insert语句的结果，然后把这张临时表的数据文件移动到指定的表中；

    把一张表的查询结果插入到另一张表中：

    insert into table student_c select * from student where age <= 18;

    导入数据到分区表中的分区，这个分区可以不存在，会自动创建

    insert into table student_ptn partition (age=18) select * from student where age <=18;

    insert into table student_ptn partition (age=19) select * from student where age =18;

    insert into table student_ptn partition (age=20) select * from student where age >=18;

    所以上面的写法不好需要采用多重插入

    多重插入：注意insert语句不用再写“from 表名”了

     使用多重插入来降低任务的复杂度：主要减少就是源表的数据扫描次数

     from mingxing

     insert into table student_ptn partition (age=18) select * where age <=18;

     insert into table student_ptn partition (age=19) select *  where age =19;

     insert into table student_ptn partition (age=20) select *  where age >=20;

    问题引深：现有很多不同年龄的数据，怎么插入？一点点写？需要用到动态分区插入

    动态数据插入：

    需求：遇见一个不同的age字段的值就动态的创建一个分区

     如果我们不需要设置静态分区字段就需要使用下面橘黄色字体的代码；

    student_ptn_dpt表有四个字段 ,student表有5个字段，但是没关系，最后的student_ptn_dpt表会有5个字段。那个倒数第二个字段将会是NULL

    需要先执行以下命令改成非严格模式：set hive.exec.dynamic.partition.mode=nonstrict 在执行下面的代码,注意动态分区字段要放在查询结果字段的最后,为什么这么设计？因为防止用户使用不当造成很多动态分区插入，这样是非常影响机器性能的。当你确保万无一失且不需要静态分区字段的时候，就使用下面橘黄色的代码

    insert into table student_ptn_dpt partition(age) select id,name,department,sex,age from student;

 如果一张分区表有多个分区字段：在默认的严格模式strict下执行：那么在进行动态分区插入的时候一定要有一列是静态分区，在插入的时候要保证第一个分区是静态的，剩下的是动态的就可以了；

create table student_ptn_dpt(id int ,name string,sex string)

partitioned by (department string,age int )

row format delimited fields terminated by ",";

insert into table student_ptn_dpt partition(department='cs' , age) select id,name,age,department,sex from student;

如果往分区表中插入数据，千万不要使用load的方式，除非在非常确定的情况下。

7、 查询数据：select * from t_user;

8、 导入数据：

a) 导入 HDFS 数据：load data inpath '/user.txt' into table t_user;

注意从HDFS往hive表中导入数据后的数据文件在原来的位置是不在的。

还有一种最暴力的方式就是直接把数据文件放在hive的那张表的数据存储目录下

b) 导入本地数据：load data local inpath '/home/hadoop/user.txt' into table t_user;

load data local inpath '/root/hivedata/mingxing.txt' overwrite into table mytable3 partition(sex='boy');

    user.txt 的数据为：

        4,liudehua

        5,wuyanzu

        6,liangchaowei

再次查询数据：select * from t_user;

9、导出数据

    导出数据到本地：insert overwrite local directory '/home/hadoop/student.txt' select * from studentss;

   导出数据到 HDFS：insert overwrite directory '/student' select * from studentss where age >= 20;

        insert overwrite directory 'hdfs://hadoop02:9000/user/hive/warehouse/mystudent' select *from studentss;

    分桶和排序的组合操作，对 id 进行分桶，对 age，id 进行降序排序：

        insert overwrite directory '/root/outputdata6' select * from mingxing2 distribute by id sortby age desc, id desc;

    分桶操作，按照 id 分桶，并且按照 id 排序：

        insert overwrite directory '/root/outputdata3' select * from mingxing2 cluster by id;

10、查看表结构
desc student;
desc extended student;

desc formatted student;

11、删除库操作

    drop database dbname;

    drop database if exists dbname;

    drop database if exists dbname cascade;     //删除包含表的数据库

    drop database if exists myhive ==== drop database if exists myhive restrict

12、修改表

    重命名表语法结构：

        ALTER TABLE table_name RENAME TO new_table_name;

  修改表属性语法结构：

        ALTER TABLE table_name SET TBLPROPERTIES table_properties;

        table_properties: (property_name = property_value, property_name = property_value, ... )

    实例：

        ALTER TABLE table_name SET TBLPROPERTIES ('comment' = 'my new students table');

    更改列分隔符：

        ALTER TABLE student SET SERDEPROPERTIES ('field.delim' = '-');

    增加/删除/改变/替换列:

        ALTER TABLE student ADD COLUMNS (col_spec[, col_spec ...])   增加student表一个字段

       案例： alter table student add colums (sex string ,age int );

       ALTER TABLE name CHANGE c_name new_name new_type [FIRST|AFTER c_name]  修改student表的name字段，名称改为new_name 类型改成string

        案例：alter table student change name new_name string;

        删除一个字段：不支持

        ALTER TABLE student REPLACE COLUMNS (col_spec[, col_spec ...])  替换student表中所有的字段为下面括号所示

        案例：alter table student replace columns(id int,name string);

        注意：如果有需求是更改某个表的所有字段的定义，那么一定要注意类型是否匹配，如果实hive-1.2.2这个版本，没有任何阻碍，不管是任何几个列，不管是任何数据类型，统统可以替换。  

（注意：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)，REPLACE 则是表示替换表中所有字段）        

        增加分区

            下面两个代码是静态分区

 alter table student add partition(stat_date='20140101') location '/user/hive/warehouse/student' partition(stat_date='20140102');

    alter table student add partition(stat_date='chongqing1') partition(stat_date='chongqing2') partition(stat_date='chongqing3')

       删除分区

         alter table student_ptn drop partition(city='beijing'),partition(stat_date='20140102');

        ALTER TABLE student_p DROP if exists partition(part='aa');

        ALTER TABLE student_p DROP if exists partition(part='aa') if exists partition(part='bb');

    修改分区:

        一般来讲都是修改分区的数据存储目录：

                       //在添加分区的时候，直接指定当前分区的数据存储目录

        ALTER TABLE student_ptn IF NOT EXISTS partition (city='beijing') location '/student_ptn_beijing' partition(city='cc') location '/myhive_cc';

                        //修改已经指定好的分区的数据存储目录

                    alter table student_ptn partition (city='beijing') SET location '/student_ptn_beijing';

    最后补充：

        1、 防止分区被删除：alter table student_p partition (part='aa') enable no_drop;

        2、 防止分区被查询：alter table student_p partition (part='aa') enable offline;enable 和 disable 是反向操作

 13、删除表

        drop table if exists mytable;

  14、清空表

        truncate table student;

        truncate table student_ptn partition(city=’beijing’);

 15、查看 hive 函数列表

        show functions;

16、查看 hive 表的分区

        show partitions table_name;

        show partitions table_name partition(city='beijing');

17、动态分区

        hive> set hive.exec.dynamic.partition=true;

        hive> set hive.exec.dynamic.partition.mode=nonstrict;

    注意：动态分区默认情况下是开启的。但是却以默认是”strict”模式执行的，在这种模式下要求至少有一列分区字段是静态的。这有助于阻止因设计错误导致查询产生大量的分区。但是此处我们不需要静态分区字段，估将其设为 nonstrict。

        set hive.exec.max.dynamic.partitions.pernode=100; //每个节点生成动态分区最大个数

        set hive.exec.max.dynamic.partitions=1000;//生成动态分区最大个数，如果自动分区数大于这个参数，将会报错

        set hive.exec.max.created.files=100000; //一个任务最多可以创建的文件数目

        set dfs.datanode.max.xcievers=4096; //限定一次最多打开的文件数

        set hive.error.on.empty.partition=false; //表示当有空分区产生时，是否抛出异常

        如果以上参数被更改过，想还原，请使用 reset 命令执行一次即可

        set hive.exec.reducers.bytes.per.reducer=51200000; //一次 hive 查询中，每一个reduce 任务它处理的平均数据量

        set hive.exec.reducers.max = 20; //设置的最大 reducetask 数量

        set mapreduce.job.reduces; //查看设置的一个 reducetask 常量数量

    

        set hive.exec.mode.local.auto=true; // hive 会尝试使用本地模式执行

18、join

        只支持等值链接，支持 and，不支持 or

        SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

        SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

        inner join（内连接）（把符合两边连接条件的数据查询出来）

        select * from tablea a inner join tableb b on a.id=b.id;

        left join（左连接，等同于 left outer join）

1、 进入到用户的主目录，使用命令 cat /home/hadoop/.hivehistory 可以查看到 hive 执行的历史命令

2、 执行查询时若想显示表头信息时，请执行命令：Hive> set hive.cli.print.header=true;

3、 hive的执行日志的存储目录在${java.io.tmpdir}/${user.name}/hive.log中，假如使用hadoop用户操作的 hive，那么日志文件的存储路径为：/temp/hadoop/hive.log

 

--------------------------------------------------------------------------------------------------------------------------------

一、关系运算：
1. 等值比较: =
2. 等值比较:<=>
3. 不等值比较: <>和!=
4. 小于比较: <
5. 小于等于比较: <=
6. 大于比较: >
7. 大于等于比较: >=
8. 区间比较
9. 空值判断: IS NULL
10. 非空判断: IS NOT NULL
10. LIKE比较: LIKE
11. JAVA的LIKE操作: RLIKE
12. REGEXP操作: REGEXP
二、数学运算： 
1. 加法操作: +
2. 减法操作: –
3. 乘法操作: *
4. 除法操作: /
5. 取余操作: %
6. 位与操作: &
7. 位或操作: |
8. 位异或操作: ^
9．位取反操作: ~
三、逻辑运算：  
1. 逻辑与操作: AND 、&&
2. 逻辑或操作: OR 、||
3. 逻辑非操作: NOT、!
四、复合类型构造函数    
1. map结构
2. struct结构
3. named_struct结构
4. array结构
5. create_union
五、复合类型操作符 
1. 获取array中的元素
2. 获取map中的元素
3. 获取struct中的元素
六、数值计算函数  
1. 取整函数: round
2. 指定精度取整函数: round
3. 向下取整函数: floor
4. 向上取整函数: ceil
5. 向上取整函数: ceiling
6. 取随机数函数: rand
7. 自然指数函数: exp
8. 以10为底对数函数: log10
9. 以2为底对数函数: log2
10. 对数函数: log
11. 幂运算函数: pow
12. 幂运算函数: power
13. 开平方函数: sqrt
14. 二进制函数: bin
15. 十六进制函数: hex
16. 反转十六进制函数: unhex
17. 进制转换函数: conv
18. 绝对值函数: abs
19. 正取余函数: pmod
20. 正弦函数: sin
21. 反正弦函数: asin
22. 余弦函数: cos
23. 反余弦函数: acos
24. positive函数: positive
25. negative函数: negative
七、集合操作函数    
1. map类型大小：size
2. array类型大小：size
3. 判断元素数组是否包含元素：array_contains
4. 获取map中所有value集合
5. 获取map中所有key集合
6. 数组排序 sort
八、类型转换函数 
1. 二进制转换：binary
2. 基础类型之间强制转换：cast
九、日期函数   
1. UNIX时间戳转日期函数: from_unixtime
2. 获取当前UNIX时间戳函数: unix_timestamp
3. 日期转UNIX时间戳函数: unix_timestamp
4. 指定格式日期转UNIX时间戳函数: unix_timestamp
5. 日期时间转日期函数: to_date
6. 日期转年函数: year
7. 日期转月函数: month
8. 日期转天函数: day
9. 日期转小时函数: hour
10. 日期转分钟函数: minute
11. 日期转秒函数: second
12. 日期转周函数: weekofyear
13. 日期比较函数: datediff
14. 日期增加函数: date_add
15. 日期减少函数: date_sub
十、条件函数   
1. If函数: if
2. 非空查找函数: COALESCE
3. 条件判断函数：CASE
4. 条件判断函数：CASE
十一、字符串函数  
1.    字符ascii码函数：ascii
2.    base64字符串
3. 字符串连接函数：concat
4.    带分隔符字符串连接函数：concat_ws
5. 数组转换成字符串的函数：concat_ws
6. 小数位格式化成字符串函数：format_number
7. 字符串截取函数：substr,substring
8. 字符串截取函数：substr,substring
9. 字符串查找函数：instr
10. 字符串长度函数：length
11. 字符串查找函数：locate
12. 字符串格式化函数：printf
13. 字符串转换成map函数：str_to_map
14. base64解码函数：unbase64(string str)
15. 字符串转大写函数：upper,ucase
16. 字符串转小写函数：lower,lcase
17. 去空格函数：trim
18. 左边去空格函数：ltrim
19. 右边去空格函数：rtrim
20. 正则表达式替换函数：regexp_replace
21. 正则表达式解析函数：regexp_extract
22. URL解析函数：parse_url
23. json解析函数：get_json_object
24. 空格字符串函数：space
25. 重复字符串函数：repeat
26. 左补足函数：lpad
27. 右补足函数：rpad
28. 分割字符串函数: split
29. 集合查找函数: find_in_set
30.    分词函数：sentences
31. 分词后统计一起出现频次最高的TOP-K
32. 分词后统计与指定单词一起出现频次最高的TOP-K
十二、混合函数  
1. 调用Java函数：java_method
2. 调用Java函数：reflect
3. 字符串的hash值：hash
十三、XPath解析XML函数   
1. xpath
2. xpath_string
3. xpath_boolean
4. xpath_short, xpath_int, xpath_long
5. xpath_float, xpath_double, xpath_number
十四、汇总统计函数（UDAF） 
1. 个数统计函数: count
2. 总和统计函数: sum
3. 平均值统计函数: avg
4. 最小值统计函数: min
5. 最大值统计函数: max
6. 非空集合总体变量函数: var_pop
7. 非空集合样本变量函数: var_samp
8. 总体标准偏离函数: stddev_pop
9. 样本标准偏离函数: stddev_samp
10．中位数函数: percentile
11. 中位数函数: percentile
12. 近似中位数函数: percentile_approx
13. 近似中位数函数: percentile_approx
14. 直方图: histogram_numeric
15. 集合去重数：collect_set
16. 集合不去重函数：collect_list
十五、表格生成函数Table-Generating Functions (UDTF)  
1. 数组拆分成多行：explode

2. Map拆分成多行：explode

十六、JAVA自定义函数

1.继承 org.apache.hadoop.hive.ql.exec.UDF，重载 evaluate 方法

import java.util.HashMap;

import org.apache.hadoop.hive.ql.exec.UDF;

public class ToLowerCase extends UDF {

    // 必须是 public，并且 evaluate 方法可以重载

    public String evaluate(String field) {

        String result = field.toLowerCase();

        return result;

    }

}

2、打成 jar 包上传到服务器

3、将 jar 包添加到 hive 的 classpath

hive>add JAR /home/hadoop/hivejar/udf.jar;

查看加入的 jar 的命令：hive> list jar;

4、创建临时函数与开发好的 class 关联起来

hive>create temporary function tolowercase as 'com.ghgj.hive.udf.ToLowerCase';

5、至此，便可以在 hql 在使用自定义的函数

select tolowercase(name),age from student;

 

hive的shell操作命令