Can we add column to an existing table in AWS Athena using SQL query?

You can find more information about adding columns to table in Athena documentation

Or you can use CTAS

For example, you have a table with

CREATE EXTERNAL TABLE sample_test(
  id string)
LOCATION
  's3://bucket/path'

and you can create another table from sample_test with the query

CREATE TABLE new_test
AS 
SELECT *, 'new' AS new_col FROM sample_test

You can use any available query after AS

This is mainly for future readers like me, who was struggling to get this working for Hive table with AVRO data and if you don't want to create new table i.e updating schema of the existing table. It works for csv using 'add columns', but not for Hive + AVRO. For Hive + AVRO, to append columns at the end, before partition columns, the solution is available at this link. However, there are couple of things to note that, we need to pass full schema to the literal attribute and not just the changes; and (not sure why but) we had to alter hive table for all 3 things in the same order - 1. add columns using add columns 2. set tblproperties and 3. set serdeproperties. Hopefully it helps someone.