Hive - Metastore

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.

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

4.1 - Derby

Apache Derby (JavaDB)


<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

<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

<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>

5 - Documentation / Reference


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap