HiveSQL汇总

创建数据库
create database if not exists sopdm
comment 'this is test database'
with dbproperties('creator'='gxw','date'='2014-11-12') 
--数据库键值对属性信息
location '/my/preferred/directory';


显示所有表
show tables ;
显示表的描述信息
desc [extended,formatted] tablename;
显示建表语句
show create table tablename;
删除表
drop table tablename;

由一个表创建另一个表，相当于复制，表结构复制，数据没复制
create table test3 like test2;

由其他表查询创建表
create table test4 as select name,addr from test5;


stored as textfile
可以直接查看
stored as sequencefile
必须用hadoop fs -text查看
stored as rcfile
hive -service rcfilecat path 查看

stored as inputformat 'class'（自定义的）


加载jar包
shell窗口add jar path（作用范围本shell）
加载到分布式缓存中供各个节点使用

或者直接拷贝到hive安装目录下的lib目录


SerDe（hive使用SerDe读、写表的行）

读写顺序：
HDFS文件-->InputFileFormat--> -->Deserializer-->Row对象（供hive使用）
Row对象-->Serializer--> -->OutputFileFormat-->HDFS文件

hive自带RegexSerDe.class 正则表达式匹配每一行的数据

create table apachelog(
host STRING, 
identity STRING,
user STRING,
time STRING,
request STRING,
status STRING,
size STRING,
refer STRING,
agent STRING) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' 
WITH SERDEPROPERTIES (
"input.regex="([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([0-9]*) ([0-9]*) ([^ ]*) ([^ ]*)"
) stored AS TEXTFILE；


分区表（相当于表的子目录）

create table tablename (name string) partitioned by (key type,...)


create external table employees(
name string,
salary float,
subordinates array,
deductions map,
address struct)
partitioned by (dt string,type string)
row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/data';
数据格式：
wang  123  a1,a2,a3  k1:1,k2:2,k3:3  s1,s2,s3,4

查看分区：
show partitions employees
增加分区

alter table employees add if not exists partition(country='xxx'[,state='yyy'])
删除分区
alter table employees drop if  exists partition(country='xxx'[,state='yyy'])

动态分区：
1.不需要为不同的分区添加不同的插入语句
2.分区不确定，需要从数据中获取
参数：(动态分区前两个必须开启）
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrick;
//无限制模式，如果是strict，则必须有一个静态分区，且放在最前面
set hive.exec.max.dynamic.partitions.pernode=10000;
//每个节点生成动态分区的最大个数
set hive.exec.max.dynamic.partitions=100000;
//每次sql查询生成动态分区的最大个数
set hive.exec.max.created.files=150000;
//一个任务最多可以创建的文件数目
set dfs.datanode.max.xcievers=8182;
//限定一次最多打开的文件数
1. 创建分区表
create table d_part (
name string
)
partitioned by (value string)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
2. 插入动态分区
insert overwrite table d_part partition(value)
select name,addr as value 
from testtext;


分桶
set hive.enforce.bucketing=true;

按id分桶
create table bucketed_user
(id string,
name string)
clustered by (id) sorted by(name)  into 4 buckets
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
分桶抽样
select * from bucketed_user tablesample(bucket 1 out of 2 on id )
取一半的桶
优化
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

硚口网站制作公司哪家好，找成都创新互联公司！从网页设计、网站建设、微信开发、APP开发、响应式网站建设等网站项目制作，到程序开发，运营维护。成都创新互联公司2013年开创至今到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选成都创新互联公司。

beeline底层使用的jdbc，命令行使用jdbc(可以远程访问）

hive -help
hive --help
hive --service -help

查询结果写到文件
hive -V -e "select name from testtext" > /home/data/result


hive命令行输入list jar
显示当前分布式缓存有哪些jar包也就是add jar命令加载的jar包）

命令行执行hql文件（类似于hive -f)，常用于设置初始化参数
source /home/data/hql/select_hql

配置变量
set val='';
hql使用hive变量
${hiveconf:val}
select * from testtext where name ='${hiveconf:val}';
env查看linux环境变量
HOME=/root
hql使用linux环境变量
select '${env:HOME}' from testtext;



1.内表数据加载(overwrite和into不能同时存在，只能存在一个）
(1) 创建表时加载
create table newtable as select col1,col2 from oldtable;
(2)创建表时指定数据位置(对location下的数据具有拥有权，删除内表时也会删除数据）

create table tablename() location '';
(3)本地数据加载
load data local inpath 'localpath' [overwrite] into table tablename;
(4)加载hdfs数据（移动数据，原来的数据移动到表的位置下）
load data  inpath 'hdfspath' [overwrite] into table tablename;

hive命令行执行linux shell命令在前面加个！
!ls /home/data

(5)通过查询语句加载数据

insert  into table test_m select name,addr from testtext where name ='wer';
或者
from testtext insert  into table test_m select name,addr  where name ='wer';
或者
select name,addr from testtext  where name ='wer' insert  into table test_m ;

2.外部表数据加载
(1)创建表时指定数据位置
create external table tablename() location '';
(2)查询插入，同内表
(3)使用hadoop命令拷贝数据到指定位置（hive的shell中执行和linux的shell执行）

3.分区表数据加载
（1）内部分区表数据加载类似于内表

（2）外部分区表数据加载方式类似于外表
注意：数据存放的路径层次要和表的分区一致,并且表要增加相应分区才能查到数据

load data local inpath 'localpath' [overwrite] into table tablename partition(dt='20140905');

4.数据类型对应问题
Load数据，字段类型不能相互转化时，查询返回NULL；
select查询输入，字段类型不能相互转化时，插入数据为NULL(文件保存是 \N)；
select查询输入数据，字段名称可不一致，数据加载不做检查，查询时检查



数据导出

导出到本地，默认分隔符^A
insert overwrite local directory '/home/data3' 
row format delimited fields terminated by '\t'
select name,addr from testtext; 

导出到hdfs，不支持row format delimited fields terminated by '\t'，只能采用默认分隔符是I
insert overwrite directory '/home/data3' 
select name,addr from testtext; 

表属性操作

1.修改表名
alter table table_name rename to new_table_name；

2.修改列名
alter table tablename change column c1 c2 int comment 'xxx' after severity;
c1旧列，c2新列，int代表新列数据类型
after severity;可以把该列放到指定列的后面，或者使用'first'放到第一位

3.增加列(默认新增列放到最末尾）
alter  table tablename add column (c1 string comment 'xxxx',c2 string comment 'yyyy')；

4.修改tblproperties
alter table test set tblproperties('comment'='xxxx');

5.修改分隔符(分区表比较特殊）
方式一
alter table city set serdeproperties('field.delim'='\t');（对分区表原始数据无效，对新加分区有效）

方式二 对分区表原始数据也使用最新的分隔符
alter table city partitin(dt='20140908') set serdeproperties('field.delim'='\t');

6.修改location
alter table city [partition(...)] set location 'hdfs://master:9000/location' ;

7.内部表和外部表转换
alter table test set tblproperties('EXTERNAL'='TRUE');内部表转外部表
alter table test set tblproperties('EXTERNAL'='FALSE');外部表转内部表

聚合操作
1.count计数
count(*)所有的字段不全为null，全为null不加1
count(1)不管记录是啥，只要有这条记录都加1
count(col)列不为空加1

2.sum求和
sum(可转成数字的值)  返回bigint
sum(col)+cast(1 as bigint)

3.avg
avg(可转成数字的值) 返回double

where条件在map端执行

group by是在reduce端执行 分组的列组合为key

having字句聚合操作之后执行判断，也是在reduce端执行


groupby数据倾斜优化
hive.groupby.skewindata=true;（多起一个job）


join操作（普通join不支持不等值链接）
优化参数set hive.optimize.skewjoin=true;
样例
select m.col as col,m.col2 as col2,n.col3 as col3
from 
(select col,col2
from test
where ...(map端执行)
) m
[left outer|right outer|left semi] join
n (右表)
on m.col=n.col
where condition (reduce端执行)

LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现

Hive 当前没有实现 IN/EXISTS 子查询，所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 

ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

 SELECT a.key, a.value

 FROM a

 WHERE a.key in

  (SELECT b.key

  FROM b);

可以被重写为：

  SELECT a.key, a.val

  FROM a LEFT SEMI JOIN b on (a.key = b.key)

order by全局排序，只有一个reduce
distribute by col分散数据，按col分散到不同的reduce
和sort by结合保证每个reduce输出是有序的
 

union all（不去重）和union（去重）

hive只支持union all，子查询不允许起别名
select col from 
(select a as col from t1 
union all
select b as col from t2
) tmp;

要求：
1.字段名字一样
2.字段类型一样
3.字段个数一样
4.子表不能有别名
5.如果需要从合并之后的表中查询数据，那么合并之后的表必须要有别名

hive创建索引表

hive> create index user_index on table user(id)     
        > as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'    
        > with deferred rebuild    
        > IN TABLE user_index_table;    
    hive> alter index user_index on user rebuild;    
    hive> select * from user_index_table limit 5;

直接用hadoop命令复制删除hive存储数据后，需要add partition或alter来同步源数据信息，否则drop表等操作时会查询元数据metastore，查到metastore信息和hdfs信息不一致，会报错。这个也可算是hive的bug，尚未修复，但也可以理解为初衷不建议直接操作hdfs数据。
目前，可以采用命令： 

1. MSCK REPAIR TABLE table_name;  

该命令会把没添加进partition的数据，都增加对应的partition。同步源数据信息metadata。

Recover Partitions

Hive在metastore中存储每个表的分区列表，如果新的分区加入HDFS后，metastore不会注意这些分区，除非

ALTER TABLE table_name ADD PARTITION

当然可以通过

MSCK REPAIR TABLE table_name;

类似EMR版本中的如下命令

ALTER TABLE table_name RECOVER PARTITIONS;