Hive-JDBC operation

One: Start Hadoop

1. Core-site.xml configure proxy user properties

Special attention: hadoop.proxyuser.<server user name>.hosts and hadoop.proxyuser.<server user name>.groups these two attributes, the server user name is the login name of the machine where hadoop is located, according to your actual login name To configure. Here my computer user name is mengday.

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
 <property>
  <name>hadoop.tmp.dir</name>
  <value>file:/usr/local/Cellar/hadoop/3.2.1/libexec/tmp</value>
 </property>
 <property>
     <name>fs.defaultFS</name>
     <value>hdfs://localhost:8020</value>
  </property>
  <property>
      <name>hadoop.proxyuser.mengday.hosts</name>
      <value>*</value>
  </property>
  <property>
      <name>hadoop.proxyuser.mengday.groups</name>
      <value>*</value>
  </property>    
</configuration>
2. Start hadoop
> cd /usr/local/Cellar/hadoop/3.2.1/sbin
> ./start-all.sh
> jps

After the startup is successful, pay attention to check whether the DataNode node is started, and often encounter unsuccessful startup of the DataNode node.

Hive JDBC operation

Two: Configure hive-site.xml

Java connects to Hive through beeline. The most important thing to start beeline is to configure hive-site.xml.

Among them, javax.jdo.option.ConnectionURL involves a database. It is best to delete the original metastore database and recreate one and initialize it.

mysql> create database metastore;
> cd /usr/local/Cellar/hive/3.1.2/libexec/bin
> schematool -initSchema -dbType mysql

hive-site.xml

<configuration>
  <property>
        <name>hive.metastore.local</name>
        <value>true</value>
    </property>
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://localhost:9083</value>
        <description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description>
      </property>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://localhost:3306/metastore?characterEncoding=UTF-8&createDatabaseIfNotExist=true</value>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.cj.jdbc.Driver</value>
    </property>
  <!--mysql用户名-->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>
  <!--mysql密码-->
  <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>root123</value>
    </property>

 <!-- hive用来存储不同阶段的map/reduce的执行计划的目录,同时也存储中间输出结果
 ,默认是/tmp/<user.name>/hive,我们实际一般会按组区分,然后组内自建一个tmp目录存>储 -->

    <property>
        <name>hive.exec.local.scratchdir</name>
        <value>/tmp/hive</value>
    </property>

    <property>
        <name>hive.downloaded.resources.dir</name>
            <value>/tmp/hive</value>
    </property>

    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/data/hive/warehouse</value>
    </property>

    <property>
        <name>hive.metastore.event.db.notification.api.auth</name>
        <value>false</value>
    </property>
    <property>
        <name>hive.server2.active.passive.ha.enable</name>
        <value>true</value>
    </property>
    <property>
        <name>hive.server2.transport.mode</name>
        <value>binary</value>
        <description>
          Expects one of [binary, http].
          Transport mode of HiveServer2.
        </description>
    </property>
    <property>
        <name>hive.server2.logging.operation.log.location</name>
        <value>/tmp/hive</value>
    </property>
    <property>
        <name>hive.hwi.listen.host</name>
        <value>0.0.0.0</value>
        <description>This is the host address the Hive Web Interface will listen on</description>
    </property>

    <property>
        <name>hive.server2.webui.host</name>
        <value>0.0.0.0</value>
        <description>The host address the HiveServer2 WebUI will listen on</description>
    </property>

</configuration>

Three: start metastore

You need to start hiveserver2 before starting beeline, and you need to start metastore before starting hiveserver2. The default port of metastore is 9083.

> cd /usr/local/Cellar/hive/3.1.2/bin
> hive --service metastore &
Hive JDBC operation

After starting, make sure to confirm whether the starting is successful.

Hive JDBC operation

Four: start hiveserver2

> cd /usr/local/Cellar/hive/3.1.2/bin
> hive --service hiveserver2 &
Hive JDBC operation

The default port of hiveserver2 is 10000. After starting, you must check whether the 10000 port exists. If there is a configuration problem, the 10000 port is basically unsuccessful. The existence or nonexistence of port 10000 is the key to start beeline.

Hive JDBC operation

Five: start beeline

> cd /usr/local/Cellar/hive/3.1.2/bin
> beeline -u jdbc:hive2://localhost:10000/default -n mengday -p
  • -u: URL of the connection, jdbc:hive2://<hostname or IP>:<port default>/<database name>, the default port number is 10000, which can be passed through ```hiveserver2 --hiveconf hive.server2.thrift.port =14000 modify the port number, default is its own database
  • -n: The login account name of the server where hive is located, here is the login user name mengday of my Mac machine, the name here must be the same as hadoop.proxyuser.mengday.hosts and hadoop.proxyuser.mengday in core-site.xml The mengday in .groups is consistent.
  • -p: password, the password corresponding to the user name

Seeing 0: jdbc:hive2://localhost:10000/default> means that the startup is successful.

Hive JDBC operation

六:Hive JDBC

1. Introduce dependencies
<dependency>
   <groupId>org.apache.hive</groupId>
   <artifactId>hive-jdbc</artifactId>
   <version>3.1.2</version>
</dependency>
2. Prepare the data

/data/employee.txt

1,zhangsan,28,60.66,2020-02-01 10:00:00,true,eat#drink,k1:v1#k2:20,s1#c1#s1#1
2,lisi,29,60.66,2020-02-01 11:00:00,false,play#drink,k3:v3#k4:30,s2#c2#s1#2
3. Java
import java.sql.*;

public class HiveJdbcClient {
    private static String url = "jdbc:hive2://localhost:10000/default";
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    private static String user = "mengday";
    private static String password = "user对应的密码";

    private static Connection conn = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;

    static {
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void init() throws Exception {
        stmt.execute("drop database if exists hive_test");
        stmt.execute("create database hive_test");
        rs = stmt.executeQuery("show databases");
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }

        stmt.execute("drop table if exists employee");
        String sql = "create table if not exists employee(" +
                " id bigint, " +
                " username string, " +
                " age tinyint, " +
                " weight decimal(10, 2), " +
                " create_time timestamp, " +
                " is_test boolean, " +
                " tags array<string>, " +
                " ext map<string, string>, " +
                " address struct<street:String, city:string, state:string, zip:int> " +
                " ) " +
                " row format delimited " +
                " fields terminated by ',' " +
                " collection items terminated by '#' " +
                " map keys terminated by ':' " +
                " lines terminated by '\n'";
        stmt.execute(sql);

        rs = stmt.executeQuery("show tables");
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }

        rs = stmt.executeQuery("desc employee");
        while (rs.next()) {
            System.out.println(rs.getString(1) + "\t" + rs.getString(2));
        }
    }

    private static void load() throws Exception {
        // 加载数据
        String filePath = "/data/employee.txt";
        stmt.execute("load data local inpath '" + filePath + "' overwrite into table employee");

        // 查询数据
        rs = stmt.executeQuery("select * from employee");
        while (rs.next()) {
            System.out.println(rs.getLong("id") + "\t"
                    + rs.getString("username") + "\t"
                    + rs.getObject("tags") + "\t"
                    + rs.getObject("ext") + "\t"
                    + rs.getObject("address")
            );
        }
    }

    private static void close() throws Exception {
        if ( rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }

    public static void main(String[] args) throws Exception {
        init();

        load();

        close();
    }
}


(stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}

public static void main(String[] args) throws Exception {
    init();

    load();

    close();
}

}


[外链图片转存中...(img-RM37EYNt-1622706179032)]