phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Heather <james.heat...@mendeley.com>
Subject Re: Number of regions in SYSTEM.SEQUENCE
Date Tue, 22 Sep 2015 20:14:41 GMT
I don't think it's trying to stop you looking inside the table. I think 
it's complaining that SEQUENCE is a keyword, and shouldn't be appearing 
there.

You could try quoting it.

James

On 22/09/15 21:11, Michael McAllister wrote:
> OK - so the traditional methods of recreating sequences, that makes 
> sense.
>
> Interestingly btw, at least from within Phoenix I can’t see the 
> content of SYSTEM.SEQUENCE. I get the following error:-
>
> 0: jdbc:phoenix:redacted,> select count(*) from system.sequence;
> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting 
> "NAME", got "sequence" at line 1, column 29. (state=42P00,code=604)
>
> I do understand this is a system table, but it would be nice to see 
> inside it. This is from Apache Phoenix 4.2 on HDP 2.2.6.
>
> Michael McAllister
> Staff Data Warehouse Engineer | Decision Systems
> mmcallister@HomeAway.com <mailto:mmcallister@HomeAway.com> | C: 
> 512.423.7447 | skype: michael.mcallister.ha 
> <mailto:zimmkate@hotmail.com> | webex: https://h.a/mikewebex
>
>
> This electronic communication (including any attachment) is 
> confidential.  If you are not an intended recipient of this 
> communication, please be advised that any disclosure, dissemination, 
> distribution, copying or other use of this communication or any 
> attachment is strictly prohibited.  If you have received this 
> communication in error, please notify the sender immediately by reply 
> e-mail and promptly destroy all electronic and printed copies of this 
> communication and any attachment.
>
>> On Sep 22, 2015, at 2:47 PM, James Heather 
>> <james.heather@mendeley.com <mailto:james.heather@mendeley.com>> wrote:
>>
>> If no one else will be hitting the table while you complete the 
>> operation, and if you don't mind about missing a few sequence values 
>> (i.e., having a gap), you should just need the following.
>>
>>     SELECT NEXT VALUE FOR sequencename FROM sometable;
>>
>> That will tell you the next value the sequence wants to hand out.
>>
>>     DROP SEQUENCE sequencename;
>>
>> Then reconnect with the property as given below, and
>>
>>     CREATE SEQUENCE sequencename START WITH n;
>>
>> where n is the value you retrieved in the first step.
>>
>> The reason this might cause gaps is that client connections will 
>> cache sequence values, so the one you retrieve might not actually be 
>> the first one that hasn't been used; it'll just be the first one 
>> cached by the connection you're using. But if you do it this way, and 
>> nothing else is connected in the meantime, then you won't get any 
>> duplicates.
>>
>> As far as I can see, if you're the only connected client, this 
>> *should* do it with no gaps: no other clients will have cached any 
>> sequence values, so you'll retrieve the first one your connection has 
>> cached (which will be the first one available), and then that's where 
>> your sequence will start when you recreate the sequence. But I'm not 
>> absolutely certain about that, and you might want to try some 
>> experiments.
>>
>> If the sequence is being used for a primary key column (a sort of 
>> auto_increment), then the other option is to
>>
>>     SELECT MAX(id) FROM sometable;
>>
>> and then add one to this value to determine where the recreated 
>> sequence should start. That will ensure no gaps.
>>
>> James
>>
>>
>> On 22/09/15 19:47, Michael McAllister wrote:
>>> Mujtaba
>>>
>>> Thanks for this information. Seeing as I am using Phoenix 4.2, what 
>>> is the safe and approved sequence of steps to drop this table and 
>>> recreate it as you mention? Additionally, how do we ensure we don’t 
>>> lose sequence data?
>>>
>>> Michael McAllister
>>> Staff Data Warehouse Engineer | Decision Systems
>>> <mailto:mmcallister@HomeAway.com>mmcallister@HomeAway.com | C: 
>>> 512.423.7447 | skype: michael.mcallister.ha 
>>> <mailto:zimmkate@hotmail.com> | webex: https://h.a/mikewebex
>>>
>>> <Mail Attachment.png>
>>> This electronic communication (including any attachment) is 
>>> confidential.  If you are not an intended recipient of this 
>>> communication, please be advised that any disclosure, dissemination, 
>>> distribution, copying or other use of this communication or any 
>>> attachment is strictly prohibited. If you have received this 
>>> communication in error, please notify the sender immediately by 
>>> reply e-mail and promptly destroy all electronic and printed copies 
>>> of this communication and any attachment.
>>>
>>>> On Sep 22, 2015, at 1:32 PM, Mujtaba Chohan <mujtaba@apache.org 
>>>> <mailto:mujtaba@apache.org>> wrote:
>>>>
>>>> Since Phoenix 4.5.x default has been changed for 
>>>> phoenix.sequence.saltBuckets to not split sequence table. See this 
>>>> <https://git-wip-us.apache.org/repos/asf?p=phoenix.git;a=blobdiff;f=phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java;h=79776e7f688fc700275d0502e31646afe2bbcb1e;hp=4e8879b1b7a6358db2c1f9ccb4fa169394fec721;hb=18e52cc4ce2384bdc7a9c72d63901058e40f04ae;hpb=b82c5cbccdf4eb944238e69a514841be361bfb6d>

>>>> commit. For older versions you can drop sequence table and 
>>>> reconnect with setting client side phoenix.sequence.saltBuckets 
>>>> property.
>>>>
>>>> On Tue, Sep 22, 2015 at 11:14 AM, Michael McAllister 
>>>> <mmcallister@homeaway.com <mailto:mmcallister@homeaway.com>>
wrote:
>>>>
>>>>     Hi
>>>>
>>>>     By default SYSTEM.SEQUENCE is installed with 256 regions. In an
>>>>     environment where you don’t have a large number of tables and
>>>>     regions (yet), the end result of this seems to be that with
>>>>     hbase balance_switch=true, you end up with a lot of region
>>>>     servers with nothing but empty SYSTEM.SEQUENCE regions on them.
>>>>     That mans inefficient use of our cluster.
>>>>
>>>>     Have there been any best practices developed as to how to deal
>>>>     with this situation?
>>>>
>>>>     Michael McAllister
>>>>     Staff Data Warehouse Engineer | Decision Systems
>>>>     mmcallister@HomeAway.com <mailto:mmcallister@HomeAway.com> | C:
>>>>     512.423.7447 <tel:512.423.7447> | skype: michael.mcallister.ha
>>>>     <mailto:zimmkate@hotmail.com> | webex:
>>>>     <https://h.a/mikewebex>https://h.a/mikewebex
>>>>
>>>>     <image002.png>
>>>>     This electronic communication (including any attachment) is
>>>>     confidential.  If you are not an intended recipient of this
>>>>     communication, please be advised that any disclosure,
>>>>     dissemination, distribution, copying or other use of this
>>>>     communication or any attachment is strictly prohibited.  If you
>>>>     have received this communication in error, please notify the
>>>>     sender immediately by reply e-mail and promptly destroy all
>>>>     electronic and printed copies of this communication and any
>>>>     attachment.
>>>>
>>>>
>>>
>>
>


Mime
View raw message