Athena

Add columns

ALTER TABLE db.table ADD COLUMNS (column_name string)

Change column type (doesn't work?!)

ALTER TABLE db.table CHANGE column_name column_name timestamp;

List all partitions

SHOW PARTITIONS appsflyer_stream

Special characters

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

Using a substring

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

Between timestamps

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

Null handling

WHERE column IS null

Casting

SUM(CAST(sysvar_sales AS Double)) AS Sales

Literals

true AS picnic_install