Creating a primary key in dremio

1

Hi i have data which doesnt naturally have a primary key but i need one.

Any way to obtain this? (i notice there is no ROWNUMBER function or even MD5 by which i could try to generate one).

(edited, there is a row number :))

2

Hey @Giovanni_Tummarello window functions should help here.

3

SELECT *,ROW_NUMBER() OVER () as PK
FROM table

produced a PK column that seems correct i wonder if there is any guarantee that the PKs will be stable over subcessive runs? (i.e. if i dont change the data of course)

1 Like
4

You may want to potentially persist it since there might not be a guaranteed order without sorting. One option is using CREATE TABLE AS.

1 Like
5

cool i didnt know one could write actuall new table :). I read that would make the table readable from all users, is there a plan to secure the scratch space? (at least in the future?)

6

Yeap, this is on our radar. Will reach out as we finalize our plans on this front.

1 Like