Search code examples
sqlimpala

generate sequence in impala sql

I'd like to generate in sql some rows of fake data with a sequence of integer (given a max number). The result should be something like that

1
2
3
4
5
...
10

Thank you very much

Solution

  • There is no easier way to generate series but its not too difficult either. You need to use a large table with lots of rows for this.

    SELECT seq FROM (
SELECT
    row_number() OVER (PARTITION BY 'test' ORDER BY 'test') as seq
FROM 
    large_table
)rs
WHERE seq <= 100

    here row_number() - is used to generate row number for each row in large table.
    where - is used to limit rows to 100. You can change this to any number.