create view from athena table using program

I've 100 plus tables in Athena, for example table1, table2 and so on.

Is there any way I could create generic view once I have any table in Athena automatically (using any program or script)?

For example, If I create any table MyTable in Athena, so v_MyTable should be created as view too.

2 Answers

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.