This is the sixth article in our series on parsing SQL in different database technologies and SQL dialects. We explored SQL parsing on Snowflake, MS SQL Server, Oracle , Databricks and Redshift in our earlier blog posts. We cover SQL parsing on AWS Athena in this blog post and go through a practical example of table and column audit logging.

We provide practical examples of deconstructing SQL from the AWS Athena query history. Additionally, we will present some code that utilises the FlowHigh SQL parser SDK to programmatically parse SQL from AWS Athena. The parsing of AWS Athena can be automated using the SDK.

In another post on the Sonra blog, I go into great depth on the use cases of using an SQL parser for data engineering and data governance use cases. The mentioned article is a complete deep dive into SQL parsing, how it works and how you can use it for use cases in data engineering and data governance.

SQL parser for AWS Athena

Sonra has created an online SQL parser. Our vision is to be SQL dialect agnostic. The parser also works with AWS Athena. It is called FlowHigh. Our SaaS platform includes a UI for manual SQL parsing as well as an SDK for managing bulk SQL parsing requirements or automating the process. We demonstrate FlowHigh’s capabilities by parsing the query history of AWS Athena.

Let’s look at both options starting with the SDK for automating SQL parsing.

Programmatically parsing the AWS Athena query history with the FlowHigh SDK

AWS Athena provides a powerful API that allows users to interact with its features programmatically. One of the valuable capabilities of this API is the ability to retrieve the history of SQL queries executed in Athena. By using the list_query_executions() method from the Athena client in the Boto3 library, users can obtain a list of query execution IDs. Each of these IDs corresponds to an individual SQL query that was run in Athena. To further obtain the details of each query, including the actual SQL text, users can pass these execution IDs to the get_query_execution() method. This not only returns the SQL text but also provides other metadata such as the query’s execution time, status, and data scanned. When combined, these methods provide a comprehensive view of the SQL query history in Athena, making it easier for users to audit, review, or replicate past queries.

Sample output of api methods.

list_query_executions()

Will return list of query ids execute in the specified workgroup.

get_query_execution()

The function will retrieve the specifics of the query when provided with a query ID as its argument as shown below.

The python code in the next section shows how the query history is pulled from AWS Athena and processed using the FlowHigh SDK:

Analysing the output of the FlowHigh SQL parser

The FlowHigh SQL parser for AWS Athena processes incoming SQL queries and outputs the results in either JSON or XML format. For example, from our collected query history, the parser generates a comprehensive JSON or XML representation of the SQL query. This detailed output includes data on filter criteria, retrieved columns, aliases used, join conditions, tables, and other clauses of the SQL command.

Let’s go through an example. We will use a simple SQL query to demonstrate some of the features of the parser.

Let’s examine the XML output from the FlowHigh SQL parser for the given SQL query. This XML format is more concise than its JSON cousin. Both types of messages have the same content though.

The XML output generated by the FlowHigh SQL parser for AWS Athena provides an in-depth analysis of the SQL statement.

Tables and columns

From the XML structure we see that tables weblogs and users from the schema weblogs_db are referenced in the SQL query. The columns involved from these tables are URL, response_code, user_id from the weblogs table, and username, user_id from the users table. The name of the schemas, tables, and columns can be found in the xml from the FH parser.

Joins

There is an inner join identified between the weblogs table (with alias w) and the users table (with alias u). The join condition dictates that the user_id from the weblogs table matches the user_id from the users table.

You can find the JOIN in the below section of the XML output.

Internally the column user_id from T1 and user_id from T2 is referenced as C4 and C5 , which can be looked up from the query hierarchy <DBOHier> at the end of the XML message.

GROUP BY

The XML indicates an aggregation based on the username column from the users table and the URL column from the weblogs table.

You can find the GROUP BY in the aggregation section of the XML output

Internally the column username is referenced as C1 and column URL is referenced as C2, which can be looked up from the query hierarchy <DBOHier> at the end of the XML message.

FILTER

The query incorporates a filter condition on the response_code column from the weblogs table. Specifically, it selects records where the response_code value is ‘200’. This can be found in the filter section of the XML output:

Internally the column response_code is referenced as C3, which can be looked up from the query hierarchy <DBOHier> at the end of the XML message.

ORDER BY

The results of the query are sorted in descending order based on the count of response_code column from the weblogs table. This ordering ensures that the records with a specific response code appear at the top of the output. This can be found in the sort section of the XML output:

While AWS Athena may not always provide granular details about specific tables and columns in a query, FlowHigh supplements this information. It not only identifies the tables and columns but also zeroes in on the columns involved in join operations.

FlowHigh User Interface for SQL parsing

You can also access the FlowHigh SQL parser through the web based user interface. The below figure shows how FlowHigh provides the information about tables in a SQL query by grouping them into types of tables.

We took the Athena SQL query example and parsed it through the web user interface.

We got the weblogs and users tables back. As you can see the web UI also classifies these two tables as physical tables. Other types of tables are CTE, Views etc.

When we select a table name, it reveals the associated column names. For instance, by selecting the WEBLOGS table, we can view its corresponding column names.

Likewise FlowHigh can be used to get columns used in a where conditions ,order by, group by and joins in the SQL query.

Columns used in GROUP BY / ORDER BY clause

This figure shows how FlowHigh can be used to filter out the columns used in order by and group by clause.

Filter columns

This figure shows how FlowHigh can be used to filter out the columns used in where clauses.

Columns in join conditions

This figure shows how FlowHigh can be used to filter out the columns used and types of join. I have outlined in detail how this type of information can be very useful for data engineering scenarios to identify indexes, cluster keys etc.

Need more?

FlowHigh ships with two other modules

• FlowHigh SQL Analyser. The Analyser checks for issues in your SQL coed. It detects 30+ anti patterns in SQL code

I have written up a blog post on automatically detecting bad SQL code.

• FlowHigh SQL Visualiser. Visualising SQL queries helps understand complex queries. It lets developers see how each part of the query contributes to the final result, making it easier to understand and debug.

You can try FlowHigh yourself.