I have a requirement to design a table with defined column families but not consistent qualifiers at the beginning. For each records, it might vary.
I gave a try to store the varying qualifiers into a Json object and then traverse through it . But the performance gone down when try to fetch for a specific data from a particular qualifier. And moreover I need to break the query into two, first fetch how many qualifiers I have, based on that I need to pass the actual select query.
Please let me know how can I get this done in HBASE?
The example could be like:
Type of queries I want to achieve are:
1. select count(*) from table where one of the Q= 'REW' (How do I know how many qualifiers are there for each row)
2. select first and last qualifier data for keys in (1231, 321)
You can use dynamic columns feature supported by Phoenix for your use case.
bq. How do I know how many qualifiers are there for each row
Until unless we scan through full row we cannot find the columns. This can be achieved by HBase APIs only.
bq. select first and last qualifier data for keys in (1231, 321)
If we don't know the columns we need to select all the columns and get first and last column values but dynamic columns won't be returned.
You can try extendable views for this use case as well.
At present I am using phoenix to add dynamic column. I don't have any issue in adding them.
If I have to scan the entire table data to find out no. of qualifiers for each row, I need to do it for each individual queries.