福州大学苏立超老师《大数据库系统》第六章复习提纲“hive”

第六章 hive（6.3-6.8 hive的常用的属性交互命令，数据简介基本操作，hql基本操作导入为数据导入，导出内部表、外部表、分区表、窗口函数、case when命令，这些全部都是非常重要的部分）

一.hive常用交互指令

1. 查看帮助：bin/hive -help
2. 启动时指定进入某个数据库：bin/hive –database dbname
3. 在命令行执行sql语句：bin/hive -e “sql语句”
4. 执行含sql语句的文件：bin/hive -f filepath
5. 临时修改配置参数，重启hive后失效：bin/hive -hiveconfig <property=value>
6. 退出hive：quit、exit、ctrl+C
7. Hive shell中执行linux命令：!如!ls/ !cd …/
8. Hive shell中执行hdfs命令：dfs -ls/

二.Hive常见数据类型

1. tinyint：1byte整数
2. smallint：2byte整数
3. int：4byte整数
4. bigint：8byte整数
5. Boolean：布尔类型
6. Float：单精度浮点数
7. Double：双精度浮点数
8. String：字符
9. Timestamp：时间

三.Hive 数据库的基本操作

1. 创建数据库：create database if not exists dbname
2. 数据库中数据表导入数据： use dbname; create table if not exists tbname(name string,age int)row format delimited fields terminated by ‘\t’; load data inpath ‘path’into table tbname; select * from tbname;
3. 创建数据库时指定location：默认在hdfs的user/hive/warehouse目录下创建dbname.db目录，其中创建tbname目录，创建数据库时可指定location create database if not exists tmp2 location ‘/hive/tmp2’;
4. 数据库删除语句 无法删除非空数据库：drop database dbname 强制删除：drop database dbname cascade
5. 显示数据库：show databases 模糊查找：show databases like “db_hive*”
6. 查看数据库详细信息 desc database extended dbname

四.Hive中表的基本操作

1. 创建表命令格式：create table if not exists tbname(name string,age int)
2. row format delimited fields terminated by ‘\t’以\t分割
3. stored as textfile 数据存储的格式为文本文件
4. load data local inpath ‘…’ 表示数据存储的路径和文件名，加local表示本地路径，不加表示hdfs路径
5. 导入数据 load data local inpath ‘…’overwrite into table tbname 红色是可选，overwrite代表覆盖
6. 添加as子查询方式创建表 create table tbname as select name from tbname
7. 通过like创建表：create table tbname2 like tbname1，as是将查询结构放入新表，like只复制表结构
8. 删除表：drop table tbname
9. 清空表：truncate table tbname
10. 查看表：desc extend formatted tbname ：formatted格式化输出表信息
11. 重命名表：alter table tbname rename to newname
12. 替换（修改）列：alter table tbname change col_old_name col_new_name type
13. 增加列：alter table tbname add col_name type

五.HQL基本使用

1. 全表查询与特定列查询：select * from tmp
2. 指定列别名：select ename as name,depno db from tmp
3. 算术运算符：select sal+1 from tmp
4. Where、limit、distinct：select * from tmp where deptno=30 distinct去重
5. 比较运算符： = （省略）
6. 逻辑运算符：and or not
7. 聚合函数：count max min sum avg count(列名)会不统计null
8. Like选择类似的值：select * from tmp where name like ‘a%’
9. 分组查询：group by ,having
10. 连接：join on,left join,right join,full join
11. 排序：order by,sort by,distribute by（对数据按字段进行分区，交给不同的reduce进行处理）

六.Hive内部表、外部表、分区表

1. 内部表：table type=MANAFE_TABLE，删除表时删除元数据和hdfs数据文件
2. 外部表：table type=EXTERNAL_TABLE，创建 creare [external] table …，删除表时删除元数据，不会删除hdfs数据文件
3. 转换：alter table tablename set tblproperties(‘EXTERNAL’=’TRUE’)
4. Location指定源数据，为什么不复制多份源数据？ Answer：导致数据冗余，HDFS已有备份机制；都使用外部表分析共享源数据，不影响其他人分析使用
5. 分区表功能：将表中数据进行分区，在进行分区检索时，直接加载对应分区的数据
6. 创建分区表：create table tablename partitioned by col_name load data …

七.Hive中数据导入与导出

1. 加载本地文件到hive：load data local inpath ‘path’ [overwrite] into table table_name
2. 加载hdfs文件到hive：load data inpath ‘hdfs_path’into table table_name
3. 创建表时通过as select加载：create table table_name as select …
4. 创建表时通过location加载：create table_name location ‘path’
5. 创建表后insert数据：insert overwrite|into table table_name select …
6. Insert导出数据：insert overwrite [local] directory ‘path’ select …
7. Hadoop的hdfs命令的get操作导出数据：bin/hdfs -get path
8. 执行hive语句：bin/hive -e select * from path
9. Import和export命令：export/import table table_name to ‘path’
10. 清空表中数据：truncate tablename，不能删除外部表中数据

八.窗口函数（超级重点）

1. 窗口函数：可以对分组后的数据进行组内每行的处理
2. 格式：Function (arg1,…, argn) OVER ([PARTITION BY <…>] [ORDER BY <…>][ROWS BETWEEN <start_expr> AND <end_expr> ]) Function (arg1,…, argn)表示函数及传入的参数 PARTITION BY <…> 对哪一个字段进行分组，和Group By类似 ORDER BY <…>对哪一个字段数据进行排序，默认为升序 如果不指定ORDER BY，默认从起点到终点
3. Function函数： COUNT计数、SUM 求和、AVG 求平均、MIN/MAX 最小/最大（支持ROW BETWEEN） ROW_NUMBER 从1开始，按照顺序，生成分组内每条记录的序列编号 RANK 生成数据项在分组中的排名，排名相等会在名次中留下空位 DENSE_RANK 生成数据项在分组中的排名，排名相等会在名次中不会留下空位 FIRST_VALUE取分组内排序后，截止到当前行，第一个值 LAST_VALUE取分组内排序后，截止到当前行，最后一个值 LEAD用于统计窗口内往后第n行值 LAG 用于统计窗口内往前第n行值 NTILE n切分的片数
4. rows between ROWS BETWEEN <start_expr> AND <end_expr>： 如果不指定ROWS BETWEEN，默认从起点到当前行 rows between unbounded preceding and current row 起点到当前 rows between 3 preceding and 1 following 当前行前三行到当前后一行 rows between current row and unbounded following 当前行到终点
5. 案例：在emp表中按照每个部门分组显示所有雇员的薪资降序排列并显示序号 Select empno,ename,depeno,sal,ROW_NUMBER() over (partition by depno order by sal desc)as number from emp;
6. LEAD函数：lead(col,value2,value3)，显示当前行的后value2行的目标列col的值，value3是超出窗口的默认值
7. Lag函数：lag(col.value2,value3)，显示当前行的前value2行的目标列col的值
8. RANK、DENSE_RANK、ROW_NUMBER函数：rank生成数据在分组中排名，排名相等则留空；dense_rank排名相等不留空；row_number生成序号
9. 窗口函数和group by区别：窗口函数保留所有进入分区的数据，不去重，可以进行更为复杂个性化的操作