Binary Tables
Binary tables store data in a flat table structure where the table consists of columns and column values. Every field in a binary table is stored as a sequence of bytes.
Binary tables do not store data type information. You manage the encoding for binary tables when storing data and then convert the sequence of bytes into a specific data type using the CAST or CONVERT functions when you run queries against the tables.
For example, if a string is stored in binary format, such as a UTF-8 encoded string, you must use the CAST function for the query to return a string type. If an integer is stored in binary format, such as 4-byte little endian encoding, you must use the CONVERT function for the query to return the integer value instead of the 4-byte sequence.
Querying a Binary Table
The following examples, from the Querying HBase Tutorial, display query results when the binary table “/user/root/binary/students” with two column families, “account” and “address,” is queried without using a conversion function to convert the binary table data into specific data types.
Example 1
SELECT * FROM `/user/root/binary/students`
students;
+--------------+------------------------------------------------+----------------------------------------------------------------------------+
| row_key | account | address |
+--------------+------------------------------------------------+----------------------------------------------------------------------------+
| [B@78dfa1ed | {"date":"MjAxNi0wMS0xNQ==","name":"QWxpY2U="} | {"state":"Q0E=","street":"MTIzIEJhbGxtZXIgQXY=","zipcode":"MTIzNDU="} |
| [B@22000c9a | {"date":"MjAxNi0wMy0wOA==","name":"Qm9i"} | {"state":"Q0E=","street":"MSBJbmZpbml0ZSBMb29w","zipcode":"MTIzNDU="} |
| [B@313b63e6 | {"date":"MjAxNS0xMi0yMg==","name":"RnJhbms="} | {"state":"Q0E=","street":"NDM1IFdhbGtlciBDdA==","zipcode":"MTIzNDU="} |
| [B@321baa4a | {"date":"MjAxNS0wOS0xNQ==","name":"TWFyeQ=="} | {"state":"Q0E=","street":"NTYgU291dGhlcm4gUGt3eQ==","zipcode":"MTIzNDU="} |
+--------------+------------------------------------------------+----------------------------------------------------------------------------+
4 rows selected (0.612 seconds)
In example 2, using the CONVERT_FROM and CAST functions in a query on the same table converts the binary table data to typed data.
Example 2
SELECT CONVERT_FROM(row_key, 'UTF8') AS studentid, CONVERT_FROM(students.account.name,
'UTF8') AS name, CONVERT_FROM(students.address.state, 'UTF8') AS state,
CONVERT_FROM(students.address.street, 'UTF8') AS street, CONVERT_FROM(students.address.zipcode,
'UTF8') AS zipcode, CAST(students.account.`date` as date) AS `date` FROM
dfs.`/user/root/binary/students`
students;
+------------+--------+--------+-------------------+----------+-------------+
| studentid | name | state | street | zipcode | date |
+------------+--------+--------+-------------------+----------+-------------+
| student1 | Alice | CA | 123 Ballmer Av | 12345 | 2016-01-15 |
| student2 | Bob | CA | 1 Infinite Loop | 12345 | 2016-03-08 |
| student3 | Frank | CA | 435 Walker Ct | 12345 | 2015-12-22 |
| student4 | Mary | CA | 56 Southern Pkwy | 12345 | 2015-09-15 |
+------------+--------+--------+-------------------+----------+-------------+
4 rows selected (0.702 seconds)