HIVE你好------hive-0.13.1安装

最新推荐文章于 2019-04-29 19:29:00 发布

倒不了的塔

最新推荐文章于 2019-04-29 19:29:00 发布

阅读量449

点赞数

分类专栏： hive

本文链接：https://blog.csdn.net/sinat_19687693/article/details/43965617

版权

hive 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。

其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

Hive架构与基本组成

基本组成
•用户接口，包括 CLI，JDBC/ODBC，WebUI
•元数据存储，通常是存储在关系数据库如 mysql, derby 中
•解释器、编译器、优化器、执行器
•Hadoop：用 HDFS 进行存储，利用 MapReduce 进行计算

各组件的基本功能
•用户接口主要有三个：CLI，JDBC/ODBC和 WebUI
•CLI，即Shell命令行
•JDBC/ODBC 是 Hive 的JAVA，与使用传统数据库JDBC的方式类似
•WebGUI是通过浏览器访问 Hive
•Hive 将元数据存储在数据库中，目前只支持 mysql、derby，下一版本会支持更多的数据库。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等
•解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有 MapReduce 调用执行
•Hive 的数据存储在 HDFS 中，大部分的查询由 MapReduce 完成（包含 * 的查询，比如 select * from table 不会生成 MapRedcue 任务）

创建表并创建索引字段ds
hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

创建简单表：
hive> CREATE TABLE pokes (foo INT, bar STRING);

显示所有表：
hive> SHOW TABLES;
按正条件（正则表达式）显示表，
hive> SHOW TABLES '.*s';

表添加一列 ：
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
添加一列并增加列字段注释
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
更改表名：
hive> ALTER TABLE events RENAME TO 3koobecaf;
删除列：
hive> DROP TABLE pokes;

向数据表内加载文件
hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

HIVE0.13.1安装配置

1.解压配置环境变量

2.修改conf目录下的文件

mv hive-env.sh.template  hive-env.sh

mv hive-default.xml.template  hive-site.xml

3.修改bin目录下的hive-config.sh

最后添加：

export JAVA_HOME=/usr/jdk1.6.0_45
export HIVE_HOME=/opt/hadoop/hive
export HADOOP_HOME=/opt/hadoop/hadoop-2.4.1

4.设置相关目录

在修改之前，要相应的创建目录，以便与配置文件中的
路径相对应，否则在运行hive时会报错的。
mkdir -p /usr/hive/warehouse
mkdir -p /usr/hive/tmp
mkdir -p /usr/hive/log

修改hive-site.xml

其中有三处需要修改：
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/usr/hive/warehouse</value>
</property>
这个是设定数据目录
-------------------------------------
<property>
<name>hive.exec.scratdir</name>
<value>/usr/hive/tmp</value>
</property>
这个是设定临时文件目录
--------------------------------------
//这个在笔者的文件中没有可以自己添加
<property>
<name>hive.querylog.location</name>
<value>/usr/hive/log</value>
</property>
这个是用于存放hive相关日志的目录
其余的不用修改。
--------------------------------------finish hive-site.xml

5. 配置mysql,修改$HIVE_HOME/conf/hive-site.xml

接下来要配置的是以mysql作为存储元数据l数据库的hive的安装
要使用Hadoop来创建相应的文件路径，
并且要为它们设定权限：
hdfs dfs -mkdir -p  /usr/hive/warehouse
hdfs dfs -mkdir -p /usr/hive/tmp
hdfs dfs -mkdir -p /usr/hive/log
hdfs dfs -chmod g+w /usr/hive/warehouse
hdfs dfs -chmod g+w /usr/hive/tmp
hdfs dfs -chmod g+w /usr/hive/log
此种模式下是将hive的metadata存储在Mysql中

<property>  
  <name>javax.jdo.option.ConnectionURL</name>  
  <value>jdbc:mysql://msg-01:3306/hive</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.PersistenceManagerFactoryClass</name>  
  <value>org.datanucleus.api.jdo.JDOPersistenceManagerFactory</value>  
  <description>class implementing the jdo persistence</description>  
</property>  
  
<property>  
  <name>javax.jdo.option.DetachAllOnCommit</name>  
  <value>true</value>  
  <description>detaches all objects from session so that they can be used after transaction is committed</  
description>  
</property>  
  
<property>  
  <name>javax.jdo.option.NonTransactionalRead</name>  
  <value>true</value>  
  <description>reads outside of transactions</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>admin</value>  
  <description>password to use against metastore database</description>  
</property>

6. cp mysql-connector-java-5.1.1.18-bin .../hive/lib

测试：

#创建数据(文本以tab分隔)
~ vi /home/cos/demo/t_hive.txt

16      2       3
61      12      13
41      2       31
17      21      3
71      2       31
1       12      34
11      2       34

#创建新表
hive> CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: 0.489 seconds

#导入数据t_hive.txt到t_hive表
hive> LOAD DATA LOCAL INPATH '/home/cos/demo/t_hive.txt' OVERWRITE INTO TABLE t_hive ;
Copying data from file:/home/cos/demo/t_hive.txt
Copying file: file:/home/cos/demo/t_hive.txt
Loading data to table default.t_hive
Deleted hdfs://c1.wtmart.com:9000/user/hive/warehouse/t_hive
OK
Time taken: 0.397 seconds

查看数据

#查看表 
hive> show tables;
OK
t_hive
Time taken: 0.099 seconds

#正则匹配表名
hive>show tables '*t*';
OK
t_hive
Time taken: 0.065 seconds

#查看表数据
hive> select * from t_hive;
OK
16      2       3
61      12      13
41      2       31
17      21      3
71      2       31
1       12      34
11      2       34
Time taken: 0.264 seco

<pre name="code" class="java">#在HDFS中查找刚刚导入的数据
~ hadoop fs -cat /user/hive/warehouse/t_hive/t_hive.txt


#查看表结构
hive> desc t_hive;
OK
a       int
b       int
c       int
Time taken: 0.1 seconds

修改表

#增加一个字段
hive> ALTER TABLE t_hive ADD COLUMNS (new_col String);
OK
Time taken: 0.186 seconds
hive> desc t_hive;
OK
a       int
b       int
c       int
new_col string
Time taken: 0.086 seconds

#重命令表名
~ ALTER TABLE t_hive RENAME TO t_hadoop;
OK
Time taken: 0.45 seconds
hive> show tables;
OK
t_hadoop
Time taken: 0.07 seconds

从HDFS加载数据

创建表t_hive2
hive> CREATE TABLE t_hive2 (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

#从HDFS加载数据
hive> LOAD DATA INPATH '/user/hive/warehouse/t_hive/t_hive.txt' OVERWRITE INTO TABLE t_hive2;
Loading data to table default.t_hive2
Deleted hdfs://c1.wtmart.com:9000/user/hive/warehouse/t_hive2
OK
Time taken: 0.325 seconds

#查看数据
hive> select * from t_hive2;
OK
16      2       3
61      12      13
41      2       31
17      21      3
71      2       31
1       12      34
11      2       34
Time taken: 0.287 seconds

创建表并从其他表导入数据

#创建表并从其他表导入数据
hive> CREATE TABLE t_hive AS SELECT * FROM t_hive2 ;

Total MapReduce jobs = 2
Launching Job 1 out of 2
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201307131407_0003, Tracking URL = http://c1.wtmart.com:50030/jobdetails.jsp?jobid=job_201307131407_0003
Kill Command = /home/cos/toolkit/hadoop-1.0.3/libexec/../bin/hadoop job  -Dmapred.job.tracker=hdfs://c1.wtmart.com:9001 -kill job_201307131407_0003
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2013-07-16 10:36:48,612 Stage-1 map = 0%,  reduce = 0%
2013-07-16 10:36:54,648 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.13 sec
2013-07-16 10:36:55,657 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.13 sec
2013-07-16 10:36:56,666 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.13 sec
2013-07-16 10:36:57,673 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.13 sec
2013-07-16 10:36:58,683 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.13 sec
2013-07-16 10:36:59,691 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.13 sec
MapReduce Total cumulative CPU time: 1 seconds 130 msec
Ended Job = job_201307131407_0003
Ended Job = -670956236, job is filtered out (removed at runtime).
Moving data to: hdfs://c1.wtmart.com:9000/tmp/hive-cos/hive_2013-07-16_10-36-39_986_1343249562812540343/-ext-10001
Moving data to: hdfs://c1.wtmart.com:9000/user/hive/warehouse/t_hive
Table default.t_hive stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 56, raw_data_size: 0]
7 Rows loaded to hdfs://c1.wtmart.com:9000/tmp/hive-cos/hive_2013-07-16_10-36-39_986_1343249562812540343/-ext-10000
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 1.13 sec   HDFS Read: 272 HDFS Write: 56 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 130 msec
OK
Time taken: 20.13 seconds

hive> select * from t_hive;
OK
16      2       3
61      12      13
41      2       31
17      21      3
71      2       31
1       12      34
11      2       34
Time taken: 0.109 seconds

仅复制表结构不导数据

hive> CREATE TABLE t_hive3 LIKE t_hive;
hive> select * from t_hive3;
OK
Time taken: 0.077 seconds

通过Hive导出到本地文件系统

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/t_hive' SELECT * FROM t_hive;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201307131407_0005, Tracking URL = http://c1.wtmart.com:50030/jobdetails.jsp?jobid=job_201307131407_0005
Kill Command = /home/cos/toolkit/hadoop-1.0.3/libexec/../bin/hadoop job  -Dmapred.job.tracker=hdfs://c1.wtmart.com:9001 -kill job_201307131407_0005
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2013-07-16 10:46:24,774 Stage-1 map = 0%,  reduce = 0%
2013-07-16 10:46:30,823 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.87 sec
2013-07-16 10:46:31,833 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.87 sec
2013-07-16 10:46:32,844 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.87 sec
2013-07-16 10:46:33,856 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.87 sec
2013-07-16 10:46:34,865 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.87 sec
2013-07-16 10:46:35,873 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.87 sec
2013-07-16 10:46:36,884 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 0.87 sec
MapReduce Total cumulative CPU time: 870 msec
Ended Job = job_201307131407_0005
Copying data to local directory /tmp/t_hive
Copying data to local directory /tmp/t_hive
7 Rows loaded to /tmp/t_hive
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 0.87 sec   HDFS Read: 271 HDFS Write: 56 SUCCESS
Total MapReduce CPU Time Spent: 870 msec
OK
Time taken: 23.369 seconds

#查看本地操作系统
hive> ! cat /tmp/t_hive/000000_0;
hive> 1623
611213
41231
17213
71231
11234
11234

普通查询：排序，列别名，嵌套子查询

hive> FROM (
    >   SELECT b,c as c2 FROM t_hive
    > ) t
    > SELECT t.b, t.c2
    > WHERE b>2
    > LIMIT 2;
12      13
21      3

连接查询：JOIN

hive> SELECT t1.a,t1.b,t2.a,t2.b
    > FROM t_hive t1 JOIN t_hive2 t2 on t1.a=t2.a
    > WHERE t1.c>10;

1       12      1       12
11      2       11      2
41      2       41      2
61      12      61      12
71      2       71      2

倒不了的塔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
HIVE你好------hive-0.13.1安装

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。Hive架构与基本组成基本组成•用户接口，包括 CLI，J
复制链接

扫一扫

专栏目录