Bigdata_Spark_Consultant: Hive Expert part 3

Conditional Functions

Return Type	Name(Signature)	Description
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.
boolean	isnull( a )	Returns true if a is NULL and false otherwise.
boolean	isnotnull ( a )	Returns true if a is not NULL and false otherwise.
T	nvl(T value, T default_value)	Returns default value if value is null else returns value (as of HIve 0.11).
T	COALESCE(T v1, T v2, ...)	Returns the first v that is not NULL, or NULL if all v's are NULL.
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	When a = b, returns c; when a = d, returns e; else returns f.
T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	When a = true, returns b; when c = true, returns d; else returns e.
T	nullif( a, b )	Returns NULL if a=b; otherwise returns a (as of Hive 2.3.0). Shorthand for: CASE WHEN a = b then NULL else a
void	assert_true(boolean condition)	Throw an exception if 'condition' is not true, otherwise return null (as of Hive 0.8.0). For example, select assert_true (2<1 td="">

String Functions

The following built-in String functions are supported in Hive:

Return Type

Name(Signature)

Description

int ascii(string str) Returns the numeric value of the first character of str.

string base64(binary bin) Converts the argument from binary to a base 64 string (as of Hive 0.12.0).

int character_length(string str) Returns the number of UTF-8 characters contained in str (as of Hive 2.2.0). The function char_length is shorthand for this function.

string chr(bigint|double A) Returns the ASCII character having the binary equivalent to A (as of Hive 1.3.0 and 2.1.0). If A is larger than 256 the result is equivalent to chr(A % 256). Example: select chr(88); returns "X".

string concat(string|binary A, string|binary B...) Returns the string or bytes resulting from concatenating the strings or bytes passed in as parameters in order. For example, concat('foo', 'bar') results in 'foobar'. Note that this function can take any number of input strings.

array> context_ngrams(array>, array, int K, int pf) Returns the top-k contextual N-grams from a set of tokenized sentences, given a string of "context". See StatisticsAndDataMining for more information.

string concat_ws(string SEP, string A, string B...) Like concat() above, but with custom separator SEP.

string concat_ws(string SEP, array) Like concat_ws() above, but taking an array of strings. (as of Hive 0.9.0)

string decode(binary bin, string charset) Decodes the first argument into a String using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive 0.12.0.)

string elt(N int,str1 string,str2 string,str3 string,...) Return string at index number. For example elt(2,'hello','world') returns 'world'. Returns NULL if N is less than 1 or greater than the number of arguments.
(see https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_elt)

binary encode(string src, string charset) Encodes the first argument into a BINARY using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive 0.12.0.)

int field(val T,val1 T,val2 T,val3 T,...) Returns the index of val in the val1,val2,val3,... list or 0 if not found. For example field('world','say','hello','world') returns 3.
All primitive types are supported, arguments are compared using str.equals(x). If val is NULL, the return value is 0.
(see https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_field)

int find_in_set(string str, string strList) Returns the first occurance of str in strList where strList is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument contains any commas. For example, find_in_set('ab', 'abc,b,ab,c,def') returns 3.

string format_number(number x, int d) Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. (As of Hive 0.10.0; bug with float types fixed in Hive 0.14.0, decimal type support added in Hive 0.14.0)

string get_json_object(string json_string, string path) Extracts json object from a json string based on json path specified, and returns json string of the extracted json object. It will return null if the input json string is invalid. NOTE: The json path can only have the characters [0-9a-z_], i.e., no upper-case or special characters. Also, the keys *cannot start with numbers.* This is due to restrictions on Hive column names.

boolean in_file(string str, string filename) Returns true if the string str appears as an entire line in filename.

int instr(string str, string substr) Returns the position of the first occurrence of substr in str. Returns null if either of the arguments are null and returns 0 if substr could not be found in str. Be aware that this is not zero based. The first character in str has index 1.

int length(string A) Returns the length of the string.

int locate(string substr, string str[, int pos]) Returns the position of the first occurrence of substr in str after position pos.

string lower(string A) lcase(string A) Returns the string resulting from converting all characters of B to lower case. For example, lower('fOoBaR') results in 'foobar'.

string lpad(string str, int len, string 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. In case of empty pad string, the return value is null.

string ltrim(string A) Returns the string resulting from trimming spaces from the beginning(left hand side) of A. For example, ltrim(' foobar ') results in 'foobar '.

array> ngrams(array>, int N, int K, int pf) Returns the top-k N-grams from a set of tokenized sentences, such as those returned by the sentences() UDAF. See StatisticsAndDataMining for more information.

int octet_length(string str) Returns the number of octets required to hold the string str in UTF-8 encoding (since Hive 2.2.0). Note that octet_length(str) can be larger than character_length(str).

string parse_url(string urlString, string partToExtract [, string keyToExtract]) Returns the specified part from the URL. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. For example, parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') returns 'facebook.com'. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, for example, parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') returns 'v1'.

string printf(String format, Obj... args) Returns the input formatted according do printf-style format strings (as of Hive 0.9.0).

string

quote(String text)

Returns the quoted string (Includes escape character for any single quotes HIVE-4.0.0)

Input	Output
NULL	NULL
DONT	'DONT'
DON'T	'DON\'T'

string regexp_extract(string subject, string pattern, int index) Returns the string extracted using the pattern. For example, regexp_extract('foothebar', 'foo(.*?)(bar)', 2) returns 'bar.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc. The 'index' parameter is the Java regex Matcher group() method index. See docs/api/java/util/regex/Matcher.html for more information on the 'index' or Java regex group() method.

string regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For example, regexp_replace("foobar", "oo|ar", "") returns 'fb.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc.

string repeat(string str, int n) Repeats str n times.

string replace(string A, string OLD, string NEW) Returns the string A with all non-overlapping occurrences of OLD replaced with NEW (as of Hive 1.3.0 and 2.1.0). Example: select replace("ababab", "abab", "Z"); returns "Zab".

string reverse(string A) Returns the reversed string.

string rpad(string str, int len, string 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. In case of empty pad string, the return value is null.

string rtrim(string A) Returns the string resulting from trimming spaces from the end(right hand side) of A. For example, rtrim(' foobar ') results in ' foobar'.

array> sentences(string str, string lang, string locale) Tokenizes a string of natural language text into words and sentences, where each sentence is broken at the appropriate sentence boundary and returned as an array of words. The 'lang' and 'locale' are optional arguments. For example, sentences('Hello there! How are you?') returns ( ("Hello", "there"), ("How", "are", "you") ).

string space(int n) Returns a string of n spaces.

array split(string str, string pat) Splits str around pat (pat is a regular expression).

map str_to_map(text[, delimiter1, delimiter2]) Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ',' for delimiter1 and ':' for delimiter2.

string substr(string|binary A, int start) substring(string|binary A, int start) Returns the substring or slice of the byte array of A starting from start position till the end of string A. For example, substr('foobar', 4) results in 'bar' (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr]).

string substr(string|binary A, int start, int len) substring(string|binary A, int start, int len) Returns the substring or slice of the byte array of A starting from start position with length len. For example, substr('foobar', 4, 1) results in 'b' (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr]).

string substring_index(string A, string delim, int count) Returns the substring from string A before count occurrences of the delimiter delim (as of Hive 1.3.0). 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. Example: substring_index('www.apache.org', '.', 2) = 'www.apache'.

string translate(string|char|varchar input, string|char|varchar from, string|char|varchar to) Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string. This is similar to the translate function in PostgreSQL. If any of the parameters to this UDF are NULL, the result is NULL as well. (Available as of Hive 0.10.0, for string types)
Char/varchar support added as of Hive 0.14.0.

string trim(string A) Returns the string resulting from trimming spaces from both ends of A. For example, trim(' foobar ') results in 'foobar'

binary unbase64(string str) Converts the argument from a base 64 string to BINARY. (As of Hive 0.12.0.)

string upper(string A) ucase(string A) Returns the string resulting from converting all characters of A to upper case. For example, upper('fOoBaR') results in 'FOOBAR'.

string initcap(string A) Returns string, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by whitespace. (As of Hive 1.1.0.)

int levenshtein(string A, string B) Returns the Levenshtein distance between two strings (as of Hive 1.2.0). For example, levenshtein('kitten', 'sitting') results in 3.

string soundex(string A) Returns soundex code of the string (as of Hive 1.2.0). For example, soundex('Miller') results in M460.

explode (array)

select explode(array('A','B','C'));

select explode(array('A','B','C')) as col;

select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf;

select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf as col;

col
A
B
C

explode (map)

select explode(map('A',10,'B',20,'C',30));

select explode(map('A',10,'B',20,'C',30)) as (key,value);

select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf;

select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf as key,value;

key	value
key	value
A	10
B	20
C	30

posexplode (array)

select posexplode(array('A','B','C'));

select posexplode(array('A','B','C')) as (pos,val);

select tf.* from (select 0) t lateral view posexplode(array('A','B','C')) tf;

select tf.* from (select 0) t lateral view posexplode(array('A','B','C')) tf as pos,val;

pos	val
0	A
1	B
2	C

inline (array of structs)

select inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02')));

select inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) as (col1,col2,col3);

select tf.* from (select 0) t lateral view inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) tf;

select tf.* from (select 0) t lateral view inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) tf as col1,col2,col3;

col1	col2	col3
A	10	2015-01-01
B	20	2016-02-02

Array myCol
[100,200,300]
[400,500,600]

Then running the query:

SELECT explode(myCol) AS myNewCol FROM myTable;

will produce:

(int) myNewCol
100
200
300
400
500
600

Array myCol
[100,200,300]
[400,500,600]

Then running the query:

SELECT posexplode(myCol) AS pos, myNewCol FROM myTable;

will produce:

(int) pos	(int) myNewCol
1	100
2	200
3	300
1	400
2	500
3	600

Examples:
Non-matching XPath expression:

> select xpath('b1b2','a/*') from src limit 1 ;

[]

Get a list of node text values:

> select xpath('b1b2','a/*/text()') from src limit 1 ;

[b1","b2]

Get a list of values for attribute 'id':

> select xpath('b1b2','//@id') from src limit 1 ;

[foo","bar]

Get a list of node texts for nodes where the 'class' attribute equals 'bb':

> SELECT xpath ('b1b2b3c1c2', 'a/*[@class="bb"]/text()') FROM src LIMIT 1 ;

[b1","c1]

xpath_string

The xpath_string() function returns the text of the first matching node.
Get the text for node 'a/b':

> SELECT xpath_string ('bbcc', 'a/b') FROM src LIMIT 1 ;

bb

Get the text for node 'a'. Because 'a' has children nodes with text, the result is a composite of text from the children.

> SELECT xpath_string ('bbcc', 'a') FROM src LIMIT 1 ;

bbcc

Non-matching expression returns an empty string:

> SELECT xpath_string ('bbcc', 'a/d') FROM src LIMIT 1 ;

Gets the text of the first node that matches '//b':

> SELECT xpath_string ('b1b2', '//b') FROM src LIMIT 1 ;

b1

Gets the second matching node:

> SELECT xpath_string ('b1b2', 'a/b[2]') FROM src LIMIT 1 ;

b2

Gets the text from the first node that has an attribute 'id' with value 'b_2':

> SELECT xpath_string ('b1b2', 'a/b[@id="b_2"]') FROM src LIMIT 1 ;

b2

xpath_boolean

Returns true if the XPath expression evaluates to true, or if a matching node is found.
Match found:

> SELECT xpath_boolean ('b', 'a/b') FROM src LIMIT 1 ;

true

No match found:

> SELECT xpath_boolean ('b', 'a/c') FROM src LIMIT 1 ;

false

Match found:

> SELECT xpath_boolean ('b', 'a/b = "b"') FROM src LIMIT 1 ;

true

No match found:

> SELECT xpath_boolean ('10', 'a/b < 10') FROM src LIMIT 1 ;

false

xpath_short, xpath_int, xpath_long

These functions return an integer numeric value, or the value zero if no match is found, or a match is found but the value is non-numeric.
Mathematical operations are supported. In cases where the value overflows the return type, then the maximum value for the type is returned.
No match:

> SELECT xpath_int ('b', 'a = 10') FROM src LIMIT 1 ;

0

Non-numeric match:

> SELECT xpath_int ('this is not a number', 'a') FROM src LIMIT 1 ;

0

> SELECT xpath_int ('this 2 is not a number', 'a') FROM src LIMIT 1 ;

0

Adding values:

> SELECT xpath_int ('1248', 'sum(a/*)') FROM src LIMIT 1 ;

15

> SELECT xpath_int ('1248', 'sum(a/b)') FROM src LIMIT 1 ;

7

> SELECT xpath_int ('1248', 'sum(a/b[@class="odd"])') FROM src LIMIT 1 ;

5

Overflow:

> SELECT xpath_int ('200000000040000000000', 'a/b * a/c') FROM src LIMIT 1 ;

2147483647

xpath_float, xpath_double, xpath_number

Similar to xpath_short, xpath_int and xpath_long but with floating point semantics. Non-matches result in zero. However,
non-numeric matches result in NaN. Note that xpath_number() is an alias for xpath_double().
No match:

> SELECT xpath_double ('b', 'a = 10') FROM src LIMIT 1 ;

0.0

Non-numeric match:

> SELECT xpath_double ('this is not a number', 'a') FROM src LIMIT 1 ;

NaN

A very large number:

SELECT xpath_double ('200000000040000000000', 'a/b * a/c') FROM src LIMIT 1 ;

8.0E19

Join Syntax

Hive supports the following syntax for joining tables:

join_table:

    table_reference [INNER] JOIN table_factor [join_condition]

  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition

  | table_reference LEFT SEMI JOIN table_reference join_condition

  | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)

table_reference:

    table_factor

  | join_table

table_factor:

    tbl_name [alias]

  | table_subquery alias

  | ( table_references )

join_condition:

    ON expression

SELECT *
FROM table1 t1, table2 t2, table3 t3
WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '02535';

SELECT a.* FROM a JOIN b ON (a.id = b.id)

SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)

are valid joins.

More than 2 tables can be joined in the same query e.g.

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

Hive converts joins over multiple tables into a single map/reduce job if for every table the same column is used in the join clauses e.g.

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

is converted into a single map/reduce job as only key1 column for b is involved in the join. On the other hand

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

is converted into two map/reduce jobs because key1 column from b is used in the first join condition and key2 column from b is used in the second one. The first map/reduce job joins a with b and the results are then joined with c in the second map/reduce job.

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

all the three tables are joined in a single map/reduce job and the values for a particular value of the key for tables b and c are buffered in the memory in the reducers. Then for each row retrieved from a, the join is computed with the buffered rows. If the STREAMTABLE hint is omitted, Hive streams the rightmost table in the join.

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

SELECT /*+ MAPJOIN(b) */ a.key, a.value

FROM a JOIN b ON a.key = b.key

set hive.optimize.bucketmapjoin = true

set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

set hive.optimize.bucketmapjoin = true;

set hive.optimize.bucketmapjoin.sortedmerge = true;

set hive.auto.convert.join=true;
select count(*) from
store_sales join time_dim on (ss_sold_time_sk = t_time_sk)

set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;

There is an option to set the big table selection policy using the following configuration:

set hive.auto.convert.sortmerge.join.bigtable.selection.policy 
    = org.apache.hadoop.hive.ql.optimizer.TableSizeBasedBigTableSelectorForAutoSMJ;

SHOW FUNCTIONS;
DESCRIBE FUNCTION ;
DESCRIBE FUNCTION EXTENDED ;
SELECT u.id, actions.date
FROM (
    SELECT av.uid AS uid
    FROM action_video av
    WHERE av.date = '2008-06-03'
    UNION ALL
    SELECT ac.uid AS uid
    FROM action_comment ac
    WHERE ac.date = '2008-06-03'
) actions JOIN users u ON (u.id = actions.uid)

INSERT OVERWRITE TABLE target_table
SELECT name, id, category FROM source_table_1
UNION ALL
SELECT name, id, "Category159" FROM source_table_2

SELECT name, id, cast('2001-01-01' as date) d FROM source_table_1
UNION ALL
SELECT name, id, hiredate as d FROM source_table_2

Lateral View Syntax

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*

fromClause: FROM baseTable (lateralView)*

Example

Consider the following base table named pageAds. It has two columns: pageid (name of the page) and adid_list (an array of ads appearing on the page):

Column name	Column type
pageid	STRING
adid_list	Array

An example table with two rows:

pageid	adid_list
front_page	[1, 2, 3]
contact_page	[3, 4, 5]

and the user would like to count the total number of times an ad appears across all pages.

A lateral view with explode() can be used to convert adid_list into separate rows using the query:

SELECT pageid, adid

FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

The resulting output will be

pageid (string)	adid (int)
"front_page"	1
"front_page"	2
"front_page"	3
"contact_page"	3
"contact_page"	4
"contact_page"	5

Then in order to count the number of times a particular ad appears, count/group by can be used:

SELECT adid, count(1)

FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid

GROUP BY adid;

int adid	count(1)
1	1
2	1
3	2
4	1
5	1

Multiple Lateral Views

A FROM clause can have multiple LATERAL VIEW clauses. Subsequent LATERAL VIEWS can reference columns from any of the tables appearing to the left of the LATERAL VIEW.

For example, the following could be a valid query:

SELECT * FROM exampleTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

LATERAL VIEW clauses are applied in the order that they appear. For example with the following base table:

Array col1	Array col2
[1, 2]	[a", "b", "c"]
[3, 4]	[d", "e", "f"]

The query:

SELECT myCol1, col2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1;

Will produce:

int mycol1	Array col2
1	[a", "b", "c"]
2	[a", "b", "c"]
3	[d", "e", "f"]
4	[d", "e", "f"]

A query that adds an additional LATERAL VIEW:

SELECT myCol1, myCol2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(col2) myTable2 AS myCol2;

Multiple Lateral Views

A FROM clause can have multiple LATERAL VIEW clauses. Subsequent LATERAL VIEWS can reference columns from any of the tables appearing to the left of the LATERAL VIEW.

For example, the following could be a valid query:

SELECT * FROM exampleTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

LATERAL VIEW clauses are applied in the order that they appear. For example with the following base table:

Array col1	Array col2
[1, 2]	[a", "b", "c"]
[3, 4]	[d", "e", "f"]

The query:

SELECT myCol1, col2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1;

Will produce:

int mycol1	Array col2
1	[a", "b", "c"]
2	[a", "b", "c"]
3	[d", "e", "f"]
4	[d", "e", "f"]

A query that adds an additional LATERAL VIEW:

SELECT myCol1, myCol2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(col2) myTable2 AS myCol2;

Will produce:

int myCol1	string myCol2
1	"a"
1	"b"
1	"c"
2	"a"
2	"b"
2	"c"
3	"d"
3	"e"
3	"f"
4	"d"
4	"e"
4	"f"

For example, the following query returns an empty result:

SELEC * FROM src LATERAL VIEW explode(array()) C AS a limit 10;

But with the OUTER keyword

SELECT * FROM src LATERAL VIEW OUTER explode(array()) C AS a limit 10;

it will produce:

238 val_238 NULL
86 val_86 NULL
311 val_311 NULL
27 val_27 NULL
165 val_165 NULL
409 val_409 NULL
255 val_255 NULL
278 val_278 NULL
98 val_98 NULL
...

Subqueries in the FROM Clause

SELECT ... FROM (subquery) name ...

SELECT ... FROM (subquery) AS name ... (Note: Only valid starting with Hive 0.13.0)

Example with simple subquery:

SELECT col

FROM (

SELECT a+b AS col

FROM t1

) t2

Example with subquery containing a UNION ALL:

SELECT t3.col

FROM (

SELECT a+b AS col

FROM t1

UNION ALL

SELECT c+d AS col

FROM t2

) t3

SELECT *

FROM A

WHERE A.a IN (SELECT foo FROM B);

The other supported types are EXISTS and NOT EXISTS subqueries:

SELECT A

FROM T1

WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

Virtual Columns

Hive 0.8.0 provides support for two virtual columns:

One is INPUT__FILE__NAME, which is the input file's name for a mapper task.

the other is BLOCK__OFFSET__INSIDE__FILE, which is the current global file position.

For block compressed file, it is the current block's file offset, which is the current block's first byte's file offset.

Since Hive 0.8.0 the following virtual columns have been added:

ROW__OFFSET__INSIDE__BLOCK

RAW__DATA__SIZE

ROW__ID

GROUPING__ID

It is important to note, that all of the virtual columns listed here cannot be used for any other purpose (i.e. table creation with columns having a virtual column will fail with "SemanticException Error 10328: Invalid column name..")

Simple Examples

select INPUT__FILE__NAME, key, BLOCK__OFFSET__INSIDE__FILE from src;

select key, count(INPUT__FILE__NAME) from src group by key order by key;

select * from src where BLOCK__OFFSET__INSIDE__FILE > 12000 order by key;

Sampling Bucketized Table
table_sample: TABLESAMPLE (BUCKET x OUT OF y [ON colname])

In the following example the 3rd bucket out of the 32 buckets of the table source. 's' is the table alias.

SELECT *
FROM source TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;

So in the above example, if table 'source' was created with 'CLUSTERED BY id INTO 32 BUCKETS'

TABLESAMPLE(BUCKET 3 OUT OF 16 ON id)

Block Sampling
Block sampling is available starting with Hive 0.8. Addressed under JIRA - https://issues.apache.org/jira/browse/HIVE-2121

block_sample: TABLESAMPLE (n PERCENT)

In the following example the input size 0.1% or more will be used for the query.

SELECT *
FROM source TABLESAMPLE(0.1 PERCENT) s;

set hive.sample.seednumber=;

SELECT *
FROM source TABLESAMPLE(100M) s;

SELECT * FROM source TABLESAMPLE(10 ROWS);

Windowing functions
LEAD
The number of rows to lead can optionally be specified. If the number of rows to lead is not specified, the lead is one row.
Returns null when the lead for the current row extends beyond the end of the window.
LAG
The number of rows to lag can optionally be specified. If the number of rows to lag is not specified, the lag is one row.
Returns null when the lag for the current row extends before the beginning of the window.
FIRST_VALUE
This takes at most two parameters. The first parameter is the column for which you want the first value, the second (optional) parameter must be a boolean which is false by default. If set to true it skips null values.
LAST_VALUE
This takes at most two parameters. The first parameter is the column for which you want the last value, the second (optional) parameter must be a boolean which is false by default. If set to true it skips null values.

The OVER clause
OVER with standard aggregates:
COUNT
SUM
MIN
MAX
AVG
OVER with a PARTITION BY statement with one or more partitioning columns of any primitive datatype.
OVER with PARTITION BY and ORDER BY with one or more partitioning and/or ordering columns of any datatype

COUNT(DISTINCT a) OVER (PARTITION BY c)

COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

SELECT rank() OVER (ORDER BY sum(b))
FROM T
GROUP BY a;

Examples
This section provides examples of how to use the Hive QL windowing and analytics functions in SELECT statements. See HIVE-896 for additional examples.

PARTITION BY with one partitioning column, no ORDER BY or window specification
SELECT a, COUNT(b) OVER (PARTITION BY c)
FROM T;
PARTITION BY with two partitioning columns, no ORDER BY or window specification
SELECT a, COUNT(b) OVER (PARTITION BY c, d)
FROM T;
PARTITION BY with one partitioning column, one ORDER BY column, and no window specification
SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d)
FROM T;
PARTITION BY with two partitioning columns, two ORDER BY columns, and no window specification
SELECT a, SUM(b) OVER (PARTITION BY c, d ORDER BY e, f)
FROM T;
PARTITION BY with partitioning, ORDER BY, and window specification
SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM T;
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM T;
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
FROM T;
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM T;

SELECT
a,
COUNT(b) OVER (PARTITION BY c),
SUM(b) OVER (PARTITION BY c)
FROM T;

Aliases can be used as well, with or without the keyword AS:

SELECT
a,
COUNT(b) OVER (PARTITION BY c) AS b_count,
SUM(b) OVER (PARTITION BY c) b_sum
FROM T;
WINDOW clause
SELECT a, SUM(b) OVER w
FROM T
WINDOW w AS (PARTITION BY c ORDER BY d ROWS UNBOUNDED PRECEDING);
LEAD using default 1 row lead and not specifying default value
SELECT a, LEAD(a) OVER (PARTITION BY b ORDER BY C)
FROM T;
LAG specifying a lag of 3 rows and default value of 0
SELECT a, LAG(a, 3, 0) OVER (PARTITION BY b ORDER BY C)
FROM T;
Distinct counting for each partition
SELECT a, COUNT(distinct a) OVER (PARTITION BY b)
FROM T;

EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query

Bigdata_Spark_Consultant

Monday, October 7, 2019

Hive Expert part 3

Conditional Functions

String Functions

explode (array)

explode (map)

posexplode (array)

inline (array of structs)

xpath_string

xpath_boolean

xpath_short, xpath_int, xpath_long

xpath_float, xpath_double, xpath_number

Join Syntax

Lateral View Syntax

Example

Multiple Lateral Views

Multiple Lateral Views

No comments:

Post a Comment

Python Challenges Program

Blog Archive

Labels