Hive

一、了解 Hadoop 生态的中的 Hive

1、简介

  • 分布式存储大型数据集的查询和管理
  • hive 通过类 sql 的 hql 语句转化为 hadoop 的 map reduce 程序，然后去 hadoop hdfs 中查询后返回结果
  • hive 是对 map reduce 的一层封装

2、历史

  • 起源自 facebook 由 Jeff Hammerbacher 领导的团队

  • 构建在 Hadoop 上的数据仓库框架

  • 设计的目的是让 Java 技能较弱的分析师可以通过类 SQL 查询海量数据

  • 2008 年 facebook 把 hive 项目贡献给 Apache

二、Hive 组件与体系架构

1、组件

  1. 用户接口 : shell Client、thrift(自动生成Java代码)、web UI
  3. Thrift 服务器：提供了可以远程访问其他进程的功能，也提供使用 JDBC 和 ODBC 访问 hive 的功能，这些都是基于 Thirft 实现的
  5. 元数据库 (MetAStore) "Derby,Mysql" 等
  7. 解析器 : 输入 SQL 映射为 MapReduce 的 job
  9. Hadoop : Hive 的数据都是放在 Hadoop 里面的

2、构架图

image image

3、端口

端口 说明 详细
0 0.0.0.0:10000 hiveserver 服务端口 Thrift Server 服务器

三、Hive 安装

1、三种安装模式

  • 1) 内嵌模式 元数据保持在内嵌的 Derby 数据库中，一次只能有一个连接会话。
  • 2) 本地独立模式 在本地安装 Mysql , 把元数据放到 Mysql 中
  • 3) 远程模式 元数据放到远程的 Mysql 数据库中

2、我们采用 cloudera manager 部署

四、操作 Hive

提醒事项

（读本章前，请选阅读 hive-problem.md 你会少走很多弯路，以后把遇到的问题都整理到 hive-problem.md 文档下）

1、Hive Sql

  • 部署完成以后，通过 shell 命令操作
  • 更多语法参见：链接
  1. 1) 显示所有数据库
  2.   SHOW DATABASES;
  4. 2) 使用指定数据库
  5.   use x_DATABASE;
  7. 3) 显示所有表
  8.   SHOW TABLEs;
  10. 4) 删除表
  11.   DROP TABLE xxx;
  13. 5) 删除数据库
  14.   DROP DATABASE xxx;
  16. 6) 创建表
  17.   看下面的详细说明
  19. 7) 重命名表 student to student2
  20.   ALTER TABLE student rename to student2;
  22. 8) 列操作
  23.   -- 增加列
  24.   ALTER TABLE student add columns(sage int  comment "the student's age");
  26.   -- 替换，仅保存被替换的列(可以用来删除)
  27.   ALTER TABLE student replace columns(s1 string ,s2 int, s2 int);  
  29.   -- 重命名列
  30.   ALTER TABLE TABLE_name CHANGE old_col_name new_col_name String;
  32.   -- 修改列的数据类型
  33.   ALTER TABLE TABLE_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
  34.     如：
  35.     ALTER TABLE TABLE_name CHANGE status status INT;
  38. 9) 查看表结构
  39.   DESC student;
  41.   -- 查看表结构信息(表目录/格式等详信息)
  42.   DESC formatted table;
  44.   -- 查看表的分区详细信息(表分区目录/格式等详信息)
  45.   DESC formatted table PARTITION(p_a='a',p_b='b');
  47. 9.1) 表属性修改操作
  49.   -- 修改表 SERDEPROPERTIES 信息
  50.   ALTER TABLE table SET SERDEPROPERTIES('hbase.columns.mapping'='XXX');
  52.   -- 修改表 TBLPROPERTIES 信息
  53.   ALTER TABLE table SET TBLPROPERTIES('a'='XXX');
  55. 10) 分区信息
  56.   查看分区
  57.   SHOW PARTITIONS db.tb;
  59.   SHOW PARTITIONS db.tb PARTITION(p_dt);
  61.   SHOW PARTITIONS db.tb PARTITION(p_dt='2017-04-08');
  63.   添加分区
  64.   ALTER TABLE table ADD IF NOT EXISTS PARTITION (p_dt = '2015-07-13')
  66.   删除分区
  67.   ALTER TABLE table DROP IF EXISTS PARTITION (p_dt = '2015-06-28');
  69. 11) 截断表 (貌似无用)
  70.   RUNCATE TABLE student;
  72. 12) 按照模板复制表
  73.   CREATE TABLE empty_TABLE_name LIKE TABLE_name;
  75. 13) 查看数据库结构
  76.   DESC DATABASE dbname;
  78. 14) 查看创建表语句
  79.   SHOW CREATE TABLE tb_name;
  81. 15) 创建数据指定文件路径,注意加引号
  82.   CREATE DATABASE db_name  LOCATION '/user/hive/uba_log';
  84. 16) 查询数据保存到文件中
  85.   hive -e "SELECT * FROM access_log.access_log_20150326 WHERE hostname='api.angejia.com' LIMIT 1" >> /tmp/log.log
  87. 17) 通过 sql 通过文件执行，把结果输出到文件中
  88.   bin/hive -f sql.q >> /tmp/log.log
  90. 18) CASE 字句
  91.   CASE
  92.     WHEN
  93.       a.account_type = 1 THEN 'broker'
  94.     WHEN
  95.       a.account_type = 2 THEN 'agcy'
  96.     ELSE
  97.       'CCC'
  98.   END AS start_and_end
  100. 19) JOIN
  101.   LEFT JOIN 返回左边所有符合 WHERE 语句的记录，右表匹配不上的的字段值用 NULL 代替
  102.   RIGHT JOIN 返回右边表所有符合 WHERE 语句的记录。左表匹配不上的的字段值用 NULL 代替
  104.   NOT IN 查询
  105.     ON
  106.       bs_tb.a_1 = on_tb.b_1
  107.     WHERE
  108.       on_tb.a_1 is null
  111. 20) 排序
  112.   参考文章：
  113.     http://wenku.baidu.com/link?url=j_mZCBQ7R_f_XOBjfYVSECVbS7e7qV9ajmc46_V_pN8ClJp2i-1k4mlEKQxNyr5hYYZGyHZbrrCDLGqjCmoMJtRUk_vg4QCSsR9ANZFvdbq
  115.   ORDER BY
  116.     解释：
  117.       会对输入做全局排序，因此只有一个reducer
  119.     问题：
  120.       1、在hive.mapred.mode = strict 模式下 必须指定 LIMIT 否则执行会报错
  121.       2、原因： 在order by 状态下所有数据会到一台服务器进行reduce操作也即只有一个reduce，如果在数据量大的情况下会出现无法输出结果的情况
  124.   SORT BY
  126.     解释：
  127.       sort by 不是全局排序，其在数据进入(reducer前完成排序)
  129.     问题:
  130.       1、如果用sort by进行排序，并且设置 (mapred.reduce.tASks>1)， 则sort by只保证每个reducer的输出有序，不保证全局有序
  131.       2、sort by 的数据只能保证在同一 reduce 中的数据可以按指定字段排序
  132.       3、使用sort by 你可以指定执行的reduce 个数 （SET mapred.reduce.tASks=<number>）,对输出的数据再执行归并排序，即可以得到全部结果。
  135.   distribute by
  136.     解释：
  137.       按照指定的字段,对数据进行划分到不同的,输出 reduce 文件中
  138.       人话：就是把结果按照 p_dt(如日期) 划分到同类型的 reduce 文件中
  140.     例子:
  141.       SELECT
  142.         id,
  143.         p_dt
  144.       FROM
  145.         TABLE_n
  146.       distribute by
  147.         p_dt
  150.   Cluster By
  151.     解释：
  152.       cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。  
  154.     问题： 
  155.       但是排序只能是倒序排序，不能指定排序规则为 ASC 或者DESC
  158.   组合使用
  159.     FROM
  160.       record2
  161.     SELECT
  162.       year,
  163.       temperature  
  164.     distribute by
  165.       year  
  166.     sort by
  167.       year ASC,
  168.       temperature DESC
  169.     ;
  172. 21) 模糊匹配
  173.   LIKE
  174.   RLIKE '正则'
  177. 22) 字段类型转换
  178.   WHERE cASt(aa AS float) < 1000;
  181. 23) DESCRIBE
  182.   DESCRIBE invites; 显示表结构
  183.   DESCRIBE function substr; 显示函数用法
  184.   DESCRIBE EXTENDED valid_records; 显示函数用法
  187. 24) TBLPROPERTIES 表级属性，如是否外部表，表注释等
  189.   SHOW TBLPROPERTIES db_name.tb_name;
  191.     transient_lAStDdlTime 最后创建修改表时间
  194. 25) 统计(分析和描述)
  195.   统计表的分区状态
  196.   ANALYZE TABLE [TABLEName] PARTITION([p_dt]) COMPUTE STATISTICS noscan;
  198.   1. 案例
  199.   ANALYZE TABLE db_name.tb_name PARTITION(p_dt) COMPUTE STATISTICS noscan;
  201.   ANALYZE TABLE db_name.tb_name PARTITION(p_dt='2016-04-01') COMPUTE STATISTICS noscan;
  204. 26) LOCKS 查看表锁
  205.   S 共享锁: 读锁, 事物 T 锁上对象 A 被后, 可读, 不可写. 其他事物在对象 A 上不可再加锁、不可改、只可读.
  206.   X 互斥锁: 写锁, 事物 T 锁上对象 A 被后, 可读, 可写. 其他事物在对象 A 不可再加锁、不可读、不可改.
  208.   SHOW LOCKS;  显示所有锁
  210.   SHOW LOCKS db_name.tb_name extended;  显示表锁
  212.   SHOW LOCKS db_name.tb_name PARTITION(p_dt='xx');  显示指定分区锁
  214.   UNLOCK TABLE dw_db.dw_product_safe_use_log PARTITION(p_dt='xx');  解锁分区
  217. 27) 修复表(根据文件修复分区)
  218.   MSCK REPAIR TABLE table_name
  221. 28) 设置提交任务提交队列 mapred-site.xml
  222.   mapreduce.job.queuename=root.default
  225. 29) FROM INSERT 语法
  226.   FROM (SELECT * FROM db.tb WHERE type = 'x') AS m
  227.   INSERT OVERWRITE TABLE db.tb_2 PARTITION(type)
  228.   SELECT m.*
  229.   ;

2、HIVE DDL

2.1、创建表

  2. *) 分隔符语法
  4. CREATE TABLE employees(
  5.     name STRING,
  6.     salary FLOAT,
  7.     -- 数组类型
  8.     subordinates ARRAY(STRING),
  9.     -- MAP
  10.     deductions MAP(STRING,FLOAT),
  11.     -- 映射
  12.     address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
  13. )
  14.   --必须写在下面的子句之前（stored AS 除外）
  15.   ROW FORMAT DELIMITED
  16.   --Hive 将使用 ^A 做为列分隔符
  17.   FIELDS TERMINATED BY '\001'
  18.   --Hive 将使用 ^B 做为集合元素间分隔符
  19.   COLLECTION ITEMS TERMINATED BY '\002'
  20.   --Hive 将使用 ^C 做为 MAP 的键值之间的分隔符
  21.   MAP KEYS TERMINATED BY '\003'
  22.   -- 到目录前为止 Hive 对于 lines terminated by 公支持 \n 也就是说行与行之间分隔符只能是 \n
  23.   LINES TERMINATED BY '\n'
  24.   STORED AS TEXTFILE;
  26. 文本分隔符:
  27.   \n    文本文件的换行符
  28.   ^A    分隔字段（列），在 CREATE TABLE 语句中可以使用八进制编码（\001）表示
  29.   ^B    分隔 ARRAY 或者 STRUCT 中的元素，或用于 MAP 中键值对之间的分隔，使用八进制编码（\002）表示
  30.   ^C    用于 MAP 中键和值之间的分隔，使用八进制编码（\003）表示
  34. 1) 普通表
  35.   CREATE TABLE student(
  36.     sid int,sname string
  37.   )
  38.   ROW FORMAT DELIMITED
  39.   FIELDS TERMINATED BY '\001'
  40.   LINES TERMINATED BY '\n'
  41.   STORED AS TEXTFILE;
  44. 2) 创建分区表 (ds 为分区字段)
  45. CREATE TABLE student_index(
  46.   sid int , sname string
  47.   ) PARTITIONed by (ds string)
  48. ROW FORMAT DELIMITED
  49. FIELDS TERMINATED BY '\001'
  50. LINES TERMINATED BY '\n'
  51. STORED AS TEXTFILE;
  54. 3) 创建二级分区表 (teacher、nickname 为分区字段,加上备注)
  55. CREATE TABLE clASsmem_index_1(
  56.   student string,age int
  57. ) PARTITIONed by(
  58.     teacher string comment 'the teacher',
  59.     nickname string comment 'the nickname'
  60. )
  61. ROW FORMAT DELIMITED
  62. FIELDS TERMINATED BY '\001'
  63. LINES TERMINATED BY '\n'
  64. STORED AS TEXTFILE;
  67. 4) 创建 PARQUET 文件格式的表
  68.   CREATE TABLE parquet_TABLE_name (x INT, y STRING) STORED AS PARQUET;
  69.   INSERT OVERWRITE TABLE parquet_TABLE_name SELECT * FROM other_TABLE_name;
  71.    OR
  73.   -- 使用目标表的数据, 创建一张 parquet 格式的表
  74.   CREATE TABLE IF NOT EXISTS parquet_TABLE_name
  75.   STORED AS PARQUET
  76.   AS
  77.   SELECT * FROM other_TABLE_name  LIMIT 10;
  80. 5) 创建 SEQUENCEFILE 文件格式的表
  81.   CREATE TABLE sequencefile_TABLE_name (x INT, y STRING) STORED AS SEQUENCEFILE;
  82.   INSERT OVERWRITE TABLE sequencefile_TABLE_name SELECT * FROM other_TABLE_name;
  85. 6) 创建 ORC 格式的数据表
  86.   CREATE TABLE orc_TABLE_name (x INT, y STRING) STORED AS ORC;
  87.   INSERT OVERWRITE TABLE orc_TABLE_name SELECT * FROM other_TABLE_name;
  90. 7) 创建任意格式表(案例是 TEXTFILE)
  91.   CREATE TABLE IF NOT EXISTS other_table
  92.     ROW FORMAT DELIMITED
  93.     FIELDS TERMINATED BY ','
  94.   STORED AS TEXTFILE
  95.   AS
  96.   SELECT a,b FROM table;

2.2、生产环境的创建表

可以事先把数据写入到 hadoop hdfs 中 hive 表的对应目录中，这样就可以先创建表，再导入数据了。如案例 1)

  1. 1) 创建内部(内部表根据数据库创建时的路径指定到对应的目录下)数据表，<正则格式化>
  2. CREATE TABLE access_log_20150326 (
  3.     request_time string,
  4.     upstream_response_time string,
  5.     remote_addr string,
  6.     request_length string,
  7.     upstream_addr string,
  8.     server_date string,
  9.     server_time string,
  10.     hostname string,
  11.     method string,
  12.     request_uri string,
  13.     http_code string,
  14.     bytes_sent string,
  15.     http_referer string,
  16.     user_agent string,
  17.     gzip_ratio string,
  18.     http_x_forwarded_for string,
  19.     auth string,
  20.     mobile_agent string
  21. )
  22. ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
  23. WITH SERDEPROPERTIES (
  24.   "input.regex" = "(.*?)\\t(.*?)\\t(.*?)\\t(.*?)\\t(.*?)\\t\\[(.+?)T(.+?)\\+.*?\\]\\t(.*?)\\t(.*?)\\s(.*?)\\s.*?\\t(.*?)\\t(.*?)\\t(.*?)\\t(.*?)\\t(.*?)\\t(.*?)\\t(.*?)\\t(.*?)",
  25.   "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s %10$s %11$s %12$s %13$s %14$s %15$s %16$s %17$s %18$s"
  26. )
  27. STORED AS TEXTFILE;
  30. 2) 创建内部(内部表根据数据库创建时的路径指定到对应的目录下)数据表，<json 格式化>
  31. CREATE  TABLE `uba_web_visit_log_20150326`(
  32.   `uid` string COMMENT 'FROM deserializer',
  33.   `ccid` string COMMENT 'FROM deserializer',
  34.   `referer` string COMMENT 'FROM deserializer',
  35.   `url` string COMMENT 'FROM deserializer',
  36.   `guid` string COMMENT 'FROM deserializer',
  37.   `client_time` string COMMENT 'FROM deserializer',
  38.   `page_param` string COMMENT 'FROM deserializer',
  39.   `client_param` string COMMENT 'FROM deserializer',
  40.   `server_time` string COMMENT 'FROM deserializer',
  41.   `ip` string COMMENT 'FROM deserializer',
  42.   `agent` string COMMENT 'FROM deserializer')
  43. ROW FORMAT SERDE
  44.   'org.apache.hadoop.hive.contrib.serde2.JsonSerde'
  47. 3) 创建外部表(关键字 EXTERNAL)，分区表，并且指定表的路径 <FIELDS格式化',' COLLECTION格式化'\t'>
  48. CREATE EXTERNAL TABLE student_index(
  49.   sid int ,
  50.   sname string
  51. ) PARTITIONed by (ds string)
  52. ROW FORMAT DELIMITED
  53. FIELDS TERMINATED BY '\001'
  54. LINES TERMINATED BY '\n'
  55. STORED AS TEXTFILE
  56. -- 指定表的数据源，可写可不写
  57. LOCATION 'hdfs://NameNode:8020/user/test/student_index';

3、导入数据

三种模式

  • 从本地文件导入数据
  • HDFS上导入数据到Hive表
  • 从别的表中查询出相应的数据并导入到Hive表中

3.1、 从本地文件导入数据

hive表默认的分隔符是’\001’

  1. *** 执行本地导入，OVERWRITE 表示覆盖
  2.   hive> LOAD DATA LOCAL INPATH '/root/student_two.txt' OVERWRITE INTO TABLE student;
  4. 向分区表导入数据(ds为分区字段名称，ds=111 表示把匹配111的数据，导入到当前表目录的一个文件中)
  5.   LOAD DATA LOCAL INPATH '/home/hadoop/DATA/student_index.log' OVERWRITE INTO TABLE student_index PARTITION(ds='111');
  7.   查看 dfs 文件接口
  8.   hive> dfs -ls /user/hive/warehouse/jASon_test.db/wyp;

3.2、HDFS上导入数据到Hive表

  1. *** hdfs 导入
  2.   hive> LOAD DATA INPATH '/home/wyp/add.txt' OVERWRITE INTO TABLE wyp;

3.3、从别的表中查询出相应的数据并导入到 Hive 表中

  1. 1) *** 如果需要分区插入设置线下
  2.   hive> SET hive.exec.dynamic.PARTITION.mode=nonstrict;
  4. 2)*** 其他表导入数据(其中 age 是动态的分区)
  5.   hive> INSERT INTO TABLE test
  6.   PARTITION (age)
  7.   SELECT id, name,tel, age FROM wyp;

4、hive TABLE 导出数据

三种方式

  • （1）导出到本地文件系统
  • （2）导出到 HDFS 中
  • （3）导出到 Hive 的另一个表中

4.1、导出到本地文件系统 

  1. row format delimited
  2. FIELDS TERMINATED BY '\001' 字段分隔符是 '\001'
  3. LINES TERMINATED BY '\n' 行分隔符是 '\n'
  4. 语法：
  5. INSERT OVERWRITE LOCAL directory <to_file_dir>
  6. <row format delimited>
  7. <FIELDS TERMINATED BY '\001'>
  8. <LINES TERMINATED BY '\n'>
  9. <fields>
  10. <FROM_TABLE_name>
  12. 命令：
  13. INSERT OVERWRITE LOCAL directory 'to_file_dir'
  14. row format delimited
  15. FIELDS TERMINATED BY '\001'
  16. LINES TERMINATED BY '\n'
  17. SELECT fields FROM FROM_TABLE_name;

4.2、导出到 HDFS 中

4.3、导出到 Hive TABLE 的另一个表中 

  1. 语法：
  2. INSERT INTO TABLE
  3. <to_TABLE_name>
  4. <PARTITION (age='25')>
  5. <fields>
  6. <FROM_TABLE_name>
  8. 命令：
  9. INSERT INTO TABLE uba_web_visit_log_template_20150331
  10. SELECT * FROM uba_web_visit_log_20150331;

5、表说明

5.1、表类型：

  1. 1) 普通表
  2. 一个表，就对应一个表名对应的文件目录。
  4. 2) 外部表 关键字 EXTERNAL
  5. 内部表 : 在DROP的时候会从HDFS上删除数据，
  6. 外部表 : 在DROP的时候会从HDFS不会删除。
  8. EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。具体sql如下
  10. Sql代码
  11. CREATE EXTERNAL TABLE `test_1`(
  12.   id INT,
  13.    name STRING,
  14.    city STRING
  15. )
  17. 3) 分区表
  18. 有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能.
  21. 分区表实际是一个文件夹，表名即文件夹名。每个分区，实际是表名这个文件夹下面的不同文件。分区可以根据时间、地点等等进行划分。比如，每天一个分区，等于每天存每天的数据；或者每个城市，存放每个城市的数据。每次查询数据的时候，只要写下类似 WHERE pt=2010_08_23这样的条件即可查询指定时间得数据

5.2、注意事项

  1. Hive不支持一条一条的用INSERT语句进行插入操作，也不支持update的操作。数据是以LOAD的方式，加载到建立好的表中。数据一旦导入，则不可修改。要么DROP掉整个表，要么建立新的表，导入新的数据。
  3. 数据类型
  4. TINYINT
  5. SMALLINT  
  6. INT  
  7. BIGINT  
  8. BOOLEAN
  9. FLOAT  
  10. DOUBLE  
  11. STRING
  13. 如果数据需要压缩，使用 [STORED AS SEQUENCE] 。
  14. 默认采用 [STORED AS TEXTFILE]。

6、视图

  1. 创建视图
  2. CREATE VIEW view_1 AS
  3. SELECT
  4.   id,
  5.   name,
  6.   quanpin
  7. FROM
  8.   db_sync.angejia__city;
  10. 删除视图
  11. DROP VIEW IF EXISTS TABLE_name;
  14. 动态视图
  15. CREATE IF NOT EXISTS VIEW TABLE_name AS
  16. SELECT
  17.   id,
  18.   name,
  19.   quanpin
  20. FROM
  21.   db_sync.angejia__city;