hive 安装及使用

hive解压安装

hive配置

cp hive-default.xml.template hive-site.xmlcp 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.EventCounterlog4j.appender.EventCounter=org.apache.hadoop.log.metrics.EventCounter

配置环境变量

vi ~/.bahsrcexport HIVE_HOME=/usr/local/hiveexport 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端口已经退出后台运行metastorenohup 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/libmysql-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 shellLogging initialized using configuration in file:/root/hive-0.9.0/conf/hive-log4j.propertiesHive history file=/tmp/root/hive_job_log_root_201211141845_1864939641.txthive> show tablesOK

查询MySQL数据库中的元数据

~ mysql -uroot -pmysql> use hive_metadata;Database changedmysql> 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.txt16      2       361      12      1341      2       3117      21      371      2       311       12      3411      2       34#创建新表hive> CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';OKTime 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.txtCopying file: file:/home/cos/demo/t_hive.txtLoading data to table default.t_hiveDeleted hdfs://c1.wtmart.com:9000/user/hive/warehouse/t_hiveOKTime 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_hive2hive> 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_hive2Deleted hdfs://moon:9000/user/hive/warehouse/t_hive2OKTime 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;OKTime taken: 0.077 seconds

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

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/t_hive' SELECT * FROM t_hive;

参考hive安装及使用攻略