Split rowkey in HBASE to composite key in phoenix

[thanaphol <th...@mindterra.com>]

I import data from sql database (has 2 primary key in this case I assume B
and C) to hbase: 

 

bin/sqoop-import --connect jdbc:mysql://sqlserver:3306/dbname --username
user --password pass --query 'SELECT A, B, C FROM table WHERE $CONDITIONS'
--hbase-create-table --hbase-table table --hbase-row-key B,C --column-family
info --num-mappers 1

when I scan data in hbase I got

Row           column:cell

dataB_dataC       Column=info:A, timestamp=bla bla, value dataA

 

I want to query data in my hbase by using phoenix and I use command to
create view like this:

create view "table" (A VARCHAR(16), B VARCHAR(11), C VARCHAR(12) CONSTRAINT
pk PRIMARY KEY (B,C))default_column_family='info';

But when I use "select * from "table"; I got result like this

   A     |         B         | C

dataA | dataB_dataC | null

My question is how can I split row key to column B and C in phoenix table
with _ is separator.

I expected Result like this

   A     |  B      | C

dataA | dataB | dataC

 

Re: Split rowkey in HBASE to composite key in phoenix

[Ravi Kiran <ma...@gmail.com>]

Hi Thanapool,

   Though its easy to do a import of a sql table to hbase tables backed by
phoenix using Sqoop, we do notice issues when the row key of the hbase
table is a set of composite columns. This is because the delimiter used by
Phoenix is different from what Sqoop uses by default.
   We are working on having a smoother integration in place as part of
https://issues.apache.org/jira/browse/PHOENIX-763 .  Until then, you can
try exporting the Database data to a csv and use the bulk load to import
into Hbase.  http://phoenix.apache.org/bulk_dataload.html .
   My two cents.

HTH

Regards
Ravi


On Tue, Jul 29, 2014 at 8:13 PM, thanaphol <th...@mindterra.com>
wrote:

> I import data from sql database (has 2 primary key in this case I assume B
> and C) to hbase:
>
>
>
> bin/sqoop-import --connect jdbc:mysql://sqlserver:3306/dbname --username
> user --password pass --query 'SELECT A, B, C FROM table WHERE $CONDITIONS'
> --hbase-create-table --hbase-table table --hbase-row-key B,C --column-family
> info --num-mappers 1
>
> when I scan data in hbase I got
>
> Row           column:cell
>
> dataB_dataC       Column=info:A, timestamp=bla bla, value dataA
>
>
>
> I want to query data in my hbase by using phoenix and I use command to
> create view like this:
>
> create view "table" (A VARCHAR(16), B VARCHAR(11), C VARCHAR(12)
> CONSTRAINT pk PRIMARY KEY (B,C))default_column_family='info';
>
> But when I use "select * from "table"; I got result like this
>
>    A     |         B         | C
>
> dataA | dataB_dataC | null
>
> *My question is* how can I split row key to column B and C in phoenix
> table with _ is separator.
>
> I expected Result like this
>
>    A     |  B      | C
>
> dataA | dataB | dataC
>
>
>