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