hive 安装及使用

hive解压安装

hive配置

cp hive-default.xml.template hive-site.xml
cp hive-log4j.properties.template hive-log4j.properties

修改hive-site.xml配置文件

~ vi conf/hive-site.xml

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://moon:3306/hive_metadata?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>123</value>
<description>password to use against metastore database</description>
</property>

<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>location of default database for the warehouse</description>
</property>

修改hive-log4j.properties

#log4j.appender.EventCounter=org.apache.hadoop.metrics.jvm.EventCounter
log4j.appender.EventCounter=org.apache.hadoop.log.metrics.EventCounter

配置环境变量

vi ~/.bahsrc
export HIVE_HOME=/usr/local/hive
export PATH=$PATH:$HIVE_HOME/bin

在hdfs上面，创建目录

$HADOOP_HOME/bin/hadoop fs -mkidr /tmp
$HADOOP_HOME/bin/hadoop fs -mkidr /user/hive/warehouse
$HADOOP_HOME/bin/hadoop fs -chmod g+w /tmp
$HADOOP_HOME/bin/hadoop fs -chmod g+w /user/hive/warehouse

hive client安装 ,多用户使用hive

按上述步骤安装hive（或者直接拷贝过去）
修改配置文件
[hadoop@hadoop2 conf]$ vi hive-site.xml

<property>
  <name>hive.metastore.uris</name>
  <value>thrift://hadoop3:9083</value>
  <description>Thrift uri for the remote metastore. Used by metastore client to connect to remote metastore.</description>
</property>

配置HIVE_HOME环境变量
多用户使用hive

[hadoop@hadoop3 hive013]$ bin/hive --service metastore  启动后其他客户端只有配置hive.metastore.uris==thrift://hadoop:9083才能访问metastore
[hadoop@hadoop2 hive013]$ bin/hive   启动客户端
hadoop3> ctrl+c 退出metastore后，用netstat -nlt发现9083端口已经退出
后台运行metastore
nohup bin/hive --service metastore > metastore.log 2>&1 &
后台退出
[hadoop@hadoop3 hive013]$ jobs
[hadoop@hadoop3 hive013]$ kill %num  num是后台job的id

在MySQL中创建数据库

create database hive_metadata;
grant all on hive_metadata.* to root@'%' identified by '123';
grant all on hive_metadata.* to root@localhost identified by '123';
ALTER DATABASE hive_metadata CHARACTER SET latin1;

手动上传mysql的jdbc库到hive/lib

~ ls /usr/local/hive-0.9.0/lib
mysql-connector-java-5.1.22-bin.jar

启动hive

#启动metastore服务
~ bin/hive --service metastore &
Starting Hive Metastore Server

#启动hiveserver服务
~ bin/hive --service hiveserver &
Starting Hive Thrift Server

#启动hive客户端
~ bin/hive shell
Logging initialized using configuration in file:/root/hive-0.9.0/conf/hive-log4j.properties
Hive history file=/tmp/root/hive_job_log_root_201211141845_1864939641.txt

hive> show tables
OK

查询MySQL数据库中的元数据

~ mysql -uroot -p
mysql> use hive_metadata;
Database changed

mysql> show tables;
+-------------------------+
| Tables_in_hive_metadata |
+-------------------------+
| BUCKETING_COLS          |
| CDS                     |
| COLUMNS_V2              |
| DATABASE_PARAMS         |
| DBS                     |
| IDXS                    |
| INDEX_PARAMS            |
| PARTITIONS              |
| PARTITION_KEYS          |
| PARTITION_KEY_VALS      |
| PARTITION_PARAMS        |
| PART_COL_PRIVS          |
| PART_PRIVS              |
| SDS                     |
| SD_PARAMS               |
| SEQUENCE_TABLE          |
| SERDES                  |
| SERDE_PARAMS            |
| SORT_COLS               |
| TABLE_PARAMS            |
| TBLS                    |
| TBL_COL_PRIVS           |
| TBL_PRIVS               |
+-------------------------+
23 rows in set (0.00 sec)

新建表

#创建数据(文本以tab分隔)
~ vi /usr/local/hive/my_hive/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 '/usr/local/hive/my_hive/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交互式模式

• quit,exit ;退出交互式shell
• reset: 重置配置为默认值
• set = : 修改特定变量的值(如果变量名拼写错误，不会报错)
• set : 输出用户覆盖的hive配置变量
• set -v : 输出所有Hadoop和Hive的配置变量
• add FILE[S] , add JAR[S] , add ARCHIVE[S] * : 添加 一个或多个 file, jar, archives到分布式缓存
  ## 从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://moon:9000/user/hive/warehouse/t_hive2
OK
Time taken: 0.325 seconds

从其他表导入数据

hive> INSERT OVERWRITE TABLE t_hive2 SELECT * FROM t_hive ;

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

#删除表
hive> DROP TABLE t_hive;

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

仅复制表结构不导数据

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;

参考hive安装及使用攻略