//创建一个普通表 , 并且用默认的分割字段
/001 在shell中ctrl+v然后ctrl+a
create table tableName(id int,name string,age int);
//建表语法
create [external] table [if not exists] table_name(filed_name field_type,filed_name field_type...)
[partitioned by(partitionField_Name partitionField_Type,....)]
[clustered by(clusteredField_Name,...) into cluster_Num buckets]
[sorted by(sortedField_Name[DESC|ASC],...)]
[row format DELIMITED]
[stored as file_format]
[location hdfs_path]
注意 : 建表语法跟上面的顺序保持一致!
LIKE 关键字 : 是将表结构进 行复制! 不会复制表的数据!
语法 : CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name LIKE existing_table;
//高级写法 ;
create table table_Name as select...... //将select 查询到的结果作为创建的表的字段! 建立表的字段和个数取决于后面的select查询语句返回的结果
group by 语法: select表达式的字段要么在group by分组条件中,要么被聚合函数包围
报错 : select a.mouth,a.day,a.houst from tablename as a where a.datestr='分区字段' group by a.houst;
正确group by 写法 : select a.mouth,a.day,a.houst,count(*) from tablename as a where a.datestr='分区字段' group by a.mouth,a.day,a.houst;
或者是 : select count(a.mouth) from tablename as a where a.datestr='分区字段' group by a.houst;
------------------------------------------------------------------------------------------------------------------------
ROW FORMAT DELIMITED（指定分隔符）
create table day_table (id int, content string) partitioned by (dt string) row format delimited fields terminated by ','; ---指定分隔符创建分区表
复杂类型的数据表指定分隔符
create table complex_array(name string,work_locations array<string>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',';
数据如下：
zhangsan
beijing,shanghai,tianjin,hangzhou
wangwu
shanghai,chengdu,wuhan,haerbin
create table t_map(id int,name string,hobby map<string,string>)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':' ;
数据：
1,zhangsan,唱歌:非常喜欢-跳舞:喜欢-游泳:一般般
2,lisi,打游戏:非常喜欢-篮球:不喜欢
------------------------------------------------------------------------------------------------------------------------
//创建内部表 , 外部表
内:create table if not exists student(id int,name string,age int) row format delimited fields terminated by ',';
外:create external table if not exists student(id int,name string,age int) row format delimited fields terminated by ',' location '指定一个要映射的HDFS文件目录';
//内部表外部表的区别:
内部表 : 表映射的的结构化数据必须在 /use/hive/warehouse
最大的缺点 : 在drop 删除表的时候 , 结构化的数据也删除了 , 因为内部表是把数据移动到了当前目录,
外部表 : 表映射的外置可以再HDFS上的任何外置 ,因此再建表的时候需要指定所在路径.
创建外部表的时候,不会在HDFS上面生成相应的目录,会将目标目录下面的文件进行重命名 , 映射前表名student.txt,映射后表名student_cope_1.txt ,删除表不会删除对应的元数据!
//加载数据
本地加载 : load data local inpath '本地文件所在路径' [overwrite] into table 'table_Name';
非本地加载 : load data inpath 'HDFS上文件所在路径' into table 'table_Name';
load加载数据,当指定的目录是一个文件的时候,会加载本文件上去! 当加载的目录是一个文件夹的时候, 就会把文件夹下面的所有的文件加载到表中!
load本地带local加载数据的方式! 其实本质上就是由hive去替我们执行put命令! 是一个复制操作!
load不带local的! 其实本质上是有hive执行 hadoop -fs -mv '文件目录' '/目标文件目录' ! 本质上是移动的操作!
----------------------------------------------------------------------------------------------------------------------
//分区表的创建
creat table student(id int,name string,age int) partitioned by(partitionField_Name partitionField_Type,...) row format delimited fields terminated by ',';
//分区表加载数据不能够使用普通的hadoop fs -put命令上传了!
load data local inpath '本地文件所在路径' into table 'table_Name' partition(分区字段='分区值');
load data inpath 'HDFS上文件所在路径' into table 'table_Name' partition(分区字段='分区值'); //分区字段和我们定义的partitionedFiled_Name保持一致
基于分区的查询：
SELECT day_table.* FROM day_table WHERE day_table.dt = '2017-07-07';
查看分区：
show partitions day_hour_table;
分区表分为多分区 、 单分区
关于分区表:
1、是一种更加细致组织文件的形式 把文件划分的更加规范 再进行全表查询的时候 不需要进行全表扫描
2、分区表的字段不能够是表结构中已经存在的字段
3、分区表的现象 在表所在的那个文件夹下面 以分区的字段和分区字段值 再建立一个子文件夹 分区字段=分区值
然后再把对应的结构化数据文件 放在这个文件夹下面
这样的好处就在与 当指定查询某些范围的时候 直接去指定分区下面查询 前提是你分区的字段就是你查询条件
否则还是进行全局扫描
4、虽然select 分区表能够看到分区字段 和分区字段值 但是分区字段是个虚拟的字段 并不真实存在于结构化数据文件当中
总的说来partition就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。
---------------------------------------------------------------------------------------------------------------------
简单的显示命令 :
show tables;
显示当前数据库所有表
show databases |schemas;
显示所有数据库
show partitions table_name;
显示表分区信息，不是分区表执行报错
show functions;
显示当前版本 hive 支持的所有方法
desc extended table_name;
查看表信息
desc formatted table_name;
查看表信息（格式化美观）
describe database database_name;
查看数据库相关信息
---------------------------------------------------------------------------------------------------------------------
//分桶表 、分簇 cluster by(filed_name) into num buckets 思考: 按照什么规则分桶呢? 按照Hash取模来分桶 字段的哈希值 % 桶的个数 列值哈希
分桶默认是不开启的 , 需要设置开启
set hive.enforce.bucketing = true
set mapreduce.job.reduces=4; //reduce 设置成几个 就是最后输出几个文件 , 相当于分了几个桶!
//分桶的底层才是真正的 mr当中的partitioner分区 + reduceTask
//创建桶表
下面的4 代表分四个桶,而且set mapreduce的数量要保持一致
create table student(sno int,sname string,sage int) clustered by(要根据哪个字段分桶) into 4 buckets row format delimited fields terminated by ','; //分桶字段必须是表里已经存在的字段
//加载数据 分桶支持inster + select 来加载数据 表名插入表的数据来自另一个表的查询的返回的结果
总结：分桶表 分簇表 同样是把结构化的数据文件划分颗粒更加细致 有别于分区表在文件夹上做文章 这里是在文件层面 把文件划分开几个部分 也就是几桶
1、分桶表的功能需要提前开启 默认不开启
2、分桶表的字段一定要是表中存在的字段
3、建表的时候分为几桶 INTO X BUCKETS，就需要在环境中设置set mapreduce.job.reduces= X，对应上。
当我们建表的桶个数 跟执行环境中 hiveconf mapreduce.job.reduces 个数不一致的话 会在insert的时候 由hive做出改变
使得结果文件符合表的定义 这时候就没有规律可行的
hive的各种定义只是一种软性约束 就要求使用者一定要结合具体情况对应上 当不对应上 hive尝试做转换 但是保证符合你最终的样子
---------------------------------------------------------------------------------------------------------------------
insert 语句的使用!
insert + value sql的插入语法,在当前的hive中也支持,但是我们坚决不使用! 这不是hive的核心,hive的核心跟已经存在的结构化数据产生映射 也就是数据已经存在了
再hive的生产中! 更多的配合select使用!
多重插入 : 就是一次查询可以多次插入! 我们主要研究的是动态分区插入!
Multi Inserts 多重插入:
from source_table
insert overwrite table tablename1 [partition (partcol1=val1,partclo2=val2)]
select_statement1
insert overwrite table tablename2 [partition (partcol1=val1,partclo2=val2)]
select_statement2..
所谓的动态分区插入 还是静态分区取决于给分区加载数据的时候分区是写死的还是没有写死!
set hive.exec.dynamic.partition=true; #是否开启动态分区功能，默认false关闭。
set hive.exec.dynamic.partition.mode=nonstrict; #动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。
我们一般使用的是nonstrict 就是都可以是动态的分区! 那么这个分区是在哪里写死的呢???这是我们需要考虑的!
动态分区用insert+select来加载数据!
动态分区的加载: 就是把你查询的结果按照顺序依次插入到它的目标表的真实字段和分区字段中! 在这个时候我们指定的分区!
动态分区是根据位置来对应分区值的! 如果位子不对! HQL尝试解析! 解析失败返回null;
例子 :
需求：
将dynamic_partition_table中的数据按照时间(day)，插入到目标表d_p_t的相应分区中。
原始表：
create table dynamic_partition_table(day string,ip string)row format delimited fields terminated by ",";
load data local inpath '/root/hivedata/dynamic_partition_table.txt' into table dynamic_partition_table;
2015-05-10,ip1
2015-05-10,ip2
2015-06-14,ip3
2015-06-14,ip4
2015-06-15,ip1
2015-06-15,ip2
目标表:
create table d_p_t(ip string) partitioned by (month string,day string);
动态插入： 没有跑mr程序
insert table d_p_t partition (month,day)
select ip,substr(day,1,7) as month,day
from dynamic_partition_table;
导出数据 : //注意local的是使用! 加local就会导入到HDFS上面! 跑了mr程序且每列用^A来区分,/n来表示换行
insert overwrite local director '/root/123456'
select ip,substr(day,1,7) as month,day
from dynamic_partition_table;
<!--mr程序在什么时候会跑呢?如果只是在屏幕显示出来,就不需要跑mr程序,但是如果涉及到聚合、移动位子、二合一等等就要跑mr程序了!-->
---------------------------------------------------------------------------------------------------------------
select关键字
语法结构
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
JOIN table_other ON expr
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
where 过滤和 having过滤有什么区别!?
where过滤是在分组前! having过滤是在分组后!
where是全局过滤! 全局过滤之后的字段进行group by之后在进行having过滤!
cluster by的查询 : select * from table_Name cluster by(fields_Name); cluster by字段是 分桶+排序 不能和distribute by、sort by 共存!
我们可以设置分桶的数 然后进行查询:set mapreduce.job.reduce=? 设置几个! 查询的时候就按照几分去分桶查!
需求 : 在按照分桶查询后的情况下 再次按照另外一个字段进行排序
首先会想到 : select * from table_Name cluster by(clusterField_Name) sort by(sortField_Name); ///报错! 因为cluster by 不能和sort by共存!
//解析 首先按照clusterField_Name进行分组,然后在按照sortField_Name进行排序!
正确做法: select * from distribute by(distrustbyField_Name) sort by(sortField_Name asc|desc);
解析 : 这里distribute by : 负责分 sort by : 负责排序
非常重要 : 当distribute by 和sort by 字段相同的时候! distribute by + sort by = cluster bu
order by 查询: 当进行order by 查询的时候! 是一个全局的分组 这时候reduce默认的只有一个! 不管这里设置的set mapreduce.num.reduce=?,这种情况下在编译期间就决定了为1
limit number : 这里设置的是多少! 就会显示多少行!
---------------------------------------------------------------------------------------------------------------------------
hive join 查询 :
Hive 中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持 LEFT SEMI JOIN 和 CROSS JOIN cross翻译: 交叉
Hive就目前来说支持等值连接查询(a.id=b.id) ,不支持非等值连接(a.id>b.id),因为hive最终会解析成mapreduce,而非等值的查询需要拿值作比较!非常麻烦!后面的版本不知道能不能开发!
注意事项: 优化join方式
1. join时 , 每次map/reduce的任务的逻辑
reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）
2.LEFT ， RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val，当a.key=b.key 时，而当 b.key 中找不到等值的 a.key 记录时也会输出:a.val, NULL所以 a 表中的所有记录都被保留了；“a RIGHT OUTER JOIN b”会保留所有 b 表的记录。
3.Join 发生在 WHERE 子句 之前 , 如果有join outer和where两个条件, 那么最好将where 的条件添加到join outer中直接过滤;
4. Join 是不能交换位置的。无论是 LEFT 还是 RIGHT join，都是左连接的。
SELECT a.val1, a.val2, b.val, c.val
FROM a
JOIN b ON (a.key = b.key)
LEFT OUTER JOIN c ON (a.key = c.key)
先 join a 表到 b 表，丢弃掉所有 join key 中不匹配的记录，然后用这一中间结果和 c 表做 join。
//注意 : 网上所说的left outer join(左外连接) 和
right outer join(右外连接) 和 left join(左链接) 和right join(右连接)都是一回事;
select * from a inner join b on a.id=b.id; //只显示对应的上的字段内容
select * from a left join b on a.id=b.id; //显示左边表的全部 , 右边表对应上的显示,对应不上的显示为null;
select * from a right join b on a.id=b.id;
select * from b right join a on b.id=a.id;//这两个显示的是一样的,都是显示右表的全部,对应上的显示数据,对应不上的显示为null;
select * from a full outer join b on a.id=b.id; //将左表与右表都进行显示,能对应的上就显示,对应不上就显示为null;
**hive中的特别join
select * from a left semi join b on a.id = b.id;
相当于select a.* from a left join b on a.id=b.id; 就是将左内连接的结果的a的数据全部显示
cross join(慎用) : 就是显示两个表的笛卡尔积 ; 左表的数量 * 右表的数量的积!
----------------------------------------------------------------------------------------------------------------------
hive的参数配置 :
1. hive的命令行:
输入$HIVE_HOME/bin/hive –H 或者 –help 可以显示帮助选项：
说明：
1、 -i 初始化 HQL 文件。
2、 -e 从命令行执行指定的 HQL
3、 -f 执行 HQL 脚本
4、 -v 输出执行的 HQL 语句到控制台
5、 -p <port> connect to Hive Server on port number
6、 -hiveconf x=y Use this to set hive/hadoop configuration variables.
2. Hive 参数配置方式
Hive 参数大全：
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
对于一般参数，有以下三种设定方式：
配置文件 （全局有效）
命令行参数 （对 hive 启动实例有效）
参数声明 （对 hive 的连接 session 有效
配置文件: 用户自定义配置会覆盖默认配置
配置文件的设定对本机启动的所有 Hive 进程都有效.
命令行参数 : 本次启动的 Session有效
参数声明 : SET 关键字设定 参数声明覆盖命令行参数，命令行参数覆盖配置文件设定
--------------------------------------------------------------------------------------------------------------------------------
Hive 自定义函数和 Transform:
1. 新建 JAVA maven 项目
2.添加 hive-exec-1.2.1.jar 和 hadoop-common-2.7.4.jar 依赖
3.写一个 java 类，继承 UDF，并重载 evaluate 方法
4.打成 jar 包上传到服务器
5.将 jar 包添加到 hive 的 classpath 命令行 : hive>add JAR /home/hadoop/udf.jar;
6.创建临时函数与开发好的 java class 关联 create
temporary function tolowercase as 'cn.itcast.bigdata.udf.ToProvince';
7.即可在 hql 中使用自定义的函数 tolowercase ip
Select tolowercase(name),age from t_test;
． Transform 实现 实现 （了解）
Hive 的 TRANSFORM 关键字 提供了在 SQL 中调用自写脚本的功能 , 适合实现 Hive 中没有的功能又不想写 UDF 的情况
----------------------------------------------------------------
----------------------------------------------------------------
Hive 特殊分隔符处理 （扩展）
hive 读取数据的机制：
首先用 InputFormat< 默认是：org.apache.hadoop.mapred.TextInputFormat >的一个具体实现类读入文件数据，返回一条一条的记录（可以是行，或者是你逻辑中的“行”）
然后利用 SerDe< 默认：org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe>的一个具体实现类，对上面返回的一条一条的记录进行字段切割 。
Hive 对文件中字段的分隔符默认情况下只支持单字节分隔符，如果数据文件中的分隔符是多字符的，如下所示：
01||zhangsan
02||lisi
可用使用 RegexSerDe 通过正则表达式来抽取字段
----------------------------------------------------------------
----------------------------------------------------------------
通过窄表往宽表中插入数据的一个语句! 将url进行宽表细分的详解:
create table ods_weblog_origin
select a.* , b.* from ods_weblog_origin a lateral view pasr_url_tuple(regexp_replace(http_referer,"\"",""),'host','path','query','query:id') b as host,path,query,query_id;
lateral view : 后面一般跟可以把一个字段变成多个字段的函数 ,然后把新增的字段作为虚拟的字段,拼在lateral view 左边的表上!
在上面进行宽表数据的插入的时候 , 我们hive有一个内置函数 叫parse_url_tuple :传入一个标准的url(http://host:port/path?query=queryid) ,这个函数会解析成 host path query queryid字段
前提必须是标准的url , 带别的符号的话就用正则给它替换掉