Create View from Athena Table Using Program

You can use AWS-Glue or AWS-Lambda for this purpose. I'd prefer using AWS-Glue with daily schedule like below Glue Job example but you can design your own solution. AWS-Lambda supports file-upload trigger but not folder-upload trigger so you should use a schedule anyway.

1- Create a delta-table which will keep all table names which have also its own view. But of course it will be empty at the beginning since there is no table-view pair at the beginning. (By the way you could use normal Glue Catalog table instead Delta Table by overwriting at each time. However, Delta provides of using Insert-Update-Delete on S3).

2- Create a sqlContext on your Glue Job.

spark_context = SparkContext()
glue_context = GlueContext(spark_context)
spark_session = glue_context.spark_session
sql_context = SQLContext(spark_session.sparkContext, spark_session)

3- Get all the table names from the database desired like below;

table_names_df = sqlContext.sql("show tables in <database_name>")

4- Get the table names which have its relevant view from the Delta table;

tables_with_view_df = sqlContext.sql("select * from <database_name.delta_table_name>")

5- Left join these tables to find out which tables don't have its own view.

tables_wo_view_df = (table_names_df.join(tables_with_view_df,
              table_names_df.table_name==tables_with_view_df.table_name2,
"leftouter")
.where(col("table_name2").isNull())
.drop("table_name2"))

6- Create the views but you'd better use a loop unlike below (do not forget to convert tables_wo_view_df to a list. You can use rdd.flatMap() for example.

create_views_sql_script = """
CREATE VIEW <{database_name}.{table_name_wo_view}_view> AS
SELECT *
FROM <{database_name}.{table_name_wo_view}>
"""
ath = boto3.client('athena')
ath.start_query_execution(
        QueryString=create_views_sql_script,
        ResultConfiguration={'OutputLocation': 's3://.../'})

7- Finally update the Delta Table with tables_wo_view_df.

Thanks.

The AWS Glue Data Catalog is accessible via API calls.

For example, there are calls to get_tables()(which includes Views) and create_table().

You could create an AWS Lambda function that runs on a schedule. The Lambda function would call the APIs to query the existing tables (including views) and create new views when necessary.