Ambari Hive cannot be properly queried

Ambari Hive cannot be properly queried

1. An error occurred today. After connecting to hive, no command can be executed.

$ Beeline-u "jdbc: hive2: // hostname.domain.cn: 10000/default; principal = hive/_ HOST@domain.com ;"
Connecting to jdbc: hive2: // hostname.domain.cn: 10000/default; principal = hive/_ HOST@domain.com;
Connected to: Apache Hive (version release-1.2.1-EDH-1.1.2)
Driver: Hive JDBC (version release-1.2.1-EDH-1.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version release-1.2.1-EDH-1.1.2 by Apache Hive
0: jdbc: hive2: // hostname.domain.cn: 10000> show databases;

The returned error is as follows:

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.Hadoop.hive.ql.exe c. DDLTask. MetaException
(Message: For direct MetaStore DB connections, we don't support retries at the client level.) (state = 08S01, code = 1)

2. Baidu this error is mentioned in many documents as a character set problem. Ensure that the mysql character set is latin1.

Mysql> show variables like '% collation _ % ';
+ ---------------------- + ------------------- +
| Variable_name | Value |
+ ---------------------- + ------------------- +
| Collation_connection | latin1_swedish_ci |
| Collation_database | latin1_swedish_ci |
| Collation_server | latin1_swedish_ci |
+ ---------------------- + ------------------- +

3. If the character set is not latin1, You need to manually execute it (this is not the problem, my character set, originally latin1)

Mysql> use hive
Mysql> alter database hive character set latin1;

4. Then I tried to restart hive. As a result, metastore failed to start. Viewing the log indicates that table creation failed. This hive has been running for a while and has never been a problem. The corresponding tables also exist and cannot be reconstructed.

5. log output is as follows:

Traceback (most recent call last ):
File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 259, in <module>
Hivemetastore(cmd.exe cute ()
File "/usr/lib/python2.6/site-packages/resource_management/libraries/script. py", line 280, in execute
Method (env)
File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 59, in start
Self. configure (env)
File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 73, in configure
Hive (name = 'metastore ')
File "/usr/lib/python2.6/site-packages/ambari_commons/OS _family_impl.py", line 89, in thunk
Return fn (* args, ** kwargs)
File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive. py", line 320, in hive
User = params. hive_user
File "/usr/lib/python2.6/site-packages/resource_management/core/base. py", line 155, in _ init __
Self. env. run ()
File "/usr/lib/python2.6/site-packages/resource_management/core/environment. py", line 160, in run
Self. run_action (resource, action)
File "/usr/lib/python2.6/site-packages/resource_management/core/environment. py", line 124, in run_action
Provider_action ()
File "/usr/lib/python2.6/site-packages/resource_management/core/providers/system. py", line 273, in action_run
Tries = self. resource. tries, try_sleep = self. resource. try_sleep)
File "/usr/lib/python2.6/site-packages/resource_management/core/shell. py", line 71, in inner
Result = function (command, ** kwargs)
File "/usr/lib/python2.6/site-packages/resource_management/core/shell. py", line 93, in checked_call
Tries = tries, try_sleep = try_sleep)
File "/usr/lib/python2.6/site-packages/resource_management/core/shell. py", line 141, in _ call_wrapper
Result = _ call (command, ** kwargs_copy)
File "/usr/lib/python2.6/site-packages/resource_management/core/shell. py", line 294, in _ call
Raise Fail (err_msg)
Resource_management.core.exceptions.Fail: Execution of 'export HIVE_CONF_DIR =/usr/hdp/current/hive-metastore/conf. server;/usr/hdp/current/hive-metastore/bin/schematool-initSchema-dbType mysql-userName hive-passWord [PROTECTED]-verbose 'returned 1. metastore connection URL: jdbc: mysql: // hostname.mysql.cn/hivemeta
Metastore Connection Driver: com. mysql. jdbc. Driver
Metastore connection User: hive
Starting metastore schema initialization to 1.2.0
Initialization script hive-schema-1.2.0.mysql. SQL
Connecting to jdbc: mysql: // hostname.mysql.cn/hivemeta
Connected to: MySQL (version 5.1.73)
Driver: MySQL Connector Java (version mysql-connector-java-5.1.40 (Revision: 402933ef52cad9aa82624e80acbea46e3a701ce6 ))
Transaction isolation: TRANSACTION_READ_COMMITTED
0: jdbc: mysql: // hostname.mysql.cn/hivem>! Autocommit on
Autocommit status: true
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40101 SET @ OLD_CHARACTER_SET_C
LIENT = @ CHARACTER_SET_CLIENT */
No rows affected (0.003 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40101 SET @ OLD_CHARACTER_SET_R
ESULTS = @ CHARACTER_SET_RESULTS */
No rows affected (0 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40101 SET @ OLD_COLLATION_CONNE
CTION = @ COLLATION_CONNECTION */
No rows affected (0 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40101 set names utf8 */
No rows affected (0 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40103 SET @ OLD_TIME_ZONE = @ TIM
E_ZONE */
No rows affected (0.002 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40103 SET TIME_ZONE = '+ '*
/
No rows affected (0 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40014 SET @ OLD_UNIQUE_CHECKS = @
@ UNIQUE_CHECKS, UNIQUE_CHECKS = 0 */
No rows affected (0 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40014 SET @ OLD_FOREIGN_KEY_CHE
CKS =@foreign_key_checks, FOREIGN_KEY_CHECKS = 0 */
No rows affected (0.001 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40101 SET @ OLD_ SQL _MODE =@@ SQL _
MODE, SQL _MODE = 'no _ AUTO_VALUE_ON_ZERO '*/
No rows affected (0 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40111 SET @ OLD_ SQL _NOTES = @ SQL
_ NOTES, SQL _NOTES = 0 */
No rows affected (0.001 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40101 SET @ saved_cs_client
= @ Character_set_client */
No rows affected (0 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> /*! 40101 SET character_set_client
= Utf8 */
No rows affected (0.001 seconds)
0: jdbc: mysql: // hostname.mysql.cn/hivem> create table if not exists 'bucke
TING_COLS '('sd _ id' bigint (20) not null, 'bucket _ COL_NAME' varchar (256) CHARACT
Er set latin1 COLLATE latin1_bin default null, 'integer _ idx' int (11) not null, P
Rimary key ('sd _ id', 'integer _ idx'), KEY 'bucket _ COLS_N49 '('sd _ id'), CONSTRAI
NT 'bucket _ COLS_FK1 'foreign key ('sd _ id') REFERENCES 'sds' ('sd _ id') ENGIN
E = InnoDB default charset = latin1
Error: Can't create table 'hivemeta. bucketing_cols' (errno: 121) (state = HY000, code = 1005)
Closing: 0: jdbc: mysql: // hostname.mysql.cn/hivemeta
Org. apache. hadoop. hive. metastore. HiveMetaException: Schema initialization FAILED! Metastore state wocould be inconsistent !!
Org. apache. hadoop. hive. metastore. HiveMetaException: Schema initialization FAILED! Metastore state wocould be inconsistent !!
At org. apache. hive. beeline. HiveSchemaTool. doInit (HiveSchemaTool. java: 270)
At org. apache. hive. beeline. HiveSchemaTool. doInit (HiveSchemaTool. java: 243)
At org. apache. hive. beeline. HiveSchemaTool. main (HiveSchemaTool. java: 473)
At sun. reflect. NativeMethodAccessorImpl. invoke0 (Native Method)
At sun. reflect. NativeMethodAccessorImpl. invoke (NativeMethodAccessorImpl. java: 57)
At sun. reflect. DelegatingMethodAccessorImpl. invoke (DelegatingMethodAccessorImpl. java: 43)
At java. lang. reflect. Method. invoke (Method. java: 606)
At org. apache. hadoop. util. RunJar. run (RunJar. java: 221)
At org. apache. hadoop. util. RunJar. main (RunJar. java: 136)
Caused by: java. io. IOException: Schema script failed, errorcode 2
At org. apache. hive. beeline. HiveSchemaTool. runBeeLine (HiveSchemaTool. java: 358)
At org. apache. hive. beeline. HiveSchemaTool. runBeeLine (HiveSchemaTool. java: 326)
At org. apache. hive. beeline. HiveSchemaTool. doInit (HiveSchemaTool. java: 266)
... 8 more
* ** SchemaTool failed ***

6. Based on the keyword "Schema initialization FAILED! Metastore state wocould be inconsistent !!", Find the official hdp forum.
This is a known issue. You need to modify the SQL script of ambari and delete some indexes of mysql.
Https://community.hortonworks.com/questions/113748/hive-metastore-start.html

7. There are many officially recommended steps. Individuals are relatively lazy. In addition, this hive library is still normal the previous day. This problem suddenly occurs. I don't like it. This is the possibility of a bug.
Continue the analysis and check the log of hivemetastore.
The table does not exist.

15:34:43, 871 INFO [pool-8-thread-200]: metastore. HiveMetaStore (HiveMetaStore. java: logInfo (746)-200: get_all_databases
15:34:43, 871 INFO [pool-8-thread-200]: HiveMetaStore. audit (HiveMetaStore. java: logAuditEvent (371)-ugi = ambari-qa-hbjt_hadoop@domain.com ip =/172.18.98.57 cmd = get_all_databases
15:34:43, 873 ERROR [pool-8-thread-200]: metastore. retryingHMSHandler (RetryingHMSHandler. java: invoke( 165)-HMSHandler Fatal error: javax. jdo. JDOException: Exception thrown when executing query
At org. datanucleus. api. jdo. nucleus usjdohelper. getjdoexceptionfornucleus usexception (nucleus usjdohelper. java: 596)
......
NestedThrowablesStackTrace:
Com. mysql. jdbc. exceptions. jdbc4.MySQLSyntaxErrorException: Table 'hivemeta. dbs 'doesn' t exist
At sun. reflect. GeneratedConstructorAccessor60.newInstance (Unknown Source)
At sun. reflect. delegatingconstruct%cessorimpl. newInstance (delegatingconstruct%cessorimpl. java: 45)
At java. lang. reflect. Constructor. newInstance (Constructor. java: 526)

8. Now we need to analyze mysql and check it step by step.

Mysql> use hive
Mysql> show tables;
+ --------------------------- +
| Tables_in_hivemeta |
+ --------------------------- +
| BUCKETING_COLS |
| CDS |
| COLUMNS_V2 |
| COMPACTION_QUEUE |
| COMPLETED_TXN_COMPONENTS |
| DATABASE_PARAMS |
| DBS |
| DB_PRIVS |
| DELEGATION_TOKENS |

9. The problem is that the table does not exist. Why does it not exist? Actually, I already know the problem. Mysql table names are case sensitive.

Mysql> select count (*) from 'dbs ';
ERROR 1146 (42S02): Table 'hivemeta. DBS 'doesn' t exist

10. Table names in many Oracle databases are case-insensitive. However, Mysql is case-sensitive. Therefore, many developers prefer to set mysql to be case-insensitive.
After show tables, I suspect this is possible.

11. After checking/etc/my. cnf, we have indeed set lower_case_table_names = 1

12. mysql is case sensitive when the Hive library is created. Therefore, the table names are in uppercase. Later, the mysql parameter was modified to disable case sensitivity. In this way, the problem arises. Mysql is not completely insensitive, but the table name in the SQL statement created and queried after the lower_case_table_names = 1 parameter is set. It is changed to lowercase by default. The created table name is displayed in uppercase. However, no matter whether you use an upper-case or lower-case table name, you cannot query it correctly.

13. scenarios where this problem occurs:
Only after the hive database is created, modify/etc/my. cnf and add the parameter lower_case_table_names = 1.

14. After the problem is found, there are two solutions:
A. Cancel parameter lower_case_table_names = 1
B. If there is no data in the hive database of mysql, delete the hive database table, recreate the hive meta table, and create a lower-case table name directly.
The output is in lower case:

Mysql> show tables;
+ --------------------------- +
| Tables_in_hive_beta |
+ --------------------------- +
| Bucketing_cols |
| Cds |
| Columns_v2 |
| Compaction_queue |
| Completed_txn_components |
| Database_params |
| Db_privs |
| Dbs |
| Delegation_tokens |
| Func_ru |

15. I finally chose to cancel the parameter lower_case_table_names = 1 and restart mysql. The query is normal.

Mysql> select count (*) from 'dbs ';
+ ---------- +
| Count (*) |
+ ---------- +
| 2 |

16. Ambari started hive metastore.
Solve the problem and restore everything to normal

$ Beeline-u "jdbc: hive2: // hostname.domain.cn: 10000/default; principal = hive/_ HOST@domain.com ;"
Connecting to jdbc: hive2: // hostname.domain.cn: 10000/default; principal = hive/_ HOST@domain.com;
Connected to: Apache Hive (version release-1.2.1-EDH-1.1.2)
Driver: Hive JDBC (version release-1.2.1-EDH-1.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version release-1.2.1-EDH-1.1.2 by Apache Hive
0: jdbc: hive2: // hostname.domain.cn: 10000> show databases;
+ ---------------- + -- +
| Database_name |
+ ---------------- + -- +
| Default |
| Hbjt |
+ ---------------- + -- +
2 rows selected( 1.206 seconds)

The final cause is/etc/my. cnf modified.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151545.htm