Support Questions
Find answers, ask questions, and share your expertise

Hive Metastore SchemaTool

New Contributor

I'm trying to set up my Hive 2.1.0

When I try to run the Hive CLI i get an error saying I do not have the schema initlized so I went to use the schema tool

and set up all the hive-site.xml settings but I keep getting this error I do not understand.


[root@sandbox apache-hive-2.1.0-bin]# schematool -dbType mysql -initSchema WARNING: Use "yarn jar" to launch YARN applications. SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/root/apache-hive-2.1.0-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See for an explanation. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory] Metastore connection URL: jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true Metastore Connection Driver : com.mysql.jdbc.Driver Metastore connection User: root Starting metastore schema initialization to 2.1.0 Initialization script hive-schema-2.1.0.mysql.sql Error: Specified key was too long; max key length is 1000 bytes (state=42000,code=1071) org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! Underlying cause: : Schema script failed, errorcode 2 Use --verbose for detailed stacktrace. *** schemaTool failed ***


@Thuan Pham Can you check if mysql service is up and running ?

I hope metastore already existed, but not in complete form.

Therefore follow below steps and modify database like MySql as per your requirement:

  1. Before you run hive for the first time, run

    schematool -initSchema -dbType derby

  2. If you already ran hive and then tried to initSchema and it's failing:

    mv metastore_db metastore_db.tmp

  3. Re run

    schematool -initSchema -dbType derby

  4. Run hive again

Also if you change directories, the metastore_db created above won't be found.

Cloudera Employee

I would not reccomend using derby for any production workload.

Cloudera Employee

Running the command with "-verbose" will give more information.

Looks like the error is "Specified key was too long; max key length is 1000 bytes" . It seems like you might be using myisam instead of innodb as the engine for some table.

You might want to see if suggestions to change type to innodb as mentioned here works for you -

The tables do explicitly specify innodb engine type, so not sure why you are seeing this issue. Are you on a very old version of mysql ? See supported versions here -