2021 10 14

配置远程mysql

1）remote一体（未亲测）

这种存储方式需要在远端服务器运行一个mysql服务器，并且需要在Hive服务器启动meta服务。这里用mysql的测试服务器，

ip位192.168.13.138，新建hive_remote数据库，字符集位latine1

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://192.168.13.135:3306/hive?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>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>1234</value>
</property>
<property>
<name>hive.metastore.local</name>
<value>false</value>
</property>
<property>
<name>hive.metastore.uris</name>
<value>thrift://192.168.13.138:9083</value>
</property>
</configuration>
注：这里把hive的服务端和客户端都放在同一台服务器上了。服务端和客户端可拆开。
2）remote分开
1.如上，在node8（任意另一节点）上成功安装hive
2.修改node8中的hive-site.xml（服务端配置文件）
vi /opt/sxt/soft/apache-hive-1.2.1-bin/conf/hive-site.xml，内容为

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://192.168.13.135:3306/hive?createDatabaseIfNotExist=true</value>
//192.168.13.135:3306为安装mysql的服务器，不一定是135
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>1234</value>
</property>
</configuration>
3.启动hive服务端程序（node8中）
hive --service metastore
4.修改node5中的hive-site.xml（客户端配置文件）
vi /opt/sxt/soft/apache-hive-1.2.1-bin/conf/hive-site.xml，内容为
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<name>hive.metastore.local</name>
<value>false</value>
</property>
<property>
<name>hive.metastore.uris</name>
<value>thrift://192.168.13.138:9083</value>
</property>
</configuration>
5.客户端直接使用hive命令即可（node5中）
hive
hive> show tables;
OK
Time taken: 0.707 seconds
hive>
6.启动hiveserver库
$HIVE_HOME/bin/hiveserver2或者$HIVE_HOME/bin/hive --service hiveserver2
7.连接hiveserver库
1）beeline方式
beeline
beeline>!connect jdbc:hive2://localhost:10000 root org.apache.hive.jdbc.HiveDriver或
beeline>!connect jdbc:hive2://localhost:10000/default
2）客户端连接（JDBC）
public class TestHive2 {
public static void main(String[] args) {
try {
Class.forName("org.apache.hive.jdbc.HiveDriver");
Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.13.135:10000/default","root","");
Statement stmt = conn.createStatement();
ResultSet resultSet = stmt.executeQuery("select count(*) from people");
if(resultSet.next()){
System.out.println(resultSet.getInt(1));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}