Ian Hellström

• • 2 min

Batch-Updating Non-Key Columns in Apache Phoenix

Apache Phoenix is a SQL skin for HBase, a distributed key-value store. Phoenix offers two flavours of UPSERT, but from that it may not be obvious how to update non-key columns, as you need the whole key to add or modify records.

Primary keys in Phoenix are HBase row keys. So, when you define a compound primary key, the concatenated column values become the row key. Because the row key is the, well, key to accessing individual records, there is no native way to update key columns in Phoenix (or HBase). The only way to achieve a key update is to delete the record and insert a fresh record.

But what about non-key columns? Surely for individual records you can do batch updates, for instance after adding a new column, right? Well, it’s not as easy as in RDBMS-land, where you would do the following:

UPDATE
  tab_name
SET
  col = <expr>
WHERE
  <predicate>
;

Phoenix does not support that syntax. In fact, all you have is UPSERT, which is a MERGE but without the DELETE, which for instance Oracle Database offers.

Don’t fret though, there is a way to update a single column but it’s a bit cumbersome:

UPSERT INTO
  tab_name
(
  pk_col1
, pk_col2
, pk_col3
, col
)
SELECT
  pk_col1
, pk_col2
, pk_col3
, <expr>
FROM
  tab_name
WHERE
  <predicate>
;

Example

Let’s look at a simple example:

CREATE TABLE
  dummy
(
  k1 INTEGER NOT NULL
, k2 INTEGER NOT NULL
, v  VARCHAR
, CONSTRAINT pk PRIMARY KEY (k1, k2)
);

UPSERT INTO dummy VALUES (1, 10, 'foo');
UPSERT INTO dummy VALUES (2, 20, 'bar');
UPSERT INTO dummy VALUES (3, 30, 'baz');

SELECT * FROM dummy;
+----------------+----------------+----------------+
|       K1       |       K2       |       V        |
+----------------+----------------+----------------+
| 1              | 10             | foo            |
| 2              | 20             | bar            |
| 3              | 30             | baz            |
+----------------+----------------+----------------+

UPSERT INTO
  dummy (k1, k2, v)
SELECT
  k1
, k2
, UPPER(v)
FROM
  dummy
WHERE k1 <> 2;

SELECT * FROM dummy;
+----------------+----------------+----------------+
|       K1       |       K2       |       V        |
+----------------+----------------+----------------+
| 1              | 10             | FOO            |
| 2              | 20             | bar            |
| 3              | 30             | BAZ            |
+----------------+----------------+----------------+

Simple.

A Couple of Notes

What’s important when working with HBase and Phoenix is that Phoenix encodes certain data types differently. The reason is that it allows binary comparisons. The consequence is, however, that data entered with Phoenix and read via HBase may require flipping sign bits.

There is currently no simple way to rename tables or columns. What is more, there is no CREATE TABLE AS SELECT statement, which is slightly annoying.

When you use salt buckets to avoid region server hotspotting, there are a few important facts to keep in mind. First, the number of salt buckets must be set when creating the table and it cannot be altered afterwards. Second, the number of salt buckets is recommended to be equal to the number of region servers. It’s in the documentation, but you have to look for it in a note that’s tucked between parentheses, although there are different opinions on the matter too. As always, try it yourself on your own hardware.

The Phoenix/Spark plugin allows you to interact with Phoenix from Spark. It’s fairly easy to use but all the ‘documentation’ available is what you see on the main page of the library. Compared to native Phoenix statements, my experience has been that the number of requests (RPS) per seconds each region server can handle via the plugin is a factor 10 to 20 lower.

Oh, and if you’re on a Phoenix version prior to 4.8.0 and on a cluster with Kerberos, you’re pretty much screwed; the workaround never worked for me.

Finally, when you use SQLLine and the tables are wider than your terminal screen, which will happen pretty much immediately for any realistic table, a neat trick is to use !outputformat vertical. It will output all columns as rows, so you can at least see all the data.

Related posts