Search code examples
u-sqlazure-servicebus-queues

auto_increment in U-SQL

I am trying to form a new table that contains unique user_id's from existing one. Is it possible to add auto_increment primary key in U-SQL like we can add in MySQL?

Solution

  • To elaborate on David's answer: Unlike MySQL, ADLA/U-SQL is executed in a scale-out shared nothing architecture. Thus there is not an easy way to manage auto-incremental numbers.

    However, there is are some tricks that you can use:

    1. You can use the ROW_NUMBER() function to generate a number per row. You could add that to the MAX you have so far.
    2. Or you could use DateTime.Now.Ticks to get an initial seed (plus some additional offset if you want to make sure you do not have overlapping ranges between different inserts) and the use ROW_NUMBER().
    3. Less recommended is the use of NewGUID(), since that generates a different guid and is not repeatable. Thus if a vertex is retried, it may fail the job due to non-determinism.

    I hope this helps.