Rob's Wiki
Search…
Athena

Add columns

1
ALTER TABLE db.table ADD COLUMNS (column_name string)
Copied!

Change column type (doesn't work?!)

1
ALTER TABLE db.table CHANGE column_name column_name timestamp;
Copied!

List all partitions

1
SHOW PARTITIONS appsflyer_stream
Copied!

Special characters

1
SELECT column_name_1, "column-name-2", column_name_3, ##
2
FROM db.table
3
WHERE column_name_3 is not null
4
ORDER BY timestamp_column DESC;
Copied!

Using a substring

1
SELECT event_name, substr(timestring, 1, 10) as datestring, count(##) AS count
2
FROM stream
3
WHERE test_mode != 'true' AND event_name = 'event_a'
4
GROUP BY event_name, substr(timestring, 1, 10)
5
ORDER BY datestring DESC
Copied!

Between timestamps

1
WHERE timestamp BETWEEN timestamp '2017-06-13 15:50:00.000' AND timestamp '2017-06-13 16:52:29.000'
Copied!

Null handling

1
WHERE column IS null
Copied!

Casting

1
SUM(CAST(sysvar_sales AS Double)) AS Sales
Copied!

Literals

1
true AS picnic_install
Copied!
Last modified 3yr ago