Table of Contents
1 Introduction
2. Configure metadata to MySQL
2.1. Create a new metadata database
2.2. Copy the MySQL JDBC driver to the Hive lib directory (the MySQL8.0.33 driver is used here)
2.3. Create the hive-site.xml file under $HIVE_HOME/conf
2.4. Initialize the hive metadata database and use MySQL for storage instead.
3. Verify whether the metadata is configured successfully
3.1. Start hive
3.2. Using hive
3.3. Open hive in another window. You can see that hive can be operated in both windows without exception.
4. View metadata in MySQL
4.1. Log in to Mysql
4.1. View the metastore metastore
4.2. View the library information stored in metadata. There is a default database in hive by default.
4.3. View the column-related information stored in the metadata and save the information about the fields in the created table.
1. Introduction
Write Hive metadata into the MySQL metastore database (MySQL does not have a metastore database by default and needs to be created in advance: create database metastore;)
2. Configure metadata to MySQL
2.1. Create a new metadatabase
#Log in to MySQL mysql -u root -p123456 #Create metadatabase mysql>create database metastore; mysql>quit;
2.2. Copy the MySQL JDBC driver to the Hive lib directory ( The driver used here is MySQL8.0.33)
cp /opt/software/mysql-connector-j-8.0.33.jar /opt/module/hive/lib
2.3. Create hive-site.xml file under $HIVE_HOME/conf
<?xml version="1.0"?> <?xml-stylesheettype type="text/xsl" href="configuration.xsl"?> <configuration> <!-- URL of jdbc connection--> <!--Host name: port number/database name --> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://hadoop104:3306/metastore?useSSL=false</value> </property> <!-- Driver for jdbc connection--> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.cj.jdbc.Driver</value> </property> <!--username of jdbc connection--> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> </property> <!-- Password for jdbc connection --> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>123456</value> </property> <!-- Hive is in the HDFS working directory by default --> <property> <name>hive.metastore.warehouse.dir</name> <value>/user/hive/warehouse</value> </property> </configuration>
2.4. Initialize hive metadatabase and change to use MySQL for storage
bin/schematool -dbType mysql -initSchema -verbose
3. Verify whether the metadata is configured successfully
3.1, start hive
bin/hive
3.2, use hive
hive> show databases; hive> show tables; hive> create table stu(id int, name string); hive> insert into stu values(1,"liao"); hive> select * from stu;
3.3. Open hive in another window, you can see that both windows can operate hive , no exception
hive>show databases; hive>show tables; hive>select * from stu;
4. View metadata in MySQL
4.1, log in to Mysql
mysql -u root -p123456
4.1. View metastore metastore
mysql> show databases; mysql> use metastore; mysql> show tables;
4.2. View the library information stored in the metadata. There is a default database in hive by default
mysql>select * from DBS;
View the table information stored in the metadata. TBLS stores the information of all tables created in hive. The root directory of the created tables and other information is stored in the SDS table:
mysql>select * from TBLS;
4.3. View the column-related information stored in the metadata and save the information about the fields used to create the table
mysql>select * from COLUMNS_V2;