Uli Bethke
What is an online SQL parser
An SQL parser analyses the structure of an SQL query. It takes the SQL statement and decomposes it into its elements, e.g. tables, columns, joins, filters, subqueries etc.
Once you have deconstructed your SQL queries you can apply analytics on SQL usage inside your organisation. Analysis can answer a lot of useful questions
- Which tables are frequently used in SQL queries?
- What are common join columns?
- What columns are used in filters?
- Which tables are frequently queried together?
- What columns are used to aggregate data?
These are just some of the questions you can answer with an SQL parser. We will discuss the benefits and use cases of SQL parsing in detail in a minute.
Let’s first clarify what we mean by online SQL parsing. An online SQL parser follows the Software as a Service (SaaS) model. Another word for online SQL parser is SaaS SQL parser. You don’t need to download and install any software locally on your laptop or PC. You just sign up for the service online and off you go. You access the software through your web browser or programmatically through an API or an SDK. Sonra’s FlowHigh is an online SQL parser
Related concepts to an SQL parser
You will come across similar or related terms and concepts for an SQL parser.
SQL statement parser or SQL command parser are often used interchangeably with SQL parser. However, the emphasis of SQL statement or command parser is often on an individual SQL statement rather than entire scripts or stored procedures.
SQL Parsing Tool and SQL Parsing Library refer to similar concepts as SQL parser. An SQL parsing library is a collection of pre-built functions, classes, or modules that can be integrated into other software applications. It provides a programmatic interface for developers to include SQL parsing functionality in their projects.
SQL parser API is very similar to the terms online SQL parser or SaaS SQL parser. An SQL parser API is an Application Programming Interface (API) that offers access to SQL parsing functionality via a web service or a remote procedure call. This enables developers to use SQL parsing features in their applications without directly integrating a parsing library. Sonra’s FlowHigh is an online SQL parser.
SQL Syntax Checker, SQL Formatter, SQL Linter, SQL Code Validator, SQL Optimization Tool, SQL Query Analyzer are useful applications that are built on top of an SQL parser. The SQL parser is the foundation for these tools.
Benefits of an online SQL parser
We have written extensively in the past about use cases for SQL parsing. Let me briefly summarise the core benefits of an SQL parser:
- Formatting and maintenance of code: SQL parsers make code easier to read and maintain by requiring uniform formatting and structure in queries. This makes it easier for the team to work together and simplifies the code.
- Query conversion: SQL parsers can be used to convert SQL queries into other query languages, ETL tools, or data integration tools. They can also translate SQL queries between different dialects. When moving applications to new database systems, this can be helpful.
- Visualisation and analysis of queries: Parsers can visualise and analyse SQL queries, helping developers understand the relationships between tables and columns.
- Query formatting: To make SQL code more readable and compliant with coding standards, a SQL parser can automatically format SQL code using predefined styles or rules.
- SQL parsers may extract metadata from queries, including the tables and columns used, which is useful for processes like schema validation, impact analysis, and documentation creation.
- Optimising and refactoring queries: Parsers can assist in refactoring SQL queries by spotting unnecessary or inefficient code and recommending alternatives to speed up query execution and boost performance.
- Data lineage analysis: By detecting dependencies between tables and columns in queries, SQL parsers can be used to track data lineage, allowing for a better understanding of data flows and linkages in complicated systems.
- Automated testing: By producing test cases, validating query outputs, and spotting any bugs in the SQL code, SQL parsers can help with automated testing of SQL queries. The SQL parser assists in identifying any anti-pattern mistakes or consistency problems in the SQL queries as part of a CI pipeline, giving developers thorough feedback about the problems detected. The queries can then be tested again to make sure they are functioning properly after developers have fixed the problems.
- Impact analysis involves assessing the prospective effects of system changes, such as adjustments to database architectures or data processing pipelines. Developers and database administrators may make wise decisions, prepare for essential changes, and reduce risks by identifying the affected components and evaluating the potential impact.
SQL parser versus online SQL parser
Traditionally SQL parsers had to be installed locally or on premise. The IT department typically provides a server, instals and maintains the SQL parser software or library. An online SQL parser follows the Software as a Service (SaaS) model. It is hosted and managed by the vendor of the SaaS SQL parser.
Here are some of the advantages of an online SQL parser over the traditional way of parsing SQL.
- Accessibility: A SaaS SQL parser can be used from anywhere with an internet link, while locally installed software can only be used on the device where it was installed. This means that users can use SaaS apps on any device without having to run the software, making it easier and more flexible.
- Scalability: A SaaS SQL parser can be easily scaled up or down.
- Maintenance and updates: SaaS software companies take care of maintenance and updates, so users don’t have to spend time and money keeping their software up-to-date and in good shape.
At Sonra we have created FlowHigh a free online SQL parser to bring you the benefits of SQL parsing and parsing your SQL online via a SaaS application.
FlowHigh – free online SQL parser
So ‘Say hello’ to FlowHigh and a new era of SQL parsing.
Be the first to explore our groundbreaking online SQL parser by signing up for our private preview. It’s free.
You can access a free version through your browser. You can also use our Python SDK to parse SQL programmatically and to automate SQL parsing steps.
Parse SQL online
Let’s go through a practical example of parsing SQL through your browser.
Log on to FlowHigh SQL parser with your browser.
Use the following simple query to test the parser
|
1 2 3 4 5 6 7 8 |
SELECT d.year ,i.brand_id ,cs.quantity-COALESCE(cr.return_quantity ,0) AS sales_cnt FROM catalog_sales cs JOIN item i ON i.item_sk=cs.item_sk WHERE i.category_id=10 |
You can get the parsed output either as a JSON or XML message. The deconstructed SQL is returned as a tree.
I have highlighted some of the SQL clauses and mapped them to the matching components in the XML output.
Message
Flexter generates a message as output from the SQL. The message can either be returned as XML or JSON. The message is based on a schema, which is based on an XSD.
The XSD is structured hierarchically and made up of various complex types. At the root of this tree we have the complexType ParseQL. It contains various elements, e.g. Statements. You can view the XSD in the online documentation.
Online SQL parser SDK
FlowHigh ships a powerful Python SDK to automate SQL parsing tasks.
SQL parser code snippets
We have prepared a couple of SQL parser SDK snippets that you can view online. Those snippets have been built from the primitives of the FlowHigh SDK. It covers some common use cases
- Get all of the tables and table types from an SQL query
- Get a list of all of the columns in the SELECT clause of an SQL query
- Get all of the columns that are used to filter a query
- Get all of the columns in the ORDER BY clause. This is useful to identify columns that are involved in expensive sort operations. These are candidates for clustered indexes or sort keys.
- Get all of the columns in the GROUP BY clause. These may be candidates for Materialised Views.
Here is a screenshot of the SDK snippet that deconstructs an SQL statement into its tables and table types
The online documentation contains many more examples on how the SQL parser SDK works to meet your specific needs.
The SDK ships with various abstractions for common use cases, e.g. to get all of the tables in an SQL statement you can run the following code
|
1 2 3 |
>>> from flowhigh.utils.converter import FlowHighSubmissionClass >>> fh = FlowHighSubmissionClass.from_sql("SELECT id FROM TAB GROUP BY 1") >>> [fh.get_tables(s) for s in fh.get_statements()] |
FlowHigh online SQL parser apps
But wait there’s more.
We have built a couple of useful apps on top of the FlowHigh online SQL parser.
SQL formatter
The FlowHigh SQL formatter is the only online SQL formatter that can be accessed programmatically via an SDK and has a free plan.
You can use the SDK to programmatically format thousands of SQL statements. It’s super fast even for very complex and nested SQL. Try FlowHigh and see for yourself.
Visualise SQL
Do you need to visualise SQL? You probably have inherited some spaghetti code SQL with inline views, derived tables, subqueries etc. in the past. Debugging and reverse engineering such an SQL statement can be quite frustrating. Not so with FlowHigh SQL Visualiser. FlowHigh is the only tool that will visualise complex SQL into its components.
Let’s look at a simple example to highlight some of the features of the SQL visualiser.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT p1 ,p2 ,COUNT(*) AS numorders FROM(SELECT op1.OrderID ,op1.ProductID AS p1 ,op2.ProductID AS p2 FROM(SELECT DISTINCT OrderID ,ProductID FROM OrderLines)op1 JOIN(SELECT DISTINCT OrderID ,ProductID FROM OrderLines)op2 ON op1.OrderID = op2.OrderID AND op1.ProductID > op2.ProductID)combinations GROUP BY p1 ,p2 |
Visualise the inline views and the physical tables
Display columns
Highlighting an inline view or any other object will highlight the SQL code
Drill down: expand and collapse the SQL tree
Fully collapsed
One level down
Second level down
Optimise SQL
Are you worried about the performance, correctness, and readability of your SQL queries? You can use FlowHigh to detect SQL antipatterns and optimise your team’s SQL code.
Check the online documentation to find out which SQL antipatterns FlowHigh can detect.
All features of FlowHigh can be accessed programmatically through our Python SDK. Optimising SQL is no exception.
Data Lineage and impact analysis
FlowHigh can be used to generate data lineage in your SQL data pipelines.
You can use it to trace your data’s journey through your SQL pipelines.
Data engineers can use it to perform impact analysis on their SQL data pipelines, e.g. what will be the impact on upstream and downstream consumers if we drop a column in the customer table.
Data lineage and impact analysis are features under active development. Sign up for FlowHigh to to be the first to know when it is released.
