CODEWITHSUNDEEP
amazon-athenapresto
spectre009
spectre009

Reputation: 124

Adding a column with unique values in AWS Athena

So, I am looking for a way to sequence the rows of my athena table. I have already tried:

ROW_NUMBER() OVER ()

But then this leads to Query exhausted resources at this scale factor error. It has to be a unique value for every row, so yes it doesn't need to be an integer. The method should be performance effective if it can be.

Upvotes: 1

Views: 3763

Answers (2)

Rick James
Rick James

Reputation: 142453

Assuming Athena is based on MySQL...

Please provide SHOW CREATE TABLE for the table in question.

If you don't already have an AUTO_INCREMENT column on the table, consider the following.

ALTER TABLE t
    ADD COLUMN id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    ADD INDEX(id);

If you don't already have a PRIMARY KEY on the table, do this instead:

ALTER TABLE t
    ADD COLUMN id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    ADD PRIMARY KEY(id);

Upvotes: 0

Nicolas Busca
Nicolas Busca

Reputation: 1305

You can give each row a unique id, uuid, using the uuid() function. Here are the docs:

https://trino.io/docs/current/functions/uuid.html

Upvotes: 1

Related Questions