Athena insert data into new added column-CodePudding

Trying to insert data into a new column I added. Athena does not have an update table command. Is there anyway to do this without reloading the whole table?

I created a test table and then added the column doing this:

ALTER TABLE MikeTest ADD COLUMNS (monthNum int);

I want to update the column with this SQL statement:

month(date_parse("date", '%m/%d/%Y'))

CodePudding user response：

Amazon Athena reads its data from Amazon S3. It is not possible to 'update' a table because this would require re-writing the files in S3.

You could create a new table with the additional column:

CREATE TABLE new_table
WITH (
      external_location = 's3://my_athena_results/folder/',
      format = 'Parquet',
      write_compression = 'SNAPPY'
)
AS
SELECT
  *,
  month(date_parse("date", '%m/%d/%Y')) as month
from old_table

This will copy the data to a new location in S3, while populating the new column