Grammar: A=B
Operation Type: All Basic Types
Description: true; If expression a is equal to expression b; Otherwise it's fake.
For example:
Hive & gt select 1 where1=1from lxw_dual;
1
2. Unequal comparison:
Grammar: a
Operation Type: All Basic Types
Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a and expression b are not equal; Otherwise it's fake.
For example:
Hive & gt select1from lxw _ dual where1<; & gt2;
1
3. Less than comparison:
Grammar: a
Operation Type: All Basic Types
Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a is less than expression b; Otherwise it's fake.
For example:
Hive & gt select1from lxw _ dual where1<; 2;
1
4. Less than or equal to the comparison value:
Grammar: a
Operation Type: All Basic Types
Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a is less than or equal to expression b; Otherwise it's fake.
For example:
Hive & gt select1from lxw _ dual where1<; = 1;
1
5. greater than comparison: >
Grammar: A > B
Operation Type: All Basic Types
Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a is greater than expression b; Otherwise it's fake.
For example:
hive & gtselect 1 from lxw _ dual where 2 & gt； 1;
1
6. greater than or equal to comparison: > =
Grammar: A > = B
Operation Type: All Basic Types
Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a is greater than or equal to expression b; Otherwise it's fake.
For example:
hive & gtselect 1 from lxw _ dual where 1 & gt； = 1;
1
Note: Pay attention to the comparison of strings (common time comparison can be made before to_date).
hive & gtselect * from lxw _ dual
good
20 1 1 1 1 120900:00:00 20 1 1 1 1 1209
Hive & gt selects a, b, a<b, a>b, and a=b comes from lxw _ dual.
20111/kloc-0: 00: 00 2011/kloc-0-0.
7. Null value judgment: null.
Syntax: A is empty.
Operation type: all types
Description: true; If the value of expression a is empty; Otherwise it's fake.
For example:
Hive & gt select1from lxw _ dual where null is null;
1
8. Non-NULL judgment: not null
Syntax: A is not empty.
Operation type: all types
Description: false; If the value of expression a is empty; Otherwise it's true.
For example:
Hive & gt select 1 from lxw _ dual where1is not empty;
1
9. like contrast: like it
Grammar: A is like B.
Operation type: string
Description: If string A or string B is NULL, null is returned; True; If string a conforms to the regular grammar of expression b; Otherwise it's fake. The character "_" in B represents any single character, while the character "%"represents any number of characters.
For example:
hive & gtselect 1 from lxw _ dual where ' football ' like ' foot % '；
1
Hive & gt select1from lxw _ dual where' football' is like' ‘foot _ _ _ _';
1
Note: use notlike b when negative comparison.
hive & gtselect 1 from lxw _ dual where NOT ' football ' like ' fff % '；
1
Like the operation of 10. JAVA: RLIKE
Grammar: A RLIKE B
Operation type: string
Description: If string A or string B is NULL, null is returned; True; If the string A conforms to the regular syntax of JAVA regular expression B; Otherwise it's fake.
For example:
hive & gtselect 1 from lxw _ dual where‘footbalr’like '^f.*r$'；
1
Note: To determine whether a string is all numbers:
hive & gtselect 1 from lxw _ dual where ' 123456 ' r like '^\\d+$'；
1
hive & gtselect 1 from lxw _ dual where ' 123456 aa ' like '^\\d+$'；
1 1. Regular expression operation: regular expression
Syntax: A REGEXP B
Operation type: string
Note: the function is the same as RLIKE.
For example:
hive & gtselect 1 from lxw _ dual where ' footbar ' regexp '^f.*r$'；
1
2. Mathematical operation: 1. Addition operation:+
Grammar: A+B
Operation type: all numeric types
Description: Returns the result of adding A and B, and the numerical type of the result is equal to the minimum parent type of A and B (see the inheritance relationship of data types for details). For example, int+int generally produces int type, and int+double generally produces double type.
For example:
Hive & gt select1+9 from lxw_dual;
10
hive & gtcreate table lxw _ dual as select 1+ 1.2 from lxw _ dual；
Hive & gt describes lxw _ dual
_c0 double precision
2. Subtraction operation:-
Grammar: a–b
Operation type: all numeric types
Description: Returns the result of subtraction of A and B, and the numerical type of the result is equal to the minimum parent type of A and B (see the inheritance relationship of data types for details). For example, int–int usually produces an int type, while int-double usually produces a double type.
For example:
Hive & gt select10–5 from lxw_dual;
five
Hive & gt creates the table lxw_dual as select 5.6-4 from lxw _ dual；;
Hive & gt describes lxw _ dual
_c0 double precision
3. Multiplication operation: *
Grammar: A * B
Operation type: all numeric types
Description: Returns the result of multiplication of A and B, and the numerical type of the result is equal to the minimum parent type of A and B (see the inheritance relationship of data types for details). Note that if the result of multiplying a by b exceeds the numerical range of the default result type, you need to convert the result to a wider numerical type through cast.
For example:
Hive & gt choose 40 * 5 from lxw_dual;
200
4. Division operation:/
Grammar: A/B
Operation type: all numeric types
Description: Returns the result of dividing a by b, and the numerical type of the result is double.
For example:
Hive & gt choose 40/5 from lxw_dual;
8.0
Note: The data type with the highest precision in hive is double, which is only accurate to 16 digits after the decimal point. Pay special attention when doing division.
Hive & gt choose CEIL (28.0/6.9999) from lxw_duallimit 1;
The result is 4.
Hive & gt choose CEIL (28.0/6.9999) from lxw_dual limit 1;
The result is 5.
5. Remainder operation:%
Grammar: A% B
Operation type: all numeric types
Description: Returns the remainder of a divided by b, and the numerical type of the result is equal to the minimum parent type of a type and b type (see the inheritance relationship of data types for details).
For example:
Hive & gt choose 41%5 from lxw_dual;
1
Hive & gt selects 8.4% 4 from lxw_dual;
0.40000000000000036
Note: accuracy is a big problem of hive, and it is best to specify accuracy through round for operations like this.
Hive & gt selects round (8.4% 4,2) from lxw_dual;
0.4
6. Bitwise AND operation:&;
Grammar: a &；; B
Operation type: all numeric types
Remarks: Returns the bitwise AND result of A and B, and the numerical type of the result is equal to the minimum parent type of A and B (see the inheritance relationship of data types for details).
For example:
Hive & gt chooses 4 & amp8 from lxw _ dual.
Hive & gt chooses 6 & amp4 from lxw _ dual.
four
7. Bit or operation: |
Grammar: A | B
Operation type: all numeric types
Remarks: Returns the result of bitwise OR operation of A and B. The numerical type of the result is equal to the minimum parent type of type A and type B (see the inheritance relationship of data types for details).
For example:
Hive & gt select 4 | 8 from lxw_dual;
12
Hive & gt select 6 | 8 from lxw_dual;
14
8. Bitwise XOR operation:
Grammar: a b
Operation type: all numeric types
Remarks: Returns the bitwise XOR result of A and B. The numerical type of the result is equal to the smallest parent type of A and B (see the inheritance relationship of data types for details).
For example:
Hive & gt choose 4 8 from lxw_dual;
12
Hive & gt choose 6 4 from lxw_dual;
2
9. bit inversion operation: ~
Grammar: ~A
Operation type: all numeric types
Description: The bitwise negation result returned. The numeric type of the result is equal to the type of.
For example:
Hive & gt selects ~ 6 from lxw_dual;
-7
Hive & gt selects ~ 4 from lxw_dual;
-5
Three. Logical operation: 1. Logic and operation: AND
Grammar: a and b
Operation type: Boolean
Description: If both A and B are true, it is true; Otherwise it's fake. If a is empty or b is empty, it is empty.
For example:
Hive & gt select 1 from lxw_dual, where 1= 1, 2 = 2;
1
2. Logical OR operation: or
Grammar: a or b
Operation type: Boolean
Description: If A is true, or B is true, or both A and B are true, it is true; Otherwise it's fake.
For example:
Hive & gt select 1 from lxw_dual, where 1=2 or 2 = 2;
1
3. Logical NOT operation: NOT
Grammar: No.
Operation type: Boolean
Description: true; If a is false or a is empty; Otherwise it's fake.
For example:
Hive & gt selects 1 from lxw_dual, where not1= 2;
1
4. Numerical calculation 1. Rounding function: rounding
Grammar: round (double a)
Return value: BIGINT
Description: Returns the integer value part of type double (following rounding).
For example:
Hive & gt select round (3.1415926) from lxw_dual;
three
Hive & gt select round (3.5) from lxw_dual;
four
Hive & gt creates a table lxw_dual from lxw_dual as a select round (9542.158);
Hive & gt describes lxw _ dual
_c0 bigint
2. Specify the exact rounding function: round.
Syntax: round(double a, int d)
Return value: DOUBLE
Description: Returns a double-precision value with the specified precision d.
For example:
Hive & gt select round (3.1415926,4) from lxw_dual;
3. 14 16
3. Rounding down function: lower limit
Grammar: floor (double a)
Return value: BIGINT
Description: Returns the largest integer equal to or less than the double variable.
For example:
Hive & gt select the floor from lxw_dual (3.1415926);
three
Hive & gt select the floor from lxw_dual (25);
25
4. Integer function: ceil
Grammar: ceil (double A)
Return value: BIGINT
Description: Returns the smallest integer equal to or greater than a double variable.
For example:
Hive & gt choose CEIL (3.1415926) from lxw_dual;
four
Hive & gt chooses Ceil (46) from lxw_dual;
46
5. Round-up function: upper limit
Grammar: ceiling (double A)
Return value: BIGINT
Description: Same function as ceiling.
For example:
Hive & gt selects the ceiling from lxw_dual (3.1415926);
four
Hive & gt selects ceiling (46) from lxw_dual;
46
6. Take random number function: rand
Grammar: rand (), rand(int seed)
Return value: double
Description: Returns a random number from 0 to 1 If seed is specified, you will wait for a stable random number sequence.
For example:
Hive & gt choose rand () from lxw_dual;
0.5577432776034763
Hive & gt choose rand () from lxw_dual;
0.6638336467363424
Hive & gt selects rand from lxw_dual (100);
0.7220096548596434
Hive & gt selects rand from lxw_dual (100);
0.7220096548596434
7. Natural exponential function: exp
Grammar: exp (double a)
Return value: double
Description: Returns the power of natural logarithm e.
For example:
hive & gtselect exp(2)from lxw _ dual；
7.38905609893065
Natural logarithmic function: ln
Grammar: ln (double a)
Return value: double
Description: The natural logarithm returned.
For example:
Hive & gt chooses ln (7.7000) from lxw_dual;
2.0
8. Logarithmic function with radix 10: log 10.
Syntax: log 10 (double a)
Return value: double
Description: Returns the logarithm of a based on 10.
For example:
Hive & gt select log10 (100) from lxw_dual;
2.0
9. log2 is the logarithm function with base 2.
Syntax: log2 (double A)
Return value: double
Description: Returns the base 2 logarithm.
For example:
Hive & gt select log2 (8) from lxw_dual;
3.0
10. Logarithmic function: log
Syntax: log (double radix, double A)
Return value: double
Description: Returns the base logarithm.
For example:
Hive & gt select log (4,256) from lxw_dual;
4.0
1 1. power supply operation function: power supply
Grammar: pow (double a, double p)
Return value: double
Description: returns a to the power of p.
For example:
Hive & gt choose pow (2,4) from lxw_dual;
16.0
12. Power supply operation function: power supply
Grammar: power (double a, double p)
Return value: double
Description: Returns the power of a to the power of p, the same as pow.
For example:
Hive & gt selects power (2,4) from lxw_dual;
16.0
13. Square root function: sqrt
Grammar: sqrt (double a)
Return value: double
Description: Returns.
For example:
Hive & gt selects sqrt (16) from lxw_dual;
4.0
14. Binary function: bin
Grammar: bin(BIGINT a)
Return value: string
Description: Returns.
For example:
Hive & gt selects bin (7) from lxw_dual;
1 1 1
15. Hexadecimal function
Grammar: hexadecimal (BIGINT a)
Return value: string
Description: If the variable type is int, the hexadecimal representation of a is returned; If the variable is a string type, the hexadecimal representation of the string is returned.
For example:
Hive & gt selects hexadecimal from lxw_dual (17);
1 1
Hive & gt selects hexadecimal ('ABC') from lxw_dual;
6 16263
16. Anti-hexadecimal function: unhex
Syntax: unhex (string A)
Return value: string
Description: Returns the string encoded by this hexadecimal string.
For example:
Hive & gt Select Unhex ('616263') from lxw_dual;
alphabet
Hive & gt Select Unhex ('11') from lxw_dual;
-
Hive & gt choose unhax (616263) from lxw_dual;
alphabet
17. binary conversion function: conv
Syntax: Conv (bigint num, int from _ base, int to _ base)
Return value: string
Description: Converts the numeric value num from _base to _base.
For example:
Hive & gt select conv( 17, 10,16) from lxw_dual;
1 1
Hive & gt select conv( 17,10,2) from lxw_dual;
1000 1
18. Absolute value function: abs
Grammar: abs (double a) abs(int a)
Return value: double int
Description: Returns the absolute value of a.
For example:
Hive & gt selects ABS (-3.9) from lxw_dual;
3.9
Hive & gt selects ABS from lxw_dual (10.9);
10.9
19. Positive complementary function: pmod
Grammar: pmod (int a, int b), pmod (double a, double b)
Return value: int double
Description: returns the remainder of positive a divided by b.
For example:
Hive & gt selects pmod (9,4) from lxw_dual;
1
Hive & gt selects pmod (-9,4) from lxw_dual;
three
20. Sine function: sine
Grammar: sin (double a)
Return value: double
Description: Returns.
For example:
Hive & gt selects sin (0.8) from lxw_dual;
0.7 173560908995228
2 1. arcsine function: asin
Grammar: asin (double a)
Return value: double
Description: Returns.
For example:
Hive & gt choose asin (0.7173560908995228) from lxw_dual;
0.8
22. Cosine function: cos
Grammar: cos (double a)
Return value: double
Description: Returns.
For example:
Hive & gt selects cos (0.9) from lxw_dual;
0.62 16099682706644
23. anti-cosine function: acos
Grammar: acos (double a)
Return value: double
Remarks: Return.
For example:
Hive & gt choose acos (0.6216099682706644) from lxw_dual;
0.9
24. Positive function: positive
Grammar: positive (int a), positive (double a)
Return value: int double
Description: Return to
For example:
Hive & gt selects a positive number (-10) from lxw_dual;
- 10
Hive & gt selects positive from lxw_dual (12);
12
25. Negative function: negative
Grammar: negative (int a), negative (double a)
Return value: int double
Description: Return to -a
For example:
Hive & gt selects negative (-5) from lxw_dual;
five
Hive & gt selects negative (8) from lxw_dual;
-8