Deep dive on SQL parsing on Teradata

Published on October 19, 2023
Updated on September 11, 2024

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 ,Redshift and AWS Athena in our earlier blog posts. We cover SQL parsing in Teradata 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 Teradata history. Additionally, we will present some code that utilises the FlowHigh SQL parser SDK to programmatically parse SQL from Teradata. The parsing of sql from Teradata 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 Teradata

Sonra has created an online SQL parser. Our vision is to be SQL dialect agnostic. The parser also works with Teradata. 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 Teradata.

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

Programmatically parsing the Teradata query history with the FlowHigh SDK

We used a Teradata Vantage cloud hosted instance. This resource enables us to explore, test, and develop without a local Teradata setup. Teradata Vantage is available at https://clearscape.teradata.com. You can check out Teradata’s capabilities, play with queries, and build prototypes in an environment that mirrors a full-scale Teradata deployment. It offers a practical and convenient way for developers and analysts to hone their skills and run test cases in a real-world Teradata environment.

We used this environment to iterate over the Teradata query history and parse the submitted SQL. The query history is contained in QryLogSQLV.

QryLogSQLV is a system view residing within the DBC database to store and present detailed information about the SQL queries executed in the Teradata environment. Specifically, QryLogSQLV focuses on capturing and displaying the SQL text of queries.

We can query QryLogSQLV to retrieve information about the executed SQL queries on Teradata database using the below query.

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 Teradata analyses the SQL queries you submit and delivers the output as a JSON or XML message. The output message contains information about filter parameters, fetched columns, aliases, join conditions, tables, and any other SQL command clauses used in the query.

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

The SQL parser also supports other DML and DDL statements such as CREATE, INSERT, UPDATE, MERGE etc.

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

The XML outlines references to three tables used in the query: Customers, Orders, and OrderDetails, each of which is shown with a unique identifier in the DBOHier section of the XML message. The columns involved from these tables are as follows:

  • From the Customers table: CustomerName and CustomerId.
  • From the Orders table: OrderDate and OrderId.
  • From the OrderDetails table: Quantity and OrderId.

Joins

The XML includes two inner joins:

  • Between the Customers table (alias C) and the Orders table (alias O), connected via the CustomerId column.
  • Between the Orders table (alias O) and the OrderDetails table (alias OD), linked through the OrderId column.

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

Internally the column CustomerId and from T1 and CustomerId from T2 is referenced as C5 and C6 and column OrderId from T2 and T3 is referenced as C3 and C7 (oref) , which can be looked up from the query hierarchy <DBOHier> at the end of the XML message.

GROUP BY

The results are grouped by CustomerName from the Customers table and OrderDate from the Orders table.

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

Internally the column CustomerName from the Customers table and OrderDate from the Orders table is referenced as C1 and C2, which can be looked up from the query hierarchy <DBOHier> at the end of the XML message.

FILTER

An aggregate filter has been applied to ensure the SUM of the Quantity column from the OrderDetails table is greater than 10.

This can be found in the filter section of the XML output:

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

ORDER BY

Results are ordered in descending order based on the total quantity ordered, which is an aggregated summation of the Quantity column from the OrderDetails table.This can be found in the sort section of the XML output:

Although Teradata might not consistently offer detailed insights about specific tables and columns within a query, FlowHigh enhances this data. It not only pinpoints the tables and columns but also correctly identifies any columns used 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 Teradata SQL query example and parsed it through the web user interface.

We got the customer, orders and orderdetails tables back. As you can see the web UI also classifies these three 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 CUSTOMERS 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. Register for the FlowHigh.