配置hive+mysqlt的详细方法和步骤介绍

Hive配置文件介绍

•hive-site.xml      hive的配置文件

•hive-env.sh        hive的运行环境文件

•hive-default.xml.template  默认模板

•hive-env.sh.template    hive-env.sh默认配置

•hive-exec-log4j.properties.template  exec默认配置

• hive-log4j.properties.template log默认配置

hive-site.xml

< property>

  <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://localhost:3306/hive?createData baseIfNotExist=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>test</value>

  <description>password to use against metastore database</description>

</property>

 

hive-env.sh

•配置Hive的配置文件路径

•export HIVE_CONF_DIR= your path

•配置Hadoop的安装路径

•HADOOP_HOME=your hadoop home

 

 

 

a.配置 hive-conf.sh
在“/home/hadoop/hive-0.9.0/bin”目录下,“hive-conf.sh”,然后在里面添加下面内容。

#set java environment

HIVE_HOME=/home/hadoop/hive-0.9.0

HADOOP_HOME=/home/hadoop/hadoop-1.1.1

JAVA_HOME=/home/hadoop/jdk1.7.0

PATH=$JAVA_HOME/bin:$HIVE_HOME/bin:$HADOOP_HOME/bin:$PATH

CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$HIVE_HOME/lib:$JAVA_HOME/lib/tools.jar

export HADOOP_HOME

export JAVA_HOME

export HIVE_HOME

export PATH

export CLASSPATH

 

 

/home/hadoop/hive-0.9.0/conf/hive-env.sh
在以上三个文件中添加如下内容：

#set java environment

HIVE_HOME=/home/hadoop/hive-0.9.0

HADOOP_HOME=/home/hadoop/hadoop-1.1.1

JAVA_HOME=/home/hadoop/jdk1.7.0

PATH=$JAVA_HOME/bin:$HIVE_HOME/bin:$HADOOP_HOME/bin:$PATH

CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$HIVE_HOME/lib:$JAVA_HOME/lib/tools.jar

export HADOOP_HOME

export JAVA_HOME

export HIVE_HOME

export PATH

export CLASSPATH

 

 

 

1.在mysql专门为hive添加用户

 

mysql> CREATE USER 'hive'@'%' IDENTIFIED BY 'hivepasswd';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'hive'@'%' WITH GRANT OPTION;

2.修改配置文件conf/hive-default.xml 中的配置

 

1.<property>   

2.        <name>hive.metastore.local</name>   

3.        <value>true</value>    4.        <description>controls whether to connect to remove metastore server or open a new metastore server in Hive Client JVM</description>  5.</property> 

6.<property>   

7.        <name>javax.jdo.option.ConnectionURL</name>   

8.        <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>    9.        <description>JDBC connect string for a JDBC metastore</description>  10.</property> 

11.<property>   

12.        <name>javax.jdo.option.ConnectionDriverName</name>   

13.        <value>com.mysql.jdbc.Driver</value>   

14.        <description>Driver class name for a JDBC metastore</description>  15.</property> 

16.<property>   

17.        <name>javax.jdo.option.ConnectionUserName</name>   

18.        <value>hive</value>   

19.        <description>username to use against metastore database</description> 

20.</property> 

21.<property>   

22.        <name>javax.jdo.option.ConnectionPassword</name>   

23.        <value>hivepasswd</value>   

24.        <description>password to use against metastore database</description> 

25.</property> 

 

3.添加jdbc的jar包

wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.11.tar.gz/from/http://mysql.he.net/

tar -xvzf mysql-connector-java-5.1.11.tar.gz

cp mysql-connector-java-5.1.11/*.jar /data/soft/hive/lib

 

4.启动hive

bin/hive

hive> show tables;

 

安装Hive

•解压Hive

•tar zxvf  hive-0.8.1.tar  /home/test/Desktop

•建立软连接

•ln –s hive-0.8.1 hive

•添加环境变量

•export HIVE_HOME=/home/test/Desktop/hive

•export PATH=….HIVE_HOME/bin:$PATH:.

配置Hive

•进入hive/conf目录

•依据hive-env.sh.template，创建hive-env.sh文件

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

•修改hive-env.sh

•指定hive配置文件的路径

•export HIVE_CONF_DIR=/home/test/Desktop/hive/conf

•指定Hadoop路径

• HADOOP_HOME=/home/test/Desktop/hadoop  

hive-site.xml

<property>

  <name>javax.jdo.option.ConnectionURL</name>

<value>jdbc:derby:;databaseName=metastore_db;create=true</value>

  <description>JDBC connect string for a JDBC metastore</description>

</property>

 

<property>

  <name>javax.jdo.option.ConnectionDriverName</name>

  <value>org.apache.derby.jdbc.EmbeddedDriver</value>

  <description>Driver class name for a JDBC metastore</description>

</property>

<property>

  <name>javax.jdo.option.ConnectionUserName</name>

  <value>APP</value>

  <description>username to use against metastore database</description>

</property>

 

<property>

  <name>javax.jdo.option.ConnectionPassword</name>

  <value>mine</value>

  <description>password to use against metastore database</description>

</property>

 

启动Hive

•命令行键入

•Hive

•显示

WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use org.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.

Logging initialized using configuration in jar:file:/home/test/Desktop/hive-0.8.1/lib/hive-common-0.8.1.jar!/hive-log4j.properties

Hive history file=/tmp/test/hive_job_log_test_201208260529_167273830.txt

hive>

测试语句

•建立测试表test

•create table test  (key string);

•show tables;

 

 

 

 --------------------------------------------------------------------------mysql

 

4.配置文件

在/etc目录下，添加my.cnf

安装好mysql后，在/usr/local/mysql/share/mysql目录下，会有my-huge.cnf,  my-medinum.cnf, my-small.cnf

 

1.  [root@slave-003 mysql]# cp /usr/local/mysql/share/mysql/my-huge.cnf /etc/my.cnf 

如果你的内存≤64M，则复制/usr/local/share/mysql/my-small.cnf为/etc/my.cnf

# This is for a system with little memory (<= 64M) where MySQL is only used

# from time to time and it's important that the mysqld daemon

# doesn't use much resources.

 

如果内存是128M，则复制/usr/local/share/mysql/my-medium.cnf为/etc/my.cnf

# This is for a system with little memory (32M - 64M) where MySQL plays

# an important part, or systems up to 128M where MySQL is used together with

# other programs (such as a web server)

 

如果内存是512M，则复制/usr/local/share/mysql/my-large.cnf为/etc/my.cnf

# This is for a large system with memory = 512M where the system runs mainly

# MySQL.

 

如果内存是1-2G，www.linuxidc.com则复制/usr/local/share/mysql/my-huge.cnf为/etc/my.cnf

# This is for a large system with memory of 1G-2G where the system runs mainly

# MySQL.

 

5. 生成mysql用户数据库和表文件

 

1.  /usr/local/mysql/bin/mysql_install_db --user=mysql 

会自动在localstatedir定义的路径生成文件

 

6.添加帐号

 

1.  groupadd mysql 

2.  useradd -g mysql mysql 

7.修改权限

 

1.  chown -R mysql:mysql /data/mysql/data 

2.  chmod 775 /data/mysql/data/ 

8.启动mysql

 

1.  /usr/local/mysql/bin/mysqld_safe & 

9.添加到系统的启动目录

 

1.  cp /usr/local/mysql/share/mysql/mysql.server  /etc/init.d/mysql 

 

1.  /etc/init.d/mysql start 

2.  /etc/init.d/mysql stop 

3.  /etc/init.d/mysql restart 

 

 

10.运行客户端

 

1.  /usr/local/mysql/bin/mysql 

 

 

 

设置密码 

1.  /usr/local/mysql/bin/mysqladmin -uroot password '360buy' 

参看密码是否生效

/usr/local/mysql/bin/mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

进不去了，说明已经生效

 

12.使用密码登录

 

1.  /usr/local/mysql/bin/mysql -uroot -p 

 

输入刚刚新建的密码，就可以进去了

 

13.修改my.cnf

 

1.  [mysqld] 

2.  datadir=/usr/local/mysql/data/ 

3.  socket=/usr/local/mysql/data/mysql.sock 

4.  user=mysql 

5.  old_passwords=1 

6.  default-character-set=latin1 

7.  skip-character-set-client-handshake 

8.  wait_timeout=864000 

9.  interactive-timeout=864000 

10. max_connections=2000 

11. [mysqld_safe] 

12. log-error=/var/log/mysqld.log 

13. pid-file=/var/run/mysqld/mysqld.pid 

14. [client] 

15. socket=/usr/local/mysql/data/mysql.sock 

16. #default-character-set=utf8 

 

 

14.登录客户端查看

mysql> SHOW VARIABLES LIKE '%character%';

+--------------------------+----------------------------------------+

| Variable_name            | Value                                  |

+--------------------------+----------------------------------------+

| character_set_client    | latin1                                |

| character_set_connection | latin1                                |

| character_set_database  | latin1                                |

| character_set_filesystem | binary                                |

| character_set_results    | latin1                                |

| character_set_server    | latin1                                |

| character_set_system    | utf8                                  |

| character_sets_dir      | /usr/local/mysql/share/mysql/charsets/ |

+--------------------------+----------------------------------------+

8 rows in set (0.00 sec)

除了system为utf8，其他最好为latin1，否则可能出现异常com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes ）

 

数据库引擎

mysql> show engines; （最好有InnoDB模式支持）

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine    | Support | Comment                                                        | Transactions | XA  | Savepoints |

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

| ndbcluster | NO      | Clustered, fault-tolerant tables                              | NULL        | NULL | NULL      |

| MRG_MYISAM | YES    | Collection of identical MyISAM tables                          | NO          | NO  | NO        |

| BLACKHOLE  | YES    | /dev/null storage engine (anything you write to it disappears) | NO          | NO  | NO        |

| CSV       | YES    | CSV storage engine                                            | NO          | NO  | NO        |

| MEMORY    | YES    | Hash based, stored in memory, useful for temporary tables      | NO          | NO  | NO        |

| FEDERATED  | NO      | Federated MySQL storage engine                                | NULL        | NULL | NULL      |

| ARCHIVE    | YES    | Archive storage engine                                        | NO          | NO  | NO        |

| InnoDB    | YES     | Supports transactions, row-level locking, and foreign keys    | YES          | YES  | YES        |

| MyISAM    | DEFAULT | Default engine as of MySQL 3.23 with great performance        | NO          | NO  | NO        |

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

 

15.远程访问mysql

 

1.  mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root"; 

2.  mysql> flush privileges; 

第一句中"%"表示任何主机都可以远程登录到该服务器上访问。如果要限制只有某台机器可以访问，将其换成相应的IP即可，如：

GRANT ALL PRIVILEGES ON *.* TO root@"192.168.105.83" IDENTIFIED BY "root";

第二句表示从mysql数据库的grant表中重新加载权限数据。www.linuxidc.com 因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。

 

16.修改数据库的binlog

 

1.  set global binlog_format='MIXED'; 

READ-COMMITTED需要把bin-log以mixed方式来记录

否则进入hive，会如下错误

FAILED: Error in metadata: javax.jdo.JDOException: Couldnt obtain a new sequence (unique id) : Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'