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

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

Solution

The semicolon after the case statement is what is throwing the "Only one sql statement is allowed" error.

Remove the semicolon, remove the "set" and adjust/reformat your query like this below:

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