I am transforming my table by adding new columns using SQL Query Transform in AWS Glue Job studio.
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
Pls accept apologies for the late response as I was busy with MVP deadlines. It actually worked. Thanks a lot @Gonzalo Herreros.
So, a follow up question. when a data catalog table is getting transformed with SQL transform, does the catalog table always referenced as a View?
They are separate things, a DDL (ALTER TABLE) updates the table, all the other transformations just exists while you make the query