Hive Lateral View [转]

用Lateral View可以比较方便的将UDTF得到的行转列(没有理解到的请先看例子)的结果集合在一起提供服务。

Lateral view语法如下:

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)*
fromClause: FROM baseTable (lateralView)*

hive> create table sunwg ( a array, b array )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ‘\t’
> COLLECTION ITEMS TERMINATED BY ‘,’;
OK
Time taken: 1.145 seconds

hive> load data local inpath ‘/home/hjl/sunwg/sunwg.txt’ overwrite into table sunwg;
Copying data from file:/home/hjl/sunwg/sunwg.txt
Loading data to table sunwg
OK
Time taken: 0.162 seconds

hive> select * from sunwg;
OK
[10,11] [“tom”,”mary”]
[20,21] [“kate”,”tim”]
Time taken: 0.069 seconds

hive>
> SELECT a, name
> FROM sunwg LATERAL VIEW explode(b) r1 AS name;
OK
[10,11] tom
[10,11] mary
[20,21] kate
[20,21] tim
Time taken: 8.497 seconds

hive> SELECT id, name
> FROM sunwg LATERAL VIEW explode(a) r1 AS id
> LATERAL VIEW explode(b) r2 AS name;
OK
10 tom
10 mary
11 tom
11 mary
20 kate
20 tim
21 kate
21 tim
Time taken: 9.687 seconds

来源http://www.oratea.net/?p=650

Hive 计算标准差,方差

一、计算总体(当样本数量较大时)方差、标准差

设m为x1,x2…xn数列的期望值(平均数)

则方差 s^2 = [(x1-m)^2 + (x2-m)^2 + … (xn-m)^2]/n,s即为标准差

标准差:stddev stddev_pop (两者等效)

方差:    var_pop

select stddev(column_name) from table_name;

二、样本数量较小时,计算样本方差、标准差

设m为x1,x2…xn数列的期望值(平均数)

 

则方差s^2 = [(x1-m)^2 + (x2-m)^2 + … (xn-m)^2]/n-1,s即为标准差

标准差:stddev_samp

方差:    var_samp

Hive实践之动态分区

在上篇文章中“浅谈Hive中索引、分区对查询的优化”,提到了静态分区,对只有几条分区规则的时候勉强可以这么做,但是当有大量数据导入的时候,这样做未免太影响效率,所以可以开启动态分区

– 对现存hive表的分区

首先,新建一张我们需要的分区以后的表:
create table pv (click int) partitioned by (ds string);

(ps:若现存hive表中没有分区信息,我们需要手动修改hive配置数据库来增加hive表的分区信息。hive表分区存在PARTITION_KEYS数据表中,其中表项的| TBL_ID | PKEY_COMMENT | PKEY_NAME | PKEY_TYPE | INTEGER_IDX |分别是hive表名(在TBLS中存储),备注,分区名,分区索引的顺序。建立完成后我们就有一张类似于原始表,但是带有分区的表。)

然后,修改一下hive的默认设置以支持动态分区:

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

第二步,使用动态分区字段做分区索引。

然后用hive的insert命令进行插入操作。注意,除了所有列外,需要将分区的动态字段跟在后面。

hive>INSERT OVERWRITE TABLE pv PARTITION (ds)  SELECT click_pv, time FROM orgin;

这样,得到了一张分区后的hive大表。

通过hdfs看一下咯:

[hadoop@localhost Desktop]$ hadoop fs -ls /opt/hive/warehouse/pv

Found 2 items

drwxr-xr-x – root supergroup 0 2015-08-15 00:33 /opt/hive/warehouse/pv/ds=20150802

drwxr-xr-x – root supergroup 0 2015-08-15 00:33 /opt/hive/warehouse/pv/ds=20150804

另:

备注一下我这里使用的orgin,仅做测试。

hive> select * from orgin;

OK

10 20150804

10 20150802

Time taken: 0.038 seconds, Fetched: 2 row(s)

 

浅谈Hive中索引、分区对查询的优化

索引:用户可以在某些列上创建索引来加速某些操作,给一个表创建的索引数据被保存在另外的表中。 但不是所有的查询都会受惠于Hive索引。可以使用EXPLAIN语法来分析HiveQL语句是否可以使用索引来提升用户查询的性能。像RDBMS中的索引一样,需要评估索引创建的是否合理,毕竟,索引需要更多的磁盘空间,并且创建维护索引也会有一定的代价。 用户必须要权衡从索引得到的好处和代价:
可以自己在hive上尝试一下先建立一张表,然后导入一些数据,再对数据的某一列建索引。
hive> create index your_index on table your_table(your_column)
> as ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’
> with deferred rebuild
> IN TABLE your_index_table;
hive> alter index your_index on your_table rebuild;
通过select * from your_table where your_column=’xxx’;
可以比较建立索引前后查询花费的时间。
分区:在Hive Select查询中一般会扫描整个表内容,会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据,因此建表时引入了partition概念。
这个具体是怎么工作的呢?我用一个实例来说明吧:
先在hive上建个带有分区的表:
hive> create table test (a string) partitioned by (b string, c string); //用b,c字段建立分区
OK
Time taken: 0.242 seconds
hive> alter table test add partition(b=’1′,c=’1′); //手动添加一个分区信息
OK
Time taken: 0.197 seconds
然后我插入一条数据:
hive> insert into test partition(b=’1′,c=’1′) values(‘1’);
可以看到hive的提示信息:
Moving data to: hdfs://localhost:9000/opt/hive/warehouse/test/b=1/c=1
我们再在hdfs上查看这个文件夹:
[hadoop@localhost Desktop]$ hadoop fs -ls /opt/hive/warehouse/test/b=1/c=1
Found 1 items
-rw-r–r– 1 root supergroup 2 2015-08-14 23:59 /opt/hive/warehouse/test/b=1/c=1/000000_0
ok,想必已经理解到了分区是怎么一回事了,我们事先对其进行分区,然后相应的数据根据对partition的指定就会被存到hdfs上相应的文件目录下。这样子在查询的时候就可以避免扫整个表,通过文件夹目录就对数据进行读取了。

hive的搭建

背景:hadoop 1.2.1

HIVE 1.2.1

下载解压等不再赘述。

进入解压后的hive目录,进入conf

Hive的配置:
配置hive-env.sh  添加hadoop_home路径:
将export HADOOP_HOME前面的‘#’号去掉,
并让它指向您所安装hadoop的目录 (就是切换到这个目录下有hadoop的conf,lib,bin 等文件夹的目录),
(mine:HADOOP_HOME=/opt/hadoop)
其实在安装hive时需要指定HADOOP_HOME的原理基本上与
在安装Hadoop时需要指定JAVA_HOME的原理是相类似的。
Hadoop需要java作支撑,而hive需要hadoop作为支撑。
将export HIVE_CONF_DIR=/opt/hive/conf,并且把‘#’号去掉
将export HIVE_AUX_JARS_PATH=/opt/hive/lib

source /hive-env.sh(生效文件)
[root@db96 conf]# vim hive-env.sh
HADOOP_HOME=/opt/hadoop/xxxx 。。。 根据你的定
export HIVE_CONF_DIR=/opt/hadoop/xxxxx/conf
export HIVE_AUX_JARS_PATH=/opt/hive/lib
———————————————————-finish hive-env.sh
在修改之前,要相应的创建目录,以便与配置文件中的路径相对应,否则在运行hive时会报错的。
mkdir -p /opt/hive/warehouse
mkdir -p /opt/hive/tmp
mkdir -p /opt/hive/log
进入hive安装目录下的conf文件夹下:
cp  hive-default.xml.template hive-site.xml
这个文件中的配置项很多,篇幅也很长,所以要有耐心看。
当然也可以使用搜索匹配字符串的方式进行查找:
键入‘/hive.metastore.warehouse.dir’(回车)
就会锁定到所需要的字符串上。
其中有4处需要修改:
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/opt/hive/warehouse</value>
</property>
这个是设定数据目录
————————————-
<property>

<name>hive.exec.mode.local.auto</name>

<value>true</value>  —修改为true, 我是伪分布,自然先检查是否是本地模式..

<description>Let Hive determine whether to run in local mode automatically</description>

</property>

<property>

<name>hive.exec.scratchdir</name>

<value>/opt/hive/tmp</value>  — 改了

<description>HDFS root scratch dir for Hive jobs which gets created with write all (733) permission. For each connecting user, an HDFS scratch dir: ${hive.exec.scratchdir}/&lt;username&gt; is created, with ${hive.scratch.dir.permission}.</description>

</property> <property>
————————————–
再搜一个querylog, 修改为/opt/hive/log
这个是用于存放hive相关日志的目录
其余的不用修改。
————————————–finish hive-site.xml

接下来创建并修改hdfs中权限:

(如果是高版本的hadoop, 将下面命令的hadoop 换为 hdfs)

hadoop dfs -mkdir -p  /opt/hive/warehouse

hadoop dfs -mkdir -p /opt/hive/tmp

hadoop dfs -mkdir -p /opt/hive/log

hadoop dfs -chmod 777 /opt/hive/warehouse

hadoop dfs -chmod 777 /opt/hive/tmp

hadoop dfs -chmod 777 /opt/hive/log

如果还发现在启动hive的过程中,有报错的话,多半是权限问题,将相应的

hadoop dfs -chmod 777 the_directory

就好了。

持续报错的,可以选择用root来启动hive,可能是hadoop用户的一些权限问题。

然后 hive-site.xml中所有含有“system:java.io.tmpdir”的配置项的值修改为一个固定的地址,比如 /opt/hive/iotmp,也要给够权限。

现在应该可以运行了。

扩充:

默认情况下,Hive元数据保存在内嵌的 Derby 数据库中,只能允许一个会话连接,只适合简单的测试。为了支持多用户多会话,则需要一个独立的元数据库,我们使用 MySQL 作为元数据库,Hive 内部对 MySQL 提供了很好的支持,配置一个独立的元数据库需要增加以下几步骤:

一、为Hive建立相应的MySQL账户,并赋予足够的权限,执行命令如下:

hadoop@localhost:~$ mysql -uroot -pmysql
mysql> CREATE USER 'hive' IDENTIFIED BY 'mysql';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'hive'@'%' WITH GRANT OPTION;
mysql> flush privileges;

默认情况下Mysql只允许本地登录,所以需要修改配置文件将地址绑定给注释掉:

root@localhost:~# sudo gedit /etc/mysql/my.cnf

找到如下内容:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1  <---注释掉这一行就可以远程登录了

重启mysql服务:

sudo service mysql restart

二、:建立 Hive 专用的元数据库,记得创建时用刚才创建的“hive”账号登陆。

mysql> exit;
hadoop@localhost:~$ mysql -uhive -pmysql
mysql> create database hive;

三:在Hive的conf目录下的文件“hive-site.xml”中修改如下配置:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
    <property>
        <name>hive.metastore.local</name>
        <value>true</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://localhost:3306/hive?characterEncoding=UTF-8</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>hive</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>mysql</value>
    </property>
</configuration>

四、:把MySQL的JDBC驱动包复制到Hive的lib目录下。
JDBC驱动包的版本:mysql-connector-java-5.1.18-bin.jar

五、:启动 Hive Shell, 执行“show tables;”命令,如果不报错,表明基于独立元数据库的 Hive 已经安装成功了。

hadoop@localhost:~$ hive
Logging initialized using configuration in file:/home/hadoop/hive-0.9.0/conf/hive-log4j.properties
Hive history file=/tmp/hadoop/hive_job_log_hadoop_201303041631_450140463.txt
hive> show tables;
OK
Time taken: 1.988 seconds
hive>

六、:验证Hive配置是否有误,进入Hive的shell新建表,在MySQL的Hive数据库中可以看到相应的元数据库信息。
1)在Hive上建立数据表
hive> CREATE TABLE xp(id INT,name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’;
2)从 MySQL 数据库上查看元数据信息
用到的 SQL 语句:
use hive; //使用 hive 数据库库
show tables;//显示 hive 数据库中的数据表
select * from TBLS;//查看 hive 的元数据信息
到此Hive集成Mysql作为元数据已完成。

远程模式安装
远程模式安装是把 metastore 配置到远程机器上,可以配置多个。在独立模式的基础上需要在 hive-site.xml 文件中增加的配置项如下:

<property>
    <name>hive.metastore.local</name>
    <value>local</value>
</property>
<property>
    <name>hive.metastore.uris</name>
    <value>uri1,uri2,... </value>//可配置多个 uri
    <description>JDBC connect string for a JDBC metastore</description>
</property>

hive常用字符串函数(转)

写sql和hiveql较多,mark一个

字符串函数

字符串长度函数:length

 

Java代码
  1. 语法: length(string A)
  2. 返回值: int
  3. 说明:返回字符串A的长度
  4. 举例:
  5. hive> select length(‘abcedfg’) from dual;
  6. 7

 

字符串反转函数:reverse

 

Java代码
  1. 语法: reverse(string A)
  2. 返回值: string
  3. 说明:返回字符串A的反转结果
  4. 举例:
  5. hive> select reverse(‘abcedfg’) from dual;
  6. gfdecba

 

 

 

字符串连接函数:concat

 

Java代码
  1. 语法: concat(string A, string B…)
  2. 返回值: string
  3. 说明:返回输入字符串连接后的结果,支持任意个输入字符串
  4. 举例:
  5. hive> select concat(‘abc’,‘def’,’gh’) from dual;
  6. abcdefgh

 

 

 

 

带分隔符字符串连接函数:concat_ws

 

Java代码
  1. 语法: concat_ws(string SEP, string A, string B…)
  2. 返回值: string
  3. 说明:返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符
  4. 举例:
  5. hive> select concat_ws(‘,’,‘abc’,’def’,’gh’) from dual;
  6. abc,def,gh

 

 

 

 

字符串截取函数:substr,substring

 

Java代码
  1. 语法: substr(string A, int start),substring(string A, int start)
  2. 返回值: string
  3. 说明:返回字符串A从start位置到结尾的字符串
  4. 举例:
  5. hive> select substr(‘abcde’,3) from dual;
  6. cde
  7. hive> select substring(‘abcde’,3) from dual;
  8. cde
  9. hive>  select substr(‘abcde’,-1) from dual;  (和ORACLE相同)
  10. e

 

 

 

字符串截取函数:substr,substring

 

Java代码
  1. 语法: substr(string A, int start, int len),substring(string A, int start, int len)
  2. 返回值: string
  3. 说明:返回字符串A从start位置开始,长度为len的字符串
  4. 举例:
  5. hive> select substr(‘abcde’,3,2) from dual;
  6. cd
  7. hive> select substring(‘abcde’,3,2) from dual;
  8. cd
  9. hive>select substring(‘abcde’,-2,2) from dual;
  10. de

 

 

 

 

字符串转大写函数:upper,ucase

 

Java代码
  1. 语法: upper(string A) ucase(string A)
  2. 返回值: string
  3. 说明:返回字符串A的大写格式
  4. 举例:
  5. hive> select upper(‘abSEd’) from dual;
  6. ABSED
  7. hive> select ucase(‘abSEd’) from dual;
  8. ABSED

 

 

 

字符串转小写函数:lower,lcase

 

Java代码
  1. 语法: lower(string A) lcase(string A)
  2. 返回值: string
  3. 说明:返回字符串A的小写格式
  4. 举例:
  5. hive> select lower(‘abSEd’) from dual;
  6. absed
  7. hive> select lcase(‘abSEd’) from dual;
  8. absed

 

去空格函数:trim

 

Java代码
  1. 语法: trim(string A)
  2. 返回值: string
  3. 说明:去除字符串两边的空格
  4. 举例:
  5. hive> select trim(‘ abc ‘) from dual;
  6. abc

 

 

左边去空格函数:ltrim

 

Java代码
  1. 语法: ltrim(string A)
  2. 返回值: string
  3. 说明:去除字符串左边的空格
  4. 举例:
  5. hive> select ltrim(‘ abc ‘) from dual;
  6. abc

 

 

右边去空格函数:rtrim

 

Java代码
  1. 语法: rtrim(string A)
  2. 返回值: string
  3. 说明:去除字符串右边的空格
  4. 举例:
  5. hive> select rtrim(‘ abc ‘) from dual;
  6. abc

 

 

 

 

 

正则表达式解析函数:regexp_extract

其中的index,是按照正则字符串()的位置

 

Java代码
  1. 语法: regexp_extract(string subject, string pattern, int index)
  2. 返回值: string
  3. 说明:将字符串subject按照pattern正则表达式的规则拆分,返回index指定的字符。注意,在有些情况下要使用转义字符
  4. 举例:
  5. hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 1) from dual;
  6. the
  7. hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) from dual;
  8. bar
  9. hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 0) from dual;
  10. foothebar

 

 

 

 

 

函数parse_url,解析URL字符串

 

Java代码
  1. parse_url(url, partToExtract[, key]) – extracts a part from a URL
  2. 解析URL字符串,partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]。
  3. 举例:
  4. * parse_url(‘http://facebook.com/path/p1.php?query=1’‘HOST’)返回‘facebook.com’
  5. * parse_url(‘http://facebook.com/path/p1.php?query=1’‘PATH’)返回‘/path/p1.php’
  6. * parse_url(‘http://facebook.com/path/p1.php?query=1’‘QUERY’)返回‘query=1’
  7. 可以指定key来返回特定参数,例如
  8. * parse_url(‘http://facebook.com/path/p1.php?query=1’‘QUERY’,‘query’)返回‘1’
  9. * parse_url(‘http://facebook.com/path/p1.php?query=1#Ref’‘REF’)返回‘Ref’
  10. * parse_url(‘http://facebook.com/path/p1.php?query=1#Ref’‘PROTOCOL’)返回‘http’

 

json解析函数:get_json_object

语法: get_json_object(string json_string, string path)

Java代码
  1. 返回值: string
  2. 说明:解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效,那么返回NULL。
  3. 举例:
  4. hive> select  get_json_object(‘{“store”:
  5. >   {“fruit”:\[{“weight”:8,“type”:“apple”},{“weight”:9,“type”:“pear”}],
  6. >    “bicycle”:{“price”:19.95,”color”:”red”}
  7. >   },
  8. >  “email”:”amy@only_for_json_udf_test.net”,
  9. >  “owner”:”amy”
  10. > }
  11. > ‘,’$.owner’) from dual;
  12. amy

使用实例:

 

Java代码
  1. select get_json_object(‘{“store”:{“fruit”:\[“aa”,”bb”,”cc”]},”owner”:”amy”}’,‘$.store.fruit[0]’) from test_msg limit 1;

 

 

空格字符串函数:space

语法: space(int n)

Java代码
  1. 返回值: string
  2. 说明:返回长度为n的字符串
  3. 举例:
  4. hive> select space(10) from dual;
  5. hive> select length(space(10)) from dual;
  6. 10

 

 

重复字符串函数:repeat

语法: repeat(string str, int n)

Java代码
  1. 返回值: string
  2. 说明:返回重复n次后的str字符串
  3. 举例:
  4. hive> select repeat(‘abc’,5) from dual;
  5. abcabcabcabcabc

 

 

首字符ascii函数:ascii

语法: ascii(string str)

Java代码
  1. 返回值: int
  2. 说明:返回字符串str第一个字符的ascii码
  3. 举例:
  4. hive> select ascii(‘abcde’) from dual;
  5. 97

 

 

左补足函数:lpad

语法: lpad(string str, int len, string pad)

Java代码
  1. 返回值: string
  2. 说明:将str进行用pad进行左补足到len位
  3. 举例:
  4. hive> select lpad(‘abc’,10,’td’) from dual;
  5. tdtdtdtabc

 

 

与GP,ORACLE不同,pad 不能默认

右补足函数:rpad

语法: rpad(string str, int len, string pad)

Java代码
  1. 返回值: string
  2. 说明:将str进行用pad进行右补足到len位
  3. 举例:
  4. hive> select rpad(‘abc’,10,’td’) from dual;
  5. abctdtdtdt

 

 

分割字符串函数: split

语法:  split(string str, string pat)

Java代码
  1. 返回值:  array
  2. 说明: 按照pat字符串分割str,会返回分割后的字符串数组
  3. 举例:
  4. hive> select split(‘abtcdtef’,’t’) from dual;
  5. [“ab”,“cd”,“ef”]

 

 

 

集合查找函数: find_in_set

语法: find_in_set(string str, string strList)

Java代码
  1. 返回值: int
  2. 说明: 返回str在strlist第一次出现的位置,strlist是用逗号分割的字符串。如果没有找该str字符,则返回0
  3. 举例:
  4. hive> select find_in_set(‘ab’,’ef,ab,de’) from dual;
  5. 2
  6. hive> select find_in_set(‘at’,’ef,ab,de’) from dual;
  7. 0

Hive get_json_object()

  • get_json_object(json_txt, path) – Extract a json object from path
 解析json object。
 path支持JSONPath的一个子集,包括:
  * $ : Root object
  *  . : Child operator
  * [] : Subscript operator for array
  * * : Wildcard for []
 例如,src_json 表只包含一列json,其中的一行内容为:
 +-------------------------------------------------------------------+
                                json
 +-------------------------------------------------------------------+
 {"store":
   {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
    "bicycle":{"price":19.95,"color":"red"}
   },
  "email":"amy@only_for_json_udf_test.net",
  "owner":"amy"
 }
 +-------------------------------------------------------------------+
 json内容可以用以下查询语句解析
 hive> SELECT get_json_object(src_json.json, '$.owner') FROM src_json;
 amy
 hive> SELECT get_json_object(src_json.json, '$.store.fruit[0]') FROM src_json;
 {"weight":8,"type":"apple"}
 hive> SELECT get_json_object(src_json.json, '$.non_exist_key') FROM src_json;
 NULL