安装Hive(独立模式 使用mysql连接)

安装Hive(独立模式 使用mysql连接)

 

1.默认安装了java+hadoop

2.下载对应hadoop版本的安装包

3.解压安装包

    tar zxvf apache-hive-1.2.1-bin.tar.gz

4.安装mysql

    yum -y install mysql-server mysql mysqldev  //需要以root身份运行   另外可能需要配置yum源

    mysql常用命令:

        service mysqld start/stop

        chkconfig mysqld on //加入开机启动 以系统root用户操作

5.授权mysql(以系统hadoop身份 数据库root身份进行操作)

    mysqladmin -u root password "root" //修改root密码为root

    mysql -uroot -p密码  (初始密码为空)

     

    create user 'hive' identified by 'hive'; //创建用于连接的hive用户 密码为hive

    grant all privileges on *.* to 'hive'@'%' identified by "hive" with grant option;

    flush privileges; //刷新权限

     

    grant all privileges on *.* to 'hive'@'localhost' identified by "hive" with grant option;

    flush privileges; //刷新权限

     

    grant all privileges on *.* to 'hive'@'hadoop.master' identified by "hive" with grant option;

    flush privileges; //刷新权限

     

    set global binlog_format='MIXED'; //设置格式  必须执行。不然报错

     

    exit;

    service mysqld restart   //重启服务

6.测试连接

    mysql -hhadoop.master -uhive -phive  //能进去则表示设置成功

    create database hive; //创建连接数据库hive

    alter database hive character set latin1;

     

7.配置环境变量(/etc/profile)

    #hive

    export HIVE_HOME=/opt/hive-1.2.1

    export HIVE_AUX_JARS_PATH=/opt/hive-1.2.1/lib

    export HIVE_CONF_DIR=/opt/hive-1.2.1/conf

    export PATH=$PATH:$HIVE_HOME/bin

    export CLASSPATH=$CLASSPATH:$HIVE_HOME/lib

    保存退出

    source /etc/profile

8.修改配置文件

    1.根据模版复制配置文件

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

        cp hive-env.sh.template hive-env.sh

        cp hive-log4j.properties.template hive-log4j.properties

    2.修改配置文件

        ####hive-site.xml####

        //添加项目   --0.11 版本以后可不用添加此项

        

        hive.metastore.local

        false

        

         

        //修改项目

        

        javax.jdo.option.ConnectionURL

        jdbc:mysql://hadoop.master:3306/hive

        JDBC connect string for a JDBC metastore

        

         

        

        javax.jdo.option.ConnectionDriverName

        com.mysql.jdbc.Driver

        Driver class name for a JDBC metastore

        

         

        

        javax.jdo.option.ConnectionUserName

        hive

        Username to use against metastore database

        

         

        

        javax.jdo.option.ConnectionPassword

        hive

        password to use against metastore database

        

         

        

        hive.exec.local.scratchdir

        /opt/hive-1.2.1/tmp        //需要创建此目录

        Local scratch space for Hive jobs

        

         

        

        hive.downloaded.resources.dir

        /opt/hive-1.2.1/tmp

        Temporary local directory for added resources in the remote file system.

        

         

         

        

        hive.hwi.war.file

        /opt/hive-1.2.1/lib/hive-hwi-1.2.1.jar

        This sets the path to the HWI war file, relative to ${HIVE_HOME}.

        

         

        ####hive-env.sh####

        HADOOP_HOME=/opt/hadoop-2.5.2

         

        ####hive-log4j.properties####

        hive.log.threshold=ALL

        hive.root.logger=INFO,DRFA

        hive.log.dir=/opt/hive-1.2.1/logs  //需创建相应目录

        hive.log.file=hive.log

9.其他配置项

    1.hdfs上创建相应文件夹并修改权限

        hadoop fs -mkdir -p /tmp/hive

        hadoop fs -chmod 777 /tmp/hive

        hadoop fs -mkdir -p /user/hive

        hadoop fs -chmod 777 /user/hive

    2.修改hadoop hadoop-env.sh配置文件

        export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CLASSPATH

    3.将mysql jdbc jar包拷贝到lib目录下

        cp mysql-connector-java-5.1.21.jar /opt/hive-1.2.1/lib

    4.将lib目录下的jline-2.12.jar 拷贝到/opt/hadoop-2.5.2/share/hadoop/yarn/lib下,并将相应包重属名

        cp /opt/hive-1.2.1/lib/jline-2.12.jar /opt/hadoop-2.5.2/share/hadoop/yarn/lib

        mv /opt/hadoop-2.5.2/share/hadoop/yarn/lib/jline-0.9.94.jar /opt/hadoop-2.5.2/share/hadoop/yarn/lib/jline-0.9.94.jar.bak

9.使用验证

    hive --service metastore & //开机首次执行

    hive -e "show databases;" //运行完不报错即安装成功

    也可以使用hive命令行进去执行

10.常用命令

    1.显示

        show tables;

        show databases;

    2.定义

      //添加外部分区表  推荐以后就是用这种表

        create external table access_info(ip string,access_date string,url string)

        partitioned by(logdate string) row format delimited fields terminated by '\t'

         

        desc access_info;

    3.添加数据

        alter table access_info add partition(logdate='2016-01-15') location '/access'; --加载文件hdfs实际路径  access为文件夹名称

        load data local inpath '/home/hadoop/huangzhijian/access.txt' into table access_info_local_file;  --加载本地文件

    3.查询

        select * from access_info;

    4.删除

        drop table access_info;  //外部表不会删除原本数据  内部表就会删除原数据

     

    //注意不能update

     

    5.其他

        1.hive –f test.sql

          ####test.sql####

          select * from t1;

          select count(*) from t1;

        2.hive -e 'hql语句'

        3.hive -S -e 'select * from t1'(用法与第一种方式的静音模式一样，不会显示mapreduce的操作过程)

        4.hive -e 'select * from t1'  > test.txt  （将结果输出到本地文件中）