Hive

Hive 是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 QL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。

 

    首先，Hive 没有专门的数据存储格式，也没有为数据建立索引，用户可以非常自由的组织 Hive 中的表，只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。

    概述：

    1.CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。

    2.EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

    3.LIKE 允许用户复制现有的表结构，但是不复制数据。

    4.用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

    5.如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

    6.有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。 

   语法：

 

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]
 STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  LIKE existing_table_name
  [LOCATION hdfs_path]

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type

primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | STRING

array_type
  : ARRAY < data_type >

map_type
  : MAP < primitive_type, data_type >

struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>

row_format
  : DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format:
  : SEQUENCEFILE
  | TEXTFILE
  | RCFILE     (Note:  only available starting with 0.6.0)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

 目前在hive中常用的数据类型有:

 

 

   BIGINT – 主要用于状态,类别,数量的字段, 如status/option/type/quantity

 

   DOUBLE – 主要用于金额的字段, 如fee/price/bid

 

   STRING – 除上述之外的字段基本都使用String, 尤其是id和日期时间这样的字段

 

    例子：

1、如果一个表已经存在，可以使用if not exists

2、 create table htduan(id int,cont string) row format delimited fields terminated by ',' stored as textfile;

terminated by：关于来源的文本数据的字段间隔符

如果要将自定义间隔符的文件读入一个表，需要通过创建表的语句来指明输入文件间隔符，然后load data到这个表。

4、Alibaba数据库常用间隔符的读取

我们的常用间隔符一般是Ascii码5，Ascii码7等。在hive中Ascii码5用’\005’表示， Ascii码7用’\007’表示，依此类推。

5、装载数据

查看一下：Hadoop fs -ls

LOAD DATA INPATH '/user/admin/htduan/a.txt' OVERWRITE INTO TABLE htduan;

6、如果使用external建表和普通建表区别

A、指定一个位置，而不使用默认的位置。如：

create  EXTERNAL  table htduan(id int,cont string) row format delimited fields terminated by '\005' stored as textfile location '/user/admin/htduan/';

--------------check结果

ij> select LOCATION from tbls a,sds b where a.sd_id=b.sd_id and tbl_name='htduan'; 

-----

LOCATION                                                                                                                        

--------------------------------------------------------------------------------------------------------------------------------

hdfs://hadoop1:7000/user/admin/htduan  

 

ij> select LOCATION from tbls a,sds b where a.sd_id=b.sd_id and tbl_name='c';

----

LOCATION                                                                                                                       

--------------------------------------------------------------------------------------------------------------------------------

hdfs://hadoop1:7000/user/hive/warehouse/c

B、对于使用create table external建表完成后，再drop掉表，表中的数据还在文件系统中。

如：

hive> create  EXTERNAL  table htduan(id int,cont string) row format delimited fields terminated by '\005' stored as textfile;

----

OK

 

hive> LOAD DATA INPATH '/user/admin/htduan' OVERWRITE INTO TABLE htduan;

--------------------------------------------------

Loading data to table htduan

OK

 

hive> drop table htduan;

----

OK

 

[admin@hadoop1 bin]$ ./hadoop fs -ls hdfs://hadoop1:7000/user/hive/warehouse/htduan

Found 1 items

 

使用普通的建表DROP后则找不到

 

    创建分区：

 

CREATE 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
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS TEXTFILE;

 指定LOCATION位置

 

 

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
 COMMENT 'This is the staging page view table'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
 STORED AS TEXTFILE
 LOCATION '<hdfs_location>';

 复制一个空表(只复制它的表结构)

 

 

CREATE TABLE empty_key_value_store
LIKE key_value_store;

 增加partitions

 

 

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...

partition_spec:
  : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
Eg:
ALTER TABLE c02_clickstat_fatdt1 ADD 
PARTITION (dt='20101202') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20101202' 
PARTITION (dt='20101203') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20101203';

 删除partitions

 

 

ALTER TABLE table_name DROP partition_spec, partition_spec,...

ALTER TABLE c02_clickstat_fatdt1 DROP PARTITION (dt='20101202');

 改表名

 

ALTER TABLE table_name RENAME TO new_table_name

 

这个命令可以让用户为表更名。数据所在的位置和分区名并不改变。

新增替换column

 

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

 create view

 

 

CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)] 
AS SELECT ...

 load数据

 

 

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

 

LOAD DATA LOCAL INPATH `/tmp/pv_2008-06-08_us.txt` INTO TABLE c02 PARTITION(date='2008-06-08', country='US')

 从hdfs导入数据到表格并覆盖原表

 

LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20101101/18/clickstat_gp_fatdt0/0' INTO table c02_clickstat_fatdt1 OVERWRITE PARTITION (dt='20101201');

 Writing data into filesystem from queries

 

 

FROM src

  INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100

  INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200

  INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300

  INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

from xi  
insert overwrite  table test2 select  '1,2,3' limit 1 
insert overwrite  table d select  '4,5,6' limit 1;

 TopK问题

下面的查询语句查询销售记录最大的 5 个销售代表。

SET mapred.reduce.tasks = 1
  SELECT * FROM sales SORT BY amount DESC LIMIT 5

 可以在HQL中使用SET关键字设定参数，例如：

set mapred.reduce.tasks=100;

 

hive复杂类型的操作

 

Constructor Function	Operands	Description
Map	(key1, value1, key2, value2, ...)	Creates a map with the given key/value pairs
Struct	(val1, val2, val3, ...)	Creates a struct with the given field values. Struct field names will be col1, col2, ...
Array	(val1, val2, ...)	Creates an array with the given elements

 

日期函数

 

返回值类型	名称	描述
string	from_unixtime(int unixtime)	将时间戳（unix epoch秒数）转换为日期时间字符串，例如from_unixtime(0)="1970-01-01 00:00:00"
bigint	unix_timestamp()	获得当前时间戳
bigint	unix_timestamp(string date)	获得date表示的时间戳
bigint	to_date(string timestamp)	返回日期字符串，例如to_date("1970-01-01 00:00:00") = "1970-01-01"
string	year(string date)	返回年，例如year("1970-01-01 00:00:00") = 1970，year("1970-01-01") = 1970
int	month(string date)
int	day(string date) dayofmonth(date)
int	hour(string date)
int	minute(string date)
int	second(string date)
int	weekofyear(string date)
int	datediff(string enddate, string startdate)	返回enddate和startdate的天数的差，例如datediff('2009-03-01', '2009-02-27') = 2
int	date_add(string startdate, int days)	加days天数到startdate: date_add('2008-12-31', 1) = '2009-01-01'
int	date_sub(string startdate, int days)	减days天数到startdate: date_sub('2008-12-31', 1) = '2008-12-30'

字符串函数

返回值类型	名称	描述
Int	length(string A)	返回字符串长度
String	reverse(string A)	反转字符串
String	concat(string A, string B...)	合并字符串，例如concat('foo', 'bar')='foobar'。注意这一函数可以接受任意个数的参数
String	substr(string A, int start) substring(string A, int start)	返回子串，例如substr('foobar', 4)='bar'
String	substr(string A, int start, int len) substring(string A, int start, int len)	返回限定长度的子串，例如substr('foobar', 4, 1)='b'
String	upper(string A) ucase(string A)	转换为大写
String	lower(string A) lcase(string A)	转换为小写
String	trim(string A)
String	ltrim(string A)
String	rtrim(string A)
String	regexp_replace(string A, string B, string C)	Returns the string resulting from replacing all substrings in B that match the Java regular expression syntax(See Java regular expressions syntax) with C e.g. regexp_replace("foobar", "oo|ar", "") returns 'fb.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc.
String	regexp_extract(string subject, string pattern, int intex)	返回使用正则表达式提取的子字串。例如，regexp_extract('foothebar', 'foo(.*?)(bar)', 2)='bar'。注意使用特殊字符的规则：使用'\s'代表的是字符's'；空白字符需要使用'\\s'，以此类推。
String	parse_url(string urlString, string partToExtract)	解析URL字符串，partToExtract的可选项有：HOST, PATH, QUERY, REF, PROTOCOL, FILE, AUTHORITY, USERINFO。
例如，
parse_url('http://facebook.com/path/p1.php?query=1', 'HOST')='facebook.com'
parse_url('http://facebook.com/path/p1.php?query=1', 'PATH')='/path/p1.php'
parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY')='query=1'，可以指定key来返回特定参数，key的格式是QUERY:<KEY_NAME>，例如QUERY:k1
parse_url('http://facebook.com/path/p1.php?query=1&field=2','QUERY','query')='1'可以用来取出外部渲染参数key对应的value值
parse_url('http://facebook.com/path/p1.php?query=1&field=2','QUERY','field')='2'
parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'REF')='Ref'
parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'PROTOCOL')='http'
String	get_json_object(string json_string, string path)	解析json字符串。若源json字符串非法则返回NULL。path参数支持JSONPath的一个子集，包括以下标记：
$: Root object
[]: Subscript operator for array
&: Wildcard for []
.: Child operator
String	space(int n)	返回一个包含n个空格的字符串
String	repeat(string str, int n)	重复str字符串n遍
String	ascii(string str)	返回str中第一个字符的ascii码
String	lpad(string str, int len, string pad)	左端补齐str到长度为len。补齐的字符串由pad指定。
String	rpad(string str, int len, string pad)	右端补齐str到长度为len。补齐的字符串由pad指定。
Array	split(string str, string pat)	返回使用pat作为正则表达式分割str字符串的列表。例如，split('foobar', 'o')[2] = 'bar'。？不是很明白这个结果
Int	find_in_set(string str, string strList)	Returns the first occurance of str in strList where strList is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument contains any commas. e.g. find_in_set('ab', 'abc,b,ab,c,def') returns 3

 

explode函数：

1、create table test2(mycol array<int>);
2、insert OVERWRITE table test2 select * from (select array(1,2,3) from a union all select array(7,8,9)  from d)c;
3、hive> select * from test2;
OK
[1,2,3]
[7,8,9]
3、	hive> SELECT explode(myCol) AS myNewCol FROM test2;
OK
1
2
3
7
8
9

 字符集：

Hadoop和Hive都是用UTF-8编码的，所以, 所有中文必须是UTF-8编码, 才能正常使用

 

备注：中文数据load到表里面, 如果字符集不同，很有可能全是乱码需要做转码的, 但是hive本身没有函数来做这个

 

不支持HAVING操作。如果需要这个功能要嵌套一个子查询用where限制

Hive不支持where子句中的子查询

   

    优化：

1.数据量大不是问题，数据倾斜是个问题。 

2.jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，耗时很长。原因是map reduce作业初始化的时间是比较长的。

3.sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map端的汇总合并优化，使数据倾斜不成问题。  

4.count(distinct ),在数据量大的情况下，效率较低，如果是多count(distinct )效率更低，因为count(distinct)是按group by 字段分组，按distinct字段排序，一般这种分布方式是很倾斜的，比如男uv,女uv，淘宝一天30亿的pv，如果按性别分组，分配2个reduce,每个reduce处理15亿数据。

 

    常用的优化手段

1.好的模型设计事半功倍。 

2.解决数据倾斜问题。 

3.减少job数。 

4.设置合理的map reduce的task数，能有效提升性能。(比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够)。 

5.了解数据分布，自己动手解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化，但算法优化有时不能适应特定业务背景，开发人员了解业务，了解数据，可以通过业务逻辑精确有效的解决数据倾斜问题。 

6.数据量较大的情况下，慎用count(distinct)，count(distinct)容易产生倾斜问题。 

7.对小文件进行合并，是行至有效的提高调度效率的方法，假如所有的作业设置合理的文件数，对云梯的整体调度效率也会产生积极的正向影响。 

8.优化时把握整体，单个作业最优不如整体最优。

 

 临时添加UDF

 

hive> select * from test;   
OK
Hello
wORLD
ZXM
ljz
Time taken: 13.76 seconds
hive> add jar /home/work/udf.jar;                              
Added /home/work/udf.jar to class path
Added resource: /home/work/udf.jar
hive> create temporary function mytest as 'test.udf.ToLowerCase';
OK
Time taken: 0.103 seconds
hive> show functions;
......
mytest
......
hive> select mytest(test.name) from test;
......
OK
hello
world
zxm
ljz
Time taken: 38.218 seconds