You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Nicolas Paris <ni...@riseup.net> on 2018/09/15 09:54:48 UTC
[feature request] auto-increment field in Hive
Hi
Hive does not provide auto-increment columns (=sequences). Is there any
chance that feature will be provided in the future ?
This is one of the highest limitation in hive data warehousing in
replacement of RDBMS right now.
Thanks,
--
nicolas
Re: [feature request] auto-increment field in Hive
Posted by Nicolas Paris <ni...@riseup.net>.
On Sat, Sep 15, 2018 at 09:19:12PM -0700, Gopal Vijayaraghavan wrote:
> Since we added a sequence + locking in Hive ACID, there's a Surrogate
> Key prototype (for Hive 3.0)
Great. I did not mention I needed an ACID compliant sequence.
> This is not an auto_increment key, but the numbering is forward ordered.
Good enough
Looking forward to use this feature.
Thanks,
--
nicolas
Re: [feature request] auto-increment field in Hive
Posted by Gopal Vijayaraghavan <go...@apache.org>.
Hi,
> It doesn't help if you need concurrent threads writing to a table but we are just using the row_number analytic and a max value subquery to generate sequences on our star schema warehouse.
Yup, you're right the row_number doesn't help with concurrent writes - it doesn't even scale beyond a single cpu core.
Because it is a dense sequence, there's a 1 reducer chokepoint for that OVER() clause and it shows up as a significant performance issue as the total number of rows start to go up.
However, that is good enough for doing it if you are doing it once and for a very small number of rows.
> It has worked pretty well so far. To provide true sequence support would require changes on the hive meta database side as well as locking so nothing has been done on it in a long time
Since we added a sequence + locking in Hive ACID, there's a Surrogate Key prototype (for Hive 3.0) that's built which uses the Txn manager sequence to seed concurrent sequence numbering.
This should work for any format (Parquet, for instance), because all data formats can be made transactional in Hive3 (though update/delete remains to be implemented for Parquet).
Here's a video describing that from one of the recent meetups discussing Hive 3.x (around the 56minute mark).
https://www.youtube.com/watch?v=b_KUVK7Lq14#t=56m
This is not an auto_increment key, but the numbering is forward ordered.
This is mostly useful to turn a complex join type into a simple equi-join (also PK-FK, which CBO can optimize with), when offloading an OLTP system like Oracle into a historical Hive EDW system.
So, if you have something like the same business key with historical information (like a UPC with a different price based on day of sale/store location or say a EUR:USD conversion rate by day), you can generate a surrogate key for each change and turn a query which has a item_id = item_id and sale_date between start_price_date and end_price_date and sale_location = price_state into a simple = query on item_price_sk.
The dimension table does not grow in size due to this transformation, but it acquires a primary key column which is auto-generated on INSERT.
Cheers,
Gopal
Re: [feature request] auto-increment field in Hive
Posted by Nicolas Paris <ni...@riseup.net>.
On Sat, Sep 15, 2018 at 09:38:01PM +0000, Vineet Garg wrote:
> Not exactly sequence but an ability to generate unique numbers (with
> limitation) is under development: https://issues.apache.org/jira/browse/HIVE-20536
unique numbers is sufficient for a sequence. However, the limitation
looks huge:
> 65K tasks and 17M rows in a task
This means the job might fail for very huge table ? (=65k * 17M rows)
--
nicolas
Re: [feature request] auto-increment field in Hive
Posted by Vineet Garg <vg...@hortonworks.com>.
Not exactly sequence but an ability to generate unique numbers (with limitation) is under development: https://issues.apache.org/jira/browse/HIVE-20536
On Sep 15, 2018, at 11:10 AM, Shawn Weeks <sw...@weeksconsulting.us>> wrote:
It doesn't help if you need concurrent threads writing to a table but we are just using the row_number analytic and a max value subquery to generate sequences on our star schema warehouse. It has worked pretty well so far. To provide true sequence support would require changes on the hive meta database side as well as locking so nothing has been done on it in a long time. A simple UDF isn't capable of providing true unique sequence support.
Thanks
Shawn
-----Original Message-----
From: Jörn Franke <jo...@gmail.com>>
Sent: Saturday, September 15, 2018 6:09 AM
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: Re: [feature request] auto-increment field in Hive
If you really need it then you can write an UDF for it.
On 15. Sep 2018, at 11:54, Nicolas Paris <ni...@riseup.net>> wrote:
Hi
Hive does not provide auto-increment columns (=sequences). Is there any
chance that feature will be provided in the future ?
This is one of the highest limitation in hive data warehousing in
replacement of RDBMS right now.
Thanks,
--
nicolas
Re: [feature request] auto-increment field in Hive
Posted by Jörn Franke <jo...@gmail.com>.
He did not say that it is for an acid table...
Then how the state (ie the sequence) is managed within the UDF is another story , but there are various options
> On 15. Sep 2018, at 20:10, Shawn Weeks <sw...@weeksconsulting.us> wrote:
>
> It doesn't help if you need concurrent threads writing to a table but we are just using the row_number analytic and a max value subquery to generate sequences on our star schema warehouse. It has worked pretty well so far. To provide true sequence support would require changes on the hive meta database side as well as locking so nothing has been done on it in a long time. A simple UDF isn't capable of providing true unique sequence support.
>
> Thanks
> Shawn
>
> -----Original Message-----
> From: Jörn Franke <jo...@gmail.com>
> Sent: Saturday, September 15, 2018 6:09 AM
> To: user@hive.apache.org
> Subject: Re: [feature request] auto-increment field in Hive
>
> If you really need it then you can write an UDF for it.
>
>> On 15. Sep 2018, at 11:54, Nicolas Paris <ni...@riseup.net> wrote:
>>
>> Hi
>>
>> Hive does not provide auto-increment columns (=sequences). Is there any
>> chance that feature will be provided in the future ?
>>
>> This is one of the highest limitation in hive data warehousing in
>> replacement of RDBMS right now.
>>
>> Thanks,
>>
>> --
>> nicolas
RE: [feature request] auto-increment field in Hive
Posted by Shawn Weeks <sw...@weeksconsulting.us>.
It doesn't help if you need concurrent threads writing to a table but we are just using the row_number analytic and a max value subquery to generate sequences on our star schema warehouse. It has worked pretty well so far. To provide true sequence support would require changes on the hive meta database side as well as locking so nothing has been done on it in a long time. A simple UDF isn't capable of providing true unique sequence support.
Thanks
Shawn
-----Original Message-----
From: Jörn Franke <jo...@gmail.com>
Sent: Saturday, September 15, 2018 6:09 AM
To: user@hive.apache.org
Subject: Re: [feature request] auto-increment field in Hive
If you really need it then you can write an UDF for it.
> On 15. Sep 2018, at 11:54, Nicolas Paris <ni...@riseup.net> wrote:
>
> Hi
>
> Hive does not provide auto-increment columns (=sequences). Is there any
> chance that feature will be provided in the future ?
>
> This is one of the highest limitation in hive data warehousing in
> replacement of RDBMS right now.
>
> Thanks,
>
> --
> nicolas
Re: [feature request] auto-increment field in Hive
Posted by Jörn Franke <jo...@gmail.com>.
If you really need it then you can write an UDF for it.
> On 15. Sep 2018, at 11:54, Nicolas Paris <ni...@riseup.net> wrote:
>
> Hi
>
> Hive does not provide auto-increment columns (=sequences). Is there any
> chance that feature will be provided in the future ?
>
> This is one of the highest limitation in hive data warehousing in
> replacement of RDBMS right now.
>
> Thanks,
>
> --
> nicolas