Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.
💃 Hive functions
Built-in functions can be classified according to their application types, such as numeric functions, date functions, and characters
String function, set function, conditional function, etc.;
User-defined functions can be classified according to the number of input and output lines, for example, UDF, UDAF, and UDTF.
💃 Built-in functions:
💃 string function:
String length function: length
The string reverse function: reverse
String concatenation function: concat
Delimited string concatation function: concat_ws
String interception functions: substr,substring
String to uppercase functions: upper,ucase
String to lowercase functions: lower,lcase
Remove whitespace function: trim
The left side of the whitespace function: ltrim
To the right, remove the whitespace function: rtrim
Regular expression replacement function: regexp_replace
Regular expression parsing function: regexp_extract
URL parsing function: parse_url
Json parsing function: get_json_object
Space string function: space
Repeat string function: repeat
First character ASCII function: ASCII
Left complement function: lpad
Right complement function: rpad
The split string function: split
Set lookup function: find_in_set
💃 User-defined function categories
User-defined -Function (UDF) common functions, one in, one out
User-defined Aggregation Function (UDAF) : multiple in and out
User-defined table-generating Functions (UDTF) Generate Functions for tables
💃 window function
Window functions are SQL functions that are very suitable for data analysis, so they are also called OLAP functions
The big feature is that the input value is retrieved from one or more rows of “Windows” in the result set of the SELECT statement, via the OVER clause, window function
Numbers are different from other SQL functions. If a function has an OVER clause, it is a window function. If it lacks the OVER clause, it is an ordinary aggregate function.
💃 date function
Get the current date: current_date
Get the current timestamp: current_timestamp
UNIX timestamp to date function: from_unixtime
Gets the current UNIX timestamp function: unix_TIMESTAMP
Date-to-unix timestamp function: unix_timestamp
Specify format date to UNIX timestamp function: unix_timestamp
Extract the date function: to_date
Date year function: year
Date to month function: month
Date to day function: day
Date to hour function: hour
Date to minute function: minute
Date to second function: second
Date rotation function: weekofyear
Date comparison function: datediff
Date increment function: date_add
Date reduction function: date_sub
💃 data function
• Take the integer function round
• Specify the precision of the integer function: round
• Take the whole function down: floor
• Integer up function: ceil
• Random number function: rand
• Binary function: bin
• Base conversion function: conv
• Absolute value function: ABS
💃 conditional function
• If (Boolean testCondition, T valueTrue, T valueFalseOrNull)
• Null judgment function: isnull(a)
• Non-null judge function: isnotnull (a)
• Null value conversion function: NVL (T value, T default_value)
• Non-null search function: COALESCE(T v1, T v2…)
CASE a WHEN B THEN C [WHEN D THEN E]* [ELSE F] END
• Nullif (a, b): if a = b, return NULL; Otherwise return a.
• ASSERT_true: Raises an exception if ‘condition’ is not true, otherwise returns null common grouping sort functions
Row_number: Within each group, assign each row a unique sequence number starting at 1, increasing, regardless of repetition;
Rank: In each group, assign a sequence number starting from 1 to each row.
Dense_rank: In each group, assign a sequence number starting from 1 to each row, allowing for repetition and not crowding subsequent positions;
💃 aggregate function
max()|min()|sum()|count()|avg()
Copy the code💃 explodes function
Explode takes map or array data as a parameter and explodes each element of the parameter into a row.
Typically, explode can be used directly or in conjunction with the lateral view.
Use of explode function
If explode is not allowed to appear next to explode, Hive provides syntax lateral View
Figure, used specifically to pair UDTF functions like explode
Lateral view
Lateral View is a special syntax that is used with UDTF-type functions to resolve UDTF functions
Some query limitation issues.
The sample
select explode(`array`(11.22.33)) as item;
select explode(`map`("id".10086."name"."zhangsan"."age".18));
select a.team_name ,b.year
from table_name a lateral view explode(tab_year) b as year
Copy the code