Apache Hive
Problem : get complete information about table in Hive
describe extended <table-name>
Example of a hive Table
Problem : Hive default record and field delimiters
\n For text files, each line is a record, so the line feed character separates records.
^A ("control" A) Separates all fields (columns).
Written using the octal code \001 when explicitly specified in CREATE TABLE statements.
^B Separate the elements in an ARRAY or STRUCT, or the key-value pairs in a MAP.
Written using the octal code \002 when explicitly specified in CREATE TABLE statements.
^C Separate the key from the corresponding value in MAP key-value pairs.
Written using the octal code \003 when explicitly specified in CREATE TABLE statements.
Data example : John Doe^A100000.0^AMary Smith^BTodd Jones^AFederal Taxes^C.2^BState Taxes^C.05^BInsurance^C.1^A1 Michigan Ave.^BChicago^BIL^B60600
Example of a hive table on how to override a delimiter
Example Avro
Example to Add new partition : note this does not move the old data
Example of table generating functions
Problem : get complete information about table in Hive
describe extended <table-name>
Example of a hive Table
Problem : Hive default record and field delimiters
\n For text files, each line is a record, so the line feed character separates records.
^A ("control" A) Separates all fields (columns).
Written using the octal code \001 when explicitly specified in CREATE TABLE statements.
^B Separate the elements in an ARRAY or STRUCT, or the key-value pairs in a MAP.
Written using the octal code \002 when explicitly specified in CREATE TABLE statements.
^C Separate the key from the corresponding value in MAP key-value pairs.
Written using the octal code \003 when explicitly specified in CREATE TABLE statements.
Data example : John Doe^A100000.0^AMary Smith^BTodd Jones^AFederal Taxes^C.2^BState Taxes^C.05^BInsurance^C.1^A1 Michigan Ave.^BChicago^BIL^B60600
Example of a hive table on how to override a delimiter
Example Avro
Example to Add new partition : note this does not move the old data
Example of table generating functions
Return type | Signature | Description |
---|---|---|
N rows
| explode(array) |
Return 0 to many rows, one row for each element from the input
array . |
N rows
| explode(map) |
(v0.8.0 and later) Return 0 to many rows, one row for each
map key-value pair, with a field for each map key and a field for the map value. |
tuple
| json_tuple(jsonStr, p1, p2, …, pn) | |
tuple
| parse_url_tuple(url, partname1, partname2, …, partnameN) where N >= 1 |
Extract
N parts from a URL. It takes a URL and the partnames to extract, returning a tuple. All the input parameters and output column types are STRING . The valid partnames are case-sensitive and should only contain a minimum of white space: HOST , PATH , QUERY , REF , PROTOCOL , AUTHORITY , FILE , USERINFO , QUERY:<KEY_NAME> . |
N rows
| stack(n, col1, …, colM) |
Convert
M columns into N rows of size M/N each. |