hive知识一览

hive的基本数据类型：
Hive数据类型 Java数据类型 长度
TINYINT byte 1byte有符号整数
SMALINT short 2byte有符号整数
INT int 4byte有符号整数
BIGINT long 8byte有符号整数
BOOLEAN boolean 布尔类型，true或者false
FLOAT float 单精度浮点数
DOUBLE double 双精度浮点数
STRING string 字符系列。可以指定字符集。可以使用单引号或者双引号。
TIMESTAMP 时间类型
BINARY 字节数组

集合数据类型
数据类型 描述 语法示例
STRUCT 和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。 struct()
MAP MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素 map()
ARRAY 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。 Array()
建表语句：
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]
其中PARTITIONED by 是分区
CLUSTERED by是分桶
SORTED by 不常用
ROW FORMAT 表示以下多种：
row format delimited by ‘,’
collection items terminated by by ‘_’
map key terminated by ‘:’
lines terminated by ‘\n’
STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE
location 指定表在HDFS上的存储位置
LIKE允许用户复制现有的表结构，但是不复制数据
比如：create table if not exists student4 like student;
基于以下数据创建表
{
“name”: “songsong”,
“friends”: [“bingbing” , “lili”] , //列表Array,
“children”: { //键值Map,
“xiao song”: 18 ,
“xiaoxiao song”: 19
}
“address”: { //结构Struct,
“street”: “hui long guan” ,
“city”: “beijing”
}
}
数据：
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

create table table1(
name sting,
friend array<string>,
children map<string,int>,
address struct<street:string,city:string>
)
row format delimited by ‘,’
collection items terminated by by ‘_’
map key terminated by ‘:’
lines terminated by ‘\n’
stored as parquet
location ‘/warehouse/table1/’;

字段解释：
row format delimited fields terminated by ‘,’ – 列分隔符
collection items terminated by ‘_’ --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ‘:’ – MAP中的key与value的分隔符
lines terminated by ‘\n’; – 行分隔符

查询：
select friends[1],children[‘xiao song’],address.city from test
where name=“songsong”;

加载的几种方式：
（1）load data local inpath “/opt/module/data/table1.txt” into table table1;
（2）create table if not exists student3 as select id, name from student;
（3）insert [overwrite|into] table student select name,…from student1
多表插入：hive (default)> from student
insert overwrite table student partition(month=‘201707’)
select id, name where month='201709’
insert overwrite table student partition(month=‘201706’)
select id, name where month=‘201709’;
（4）Import数据到指定Hive表中：import table student2 partition(month=‘201709’) from
‘/user/hive/warehouse/export/student’;

外部表和内部表的区别：
1.外部表：创建表的时候需要加external 内部表不需要
2.删除表的时候，外部表删除了表和元数据，文件仍然存在在hdfs中
内部表是全部都删掉
3、使用场景，外部表适用于多人共用，即使删除了表，也影响不了数据。如果误删了，重新创建制定这个路径，就自动恢复了数据。
内部表适合中间结果，和结果表，这些表不需要共享
4、外部表结构和分区修改时，需要进行修复：msck rapair table table_name
hive (default)>dfs -mkdir -p
/user/hive/warehouse/dept_partition2/month=201709/day=12;
hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=12;
需要执行msck rapair table dept_partition2
或者
alter table dept_partition2 add partition(month=‘201709’,
day=‘11’);
或者
load data local inpath ‘/opt/module/datas/dept.txt’ into table
dept_partition2 partition(month=‘201709’,day=‘10’);
导出到本地和导出到HDFS上(没有local)
insert overwrite [local] directory ‘/opt/module/datas/export/student1’
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ select * from student;
用命令导出：$bin/hive -e ‘select * from default.student;’ >
/opt/module/datas/export/student4.txt;

Hsql的一些注意：
1、join连接中不支持不等于
2、join连接中不支持 or

排序：
1.order by是全局排序
2.sort by mapreduce中排序
3.Distribute By是分区排序
4.cluster by 相当于distribute by和sorts by字段相同，但是排序只能是升序排序
注意：Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前
例：distribute by deptno sort by empno desc

partition by分区针对的是数据的存储路径；clustered by分桶针对的是数据文件
create table stu_buck(id int, name string)
clustered by(id) into 4 buckets
分桶是为了数据抽样
分桶是将数据集分解为更容易管理的若干部分的另一种技术
Hive是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
实际生产中分桶策略使用频率较低，更常见的还是使用数据分区。

hive的行转列：
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
李四 射手座 A
转成
射手座,A 大海|李四
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋
select
t1.base,
concat_ws(’|’, collect_set(t1.name)) name //将name组合起来，用|分隔
from
(select
name,
concat(constellation, “,”, blood_type) base //组成"射手座,A "
from
person_info) t1
group by
t1.base;
列转行：
需要用explode(col)炸裂单行，适用于只有一列：比如select explode(col1) from table1.
如果多列的话，需要LATERAL VIEW
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;
这里的category是原表列，炸裂后表为table_tmp ,列为category_name。
窗口函数和oracle差不多
分析函数和oracle的差不多
grouping sets需要设置按照什么集group
比如：grouping sets(month,day)
那么month的grouping_id是1，day是2，只有这两个集合
cube是在group by之后with cube即可，cube是所有组合，比如：(year,month)先后顺序是,null,null;year;month;(year,month)
rollup是以左侧为主的向上钻取：null,null;year;(year,month)

查看执行计划explain select * from emp;