How to perform Hive View to perform show create table truncated exception Analysis

In this issue, Xiaobian will bring you about how to carry out Hive view execution show create table truncated exception analysis. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

anomaly recurrence

When SHOW CREATE TABLE is executed on Hive's VIEW, the resulting output is only partial and truncated, which is present in Hue, Beeline, and Hive CLI, as shown below.

1. by Hue

2. by 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. via Hive CLI

hive> SHOW CREATE TABLE test_view;

OK

CREATE VIEW `test_view` AS SELECT

FROM `default`.` sample_07`

After studying and testing various CDH versions, it was found that the problem was caused by the " \t" character in the create statement of VIEW and only existed in CDH versions prior to 5.13.1. The output removes all characters after "\t." Starting with CDH5.13.1, Hive removes spaces before saving metadata information to MySQL, so this issue does not occur. However, if the problem has occurred in earlier versions of CDH, upgrading CDH alone will not solve it because the data in Metastore will not change. To resolve this issue, update the information in the Hive metabase as follows:

1. Stop the Hive service, which prevents subsequent operations on the Hive metabase;

2. Use the mysqldump command to dump the entire MySQL database into a text file;

3. Use a text editor to replace all "tabs" in the TBLS table with spaces;

4. Drop the old Hive metabase in MySQL directly;

5. Use the modified dump file to load into the newly created Hive metabase;

6. Restart the service and check if the problem is fixed;

It is best to test in DEV/TEST environment now and then make changes in production system to reduce impact on production system.

The above is how to carry out Hive view execution show create table truncated exception analysis, if there is a similar doubt, may wish to refer to the above analysis to understand. If you want to know more about it, please pay attention to the industry information channel.