About
All the metadata for Hive tables and partitions are accessed through the Hive Metastore.
Derby is the default database for the Hive metastore.
Articles Related
Management
schematool
Import / Export
Query
The metadata are stored in a apart database
use metastore;
show tables;
-- tables
select * from TBLS;
ER diagram
Database
Metadata is persisted using JPOX ORM solution (Data Nucleus) so any database that is supported by it can be used by Hive.
Derby
<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
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>
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>