Troubleshooting Hive

Error Related to Character Set Used for MySQL: "Specified key was too long; max key length is 767 bytes"
MySQL is the default database used by the Hive metastore. Depending on several factors, such as the version and configuration of MySQL, Hive developers may encounter an error message similar to the following:
```
An exception was thrown while adding/validating classes) : Specified key was too long; max key length is 767 bytes 
```
Administrators can resolve this issue by altering the Hive metastore database to use the Latin1 character set, as shown in the following example:
```
mysql> ALTER DATABASE <metastore_database_name> character set latin1; 
```

Limitations When Using the timestamp.formats SerDe Parameter

The timestamp.formats SerDe parameter, introduced in HDP 2.3, produces the following behaviors:

Displays only 3 decimal digits when it returns values, but it accepts more decimal digits.

For example, if you run the following commands:

drop table if exists src_hbase_ts;

create table src_hbase_ts( rowkey string, ts1 string, ts2 string, ts3 string, ts4 string ) 
STORED BY 'org.apache.hadoop.hive. hbase. HBaseStorageHandler' WITH SERDEPROPERTIES 
('hbase.columns.mapping' = 'm:ts1,m:ts2,m:ts3,m:ts4') TBLPROPERTIES ('hbase.table.name' = 'hbase_ts');

insert into src_hbase_ts values ('1','2011-01-01T01:01: 01.111111111', '2011-01-01T01:01: 01.123456111', 
'2011-01-01T01:01: 01.111111111', '2011-01-01T01:01: 01.134567890');

drop table if exists hbase_ts_1;

create external table hbase_ts_1( rowkey string, ts1 timestamp, ts2 timestamp, ts3 timestamp, ts4 timestamp ) 
STORED BY 'org.apache.hadoop.hive. hbase. HBaseStorageHandler' WITH SERDEPROPERTIES 
( 'hbase.columns.mapping' = 'm:ts1,m:ts2,m:ts3,m:ts4', 'timestamp.formats' = "yyyy-MM-dd'T'HH:mm:ss.SSSSSSSSS") 
TBLPROPERTIES ('hbase.table.name' = 'hbase_ts');

select * from hbase_ts_1;

The timestamp.formats parameter displays:

1 2011-01-01 01:01:01.111 2011-01-01 01:01:01.123 2011-01-01 01:01:01.111 2011-01-01 01:01:01.134

When the expected output is:

1 2011-01-01 01:01:01.111111111 2011-01-01 01:01:01.123456111 2011-01-01 01:01:01.111111111 2011-0

The yyyy-MM-dd’T'HH:mm:ss.SSSSSSSSS format accepts any timestamp data up to .SSSSSSSSS decimal digits (9 places to the left of the decimal) instead of only reading data with .SSSSSSSSS decimal digits (9 places to the left of the decimal).

For example, if you run the following commands:

drop table if exists src_hbase_ts; create table src_hbase_ts( rowkey string, ts1 string, ts2 string, ts3 string, ts4 string ) 
STORED BY 'org.apache.hadoop. hive. hbase.HBaseStorageHandler' WITH SERDEPROPERTIES 
('hbase.columns.mapping' = 'm:ts1,m:ts2,m:ts3,m:ts4') TBLPROPERTIES ('hbase.table.name' = 'hbase_ts');

insert into src_hbase_ts values ('1','2011-01-01T01:01: 01.111111111', '2011-01-01T01:01: 01.111', 
'2011-01-01T01:01: 01.11', '2011-01-01T01:01:01.1');

drop table if exists hbase_ts_1;

create external table hbase_ts_1( rowkey string, ts1 timestamp, ts2 timestamp, ts3 timestamp, ts4 timestamp ) 
STORED BY 'org.apache.hadoop. hive. hbase.HBaseStorageHandler' WITH SERDEPROPERTIES 
( 'hbase.columns.mapping' = 'm:ts1,m:ts2,m:ts3,m:ts4', 'timestamp.formats' = "yyyy-MM-dd'T'HH:mm:ss.SSSSSSSSS") 
TBLPROPERTIES ('hbase.table.name' = 'hbase_ts');

select * from hbase_ts_1;

The actual output is:

1 2011-01-01 01:01:01.111 2011-01-01 01:01:01.111 2011-01-01 01:01:01.11 2011-01-01 01:01:01.1

When the expected output is:

1 2011-01-01 01:01:01.111 NULL NULL NULL

​Troubleshooting Hive