Hive安装、配置及基本测试

Hive安装及基本测试

1.        下载并解压至/home/hive-0.10目录下;

2.        在/etc/profile中添加：

export HIVE_HOME=/home/hive-0.10 export PATH=$HIVE_HOME/bin:$PATH

3.        在hdfs上创建hive所需目录：

bin/hadoop fs -mkdir    /tmp hadoop fs -mkdir       /user/hive/warehouse hadoop fs -chmod g+w   /tmp hadoop fs -chmod g+w   /user/hive/warehouse

4.        运行：

Hive

5.        使用hive：

#创建表 CREATE TABLE words (   word STRING,   count INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE;

#查看表结构： desc words #返回： word string count int

Hive安装成功！

 

 

 

使用MySQL数据库

1.        在MySQL中创建数据库

 

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

 

2.        下载MySql JDBC驱动：

http://downloads.mysql.com/archives/c-j/

将mysql-connector-java-5.1.30-bin.jar放入hive文件夹/lib目录下；

3.        配置

（可参考：http://blog.csdn.net/badboy_1990/article/details/37764229）

进入hive文件夹下/conf把所有的模板文件copy一份

cp hive-default.xml.template  hive-site.xml cp hive-env.sh.template   hive-env.sh cp  hive-log4j.properties.template hive-log4j.properties     cp hive-exec-log4j.properties.template  hive-exec-log4j.properties

4.        修改hive-default.xml

vi conf/hive-site.xml   <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://localhost/hive?create=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>hive</value><!—mysql用户名--> <description>username to use against metastore database</description> </property>   <property> <name>javax.jdo.option.ConnectionPassword</name> <value>123456</value><!—mysql密码--> <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>

5.        修改hive-env.sh

# Set HADOOP_HOME to pointto a specific hadoop install directory HADOOP_HOME=/home/hadoop-cdh4.7   #Hive Configuration Directory can be controlled by: exportHIVE_CONF_DIR=/home/hive-0.10.0/conf

 

6.        启动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

 

7.        查询MySQL数据库中的元数据

mysql –u hive –p123456 use hive; show tables;

Hive基本使用

1.        新建表

#创建数据(文本以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: 3.909 seconds

 

#导入数据t_hive.txt到t_hive表

hive> LOAD DATA LOCAL INPATH'/home/test/t_hive.txt' OVERWRITE INTO TABLE t_hive ;   

Copying data from file:/home/test/t_hive.txt

Copying file: file:/home/test/t_hive.txt

Loading data to table default.t_hive

rmr: DEPRECATED: Please use 'rm -r'instead.

Deleted /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]

OK

Time taken: 0.999 seconds

 

2.        查看表和数据

#查看表

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 seconds

 

#查看表结构

hive> desc t_hive;

OK

a      int

b      int

c      int

Time taken: 0.1 seconds

 

3.        修改表

#增加一个字段

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

 

#重命令表名

hive>ALTER TABLE t_hive RENAME TOt_hadoop;

OK

Time taken: 0.45 seconds

hive> show tables;

OK

t_hadoop

Time taken: 0.07 seconds

 

4.        删除表

hive> DROP TABLE t_hadoop;

OK

Time taken: 0.767 seconds

 

hive> show tables;

OK

Time taken: 0.064 seconds