SQL Query Transform | AWS Glue Job

Question

I am transforming my table by adding new columns using SQL Query Transform in AWS Glue Job studio.

![visual diagram for transformation](/media/postImages/original/IMwcLXRM0iTROC0Uqb5lvOGg)

SQL aliases- study

Existing Schema from data catalog - study id, patient id, patient age

I want to transform the existing schema by adding new columns.

new columns - AccessionNo

Transformed schema - study id, patient id, patient age, AccessionNo

SQL query - **alter table study add columns (AccessionNo int)**

Error it gives- 
pyspark.sql.utils.AnalysisException: Invalid command: 'study' is a view not a table.; line 2 pos 0;
'AlterTable V2SessionCatalog(spark_catalog), default.study, 'UnresolvedV2Relation [study], V2SessionCatalog(spark_catalog), default.study, [org.apache.spark.sql.connector.catalog.TableChange$AddColumn@1e7cbfec]

I tried looking at AWS official doc for SQL transform and it says queries should be in Spark Sql syntax and my query is also in Spark Sql syntax. 
https://docs.aws.amazon.com/glue/latest/ug/transforms-sql.html

What is the exact issue and please help me resolve.
Thanks

Gonzalo Herreros · Accepted Answer

A DDL like that is mean to alter and actual catalog table, not an immutable view like is "study".  
You also have to add some content to the column (even if it's a NULL placeholder you will fill in later).  
Even better if you can set the value you need here using other columns.  
For instance:  
```
select *, 0 as AccessionNo from study
```

SQL Query Transform | AWS Glue Job

Relevant content