hive内置函数大全

最新推荐文章于 2024-05-15 08:41:29 发布
最新推荐文章于 2024-05-15 08:41:29 发布
阅读量8.6k 收藏 17
点赞数
分类专栏： hive 文章标签： hive 内置函数 函数大全 show functions  desc function extended 
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kaaosidao/article/details/79624151
版权

一、查看函数

        show functions 显示hive下内置所有函数

        desc function extended add_months 显示add_months 函数用法

二、按首字母排序

    hive版本1.1.0-cdh5.7.0.

函数Usage举例
!! a - Logical notNo example for !.
%a % b - Returns the remainder when dividing a by b.No example for %.
&a & b - Bitwise AND.SELECT 3 & 5; 1
* a * b - Multiplies a by b.No example for *.
+a + b - Returns a+b.No example for +.
-a - b - Returns a-b.No example for -.
/a / b - Divides a by b.> SELECT 3 / 2;
 1.5
<a < b - Returns TRUE if a is less than b.No example for <.
<=a <= b - Returns TRUE if a is not greater than b.No example for <=.
<=>a <=> b - Returns same result with EQUAL(=) operator for non-null operands,
 but returns TRUE if both are NULL, FALSE if one of the them is NULL.		No example for <=>.
=a = b - Returns TRUE if a equals b and false otherwise.No example for =.
== a == b - Returns TRUE if a equals b and false otherwise.No example for ==.
> a > b - Returns TRUE if a is greater than b.No example for >.
>=a >= b - Returns TRUE if a is not smaller than b.No example for >=.
^a ^ b - Bitwise exclusive OR.SELECT 3 ^ 5; 2
 absabs(expr) - Returns the absolute value of the numeric value> SELECT abs('-1');
 1
acosacos(x) - Returns the arc cosine of x if -1<=x<=1 or NaN otherwise.> SELECT acos(1);
 0.0
> SELECT acos(2);
 NaN
add_months add_months(start_date, num_months) - Returns the date that is num_months after start_date.> SELECT add_months('2016-08-31', 1);
 '2016-09-30'
anda and b - Logical AND.No example for and.
approx_count_distinctapprox_count_distinct(expr) - Returns the estimated cardinality by HyperLogLog++.
 approx_count_distinct(expr, relativeSD=0.05) - Returns the estimated cardinality by HyperLogLog++
 with relativeSD, the maximum estimation error allowed.		No example for approx_count_distinct.
arrayarray(n0, ...) - Returns an array with the given elements.No example for array.
array_containsarray_contains(array, value) - Returns TRUE if the array contains the value. > SELECT array_contains(array(1, 2, 3), 2);
 true
asciiascii(str) - Returns the numeric value of the first character of str.> SELECT ascii('222');
 50
> SELECT ascii(2);
 50
asinasin(x) - Returns the arc sin of x if -1<=x<=1 or NaN otherwise.> SELECT asin(0);
 0.0
> SELECT asin(2);
 NaN
assert_trueassert_true(condition) - Throw an exception if 'condition' is not true.No example for assert_true.
atan atan(x) - Returns the arc tangent.> SELECT atan(0);
 0.0
atan2atan2(x,y) - Returns the arc tangent2.> SELECT atan2(0, 0);
 0.0
avgavg(x) - Returns the mean calculated from values of a group.No example for avg.
base64base64(bin) - Convert the argument from binary to a base 64 string.No example for base64.
binbin(x) - Returns x in binary.> SELECT bin(13);
 '1101'
bround bround(x, d) - Round x to d decimal places using HALF_EVEN rounding mode.> SELECT bround(2.5, 0);
 2.0
cbrtcbrt(x) - Returns the cube root of a double value.> SELECT cbrt(27.0);
 3.0
ceilceil(x) - Returns the smallest integer not smaller than x.> SELECT ceil(-0.1);
 0
> SELECT ceil(5);
 5
ceilingceiling(x) - Returns the smallest integer not smaller than x.> SELECT ceiling(-0.1);
 0
> SELECT ceiling(5);
 5
coalescecoalesce(a1, a2, ...) - Returns the first non-null argument if exists. Otherwise, NULL. SELECT coalesce(NULL, 1, NULL);
 1
collect_listcollect_list(expr) - Collects and returns a list of non-unique elements.No example for collect_list.
collect_setcollect_set(expr) - Collects and returns a set of unique elements.No example for collect_set.
concat concat(str1, str2, ..., strN) - Returns the concatenation of str1, str2, ..., strNSELECT concat('Spark','SQL');
 'SparkSQL'
concat_wsconcat_ws(sep, [str | array(str)]+) - Returns the concatenation of the strings separated by sep.> SELECT concat_ws(' ', Spark', 'SQL');
 'Spark SQL'
conv conv(num, from_base, to_base) - Convert num from from_base to to_base.> SELECT conv('100', 2, 10);
 '4'
> SELECT conv(-10, 16, -10);
 '16'
corrcorr(x,y) - Returns Pearson coefficient of correlation between a set of number pairs.No example for corr.
coscos(x) - Returns the cosine of x.> SELECT cos(0);
 1.0
coshcosh(x) - Returns the hyperbolic cosine of x.> SELECT cosh(0);
 1.0
count count(*) - Returns the total number of retrieved rows, including rows containing NULL values.
 count(expr) - Returns the number of rows for which the supplied expression is non-NULL.
 count(DISTINCT expr[, expr...]) - Returns the number of rows for which the supplied expression(s) are unique and non-NULL		No example for count.
covar_popcovar_pop(x,y) - Returns the population covariance of a set of number pairs.No example for covar_pop.
covar_sampcovar_samp(x,y) - Returns the sample covariance of a set of number pairs.No example for covar_samp.
crc32crc32(input) - Returns a cyclic redundancy check value as a bigint of the input> SELECT crc32('Spark');
 '1557323817'
cube N/A. N/A.
cume_distcume_dist() - The CUME_DIST() function computes the position of a value relative to
 a all values in the partition.		No example for cume_dist.
current_databasecurrent_database() - Returns the current database.> SELECT current_database()
current_date current_date() - Returns the current date at the start of query evaluation.No example for current_date.
current_timestampcurrent_timestamp() - Returns the current timestamp at the start of query evaluation.No example for current_timestamp.
date_adddate_add(start_date, num_days) - Returns the date that is num_days after start_date.> SELECT date_add('2016-07-30', 1);
 '2016-07-31'
date_formatdate_format(date/timestamp/string, fmt) - Converts a date/timestamp/string to a value of string in the format specified by the date format fmt.> SELECT date_format('2016-04-08', 'y')
 '2016'
date_subdate_sub(start_date, num_days) - Returns the date that is num_days before start_date.> SELECT date_sub('2016-07-30', 1);
 '2016-07-29'
datediffdatediff(date1, date2) - Returns the number of days between date1 and date2.> SELECT datediff('2009-07-30', '2009-07-31');
 1
dayday(param) - Returns the day of month of date/timestamp, or the day of interval.> SELECT day('2009-07-30');
 30
dayofmonthdayofmonth(param) - Returns the day of month of date/timestamp, or the day of interval.> SELECT dayofmonth('2009-07-30');
 30
dayofyeardayofyear(param) - Returns the day of year of date/timestamp.> SELECT dayofyear('2016-04-09');
 100
decodedecode(bin, str) - Decode the first argument using the second argument character set.No example for decode.
degreesdegrees(x) - Converts radians to degrees.> SELECT degrees(3.141592653589793);
 180.0
dense_rankdense_rank() - The DENSE_RANK() function computes the rank of a value in a group of
 values. The result is one plus the previously assigned rank value. Unlike Rank,
 DenseRank will not produce gaps in the ranking sequence.		No example for dense_rank.
e e() - Returns Euler's number, E.> SELECT e();
 2.718281828459045
eltelt(n, str1, str2, ...) - returns the n-th string, e.g. returns str2 when n is 2> SELECT elt(1, 'scala', 'java') FROM src LIMIT 1;
'scala'
encodeencode(str, str) - Encode the first argument using the second argument character setNo example for encode.
expexp(x) - Returns e to the power of x.> SELECT exp(0);
 1.0
explodeexplode(a) - Separates the elements of array a into multiple rows, or the elements of map a into multiple rows and columns.
Extended Usage:
> SELECT explode(array(10,20));
 10
 20		> SELECT explode(array(10,20));
 10
 20
expm1expm1(x) - Returns exp(x) - 1.> SELECT expm1(0);
 0.0
factorialfactorial(n) - Returns n factorial for n is [0..20]. Otherwise, NULL.> SELECT factorial(5);
 120
find_in_setfind_in_set(str, str_array) - Returns the index (1-based) of the given string (left) in the comma-delimited list (right).
 Returns 0, if the string wasn't found or if the given string (left) contains a comma.		> SELECT find_in_set('ab','abc,b,ab,c,def');
 3
firstfirst(expr) - Returns the first value of `child` for a group of rows.
 first(expr,isIgnoreNull=false) - Returns the first value of `child` for a group of rows.
 If isIgnoreNull is true, returns only non-null values.		No example for first.
first_valuefirst_value(expr) - Returns the first value of `child` for a group of rows.
 first_value(expr,isIgnoreNull=false) - Returns the first value of `child` for a group of rows.
 If isIgnoreNull is true, returns only non-null values.		No example for first_value.
floorfloor(x) - Returns the largest integer not greater than x.> SELECT floor(-0.1);
 -1
> SELECT floor(5);
 5
format_numberformat_number(X, D) - Formats the number X like '#,###,###.##', rounded to D decimal places.
 If D is 0, the result has no decimal point or fractional part.
 This is supposed to function like MySQL's FORMAT.		> SELECT format_number(12332.123456, 4);
 '12,332.1235'
format_stringformat_string(String format, Obj... args) - Returns a formatted string from printf-style format strings.> SELECT format_string("Hello World %d %s", 100, "days");
 'Hello World 100 days'
from_unixtimefrom_unixtime(unix_time, format) - Returns unix_time in the specified format> SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
 '1970-01-01 00:00:00'
from_utc_timestamp from_utc_timestamp(timestamp, string timezone) - Assumes given timestamp is UTC and converts to given timezone.No example for from_utc_timestamp.
get_json_objectget_json_object(json_txt, path) - Extract a json object from pathNo example for get_json_object.
greatestgreatest(n1, ...) - Returns the greatest value of all parameters, skipping null values.No example for greatest.
groupingN/A.N/A.
grouping_idN/A.N/A.
hashhash(a1, a2, ...) - Returns a hash value of the arguments.No example for hash.
hexhex(x) - Convert the argument to hexadecimal.> SELECT hex(17);
 '11'
> SELECT hex('Spark SQL');
 '537061726B2053514C'
hourhour(param) - Returns the hour component of the string/timestamp/interval.> SELECT hour('2009-07-30 12:58:59');
 12
hypothypot(a, b) - Returns sqrt(a**2 + b**2).> SELECT hypot(3, 4);
 5.0
if if(expr1,expr2,expr3) - If expr1 is TRUE then IF() returns expr2; otherwise it returns expr3.No example for if.
ifnull ifnull(a,b) - Returns b if a is null, or a otherwise.No example for ifnull.
inexpr in(val1, val2, ...) - Returns true if expr equals to any valN.No example for in.
initcapinitcap(str) - Returns str with the first letter of each word in uppercase.
 All other letters are in lowercase. Words are delimited by white space.		> SELECT initcap('sPark sql');
 'Spark Sql'
inlineinline(a) - Explodes an array of structs into a table.> SELECT inline(array(struct(1, 'a'), struct(2, 'b')));
 [1,a]
 [2,b]
input_file_nameinput_file_name() - Returns the name of the current file being read if available> SELECT input_file_name();
 ''
instrinstr(str, substr) - Returns the (1-based) index of the first occurrence of substr in str.> SELECT instr('SparkSQL', 'SQL');
 6
isnanisnan(a) - Returns true if a is NaN and false otherwise.No example for isnan.
isnotnullisnotnull(a) - Returns true if a is not NULL and false otherwise.No example for isnotnull.
isnullisnull(a) - Returns true if a is NULL and false otherwise.No example for isnull.
json_tuplejson_tuple(jsonStr, p1, p2, ..., pn) - like get_json_object, but it takes multiple names and return a tuple. All the input parameters and output column types are string.No example for json_tuple.
kurtosiskurtosis(x) - Returns the Kurtosis value calculated from values of a group.No example for kurtosis.
laglag(input, offset, default) - LAG returns the value of 'x' at 'offset' rows
 before the current row in the window		No example for lag.
lastlast(expr,isIgnoreNull) - Returns the last value of `child` for a group of rows.No example for last.
last_daylast_day(date) - Returns the last day of the month which the date belongs to.> SELECT last_day('2009-01-12');
 '2009-01-31'
last_valuelast_value(expr,isIgnoreNull) - Returns the last value of `child` for a group of rows.No example for last_value.
lcaselcase(str) - Returns str with all characters changed to lowercase> SELECT lcase('SparkSql');
 'sparksql'
leadlead(input, offset, default) - LEAD returns the value of 'x' at 'offset' rows
 after the current row in the window		No example for lead.
leastleast(n1, ...) - Returns the least value of all parameters, skipping null values.No example for least.
length length(str | binary) - Returns the length of str or number of bytes in binary data.> SELECT length('Spark SQL');
 9
levenshteinlevenshtein(str1, str2) - Returns the Levenshtein distance between the two given strings.> SELECT levenshtein('kitten', 'sitting');
 3
likestr like pattern - Returns true if str matches pattern and false otherwise.No example for like.
lnln(x) - Returns the natural logarithm of x with base e.> SELECT ln(1);
 0.0
locatelocate(substr, str[, pos]) - Returns the position of the first occurrence of substr in str after position pos.
 The given pos and return value are 1-based.		> SELECT locate('bar', 'foobarbar', 5);
 7
loglog(b, x) - Returns the logarithm of x with base b.> SELECT log(10, 100);
 2.0
log10log10(x) - Returns the logarithm of x with base 10.> SELECT log10(10);
 1.0
log1plog1p(x) - Returns log(1 + x).> SELECT log1p(0);
 0.0
log2log2(x) - Returns the logarithm of x with base 2.> SELECT log2(2);
 1.0
lowerlower(str) - Returns str with all characters changed to lowercase> SELECT lower('SparkSql');
 'sparksql'
lpadlpad(str, len, pad) - Returns str, left-padded with pad to a length of len.
 If str is longer than len, the return value is shortened to len characters.		> SELECT lpad('hi', 5, '??');
 '???hi'
> SELECT lpad('hi', 1, '??');
 'h'
ltrim ltrim(str) - Removes the leading space characters from str.> SELECT ltrim(' SparkSQL ');
 'SparkSQL '
mapmap(key0, value0, key1, value1...) - Creates a map with the given key/value pairs.No example for map.
map_keysmap_keys(map) - Returns an unordered array containing the keys of the map.> SELECT map_keys(map(1, 'a', 2, 'b'));
 [1,2]
map_valuesmap_values(map) - Returns an unordered array containing the values of the map. > SELECT map_values(map(1, 'a', 2, 'b'));
 ["a","b"]
maxmax(expr) - Returns the maximum value of expr.No example for max.
md5md5(input) - Returns an MD5 128-bit checksum as a hex string of the input> SELECT md5('Spark');
 '8cde774d6f7333752ed72cacddb05126'
meanmean(x) - Returns the mean calculated from values of a group.No example for mean.
minmin(expr) - Returns the minimum value of expr.No example for min.
minuteminute(param) - Returns the minute component of the string/timestamp/interval.> SELECT minute('2009-07-30 12:58:59');
 58
monotonically_increasing_idmonotonically_increasing_id() - Returns monotonically increasing 64-bit integers.
 The generated ID is guaranteed to be monotonically increasing and unique, but not consecutive.
 The current implementation puts the partition ID in the upper 31 bits, and the lower 33 bits
 represent the record number within each partition. The assumption is that the data frame has
 less than 1 billion partitions, and each partition has less than 8 billion records.		> SELECT monotonically_increasing_id();
 0
monthmonth(param) - Returns the month component of the date/timestamp/interval> SELECT month('2016-07-30');
 7
months_betweenmonths_between(date1, date2) - returns number of months between dates date1 and date2.> SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
 3.94959677
named_structnamed_struct(name1, val1, name2, val2, ...) - Creates a struct with the given field names and values.No example for named_struct.
nanvlnanvl(a,b) - Returns a iff it's not NaN, or b otherwise.No example for nanvl.
negativenegative(a) - Returns -a.No example for negative.
next_day next_day(start_date, day_of_week) - Returns the first date which is later than start_date and named as indicated.> SELECT next_day('2015-01-14', 'TU');
 '2015-01-20'
notnot a - Logical notNo example for not.
nownow() - Returns the current timestamp at the start of query evaluation.>select now();1521529756611
ntilentile(x) - The NTILE(n) function divides the rows for each window partition
 into 'n' buckets ranging from 1 to at most 'n'.		No example for ntile.
nullifnullif(a,b) - Returns null if a equals to b, or a otherwise.No example for nullif.
nvlnvl(a,b) - Returns b if a is null, or a otherwise.No example for nvl.
nvl2nvl2(a,b,c) - Returns b if a is not null, or c otherwise.No example for nvl2.
ora or b - Logical OR.No example for or.
parse_urlparse_url(url, partToExtract[, key]) - extracts a part from a URLParts: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO.
 Key specifies which query to extract.  Examples:
 > SELECT parse_url('http://spark.apache.org/path?query=1', 'HOST')
 'spark.apache.org'
 > SELECT parse_url('http://spark.apache.org/path?query=1', 'QUERY')
 'query=1'
 > SELECT parse_url('http://spark.apache.org/path?query=1', 'QUERY', 'query')
 '1'
percent_rankpercent_rank() - PERCENT_RANK() The PercentRank function computes the percentage
 ranking of a value in a group of values.		No example for percent_rank.
pipi() - Returns PI.> SELECT pi();
 3.141592653589793
pmodpmod(a, b) - Returns the positive modulo> SELECT pmod(10,3);
 1
posexplodeposexplode(a) - Separates the elements of array a into multiple rows with positions, or the elements of a map into multiple rows and columns with positions.
Extended Usage:
> SELECT posexplode(array(10,20));
 0 10
 1 20		> SELECT posexplode(array(10,20));
 0 10
 1 20
positivepositive(a) - Returns a.No example for positive.
powpow(x1, x2) - Raise x1 to the power of x2.> SELECT pow(2, 3);
 8.0
powerpower(x1, x2) - Raise x1 to the power of x2.> SELECT power(2, 3);
 8.0
printfprintf(String format, Obj... args) - Returns a formatted string from printf-style format strings.> SELECT printf("Hello World %d %s", 100, "days");
 'Hello World 100 days'
quarterquarter(param) - Returns the quarter of the year for date, in the range 1 to 4.No example for quarter.
radiansradians(x) - Converts degrees to radians.> SELECT radians(180);
 3.141592653589793
randrand(a) - Returns a random column with i.i.d. uniformly distributed values in [0, 1).No example for rand.
randnrandn(a) - Returns a random column with i.i.d. gaussian random distribution.No example for randn.
rankrank() - RANK() computes the rank of a value in a group of values. The result
 is one plus the number of rows preceding or equal to the current row in the
 ordering of the partition. Tie values will produce gaps in the sequence.		No example for rank.
regexp_extract regexp_extract(str, regexp[, idx]) - extracts a group that matches regexp.> SELECT regexp_extract('100-200', '(\d+)-(\d+)', 1);
 '100'
regexp_replaceregexp_replace(str, regexp, rep) - replace all substrings of str that match regexp with rep.> SELECT regexp_replace('100-200', '(\d+)', 'num');
 'num-num'
repeatrepeat(str, n) - Returns the string which repeat the given string value n times.> SELECT repeat('123', 2);
 '123123'
reversereverse(str) - Returns the reversed given string.> SELECT reverse('Spark SQL');
 'LQS krapS'
rint> SELECT rint(12.3456, 1);
 12.3		rint(x, d) - Return the rounded x at d decimal places.
rlike str rlike regexp - Returns true if str matches regexp and false otherwise.No example for rlike.
rollupN/A.N/A.
round round(x, d) - Round x to d decimal places using HALF_UP rounding mode.> SELECT round(2.5, 0);
 3.0
row_numberrow_number() - The ROW_NUMBER() function assigns a unique, sequential number to
 each row, starting with one, according to the ordering of rows within
 the window partition.		No example for row_number.
rpadrpad(str, len, pad) - Returns str, right-padded with pad to a length of len.
 If str is longer than len, the return value is shortened to len characters.		> SELECT rpad('hi', 5, '??');
 'hi???'
> SELECT rpad('hi', 1, '??');
 'h'
rtrimrtrim(str) - Removes the trailing space characters from str.> SELECT rtrim(' SparkSQL ');
 ' SparkSQL'
secondsecond(param) - Returns the second component of the string/timestamp/interval.> SELECT second('2009-07-30 12:58:59');
 59
sentencessentences(str[, lang, country]) - Splits str into an array of array of words.> SELECT sentences('Hi there! Good morning.');
 [['Hi','there'], ['Good','morning']]
shasha(input) - Returns a sha1 hash value as a hex string of the input> SELECT sha('Spark');
 '85f5955f4b27a9a4c2aab6ffe5d7189fc298b92c'
sha1sha1(input) - Returns a sha1 hash value as a hex string of the input> SELECT sha1('Spark');
 '85f5955f4b27a9a4c2aab6ffe5d7189fc298b92c'
sha2sha2(input, bitLength) - Returns a checksum of SHA-2 family as a hex string of the input.
 SHA-224, SHA-256, SHA-384, and SHA-512 are supported. Bit length of 0 is equivalent to 256.		> SELECT sha2('Spark', 0);
 '529bc3b07127ecb7e53a4dcf1991d9152c24537d919178022b2c42657f79a26b'
shiftleftshiftleft(a, b) - Bitwise left shift.> SELECT shiftleft(2, 1);
 4
shiftrightshiftright(a, b) - Bitwise right shift.> SELECT shiftright(4, 1);
 2
shiftrightunsignedshiftrightunsigned(a, b) - Bitwise unsigned right shift.> SELECT shiftrightunsigned(4, 1);
 2
sign sign(x) - Returns the sign of x.> SELECT sign(40);
 1.0
signum signum(x) - Returns the sign of x.> SELECT signum(40);
 1.0
sinsin(x) - Returns the sine of x.> SELECT sin(0);
 0.0
sinhsinh(x) - Returns the hyperbolic sine of x.> SELECT sinh(0);
 0.0
size size(expr) - Returns the size of an array or a map. > SELECT size(array('b', 'd', 'c', 'a'));
 4
skewnessskewness(x) - Returns the Skewness value calculated from values of a group.No example for skewness.
sort_arraysort_array(array(obj1, obj2, ...), ascendingOrder) - Sorts the input array in ascending order according to the natural ordering of the array elements.
Extended Usage:
 > SELECT sort_array(array('b', 'd', 'c', 'a'), true);
 'a', 'b', 'c', 'd'		> SELECT sort_array(array('b', 'd', 'c', 'a'), true);
 'a', 'b', 'c', 'd'
soundexsoundex(str) - Returns soundex code of the string.> SELECT soundex('Miller');
 'M460'
spacespace(n) - Returns a n spaces string.> SELECT space(2);
 ' '
spark_partition_idspark_partition_id() - Returns the current partition id of the Spark task> SELECT spark_partition_id();
 0
splitsplit(str, regex) - Splits str around occurrences that match regex> SELECT split('oneAtwoBthreeC', '[ABC]');
 ['one', 'two', 'three']
sqrtsqrt(x) - Returns the square root of x.> SELECT sqrt(4);
 2.0
stackstack(n, v1, ..., vk) - Separate v1, ..., vk into n rows.> SELECT stack(2, 1, 2, 3);
 [1,2]
 [3,null]
stdstd(x) - Returns the sample standard deviation calculated from values of a group.No example for std.
stddevstddev(x) - Returns the sample standard deviation calculated from values of a group.No example for stddev.
stddev_popstddev_pop(x) - Returns the population standard deviation calculated from values of a group.No example for stddev_pop.
stddev_sampstddev_samp(x) - Returns the sample standard deviation calculated from values of a group.No example for stddev_samp.
structstruct(col1, col2, col3, ...) - Creates a struct with the given field values.No example for struct.
substrsubstr(str, pos[, len]) - Returns the substring of str that starts at pos and is of length len or the slice of byte array that starts at pos and is of length len.> SELECT substr('Spark SQL', 5);
 'k SQL'
> SELECT substr('Spark SQL', -3);
 'SQL'
> SELECT substr('Spark SQL', 5, 1);
 'k'
substringsubstring(str, pos[, len]) - Returns the substring of str that starts at pos and is of length len or the slice of byte array that starts at pos and is of length len.> SELECT substring('Spark SQL', 5);
 'k SQL'
> SELECT substring('Spark SQL', -3);
 'SQL'
> SELECT substring('Spark SQL', 5, 1);
 'k'
substring_indexsubstring_index(str, delim, count) - Returns the substring from str before count occurrences of the delimiter delim.
 If count is positive, everything to the left of the final delimiter (counting from the
 left) is returned. If count is negative, everything to the right of the final delimiter
 (counting from the right) is returned. Substring_index performs a case-sensitive match
 when searching for delim.		SELECT substring_index('www.apache.org', '.', 2);
 'www.apache'
sumsum(x) - Returns the sum calculated from values of a group.No example for sum.
tantan(x) - Returns the tangent of x.> SELECT tan(0);
 0.0
tanhtanh(x) - Returns the hyperbolic tangent of x.> SELECT tanh(0);
 0.0
to_dateto_date(expr) - Extracts the date part of the date or datetime expression expr.> SELECT to_date('2009-07-30 04:17:52');
 '2009-07-30'
to_unix_timestampto_unix_timestamp(date[, pattern]) - Returns the UNIX timestamp of the give time.No example for to_unix_timestamp.
to_utc_timestampto_utc_timestamp(timestamp, string timezone) - Assumes given timestamp is in given timezone and converts to UTC.to_utc_timestamp(timestamp, string timezone) - Assumes given timestamp is in given timezone and converts to UTC.
translatetranslate(input, from, to) - Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string> SELECT translate('AaBbCc', 'abc', '123');
 'A1B2C3'
trimtrim(str) - Removes the leading and trailing space characters from str.> SELECT trim(' SparkSQL ');
 'SparkSQL'
trunctrunc(date, fmt) - Returns returns date with the time portion of the day truncated to the unit specified by the format model fmt.> SELECT trunc('2009-02-12', 'MM')
 '2009-02-01'
> SELECT trunc('2015-10-27', 'YEAR');
 '2015-01-01'
ucaseucase(str) - Returns str with all characters changed to uppercase> SELECT ucase('SparkSql');
 'SPARKSQL'
unbase64unbase64(str) - Convert the argument from a base 64 string to binary.No example for unbase64.
unhexunhex(x) - Converts hexadecimal argument to binary.> SELECT decode(unhex('537061726B2053514C'),'UTF-8');
 'Spark SQL'
unix_timestampunix_timestamp([date[, pattern]]) - Returns the UNIX timestamp of current or specified time.No example for unix_timestamp.
upperupper(str) - Returns str with all characters changed to uppercase> SELECT upper('SparkSql');
 'SPARKSQL'
var_popvar_pop(x) - Returns the population variance calculated from values of a group.No example for var_pop.
var_samp var_samp(x) - Returns the sample variance calculated from values of a group.No example for var_samp.
variancevariance(x) - Returns the sample variance calculated from values of a group.No example for variance.
weekofyearweekofyear(param) - Returns the week of the year of the given date.> SELECT weekofyear('2008-02-20');
 8
when CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END - When a = true, returns b; when c = true, return d; else return e.No example for when.
windowN/A.N/A.
xpathxpath(xml, xpath) - Returns a string array of values within xml nodes that match the xpath expression> SELECT xpath('<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>','a/b/text()');
['b1','b2','b3']
xpath_booleanxpath_boolean(xml, xpath) - Evaluates a boolean xpath expression.> SELECT xpath_boolean('<a><b>1</b></a>','a/b');
true
xpath_double xpath_double(xml, xpath) - Returns a double value that matches the xpath expression> SELECT xpath_double('<a><b>1</b><b>2</b></a>','sum(a/b)');
3.0
xpath_floatxpath_float(xml, xpath) - Returns a float value that matches the xpath expression> SELECT xpath_float('<a><b>1</b><b>2</b></a>','sum(a/b)');
3.0
xpath_int xpath_int(xml, xpath) - Returns an integer value that matches the xpath expression> SELECT xpath_int('<a><b>1</b><b>2</b></a>','sum(a/b)');
3
xpath_longxpath_long(xml, xpath) - Returns a long value that matches the xpath expression> SELECT xpath_long('<a><b>1</b><b>2</b></a>','sum(a/b)');
3
xpath_numberxpath_number(xml, xpath) - Returns a double value that matches the xpath expression> SELECT xpath_number('<a><b>1</b><b>2</b></a>','sum(a/b)');
3.0
xpath_shortxpath_short(xml, xpath) - Returns a short value that matches the xpath expression> SELECT xpath_short('<a><b>1</b><b>2</b></a>','sum(a/b)');
3
xpath_stringxpath_string(xml, xpath) - Returns the text contents of the first xml node that matches the xpath expression> SELECT xpath_string('<a><b>b</b><c>cc</c></a>','a/c');
cc
yearyear(param) - Returns the year component of the date/timestamp/interval.> SELECT year('2016-07-30');
 2016
|a | b - Bitwise OR.> SELECT 3 | 5; 7
~~ b - Bitwise NOT.> SELECT ~ 0; -1

卡奥斯道
关注
  • 0
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive 函数大全
xiaolichaoren的博客
08-29 1231
一、关系运算： 等值比较: = 语法：A=B 操作类型：所有基本类型 描述: 如果表达式A与表达式B相等，则为TRUE；否则为FALSE 举例： hive> select 1 from lxw_dual where 1=1; 1 不等值比较: <> 语法: A <> B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE 举例： hive> sel
hive函数大全
abcdef_12a的博客
02-13 2232
一、数学函数 1.1 基本运算 1.round(double a[,d]) 保留d位小数，默认保留整数 2.bround(double a[,d]) 保留d为小数，财务计数法(保留位的数字是单数时，后面是500则进一位，双数时不进位） select bround(2.1651,2); =>2.17 select bround(2.1550,2); =>2.16 select bround(2.1650,2); =>2.16 select broun...
hive-字符串查找函数 instr和locate
小哇
10-22 1万+
找不到都是返回0 字符串查找函数: instr 语法: instr(string str, string substr) 返回值: int 说明:返回字符串 substr 在 str 中首次出现的位置 举例: hive> select instr('abcdf','df') from test; OK 4 字符串查找函数:locate 语法: locate(string substr, string str[, int pos]) 返回值: int 说明:返回字符串 substr 在 str 中.
Hive-locate
Black0629的博客
11-04 8693
locate(string substr, string str[, int pos]) 返回substr在str中的位置，0为不包含。类似instr。 可用于模糊join hive> select locate('uzi','Rnguzi'); OK 4 hive> select locate('uzi','RngUzi'); OK 0 select a.*,b...
【Hive】数据类型（struct类型）、运算符和函数（nvl函数、 case-when函数、 explode函数、 列转行、行转列、自定义函数、 窗口函数）、正则捕获组 （三）
大数据爱好者的博客
05-15 832
在Hive中，提供了非常丰富的运算符和函数，用于对数据进行处理和分析。在Hive中，运算符和函数可以归为一类如果需要查看Hive中所有的函数，可以通过如果想要查看某一个函数的描述，可以使用-- 简略描述-- 详细描述在Hive中，还允许用户自定义函数在Hive中，函数必须结合其他的关键字来构成语句！自定义UDF：需要定义一个类，Hive1.x和Hive2.x继承UDF类，但是Hive3.x，UDF类已经过时，所以需要继承GenericUDF自定义UDTF：需要定义一个类，继承。
【Hive】join中的模糊匹配（locate）
热门推荐
喜东东的博客
06-01 2万+
HIVE不支持非等值连接，但可以通过locate()函数进行功能转换。 locate(string substr, string str[, int pos]) 查找字符串str中的pos位置后字符串substr第一次出现的位置，若为找到，则返回0。 hive&gt; select locate('a','abcd'), locate('b', 'abcd'), locate('f', ...
hive查找字符出现的位置find_in_set和locate 函数
weixin_43332500的博客
05-06 6858
问题场景 查找Face这个标签在image_labels字段中出现的位置。 尝试了2个函数，find_in_set和locate 函数，如下所示 select feed_id,db_time, user_id,image_labels, find_in_set('Face',image_labels) as a, locate('Face',image_labels) as b from gravity.ods_gravity_items_hourly where d_s be
hive函数
aosimth的博客
11-17 581
字符函数 1.concat连接字符函数 作用：用于连接表字段的值 select concat(‘ab’,’cd’,’ed’) ==> abcdef 2.instr 返回字符串中子字符串第一次出现的位置，没有返回0 作用：可以判断字段的值是否包含某个字符等 类似contains 与locate函数效果重复 select instr(‘abcdefcd’,’cd’) ==> 3 3.length 字符串中字符个数 作用：计算字段本身的字符个数 ？？还是字段的值？ select l
hive函数大全.doc
09-23
Hive 函数大全 Hive 是一个基于 Hadoop 的数据仓库工具，提供了许多内置函数来帮助用户快速处理和分析数据。下面是 Hive 中的一些常用函数，可以与实际应用结合使用。 关系运算 关系运算是 Hive 中最基本的操作之...
hive函数参考手册，包含常用的hive内置函数.doc
09-24
Hive 函数是 Hive 中的内置函数，用于对数据进行处理和分析。这些函数可以分为四大类：关系运算符、算术运算符、逻辑运算符和复杂类型函数。 关系运算符用于比较两个值是否相等、不等、或是否包含于某个范围内等。...
12-Hive内置函数
02-17
Hive函数思维导图，便捷整理思路，内置运算符、内置函数、内置聚合函数、内置表、自定义函数、WordCount实例、基站掉线率实例
Hive内置函数速查表.pdf
05-17
HIVE开发中一些常用内置函数的速查工具文件，大数据离线数据仓库方向，希望对大家在工作和学习有所帮助。
Hive的collect_set函数
qq_34341930的博客
01-28 1377
1）创建原数据表 hive (gmall)> drop table if exists stud; create table stud (name string, area string, course string, score int); 2）向原数据表中插入数据 hive (gmall)> insert into table stud values('zhang3',...
hive中字符串查找函数 instr 和 locate
index_test的博客
09-03 6214
可用于优化join or关联匹配情况
hive：常用函数(case when,collect_list/set,lateral view explode)
weixin_41391619的博客
12-06 1089
case when;collect_list/set(行转列);explode(列转行)
Hive函数concat_set详解
weixin_42497376的博客
09-25 736
concat_set函数将接收到的字符串作为输入，并使用逗号作为分隔符将它们连接在一起。同时，它会去除重复的元素。
大数据技术之Hive（三）hive常用函数
five小点心的博客
02-25 2506
hive自带了一些函数，比如max/min等。但是数量有限，自己可以通过UDF来方便的扩展。当hive提供的内置函数无法满足业务需求时，就可以考虑用户自定义函数UDF根据用户自定义函数类别分为以下三种：UDF一进一出UDAF用户自定义聚合函数，多进一出类似于：count/max/minUDTF用户自定义表生成函数，一进多出。如lateral view explode()
hive按中文拼音排序_0650-6.2.0-通过UDF实现Hive&Impala的中文拼音排序
weixin_39817012的博客
12-01 765
作者：余枫1.问题重现测试环境：1.RedHat7.22.CDH6.2.03.使用root进行操作1.在Hive中创建一个表，并导入数据如下2.对s2字段进行排序select * from hanzi order by s2; 在Hive中：在Impala中：由上面两张图可以看出，在Hive和Impala中排序都失败了，没有按照期望中的中文对应的拼音进行排序。2.问题解决1.想要实现对中文字段的排...
hive内置函数头歌
最新发布
05-29
Hive内置函数是一组可以在Hive SQL中直接调用的函数，用于处理数据。以下是Hive内置函数的分类及介绍： 1. 聚合函数：用于对数据进行汇总计算，例如SUM、COUNT、AVG等； 2. 字符串函数：用于对字符串类型的数据进行操作，例如CONCAT、SUBSTR、UPPER等； 3. 数学函数：用于对数值类型的数据进行操作，例如ABS、CEIL、FLOOR等； 4. 时间日期函数：用于对日期时间类型的数据进行操作，例如YEAR、MONTH、DAY等； 5. 数组函数：用于对数组类型的数据进行操作，例如SIZE、SORT_ARRAY、ARRAY_CONTAINS等； 6. 映射函数：用于对映射类型的数据进行操作，例如MAP_KEYS、MAP_VALUES、MAP等； 7. 条件函数：用于根据条件进行数据处理，例如CASE、IF、COALESCE等； 8. 其他函数：包括UDF（用户自定义函数）、HASH、RAND等。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值