Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you how to solve the different results of Hive and Imapla query decimal types. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Abnormal recurrence

1. The file hive.txt exists in the / tmp/newfile directory on the current cluster HDFS

[root@cdp4 ~] # mkdir / data/ZK

[root@cdp4 ~] #

two。 Create a hive table from Hue test as follows

Create external table test (S1 string,s2 decimal (13jue 2)) row format delimited fields terminated by'# 'stored as textfile location' / tmp/newfile'

3. When you make a query in Hive, you can see the results shown in the following figure.

Select * from test

4. When querying in Impala, you can see that the S2 column is all empty.

The same table is queried in Hive and Impala respectively, and the results are different, and the exception can be reproduced.

Abnormal analysis

According to the steps in the exception reproduction section, the data type of the S2 field is decimal (13Phone2). The precision is only 2 digits, but our original data have 3 decimal places after the decimal point. Hive will lose precision and print results when querying here. However, when Impala queries, checking the decimal type will be more stringent. The current accuracy of the original data exceeds the precision of the data type set in the S2 field, so the direct return is empty.

Exception resolution

According to the above analysis, it is because the accuracy of the data exceeds the setting of the S2 field, which can be solved by changing the field type.

1. Execute the following SQL in Hue to change the field type

ALTER TABLE test CHANGE s2 s2 string

two。 After querying again, you can see that the data can be found in Impala, and you can see that the data type of S2 field has become string.

3. Because the data type has changed, when we query from hive, we can see that the query result has also changed, which is consistent with the query result in Impala.

When Hive and Impala encounter precision mismatch at the same time, Hive will output the result by losing precision, and in the same case, Impala will directly return NULL.

When building the table, if the decimal data type is involved, we need to design the relevant parameters according to the accuracy of the original data to avoid the loss of precision.

If there is a loss of precision in the current table due to historical reasons, we can deal with it by converting the data type. You can first convert to the string type for query. Later, according to the data range of the table, it can also be modified to other precision matching data types for processing.

This is how to solve the different results of Hive and Imapla query decimal types shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.