Ways to Access HBase tables

["Martin, Layla (HP DualStudy)" <la...@hp.com>]

Hi,

I would like to make a suggestion regarding the "read from HBase" functionality ("Ways to Access HBase tables" as of page 22 in the SQL Reference Manual). I really like this feature, but I think it should be extended to

a)      return not only row or cell, but also an entire table (with all column families and column qualifiers one can find in that table). I tried to do this, but with only two columns this is quite difficult already (SQL Statement below)

b)      return the same data which was entered when accessing a Trafodion native table. If data type is int, the function will return empty cells or some not readable special characters (I assume that the reason for this is that Trafodion doesn't use the 2-complement with negative numbers?). Thus, I cannot CAST it to int again.

The SQL Statement to transpose the _CELL_ format into relational table format has been: It requires a join against itself and several tests
INSERT INTO MYTABLE
      SELECT CAST(T1.COL_VALUE AS INT), CAST(T2.COL_VALUE AS INT)
      FROM HBASE."_CELL_"."TRAFODION.TRAFODION.MYTABLE" AS T1,
            HBASE."_CELL_"."TRAFODION.TRAFODION.MYTABLE" AS T2
      WHERE
            CAST(T1.ROW_ID AS INT) = CAST(T2.ROW_ID AS INT)
            AND T1.COL_NAME = 'C1'
            AND T2.COL_NAME = 'C2';

Best regards
Layla
Layla Martin
HP DualStudy
Hewlett-Packard GmbH
Telefon +49 7031 4504682
layla.martin@hp.com<ma...@hp.com>
Herrenberger Strasse 140| 71034 Böblingen| www.hp.com/de/dualstudy<http://www.hp.com/de/dualstudy>
[Description: Description: http://intranet.hp.com/country/germany/live/PublishingImages/NeuesLogoSmall.png]
Follow us on: [facebook] <https://www.facebook.com/DualStudy.hp>  [twitter] <http://twitter.com/hpdualstudy>  [youtube] <http://www.youtube.com/user/hpDualStudy>
Geschäftsführer: Heiko Meyer (Vorsitzender), Thomas Bässler, Volkhard Bregulla, Michael Eberhardt, Jochen Erlach, Angelika Gifford, Ernst Reichart
Vorsitzender des Aufsichtsrats: Jörg Menno Harms
Sitz der Gesellschaft: Böblingen, Amtsgericht Stuttgart HRB 244081
WEEE-Reg.-Nr. DE 30409072

RE: Ways to Access HBase tables

[Anoop Sharma <an...@esgyn.com>]

hi



  A native hbase table can contain variable number of cells in each row.
Number, Name

or Datatype of these cellss is not known when a query against this table is
compiled.

That prevents compiling a select statement with relational output of one
entry per column

similar to how it could be done for a trafodion table where the relational
structure is

known during table create.



  One can use column_display function on a “_ROW_” format row. This will
return all

cells of each row in variable format, something that looks like   “cf:1b =>
102” for each cell.



We do have plans to extend native access by mapping a relational structure
to an existing

native hbase table. Once that support is in, one can then do a select

from that mapped relational table. Similar to how phoenix does it.

One cannot add arbitrary columns/cells to the mapped native hbase table as
those columns

will not be visible unless the mapped table is recreated.



You can also use the column_lookup function on a “_ROW_” format and specify
datatype

of the stored value.

For ex:   select column_lookup (column_details, 'cf:3a', cast as int) from
hbase.”_ROW_”.

This will return value of cell “cf:3a” cast in integer format.



anoop



*From:* Martin, Layla (HP DualStudy) [mailto:layla.martin@hp.com]
*Sent:* Wednesday, August 5, 2015 5:09 AM
*To:* dev@trafodion.incubator.apache.org
*Subject:* Ways to Access HBase tables



Hi,



I would like to make a suggestion regarding the “read from HBase”
functionality (“Ways to Access HBase tables” as of page 22 in the SQL
Reference Manual). I really like this feature, but I think it should be
extended to

a)        return not only row or cell, but also an entire table (with all
column families and column qualifiers one can find in that table). I tried
to do this, but with only two columns this is quite difficult already (SQL
Statement below)

b)        return the same data which was entered when accessing a Trafodion
native table. If data type is int, the function will return empty cells or
some not readable special characters (I assume that the reason for this is
that Trafodion doesn’t use the 2-complement with negative numbers?). Thus,
I cannot CAST it to int again.



The SQL Statement to transpose the _*CELL*_ format into relational table
format has been: It requires a join against itself and several tests

INSERT INTO MYTABLE

      SELECT CAST(T1.COL_VALUE AS INT), CAST(T2.COL_VALUE AS INT)

      FROM HBASE."_CELL_"."TRAFODION.TRAFODION.MYTABLE" AS T1,

            HBASE."_CELL_"."TRAFODION.TRAFODION.MYTABLE" AS T2

      WHERE

            CAST(T1.ROW_ID AS INT) = CAST(T2.ROW_ID AS INT)

            AND T1.COL_NAME = 'C1'

            AND T2.COL_NAME = 'C2';



Best regards

Layla

*Layla Martin*
HP DualStudy

Hewlett-Packard GmbH

Telefon +49 7031 4504682

*layla.martin@hp.com <la...@hp.com>*

Herrenberger Strasse 140| 71034 Böblingen| www.hp.com/de/dualstudy

[image: Description: Description:
http://intranet.hp.com/country/germany/live/PublishingImages/NeuesLogoSmall.png]

Follow us on: [image: facebook] <https://www.facebook.com/DualStudy.hp> [image:
twitter] <http://twitter.com/hpdualstudy> [image: youtube]
<http://www.youtube.com/user/hpDualStudy>

Geschäftsführer: Heiko Meyer (Vorsitzender), Thomas Bässler, Volkhard
Bregulla, Michael Eberhardt, Jochen Erlach, Angelika Gifford, Ernst Reichart
Vorsitzender des Aufsichtsrats: Jörg Menno Harms
Sitz der Gesellschaft: Böblingen, Amtsgericht Stuttgart HRB 244081
WEEE-Reg.-Nr. DE 30409072