1、数据类型和文件格式
数据类型
创建表时需要指定字段的数据类型，hive支持一些集合数据类型，STRUCT、MAP和ARRAY：
- STRUCT：STRUCT <
<
<script type="math/tex" id="MathJax-Element-3"><</script>first:INT, second:STRING>
>
struct(5, ‘jack’)
通过字段名.first 和 字段名.second访问内容 - MAP：MAP<<STRING, FLOAT>
>
map(‘first’, 5.2)
通过字段名[‘first’]获取数据 - ARRAY：ARRAY<<STRING>
ARRAY(‘jack’, ‘rose’)
通过字段名[0]来访问
建表例子：
CREATE TABLE employees(
name STRING,
salary FLOAT,
subordinate ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
addres STRCUT<street:STRING, city:STRING, STATE:STRING>
);文件格式
分隔符：
- \n : 换行符
- ^A：用于分割字段（列），在create table 中可以使用8进制的编码\001
- ^B：用于分割ARRAY或者STRUCT中的元素，或MAP中键值对的分割，8进制\002
- ^C：用于MAP中键和值得分割，8进制\003
CREATE TABLE employees(
name STRING,
salary FLOAT,
subordinate ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
addres STRCUT<street:STRING, city:STRING, STATE:STRING>
)
ROW FORMAT DELIMITED
# 必须写在其他子句之前
FIELDS TERMINATED BY '\001'
# 指定^A作为字段（列）的分割符
COLLECTION ITEMS TERMINATED BY '\002'
# 指定^B作为集合元素的分隔符
MAP KEYS TERMINATED BY '\003'
# 指定^C为键值对的分隔符
LINES TERMINATED BY '\n'
# 指定换行符，目前只能用\n
STORED AS TEXTFILE;
# 指定存储格式
;2、数据库和表的操作
2.1 数据库中的常用操作
- SHOW DATABASES：展示所有的数据库，想筛选可以用LIKE ‘H.*’用正则表达式
- CREATE DATABASE financials：创建数据库
- DESCRIBE DATABASE financials：查看数据库位置
- USE：指定某个数据库为当前工作数据库
- DROP DATABASE IF EXISTS financials：删除数据库
- SHOW PARTITIONS financials：查看表中存在的所有分区
- DESCRIBE EXTENED employees：显示分区键
2.2 创建表
管理表、外部表和分区表
管理表和外部表的区别：
管理表将数据移动到数据仓库指向的路径，仅记录数据所在的路径，不对数据的位置做任何改变。外部表将数据存放到指定目录中。Hive删除表时，管理表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
管理表和外部表的使用场景：
①外部表：比如某个公司的原始日志数据存放在一个目录中，多个部门对这些原始数据进行分析，那么创建外部表是明智选择，这样原始数据不会被删除；
②管理表：对原始数据或比较重要的中间数据进行建表存储；
③分区表：将每个小时或每天的日志文件进行分区存储，可以针对某个特定时间段做业务分析，而不必分析扫描所有数据；
创建代码
CREATE TABLE IF NOT EXISTS 表名(...)
# 创建管理表
CREATE EXTERNAL TABLE IF NOT EXISTS 表名(...)
# 创建外部表
CREATE EXTERNAL TABLE IF NOT EXISTS 表名(...)
PARTITIONED BY (country STRING, state STRING);
# 创建外部分区表2.3 修改表
通过ALTER关键字对表进行修改
表重命名
ALTER TABLE previous_name RENAME TO new_name
增加、修改、删除表分区
增加分区表：
ALTER TABLE table_name ADD IF NOT EXISTS
PARTITION (year=2011, month=1, day=1) LOCATION ‘/logs/2011/01/01’;
修改分区表，移动位置：
ALTER TABLE table_name PARTITION (year=2011, month=1, day=1)
SET LOCATION ‘/new_logs/2011/01/01’;
删除某个分区：
ALTER TABLE table_name DROP IF EXISTS PARTITION (year=2011, month=1, day=1);
修改列
增加列：
ALTER TABLE tabl_name ADD COLUMNS(
app_ name STRING COMMENT ‘Application name’,
session_di LONG COMMENT ‘the current session id’
);
删除或替换列：
ALTER TABLE table_name REPLACE COLUMNS(
hms INT COMMENT ‘hour, minute, seconds’
…
)删除表
DROP TABLE IF EXISTS employees;
2.4 插入数据
2.4.1 本地数据插入
LOAD DATA LOCAL INPATH '/data_path'
OVERWRITE INTO TABLE employees
PARTITION (country='US', state='CA');OVERWRITE 关键字：会先删除原有数据，在插入新数据，如果想追加插入把OVERWRITE改成INTO，如果存在分区，就直接加入，不存在会先新建这个分区。
2.4.2 通过查询语句向表中插入数据
INSERT OVERWRITE TABLE employees
PARTITION (country='US', state='CA')
SELECT * FROM staged_employees se
WHERE se.cnty = 'US' AND se.st = 'OR';2.4.3 动态分区插入
动态分区不需要指定分区，由系统自己选择，使用时需要更改两个设置
set hive.exec.dynamic.partition=true;
设置开启动态分区，动态分区在插入数据时可以不指定分区类型，系统自动选择
set hive.exec.dynamic.partition.mode=nonstrict;
动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE employees
PARTITION (country, state)
SELECT ..., se.cty, se.st
FROM staged_employees se;3、表的查询
3.1 SELECT … FROM
3.1.1 列可选操作：
- 正则表达式：SELECT ‘price.*’ FROM employees
- 算术运算：常用：+ - * ／ %(求余)
- 数学函数：常用：round(DOUBLE d, INT n) 保留n位小数、sqrt、abs、exp、ln
- 聚合函数：常用：
- count(*) 计算总行数包括null行
- count(列名) 计算列中非null行数
- sum()、avg、min、max、variance
- covar_pop(col1, col2)：返回协方差
- corr(col1, col2)：返回相关系数
- 内置函数
3.1.2 其他可选参数
SELECT e.col1 as col1_name FROM employees e LIMIT 10;
as：列别名、LIMIT：限制返回行数
3.1.3 嵌套SELECT语句
SELECT e.name, e.salary FROM
(SELECT person_id as name, salary FROM employees) e
where 指定条件 and 条件3.2 WHERE 语句
- 同上例一样，给select语句限定条件，用and或者or连接条件间的关系。
- 可以用正则表示帅的。LIKE和RLIKE
3.3 GROUP BY 和 HAVING 语句
where用于筛选原表的内容，group by 对结果进行分组， having对group by结果过滤，顺序是where -> group by -> having
SELECT year(ymd), avg(price_close) FROM stocks
WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
GROUP BY year(ymd)
HAVING avg(price_close) > 50.0;3.4 JOIN 语句
JOIN … ON，JOIN连接两个表，ON表示两个表的连接条件
SELECT s.ymd, s.symbol, s.price_close, d.dividend
FROM stocks JOIN dividends d ON s.ymd = d.ymd and s.symbol=d.symbol
WHERE s.symbol = 'AAPL';INNER JOIN：JOIN默认是INNER JOIN 要两边同时存在才会显示
LEFT OUTER JOIN：左表满足条件则显示，右边填NULL
RIGHT OUTER JOIN：同上
FULL OUTER JOIN：两边都填NULL
JOIN：笛卡尔积
同时有where和join时，是先执行join再执行where，所以如果是outer join，大量数据中有null，可能会被where语句过滤掉
4、一个例子
官方API文档：
https://cwiki.apache.org/confluence/display/Hive/Home#Home-UserDocumentation
使用工具：beeline
常用指令：
- 1、!connect url –连接不同的Hive2服务器
- 2、!exit –退出shell
- 3、!help –显示全部命令列表
