hive笔记（自学整理的）
第一部分：用户管理
创建用户：CREATE DATABASE XXX
查看用户：SHOW DATABASES;
关键查看用户：show databases like 'de.*'
讲解：创建一个用户就等于在物理目录下创建了一个文件，该文件是以.db结尾的，
默认的路径是：/user/hive/warehouse/zqx.db
创建用户时可以指定路径： create database XXX
location '/my/preferred/directory'
讲解：为后期维护方便，可以创建用户时添加描述内容
create database XXX
comment 'this is my first database';
在使用describe XXX 时，就会看到用户描述信息
删除用户：drop database if exists XXX --> IF EXISTS是可选的，加上可以避免数据库XXX不存在而报错
讲解：原则上hive是不允许删除一个含有表的database。
1、用户要么先删除掉该用户下所有的表，再删除该用户
2、要么在删除用户语句中加上关键字 CASCADE，它的意思是让hive自动的先删除掉用户下的所有表
drop database if exists XXX cascade;
如果某个用户被删除了，它所对应的物理路径和文件也同时被删除
第二部分：表的管理
创建表：
create table if not EXISTS employees(
emp_id string comment 'id',
name string comment 'name',
phone_number string comment 'phone',
depar_id string comment 'depart_ID'
)
comment 'employees_table'
location '/user/hive/warehouse/zqx.db/employees';
---IF NOT EXISTS 是可选的，如果表已经存在，HIVE就会忽略掉后面的执行语句，不会有任何提示。
---LOCATION是指定该表在HDFS中的URL路径。
查看某用户下的表： show tables in zqx;
查看表结构：desc employees;
查询某列：desc employees.phone_number;
修改表： alter table
拷贝已存在数据表的表结构： create table if not exists zqx.copy_table like zqx.employees;
讲解：以上创建的employees在hive中被称为“内部表”，删除该表后，表中的数据也会同时被删除。
讲解：内部表不方便和其他领域进行共享数据，假设使用pig领域进行操作数据，会用到hive领域中的数据，
可并没有给与pig使用权，我们就可以创建一个外部表指向这部分数据，就可以直接访问hive中的数据
外部表：
create external table if not exists departitions(
depart_id string comment 'depart_id',
depart_name string comment 'departition name'
)
comment 'departition name'
row format delimited fields terminated by ','
location '/user/hive/warehouse/zqx.db/departitions'
---关键字external表示该表是外部表
---外部表特点：hive认为该表并非拥有这部分数据，删除该表时，不会删除掉数据。
查询数据表是外部表还是内部表： describe extended departitions;
内部表： ... tableType:MANAGER TABLE
外部表： ... tableType:EXTERNAL TABLE
分区表：
create table departements(
depart_id string comment 'depart id',
depart_name string comment 'depart name'
)
partitioned by (acct_month string)
row format delimited fields terminated by '|'
stored as textfile;
---指定按照 acct_month 进行分区
查看是否分区表： show partitions departments;
查看指定分区：show partitions departments partition( acct_month = '201509' );
给表增加分区：alter table employees add partition( acct_month = '201509' );
表重命名： alter table employees rename to employees_new ;
对表分区的操作
1、增加表分区： alter table employees add partition(acct_month = '201509') location = 'XXXX'
partition(acct_month = '201510') location = 'XXXX'
.... ;
在同一个怎家语句中，可以添加多个分区。
2、删除表分区： alter table employees drop partition(acct_month = '201509');
增加列： alter table employees add columns( alter_1 string comment 'alter one',
alter_2 string comment 'alter two' );
讲解：字段添加中文注释报错问题：
由于编码方式不一致导致的
执行comment ''后，中文会保存到mysql中，当编码方式不一致时，保存进去的就会是乱码，所以报错。
解决1：给字段添加注释时，需要转换编码方式，设置成UTF-8
(1)修改表字段注解和表注解
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
(2) 修改分区字段注解：
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
解决2：
修改hive连接mysql的连接为utf-8
<property>
<name></name>
<value>jdbc:mysql://IP:3306/hive?createDatabaseIfNotExist=true&characterEncoding=UTF-8</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
第三部分：数据装载
讲解：目前hive中不支持行级别的数据插入、更改和删除的操作，要想表中有数据，只能通过“大量”的数据
装载操作，通过文件给数据表加载数据。
装载数据： load data local inpath '文件放置的目录' -->如果该目录不存在的话，会首先创建该目录
overwrite into table employees
partition (acct_month = '201509'); ----> 如果employees不是分区表，则省略partition子句
local关键字：如果使用了local关键字，那么“文件放置的目录”就为本地文件系统路径，数据被拷贝到目标
位置。
如果省略掉local关键字，那么路径应该是分布式文件系统中的路径，这种情况下，数据是从分
布式文件系统路径转移到目标位置（而不是拷贝）
总结： load data local ... 拷贝本地数据到分布式文件系统上的目标位置
load data ... 从HDFS系统中，将数据从一个集群HDFS中转移到另一个集群HDFS中
讲解：HIVE并不会验证用户装载的数据和表的模式是否匹配。HIVE会验证文件格式是否和表结构是否一致
第三部分：数据导出
insert overwrite local directory 'XXXX' select * from XXX;
sqlldr userid=mid/mid679@jyfx1 control=tag_file.ctl
sqlldr userid=mid/mid679@jyfx1 control=sqlldr_ora.ctl direct=true parallel=true
-- direct=true 不展示过程进度
-- parallel=true 并发
转换编码： iconv -f UTF-8 -t GB18030 t_m_make_tag_file.txt1 -o t_m_make_tag_file.txt
第四部分：数据的查询
LIMIT语句：限制查询记录的条数
CASE ... WHEN ... THEN语句：与ORACLE用法一致
讲解：什么情况下HIVE可以避免进行MapReduce
查询原理：假设查询employees表，就是Hive访问employees表对应的存储路径下的文件。
1、只是以分区字段做为查询条件的情况，不会触发MapReduce，这种情况叫：本地模式
例如：select * from departments where acct_month = '201509' --> acct_month是分区字段
2、属性 hive.exec.mode.local.auto 的参数设置为true，Hive会一直默认使用本地模式执行其他操作
除以上两种情况以外，Hive执行操作，都会触发MapReduce来执行所有查询。
讲解：Hive会对每一个JOIN连接对象启动一个MapReduce任务。
讲解：关于Hive的执行顺序，例如：
select A.ymd,A.price_close,B.price_close,C.price_close
from stocks A JOIN stocks B ON A.ymd = B.ymd
JOIN stocks C ON A.ymd = C.ymd
where A.symbol = 'AAPL'
and B.symbol = 'IBM'
and C.symbol = 'GE';
例子中，会首先启动一个MapReduce Job对表A和表B进行连接操作，
然后再启动一个MapReduce Job将第一个MapReduce Job的输出结果和表C进行连接操作
Hive的执行顺序是按照从左往右执行的。
关于JOIN连接
1、优化：在上面的SQL语句中，表A、B、C在做JOIN关联时，所使用的关联字段都是 ymd ，的这种情
况。（即：所使用的关联字段是相同的字段）
讲解：当3个或者更多个表进行JOIN关联时，如果每个ON自居都使用相同的关联字段的话，就只会产生
一个MapReduce 。
2、关联原理：Hive在执行SQL脚本时，会假定查询中最后一个表是数据量最大的，在对每一行记录进行关联操作时，Hive会将其他的表缓存起来，然后扫描最后那张表。所以要将数据量最大的表放在最后。
3、Hive的标记机制：在编写SQL脚本时，并非总是要将最大的表放置在查询语句的最后，因为Hive提供了一个“标记”，用来指定哪个表的数据量是最大的，Hive就会首先扫描哪个表。
例如： select /*+STREAMTABLE(s)*/ s.ymd , s.symbol , d.price_close
from stocks s JOIN dividends d
ON s.ymd = d.ymd;
数据类型转换： double 类型 ---> string类型 cast(cast(user_id as bigint) as string)
数据类型：
| 数据类型 | 所占字节 | 开始支持版本 |
| TINYINT | 1byte，-128 ~ 127 |
|
| SMALLINT | 2byte，-32,768 ~ 32,767 |
|
| INT | 4byte,-2,147,483,648 ~ 2,147,483,647 |
|
| BIGINT | 8byte,-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807 |
|
| BOOLEAN |
|
|
| FLOAT | 4byte单精度 |
|
| DOUBLE | 8byte双精度 |
|
| STRING |
|
|
| BINARY |
|
从Hive0.8.0开始支持 |
| TIMESTAMP |
|
从Hive0.8.0开始支持 |
| DECIMAL |
|
从Hive0.11.0开始支持 |
| CHAR |
|
从Hive0.13.0开始支持 |
| VARCHAR |
|
从Hive0.12.0开始支持 |
| DATE |
|
从Hive0.12.0开始支持 |
