SQL Query Transform | AWS Glue Job
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
- 최신
- 최다 투표
- 가장 많은 댓글
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
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