配置hive+mysqlt的详细方法和步骤介绍，首先配置hive+mysqlt配置文件：Hive配置文件介绍•hive-site.xml hive的配置文件•hive-env.sh hive的运行环境文件•hive-default.xml.template 默认模板。
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'
声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。
