Hive搭建&基本操作

最新推荐文章于 2024-04-07 19:42:46 发布

qq_34795664

最新推荐文章于 2024-04-07 19:42:46 发布

阅读量338

点赞数

分类专栏： hive 文章标签： hive

本文链接：https://blog.csdn.net/qq_34795664/article/details/79942390

版权

hive 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.Hive 安装部署

Hive 安装前需要安装好 JDK 和 Hadoop。配置好环境变量。
根据元数据存储的介质不同，分为下面两个版本，其中 derby 属于内嵌模式。实际生产环境中则使用 mysql 来进行元数据的存储。

## 内置 derby 版：

解压 hive 安装包
bin/hive 启动即可使用
缺点：不同路径启动 hive，每一个 hive 拥有一套自己的元数据，无法共享
当我们在摸一个路径启动hive时,最后生成的数据文件即保存在我们所在目录下,数据无法共享.

mysql 版：
（a）配置HIVE_HOME环境变量

vi conf/hive-env.sh 
        配置其中的$hadoop_home

（b）解压、修改配置文件

vi conf/hive-site.xml

#添加如下内容：
        <configuration>
        <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>
        <description>JDBC connect string for a JDBC metastore</description>
        </property>

        <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
        <description>Driver class name for a JDBC metastore</description>
        </property>

        #数据库用户登录名
        <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
        <description>username to use against metastore database</description>
        </property>
        #数据库用户登录密码
        <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>root</value>
        <description>password to use against metastore database</description>
        </property>
        </configuration>

配置 Mysql 元数据库信息

 yum install mysql mysql-server mysql-devel 

 完成后，用  /etc/init.d/mysqld start    启动mysql
 设置成开机自启  chkconfig mysqld on

 启动mysql控制台： 
      mysql
    mysql>; USE mysql; 
    mysql>; UPDATE user SET Password=PASSWORD('newpassword') WHERE user='root'; 
    mysql>; FLUSH PRIVILEGES; 
     允许远程登录 
    mysql -u root -p 
    Enter Password: <your new password> 
    mysql>GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION; 
     完成后就能远程管理mysql了。

2.Hive的几种启动方式

    1.Hive交互shell       bin/hive

    2.Hive JDBC服务(参考java jdbc连接mysql)

    3.hive启动为一个服务器，来对外提供服务
        bin/hiveserver2
        nohup bin/hiveserver2 1>/var/log/hiveserver.log 2>/var/log/hiveserver.err &

        启动成功后，可以在别的节点上用beeline去连接
        bin/beeline -u jdbc:hive2://mini1:10000 -n root

        或者
        bin/beeline
        ! connect jdbc:hive2://mini1:10000

    #####登陆的用户是虚拟机主机名字或者用户名
    #####登录密码是主机密码或者用户密码

    4.Hive命令 
        hive  -e  ‘sql’
        bin/hive -e 'select * from t_test'

3.建表语句

内部表

create table t_test(id int,name string,age int);
当我们创建一个表时,会自动在HDFS上生成一个user目录,里面最终存放我们要分析的数据,所以我们要把数据上禅道文件夹下
/user/hive/warehouse/test.db/与文件数据
但是我们查询表时,会显示表中的内容为null,所以我们要进行表数据进行关联

关联数据表建表

create table t_test2(id int,name string,age int) row format delimited fields terminated by ',';
将数据上传到HDFS自动生成的路径下
/user/hive/warehouse/test.db/t_test2
我们在进行表数据的查询,就可以查询表内的数据

表关系的映射

#####   hive数据库------------>/user/hive/warehouse   数据库名.db（文件夹）
        数据库下表------------> 数据库名.db（文件夹） 子文件夹  命名就是表名
        因此要想映射的数据目前来看必须在这个数据库表所对应的文件夹下面

#####   要想映射成功  表的字段个数 类型需要跟结构化数据文件中列的个数 类型要对应上

        要想映射成功  还需要在建表的时候根据结构化数据的列之间的分隔符 建表的时候指定
        row format delimited fields terminated by ‘’；

   如果不指定分隔符 还想映射成功 就要求结构化数据的分隔符是\001 这个是hive默认分隔符
   在vi 编辑模式下 Ctrl+v 然后 Ctrl+a 即可输入'\001' -----------> ^A

分隔符

create table t_map(id int,name string,hobby map<string,string>)
    row format delimited                
    fields terminated by ','            ##字段
    collection items terminated by '-'  ##数组
    map keys terminated by ':' ;        ##map集合

    数据：
    1,zhangsan,唱歌:非常喜欢-跳舞:喜欢-游泳:一般般
    2,lisi,打游戏:非常喜欢-篮球:不喜欢

分区表
- 没有分区表的数据当涉及到where查询的时候全表扫描效率低下浪费资源分区表
- 传统的hadoop fs -put已经不能映射成功了得换一种加载数据方式

##### 单分区建表语句：create table day_table (id int, content string) partitioned by (dt string);单分区表，按天分区，在表结构中存在id，content，dt三列。

##### 双分区建表语句：create table day_hour_table (id int, content string) partitioned by (dt string, hour string);双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。

##### 创建分区表
create table t_user2(id int,name string,age int) partitioned by (country string) row format delimited fields terminated by ',';

##### 将本地的数据加载到数据库中,用country='USA'进行分区
LOAD DATA local INPATH '/root/hivedata/a.txt' INTO TABLE t_user2 partition(country='USA');
###将本地的数据加载到数据库中,用country='CHINA'进行分区
LOAD DATA local INPATH '/root/hivedata/b.txt' INTO TABLE t_user2 partition(country='CHINA');

##### 查看分区：
 show partitions day_hour_table;

##### 关于分区表:
  1、是一种更加细致组织文件的形式,把文件划分的更加规范,再进行全表查询的时候,不需要进行全表扫描
  2、分区表的字段不能够是表结构中已经存在的字段
  3、分区表的现象,在表所在的那个文件夹下面,以分区的字段和分区字段值,再建立一个子文件夹,分区字段=分区值
      然后再把对应的结构化数据文件,放在这个文件夹下面 
      这样的好处就在于,当指定查询某些范围的时候,直接去指定分区下面查询,前提是你分区的字段就是你查询条件
      否则还是进行全局扫描
**4、虽然进行select 分区表能够看到分区字段,和分区字段值,但是分区字段是个虚拟的字段,并不真实存在于结构化数据文件当中

分桶表

#分桶表（cluster by  into num buckets）

#指定开启分桶
set hive.enforce.bucketing = true;默认为false
set mapreduce.job.reduces=4;

#reduce等于几 最终输出的结果就是几份
#分桶表的底层才是真正的  mr当中的Partitioner分区+ReduceTask        sql最终是转换成mr执行的


#创建分桶表
create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)
clustered by(Sno) 
sorted by(Sno DESC)
into 4 buckets
row format delimited
fields terminated by ',';

#分桶表导入数据
insert overwrite table stu_buck
select * from student cluster by(Sno);

#创建一个表用于存储分区表的数据
create table student(Sno int,Sname string,Sex string,Sage int,Sdept string)
row format delimited
fields terminated by ',';

分桶表的数据加载形式
- 1.直接把结构化的数据hadoop fs -put 到表的文件夹下面 ========》一定不行底层没有mr执行
- 2.LOAD DATA
LOAD DATA local INPATH ‘/usr/local/data/hivedata/student.txt’ INTO TABLE stu_buck cluster(Sno);

也不行,hive就不支持这种语法加载桶表数据
- 使用insert+ select 桶表得采用这种方式才可以加载数据

总结：分桶表&分簇表,同样是把结构化的数据文件划分颗粒更加细致有别于分区表在文件夹上做文章这里是在文件层面把文件划分开几个部分也就是几桶

1、分桶表的功能需要提前开启默认不开启
2、分桶表的字段一定要是表中存在的字段
3、建表的时候分为几桶 INTO X BUCKETS，就需要在环境中设置set mapreduce.job.reduces= X，需要对应上。

当我们建表的桶个数跟执行环境中 hiveconf mapreduce.job.reduces 个数不一致的话会在insert的时候由hive做出改变

使得结果文件符合表的定义这时候就没有规律可行的

hive的各种定义只是一种软性约束就要求使用者一定要结合具体情况对应上当不对应上 hive尝试做转换但是保证符合你最终的样子
必读理解

    桶的概念就是MapReduce的分区的概念，两者完全相同。物理上每个桶就是目录里的一个文件，一个作业产生的桶（输出文件）数量和reduce任务个数相同。
而分区表的概念，则是新的概念。分区代表文件夹目录。每个文件夹下面可以放不同的数据文件。通过文件夹可以查询里面存放的文件。但文件夹本身和数据的内容毫无关系。
    桶则是按照数据内容的某个值进行分桶，把一个大文件散列称为一个个小文件。
    这些小文件可以单独排序。如果另外一个表也按照同样的规则分成了一个个小文件。两个表join的时候，就不必要扫描整个表，只需要匹配相同分桶的数据即可。效率当然大大提升。
    同样，对数据抽样的时候，也不需要扫描整个文件。只需要对每个分区按照相同规则抽取一部分数据即可。

当执行hive语句时,可以设置为本地模式,查询速度更快,单线程的

set hive.exec.mode.local.auto=true;

3.1 DDL 操作

3.1.1创建表

建表语法

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]
[LOCATION hdfs_path]

说明：
- 1、 CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS选项来忽略这个异常。
- 2、 EXTERNAL L 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（ LOCATION）。
```
#建内部表

create table student(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',';


#建外部表

create external table student_ext(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',' location '/stu';


#外部表读取hdfs上存储的数据到表student_ext

load data inpath '/stu' into table student_ext;


#当一个表的数据读取时没有local在删除表时，只是删除表的数据，存储在HDFS上的数据依然存在

drop table student_ext;
```
当hive操作外部表时读取HDFS上/stu 文件夹下的文件时，文件夹名发生改变
- Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
- 3、 LIKE 允许用户复制现有的表结构，但是不复制数据。
CREATE [EXTERNAL] TABLE [IF NOT EXISTS][db_name.]table_name LIKE existing_table;
- 4、 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,...)]

hive 建表的时候默认的分割符是’\001’，若在建表的时候没有指明分隔符，load 文件的时候文件的分隔符需要是’\001’；若文件分隔符不是’001’，程序不会报错，但表查询的结果会全部为’null’；
- 用 vi 编辑器 Ctrl+v 然后 Ctrl+a 即可输入’\001’ ———–> ^A
- SerDe 是 Serialize/Deserilize 的简称，目的是用于序列化和反序列化。
- Hive 读取文件机制：首先调用 InputFormat（默认 TextInputFormat），返回一条一条记录（默认是一行对应一条记录）。然后调用SerDe （默认LazySimpleSerDe）的 Deserializer，将一条记录切分为各个字段（默认’\001’）。Hive 写文件机制：将 Row 写入文件时，主要调用 OutputFormat、SerDe 的Seriliazer，顺序与读取相反。可通过 desc formatted 表名；进行相关信息查看。当我们的数据格式比较特殊的时候，可以自定义 SerDe。
  - 5、 PARTITIONED BY
在 hive Select 查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了 partition 分区概念。
分区表指的是在创建表时指定的 partition 的分区空间。一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。表和列名不区分大小写。分区是以字段的形式在表结构中存在，通过 e describe table 命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。
- 6、 STORED AS SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 **STORED AS SEQUENCEFILE。**TEXTFILE 是默认的文件格式，使用 DELIMITED 子句来读取分隔的文件。
- 7、 CLUSTERED BY INTO num_buckets BUCKETS
对于每一个表（table）或者分，Hive 可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive 也是针对某一列进行桶的组织。Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如 JOIN 操作。对于 JOIN 操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行 JOIN 操作就可以，可以大大较少 JOIN 的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

3.1.2修改表

增加分区：

ALTER TABLE table_name ADD PARTITION (dt='20170101') location
'/user/hadoop/warehouse/table_name/dt=20170101'; //一次添加一个分区
ALTER TABLE table_name ADD PARTITION (dt='2008-08-08', country='us') location
'/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location
'/path/to/us/part080809'; //一次添加多个分区

删除分区

ALTER TABLE table_name DROP IF EXISTS PARTITION (dt='2008-08-08');
ALTER TABLE table_name DROP IF EXISTS PARTITION (dt='2008-08-08', country='us');

修改分区

ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');

添加列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name STRING);
注： ADD 是代表新增一个字段，新增字段位置在所有列后面 (partition 列前 ) REPLACE 则是表示替换表中所有字段。

修改列

test_change (a int, b int, c int);
ALTER TABLE test_change CHANGE a a1 INT; //修改 a 字段名
// will change column a's name to a1, a's data type to string, and put it after column b. The new
table's structure is: b int, a1 string, c int
ALTER TABLE test_change CHANGE a a1 STRING AFTER b;
// will change column b's name to b1, and put it as the first column. The new table's structure is:
b1 int, a ints, c int
ALTER TABLE test_change CHANGE b b1 INT FIRST;

表重命名

ALTER TABLE table_name RENAME TO new_table_name

3.1.3显示命令

show tables;
显示当前数据库所有表
show databases |schemas;
显示所有数据库
show partitions table_name;
显示表分区信息，不是分区表执行报错
show functions;
显示当前版本 hive 支持的所有方法
desc extended table_name;
查看表信息
desc formatted table_name;
查看表信息（格式化美观）
describe database database_name;
查看数据库相关信息

3.2 DML 操作

3.2.1 Load

在将数据加载到表中时，Hive 不会进行任何转换。加载操作是将数据文件移动到与 Hive表对应的位置的纯复制/移动操作。
- 语法结构

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

说明：

带local:
load data local inpath '/root/123' into table t_t1;
本质上就是由hive去执行 hadoop fs -put  /root/123   /user/hive/warehouse/test.db/t_t1   复制操作

不带local：
load data inpath '/123' into table t_t2;    操作HDFS的某一个目录
本质上就是由hive去执行  hadoop fs -mv  /123 /user/hive/warehouse/test.db/t_t2   移动操作

1.filepath filepath

相对路径，例如：project/data1

绝对路径，例如：/user/hive/project/data1

完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1
filepath 可以引用一个文件（在这种情况下，Hive 将文件移动到表中），或者它可以是一个目录（在这种情况下，Hive 将把该目录中的所有文件移动到表中）。

2.LOCAL

如果指定了 LOCAL， load 命令将在本地文件系统中查找文件路径。load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。如果没有指定 LOCAL 关键字，如果 filepath 指向的是一个完整的 URI，hive会直接使用这个 URI。否则：如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了Namenode 的 URI。

3.OVERWRITE

如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

3.2.2.Insert

Hive 中 insert 主要是结合 select 查询语句使用，将查询结果插入到表中，例如：

insert overwrite table stu_buck
select * from student cluster by(Sno);

需要保证查询结果列的数目和需要插入数据表格的列数目一致.
如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为 NULL。
可以将一个表查询出来的数据插入到原表中, 结果相当于自我复制了一份数据。

Multi Inserts 多重插入:

from source_table
insert overwrite table tablename1 [partition (partcol1=val1,partclo2=val2)]
select_statement1
insert overwrite table tablename2 [partition (partcol1=val1,partclo2=val2)]
select_statement2..

#多重插入：

create table source_table (id int, name string) row format delimited fields terminated by ',';
create table test_insert1 (id int) row format delimited fields terminated by ',';
create table test_insert2 (name string) row format delimited fields terminated by ',';

#插入值
from source_table                     
insert overwrite table test_insert1 
select id
insert overwrite table test_insert2
select name;

Dynamic partition inserts 动态分区插入:

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...)
select_statement FROM from_statement

动态分区是通过位置来对应分区值的。原始表 select 出来的值和输出 partition的值的关系仅仅是通过位置来确定的，和名字并没有关系。
导出表数据
语法结构

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...
multiple inserts:
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

实际代码：

set hive.exec.dynamic.partition=true;    #是否开启动态分区功能，默认false关闭。
set hive.exec.dynamic.partition.mode=nonstrict;   #动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。


#需求：
将dynamic_partition_table中的数据按照时间(day)，插入到目标表d_p_t的相应分区中。

#原始表：
create table dynamic_partition_table(day string,ip string)row format delimited fields terminated by ","; 

load data local inpath '/root/hivedata/dynamic_partition_table.txt' into table dynamic_partition_table;

#数据
2015-05-10,ip1
2015-05-10,ip2
2015-06-14,ip3
2015-06-14,ip4
2015-06-15,ip1
2015-06-15,ip2

#目标表：
create table d_p_t(ip string) partitioned by (month string,day string);

#动态插入：
insert overwrite table d_p_t partition (month,day) 
select ip,substr(day,1,7) as month,day 
from dynamic_partition_table;

#将查询结果保存到指定的文件目录（可以是本地，也可以是hdfs）最终文件个数与reduce task 个数相关
insert overwrite local directory '/root/123'
select * from t_p;

insert overwrite directory '/aaa/test'
select * from t_p;

数据写入到文件系统时进行文本序列化，且每列用^A 来区分，\n 为换行符。

3.2.3. Select

基本的 Select 操作
- 语法结构

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
JOIN table_other ON expr
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list][SORT BY| ORDER BY col_list]
]
[LIMIT number]

说明：cluster by 和 sort by 不能共存

1、order by 会对输入做全局排序，因此只有一个 reducer，会导致当输入规模较大时，
需要较长的计算时间。
2、sort by 不是全局排序，其在数据进入 reducer 前完成排序。因此，如果用 sort by 进
行排序，并且设置 mapred.reduce.tasks>1，则 sort by 只保证每个 reducer 的输出有序，不保
证全局有序。
3、distribute by(字段)根据指定字段将数据分到不同的 reducer，分发算法是 hash 散列。
4、Cluster by(字段) 除了具有 Distribute by 的功能外，还会对该字段进行排序。
如果 distribute 和 sort 的字段是同一个时，此时
cluster by = distribute by + sort by

reducetask个数

设置reduce task 个数
set mapreduce.job.reduces=？？？

Number of reduce tasks not specified. Estimated from input data size: 1
Number of reduce tasks not specified. Defaulting to jobconf value of: 2
Number of reduce tasks not specified. Defaulting to jobconf value of: 4

3.3Hive join

Hive 中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持 LEFT SEMI JOIN 和 CROSS JOIN，但这两种 JOIN 类型也可以用前面的代替。

Hive 支持等值连接（a.id = b.id ）, , 不支持非等值( (a.id>b.id) ) 的连接，因为非等值连接非常难转化到 map/reduce 任务。另外，Hive 支持多 2 个以上表之间的 join。写 join 查询时，需要注意几个关键点：

join 时，每次 map/reduce 任务的逻辑：

reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。

LEFT ， RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val，当a.key=b.key 时，而当 b.key 中找不到等值的 a.key 记录时也会输出:

a.val, NULL所以 a 表中的所有记录都被保留了；“a RIGHT OUTER JOIN b”会保留所有 b 表的记录。

Join 发生在 WHERE 子句之前。

如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写。这里面一个容易混淆的问题是表分区的情况：

SELECT a.val, b.val FROM a
LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

这会 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的记录。WHERE 从句中可以使用其他列作为过滤条件。但是，如前所述，如果 b 表中找不到对应 a 表的记录，b 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 b 表中不能找到匹配 a 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b
ON (a.key=b.key AND
b.ds='2009-07-07' AND
a.ds='2009-07-07')

这一查询的结果是预先在 join 阶段过滤过的，所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的 join 中。

Join 是不能交换位置的。

无论是 LEFT 还是 RIGHT join，都是左连接的。

SELECT a.val1, a.val2, b.val, c.val
FROM a
JOIN b ON (a.key = b.key)
LEFT OUTER JOIN c ON (a.key = c.key)

先 join a 表到 b 表，丢弃掉所有 join key 中不匹配的记录，然后用这一中间结果和 c 表做 join。

4.UDF 开发实例

新建 JAVA maven 项目
- 添加 hive-exec-1.2.1.jar 和 hadoop-common-2.7.4.jar 依赖
1、写一个 java 类，继承 UDF，并重载 evaluate 方法

public class Lower extends UDF{
public Text evaluate(Text s){
if(s==null){return null;}
return new Text(s.toString().toLowerCase());
}
}

2、打成 jar 包上传到服务器
3、将 jar 包添加到 hive 的 classpath

hive>add JAR /home/hadoop/udf.jar;

4、创建临时函数与开发好的 java class 关联

create temporary function tolowercase as
'cn.test.bigdata.udf.ToProvince';

5、即可在 hql 中使用自定义的函数 tolowercase ip

Select tolowercase(name),age from t_test;

4.1Transform 实现

Hive 的 TRANSFORM 关键字提供了在 SQL 中调用自写脚本的功能
适合实现 Hive 中没有的功能又不想写 UDF 的情况
- 使用示例 1：下面这句 sql 就是借用了 weekday_mapper.py 对数据进行了处理.

add FILE weekday_mapper.py;
INSERT OVERWRITE TABLE u_data_new
SELECT
TRANSFORM (movieid , rate, timestring,uid)
USING 'python weekday_mapper.py'
AS (movieid, rating, weekday,userid)
FROM t_rating;

其中 weekday_mapper.py 内容如下

#!/bin/python

import sys

import datetime

for line in sys.stdin:

line = line.strip()

movieid, rating, unixtime,userid = line.split('\t')

weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()

print '\t'.join([movieid, rating, str(weekday),userid])

4.2Hive 特殊分隔符处理

hive 读取数据的机制：
- 首先用 InputFormat< 默认是：org.apache.hadoop.mapred.TextInputFormat >的一个具体实现类读入文件数据，返回一条一条的记录（可以是行，或者是你逻辑中的“行”）然后利用 SerDe< 默认：org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe>的一个具体实现类，对上面返回的一条一条的记录进行字段切割。
- Hive 对文件中字段的分隔符默认情况下只支持单字节分隔符，如果数据文件中的分隔符是多字符的，如下所示：

01||zhangsan
02||lisi

可用使用 RegexSerDe 通过正则表达式来抽取字段

drop table t_bi_reg;
create table t_bi_reg(id string,name string)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
'input.regex'='(.*)\\|\\|(.*)',
'output.format.string'='%1$s %2$s'
)
stored as textfile;
hive>load data local inpath '/root/hivedata/bi.dat' into table t_bi_reg;
hive>select * from t_bi_reg;

其中：
- input.regex：输入的正则表达式
- 表示 || 左右两边任意字符被抽取为一个字段
- output.format.string：输出的正则表达式
- %1 $s %2$ s 则分别表示表中的第一个字段、第二个地段
注意事项：
a、使用 RegexSerDe 类时，所有的字段必须为 string
b、input.regex 里面，以一个匹配组，表示一个字段

qq_34795664

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Hive搭建&基本操作

1.Hive 安装部署 Hive 安装前需要安装好 JDK 和 Hadoop。配置好环境变量。根据元数据存储的介质不同，分为下面两个版本，其中 derby 属于内嵌模式。实际生产环境中则使用 mysql 来进行元数据的存储。## 内置 derby 版：解压 hive 安装包bin/hive 启动即可使用缺点：不同路径启动 hive，每一个 hive 拥有一...
复制链接

扫一扫