安装mysql(mariadb)
yum安装
yum install mariadb-server
yum install -y mysql-connector-java
设置开机启动并配置
systemctl start mariadb # 开启服务
systemctl enable mariadb # 设置为开机自启动服务
mysql_secure_installation
mysql相关配置文件修改
vim /etc/my.cnf
在 [mysqld] 标签下添加
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
vim /etc/my.cnf.d/client.cnf
在 [client] 标签下添加
default-character-set=utf8
vim /etc/my.cnf.d/mysql-clients.cnf
在 [mysql] 标签下添加
default-character-set=utf8
重启数据库
systemctl restart mariadb
设置远程连接
进入cli环境
mysql -uroot -pxiangyang
更新远程连接权限
show variables like "%character%";show variables like "%collation%";
use mysql;
update user set host='%' where host='localhost';
select host, user from user;
flush privileges;
重启数据库使修改生效
systemctl restart mariadb
把jdbc驱动器jar包复制到hive库
cp /usr/share/java/mysql-connector-java.jar /usr/local/hive/lib
元数据数据库应该是latin字符集,不然后面hive管理会报错
CREATE DATABASE `hive_metadata` CHARACTER SET 'latin1' COLLATE 'latin1_general_ci';
hive配置文件
vim hive-site.xml
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://spark1:3306/hive_metadata?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive</value>
</property>
cp hive-env.sh.template hive-env.sh
vim /usr/local/hive/bin/hive-env.sh
修改如下,为你的相应组件的环境变量
export JAVA_HOME=/usr/local/java
export HIVE_HOME=/usr/local/hive
export HADOOP_HOME=/usr/local/hadoop
高版本的hive需要手动指定元数据数据库
schematool -dbType mysql -initSchema -verbose
测试安装
hive进入
create table users(id int, name string);
load data local inpath '~/users.txt' into table users;
select name from users;
更新metadata的mysql表的字符集 支持中文
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
========================================================