KEYVALUE_TUPLE

Splits a string into key-value pairs and returns the values for one or more specified keys.

To extract a value for a single key, use KEYVALUE instead. Use KEYVALUE_TUPLE when you need values for multiple keys.

Syntax

KEYVALUE_TUPLE(str, split1, split2, key1, key2, ..., keyN)

Use KEYVALUE_TUPLE with LATERAL VIEW to project each extracted value into a named column:

SELECT col1, col2, ...
FROM table_name LATERAL VIEW KEYVALUE_TUPLE(str, split1, split2, key1, key2, ...) alias AS col1, col2, ...;

Parameters

Parameter	Required	Type	Description
`str`	Yes	STRING	The string to parse.
`split1`	Yes	STRING	The delimiter that separates key-value pairs from each other (entry delimiter).
`split2`	Yes	STRING	The delimiter that separates a key from its value within each pair (key-value delimiter).
`key1, key2, ..., keyN`	Yes	STRING	One or more keys whose values to return.

Constraint: If any key-value pair produced by splitting on split1 contains more than one occurrence of split2, the result for that pair is undefined.

Return value

Returns one STRING value per key. Returns null when:

split1 or split2 is null.
str or key is null, or no matching key is found.

Examples

LATERAL VIEW with a table

The following example creates a table with key-value encoded user data, then uses LATERAL VIEW KEYVALUE_TUPLE to expand three fields into separate columns. This is equivalent to calling KEYVALUE three times separately.

-- Create a table.
CREATE TABLE mf_user (
  user_id STRING,
  user_info STRING
);

-- Insert data into the table.
INSERT INTO mf_user VALUES
  ('1', 'age:18;genda:f;address:abc'),
  ('2', 'age:20;genda:m;address:bcd');

-- KEYVALUE_TUPLE with LATERAL VIEW: extracts all three fields.
SELECT user_id, age, genda, address
FROM mf_user LATERAL VIEW KEYVALUE_TUPLE(user_info, ';', ':', 'age', 'genda', 'address') ui AS age, genda, address;

-- Equivalent result using KEYVALUE (one function call per key):
SELECT user_id,
  KEYVALUE(user_info, ';', ':', 'age') AS age,
  KEYVALUE(user_info, ';', ':', 'genda') AS genda,
  KEYVALUE(user_info, ';', ':', 'address') AS address
FROM mf_user;

Both queries return:

+----------+-----+-------+---------+
| user_id  | age | genda | address |
+----------+-----+-------+---------+
| 1        | 18  | f     | abc     |
| 2        | 20  | m     | bcd     |
+----------+-----+-------+---------+

Related functions

KEYVALUE_TUPLE is a string function. For other string search and conversion functions, see String functions.