二 基础操作
1 测试Hadoop
## 查看本地文件
$ hadoop dfs -ls /
2 hive内部
Thrift 提供可远程访问其他进程的功能，也提供使用JDBC和ODBC访问hive的功能
metastoreservice 元数据服务，来存储表模式信息和其他元数据信息。通常会用一个关系型数据库中的表来存储
3 启动hive
```
$ cd $HIVE_HOME
$ bin/hive ##启动
```
4 配置hadoop环境
1 本地模式配置
本地模式下文件都在本地文件系统而不是分布式环境；用户job都在同一个JVM中运行
配置本地模式下的hive-site.xml
```
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/home/me/hive/warehouse</value>
</property>
<property>
<name>hive.metastore.local</name>
<value>true</value>
</property>
<property>
<name>javax.jdo.opt.ConnectionURL</name>
<value>jdbc:derby:;databaseName=/home/me/hive/metastore_db;create=true</value>
</property>
```
2 分布式模式和伪分布式模式
分布式模式下，集群中会启动多个服务。
JobTracker管理Job,HDFS则由NameNode管理。
每个工作节点都有Job task,由每个节点上的TaskTracker服务管理
每个节点上存放由分布式文件系统中的文件数据块，由每个节点上的DataNode管理
3 使用JDBC连接元数据
hive所需要的组件中，metastore元数据存储是Hadoop没有的。
默认的内置数据库并不适用于生产环境(不支持并发访问元数据)
任何一个适用JDBC进行连接的数据库都可以用作元数据存储
大多数的HIVE客户端会使用MySQL.
hive-site.xml
```
<property>
<name>javax.jdo.opt.ConnectionURL</name>
<value>jdbc:mysql://dbl.mydomain.pvt/hive_db/createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.opt.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.opt.ConnectionUserName</name>
<value>database_user</value>
</property>
<property>
<name>javax.jdo.opt.ConnectionPassword</name>
<value>database_pass</value>
</property>
```
4 hive中‘一次使用’命令
$ hive -e "SELECT * FROM mytable limit 3" ## 将查询结果保持到一个文件 $ hive -S -e "SELECT * FROM mytable limit 3" > /tmp/myquery
5 hive中使用hadoop的dfs命令
```
hive> dfs -ls / ;
-- 显示字段名称
hive> set hive.cli.print.header=true;
```
三 数据类型和文件格式
1 基本数据类型
数据类型 长度 TINYINT 1byte SMALLINT 2byte INT 4byte BIGINT 8byte BOOLEAN true/false FLOAT 单精度浮点数 DOUBLE 双精度浮点数 STRING 字符序列 TIMESTAMP 整数,浮点数或者字符串 BINARY 字节数组
2 集合数据类型
STRUCT STRUCT(first STRING,last STRING)元素通过字段名.first引用 MAP map('first','join','last','doe')字段名['last']获取最后一个元素 ARRAY ARRAY('John','Doe')
3 文件分隔符
```
分隔符
\n 换行符
^A \001
^B \002
^C \003
例子:
CREATE TABLE employees (
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001' -- 列分隔符
COLLECTION ITEMS TERMINATED BY '\002' -- 集合元素间的分隔符
MAP KEYS TERMINATED BY '\003' -- map的键值之间的分隔符
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
```
四 数据定义
1 数据库
-
Hive会为每个数据库创建一个目录。数据库中的表将会以这个数据库目录的子目录形式存储。
数据库所在目录位于属性 hive.metastore.warehouse.dir所指定的顶层目录之后 数据库的文件目录名以.db结尾hive> CREATE DATABASE financials > LOCATION '/my/preferred/dir' ## 指定数据库创建位置 > COMMENT 'Holds all financials tables' ## 添加描述 > WITH DBPROPERTES('creator'='Mark','date'='2012-01-02'); ##添加一些属性信息 hive> DESCRIBE DATABASE financials; ## 会显示描述以及数据库所在目录、属性信息 hive> set hive.cli.print.current.db=true; ## 用于在提示符里面显示当前所在的数据库 hive(financials)>USE default; hive(default)> hive> DROP DATABASE IF EXISTS financials; ## 删除数据库 hive> DROP DATABASE IF EXISTS financials CASCADE; ## hive不允许删除包含表的数据库。这里会先删除表，再删库
2 创建表
hive> CREATE TABLE IF NOT EXISTS financials.employees (
name STRING COMMENT 'Employee name',
salary FLOAT COMMENT 'Employee salary',
subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
)
COMMENT 'Description of table'
TBLPROPERTIES ('createor'='me','created_at'='2020-01-02') ## hive会自动添加2个表属性 last_modified_by ,last_modified_time
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001' -- 列分隔符
COLLECTION ITEMS TERMINATED BY '\002' -- 集合元素间的分隔符
MAP KEYS TERMINATED BY '\003' -- map的键值之间的分隔符
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
- 拷贝表模式
hive> CREATE TABLE IF NOT EXISTS financials.employees2
> LIKE financials.employees;
- 查看指定数据库下的表
hive> SHOW TABLES IN financials;
- 查看表的详细信息
hive> DESCRIBE EXTENDED financials.employees;
- 提供更加详细的信息
hive> DESCRIBE FORMATTED financials.employees;
3 外部表
-
删除外部表，并不会删除数据
-
hive只是管理这些目录和文件，并不具有完全控制权限
hive> CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
> exchange STRING,
> symbol STRING,
> ymd STRING,
> price_open FLOAT,
> price_hign FLOAT,
> price_low FLOAT,
> price_close FLOAT,
> volume INT,
> price_adj_close FLOAT)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
> LOCATION '/data/stocks';
4 分区表、管理表
## 按照country(国家)再按照state(州)分区
hive> CREATE TABLE employees (
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
)PARTITIONED BY (country STRING,state STRING);
-
分区表改变了Hive对数据存储的组织方式
hdfs://master-server/user/hive/warehouse/financials.db/employees master-server 表示主节点和端口号 -
分区后
hdfs://master-server/user/hive/warehouse/financials/employees/country=CA/state=AB
hdfs://master-server/user/hive/warehouse/financials/employees/country=CA/state=BC
...
hdfs://master-server/user/hive/warehouse/financials/employees/country=US/state=AL
-
为了避免触发一个巨大的MapReduce任务
将hive设置为"strict"模式，这样如果对分区表查询where语句中没有加分区过滤，将会禁止提交这个任务 hive> set hive.mapred.mode = strict -
查看所有分区
hive> SHOW PARTITION employees;
- 查看分区字段country=US下的分区
hive> SHOW PARTITION employees PARTITION(country=US);
## 载入的方式创建分区,文件会拷入分区目录
hive> LOAD DATA LOCAL INPATH '${env:HOME}/california-employees'
> INTO TABLE employees
> PARTITION(country = 'US',state = 'CA');
## 外部分区表 -- 日志文件分析
hive> CREATE EXTERNAL TABLE IF NOT EXISTS log_messages (
> hms INT COMMENT '时间戳的剩余的小时',
> severity STRING,
> server STRING,
> process_id INT,
> message STRING)
> PARTITIONED BY(year INT,month INT,day INT)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
## 创建一个2012年1月2日的分区
hive> ALTER TABLE log_messages ADD PARTITION(year=2012,month=1,day=2)
> LOCATION 'hdfs://master-server/data/log/log_messages/2012/01/02';
5 自定义表的存储格式
Hive使用一个inputFormat对象将输入流分割成记录，
然后使用一个outputFormat对象来将记录格式化为输出流，
再使用一个SerDe(序列化/反序列化器)再读数据时将记录解析成列，在写数据时将列编码成记录
6 删除表
hive> DROP TABLE IF EXISTS employees;
7 修改表
ALTER TABLE 仅仅会修改表元数据
- 重命名
hive> ALTER TABLE log_messages RENAME TO logmsgs;
-
增加、修改和删除表分区
– 添加分区
hive> ALTER TABLE log_messages ADD IF NOT EXISTS
> PARTITION (year=2011,month =1,day=1) LOCATION '/logs/2011/01/01';
– 修改某个分区路径
hive> ALTER TABLE log_messages PARTITION(year=2011,month=12,day=2)
> set LOCATION 's3n://ourbucket/logs/2011/01/02';
– 删除某个分区
hive> ALTER TABLE log_messages DROP IF EXISTS PARTITION(year=2011,month=12,day=2);
– 修改列信息
##重命名
hive> ALTER TABLE log_messages
> CHANGE COLUMN hms hours_minutes_seconds INT
> COMMENT 'the hours,minutes,and seconds'
> AFTER severity;
– 增加列
hive> ALTER TABLE log_messages ADD COLUMNS(
> app_name STRING ,
> session_id LONG);
--删除或者替换列
## 重命名hms移除severity和message
hive> ALTER TABLE log_messages REPLACE COLUMNS(
> hours_mins_secs INT ,
> severity STRING,
> message STRING);
– 修改表属性
hive> ALTER TABLE log_messages SET TBLPROPERTIES(
> 'notes'='updated');
– 修改存储属性
## 将分区的存储格式修改为SEQUENCEFILE
hive> ALTER TABLE log_messages
> PARTITION (year=2011,month =1,day=1)
> SET FILEFORMAT SEQUENCEFILE;
## 重命名
hive> ALTER TABLE log_messages
> CHANGE COLUMN hms hours_minutes_seconds INT
> COMMENT 'the hours,minutes,and seconds'
> AFTER severity;
– 增加列
hive> ALTER TABLE log_messages ADD COLUMNS(
> app_name STRING ,
> session_id LONG);
–删除或者替换列
## 重命名hms移除severity和message
hive> ALTER TABLE log_messages REPLACE COLUMNS(
> hours_mins_secs INT ,
> severity STRING,
> message STRING);
– 修改表属性
hive> ALTER TABLE log_messages SET TBLPROPERTIES(
> 'notes'='updated');
– 修改存储属性
– 防止分区被删除和查询
hive> ALTER TABLE log_messages
> PARTITION (year=2011,month =1,day=1) ENABLE NO_DROP;
五 数据操作
1 管理表中装载数据
LOAD DATA LOCAL 路径是本地文件系统路径，local下的文件会被拷贝到相应的表所在的目录，也就是会存在多份拷贝
LOAD DATA 分布式文件系统中的路径
OVERWRITE 目标文件夹中之前存在的数据将会被先删除掉
2 通过查询语句向表中插入数据
## 使用OVERWRITE之前分区表里的内容将会被覆盖掉
hive> INSERT OVERWRITE TABLE employees
hive> PARTITION (country='US',state='OR')
hive> SELECT * FROM staged_employees se where se.cnty='US' AND se.st='OR';
## 如果staged_employees非常大，需要对65个州都执行，则需要扫描这张表65次
## hive提供了一种INSERT语法，只扫描一次输入数据
## 临时表
hive> FROM staged_employees se
> INSERT OVERWRITE TABLE employees
> PARTITION (country='US' AND state='OR')
> SELECT * WHERE se.cnty='US' AND se.st='OR'
> INSERT OVERWRITE TABLE employees
> PARTITION(country='US' ,state='CA')
> SELECT * WHERE se.cnty='US' AND se.st='CA';
## 动态分区插入
hive提供一个动态分区功能，可以基于查询参数判断出需要创建的分区名称
hive根据上述的select语句的最后2列来确定分区字段是:country和state
用户也可以混合使用动态和静态分区
## 静态分区必须出现再动态分区键之前
hive> INSERT OVERWRITE TABLE employees
> PARTITION (country='US',state)
> SELECT *
> FROM staged_employees se WHERE se.cnty='US' ;
## 动态分区默认是没有开启，开启后，默认以"strict"模式执行
## 动态分区属性
## 开启动态分区
hive.exec.dynamic.partition = false
## nonstrict表示允许所有分区都是动态的
hive.exec.dynamic.partition.mode = strict
## 每个mapper或者reducer可以创建的最大动态分区个数
hive.exec.max.dynamic.partitions.permode = 100
## 一个动态分区创建语句可以创建的最大动态分区个数
hive.exec.dynamic.partitions =+1000
## 全局可以创建的最大文件个数。hadoop计数器跟踪
hive.exec.max.created.files=100000
```
3 单个查询语句中创建表并加载数据
##这种表中含有employees表中来自加利福利亚的雇员的name,salary,address信息
hive> CREATE TABLE ca_employees
> AS SELECT name,salary,address
> FROM employees
> WHERE state = 'CA';
4 导出数据
hive> hadoop fs -cp source_path target_path;
## 或者
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
> SELECT name,salary,address
> FROM employees
> WHERE state='CA';
## 查看文件内容
hive> ! ls /tmp/ca_employees;
hive> ! cat /tmp/ca_employees/xxxx
## 指定多个输出文件夹目录
hive> FROM staged_employees se
> INSERT OVERWRITE DIRECTORY '/tmp/or_employees'
> SELECT * WHERE se.cnty='US' AND se.st='OR'
> INSERT OVERWRITE DIRECTORY '/tmp/ca_employees'
> SELECT * WHERE se.cnty='US' AND se.st='CA';
六 查询
1 SELECT … FROM 语句
## 当用户选择的列是集合数据类型，hive会使用JSON语法应用于输出。
## 引用一个不存在的元素将会返回NULL
1 使用正则表达式来指定列
hive> SELECT symbol,`price.*` FROM stocks;
2 使用列值进行计算
## 查询转换为大写的姓名，雇员对应的薪水，
## 以及需要缴纳的税收比例以及扣税后进行取整所得的税后薪资
hive> SELECT upper(name),salary,deductions["Federal Taxes"],
> round(salary*(1-deductions["Federal Taxes"])) FROM employees;
3 算术运算符
hive遵循的是底层JAVA中数据类型的规则，因此当溢出或者下溢时计算结果不会自动转为更广泛得数据类型
4 使用函数
## 数学函数
round、floor、ceil、rand、exp、
## 聚合函数
count、sum、avg、min、max、
## 提高聚合性能
hive> SET hive.map.aggr=true
## 表生成函数---将单列扩展成多列或者多行
## 将employees表中每行记录中的下属subordinates内容转成0个或者多个新得记录行
hive> SELECT explode(subordinates) AS sub FROM employees;
Mary Smith
Todd Jone
Bill King
## 表生成函数
explode(ARRAY array) 返回0到多行结果，每行对应array数组中的一个元素
explode(MAP map) 返回0到多行结果，对应键值对
explode(ARRAY<TYPE> a) 返回a中的每个元素
inline(ARRAY<STRUCT[,STRUCT]>) 将结构体数组提取出来并插入到表中
json_tuple(STRING jsonStr,p1,p2,...pn) 返回TUPLE,对输入的JSON字符串处理
parse_url_tuple(url,partname1,....) 返回TUPLE,从URL中解析N个部分信息输入参数时URL
stack(INT n,col1,...) 把M列转成N行 ，每行有M/N个字段
## 其他内置函数
ascii(STRING s) 返回s的首个ASCII字符的整数值
base64(BINARY bin) 将二进制bin转成基于64位的字符串
binary(STRING s) 返回二进制值
cast(<expr> as <type>) 将expr转成type类型
concat(STRING s1,STRING s2...) 拼接
concat_ws(STRING separator,STRING s1,...) 指定的分隔符拼接
decode(BINARY bin,STRING charset)
encode(STRING src,STRING charset)
find_in_set(STRING s,STRING commaSeparatedString) 返回以逗号分隔符的字符串中s出现的位置
format_number(NUMBER x,INT d) 将数值x转换成'#,###,###.##'格式字符串
in
in_file
instr(STRING str,STRING substr) 查找字符串str中子字符串substr第一次出现的位置
length
locate(STRING substr,STRING str[,INT pos]) 查找str中的Pos后的字符串substr第一次出现的位置
lower
lcase
lpad
ltrim
parse_url(STRING url,STRING partname[,STRING key]) 从URL中抽取指定部分的内容，partname表示要抽的部分名称
regexp_extract(STRING subject,STRING regex_pattern,STRING index)
regex_replace(STRING subject,STRING regex,STRING replacement)
size(MAP<K,V>)
space(INT n) 返回n个空格
split(STRING s,STRING pattern) 按照正则pattern分割字符串s,以字符串数组返回
upper
from_unixtime(BIGINT unixtime[,STRING format]) 将时间戳秒数转换成UTC时间
unix_timestamp(STRING date,STRING pattern)
to_date(STRING timestamp) 返回时间字符串的日期部分
year
month
day
hours
minutes
second
datediff(STRING enddate,STRING startdate) 计算时间相差天数
date_add(STRING startdate,INT days)
date_sub
from_utc_timestamp(TIMESTAMP timestamp,STRING timezone)
to_utc_timestamp(TIMESTAMP timestamp,STRING timezone)
5 limit语句
hive> SELECT upper(name),salary,deductions["Federal Taxes"],
> round(salary*(1-deductions["Federal Taxes"])) FROM employees
> LIMIT 2;
6 列别名 - as
7 嵌套SELECT语句
取别名
8 CASE ... WHEN ... THEN
## 处理单个列得查询结果
hive> SELECT name,salary,
> CASE
> WHEN salary < 50000 THEN 'low'
> WHEN salary >= 50000 AND salary < 70000 THEN 'middle'
> WHEN salary >= 70000 AND salary < 100000 THEN 'high'
> ELASE 'very high'
> END AS bracket FROM employees;
9 什么情况下hive可以避免进行MapReduce
-- 本地模式
如:
hive> SELECT * FROM employees;
-- WHERE 语句中过滤条件只是分区字段情况
hive> SELECT * FROM employees
> WHERE country='US' AND state='CA'
> LIMIT 100;
-- 如果属性hive.exec.mode.local.auto = true,hive会尝试使用本地模式执行
2 WHERE语句
sql的查询执行顺序 from/where/group by/having/select distinct / order by/having/select
-- 则where中不可直接使用列别名
-- 可使用嵌套的select子查询，然后使用其子查询的列名
1 谓词操作符
-- 无 == 符号
2 关于浮点数比较
浮点数比较常见得陷阱出现在不同类型间作比较得时候(FLOAT和DOUBLE比较)
如果 FLOAT类型的值是0.2000001，而DOUBLE类型值是0.200000000001.
当表中的FLOAT值通过 HIVE转换为DOUBLE值时，其产生的DOUBLE值是0.200001000000，其实是比0.200000000001大
这基本是浮点数编码普遍存在的问题
-- 有2中规避方法
直接设置为DOUBLE类型
显示的指出0.2为FLOAT类型，如: 0.2f.
可是hive并不支持这种语法，必须使用case操作符
case(0.2 AS FLOAT)
再有就是 和钱相关的都避免使用浮点数
3 LIKE 和 RLIKE
RLIKE 可接
正则表达式
3 GROUP BY
通常和聚合函数一起用
## 按照苹果公司股票的年份对股票记录进行分组，然后计算每年的平均收盘价
hive> SELECT year(ymd),avg(price_close) FROM stocks
> WHERE exchange = 'NASDAQ' AND symbol ='AAPL'
> GROUP BY year(ymd);
## HAVING
## 输出结果中年平均收盘价>50.0的结果
hive> SELECT year(ymd),avg(price_close) FROM stocks
> WHERE exchange = 'NASDAQ' AND symbol ='AAPL'
> GROUP BY year(ymd)
> HAVING avg(price_close) > 50.0;
## 使用子查询达到相同结果
hive> SELECT s2.year,s2.avg FROM (
> SELECT year(ymd) AS year ,avg(price_close) AS avg FROM stocks
> WHERE exchange = 'NASDAQ' AND symbol ='AAPL'
> GROUP BY year(ymd) ) s2
> WHERE s2.avg > 50.0;
4 JOIN
内连接(INNER JOIN),只有进行连接的两个表中都存在 与连接标准相匹配的数据
hive> SELECT a.ymd,a.price_close,b.price_close
> FROM stocks a JOIN stocks b ON a.ymd = b.ymd
> WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM';
HIVE目前不支持在on子句中的谓词间使用OR
## 非自连接操作(hive总是从左到右顺序执行)
hive> CREATE EXTERNAL TABLE IF NOT dividends (
> ymd STRING,
> dividend FLOAT
> )
> PATITIONED BY (exchange STRING,symbol STRING)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
## 苹果公司的stocks表和dividends表按字段ymd和symbol为等值的内连接
hive> SELECT s.ymd,s.symbol,s.price_close,d.dividend
> FROM stocks s JOIN dividends d ON s.ymd = d.ymd AND
> s.symbol = d.symbol
> WHERE s.symbol = 'AAPL';
## 多数情况下,Hive会对每对JOIN连接对象启动一个MapReduce任务,
## 首先启动一个MapReduce job对表a和表b进行连接操作，然后再启动
## 一个MapReduce将第一个 MapReduce job的输出和表c进行连接
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';
> WHERE s.symbol = 'AAPL';
## JOIN优化
-- 当对3个或更多表进行JOIN连接时，如果每个ON子句都使用相同的连接键的话，
只会产生一个MapReduce job
-- Hive 若查询中最后一个表时最大的那个表，再对每行记录进行操作时，它会
尝试将其他表缓存起来，然后扫描最后那个表进行计算。因此用户需要保证查询
中的表大小从左到右是依次增加的
-- 用户并非总是将最大的表放置再查询语句的最后面，Hive提供一个'标记'来
显示表示
hive> SELECT /*+STREAMTABLE(s)*/s.ymd,s.symbol,s.price_close,d.dividend
> FROM stocks s JOIN dividends d ON s.ymd = d.ymd AND
> s.symbol = d.symbol
> WHERE s.symbol = 'AAPL';
## LEFT OUTER JOIN
JOIN操作左边表符合WHERE子句的所有记录将会被返回。
JOIN操作符右边表中如果没有符合ON后面连接条件的记录时，右边表指定选择的列的
值将会是NULL
## OUTER JOIN
## RIGHT OUTER JOIN
## FULL OUTER JOIN
完全外连接将会返回所有表中符合WHERE语句条件的所有记录。如果任意表的指定字段
没有符合条件的值的话，就使用NULL值代替。
## LEFT SEMI-JOIN
左半开连接会返回左边表的记录，前提是其记录对于右表满足ON语句中的判定条件。
对于INNER JOIN来说，这是一个特殊的优化了的情况
## 限定的股息支付日内的股票交易记录
## SELECT和WHERE语句中不能引用右表中的字段
hive> SELECT s.ymd,s.symbol,s.price_close
> FROM stocks s LEFT SEMI JOIN dividends d ON s.ymd = d.ymd
> AND s.symbol = d.symbol;
## LEFT SEMI-JOIN比INNER JOIN 要更高效：对于左表中一条指定的记录，在右表中
一旦找到匹配的记录，Hive就会立即停止扫描
## 笛卡尔积 JOIN
表示左边表的行数*右边表的行数等于笛卡尔结果集的大小
笛卡尔积会导致产生大量的数据，且执行时间长，运行缓慢
在数据库中，会被优化成INNER JOIN，但是Hive不会优化
## 在hive中，这个查询在应用WHERE语句中的谓词条件前先进行完全笛卡尔积计算
hive> SELECT * FROM stocks JOIN dividends
> WHERE stocks.symbol = dividends.symbol and stocks.symbol='AAPL';
## 设置属性hive.mapred.mode=strict,hive会阻止用户指定笛卡尔积
## map-side JOIN
-- 如果所有表中只有一张表是小表，可以在最大表通过mapper的时候将小表完全放到内存。
-- hive可以在map端执行连接过程(map-side JOIN),因为hive可以和内存中的小表--匹配，
从而省略常规连接操作所需要的reduce过程
## 启动下面属性，才会开启map-side优化
hive> set hive.auto.convert.join = true;
hive> SELECT s.ymd,s.symbol,s.price_close,d.dividend
> FROM stocks s JOIN dividends d ON s.ymd = d.ymd
> AND s.symbol=d.symbol
> WHERE s.symbol='AAPL';
## 配置优化的小表的大小
hive> set hive.mapjoin.smalltable.filesize=25000000
## RIGHT OUTER JOIN和FULL OUTER JOIN不支持这个优化
## 如果所有表中的数据是分桶的
-- 表中的数据必须按照ON语句中的键分桶
-- 其中一张表的分桶个数必须是另一张表的分桶个数的若干倍
-- hive.optimize.bucketmapJOIN=true
5 ORDER BY 和 SORT BY
-- 对查询结果进行全局排序，即通过一个reducer处理的过程
-- ORDER BY 操作会导致运行时间更长，一般配合limit使用
-- SORT BY 局部排序，只会在reducer中对数据排序
6 含有SORT BY 的DISTRIBUTE BY
– DISTRIBUTE BY 控制map的输出在reducer中是如何划分的。
-- MapReduce会依据map输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个reducer中
## 相同股票交易的数据在一起处理;DISTRIBUTE BY保证具有相同股票交易的记录分发到同一个reducer
## 然后使用SORT BY来对期望的数据排序
hive> SELECT s.ymd,s.symbol,s.price_close
> FROM stocks s
> DISTRIBUTE BY s.symbol
> SORT BY s.symbol ASC, s.ymd ASC;
-- DISTRIBUTE BY和GROUP BY 在控制reducer是如何接受一行行数据进行处理是类似的
-- Hive要求DISTRIBUTE BY 要写在SORT BY之前
7 CLUSTERED BY
CLUSTERED BY s.symbol相当于简写
==>
DISTRIBUTE BY s.symbol SORT BY s.symbol ASC
– 简化版会剥夺SORT BY的并行性，排序是全局排序
8 类型转换
-- 转FLOAT
hive> SELECT name,salary FROM employees
> WHERE cast(salary AS FLOAT) < 100000.0
9 抽样查询
– 对于非常大的数据集，用户需要使用的是一个具有代表性的查询结果而非全部，
hive可以通过分桶抽样满足
## numbers表中只有number字段从1-10
## 根据rand()函数抽样，每次取出都不同
hive> SELECT * FROM numbers TABLESMAPLE(BUCKET 3 OUT OF 10 ON rand()) s;
## 按指定列分桶抽样，每次同一个语句多次执行取值都相同
hive> SELECT * FROM numbers TABLESMAPLE(BUCKET 5 OUT OF 10 ON number)) s;
## 分桶语句中的分母表示的是数据将会被散列的桶的个数，而分子表示将会选择的桶的个数
tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
-- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例
-- x表示从哪个bucket开始抽取
-- 例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，
分别为第3个bucket和第（3+16=）19个bucket的数据。
-- 数据块抽样
## 基于行数，按照抽样百分比进行抽样得方式;这种抽样的最小抽样单元是一个HDFS数据块
hive> SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s;
-- 分桶表的输入裁剪
-- TABLESAMPLE语句中指定的列和CLUSTERED BY语句中指定的列相同，那么TABLESAMPLE查询
就只会扫描涉及到表的哈希分区下的数据
hive> CREATE TABLE numbers_bucketed(number int) CLUSTERED BY(number) INTO 3 BUCKETS;
> set hive.enforce.bucketing = true;
> INSERT OVERWRITE TABLE numbers_bucketed SELECT number FROM numbers;
> dfs -ls /user/hive/warehouse/mydb.db/numbers_bucketed;
/user/hive/warehouse/mydb.db/numbers_bucketed/000000_0
/user/hive/warehouse/mydb.db/numbers_bucketed/000001_0
/user/hive/warehouse/mydb.db/numbers_bucketed/000002_0
> dfs -cat /user/hive/warehouse/mydb.db/numbers_bucketed/000001_0
1
7
10
4
## numbers_bucketed已被聚集成3个数据桶，下面查询可以高效地仅对其中一个数据桶进行抽样
hive> SELECT * FROM numbers_bucketed TABLESAMPLE(BUCKET 2 OUT OF 2 ON NUMBER)s;
10 UNION ALL
– 可以将2个或多个表进行合并，每个union查询都必须具有相同的列
## 日志数据合并
hive> SELECT log.ymd,log.level,log.message
> FROM (
> SELECT ll.ymd,ll.level,ll.message,'LOG1' AS source
> FROM log1 ll
> UNION ALL
> SELECT l2.ymd,l2.level,l2.message,'LOG2' AS source
> FROM log1 l2
> )log SORT BY log.ymd ASC;
## UNION 也可用于同一个源表数据合并。可以将一个长的复杂的WHERE语句分割成
2个或多个UNION子查询。不过，除非源表建立了索引，否则，查询将会对同一个源数据进行多次拷贝分发
hive> FROM (
> FROM src SELECT src.key ,src.vale WHERE src.key <100
> UNION ALL
> FROM src SELECT src.* WHERE src.key > 110
> )unioninput
> INSERT OVERWRITE DIRECTORY '/tmp/union.out' SELECT unioninput.*;
```
七 视图
1 使用视图来降低查询复杂度
## 嵌套子查询
hive> FROM(
> SELECT * FROM people JOIN cart
> ON (cart.people_id = people.id)WHERE firstname='join'
> )a SELECT a.lastname WHERE a.id=3;
##使用视图
hive> CREATE VIEW shorter_join AS
> SELECT * FROM people JOIN cart
> ON (cart.people_id=people.id)WHERE firstname='join';
hive> SELECT lastname FROM shorter_join WHERE id=3;
```
2 使用视图来限制基于条件过滤的数据
3 动态分区中的视图和map类型
##例子：使用^A作为集合内元素间的分隔符，^B作为map中的键和值之间的分隔符
time^B1222121212^Atype^Brequest^Astate^Bny^Acity^Bwhiteplains^Apart\^Bmuffler
time^B122333233212^Atype^Bresponse^Astate^Bny^Acity^Btarrytown^Apart\^Bmuffler
time^B1222121212^Atype^Brequest^Astate^Btx^Acity^Baustin^Apart\^Bheadlight
hive> CREATE EXTERNAL TABLE dynamictable(cols map<string,string>)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\004'
> COLLECTION ITEMS TERMINATED BY '\001'
> MAP KEYS TERMINATED BY '\002'
> STORED AS TEXTFILE;
## 取出type=request的city、state、part的信息
hive> CREATE VIEW order(state,city,part) AS
> SELECT cols["state"],cols["city"],cols["part"]
> FROM dynamictable
> WHERE cols["type"] ="request";
## 取出type=response的time、part字段信息
hive> CREATE VIEW shipment(time,part) AS
> SELECT cols["time"],cols["parts"]
> FROM dynamictable
> WHERE cols["type"]="response"
八 索引
当逻辑分区实际上太多太细而无法使用时，建立索引也就成为分区的另一个选择。
建立索引可以帮助裁剪掉一张表的一些数据块，这样能够减少MapReduce的输入数据量。
1 创建索引
hive> CREATE TABLE employees (
> name STRING,
> salary FLOAT,
> subordinates ARRAY<STRING>,
> deductions MAP<STRING,FLOAT>,
> address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
> )PARTITIONED BY(country STRING,state STRING);
hive> CREATE INDEX employees_index
> ON TABLE employees(country)
## 指定索引处理器
> AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
> WITH DEFERRED REBUILD
> IDXPROPERTIES('creator'='me','created_at'='some_time')
> IN TABLE employees_index_table
## 如果去掉分区，索引将会包含原始表的所有分区
> PARTITIONED BY(country,name)
> COMMENT 'Employees index by country and name';
-- Bitmap索引
应用于排重后值较少的列
hive> CREATE INDEX employees_index
> ON TABLE employees(country)
> AS 'BITMAP'
> WITH DEFERRED REBUILD
> IDXPROPERTIES('creator'='me','created_at'='some_time')
> IN TABLE employees_index_table
> PARTITIONED BY(country,name)
> COMMENT 'Employees index by country and name';
2 重建索引
如果用户指定了DEFERRED REBUILD,那新索引将呈现空白状态。在任何时候都可以进行第一次 索引创建
或者使用ALTER INDEX对索引进行重建
hive> ALTER INDEX employees_index
> ON TABLE employees
## 省略掉 PARTITION,将对所有分区进行重建索引
> PARTITION(country='US')
> REBUILD;
3 显示索引
## FORMATTED可选，增加这个关键字可以输出中包含列名称
hive> SHOW FORMATTED INDEX ON employees;
4 删除索引
## hive不允许用户直接使用DROP TABLE语句之前删除索引表
hive> DROP INDEX IF EXISTS employees_index ON TABLE employees;
九 模式设计
1 按天划分的表
通常会在表名中加入一个时间戳
hive> CREATE TABLE supply_2011_01_02(id int,part string,quantity int);
hive> CREATE TABLE supply_2011_01_03(id int,part string,quantity int);
hive> CREATE TABLE supply_2011_01_04(id int,part string,quantity int);
hive> SELECT part,quantity from supply_2011_01_02
> union ALL
> SELECT part,quantity from supply_2011_01_03
> WHERE quantity < 4;
## 上述情况应该使用分区表。Hive通过WHERE子句中的表达式来选择查询所需要的指定分区
hive> CREATE TABLE supply(id int,part string,quantity int) PARTITIONED BY (int day);
hive> ALTER TABLE supply add PARTITION (day=20110102);
hive> ALTER TABLE supply add PARTITION (day=20110103);
hive> ALTER TABLE supply add PARTITION (day=20110104);
hive> SELECT part,quantity FROM supply
> WHERE day>=20110102 AND day<20110103 AND quantity < 4;
2 关于分区
– HDFS用于设计存储数百万的大文件，而非数十亿的小文件。使用过多分区可能导致的问题是会创建
大量的非必须的Hadoop文件和文件夹。
一个分区对应着一个包含有多个文件的文件夹。如果指定的表存在数百个分区，那么每天会创建好
几百万个文件。如果一直这样，最终会超出NameNode对系统云数据信息的处理能力。
因为NameNode必须要将所有的系统文件的元数据信息保存在内存中。
-- MapReduce会将一个job转换成多个task。默认情况下，每个task都是一个新的JVM实例，都需要开启
和销毁的开销。
-- 一个理想的分区方案不应该导致产生太多的分区和文件夹目录，并且每个目录下的文件应该足够大，
应该是文件系统中块大小的若干倍
-- 按时间范围分区的一个好的策略是按照不同的时间粒度来确定合适大小的数据积累量；随时间推移分区数量的
增长是'均匀的'，而且每个分区下包含的文件大小至少是文件系统中块的大小或块大小的数倍。
-- 另一个解决方案是使用2个级别的分区并且使用不同的维度
hive> CREATE TABLE weblogs (url string,time long,city string)
3 唯一键和标准化
Hive没有主键或基于序列密钥生成自增键;复杂得数据类型，如array,map和struct,有助于实现在单行中存储
一对多数据
避免标准化的主要原因是为了最小化磁盘寻道；非标准化数据允许被扫描或写入到大的，连续的磁盘存储区域，
从而优化磁盘驱动器的I/0性能。但是非标准化数据可能导致数据重复，而且有更大的导致 数据不一致的风险。
如：employees这张表，前面都有涉及
4 同一份数据多种处理
Hive本身提供一个独特的语法，可以从一个数据源产生多个数据聚合，而无需每次聚合都要重新扫描一次。
hive> INSERT OVERWRITE TABLE sales
> SELECT * FROM history WHERE action='purchased';
hive> INSERT OVERWRITE TABLE credits
> SELECT * FROM history WHERE action='returned';
## 简写
hive> FROM history
> INSERT OVERWRITE sales SELECT * WHERE action='purchased'
> INSERT OVERWRITE credits SELECT * WHERE action='returned';
5 对于每个表的分区
-- 由于查询或者原始数据处理的某个步骤出现问题而导致需要对好几天的输入数据重跑ETL过程。
这时就会觉得分区是由必要的
## 中间表，计算某一天的数据时会导致前一天的数据被覆盖
$ hive -hiveconf dt=2011-01-01
hive> INSERT OVERWRITE TABLE distinct_ip_in_logs
> SELECT distinct(ip)as ip from weblogs
> WHERE hit_date='${hiveconf:dt}';
hive> CREATE TABLE state_city_for_day(state string,city string);
hive> INSERT OVERWRITE state_city_for_day
> SELECT distinct(state,city) FROM distinct_ip_in_logs
> JOIN geodata ON distinct_ip_in_logs=geodata.ip;
## 使用分区
$ hive -hiveconf dt=2011-01-01
hive> INSERT OVERWRITE table distinct_ip_in_logs
> PARTITION (hit_date=${dt})
> SELECT distinct(ip) as ip from weblogs
> WHERE hit_date='${hiveconf:dt}';
hive> CREATE TABLE state_city_for_day(state string,city string)
> PARTITIONED BY (hit_date string);
hive> INSERT OVERWRITE table state_city_for_day PARTITION(${hiveconf:dt})
> SELECT distinct(state,city) FROM distinct_ip_in_logs
> JOIN geodata ON distinct_ip_in_logs.ip = geodata.ip
> WHERE hit_date ='${hiveconf:dt}'
6 分桶表数据存储
-- 分区提供一个隔离数据和优化查询的便利方式；并不是所有的数据集都可以合理的分区
-- 分桶是将数据集分解成更容易管理的若干部分的另一个技术
## 假设有个表的一级分区是dt，二级分区是user_id，这种划分会导致太多的小分区
## Hive会限制动态分区可以创建的最大分区数，用来避免由于创建太多分区导致文件系统
的处理能力以及其他问题
hive> CREATE TABLE weblog ( url STRING,source_ip STRING)
> PARTITIONED BY (dt STRING,user_id INT);
hive> FROM raw_weblog
> INSERT OVERWRITE TABLE page_view PARTITION(dt='2012-06-08',user_id)
> SELECT server_name,url,source_ip,dt,user_id;
## 采用分桶方式,user_id 作为分桶字段
hive> CREATE TABLE weblog(user_id INT,url STRING,source_ip STRING)
> PARTITIONED BY (dt STRING)
> CLUSTERED BY(user_id) INTO 96 BUCKETS;
## 如果没有设置这个属性，需要自己设置和分桶个数匹配的reducer个数
## 如 set mapred.reduce.tasks=96
hive> SET hive.enforce.bucketing = true;
> FROM raw_weblog
> INSERT OVERWRITE TABLE weblog
> PARTITION (dt ='2009-04-09')
> SELECT user_id,url,source_ip WHERE dt='2009-04-09'
-- 分桶的优点：
桶的数量固定，没有数据波动
桶有利于抽样
分桶有利于执行高效的map-side JOIN
7 为表添加列
hive> CREATE TABLE weblog (version LONG,url STRING)
> PARTITIONED BY (hit_date int)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
hive> LOAD DATA LOCAL INPATH 'log1.txt' into weblogs partition(20110101);
## 添加新字段
hive> ALTER TABLE weblogs ADD COLUMNS (user_id string);
hive> LOAD DATA LOCAL INPATH 'log2.txt' into weblogs partition(20110102);
8 使用列存储表
-- hive使用行式存储，也提供了一个列式SerDe来以混合列式格式存储
十 调优
1 使用EXPLAIN
hive> EXPLAIN SELECT SUM(number) FROM onecol;
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 is a root stage
STAGE PLANS:
Stage: Stage-1
Map Reduce ##mapreduce job
Map Operator Tree:
onecol
TableScan ##tablesan以onecol输入
alias: onecol
Select Operator
expressions:
expr:number
type:int
outputColumnNames:number ##输出
Group By Operator ##groupby应用到sum
aggregations:
expr:sum(number)
bucketGroup:false
mode:hash
outputColumnNames:_col0 ##输出
Reduce Output Operator
sort order:
tag:-1
value expressions:
expr:_col0
type:bigint
Reduce Operator Tree:
Group By Operator
aggregations:
expr:sum(VALUE._col0)
bucketGroup:false
mode:mergepartial
outputColumnNames:_col0
Select Operator
expressions:
expr:_col0
type:bigint
outputColumnNames:_col0
File Output Operator ##输出为文本格式
compressed:false
GlobalTableId:0
table:
input format : org.apache.hadoop.mapred.TextIntputFormat
output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Stage: Stage-0
Fetch Operator
limit :-1 ##这个job没有limit语句，因此Stage 0没有操作
2 EXPLAIN EXTENDED 可输出更多信息
有详细的输出文件位置等信息
3 限制调整
– 很多情况下Limit语句需要执行整个查询语句，再返回部分结果;这种情况是比较浪费的，尽可能避免；
-- hive有些属性可以配置
hive.limit.row.optimize.enable = true设置后，另外2个属性还可以控制这个操作
hive.limit.row.max.size
hive.limit.optimize.limit.file
缺点是：有可能输入中有用的数据永远不会被处理
4 JOIN优化
如果所有表中有一个表足够小，是可以完成载入到内存中的，这时Hive可以执行一个map-side JOIN，这样可以减少reduce过程
5 本地模式
hive.exec.mode.local.auto = true
6 并行执行
hive.exec.parallel = true
7 严格模式
-- 可以防止用户执行那些可能产生意想不到的不好的影响的查询
hive.mapred.mode = strict
-- 防止3类查询
对于分区表，除非WHERE语句中包含分区字段过滤条件来限制数据范围，否则不允许执行
对于使用了ORDER BY语句的查询，要求必须使用LIMIT语句。因为ORDER BY为了执行排序过程会将所有的结果分发到同一个reducer处理
限制笛卡尔积的查询
8 调整mapper和reducer个数
确定最佳的mapper个数和reducer个数取决于多个变量；
设置太多会导致启动阶段、调度阶段和运行job过程产生过多的开销；
设置太少则没有充分利用好集群内在的并行性
```
-- hive通过输入量大小来确定reducer个数
## 计算输入量大小
$ hadoop dfs -count /user/media6/fracture/ins/* | tail -4
## 默认值1GB，调整为750M
hive> set hive.exec.reducer.bytes.per.reducer = 750000000
hive> SELECT pixel_id,count(1) FROM fracture_ins WHERE hit_date=20120119
> GROUP BY pixel_id;
Total MapReduce jobs =1
Launching Job 1 out of 1
Number of reducer tasks not specified.Estimated from input data size:4
...
```
351
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
