Generate a range of values in Dremio

1

Hi there!
I have a question regarding creating a range of values in Dremio. I want to generate a table in a following format(this is just an example).

n
1
2
3
4
…
100

Remark: I want to be able to set the upper and lower border of the range like in generate-series function.
For example:
function(a, b), where a is a lower border and b is an upper border

As far as I know there is no generate_series function available in Dremio, so I am asking the community whether there is a possible workaround for this problem
Thanks)

2

Hi,
perhaps you can create an array and then use:

SELECT FLATTEN(CONVERT_FROM('[0,1,2,3,4]', 'json'));
3

There is no native generate_series functionality in Dremio as of right now.

There is, however, a potential way to get what you want. In my example below, I ran a CTAS query to create a scratch table in one of my s3 sources. As long as you run the row_number() function against a table with at least as many rows as sequential numbers you want (100 in my case), this should work.

CREATE TABLE $scratch.RowTest AS select row_number() over() as rnk from sys.jobs limit 100;

You can even do this with dates if you do something like the following:

CREATE TABLE $scratch.RowTest AS select row_number()over() as rnk, DATE_ADD(DATE ‘2023-01-01’, rnk) as “date” from sys.jobs limit 365;