schematool

See Hive - Schematool

Import / Export

• https://hdinsight.github.io/hive/hive-export-import-metastore.html

Query

The metadata are stored in a apart database

use metastore; 
show tables; 
-- tables
select * from TBLS;

ER diagram

HiveMetaStore

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

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>