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

This article introduces the relevant knowledge of "the usage of hive WHERE sentences". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

WHERE statement

SELECT statement is used to select fields, WHERE statement is used to filter conditions, and the combination of the two can find records that meet the filter criteria. We've used it in many simple examples before, so let's take a closer look at it.

WHERE statements use predicate expressions, which we will discuss later when columns are applied to predicate operators. There are several predicate expressions that can be concatenated using AND and OR. When the predicate expression evaluates to true, the corresponding rows are retained and output.

Note: aliases cannot be used after WHERE. If you want to use aliases, you can use nested statements to query.

1. Predicate operator

The following predicate operators can be used for JOIN... In ON and HAVING statements.

Operator

Supported data types

Description

Axib

Basic data type

Returns TRUE if An equals B, and FLASE otherwise

AB

Basic data type

Return TRUE if both An and B are NULL, the others are the same as the equal sign (=) operator, and NULL if either is NULL

Astragalus B

No

This is the wrong syntax! SQL uses = instead of = =

ABBJ Achievement B

Basic data type

If An or B is NULL, NULL; is returned. If An is not equal to B, TRUE is returned, otherwise FALSE is returned.

AB

Basic data type

If An or B is NULL, return NULL;. If An is greater than B, return TRUE, and vice versa. Return FALSE.

A > = B

Basic data type

If An or B is NULL, NULL; is returned. If An is greater than or equal to B, TRUE is returned, otherwise FALSE is returned.

A [NOT] BETWEEN B AND C

Basic data type

If either NULL B or C is NULL, the result is NULL. If the value of An is greater than or equal to B and less than or equal to C, the result is TRUE, and vice versa. If you use the NOT keyword, you can achieve the opposite effect.

An IS NULL

All data types

Returns TRUE; if An equals NULL, and vice versa returns FLASE

An IS NOT NULL

All data types

Return TRUE; if An is not equal to NULL, otherwise return FLASE

A [NOT] LIKE B

STRING Typ

B is a simple regular expression under SQL. If A matches it, it returns TRUE;, and vice versa. The expression of B is as follows:'x% 'means that A must start with the letter' x','% x 'means that A must end with the letter' x', and'% x% 'means that A contains the letter' x 'and can be at the beginning, the end, or the middle of the string. Similarly, the underscore'_ 'matches a single character. B must match the entire string A. If you use the NOT keyword, you can achieve the opposite effect.

A RLIKE B,A REGEXP B

STRING Typ

B is a regular expression, and if A matches it, it returns TRUE; and vice versa. Matching is implemented using the regular expression interface in JDK, because the regular rules are also based on the rules in it. For example, a regular expression must match the entire string A, not just its substring.

two。 About floating-point number comparison

A common trap for floating point comparison occurs when comparing different types (that is, FLOAT and DOUBLE comparisons).

Eg: the following query statement for employees, which will return the employee's name, payroll, and federal tax. The filter condition is that the payroll tax deduction exceeds 0.2 (the previously defined map of deductions is of type FLOAT)

Hive > SELECT name,salary,deductions ['Federal Taxes']

> FROM employees WHERE deductions ['Federal Taxes'] > 0.2

John Doe 100000.0 0.2

Mary Smith 80000.0 0.2

Boss Man 200000.0 0.3

Fred Finance 150000.0 0.3

Wait! Why is the record of deductions ['Federal Taxes'] = 0.2 also output?

Is this Hive's Bug? This actually reflects how floating-point comparisons are made internally, which affects almost all software written in a variety of programming languages in today's digital computers.

In fact, we can say that 0.2 is 0.2000001 for FLOAT type and 0.200000000001 for DOUBLE type. This is because an 8-byte double value has more decimal places. When the float value in the table is converted to a double value through Hive, the resulting double value is 0.200000100000, which is actually larger than 0.200000000001. This is why the query result seems to use > =.

This problem exists not only in Hive or Java, but also in other systems.

Hive has two ways to circumvent this problem.

1. If the storage format is TEXTFILE, then Hive reads the string "0.2" from the data file and converts it to a real number. We can define the corresponding field type as DOUBLE instead of FLOAT in the table schema, so that we can compare the deductions ['Federal Taxes'] double value with the 0.2 double value.

two。 To change the data type using the cast operator

Eg: here are the modified statements and results

Hive > SELECT name,salary,deductions ['Federal Taxes']

> FROM employees WHERE deductions ['Federal Taxes'] > cast (0.2 AS FLOAT)

Boss Man 200000.0 0.3

This is the end of Fred Finance 150000.0 "usage of hive WHERE sentences". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.