Best Rowkey Design for Hbase Table

Question

We have a Hbase Table where they rowkey is prepared by concatenating Site+Article i.e if I have site A which sells 100,200,300 article nos. My rowkeys are A100,A200,A300 respectively. Now we want to scan the hbase table using the article number only. Which can be present in multiple sites. We tried performing a scan using substring comparator. But it takes a long time. Can anyone suggest a better salting or rowkey design for the same scenario.

Answer 1

It doen't seems like this problem can be solved by simple rowkey redesign until you are able to exchange SiteId and ArticleId, but in that case you will have the same problem with searching by SiteId. The reason for such behaviour is that HBase can't optimize a search by middle or last part of keys in anyway and it has to do a full scan.

Some solutions which you might think of:
1. Do several concurrent searches one per each site with condition rowkey == SiteIdArticleId. This would work fast if you have relatively small number of sites.
2. Do a custom secondary index. A second index table with AtricleId as rowkey and SiteIds as sell values.
3. Use Apache Phoenix which can do secondary indexing out of the box. (But check that it fits to need first)

In the second case you are able to perform get by key from index table and than from zero to multiple gets for each cell from the first get. This will work pretty fast, but require some space overhead.

The second option in more details:

Suppose your table colled SiteToArticle and the second table is colled ArticleToSite When you do writes you write to both tables to the first as you usually do and to the second like {"rowkey"=ArticleId, "SiteId"=siteId}

When you do reads, firstly you read from ArticleToSite, then iterate over each SiteId create new get with key SiteId:ArticleId and perform the second batch of gets. Code may look approximately like this:

byte[] articleId = "ArticleId".getBytes();
Get get = new Get(articleId).readAllVersions();
Table t = connection.getTable(TableName.valueOf("ArticleToSite"));

List<Get> gets = new ArrayList<>();
for (Cell c : t.get(get).getColumnCells("CF".getBytes(), "SiteId".getBytes())) {
    byte[] key = Bytes.add(CellUtil.cloneValue(c), ":".getBytes(), articleId);
    gets.add(new Get(key));
}
return connection.getTable(TableName.valueOf("SiteToArticle")).get(gets);