How can I add conditions in athena view definition in order to modify column values?

I have a data warehouse in s3 with a bronze, silver and gold layer where the data is saved in delta format. I need to create a gold table from a silver table, and have decided that the best way to do this would be to create a view in athena. The view needs to include a couple of transformation where a column’s values are modified based on another column’s values, and also one transformation where a new column is created and populated based on a regex match of another column.

I have been able to do this in pyspark but am unable to move it to sql statements in athena. For the first transformation I tried this:

CREATE OR REPLACE VIEW "test_view" AS 
SELECT a.* 
set property_name = (CASE 
                      WHEN domain = 'Some.com' 
                        THEN 'properties/4'
                    END);
FROM "silver_d"."silver_table" a

But got the following error: Only one sql statement is allowed.

Does anyone know how I can create a view based on a table and apply these transformations?

Any help would be much appreciated

You need to sign in to view this answers