Hive SchemaTool 命令详解

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 whenadding 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 iscoming from.  This is required if youare moving a database or table.-fromDatabase <arg>                Database a moving table is comingfrom.  This is required if you aremoving a table.-help                              print this message-ifNotExists                       If passed then it is not an error tocreate 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 systemcatalog for hive-moveDatabase <arg>                Move a database between catalogs.Argument is the database name.Requires --fromCatalog and --toCatalogparameters as well-moveTable <arg>                   Move a table to a different database.Argument is the table name. Requires--fromCatalog, --toCatalog,--fromDatabase, and --toDatabaseparameters as well.-passWord <password>               Override config file password-servers <serverList>              a comma-separated list of servers usedin location validation in the formatof scheme://authority (e.g.hdfs://localhost:8000)-toCatalog <arg>                   Catalog a moving database or table isgoing to.  This is required if you aremoving a database or table.-toDatabase <arg>                  Database a moving table is going to.This is required if you are moving atable.-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 validationValidating 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