AWS Athena: How to escape reserved word column name and cast to integer-CodePudding

I have a column named 'timestamp' which is a reserved word. I need to select the column then cast it as an integer to perform the query below. I can successfully perform a simple select of the column data. It is only when I try to cast the value as an integer that the error is returned.

I have tried to escape the word using backticks and double quotes as suggested in the AWS Docs without success.

*The query works with another table that does not use a reserved word for the column name. This is what I have attempted:

Query with reserved word un-escaped:

SELECT timestamp
FROM my_table
WHERE from_unixtime(cast(timestamp as integer)) >= date_add('day', -7, now())

Error:

INVALID_CAST_ARGUMENT: Cannot cast '' to INT

Query with backticks:

SELECT `timestamp`
FROM my_table
WHERE from_unixtime(cast(`timestamp` as integer)) >= date_add('day', -7, now())

Error:

Queries of this type are not supported

Query with double quotes:

SELECT "timestamp"
FROM my_table
WHERE from_unixtime(cast("timestamp" as integer)) >= date_add('day', -7, now())

Error:

INVALID_CAST_ARGUMENT: Cannot cast '' to INT

Thanks!

CodePudding user response：

The problem is not your escaping logic, but that your dataset contains empty strings in the timestamp column which can not be casted. You can avoid that, by filtering out the empty string records.

SELECT "timestamp"
FROM (SELECT "timestamp" from my_table where "timestamp" != '')
WHERE from_unixtime(cast("timestamp" as integer)) >= date_add('day', -7, now())

CodePudding user response：

Instead of cast you can use try_cast which returns null in case of unsuccessful cast (empty string can't be turned into integer), which will result in the where condition evaluated to false:

with dataset(col) as (
    values (''),
    ('1659648600')
)

select *
from dataset
where from_unixtime(try_cast(col as integer)) >= date '2022-08-03';

Output:

col
1659648600