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:[email protected]] *Sent:* Wednesday, August 5, 2015 5:09 AM *To:* [email protected] *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 *[email protected] <[email protected]>* 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
