2. From Beeline:
0: jdbc:hive2://localhost:10000/default> SHOW CREATE TABLE test_view; +------------------------------------+--+ | createtab_stmt | +------------------------------------+--+ | CREATE VIEW `test_view` AS SELECT | | | | FROM `default`.`sample_07` | +------------------------------------+--+3. From Hive CLI:
hive> SHOW CREATE TABLE test_view; OK CREATE VIEW `test_view` AS SELECT FROM `default`.`sample_07`After some researching and testing in varies CDH versions, I found out that the issue was caused by having “\t” character in the VIEW’s create statement, and it only happens in CDH version before 5.13.1. Any characters after “\t” will be removed in the output. What happens in CDH 5.13.1 version is that Hive will remove white spaces in the query string before saving to database, so this will not happen, however, simply upgrade CDH will not help to resolve the issue, as the code that reads from Hive MetaStore database does not change. To fix the issue, we need to update the database stored in the Hive MetaStore backend, please follow below steps (this is for MySQL, for other database types, please consult with vendor): 1. STOP Hive services, so that no once an use the it to update Hive Metadata by running any queries 2 Dump the whole MySQL database out into text a file using “mysqldump” command line tool (please make sure all data is dumped correctly so that we can load data back) 3. Use text editor to replace “tab” with spaces for all the data stored in “TBLS” table 4. Since we can not RENAME databases, just drop it and re-create it 5. Reload the modified dump file back into newly created database 6. Restart Hive and confirm if issue is fixed. It is better to test this in the DEV/TEST cluster before applying the same change in PROD to minimize mistakes. Hope above helps.
