1、给用户赋予权限

# 角色创建与删除相关命令
create role rolename;
drop role name;
# 将某个角色赋予某个用户
grant role name to user username；
# 使某个用户拥有databasename数据库的select权限
grant select on database databasename to user username;

2、数据库新建/删除操作

• 创建数据库

create database hello_world;
create database if not exists database_name

• 查看所有的数据库

show databases;

• 进入某个数据库

use  hello_world;

• 删除数据库

//删除空的数据库
drop database if exists database_name; 
//先删除数据库中的表再删除数据库
drop database if exists database_name cascade; 

3、hive表操作

• 查看所有表

show tables;

• 显示表结构

desc hello_world_inner;

• 显示表的分区

show partitions hello_world_parti;

• 显示创建表的语句

show create table table_name;

• 删除表

drop table t1;  
drop table if exists t1;

• 清空表（保留表结构）
  内部表

  truncate table 表名; 
  

  外部表
  由于外部表不能直接删除，所以用shell命令直接删除hdfs上的数据

  #!/bin/bash
  hdfs dfs -rm -r /user/hive/temp_table
  

• 重命名表名称

alter table table_name rename to new_table_name;

4、建表语句

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 table hello_world_inner
(
    id bigint, 
    account string, 
    name string,
    age int
)
row format delimited fields terminated by ',';

• 创建内部表

create EXTERNAL  table hello_world_inner
(
    id bigint, 
    account string, 
    name string,
    age int
)
row format delimited fields terminated by ',';

• 创建分区表

create table hello_world_parti
(
    id bigint,
    name string
)
partitioned by (dt string, country string);

参数说明：
EXTERNAL：创建外部表，在建表的同时可以指定源数据的路径（LOCATION），创建内部表时，会将数据移动到数据仓库指向的路径，若创建外部表不会有任何改变。在删除表时，内部表的元数据和源数据都会被删除，外部表不会删除源数据。

COMMENT：为表和列增加注释

PARTITIONED BY：创建分区表，
——PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY：创建分桶表
SORTED BY：创建排序后分桶表（不常用）
——CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

ROW FORMAT DELIMITED：是用来设置创建的表在加载数据的时候，支持的列分隔符。Hive默认的分隔符是\001，属于不可见字符，这个字符在vi里是^A
—— ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'；

STORED AS：指定存储文件类型 sequencefile （二进制序列文件）、textfile（文本）、rcfile（列式存储格式文件）、ORC
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。
如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

LOCATION：指定表在 hdfs 上的存储位置

5、表结构修改

• 增加字段

alter table table_name add columns (newcol1 int comment ‘新增’)；

• 修改字段名称及类型

alter table table_name change col_name new_col_name new_type；

• 删除字段(COLUMNS中只放保留的字段)

alter table table_name replace columns (col1 int,col2 string,col3 string)；

• 删除分区

alter table table_name drop if exists partitions (d=‘2016-07-01');

注意：若是外部表，则还需要删除文件(hadoop fs -rm -r -f hdfspath)

• 修改字段分隔符 和 修改序列化分隔符

//修改字段分隔符
alter table aaa set serdeproperties('field.delim'='\t');
//修改序列化分隔符
alter table aaa set serdeproperties('serialization.format'='1');
//aaa为表名

6、字段类型

7、加载数据到hive表

• 1、从HDFS加载

//使用data_base_zhangkai数据库
use data_base_zhangkai;
LOAD DATA INPATH '/user/zhangkai/aaa' OVERWRITE INTO TABLE ods_aaa; 

注意：INPATH后面的文件路径不能和hive表路径在hdfs上一致，最好是两个不同的文件路径，在加载过程中，源路径下的文件会被移动到hive表所在路径下，如果一致，会找不到文件错误；

• 2、从本地加载

use data_base_zhangkai;
LOAD LOCLDATA INPATH '/user/zhangkai/aaa' OVERWRITE INTO TABLE ods_aaa; 

8、存储格式

Hive支持内置和自定义开发的文件格式。以下是Hive内置的一些格式：

默认是文本格式.
textfile 存储空间消耗比较大，并且压缩的text 无法分割和合并查询的效率最低,可以直接存储，加载数据的速度最高.
sequencefile 存储空间消耗最大,压缩的文件可以分割和合并查询效率高，需要通过text文件转化来加载.
rcfile 存储空间最小，查询的效率最高 ，需要通过text文件转化来加载，加载的速度最低.

相比传统的行式存储引擎，列式存储引擎具有更高的压缩比，更少的IO操作而备受青睐（注：列式存储不是万能高效的，很多场景下行式存储仍更加高效），尤其是在数据列（column）数很多，但每次操作仅针对若干列的情景，列式存储引擎的性价比更高。

• 1、TEXTFILE
  默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理。源文件可以直接通过hadoop fs -cat 查看.
  存储方式：行存储
  磁盘开销大,数据解析开销大.
  压缩的text文件 hive无法进行合并和拆分

• 2、SEQUENCEFILE
  一种Hadoop API提供的二进制文件，使用方便、可分割、可压缩等特点。
  SEQUENCEFILE将数据以< key,value>的形式序列化到文件中。序列化和反序列化使用Hadoop 的标准的Writable 接口实现。key为空，用value 存放实际的值， 这样可以避免map 阶段的排序过程。
  三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。文件和Hadoop api中的mapfile是相互兼容的。

      //使用时设置参数: 
  set hive.exec.compress.output=true; 
  set io.seqfile.compression.type=BLOCK;      – NONE/RECORD/BLOCK 
  create table test2(str STRING) STORED AS SEQUENCEFILE; 
  

• 3、RCFILE
  一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。
  理论上具有高查询效率（但hive官方说效果不明显，只有存储上能省10%的空间，所以不好用，可以不用）。
  RCFile结合行存储查询的快速和列存储节省空间的特点
  1）同一行的数据位于同一节点，因此元组重构的开销很低；
  2）块内列存储，可以进行列维度的数据压缩，跳过不必要的列读取。
  查询过程中，在IO上跳过不关心的列。实际过程是，在map阶段从远端拷贝仍然拷贝整个数据块到本地目录，也并不是真正直接跳过列，而是通过扫描每一个row group的头部定义来实现的。
  但是在整个HDFS Block 级别的头部并没有定义每个列从哪个row group起始到哪个row group结束。所以在读取所有列的情况下，RCFile的性能反而没有SequenceFile高。

• 4、ORC
  hive给出的新格式，属于RCFILE的升级版。
  ORC（OptimizedRC File）存储源自于RC（RecordColumnar File）这种存储格式，RC是一种列式存储引擎，对schema演化（修改schema需要重新生成数据）支持较差，而ORC是对RC改进，但它仍对schema演化支持较差，主要是在压缩编码，查询性能方面做了优化。RC/ORC最初是在Hive中得到使用，最后发展势头不错，独立成一个单独的项目。Hive 1.x版本对事务和update操作的支持，便是基于ORC实现的（其他存储格式暂不支持）。ORC发展到今天，已经具备一些非常高级的feature，比如支持update操作，支持ACID，支持struct，array复杂类型。你可以使用复杂类型构建一个类似于parquet的嵌套式数据架构，但当层数非常多时，写起来非常麻烦和复杂，而parquet提供的schema表达方式更容易表示出多级嵌套的数据类型。
  ORC是RCfile的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩比和Lzo压缩差不多，比text文件压缩比可以达到70%的空间。而且读性能非常高，可以实现高效查询。

9、配置优化

# 开启任务并行执行
set hive.exec.parallel=true
# 设置运行内存
set mapreduce.map.memory.mb=1024;
set mapreduce.reduce.memory.mb=1024;
# 指定队列
set mapreduce.job.queuename=jppkg_high;
# 动态分区，为了防止一个reduce处理写入一个分区导致速度严重降低，下面需设置为false
# 默认为true
set hive.optimize.sort.dynamic.partition=false;
# 设置变量
set hivevar:factor_timedecay=-0.3;
set hivevar:pre_month=${zdt.addDay(-30).format("yyyy-MM-dd")};
set hivevar:pre_date=${zdt.addDay(-1).format("yyyy-MM-dd")};
set hivevar:cur_date=${zdt.format("yyyy-MM-dd")};
# 添加第三方jar包, 添加临时函数
add jar ***.jar;
# 压缩输出，ORC默认自带压缩，不需要额外指定,如果使用非ORCFile,则设置如下
hive.exec.compress.output=true
# 如果一个大文件可以拆分，为防止一个Map读取过大的数据，拖慢整体流程，需设置
hive.hadoop.suports.splittable.combineinputformat
# 避免因数据倾斜造成的计算效率，默认false
hive.groupby.skewindata
# 避免因join引起的数据倾斜
hive.optimize.skewjoin
# map中会做部分聚集操作，效率高，但需要更多内存
hive.map.aggr   -- 默认打开
hive.groupby.mapaggr.checkinterval  -- 在Map端进行聚合操作的条目数目
# 当多个group by语句有相同的分组列，则会优化为一个MR任务。默认关闭。
hive.multigroupby.singlemr
# 自动使用索引，默认不开启，需配合row group index，可以提高计算速度
hive.optimize.index.filter

10、常用函数

• if 函数，如果满足条件，则返回A， 否则返回B
  if (boolean condition, T A, T B)

• case 条件判断函数, 当a为b时则返回c；当a为d时，返回e；否则返回f
  case a when b then c when d then e else f end

• Json操作
  将字符串类型的数据读取为json类型，并得到其中的元素key的值
  第一个参数填写json对象变量，第二个参数使用.key')

• url解析 parse_url()
  parse_url('http://facebook.com/path/p1.php?query=1','HOST')返回'facebook.com'
  parse_url('http://facebook.com/path/p1.php?query=1','PATH')返回'/path/p1.php'
  parse_url('http://facebook.com/path/p1.php?query=1','QUERY')返回'query=1'，

• explode ：将hive一行中复杂的array或者map结构拆分成多行
  explode(colname)

• lateral view ：将一行数据adid_list拆分为多行adid后，使用lateral view使之成为一个虚表adTable，使得每行的数据adid与之前的pageid一一对应, 因此最后pageAds表结构已发生改变，增加了一列adid
  select pageid, adid from pageAds
  lateral view explode(adid_list) adTable as adid

• 字符串函数
  • 字符串分割str，按照pat，返回分割后的字符串数组
    split(string str, string pat)
  • 将字符串转为map, item_pat指定item之间的间隔符号，dict_pat指定键与值之间的间隔
    str_to_map(string A, string item_pat, string dict_pat)
  • 反转字符串
    reverse()
  • 字符串截取
    substring(string A, int start, int len)
  • 字符串连接
    concat(string A, string B, string C, ...)
  • 自定义分隔符sep的字符串连接
    concat_ws(string sep, string A, string B, string C, ...)
  • 返回字符串长度
    length()
  • 去除两边空格
    trim()
  • 大小写转换
    lower(), upper()
  • 返回列表中第一个非空元素，如果所有值都为空，则返回null
    coalesce(v1, v2, v3, ...)
  • 返回第二个参数在待查找字符串中的位置（找不到返回0）
    instr(string str, string search_str)
  • 将字符串A中的符合java正则表达式pat的部分替换为C;
    regexp_replace(string A, string pat, string C)
  • 将字符串subject按照pattern正则表达式的规则进行拆分，返回index指定的字符，0:显示与之匹配的整个字符串， 1：显示第一个括号里的， 2：显示第二个括号里的
    regexp_extract(string subject, string pattern, int index)

• 类型转换
  cast(expr as type)

• 提取出map的keys, 返回key的array
  map_keys(map m)

• 日期函数

  • 日期比较函数，返回相差天数，
    datediff('${cur_date},d)
    datediff(date1, date2)
  • 返回当前时间
    from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')

• 行转列
  collect_set 去重
  collect_list 不去重

  //sub 为数组
  SELECT p.member_id, collect_set(p.sub1) sub from temp p group by p.member_id;
  // 把数组以逗号拼接为字符串
  SELECT p.member_id, concat_ws(',',collect_set(p.sub1)) sub from temp p group by p.member_id;
  

• 列转行

  //将表中path列（字符串）的数据以'/'切割为
  select 
       member_id,parent_id
    from 
        recommend_path 
    LATERAL VIEW explode(split(path,'/')) idtable as parent_id 
    where parent_id != "" and parent_id is not null and parent_id != member_id 
  

使用

• 从身份证中获取年龄

  //id_no为身份证号
  select (from_unixtime(unix_timestamp(),'yyyy')-substr(id_no,7,4))
  age,id_no from aaa_bank_info  limit 100
  

• 日期判断
  使用to_date(),如：to_date(orderdate)=‘2016-07-18’

• 字符串比较
  HQL中字符串的比较比较严格，区分大小写及空格，因此在比较时建议upper(trim(a))=upper(trim(b))

• update操作
  HQL不支持update
  实际采用union all + left join (is null)变相实现update
  思路：
  1.取出增量数据；
  2.使用昨日分区的全量数据通过主键左连接增量数据，并且只取增量表中主键为空的数据（即，取未发生变化的全量数据）；
  3.合并1、2的数据覆盖至最新的分区，即实现了update；

• delete实现
  采用not exists/left join(is null)的方法变相实现。
  1.取出已删除的主键数据（表B）；
  2.使用上一个分区的全量数据（表A）通过主键左连接A，并且只取A中主键为空的数据，然后直接insert overwrite至新的分区；

• UNION实现
  HQL中没有UNION，可使用distinct+ union all 实现 UNION；

• with

• join