Hi Krishnaprasad It is not possible to query a column from HBase whose column name is not a varchar. Drill expects that all the columns or fields be defined as a Varchar. The byte transformation capabilities are currently only available for the data values itself.
Also, as a best practice, names of any variables, properties, etc... should have atleast one leading non-numeric character. You might try having the column name defined in UTF-8 for readability and ease in querying. Hope that helps. Kunal Khatua Engineering (Performance) [image: MapR] <http://www.mapr.com> www.mapr.com On Fri, Jun 17, 2016 at 11:45 AM, Krishnaprasad A S < krishna.pra...@flytxt.com> wrote: > Can anyone help me on this. > Is it possible to query a column from hbase who's column name is an > integer. (In the form of byte[]). > ---------- Forwarded message ---------- > From: "Krishnaprasad A S" <krishna.pra...@flytxt.com> > Date: Jun 17, 2016 7:10 PM > Subject: Re: Hbase SQL > To: <user@drill.apache.org> > Cc: > > Hi Faraaz, > > Thank you for the reply. > > I'm trying these on Drill SQL prompt. > > Actually I can query the data from column family 'M' for the same key and > > the value is a byte[] serialized using avro. > > But my issue is I can't query the specific column from the column family > > 'M'. The column name is '11503' (byte[] for int 11503, not string). > > Please refer to the query output below for more clarity. > > > > 0: jdbc:drill:> select SUBSCRIBER.M from hbase.`SUBSCRIBER` where > > convert_from(byte_substr(row_key, 1, 12), 'bigint_be') = 916124337090; > > +---+ > > | M | > > +---+ > > | > > > {"\u0000\u0000\u0002=":"AN6zAQAAAAEAAACs6sHFqlU=","\u0000\u0000IR":"ANAPABSQvwUAAAAAAAAAAAAAABSQvwUAAAAAAAAAAAAAABSQvwUAAAAAAAAAAAAAAAAAAAAAAAAAgOHs5qZVAIDh7OamVQCA4ezmplUAAA=="} > > | > > +---+ > > 1 row selected (0.632 seconds) > > 0: jdbc:drill:> select SUBSCRIBER.M.`11503` from hbase.`SUBSCRIBER` where > > convert_from(byte_substr(row_key, 1, 12), 'bigint_be') = 916124337090; > > +---------+ > > | EXPR$0 | > > +---------+ > > +---------+ > > No rows selected (0.831 seconds) > > 0: jdbc:drill:> select SUBSCRIBER.M.`11503` from hbase.`SUBSCRIBER` where > > convert_from(row_key, 'UTF8') = 916124337090; > > +---------+ > > | EXPR$0 | > > +---------+ > > +---------+ > > No rows selected (0.662 seconds) > > 0: jdbc:drill:> > > > > > > > > On Fri, Jun 17, 2016 at 6:09 PM, Khurram Faraaz <kfar...@maprtech.com> > > wrote: > > > >> Can you try these two queries ?, and are you trying to execute your > query > >> from Drill's sqlline prompt or from a Java program ? > >> > >> select X.CF.`11503` from hbase.`X**` where > >> convert_from(row_key, 'bigint_be') = 916124337090; > >> > >> and this one > >> > >> select X.CF.`11503` from hbase.`X**` where > >> convert_from(row_key, 'UTF8') = 916124337090; > >> > >> > >> On Fri, Jun 17, 2016 at 5:31 PM, Krishnaprasad A S < > >> krishna.pra...@flytxt.com> wrote: > >> > >> > Hi, > >> > I have a doubt on Hbase storage in Drill. > >> > > >> > My HBase table 'X' has a column family 'CF' and a column name which is > >> an > >> > integer. > >> > we store the data in hbase with column name as bytes array, using the > >> > following code to create the byte[] (as normal in Hbase). > >> > byte[] column = > >> org.apache.hadoop.hbase.util.Bytes*.toBytes(integerValue)* > >> > > >> > if my column name is integer 11503, > >> > then how can I query the data from the specific column. > >> > Currently the following query does not work, it returns zero rows even > >> > though data exists in the column. > >> > *select X.CF.`11503` from hbase.`X**` where > >> > convert_from(byte_substr(row_key, 1, 12), 'bigint_be') = > 916124337090;* > >> > > >> > Any help ? > >> > > >> > -- > >> > Krishnaprasad A S > >> > < > >> > > >> > http://www.linkedin.com/company/22166?goback=%2Efcs_GLHD_flytxt_false_*2_*2_*2_*2_*2_*2_*2_*2_*2_*2_*2_*2&trk=ncsrch_hits > >> > > > >> > > >> > > > > > > > > -- > > Krishnaprasad A S > > Lead Engineer > > Flytxt > > Skype: krishnaprasadas > > M: +91 8907209454 | O: +91 471.3082753 | F: +91 471.2700202 > > www.flytxt.com | Visit our blog <http://blog.flytxt.com/> | Follow us > > <http://www.twitter.com/flytxt> | Connect on LinkedIn > > < > http://www.linkedin.com/company/22166?goback=%2Efcs_GLHD_flytxt_false_*2_*2_*2_*2_*2_*2_*2_*2_*2_*2_*2_*2&trk=ncsrch_hits > > > > >
