• 1. 2013年2月Hive使用入门手册
  • 2. 1.Hive数据库 2.Hive的表 3.Hive Partition Table 3.HiveQL VS SQL92 4.Hive内建MapReduce支持 5.在Shell里面执行Hive脚本 其他目录
  • 3. Hive拥有数据库或者属主的概念。 这样做的好处是避免多个用户在使用Hive对数据库对象命名的时候出现命名的冲突。 我们使用CLI命令行是看不到当前数据库是什么的。 使用如下命令,可以显示地展示当前使用的数据库。 hive> set hive.cli.print.current.db=true; 这个命令用于切换当前的数据库 hive(default)> USE financials; Hive数据库概念
  • 4. Hive数据库的使用1. 创建数据库命令 hive> CREATE DATABASE financials; 2. 删除数据库的时候,不允许删除有数据的数据库,如果数据库里面有数据则会报错。 如果要忽略这些内容,则在后面增加CASCADE关键字,则忽略报错,删除数据库。 hive> DROP DATABASE DbName CASCADE(可选); hive> DROP DATABASE IF EXISTS DbName CASCADE; 3. 查看当前有DB有啥表。 hive> SHOW TABLES IN DbName; 4. 也可以使用正则表达式 hive> SHOW TABLES LIKE 'h*' ; hive> SHOW TABLES 'h*' ; 5. 查看所有的数据库。 hive> show databases ; hive> describe databases DbName; --查看数据库信息 6. 可以通过Alter关键字修改DB相关信息 hive> ALTER DATABASE financials SET DBPROPERTIES ('edited-by' = 'Joe Dba');
  • 5. 首先Hive有内部表,和External Table外部表两种表的概念。内部表就是通过Hive自身创建的表,并由Hive本身来管理数据。 而外部表,则是通过Hive建立表的Metadata提供一些数据查询服务,比如一些HDFS文件,Pig,Hbase的文件都可以通过Hive进行批处理和查询。CREATE TABLE IF NOT EXISTS mydb.employees ( name STRING COMMENT 'Employee name', salary FLOAT COMMENT 'Employee salary', subordinates ARRAY COMMENT 'Names of subordinates', deductions MAP COMMENT 'Keys are deductions names, values are percentages', address STRUCT COMMENT 'Home address‘ ) COMMENT 'Description of the table' TBLPROPERTIES ('creator'='me', 'created_at'='2012-01-02 10:00:00', ) LOCATION '/user/hive/warehouse/mydb.db/employees'; Hive Table
  • 6. 使用外部表就使用CREATE EXTERNAL TABLE,增加External关键字,并且通过LOCATION关键字指定外部表的数据存放位置。 内部表使用LOCATION关键字时指定该表的存放在HDFS中位置。 在使用DELETE TABLE 命令的时候,内部表的数据会与表结构一起删除,而外部表则只会删除表结构。 与RDBMS一样,可以使用COMMENT关键字来给字段和表添加注释。 与RDBMS不一样,Hive支持各种数据类型,常用的是string,bigint,double 日期类型也是使用string来存储。 此外,Hive支持各种结构,如Map,Array,Struct,这些数据类型非常强大。 最后,Hive表还有一个TBLPROPERTIES用来给表添加一些描述信息,比如最后一次修改信息,最后一个修改人。 Hive Table
  • 7. create table choice(userid int,classname string) row format delimited fields terminated by '\t' STORED AS TEXT FILE LOCATION '/data/test01/daxingyu930’; STORED AS TEXTFILE 关键字指代数据的存储格式为文本格式,如果对数据进行压缩,可以使用RCFILE或者是SEQUENCE FILE。 ROW FORMAT DELIMITED FIELDS TERMINATED BY 指定读取的文本的数据分隔符,还有可以逗号,CTRL-A等等。Hive默认的分隔符是:\u0001,所以创建表格的时候需要根据实际情况修改一下。(\001) 通过:DESC TB_NAME ; 或者:DESC EXTENDED TB_NAME ; 这个也很好用:DESC FORMATTED TABLE_NAME; 可以查看表的基本信息(包括字段类型,字段名称等等), 和表的详细信息 (包括表的属性,存储的位置等)。 Hive Table
  • 8. CREATE TABLE partition_test (member_id string, name string) PARTITIONED BY (stat_date string,province string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 对于Hive来说,与Oracle的分区有很大的不同。Oracle按照字段的值进行分区,Hive的分区是指定分区,分区列也不是表中的一个实际的字段,而是一个或者多个伪列。意思是说在表的数据文件中实际上并不保存分区列的信息与数据。 Hive Partition Table这个例子中创建了stat_date和province两个字段作为分区列。 通常情况下需要先预先创建好分区,然后才能使用该分区, 例如: ALTER TABLE partition_test ADD PARTITION (stat_date='20110728',province='zhejiang'); 在Hive0.7版本之后,可以动态创建分区了。不需要再用Alter table,直接使用 INSERT OVERWRITE TABLE TB_NAME PARTITION (stat_date='20110728',province='zhejiang') SELECT member_id,name FROM tbl_test;
  • 9. 番外:分区表以外的功能BucketCREATE TABLE page_view (viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User') COMMENT 'This is the page view table' PARTITIONED BY(dt STRING, country STRING) CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS STORED AS TEXTFILE; Bucket功能类似于Oracle中的哈希分区,上述例子就是将userid做哈希数放到32个桶里面。通过如下命令对海量数据进行采样: SELECT * FROM page_view TABLESAMPLE(BUCKET 3 OUT OF 32);
  • 10. Hive Table与数据库的Table的概念是类似的,每一个Table,Parition,Bucket都会对应一个目录存储其数据。 通过DESC FORMATTED TB_NAME;可以查看 如果表A是按日期,地区分区的,则表的HDFS存储路径如下: HDFS:/ warehouse /xiaojun/dt=20100801/ctry=US 如果表A是按照USERID进行BUCKET 32份,则表的HDFS存储路径如下: HDFS:/ warehouse /xiaojun/part-00020 在Hadoop和Hive中,所有的数据都会以文件的形式存储在节点上,如果你在Hive下面使用dfs –ls命令查看HDFS目录,你会看到, 在HDFS:/ warehouse /xiaojun/part-00020 有个 tb_name.txt 这样的文件,存放着表数据 Hive的数据数据存储
  • 11. HiveQLHiveQL是一种非常类似于SQL的脚本语言,用于定义数据查询和生成MapReduce进行数据批处理的操作。 Hive使用LIMIT关键字,只返回一定数量的结果集: SELECT upper(name), salary, taxes_rate FROM employees LIMIT 2; 下面将会介绍一下HQL和标准SQL的10大不同。
  • 12. HiveQL & 标准SQL1.别名: 在SQL中,我们可以使用: SELECT user_type type FROM s_auction_auctions; 但是在HQL中,这样会报错,我们的这样写: SELECT user_type AS type FROM s_auction_auctions; 2.虚拟列 Hive中有个"虚拟列"的概念,此列并未在表中真正存在,其用意是为了将Hive中的表进行分区(partition),这对每日增长的海量数据存储而言是非常有用的。为了保证HiveQL的高效运行,强烈推荐在where语句后使用虚拟列作为限定。 SELECT url FROM web_log WHERE logdate='20090603'; 陷阱 SELECT * FROM r_winner_details r JOIN t_users s ON r.seller_id=s.user_id WHERE r.pt='20091029000000' 在Hive里面,没有SQL优化器,所以这样写的结果是,直接将r表与s表全量表连接,产生大量的MapReduce操作。
  • 13. HiveQL & 标准SQL正确的写法是: SELECT * FROM (SELECT * FROM r_winner_details WHERE pt='20091029000000') r JOIN t_users s ON r.seller_id=s.user_id 3.IN操作 在SQL里面,我们可以这样写: SELECT * FROM Persons WHERE LastName IN ('Adams','Carter'); 但在HQL里面,我们要这样写: SELECT * FROM Persons WHERE LastName = 'Adams' OR LastName = 'Carter';
  • 14. HiveQL & 标准SQL4.表连接 在SQL中,表的内连接可以写成: SELECT a.col, b.col FROM t1 a, t2 b WHERE a.id=b.id; 但是在HQL中不支持,只能用JOIN SELECT a.col, b.col FROM t1 a JOIN t2 b ON a.id=b.id; 同样,做连接和有连接要写成LEFT OUTER JOIN 和 RIGHT OUTER JOIN 5.替换IN/Exists子查询 在SQL中,我们会用SQL SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.ID=B.ID) --WHERE A.ID IN (SELECT ID FROM B) 在HQL中都变成如下写法 SELECT A.*  FROM A LEFT SEMI JOIN B on (A.ID = B.ID)
  • 15. HiveQL & 标准SQL6.分号字符的特殊处理 在SQL中,表的内连接可以写成: SELECT concat(property,concat(';',zoo)) FROM auctions; 但是在HQL中没有那么智能,只能用ASCII 码 SELECT concat(property,concat('\073',zoo)) FROM auctions; 同样,做连接和有连接要写成LEFT OUTER JOIN 和 RIGHT OUTER JOIN 7.单条INSERT语句HQL实现 Hive不支持数据插入,更新,但是可以通过一下方式插入一条或多条数据。 INSERT OVERWRITE TABLE p1 SELECT id, value FROM ( SELECT id, value FROM p1 UNION ALL SELECT 4 AS id, 5 AS value FROM p1 LIMIT 1 ) u; PS.也可以用同样的方式完成Delete,Update操作。
  • 16. HiveQL & 标准SQL8. IS [NOT] NULL SQL中null代表空值,值得警惕的是: 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False. 9.MULTI-GROUP BY 特征 FROM ( SELECT a.status, b.school, b.gender FROM status_updates a JOIN profiles b ON (a.userid = b.userid and a.ds='2009-03-20' ) ) subq1 INSERT OVERWRITE TABLE gender_summary PARTITION (ds='2009-03-20') SELECT subq1.gender, COUNT(1) GROUP BY subq1.gender INSERT OVERWRITE TABLE school_summary PARTITION (ds='2009-03-20') SELECT subq1.school, COUNT(1) GROUP BY subq1.school 一条SQL使用不同的group by key。这一特性可以减少一次MapReduce操作。
  • 17. HiveQL & 标准SQL10.Order By 和 Sort By order by 会对输入做全局排序,因此只有一个reducer(多个reducer无法保证全局有序) 只有一个reducer,会导致当输入规模较大时,需要较长的计算时间。 sort by不是全局排序,其在数据进入reduce Sort ByOrder By
  • 18. HiveQL 总而言之,编写HQL和编写SQL的理念都是相似的,尽可能地减少结果集,尽可能地做一次扫描,尽可能地使用分区。 但是,与SQL不一样的是,HQL在编写过程中没有SQL那么智能,它还是一个比较初级的,不完善的产品,需要手动地去添加一些信息,以供解释器翻译成高效的MapReduce Job。
  • 19. Hive中UDF与Oracle类似,Hive提供各种函数,供HQL查询过程中调用。那么除了自带的各种函数和聚合函数之外,还可以自定义开发各种函数。 在Hive中,函数主要分成3种类型: UDF: User Define Function 自定义函数 UDAF: User Define Aggregation Function 自定义聚合函数 UDTF: User Define Table Function 自定义表格函数 UDAF就像sum,count,avg,这种 UDTF函数,就是一种输入一行,输出多行的函数,多用于处理数组类型。有个例子的函数叫做explode()
  • 20. Hive内建MapReduce支持首先,介绍一下Hive Resources。 Hive可以在查询处理的时候,可以从本地加载一些处理资源文件,如Jar,Python脚本。在执行查询的时候,会通过Hadoop Distributed Cache将此资源共享到所有的集群上,进行处理。 使用方式: ADD { FILE[S] | JAR[S] | ARCHIVE[S] } []* LIST { FILE[S] | JAR[S] | ARCHIVE[S] } [ ..] DELETE { FILE[S] | JAR[S] | ARCHIVE[S] } [ ..] hive>Add File /tmp/excute_map.py; hive>list Files; hive>delete File /tmp/excute_map.py; PS.除了支持Jar,Python,还支持Perl,Shell,Ruby等,比较建议使用Python,因为是蛇年嘛,而且Python有很多的开源支持,资源相当丰富。
  • 21. Hive内建MapReduce支持hive>CREATE TABLE u_data_new >( userid INT, > movieid INT, > rating INT, > weekday INT) >ROW FORMAT DELIMITEDFIELDS TERMINATED BY '\t'; hive> add FILE weekday_mapper.py; hive> INSERT OVERWRITE TABLE u_data_new SELECT TRANSFORM (userid, movieid, rating, unixtime) USING ‘python weekday_mapper.py’ --这里还可以添加参数 AS (userid, movieid, rating, weekday) FROM u_data; 这里使用了一个Transform,作为Hive和自定义处理脚本的接口。这里实际上就是一个简单的Mapper操作,不知道大家发现没有。 在我们的测试环境机子:10.25.22.99上面已经安装Python环境,大家可以登上去自己试一下。
  • 22. import sys import datetime for line in sys.stdin: line = line.strip() userid, movieid, rating, unixtime = line.split('\t') weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday() print '\t'.join([userid, movieid, rating, str(weekday)]) 一个简单的Python脚本案例。 Hive内建MapReduce支持
  • 23. 在Shell里面执行Hive脚本 Linux下直接执行HiveQL >hive –e “SELECT * FROM TB_A” 如果你的Hive脚本很长 >hive –f /tmp/my_document/script.hql 如果你想将你的Hive的执行结果输出。使用Linux管道命令 > hive –e “SELECT * FROM TB_A”> /tmp/myResult.txt 在Hive中使用-f执行脚本的时候,可以通过如下方式增加注释: #** [Subject: hello world test] [Author: Daxingyu] …. *#
  • 24. Hive下直接访问HDFS在Hive命令行模式下,可以使用dfs前缀,就可以使用Hadoop dfs命令。 如常见的有: hive>dfs –ls /data; hive>dfs –lsr /data; hive>dfs –cat /data/test01/hello.txt; 还支持chmod,chown,mkdir等等。
  • 25. Thanks!