Here is a simple example --Running a Select count inside Beeline for a table by name patb 0: jdbc:hive2://node1:10000> select * from patb; | patb.ida | patb.name | patb.month | | 5 | Ujjain | 6 | 1 row selected (0.388 seconds) Now we go ahead and set a variable. 0: jdbc:hive2://node1:10000> set hivevar:tablename=patb; No rows affected (0.005 seconds) 0: jdbc:hive2://node1:10000> select * from ${hivevar:tablename}; | patb.ida | patb.name | patb.month | | 5 | Ujjain | 6 | 1 row selected (0.14 seconds) Resolution: Please set the variable as set hivevar:tablename=patb; ... View more

Customer has a Hive Query with a Group By Clause and is stuck on the last Reducer. This looks like a Reducer Skew in the Group By Clause. You may please use the below said configuration in the Hive CLI at the session level. Inside the Hive CLI. set hive.groupby.skewindata=true; <Run the Query> ... View more

SYMPTOM: FAILED: SemanticException [Error 10122]: Bucketized tables do not support INSERT INTO: Table: XXXX ROOT CAUSE: Customer has a ACID enabled Cluster. Customer created a Table for which the TBLPROPERTIES ('transactional'='true') was not set. Customer's table has 50 Billion records in it. WORKAROUND: For a table to be transactional, we need to set TBLPROPERTIES ('transactional'='true') We can convert a table to Transactional Table using the Alter Table Command, assuming the table satisfies acid requirements --Bucketed, --Using ORC --Not sorted. RESOLUTION: Logically alter table to set transactional=true is supported. ... View more

PROBLEM: To run Hive in Silent Mode. HOW TO hive -S -e "Show databases" For example: hive -S -e "show databases;" SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/spark/lib/spark-assembly-1.4.1.2.3.2.0-2950-hadoop2.7.1.2.3.2.0-2950.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] WARNING: Use "yarn jar" to launch YARN applications. SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/spark/lib/spark-assembly-1.4.1.2.3.2.0-2950-hadoop2.7.1.2.3.2.0-2950.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] default prod_mti_hiroshima_fab_15_parm_dm xademo SOLUTION: hive -S -e "<Hive-Command>" ... View more

Please log on to the backend database: mysql> use hive; Database changed: mysql> show tables; The table of importance to us in this scenario is HIVE_LOCKS. (MySQL is sensitive to Upper and Lower Caps) mysql> describe HIVE_LOCKS; +---------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------+--------------+------+-----+---------+-------+ | HL_LOCK_EXT_ID | bigint(20) | NO | PRI | NULL | | | HL_LOCK_INT_ID | bigint(20) | NO | PRI | NULL | | | HL_TXNID | bigint(20) | YES | MUL | NULL | | HL_DB | varchar(128) | NO | | NULL | | | HL_TABLE | varchar(128) | YES | | NULL | | | HL_PARTITION | varchar(767) | YES | | NULL | | | HL_LOCK_STATE | char(1) | NO | | NULL | | | HL_LOCK_TYPE | char(1) | NO | | NULL | | | HL_LAST_HEARTBEAT | bigint(20) | NO | | NULL | | | HL_ACQUIRED_AT | bigint(20) | YES | | NULL | | | HL_USER | varchar(128) | NO | | NULL | | | HL_HOST | varchar(128) | NO | | NULL | | | HL_HEARTBEAT_COUNT | int(11) | YES | | NULL | | | HL_AGENT_INFO | varchar(128) | YES | | NULL | | | HL_BLOCKEDBY_EXT_ID | bigint(20) | YES | | NULL | | | HL_BLOCKEDBY_INT_ID | bigint(20) | YES | | NULL | | Run the following command: mysql> select * from HIVE_LOCKS; Empty set (0.00 sec) The above result would indicate that the tables do not have any locks. If locks are present, there will be entries in the HIVE_LOCKS table. ... View more

This is due to a setting in yarn-site.xml:yarn.resourcemanager.fs.state-store.retry-policy-spec Here is an example: yarn.resourcemanager.fs.state-store.retry-policy-spec: 2000,500 It is specified in pairs of sleep-time and number-of-retries This will mean that Hive Client will try connecting to nn1 for 500 times at an interval of 2000 Milliseconds. Contingent on the fact that it could not connect to nn1 for 500 times at an interval of 2000 Milliseconds, it will then try connecting to nn2. Resolution/Workaround: Lower the value of yarn.resourcemanager.fs.state-store.retry-policy-spec to 1000,10. ... View more