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 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 \-moveDatabasetest\--fromCatalog hive \
—toCatalog spark

-moveTable

移动数据库，需要

--fromCatalog

，

--toCatalog

，

--fromDatabase

,

--toDatabase

参数

schematool -dbType mysql \-moveTable  t1  \--fromCatalog hive \
—toCatalog spark \--fromDatabasetest\--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 incolumn validation for incorrect NULL values.
[SUCCESS]

Done with metastore validation: [SUCCESS]
schemaTool completed