02-17-2016 11:20 PM
The order of column from hive metastore on Cloudera 5.5.2 - MySQL was wrong so that all hive tables do not follow the underlying data in hdfs. Please see an example below. Pay attention to the order of the columns.
The order of the column after creation was changed. See the column 3 which should be fcode now change to status.
I tried to find in metastore table, my conclusion is hive metatstore somehow order the column integer_idx base on varchar. This way, the column status which index is 10 comes after actype which index is 1. It should be column fcode which index is 2.
From the table description, I found the the integer_idx has varchar data type. This is wierd because in Postgresql, the table Columns_V2 has integer data type.
I have just changed the type to integer but the error persists.
02-18-2016 05:49 PM
After trying several methods, I finally reverted back to Postgresql. I got this problem when I changed Hive Metastore Database from Postgresql into MySQL. Along the way I found several problem such as this question, problem with MySql 5.7 which drops OPTION command (hive or hue still using it), and problem with old libmysql-java.jar which solved by using mysql-connector-java-5.1.38-bin.jar. However the strange behavior on the order of hive table column (it reads underlying hdfs data wrongly) was persistent. I even downgraded the Cloudera Parcel from 5.5.2 to 5.4.9. which no avail. Reverting back to Postgresql is the only solution right now, I hope somebody can solve this very soon.
02-23-2016 03:00 PM
The INTEGER_IDX column in COLUMNS_V2 table has always been of integer datatype (as the name of the column suggests) in HMS schemas for all db types, including Postgres and MySQL. If it is of type VARCHAR, it will change ordering of columns, as you suggest.
When you say you "changed Hive Metastore Database from Postgresql into MySQL", how exactly did you do that ? The schema probably got corrupted in this process (wrong column name to type mapping for example). It is likely that other tables in the schema are corrupted as well. One way to check is to create a fresh metastore schema for MySQL (in a test installation, for example) and compare your current schema with it.