You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Tianying Chang <ty...@gmail.com> on 2014/03/22 00:07:03 UTC
How to enable a HBase table to be phoenix-able
Hi,
I have put the phoenix jar under the hbase/lib. and tested that I can
create a new table with sqlline, and do the select * statement.
My question is how can I convert the existing HBase table to run those
select commands? I have a HBase table that already has large amount of data
in it.
Thanks
Tian-Ying
Re: How to enable a HBase table to be phoenix-able
Posted by James Taylor <ja...@apache.org>.
Please start by clicking on the link to the email I indicated and reading
the directions.
Thanks,
James
On Mon, Mar 24, 2014 at 4:33 PM, Tianying Chang <ty...@gmail.com> wrote:
> My version is 2.2.3. I downloaded from
> http://www.carfab.com/apachesoftware/incubator/phoenix/ . Where can I see
> the 3.0 version?
>
> I created and populated a table "TestTable" with HBase's
> PerformanceEvaluation tools. I am able to create a view of TestTable with
> no error reported. But if I do a select * from TestTable; the value are
> all shown as null, but if I do a hbase scan, the val are not null. Does
> this mean view is not created correctly?
>
> *+------------+------------+*
>
> *| ** PK ** | ** VAL ** |*
>
> *+------------+------------+*
>
> *| *0002880099* | *null * |*
>
> *| *0002880100* | *null * |*
>
> *| *0002880101* | *null * |*
>
> *| *0002880102* | *null * |*
>
> *| *0002880103* | *null * |*
>
> *| *0002880104* | *null * |*
>
> *| *0002880105* | *null * |*
>
> *| *0002880106* | *null * |*
>
> *| *0002880107* | *null * |*
>
>
>
> *HBase scan result of one row:*
>
> 0000000025 column=info:data, timestamp=1395695767435,
> value=V+`(\xB4\
>
> xC5@
> \xE5\xAAV\x8FO\xB3\x93\xD8a\x00\xD9\xED\xA3U\xD3\x1B\x
>
> C5\x9E\x9C\xF1@
> \xB2\x1A\x028'\xF2\xB0Xr\x8D\xEE\x99s?\xE9T
>
>
> \xF5\xA18\xFE\xD2\x17\x8D\x1E\x91QDl\xB0\xFDZ{\xB1Y|\x15\x
>
>
> B4\x13\xFF\xE0\xED\x5C\x8E\x13\xCF\x13\xECoJ\x1EK9\xD2\x0C
>
> {lC\x8E\xBB\x09\x88;4\xB1N@
> \xF1\xF5h\xE2fO\xEE\xF0\xA9\xA5
>
>
> U\xDBy\xF4\xA4W\x0AH\x0D\xD1\xA4M1e\xBDR2*\x831\x04\x96_{\
>
> x15\xC6@
> \x9E\xD3\xF9\xC7$\xA5KoI\xA9L4\x906\x05\x15'\xF1\x
>
> CA%\x15\xFD\xA76YE\x99\xE8\x01m\x04a@
> \x0Fz\xB7A\xDE\x04\xA
>
>
> 2\x9C5n\xE5{"\xA2\x97to\x93\x83\xFD\xFC!|\xC5\xF6iD\x09\x8
>
>
> A\x19X\xCD\xA6txF%\xDE\xB9<\xD61\xD9{LY\xD7\xCFk\x88M\xF9\
>
>
> x85(\x91\x94\xEECi\xFFg\xA05w\xB2\xEB\x1B\xAD%B7\xB3Ot:\x1
>
>
> 9R\x85\x96\x8C\xF9\xCF3\x99\xB4\x13\x83\xE2z\x89\xEF1d\xCB
>
>
> \x8Fa\xE0.5\xFA\x8A\x9B8\xF1\x16\xAC-kv\xAA\xE5{\xFF\xA2\x
>
>
> C7\x9C\xE9\xB7\x8E+\xDC\x9D\xFC\x12\xC6l\xD1g\xF3y\xB6\x1D
>
>
> \x85\xD1\xD0\xE1\xD5\xEB~\xDF\x1D\x87y\x02\xBAS\xF3\xFDF*\
>
> x12\x02\xC7\x8F\xBE\x0D4\x8F\x10\xDF@
> \xE3R^K\xD4\x00}\xEC\
>
>
> xB4\x1D\x09S\xB4\x82P9\xA4\xFCg2\x95B\x92\xE5\x93\xC2\xDD;
>
>
> \x9E\xEFe1\x9A\xBF|\x969\xA6\x06\xED\x99P\x12F\xD6\xA83\xE
>
>
> 0l\x0B\x03\xD8\x1C\x18\xC2QO\xB0\xBB\xCB\x1EK\xE3\xC3unM"\
>
>
> xE9\xF0|\x1F\xB1\xCA\x83\xC7\x5C\x0B\xAE\xE7\x92n\xE2\xA9\
>
>
> xD3!X\x1D\xF23\xB9\xB9\xF7\xB5j\x07\xD3\x9B\x801\xD91Z\xFD
>
>
> \xD6\x0CM\xCC\xD3i7\xD9N\x191\xD6\xBE3x\xCE!!U;J\x06\xDC\x
>
>
> A4\xC3\xC9\xB1\x08\xDB\x14\xB6\xB4\xC0\xBC]\xF0\xC3\xD5O\x
>
> 16\xC3\x12m\xD6\xEA
> \xF1\xDB\xB3\x18\xA8o\xDAt\xB9S\x8C\x0
>
>
> 2!pw\x97\x82\xA8\xDE\xDB\xAA\xF2"\x90\xD5Q\xE2\xD1\x9F\xE6
>
>
> \x8F\xF3J18;\x89\xD4\x07\x11\xDEbs|\xA95\xDBN\x19\xEF\x0E\
>
>
> x08.2T\xF4a\x12\xA6\xBF\x86\x84FUA\xB2S\xB2\xB1\xCEU\x8Bt2
>
>
> \xDFx\xA2\xAA\xD3\xCE\x8D\xD4\x1F0H7\xB9\x92\xCB4\x95Y\x0C
>
>
> \xF4K\x19Z\xB1\xEA\x1A5&\x84\x98\xCC\xB9\xC4xX$\xDBCBV"f\x
>
>
> F4+\xD2\xDC\xCDh\x9A\xAEG\xBB\xD6\x1A\x94w\x1B\x1A\x9C\x88
>
> 7os\x01f
> \xB6{5\x92\x0B\xA6\x8C\xB9b]\xEE\x04\xC1\xB1*0u&{
>
>
> \xE1\xBE\xC2\x08\xA7\x8D\x042"B\xA7`d\x85\x8C\x1C\x8C\xCA\
>
>
> xAD,C\xE6\xBEl\xF3>\xE6\xBB\x11\x01\xC4\x9F\xBD\xE7At\xD3:
>
>
> \x8E\x1B\xD9\xEA]\x0C\xDAj\xB9\xFC\x10\xC0\x83\xEA\xCC\x9C
>
>
> e\x8Cs\x8C4\x8B\xB0\xC8\xE8\x01Om\x19)\x1D\xB0~\xDA)\xECh)
>
>
> E^g\xC1'\x0A\xF9Z\xBF\xD9\x0B\xCD\xF9\x9F\x99\xB3\xBD-(J\x
>
>
> A7\xB6c6\x92\xC6\x11|\x14\xB8"\xDF\xBC\xEC~1f\xA8\x10"?)\x
>
>
> 90\xDF\xA5\xA9j\x94$s\xE1\xB0z?\xAB\xF9Q\xAE\xE6\xACcJy\x0
>
> A\x02\x02\xED[7\x1E\x80%\xA4\xB3t&b\x12\xCE\x87\xE7
> \xB0\x
>
>
> B2\x10\xA7s\xF94\xF3\x81\xC1s`n\xDC\xE0O$\x14\xD3I2\x8F\xB
>
> 4\xE4^\xC6B\x02@
> \xE9l\xD9\xFA\xD4\xE6\xB3\xAE\x8F\xA5k\xCB
>
>
> N\xA2\x02\x98\xC8\xB7\xAB\x99~\xF8N\xC6\x9FM}\x99F\x86\xA0
>
>
> \x11l\xF6\x1Fez7\xCAEd\xF3\xB8\x90R\xE8\x15\x9F\xC3}\xE3D\
>
>
> xAD\xBD\x84\x0EQd\xA1\x92\xC1\xD9i\xE3\xC1<\x0D]\xC7\xD6'\
>
>
> x95\xB7\xAB\xDF\x00\xEC`:\xC1\xD1\xE1\x9C"L\x06.\x91\xC8\x
>
>
> 87\x07[\xA5\xC2+/\x91i\xD4\x90\x96\x17T\xEF|\xC0\x05.\xA4\
>
>
> xBEQP\xE5]\xCA\xB5]sd\x09\x9AO*+\x04Pn\xC0\xBB\xE9Gr}\xAA\
>
>
> x96\x17E\x88\x93\x94F\xF1\xA61\x0DW#\x82\x07\xDE\x05\xE5\x
>
>
> D9_?\xDCtuW6\xF5\xD1B\xBA\xE0\xA8\xF4\xC6\xF8\xE0\xCB\x8DI
>
> ~\xD6^\xF3\xA4\xD5
>
>
>
> On Fri, Mar 21, 2014 at 9:06 PM, James Taylor <ja...@apache.org>wrote:
>
>> Hi Tianying,
>> Not sure what version you're using, but I recommend you use the latest
>> release candidate [1]. If you've been playing around with earlier 3.0
>> version, then it's best if you disable and drop the system table through
>> the HBase shell, as it's been through various changes:
>> disable 'SYSTEM.CATALOG'
>> drop 'SYSTEM.CATALOG'
>> Phoenix will automatically create this table when you connect the first
>> time.
>>
>> I tried creating an HBase table in the HBase shell and then creating a
>> view in Phoenix and dropping the view, and it worked fine.
>>
>> From the HBase shell:
>> create 'foo', {NAME => 'f1'}
>> put 'foo', 'r1','f1','value'
>>
>> From the Phoenix sqlline:
>> create view "foo"(k varchar primary key, "f1". "value" varchar);
>> select * from "foo";
>> drop view "foo";
>>
>> Note that when you map a Phoenix table to an existing HBase table, the
>> table name and column family name must match exactly. Otherwise Phoenix
>> will give you an error message, because a view on an existing HBase table
>> is considered read-only.
>>
>> HTH,
>> James
>>
>>
>> [1]
>> http://mail-archives.apache.org/mod_mbox/incubator-phoenix-dev/201403.mbox/%3CCAAF1JdiBkqtR9FkEow-kGd1xsxZ%2B0B1-7NejH%3Duygocx_iM30g%40mail.gmail.com%3E
>>
>>
>> On Fri, Mar 21, 2014 at 5:55 PM, Tianying Chang <ty...@gmail.com>wrote:
>>
>>> I tried to Drop view, it complains undefined, but if I try to create the
>>> view, it will complain already exist. Any idea?
>>>
>>>
>>> 0: jdbc:phoenix:hbasetestzk001a.ec2.pin220.co> CREATE VIEW "TestTable"
>>> ( pk VARCHAR PRIMARY KEY, "info".val VARCHAR );
>>>
>>> Error: ERROR 1013 (42M04): Table already exists. tableName=TestTable
>>> (state=42M04,code=0)
>>>
>>> 0: jdbc:phoenix:hbasetestzk001a.ec2.pin220.co> DROP VIEW "TestTable";
>>>
>>> Error: ERROR 1012 (42M03): Table undefined. tableName=TestTable
>>> (state=42M03,code=1012)
>>>
>>>
>>> On Fri, Mar 21, 2014 at 5:13 PM, Tianying Chang <ty...@gmail.com>wrote:
>>>
>>>> That works. Thanks
>>>>
>>>> Tian-Ying
>>>>
>>>>
>>>> On Fri, Mar 21, 2014 at 5:01 PM, James Taylor <ja...@apache.org>wrote:
>>>>
>>>>> Hi Tianying,
>>>>> Try with our new 3.0 release candidate (
>>>>> http://mail-archives.apache.org/mod_mbox/incubator-phoenix-dev/201403.mbox/%3CCAAF1JdiBkqtR9FkEow-kGd1xsxZ%2B0B1-7NejH%3Duygocx_iM30g%40mail.gmail.com%3E).
>>>>>
>>>>>
>>>>> Follow the examples here:
>>>>> http://phoenix.incubator.apache.org/views.html
>>>>>
>>>>> Please note that Phoenix upper cases table, column, and column family
>>>>> names. So if your HBase table metadata isn't all uppercase, you need to put
>>>>> these names in double quotes as the FAQ outlines.
>>>>>
>>>>> Thanks,
>>>>> James
>>>>>
>>>>>
>>>>> On Fri, Mar 21, 2014 at 4:54 PM, Tianying Chang <ty...@gmail.com>wrote:
>>>>>
>>>>>> Hi, Mujtaba
>>>>>>
>>>>>> Thanks for the quick response.
>>>>>>
>>>>>> I tried that, but got error below
>>>>>>
>>>>>> CREATE VIEW TestTable ( pk VARCHAR PRIMARY KEY, info.val VARCHAR );
>>>>>>
>>>>>> Error: ERROR 505 (42000): Table is read only. (state=42000,code=0)
>>>>>>
>>>>>>
>>>>>> On Fri, Mar 21, 2014 at 4:19 PM, Mujtaba Chohan <mu...@apache.org>wrote:
>>>>>>
>>>>>>> Hi Tian,
>>>>>>>
>>>>>>> Following page describes how you can use existing tables with
>>>>>>> Phoenix
>>>>>>> http://phoenix.incubator.apache.org/faq.html#How_I_create_Views_in_Phoenix_Whats_the_difference_between_ViewsTables
>>>>>>>
>>>>>>> //mujtaba
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Fri, Mar 21, 2014 at 4:07 PM, Tianying Chang <ty...@gmail.com>wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> I have put the phoenix jar under the hbase/lib. and tested that I
>>>>>>>> can create a new table with sqlline, and do the select * statement.
>>>>>>>>
>>>>>>>> My question is how can I convert the existing HBase table to run
>>>>>>>> those select commands? I have a HBase table that already has large amount
>>>>>>>> of data in it.
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>> Tian-Ying
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
Re: How to enable a HBase table to be phoenix-able
Posted by Tianying Chang <ty...@gmail.com>.
My version is 2.2.3. I downloaded from
http://www.carfab.com/apachesoftware/incubator/phoenix/ . Where can I see
the 3.0 version?
I created and populated a table "TestTable" with HBase's
PerformanceEvaluation tools. I am able to create a view of TestTable with
no error reported. But if I do a select * from TestTable; the value are
all shown as null, but if I do a hbase scan, the val are not null. Does
this mean view is not created correctly?
*+------------+------------+*
*| ** PK ** | ** VAL ** |*
*+------------+------------+*
*| *0002880099* | *null * |*
*| *0002880100* | *null * |*
*| *0002880101* | *null * |*
*| *0002880102* | *null * |*
*| *0002880103* | *null * |*
*| *0002880104* | *null * |*
*| *0002880105* | *null * |*
*| *0002880106* | *null * |*
*| *0002880107* | *null * |*
*HBase scan result of one row:*
0000000025 column=info:data, timestamp=1395695767435,
value=V+`(\xB4\
xC5@
\xE5\xAAV\x8FO\xB3\x93\xD8a\x00\xD9\xED\xA3U\xD3\x1B\x
C5\x9E\x9C\xF1@
\xB2\x1A\x028'\xF2\xB0Xr\x8D\xEE\x99s?\xE9T
\xF5\xA18\xFE\xD2\x17\x8D\x1E\x91QDl\xB0\xFDZ{\xB1Y|\x15\x
B4\x13\xFF\xE0\xED\x5C\x8E\x13\xCF\x13\xECoJ\x1EK9\xD2\x0C
{lC\x8E\xBB\x09\x88;4\xB1N@
\xF1\xF5h\xE2fO\xEE\xF0\xA9\xA5
U\xDBy\xF4\xA4W\x0AH\x0D\xD1\xA4M1e\xBDR2*\x831\x04\x96_{\
x15\xC6@
\x9E\xD3\xF9\xC7$\xA5KoI\xA9L4\x906\x05\x15'\xF1\x
CA%\x15\xFD\xA76YE\x99\xE8\x01m\x04a@
\x0Fz\xB7A\xDE\x04\xA
2\x9C5n\xE5{"\xA2\x97to\x93\x83\xFD\xFC!|\xC5\xF6iD\x09\x8
A\x19X\xCD\xA6txF%\xDE\xB9<\xD61\xD9{LY\xD7\xCFk\x88M\xF9\
x85(\x91\x94\xEECi\xFFg\xA05w\xB2\xEB\x1B\xAD%B7\xB3Ot:\x1
9R\x85\x96\x8C\xF9\xCF3\x99\xB4\x13\x83\xE2z\x89\xEF1d\xCB
\x8Fa\xE0.5\xFA\x8A\x9B8\xF1\x16\xAC-kv\xAA\xE5{\xFF\xA2\x
C7\x9C\xE9\xB7\x8E+\xDC\x9D\xFC\x12\xC6l\xD1g\xF3y\xB6\x1D
\x85\xD1\xD0\xE1\xD5\xEB~\xDF\x1D\x87y\x02\xBAS\xF3\xFDF*\
x12\x02\xC7\x8F\xBE\x0D4\x8F\x10\xDF@
\xE3R^K\xD4\x00}\xEC\
xB4\x1D\x09S\xB4\x82P9\xA4\xFCg2\x95B\x92\xE5\x93\xC2\xDD;
\x9E\xEFe1\x9A\xBF|\x969\xA6\x06\xED\x99P\x12F\xD6\xA83\xE
0l\x0B\x03\xD8\x1C\x18\xC2QO\xB0\xBB\xCB\x1EK\xE3\xC3unM"\
xE9\xF0|\x1F\xB1\xCA\x83\xC7\x5C\x0B\xAE\xE7\x92n\xE2\xA9\
xD3!X\x1D\xF23\xB9\xB9\xF7\xB5j\x07\xD3\x9B\x801\xD91Z\xFD
\xD6\x0CM\xCC\xD3i7\xD9N\x191\xD6\xBE3x\xCE!!U;J\x06\xDC\x
A4\xC3\xC9\xB1\x08\xDB\x14\xB6\xB4\xC0\xBC]\xF0\xC3\xD5O\x
16\xC3\x12m\xD6\xEA
\xF1\xDB\xB3\x18\xA8o\xDAt\xB9S\x8C\x0
2!pw\x97\x82\xA8\xDE\xDB\xAA\xF2"\x90\xD5Q\xE2\xD1\x9F\xE6
\x8F\xF3J18;\x89\xD4\x07\x11\xDEbs|\xA95\xDBN\x19\xEF\x0E\
x08.2T\xF4a\x12\xA6\xBF\x86\x84FUA\xB2S\xB2\xB1\xCEU\x8Bt2
\xDFx\xA2\xAA\xD3\xCE\x8D\xD4\x1F0H7\xB9\x92\xCB4\x95Y\x0C
\xF4K\x19Z\xB1\xEA\x1A5&\x84\x98\xCC\xB9\xC4xX$\xDBCBV"f\x
F4+\xD2\xDC\xCDh\x9A\xAEG\xBB\xD6\x1A\x94w\x1B\x1A\x9C\x88
7os\x01f
\xB6{5\x92\x0B\xA6\x8C\xB9b]\xEE\x04\xC1\xB1*0u&{
\xE1\xBE\xC2\x08\xA7\x8D\x042"B\xA7`d\x85\x8C\x1C\x8C\xCA\
xAD,C\xE6\xBEl\xF3>\xE6\xBB\x11\x01\xC4\x9F\xBD\xE7At\xD3:
\x8E\x1B\xD9\xEA]\x0C\xDAj\xB9\xFC\x10\xC0\x83\xEA\xCC\x9C
e\x8Cs\x8C4\x8B\xB0\xC8\xE8\x01Om\x19)\x1D\xB0~\xDA)\xECh)
E^g\xC1'\x0A\xF9Z\xBF\xD9\x0B\xCD\xF9\x9F\x99\xB3\xBD-(J\x
A7\xB6c6\x92\xC6\x11|\x14\xB8"\xDF\xBC\xEC~1f\xA8\x10"?)\x
90\xDF\xA5\xA9j\x94$s\xE1\xB0z?\xAB\xF9Q\xAE\xE6\xACcJy\x0
A\x02\x02\xED[7\x1E\x80%\xA4\xB3t&b\x12\xCE\x87\xE7
\xB0\x
B2\x10\xA7s\xF94\xF3\x81\xC1s`n\xDC\xE0O$\x14\xD3I2\x8F\xB
4\xE4^\xC6B\x02@
\xE9l\xD9\xFA\xD4\xE6\xB3\xAE\x8F\xA5k\xCB
N\xA2\x02\x98\xC8\xB7\xAB\x99~\xF8N\xC6\x9FM}\x99F\x86\xA0
\x11l\xF6\x1Fez7\xCAEd\xF3\xB8\x90R\xE8\x15\x9F\xC3}\xE3D\
xAD\xBD\x84\x0EQd\xA1\x92\xC1\xD9i\xE3\xC1<\x0D]\xC7\xD6'\
x95\xB7\xAB\xDF\x00\xEC`:\xC1\xD1\xE1\x9C"L\x06.\x91\xC8\x
87\x07[\xA5\xC2+/\x91i\xD4\x90\x96\x17T\xEF|\xC0\x05.\xA4\
xBEQP\xE5]\xCA\xB5]sd\x09\x9AO*+\x04Pn\xC0\xBB\xE9Gr}\xAA\
x96\x17E\x88\x93\x94F\xF1\xA61\x0DW#\x82\x07\xDE\x05\xE5\x
D9_?\xDCtuW6\xF5\xD1B\xBA\xE0\xA8\xF4\xC6\xF8\xE0\xCB\x8DI
~\xD6^\xF3\xA4\xD5
On Fri, Mar 21, 2014 at 9:06 PM, James Taylor <ja...@apache.org>wrote:
> Hi Tianying,
> Not sure what version you're using, but I recommend you use the latest
> release candidate [1]. If you've been playing around with earlier 3.0
> version, then it's best if you disable and drop the system table through
> the HBase shell, as it's been through various changes:
> disable 'SYSTEM.CATALOG'
> drop 'SYSTEM.CATALOG'
> Phoenix will automatically create this table when you connect the first
> time.
>
> I tried creating an HBase table in the HBase shell and then creating a
> view in Phoenix and dropping the view, and it worked fine.
>
> From the HBase shell:
> create 'foo', {NAME => 'f1'}
> put 'foo', 'r1','f1','value'
>
> From the Phoenix sqlline:
> create view "foo"(k varchar primary key, "f1". "value" varchar);
> select * from "foo";
> drop view "foo";
>
> Note that when you map a Phoenix table to an existing HBase table, the
> table name and column family name must match exactly. Otherwise Phoenix
> will give you an error message, because a view on an existing HBase table
> is considered read-only.
>
> HTH,
> James
>
>
> [1]
> http://mail-archives.apache.org/mod_mbox/incubator-phoenix-dev/201403.mbox/%3CCAAF1JdiBkqtR9FkEow-kGd1xsxZ%2B0B1-7NejH%3Duygocx_iM30g%40mail.gmail.com%3E
>
>
> On Fri, Mar 21, 2014 at 5:55 PM, Tianying Chang <ty...@gmail.com> wrote:
>
>> I tried to Drop view, it complains undefined, but if I try to create the
>> view, it will complain already exist. Any idea?
>>
>>
>> 0: jdbc:phoenix:hbasetestzk001a.ec2.pin220.co> CREATE VIEW "TestTable"
>> ( pk VARCHAR PRIMARY KEY, "info".val VARCHAR );
>>
>> Error: ERROR 1013 (42M04): Table already exists. tableName=TestTable
>> (state=42M04,code=0)
>>
>> 0: jdbc:phoenix:hbasetestzk001a.ec2.pin220.co> DROP VIEW "TestTable";
>>
>> Error: ERROR 1012 (42M03): Table undefined. tableName=TestTable
>> (state=42M03,code=1012)
>>
>>
>> On Fri, Mar 21, 2014 at 5:13 PM, Tianying Chang <ty...@gmail.com>wrote:
>>
>>> That works. Thanks
>>>
>>> Tian-Ying
>>>
>>>
>>> On Fri, Mar 21, 2014 at 5:01 PM, James Taylor <ja...@apache.org>wrote:
>>>
>>>> Hi Tianying,
>>>> Try with our new 3.0 release candidate (
>>>> http://mail-archives.apache.org/mod_mbox/incubator-phoenix-dev/201403.mbox/%3CCAAF1JdiBkqtR9FkEow-kGd1xsxZ%2B0B1-7NejH%3Duygocx_iM30g%40mail.gmail.com%3E).
>>>>
>>>>
>>>> Follow the examples here:
>>>> http://phoenix.incubator.apache.org/views.html
>>>>
>>>> Please note that Phoenix upper cases table, column, and column family
>>>> names. So if your HBase table metadata isn't all uppercase, you need to put
>>>> these names in double quotes as the FAQ outlines.
>>>>
>>>> Thanks,
>>>> James
>>>>
>>>>
>>>> On Fri, Mar 21, 2014 at 4:54 PM, Tianying Chang <ty...@gmail.com>wrote:
>>>>
>>>>> Hi, Mujtaba
>>>>>
>>>>> Thanks for the quick response.
>>>>>
>>>>> I tried that, but got error below
>>>>>
>>>>> CREATE VIEW TestTable ( pk VARCHAR PRIMARY KEY, info.val VARCHAR );
>>>>>
>>>>> Error: ERROR 505 (42000): Table is read only. (state=42000,code=0)
>>>>>
>>>>>
>>>>> On Fri, Mar 21, 2014 at 4:19 PM, Mujtaba Chohan <mu...@apache.org>wrote:
>>>>>
>>>>>> Hi Tian,
>>>>>>
>>>>>> Following page describes how you can use existing tables with Phoenix
>>>>>> http://phoenix.incubator.apache.org/faq.html#How_I_create_Views_in_Phoenix_Whats_the_difference_between_ViewsTables
>>>>>>
>>>>>> //mujtaba
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Fri, Mar 21, 2014 at 4:07 PM, Tianying Chang <ty...@gmail.com>wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I have put the phoenix jar under the hbase/lib. and tested that I
>>>>>>> can create a new table with sqlline, and do the select * statement.
>>>>>>>
>>>>>>> My question is how can I convert the existing HBase table to run
>>>>>>> those select commands? I have a HBase table that already has large amount
>>>>>>> of data in it.
>>>>>>>
>>>>>>> Thanks
>>>>>>> Tian-Ying
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
Re: How to enable a HBase table to be phoenix-able
Posted by James Taylor <ja...@apache.org>.
Hi Tianying,
Not sure what version you're using, but I recommend you use the latest
release candidate [1]. If you've been playing around with earlier 3.0
version, then it's best if you disable and drop the system table through
the HBase shell, as it's been through various changes:
disable 'SYSTEM.CATALOG'
drop 'SYSTEM.CATALOG'
Phoenix will automatically create this table when you connect the first
time.
I tried creating an HBase table in the HBase shell and then creating a view
in Phoenix and dropping the view, and it worked fine.
>From the HBase shell:
create 'foo', {NAME => 'f1'}
put 'foo', 'r1','f1','value'
>From the Phoenix sqlline:
create view "foo"(k varchar primary key, "f1". "value" varchar);
select * from "foo";
drop view "foo";
Note that when you map a Phoenix table to an existing HBase table, the
table name and column family name must match exactly. Otherwise Phoenix
will give you an error message, because a view on an existing HBase table
is considered read-only.
HTH,
James
[1]
http://mail-archives.apache.org/mod_mbox/incubator-phoenix-dev/201403.mbox/%3CCAAF1JdiBkqtR9FkEow-kGd1xsxZ%2B0B1-7NejH%3Duygocx_iM30g%40mail.gmail.com%3E
On Fri, Mar 21, 2014 at 5:55 PM, Tianying Chang <ty...@gmail.com> wrote:
> I tried to Drop view, it complains undefined, but if I try to create the
> view, it will complain already exist. Any idea?
>
>
> 0: jdbc:phoenix:hbasetestzk001a.ec2.pin220.co> CREATE VIEW "TestTable" (
> pk VARCHAR PRIMARY KEY, "info".val VARCHAR );
>
> Error: ERROR 1013 (42M04): Table already exists. tableName=TestTable
> (state=42M04,code=0)
>
> 0: jdbc:phoenix:hbasetestzk001a.ec2.pin220.co> DROP VIEW "TestTable";
>
> Error: ERROR 1012 (42M03): Table undefined. tableName=TestTable
> (state=42M03,code=1012)
>
>
> On Fri, Mar 21, 2014 at 5:13 PM, Tianying Chang <ty...@gmail.com> wrote:
>
>> That works. Thanks
>>
>> Tian-Ying
>>
>>
>> On Fri, Mar 21, 2014 at 5:01 PM, James Taylor <ja...@apache.org>wrote:
>>
>>> Hi Tianying,
>>> Try with our new 3.0 release candidate (
>>> http://mail-archives.apache.org/mod_mbox/incubator-phoenix-dev/201403.mbox/%3CCAAF1JdiBkqtR9FkEow-kGd1xsxZ%2B0B1-7NejH%3Duygocx_iM30g%40mail.gmail.com%3E).
>>>
>>>
>>> Follow the examples here: http://phoenix.incubator.apache.org/views.html
>>>
>>> Please note that Phoenix upper cases table, column, and column family
>>> names. So if your HBase table metadata isn't all uppercase, you need to put
>>> these names in double quotes as the FAQ outlines.
>>>
>>> Thanks,
>>> James
>>>
>>>
>>> On Fri, Mar 21, 2014 at 4:54 PM, Tianying Chang <ty...@gmail.com>wrote:
>>>
>>>> Hi, Mujtaba
>>>>
>>>> Thanks for the quick response.
>>>>
>>>> I tried that, but got error below
>>>>
>>>> CREATE VIEW TestTable ( pk VARCHAR PRIMARY KEY, info.val VARCHAR );
>>>>
>>>> Error: ERROR 505 (42000): Table is read only. (state=42000,code=0)
>>>>
>>>>
>>>> On Fri, Mar 21, 2014 at 4:19 PM, Mujtaba Chohan <mu...@apache.org>wrote:
>>>>
>>>>> Hi Tian,
>>>>>
>>>>> Following page describes how you can use existing tables with Phoenix
>>>>> http://phoenix.incubator.apache.org/faq.html#How_I_create_Views_in_Phoenix_Whats_the_difference_between_ViewsTables
>>>>>
>>>>> //mujtaba
>>>>>
>>>>>
>>>>>
>>>>> On Fri, Mar 21, 2014 at 4:07 PM, Tianying Chang <ty...@gmail.com>wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I have put the phoenix jar under the hbase/lib. and tested that I can
>>>>>> create a new table with sqlline, and do the select * statement.
>>>>>>
>>>>>> My question is how can I convert the existing HBase table to run
>>>>>> those select commands? I have a HBase table that already has large amount
>>>>>> of data in it.
>>>>>>
>>>>>> Thanks
>>>>>> Tian-Ying
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
Re: How to enable a HBase table to be phoenix-able
Posted by Tianying Chang <ty...@gmail.com>.
I tried to Drop view, it complains undefined, but if I try to create the
view, it will complain already exist. Any idea?
0: jdbc:phoenix:hbasetestzk001a.ec2.pin220.co> CREATE VIEW "TestTable" (
pk VARCHAR PRIMARY KEY, "info".val VARCHAR );
Error: ERROR 1013 (42M04): Table already exists. tableName=TestTable
(state=42M04,code=0)
0: jdbc:phoenix:hbasetestzk001a.ec2.pin220.co> DROP VIEW "TestTable";
Error: ERROR 1012 (42M03): Table undefined. tableName=TestTable
(state=42M03,code=1012)
On Fri, Mar 21, 2014 at 5:13 PM, Tianying Chang <ty...@gmail.com> wrote:
> That works. Thanks
>
> Tian-Ying
>
>
> On Fri, Mar 21, 2014 at 5:01 PM, James Taylor <ja...@apache.org>wrote:
>
>> Hi Tianying,
>> Try with our new 3.0 release candidate (
>> http://mail-archives.apache.org/mod_mbox/incubator-phoenix-dev/201403.mbox/%3CCAAF1JdiBkqtR9FkEow-kGd1xsxZ%2B0B1-7NejH%3Duygocx_iM30g%40mail.gmail.com%3E).
>>
>>
>> Follow the examples here: http://phoenix.incubator.apache.org/views.html
>>
>> Please note that Phoenix upper cases table, column, and column family
>> names. So if your HBase table metadata isn't all uppercase, you need to put
>> these names in double quotes as the FAQ outlines.
>>
>> Thanks,
>> James
>>
>>
>> On Fri, Mar 21, 2014 at 4:54 PM, Tianying Chang <ty...@gmail.com>wrote:
>>
>>> Hi, Mujtaba
>>>
>>> Thanks for the quick response.
>>>
>>> I tried that, but got error below
>>>
>>> CREATE VIEW TestTable ( pk VARCHAR PRIMARY KEY, info.val VARCHAR );
>>>
>>> Error: ERROR 505 (42000): Table is read only. (state=42000,code=0)
>>>
>>>
>>> On Fri, Mar 21, 2014 at 4:19 PM, Mujtaba Chohan <mu...@apache.org>wrote:
>>>
>>>> Hi Tian,
>>>>
>>>> Following page describes how you can use existing tables with Phoenix
>>>> http://phoenix.incubator.apache.org/faq.html#How_I_create_Views_in_Phoenix_Whats_the_difference_between_ViewsTables
>>>>
>>>> //mujtaba
>>>>
>>>>
>>>>
>>>> On Fri, Mar 21, 2014 at 4:07 PM, Tianying Chang <ty...@gmail.com>wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I have put the phoenix jar under the hbase/lib. and tested that I can
>>>>> create a new table with sqlline, and do the select * statement.
>>>>>
>>>>> My question is how can I convert the existing HBase table to run
>>>>> those select commands? I have a HBase table that already has large amount
>>>>> of data in it.
>>>>>
>>>>> Thanks
>>>>> Tian-Ying
>>>>>
>>>>
>>>>
>>>
>>
>
Re: How to enable a HBase table to be phoenix-able
Posted by Tianying Chang <ty...@gmail.com>.
That works. Thanks
Tian-Ying
On Fri, Mar 21, 2014 at 5:01 PM, James Taylor <ja...@apache.org>wrote:
> Hi Tianying,
> Try with our new 3.0 release candidate (
> http://mail-archives.apache.org/mod_mbox/incubator-phoenix-dev/201403.mbox/%3CCAAF1JdiBkqtR9FkEow-kGd1xsxZ%2B0B1-7NejH%3Duygocx_iM30g%40mail.gmail.com%3E).
>
>
> Follow the examples here: http://phoenix.incubator.apache.org/views.html
>
> Please note that Phoenix upper cases table, column, and column family
> names. So if your HBase table metadata isn't all uppercase, you need to put
> these names in double quotes as the FAQ outlines.
>
> Thanks,
> James
>
>
> On Fri, Mar 21, 2014 at 4:54 PM, Tianying Chang <ty...@gmail.com> wrote:
>
>> Hi, Mujtaba
>>
>> Thanks for the quick response.
>>
>> I tried that, but got error below
>>
>> CREATE VIEW TestTable ( pk VARCHAR PRIMARY KEY, info.val VARCHAR );
>>
>> Error: ERROR 505 (42000): Table is read only. (state=42000,code=0)
>>
>>
>> On Fri, Mar 21, 2014 at 4:19 PM, Mujtaba Chohan <mu...@apache.org>wrote:
>>
>>> Hi Tian,
>>>
>>> Following page describes how you can use existing tables with Phoenix
>>> http://phoenix.incubator.apache.org/faq.html#How_I_create_Views_in_Phoenix_Whats_the_difference_between_ViewsTables
>>>
>>> //mujtaba
>>>
>>>
>>>
>>> On Fri, Mar 21, 2014 at 4:07 PM, Tianying Chang <ty...@gmail.com>wrote:
>>>
>>>> Hi,
>>>>
>>>> I have put the phoenix jar under the hbase/lib. and tested that I can
>>>> create a new table with sqlline, and do the select * statement.
>>>>
>>>> My question is how can I convert the existing HBase table to run those
>>>> select commands? I have a HBase table that already has large amount of data
>>>> in it.
>>>>
>>>> Thanks
>>>> Tian-Ying
>>>>
>>>
>>>
>>
>
Re: How to enable a HBase table to be phoenix-able
Posted by James Taylor <ja...@apache.org>.
Hi Tianying,
Try with our new 3.0 release candidate (
http://mail-archives.apache.org/mod_mbox/incubator-phoenix-dev/201403.mbox/%3CCAAF1JdiBkqtR9FkEow-kGd1xsxZ%2B0B1-7NejH%3Duygocx_iM30g%40mail.gmail.com%3E).
Follow the examples here: http://phoenix.incubator.apache.org/views.html
Please note that Phoenix upper cases table, column, and column family
names. So if your HBase table metadata isn't all uppercase, you need to put
these names in double quotes as the FAQ outlines.
Thanks,
James
On Fri, Mar 21, 2014 at 4:54 PM, Tianying Chang <ty...@gmail.com> wrote:
> Hi, Mujtaba
>
> Thanks for the quick response.
>
> I tried that, but got error below
>
> CREATE VIEW TestTable ( pk VARCHAR PRIMARY KEY, info.val VARCHAR );
>
> Error: ERROR 505 (42000): Table is read only. (state=42000,code=0)
>
>
> On Fri, Mar 21, 2014 at 4:19 PM, Mujtaba Chohan <mu...@apache.org>wrote:
>
>> Hi Tian,
>>
>> Following page describes how you can use existing tables with Phoenix
>> http://phoenix.incubator.apache.org/faq.html#How_I_create_Views_in_Phoenix_Whats_the_difference_between_ViewsTables
>>
>> //mujtaba
>>
>>
>>
>> On Fri, Mar 21, 2014 at 4:07 PM, Tianying Chang <ty...@gmail.com>wrote:
>>
>>> Hi,
>>>
>>> I have put the phoenix jar under the hbase/lib. and tested that I can
>>> create a new table with sqlline, and do the select * statement.
>>>
>>> My question is how can I convert the existing HBase table to run those
>>> select commands? I have a HBase table that already has large amount of data
>>> in it.
>>>
>>> Thanks
>>> Tian-Ying
>>>
>>
>>
>
Re: How to enable a HBase table to be phoenix-able
Posted by Tianying Chang <ty...@gmail.com>.
Hi, Mujtaba
Thanks for the quick response.
I tried that, but got error below
CREATE VIEW TestTable ( pk VARCHAR PRIMARY KEY, info.val VARCHAR );
Error: ERROR 505 (42000): Table is read only. (state=42000,code=0)
On Fri, Mar 21, 2014 at 4:19 PM, Mujtaba Chohan <mu...@apache.org> wrote:
> Hi Tian,
>
> Following page describes how you can use existing tables with Phoenix
> http://phoenix.incubator.apache.org/faq.html#How_I_create_Views_in_Phoenix_Whats_the_difference_between_ViewsTables
>
> //mujtaba
>
>
>
> On Fri, Mar 21, 2014 at 4:07 PM, Tianying Chang <ty...@gmail.com> wrote:
>
>> Hi,
>>
>> I have put the phoenix jar under the hbase/lib. and tested that I can
>> create a new table with sqlline, and do the select * statement.
>>
>> My question is how can I convert the existing HBase table to run those
>> select commands? I have a HBase table that already has large amount of data
>> in it.
>>
>> Thanks
>> Tian-Ying
>>
>
>
Re: How to enable a HBase table to be phoenix-able
Posted by Mujtaba Chohan <mu...@apache.org>.
Hi Tian,
Following page describes how you can use existing tables with Phoenix
http://phoenix.incubator.apache.org/faq.html#How_I_create_Views_in_Phoenix_Whats_the_difference_between_ViewsTables
//mujtaba
On Fri, Mar 21, 2014 at 4:07 PM, Tianying Chang <ty...@gmail.com> wrote:
> Hi,
>
> I have put the phoenix jar under the hbase/lib. and tested that I can
> create a new table with sqlline, and do the select * statement.
>
> My question is how can I convert the existing HBase table to run those
> select commands? I have a HBase table that already has large amount of data
> in it.
>
> Thanks
> Tian-Ying
>