Hive - Metastore

> Database > Apache - Hive (HS|Hive Server)

1 - About

All the metadata for Hive tables and partitions are accessed through the Hive Metastore.

Derby is the default database for the Hive metastore.

Advertising

3 - Management

3.1 - schematool

3.2 - Import / Export

3.3 - Query

The metadata are stored in a apart database

USE metastore; 
SHOW TABLES; 
-- tables
SELECT * FROM TBLS;

3.4 - ER diagram

4 - Database

Metadata is persisted using JPOX ORM solution (Data Nucleus) so any database that is supported by it can be used by Hive.

Supported database

Advertising

4.1 - Derby

Apache Derby (Java DB)

hive-site.xml
<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:derby://{file_location}/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>
  <!-- See also: org.apache.derby.jdbc.ClientDriver -->
  <description>Driver class name for a JDBC metastore</description>
</property>
 
<property>
  <name>hive.metastore.warehouse.dir</name>
  <!-- in local mode -->
  <value>file://${user.dir}/../build/ql/test/data/warehouse</value>
  <description>Data goes in here on your local filesystem.</description>
</property>

where: Hive - Warehouse

Derby is generally used as an local metastore but it can be use as a remote one. See HiveDerbyServerMode

4.2 - MySQL

  • Create a hive user
CREATE DATABASE hive;
CREATE USER 'hiveuser'@'%' IDENTIFIED BY 'hivepass';
GRANT ALL ON *.* TO 'hiveuser'@localhost IDENTIFIED BY 'hivepass';
FLUSH privileges;
  • hive-site.xml
hive-site.xml
<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://{mysql_host}:${mysql_port}/{metastore_db}?createDatabaseIfNotExist=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>{username}</value>
  <description>username to use against metastore database</description>
</property>
 
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>{password}</value>
  <description>password to use against metastore database</description>
</property>

4.3 - SQL Server

  • Create a hive user
CREATE DATABASE Hive;
 
CREATE LOGIN metastore WITH
		PASSWORD = 'pwd',
		DEFAULT_DATABASE = Hive;
 
 
USE Hive;
DROP USER metastore;
DROP schema metastore;
CREATE USER metastore FOR LOGIN metastore;
CREATE SCHEMA metastore AUTHORIZATION metastore;
GRANT CONNECT TO metastore;
GRANT CREATE TABLE TO metastore;
GRANT CREATE VIEW TO metastore;
ALTER USER metastore WITH DEFAULT_SCHEMA = metastore;
  • hive-site.xml
hive-site.xml
<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:sqlserver://{host}[:{port|1433}][;databaseName={database}]</value>
  <description>JDBC connect string for a JDBC metastore</description>
</property>
 
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value>
  <description>Driver class name for a JDBC metastore</description>
</property>
 
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>{username}</value>
  <description>username to use against metastore database</description>
</property>
 
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>{password}</value>
  <description>password to use against metastore database</description>
</property>
Advertising

5 - Documentation / Reference

db/hive/metastore.txt · Last modified: 2018/07/18 16:03 by gerardnico