HIVE 基础-->安装,建表 (前两天)

mysql安装

1.安装步骤

#更新集群时间
./ssh_root.sh ntpdate time1.aliyun.com

[hadoop@nn1 tmp]$ cd download/
1.#下载yum原
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

2.#安装mysql 源
rpm -ivh mysql57-community-release-el7-11.noarch.rpm

3.#查看是否安装成功
yum repolist enabled | grep "mysql.*-community.*"

4.#用 yum 命令安装mysql
yum install mysql-community-server

5.#查看MYSQL配置文件加载顺序：
mysqld --help --verbose|grep -A1 -B1 cnf 

6.#修改/etc/my.cnf 配置文件内的文件目录 修改mysql的配置文件
[root@nn1 yum.repos.d]# vim /etc/my.cnf
 #data目录
 datadir=/data/mysql/data
 #严格模式
 sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 log-error=/data/mysql/log/mysqld.log

7.#创建mysql 文件目录
[root@localhost jar]# mkdir -p /data/mysql/data
[root@localhost jar]# mkdir -p /data/mysql/log

7.1#生成首次登录随机密码
mysqld --initialize

8.查看密码
[root@nn1 log]# cat /data/mysql/log/mysqld.log
 A temporary password is generated for root@localhost: 7Scmvp_<4hW7

9.#修改mysql 文件目录所有者为 mysql 用户
    sudo chown -R mysql:mysql /data/mysql
10.#启动mysql 
    sudo systemctl start mysqld.service
10.1#查看服务
[root@nn1 log]# ps aux | grep mysqld.service 
11.#用生成的随机密码登录mysql
    mysql -uroot -p'7Scmvp_<4hW7'
12.#设置密码
mysql> set password=PASSWORD('000000'); 
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql>exit #退出

2.采坑

#采坑：这句命令输出入错了，需要删除/mysql/data目录重新生成就好了
8.#生成首次登录随机密码
    mysqld --initialize
    使用 rpm -e --nodeps PACKAGE
    出现错误：
Error: Trying to remove “yum”, which is protected

问题分析：
应该是要卸载的依赖项包含yum或正在被yum使用，无法卸载。

解决办法 ：
只卸载libstdc++-4.4.7-11.el6.x86_64.rpm，忽略依赖项，使用“–nodeps”参数

#密码
     A temporary password is generated for root@localhost: ra!;-w3r%g!F

3.关闭并重启mysql

# 关闭mysql
sudo systemctl stop mysqld.service
# 重启mysql
sudo systemctl start mysqld.service

hive安装

1.基本安装

1.#上传文件
[hadoop@nn1 upload]$ rz -bye #二进制形式上传
2.#解压
[hadoop@nn1 upload]$ sudo tar -zxvf apache-hive-2.1.1-bin.tar.gz -C /usr/local
hadoop密码:123456
3.#设置权限
[hadoop@nn1 upload]$ cd /usr/local
[hadoop@nn1 local]$ sudo ln -s apache-hive-2.1.1-bin hive
[hadoop@nn1 local]$ sudo chown -h hadoop:hadoop hive   
4.#设置环境变量 
[hadoop@nn1 local]$ sudo vim /etc/profile
export HIVE_CONF_DIR=/usr/local/hive/conf
5.#更新配置
[hadoop@nn1 local]$ source /etc/profile
6.#查看环境变量
[hadoop@nn1 local]$ echo $PATH
/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/usr/java/jdk1.8.0_144/bin:/usr/java/jdk1.8.0_144/bin:/usr/local/hadoop/bin:/usr/local/hadoop/sbin:/usr/local/hbase/bin:/usr/local/hive/bin:/usr/local/zookeeper/bin:/home/hadoop/.local/bin:/home/hadoop/bin:/usr/java/jdk1.8.0_144/bin:/usr/java/jdk1.8.0_144/bin:/usr/local/hadoop/bin:/usr/local/hadoop/sbin:/usr/local/hbase/bin:/usr/local/hive/bin:/usr/local/zookeeper/bin:/usr/java/jdk1.8.0_144/bin:/usr/java/jdk1.8.0_144/bin:/usr/local/hadoop/bin:/usr/local/hadoop/sbin:/usr/local/hbase/bin:/usr/local/hive/bin:/usr/local/zookeeper/bin
7.#hive配置文件
[hadoop@nn1 local]$ cd /usr/local/hive/conf
[hadoop@nn1 conf]$ ll
# 复制hive-site.xml到 /usr/local/hive/conf
[hadoop@nn1 conf]$ sudo cp /tmp/upload/hive-site.xml ./ 
# 修改hive-env.sh文件
[hadoop@nn1 conf]$ mv hive-env.sh.template hive-env.sh 
#修改 hive-log4j2.properties
[hadoop@nn1 conf]$ sudo mv hive-exec-log4j2.properties.template hive-log4j2.properties
#删除冲突的log4j
[hadoop@nn1 upload]$ sudo rm -f /usr/local/hive/lib/log4j-slf4j-impl-2.4.1.jar

2.在mysql中创建hive用的数据库和hive用户

1.#--登录mysql
mysql -uroot -p'000000';
2.#--在mysql中创建hive_meta数据库
mysql> create database hive_meta default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

3.#--创建hive用户 (这两句分别执行) 
CREATE USER 'hive'@'%' IDENTIFIED BY '000000';
CREATE USER 'hive'@'loaclhost' IDENTIFIED BY '000000';

4.#--给hive用户增加hive_meta数据库权限 (这两句分别执行)
#把hive_meta数据库 所有权限都给 hive和本地用户
grant all privileges on hive_meta.* to 'hive'@'%' identified by '000000';
grant all privileges on hive_meta.* to 'hive'@'localhost' identified by '000000';

5.#--更新
flush privileges;
mysql> quit;

3.拷贝mysql驱动jar 到/usr/local/hive/lib/

!!!!!#此处一定需要注意一下，Hive对MySQL的jar包有特定要求！我们之前使用的是MySQL的5.1.6的jar包，但是上传至Hive目录下的MySQLjar是5.1.35的！一定要注意！

#上传mysql连接的jar包
[hadoop@nn1 upload]$ sudo cp /tmp/upload/mysql-connector-java-5.1.35.jar /usr/local/lib/
#创建hive目录
[hadoop@nn1 lib]$ hadoop fs -mkdir -p /hive/warehouse
[hadoop@nn1 lib]$ hadoop fs -chmod -R 777 /hive
[hadoop@nn1 lib]$ hadoop fs -ls /hive
#hive初始化mysql 初始化mysql中的表
[hadoop@nn1 lib]$ schematool -dbType mysql -initSchema
账号:hive 密码:000000

4.启动HIVE(远程模式)

1.#先启动matesotre服务(前台进程)，再启动hive client
[hadoop@nn1 lib]$ nohup hive --service metastore > /dev/null 2>&1 &
2.#启动hive
[hadoop@nn1 lib]$ hive

5.测试hive

1)新建数据库

# 新建数据库
hive (default)> create database db1;
# 查看这个数据库的元数据信息
hive (default)> show create database db1;
# 使用某个数据库
hive (default)> use db1;
#查看数据库
hive (default)> show create database default;
OK
createdb_stmt
CREATE DATABASE `default`
COMMENT
  'Default Hive database'
LOCATION
  'hdfs://ns1/hive/warehouse' #记录的数据库所在位置
Time taken: 1.01 seconds, Fetched: 5 row(s)

2)新建表

#-- 创建一张表
hive (default)> create table user_info(id int,name string);
#-- 显示表结构
desc user_info;
#-- 显示表结构详细信息
show create table user_info;
#添加数据
 #第一种方式 单条插入(不建议)
    hive (default)> insert into table user_info values (1,'tom');
 #第二种方式 load方式从指定好分隔符文件中读取
#1.[hadoop@nn1 tmp]$ vim file1
    #(分隔符指定好)  ^A:  ctrl+v+a   hive默认分隔符等于/001
    #hive可以进行单条插入,但是需要执行mapreduce,先存到临时表,再复制到现有表
#2.插入数据
    # i键按下
    2^A赵文明
    3^A孙建国
    4^A王晓华
    5^A赵佳佳
hive (default)> load data local inpath '/tmp/file1' into table user_info;
#3.查询数据插入成功
hive (default)> select * from user_info;
OK
user_info.id    user_info.name
1       tom
2       赵文明
3       孙建国
4       王晓华
5       赵佳佳
#4.查看表结构 desc 表名;
hive (default)> desc user_info;
OK
col_name        data_type       comment
id                      int                                         
name                    string                                      
Time taken: 0.042 seconds, Fetched: 2 row(s)
#5.展示表元数据信息 show create table 表名;
hive (default)> show create table user_info;
OK
createtab_stmt
CREATE TABLE `user_info`(
  `id` int, 
  `name` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://ns1/hive/warehouse/user_info'
TBLPROPERTIES (
  'numFiles'='2', 
  'numRows'='1', 
  'rawDataSize'='5', 
  'totalSize'='54', 
  'transient_lastDdlTime'='1614509720')
Time taken: 0.081 seconds, Fetched: 17 row(s)

6.HIVE的组织形式

1.基本概念和关系型数据库类似，如：库，表，列，分区。按照数据组织粒度由大到小说明：
 1）数据库 Databases Database起到命名空间的功能，避免表，视图等定义的混乱，同时也为权限的定义及分配提供 良好的隔离。在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹。
 2）表 Tables 每个表包含一个主题信息，有多个属性字段组成的二维数据集合，一个数据库可以包含多张表。
 #hive 在hdfs中表现为db目录下的一个文件夹。 
 #1.可以分区存储  --> hive目录拆分
 #2.可以分桶存储 --> hive的数据文件拆分
3）分区 Partitions 每个表可以有一个或多个分区键值，是数据的存储单元，可以按分区key划分查询数据范围，有效提高查询效率。
4）桶 Buckets 表分区还可以按照某几列hash进行划分，是更细粒度的数据范围。桶表就是对应不同的文件。

7.常用查询

-- 数据库信息 (数据库id，数据库名称，数据库对应的hdfs存储位置)
select * from DBS;

-- 数据表信息（数据表id，数据库id，存储id，表名）
select * from TBLS;

-- 存储信息   （存储id，表对应的hdfs存储位置）
select * from SDS;

-- 查询指定库下的表名称
select t1.name as dbname,t2.TBL_NAME,t2.SD_ID 
from DBS t1 inner join TBLS t2 on t1.DB_ID=t2.DB_ID and t1.`NAME`='db1';

-- 查询指定库下指定表的目录信息(元数据信息)

-- DBS数据库相关
 -- TBLS 表相关
 -- SDS 存储位置相关
select t4.dbname, t4.DB_LOCATION,t4.TBL_NAME,t4.TBL_LOCATION from
(
select t1.name as dbname, t1.DB_LOCATION_URI as DB_LOCATION,t2.TBL_NAME,t3.LOCATION as TBL_LOCATION from DBS t1 
    inner join TBLS t2 on t2.DB_ID=t1.DB_ID 
    inner join SDS t3 on t3.SD_ID=t2.SD_ID 
) t4
where t4.dbname='db1' AND 
            t4.TBL_NAME like 'user%';

8.hive数据类型

简单类型

#string （后面常用string）
    #用单引号（'）或双引号（"）定义，这个类型是以后我们定义字符串的常用类型。

varchar    
        #空格会作为字符串的一部分，影响字符串的比较。

char
        char是固定长度的，最大长度255，#尾部的空格不影响字符串的比较。

日期类型

#date 类型只能存储 yyyy-mm-dd类型的数据  timestap 可存储 年月日时分秒
#timestap 类型的数据插入到 date 里面,数据不会报错,但是会插不进去

#将日期覆盖插入的date_dual******************（将目录下的所有都删掉，再写入）
insert overwrite table date_dual values('2017-05-31');

#获取当前timestamp：
        current_timestamp()    --返回值: timestamp
#获取当前日期：
        current_date()         --返回值：date

#日期格式化 date_format
#当前日期格式化
    select date_format(current_date(),'yyyy-MM-dd HH:mm:ss');
#当前时间格式化
    select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');
#字符串格式化
    select date_format('2018-01-30 14:23:40', 'yyyy-MM-dd');

#Unix时间戳
--获取当前timestamp的Unix时间戳
select unix_timestamp(current_timestamp);

#获取指定字符串的Unix时间戳
--获取指定字符串的Unix时间戳
select unix_timestamp('2018-07-05 09:00:00');

#【Unix时间戳】转【时间字符串】 
--转换成 yyyy-MM-dd HH:mm:ss
select from_unixtime(1530755469);

--转换成指定格式的字符串
select from_unixtime(1530755469, "yyyy-MM-dd");

复杂类型

--建表语句
CREATE TABLE complex
(
id int,
struct_col struct<name:string,country:string>,
array_col array<STRING>,
map_col map<STRING,STRING>,
union_col map<STRING,array<STRING>>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' #指定每一列的分隔符
COLLECTION ITEMS TERMINATED BY '-' #array的分隔符
MAP KEYS TERMINATED BY ':'; #map的分隔符

--插入数据
insert overwrite table complex
select
1,
NAMED_STRUCT('name','赵文明','country','中国') as struct_col,
array('a','b','c') as array_col,
map('China','cn','Germany','DE') as map_col,
map('month',array('一月','二月','三月','四月')) as union_col;

--取数据
#--查询struct
select struct_col.name from complex;

#--查询数组第一个元素
select array_col[0] from complex;

#--查询map中key对应的value值
select map_col['english'] from complex;

#--查询复杂结构map<STRING,array<STRING>>中key对应的value值
select union_col['english'] from complex;

#--查询复杂结构map<STRING,array<STRING>>中key对应的value值（数组）中的第一个元素
select union_col['english'][0] from complex;

STRUCT(NAMED_STRUCT)

//NAMED_STRUCT (kv键值对形式)

STRUCT类似于java的 自定义类型 使用，Hive中定义的struct类型也可以使用 对象.属性 来访问。从文件加载数据时，文件里的数据分隔符要和建表指定的一致。
定义struct 可以使用下面的函数 
1. struct(val1, val2, val3, ...) ，只有字段值；
2. named_struct(name1, val1, name2, val2, ...)，带有字段名和字段值； <-- 推荐使用 可以构成 key val 结构

ARRAY

ARRAY表示一组相同数据类型的集合，下标从零开始，可以用下标访问。如：arr[0]
定义 ARRAY 可以使用下面的函数 ：
array('val1','val2','val3')

MAP

MAP是一组键值对的组合，可以通过KEY访问VALUE，键值之间同样要在创建表时指定分隔符。
map('key1':'val1','key2':'val2')

CREATE TABLE complex
(
id int,
struct_col struct<name:string,country:string>,
array_col array<STRING>,
map_col map<STRING,STRING>,
union_col map<STRING,array<STRING>>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':';
-------------------------------------------------------------------
--这个子句表明hive使用字符‘,’作为列分隔符 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
--这个子句表明hive使用字符 ‘-’ 作为集合元素间分隔符(一个字段各个item的分隔符)
COLLECTION ITEMS TERMINATED BY '-' 
--这个子句表明hive使用字符作为map的键和值之间分隔符
MAP KEYS TERMINATED BY ':'

-------------------------------------------------------------------
//结构化数据取值
struct取值方法    
// select struct_col.country from complex;
array
//select array_col[2] from complex;
map
//select map_col['english11'] from complex;

文件信息查询

hive (xsmpf)> dfs -ls /hive/warehouse/xsmpf.db/complex

数据类型	大小	范围	示例
TINYINT	1byte	-128 ~ 127	100Y
SMALLINT	2byte	-32,768 ~ 32,767	100S
INT/INTEGER	4byte	-2,147,483,648 ~ 2,147,483,647	100
BIGINT	8byte	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	100L
FLOAT	4byte	单精度浮点数	3.1415926
DOUBLE	8byte	双精度浮点数	3.1415926
DECIMAL	-	高精度浮点数	DECIMAL(9,8)
BOOLEAN	-	布尔型，TRUE/FALSE	true
BINARY	-	二进制类型	-

1.运算符操作符

运算符	操作数	描述
A = B	所有基本类型	如果表达A等于表达B，结果TRUE ，否则FALSE。
A != B	所有基本类型	如果A不等于表达式B表达返回TRUE ，否则FALSE。
A < B	所有基本类型	TRUE，如果表达式A小于表达式B，否则FALSE。
A <= B	所有基本类型	TRUE，如果表达式A小于或等于表达式B，否则FALSE。
A > B	所有基本类型	TRUE，如果表达式A大于表达式B，否则FALSE。
A >= B	所有基本类型	TRUE，如果表达式A大于或等于表达式B，否则FALSE。
A IS NULL	所有类型	TRUE，如果表达式的计算结果为NULL，否则FALSE。
A IS NOT NULL	所有类型	FALSE，如果表达式A的计算结果为NULL，否则TRUE。
A LIKE B	字符串	TRUE，如果字符串模式A匹配到B，否则FALSE。关系型数据库中的like功能。
A RLIKE B	字符串	如果A任何子字符串匹配Java正则表达式B；否则FALSE。
A REGEXP B	字符串	等同于RLIKE.

-- 模糊查询的
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english'] like '%a%';

-- 正则匹配的
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english'] RLIKE '\\S';

-- 判断为null
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english1'] is null;

-- 判断不为空
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english'] is not null;

2.算数运算符

运算符	操作	描述
A + B	所有数字类型	A加B的结果
A - B	所有数字类型	A减去B的结果
A * B	所有数字类型	A乘以B的结果
A / B	所有数字类型	A除以B的结果
A % B	所有数字类型	A除以B.产生的余数
A & B	所有数字类型	A和B的按位与结果
A | B	所有数字类型	A和B的按位或结果
A ^ B	所有数字类型	A和B的按位异或结果
~A	所有数字类型	A按位非的结果

3.逻辑运算符

运算符	操作	描述
A AND B	boolean	TRUE，如果A和B都是TRUE，否则FALSE。
A && B	boolean	类似于 A AND B.
A OR B	boolean	TRUE，如果A或B或两者都是TRUE，否则FALSE。
A || B	boolean	类似于 A OR B.
NOT A	boolean	TRUE，如果A是FALSE，否则FALSE。
!A	boolean	类似于 NOT A.

4.复杂的运算符

运算符	操作	描述
A[n]	A是一个数组，n是一个int	它返回数组A的第n个元素，第一个元素的索引0。
M[key]	M 是一个 Map<K, V> 并 key 的类型为K	它返回对应于映射中关键字的值。
S.x	S 是一个结构体	它返回S的x字段

5.聚合函数

返回类型	函数	描述
BIGINT	count(*), count(expr),	count(*) - 返回检索行的总数。
DOUBLE	sum(col), sum(DISTINCT col)	返回该组或该组中的列的不同值的分组和所有元素的总和。
DOUBLE	avg(col), avg(DISTINCT col)	返回上述组或该组中的列的不同值的元素的平均值。
DOUBLE	min(col)	返回该组中的列的最小值。
DOUBLE	max(col)	返回该组中的列的最大值。

if函数 类似于case when 函数

-- 模糊查询的
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english'] like '%a%';

-- 正则匹配的
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english'] RLIKE '\\S';

-- 判断为null
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english1'] is null;

-- 判断不为空
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english'] is not null;

hive操作sql

hive -e 'sql语句';   -- 执行SQL语句（hql语句）
hive -f sql文件      -- 执行SQL文件（hql语句）

-- 将集群上的user_install_status前100条写入到文件
hive -e "use panniu;select * from user_install_status where aid >='8d304' limit 100;" > ./data.txt 

--将导出的100条data数据放到表的hdfs目录
hadoop fs -put data.txt /hive/warehouse/panniu.db/user_install_status_limit

9.数据库DDL 语句

--创建语句
create database  IF NOT EXISTS 数据库的名称
--删除语句
DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
--清空语句
TRUNCATE TABLE
--修改语句
ALTER DATABASE/SCHEMA, TABLE, VIEW
--查看创建语句
SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE
--查看结构语句
DESCRIBE DATABASE/SCHEMA, table_name, view_name

--创建带有属性的数据库
create database  IF NOT EXISTS 数据库的名称
comment '数据库的描述'  
with dbproperties ('key1'='val1','key2'='val2','key3'='val3');

--显示数据库的元数据信息
show create database 数据库名称;

操作示例：

show databases;

默认有一个default名字的数据库。

--创建一个名字为test的数据库
create database test;

--显示所有数据库
show databases;

--如果已经存在会报错
create database test;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database test already exists

--可以增加if not exists 达到不存在再创建
create database if not exists test;

--describe命令（简写 desc）查看数据库定义元数据相关信息，可以查看数据location地址
desc database test;

--显示创建语句
show create database test;

--使用数据库
use hainiu;

--查看当前数据库
select current_database();

--切换到test数据库
use test;
select current_database();

修改数据库

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; 

alter database 数据库名称 set DBPROPERTIES ('key'='val');

数据库的元数据一旦建立是不能修改的;

删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

-- 当数据库有表存在的时候 默认 是 RESTRICT 不让你删除数据库的
drop database 数据库名称 RESTRICT; 
drop database hn19 CASCADE; --> 如果强制删除需要加上 CASCADE 级联删除 

删除数据库是会校验数据库下是否有表存在，

如果在RESTRICT(限制) 模式有表存在则不能删除，修改为CASCADE(关联)模式可以级联删除数据库和数据库下所有表。
删除操作要谨慎，删除前最好做一下检测和备份。

DROP database testRESTRICT;
DROP database CASCADE; --级联删除 不建议

10.数据表的相关操作

创建表语法

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 DELIMITED row_format](行数据格式设置) 
  [STORED AS file_format](设置表的数据文件存储类型)
  [LOCATION hdfs_path](HDFS地址设置)

hive三种表

hive表类型上可以分以下三种：
    1）临时表 CREATE TEMPORARY：临时表是session内可见，将数据临时存在scratch目录，session退出后表和       数据自动删除。--存储临时数据用的

    2）内部表 CREATE：不指定类型默认为内部表，内部表的特点是删除表时会同时删除表数据

    3）外部表 CREATE EXTERNAL：创建表时指定数据目录，删除表时不会删除表数据
    --现有数据后有表  删除表对信息不影响

    --创建外部表的语法格式
    CREATE EXTERNAL TABLE 表名(字段名 字段类型)
    COMMENT '表描述信息'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '指定分隔符' 
    STORED AS TEXTFILE --指定输入输出格式
    LOCATION '<hdfs_location>'; --在hdfs的位置

    --创建外部表举例, 例如:
    create external table ext_test(word string,num int) 
    COMMENT '创建一张测试外部表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION 'hdfs://ns1/hive/warehouse/hn19_db1.db/ext_test';

创建表的时候注意表的格式
--ROW FORMAT DELIMITED
--参数
[FIELDS TERMINATED BY char] # 设置字段的分隔符 --用这个就行
[COLLECTION ITEMS TERMINATED BY char] # 设置集合的分隔符
[MAP KEYS TERMINATED BY char] # 设置map的分隔符
[LINES TERMINATED BY char] # 设置一行的分隔符

--序列化与反序列化设置
SERDE serde_name 
[WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

--比如
ROW FORMAT DELIMITED FIELDS TERMINATED BY char'\t' --默认是 '\001'

--STORED AS(hive表的数据文件格式)
1) TEXTFILE : 最普通的文件存储格式，内容是可以直接查看 用来保存文本数据 --textfile
2) SEQUENCEFILE : 包含键值对的--二进制的文件存储格式，支持压缩，可以节省存储空间 key val形式
3) RCFile : 是列式存储文件格式，适合压缩处理。对于有成百上千字段的表而言，RCFile更合适。--列式存储
    --列数存储 有压缩(省空间)
3.1)ORCFILE hive专属默认的 有索引有压缩--最好的一种存储格式

针对每种类型又有自己不同的数据格式化操作和序列化操作
INPUTFORMAT 指定输入数据由哪个数据格式化类来处理
OUTPUTFORMAT 指定输出数据由哪个数据格式化类来处理
textfile,sequencefile和rcfile的三种存储格式的本质和区别

hive索引操作

1）建立索引

在指定列上建立索引，会产生一张索引表（Hive的一张物理表），里面的字段包括，索引列的值、该值对应的HDFS文件路径、该值在文件中的偏移量;
hive的索引是需要手动进行维护的

2）利用索引查询数据

1. 在执行索引字段查询时候，首先额外生成一个MR job，根据对索引列的过滤条件，从索引表中过滤出索引列的值对应的hdfs文件路径及偏移量，输出到hdfs上的一个文件中。
2.然后根据生成的临时文件中的hdfs路径和偏移量，筛选原始input文件，生成新的split,作为整个job的split,这样就达到不用全表扫描的目的。
3.每次查询时候都要先用一个job扫描索引表，如果索引列的值非常稀疏，那么索引表本身也会非常大；
索引列不适合离散字段。

3）更新索引

索引表不会自动rebuild，如果表有数据新增或删除，那么必须手动rebuild索引表数据；

-- 语法
create index 索引名称 on table 需要创建索引表的名称(需要给表的哪个列创建索引) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
with deferred rebuild IN TABLE 这张表的索引表的名称;

-- 建立索引
create index uis_country_index1 on table user_install_status(country) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
with deferred rebuild IN TABLE index1;

-- 更新索引数据
alter index 索引名称 on 表名 rebuild;

--自动使用索引
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

-- 开启自动索引
SET hive.optimize.index.filter=true; --是否采用自动索引 
SET hive.optimize.index.filter.compact.minsize=0; --压缩索引自动应用的最小输入大小，默认是5368709120字节

-- 查看索引
SHOW INDEX on 表名;    

 SHOW INDEX on user_info;
索引名称                 查询的表名称             索引字段                    索引表名称   
index_aid               user_info               id                      index_table_user 
-- 删除索引
DROP INDEX 索引名 on 表名;  

--建完索引，索引表是空的。
索引表有三个字段：
aid：索引字段
_bucketname：索引字段的值在user_install_status 表的目录下的哪个文件里
_offsets：索引字段的值在文件的哪个行字节开头

设置当前用户后面显示数据库名称 mpf(user_info)

也可通过 hive.cli.print.current.db 参数将当前数据打印到CLI提示符
set hive.cli.print.current.db=true;
也可以直接配置到hive-site.xml中永久生效，set命令保障当前session生效

11.HIVE表优化

hive表结构优化措施

1.分区

--创建分区表的好处是查询时，不用全表扫描，查询时只要指定分区，就可查询分区下面的数据。
--分区表可以是内部表，也可以是外部表。
--表分区其实本质上就是将以前存储在一个目录下的数据现在修改为存储在不同的目录下
--partitioned by (字段名 字段类型)
--分区表在使用的时候需要将分区创建好之后再进行数据的操作

分区表sql查询语句

select t1.`NAME`, t2.TBL_NAME,t4.PART_NAME, t3.LOCATION from DBS t1, TBLS t2 , SDS t3 , `PARTITIONS` t4 
where t1.DB_ID=t2.DB_ID and 
            t4.SD_ID = t3.SD_ID AND 
            t2.TBL_ID = t4.TBL_ID and 
            t1.`NAME` = 'dbname' AND 
      t2.TBL_NAME like '%tablename' 

--建表格式
-- EXTERNAL 代表外部表                                  
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] 
-- 表所对应的hdfs目录
  [LOCATION hdfs_path]  

1.1分区表使用:

1.创建分区表 这里分区字段不能和表中的字段重复。

CREATE EXTERNAL TABLE student_par(
name STRING
)
PARTITIONED BY (age int,sex STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/xsmpf/hive/student_par';

经常会用到这连个字段进行查询的时候我们才会把数据按照这两个字段的值进行分区

2.给分区表添加一个分区

--添加partition
alter table 表名 add IF NOT EXISTS partition(分区字段值) location '分区名';
alter table student_par add IF NOT EXISTS partition(age=10,sex='boy') location '10/boy'; <-- 指定HDFS的目录格式 不能乱
# SQL : select * from student_par where age = 10 and sex = 'boy' --> 全表扫描 /10/boy

alert table student_par add partition(age=10,sex='W') location '10/W';

add partitions的时候，如果不指定location路径就给你创建一个

经验 : 一定要指定我们的分区的location 如果不指定的话会破坏分区格式

--添加partition
alter table student_par add IF NOT EXISTS partition(age=11,sex='girl');

分区表的数据要放在分区中，否则是查询不到。

给分区表添加多个分区

--再加两个分区
alter table student_par add IF NOT EXISTS partition (age=11,sex='boy') location '11/boy' partition (age=11,sex='girl') location '11/girl';

1）添加单个partition 语法格式

--在表中添加单个partition，相当于hdfs:'/……/表名/20141117/00'
alter table 分区表 add IF NOT EXISTS partition(分区字段='值',分区字段='字段') location '指定分区在HDFS上目录结构';

2)添加多个partition 语法格式

--在表中添加多个partition
--相当于hdfs:'/……/表名/20141117/00'
--相当于hdfs:'/……/表名/20141117/01'
alter table par_test add partition(day='20141117',hour='00') location '20141117/00' partition(day='20141117',hour='01') location '20141117/01';

4）查看表分区

show partitions tableName;

5）删除partition

alter table 表名 drop if exists partition(字段1='值1',字段2='值2');

3.导入数据：

insert overwrite table 分区表名字 select * from person;

分区就是把某个字段提出来 单独建一个表

2.分桶

桶表抽样

桶表抽样的语法
table_sample: TABLESAMPLE (BUCKET x OUT OF y [ON colname])  
--select * from buckets_table tablesample(bucket 11 out of 42 on country)

结论：
        如果桶表分桶字段和抽样字段不一致，扫描全表
        如果表不是桶表，抽样扫描全表
        如果桶表分桶字段和抽样字段一致，不扫描全表

分桶语句

--创建桶表的语法格式
CREATE [EXTERNAL] TABLE buckets_table(
col ....)
COMMENT 'This is the buckets_table table'
[PARTITIONED BY (`dt` string)]
CLUSTERED BY(col1) [SORTED BY(col2 [asc|desc])] INTO 2 BUCKETS
[LOCATION '.....']

CREATE EXTERNAL TABLE user_install_status_buckets(
`aid` string, 
`pkgname` string, 
`uptime` bigint, 
`type` int, 
`country` string, 
`gpcategory` string)
COMMENT 'This is the buckets_table table'
PARTITIONED BY (`dt` string) -- 按照dt字段分区
CLUSTERED BY(country)  -- 按照country字段分桶
SORTED BY(uptime desc) INTO 42 BUCKETS --桶内数据按照uptime降序 , 装进42个桶
LOCATION 'hdfs://ns1/user/xsmpf/hive32/user_install_status_buckets';

Hive 0.6.0以前需要设置以下参数，但当前版本不需要设置也可以分桶。

--可以根据bucket的个数的设置reducer的数量
set hive.enforce.bucketing=true;
--有排序的桶需要设置一下
set hive.enforce.sorting=true;

wc -l file2 查看fiole2的数据数量
小结 :  分区表 是对表目录的优化 分桶表是对表数据的优化

3. hive 的数据文件格式优化措施

1. 没有增加任何优化措施的数据格式 textfile

优点 : 简单 直接
缺点 : 占地空间大 没有索引查询数据慢

2. 对数据格式的优化 ORC File

ORC File，它的全名是Optimized Row Columnar (ORC) file，其实就是对RCFile做了一些优化。
据官方文档介绍，这种文件格式可以提供一种高效的方法来存储Hive数据。
它的设计目标是来克服Hive其他格式的缺陷。
ORC专门为hive定义的一种数据格式; hive存储的数据 --> HDFS ORC特定 让HDFS的数据能够更快速的读取

特点 : 针对 textfile 没有压缩 没有索引的特点 进行了一波优化

有压缩  有索引

问题 : 数据是压缩 所以我们不能直接查看 只能通过 select

问题 : 数据是压缩 所以我们不能直接查看 只能通过 select

如果不想通过select --> orc struct 结构  --结构文件的读取

缺点:

--orc 优点

--如何进行orc表的创建
在创建表的语句中加入 stored as orc --partitioner后面

gzip * 压缩文件

通过hive 命令分析orc文件

hive --orcfiledump 文件路径 查询文件结构信息

创建orc表
 --根据建表语句，创建user_install_status_other表
 CREATE EXTERNAL TABLE `user_install_status_other`(
`aid` string COMMENT 'from deserializer', 
`pkgname` string COMMENT 'from deserializer', 
`uptime` bigint COMMENT 'from deserializer', 
`type` int COMMENT 'from deserializer', 
`country` string COMMENT 'from deserializer', 
`gpcategory` string COMMENT 'from deserializer')
PARTITIONED BY (`dt` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION 'hdfs://ns1/user/xsmpf/user_install_status_other'
TBLPROPERTIES ('orc.compress'='SNAPPY', 'orc.create.index'='true');

如何往textfile 文件中导入压缩格式的文件？

-- 1）先设置压缩
--设置hive输出压缩
set hive.exec.compress.output=true;
set mapred.output.compress=true; 
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; 
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; 
--2）再导入数据
insert overwrite table user_install_status_gz 
select
aid,
pkgname,
uptime,
type,
country,
gpcategory
from panniu.user_install_status
where dt='20141228'; 

3.对数据格式的优化 avro file

orc是结构化数据.会产生下标越界, avro是json格式,不会产生这种

1.创建avro scheme
{
    "type": "record",
    "name": "RunRecord",
    "namespace": "com.hainiu",
    "fields": [{
            "name": "word",
            "type": "string"
        }, {
            "name": "num",
            "type": "long"
        }
    ]
}

2.上传至指定目录
[xs_mapengfei_01@op ~]$ hadoop fs -mkdir -p /user/xsmpf/hive32/config   
[xs_mapengfei_01@op ~]$ hadoop fs -put ./avro.schema /user/xsmpf/hive32/config/
[xs_mapengfei_01@op ~]$ hadoop fs -ls /user/xsmpf/hive32/config/

3.--根据avro.schema.url 对应的目录下的schema文件，创建与文件格式相对应的表结构
CREATE EXTERNAL TABLE IF NOT EXISTS word_avro
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES ('avro.schema.url'='/user/xsmpf/hive32/config/avro.schema')
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION '/user/xsmpf/hive32/word_avro';

4.如果新增字段就改scheme文件就行了.但是新加的字段要有默认值,否则会报错