Adding an identity column while creating athena table

Question

I want to create an athena table from data stored in aws-s3. But in addition I also want an identity column. Since Athena uses presto sql engine, I ran the query written below in Athena but it was giving error code 400 : invalidrequestexception. Athena query written below :-

CREATE EXTERNAL TABLE `db_name`.`preprocessed` (
id bigint unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
`event_action` string
)
PARTITIONED BY (
`platform` string
`dateval` string
)
STORED AS PARQUET
LOCATION <S3-LOCATION>
tblproperties ("parquet.compress"="SNAPPY");

Here S3-LOCATION is the location of my parquet files where data is stored inside aws-s3. Maybe this is because, AUTO_INCREMENT is a problem for Athena since CREATE TABLE query only creates a table plan with the help of s3 data stored in parquet files instead of loading the data from s3. But if there is any way to add an identity column along with the s3 data, please mention the solution. It will be a lot of help for me here.

deterministic means returns the same value always when called with the same arguments. Are you sure you need deterministic function depending on non-unique arguments ? UUID imho would be better in such case — leftjoin
@Tushar Vatsal: can you add a new column into the table having the MD5 / SHA checksum over all colums giving you a column where you can filer distinct values — sudo
You can ORDER BY over all columns. This combined with good partitioning column will still execute in parallel. Something like row_number() over (PARTITION BY some_good_partitioning_columns ORDER BY other_column_1, other_column_2, ..., other_column_150). (To be precise: you don't need to ORDER BY on columns that you PARTITION BY). Also, rank() is a better choice than row_number(). With rank() equal rows will get the same number assigned, so they will remain equal. — Piotr Findeisen
But ordering by 150 columns, do you think is a good idea performance wise — Tushar Vatsal
Can you please post an example of a file name stored in s3? Does it have some kind the common structure with some timestamp or so? — Roberto

DarkRob DarkRob · Accepted Answer · 2019-08-20T09:55:20

So basically you don't have any column for performing order by clause. So you can do this by this way using (select 1) on order by clause.

select row_number() over (partition by col1 order by (select 1)) as Slno....

This will give order by to the default value as they are inserted in table.

Adding an identity column while creating athena table

1 Answers