Hive schematool 是 hive 自带的管理 schema 的相关工具。
列出详细说明
schematool -help
直接输入 schematool 或者schematool -help 输出结果如下：
usage: schemaTool
-alterCatalog <arg> Alter a catalog, requires
--catalogLocation and/or
--catalogDescription parameter as well
-catalogDescription <arg> Description of new catalog
-catalogLocation <arg> Location of new catalog, required when
adding a catalog
-createCatalog <arg> Create a catalog, requires
--catalogLocation parameter as well
-dbOpts <databaseOpts> Backend DB specific options
-dbType <databaseType> Metastore database type
-driver <driver> driver name for connection
-dryRun list SQL scripts (no execute)
-fromCatalog <arg> Catalog a moving database or table is
coming from. This is required if you
are moving a database or table.
-fromDatabase <arg> Database a moving table is coming
from. This is required if you are
moving a table.
-help print this message
-ifNotExists If passed then it is not an error to
create an existing catalog
-info Show config and schema details
-initSchema Schema initialization
-initSchemaTo <initTo> Schema initialization to a version
-metaDbType <metaDatabaseType> Used only if upgrading the system
catalog for hive
-moveDatabase <arg> Move a database between catalogs.
Argument is the database name.
Requires --fromCatalog and --toCatalog
parameters as well
-moveTable <arg> Move a table to a different database.
Argument is the table name. Requires
--fromCatalog, --toCatalog,
--fromDatabase, and --toDatabase
parameters as well.
-passWord <password> Override config file password
-servers <serverList> a comma-separated list of servers used
in location validation in the format
of scheme://authority (e.g.
hdfs://localhost:8000)
-toCatalog <arg> Catalog a moving database or table is
going to. This is required if you are
moving a database or table.
-toDatabase <arg> Database a moving table is going to.
This is required if you are moving a
table.
-upgradeSchema Schema upgrade
-upgradeSchemaFrom <upgradeFrom> Schema upgrade from a version
-url <url> connection url to the database
-userName <user> Override config file user name
-validate Validate the database
-verbose only print SQL statements
命令选项
-dbType
数据库类型，只支持以下几种。
derby|mysql|postgres|oracle|mssql
如列出当前 schema 的信息
schematool -dbType mysql -info
结果如下：
Metastore connection URL: jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&characterEncoding=UTF-8
Metastore Connection Driver : com.mysql.jdbc.Driver
Metastore connection User: hive
Hive distribution version: 3.1.0
Metastore schema version: 3.1.0
schemaTool completed
-driver
连接后台数据库的驱动类名称。
schematool -dbType mysql -driver com.mysql.jdbc.Driver -info
-dryRun
仅仅列出要执行的SQL，不执行。
-passWord
覆盖 hive 配置中的 javax.jdo.option.ConnectionPassword参数。
-url
覆盖 hive 配置文件中的 javax.jdo.option.ConnectionURL 参数
schematool -dbType mysql -url 'jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&characterEncoding=UTF-8' -info
-userName
覆盖 hive 配置文件中的 javax.jdo.option.ConnectionUserName 参数
schematool -dbType mysql -userName hive -info
支持的命令
-info
列出当前元数据库信息
schematool -dbType mysql -info
-help
列出所有支持的命令和选项
-initSchema
初始化 schema，如以下命令会在 mysql 数据库里创建 schema 相关的表和插入记录。mysql 的参数在hive-site.xml 或者 hivemetastore-site.xml 配置文件里。注意，连接的数据库要先建立，并且为空（没有任何表）。
schematool -dbType mysql -initSchema
-createCatalog
创建 catalog, 需要 --catalogLocation 参数。
可选参数 --catalogDescription desc-ifNotExists ，仅不存在。
schematool -dbType mysql -createCatalog spark1 --catalogLocation '/user/spark/warehouse1' -catalogDescription 'spark description' -ifNotExists
Create catalog spark at location /user/spark/warehouse
Metastore connection URL: jdbc:mysql://master-e812d44/hive?createDatabaseIfNotExist=true&characterEncoding=UTF-8
Metastore Connection Driver : com.mysql.jdbc.Driver
Metastore connection User: hive
schemaTool completed
-alterCatalog
可选参数 --catalogLocation <location> 参数。
可选参数 --catalogDescription <description>
schematool -dbType mysql -alterCatalog spark1 --catalogLocation '/user/spark/warehouse' -catalogDescription 'spark description'
-initSchemaTo
初始化 schema 到指定的版本，如 2.3.0。
-metaDbType
仅仅在 -dbType hive 中使用，用于升级 hive 中的 system catalog。
-upgradeSchema
升级 schema。如之前 schema 是1.2.0，当前 hive为 3.1.0。
schematool -dbType mysql -upgradeSchema
-upgradeSchemaFrom
从指定的版本升级 schema.
-moveDatabase
移动数据库，需要 --fromCatalog和 --toCatalog 参数
schematool -dbType mysql \
-moveDatabase test \
--fromCatalog hive \
—toCatalog spark
-moveTable
移动数据库，需要 --fromCatalog， --toCatalog ，--fromDatabase,--toDatabase参数
schematool -dbType mysql \
-moveTable t1 \
--fromCatalog hive \
—toCatalog spark \
--fromDatabase test \
--toDatabase dbw
-validate
验证当前元数据库是否正常。
schematool -dbType mysql -validate
输出结果：
Starting metastore validation
Validating schema version
Succeeded in schema version validation.
[SUCCESS]
Validating sequence number for SEQUENCE_TABLE
Succeeded in sequence number validation for SEQUENCE_TABLE.
[SUCCESS]
Validating metastore schema tables
Succeeded in schema table validation.
[SUCCESS]
Validating DFS locations
Succeeded in DFS location validation.
[SUCCESS]
Validating columns for incorrect NULL values.
Succeeded in column validation for incorrect NULL values.
[SUCCESS]
Done with metastore validation: [SUCCESS]
schemaTool completed