RISE: Rule-Driven SQL Dialect Translation via Query Reduction

TL;DR

RISE addresses the challenge of translating SQL dialects across diverse DBMSs, especially for lengthy and complex queries, by coupling dialect-aware query reduction with LLM-assisted translation rule generation. The approach first reduces a complex source query to a dialect-focused simplified form, then uses an LLM to translate the reduced query and extract a translation rule from the resulting ASTs, which is subsequently applied to the original query. Comprehensive evaluation on TPC-DS and SQLProcBench shows that RISE substantially outperforms traditional rule-based tools and pure LLM-based methods, achieving near-perfect accuracy on SQLProcBench and strong gains on TPC-DS. The results demonstrate that query reduction and automated rule generation can significantly reduce manual rule-writing effort while improving robustness for cross-dialect SQL migrations to cloud environments.

Abstract

Translating SQL dialects across different relational database management systems (RDBMSs) is crucial for migrating RDBMS-based applications to the cloud. Traditional SQL dialect translation tools rely on manually-crafted rules, necessitating significant manual effort to support new RDBMSs and dialects. Although large language models (LLMs) can assist in translating SQL dialects, they often struggle with lengthy and complex SQL queries. In this paper, we propose RISE, a novel LLM-based SQL dialect translation approach that can accurately handle lengthy and complex SQL queries. Given a complex source query $Q_c$ that contains a SQL dialect $d$, we first employ a dialect-aware query reduction technique to derive a simplified query $Q_{s}$ by removing $d$-irrelevant SQL elements from $Q_c$. Subsequently, we utilize LLMs to translate $Q_{s}$ into $Q_{s^{'}}$, and automatically extract the translation rule $r_d$ for dialect $d$ based on the relationship between $Q_{s}$ and $Q_{s^{'}}$. By applying $r_d$ to $Q_c$, we can effectively translate the dialect $d$ within $Q_c$, thereby bypassing the complexity of the source query $Q_c$. We evaluate RISE on two real-world benchmarks, i.e., TPC-DS and SQLProcBench, comparing its performance against both the traditional rule-based tools and the LLM-based approaches with respect to translation accuracy. RISE achieves accuracies of 97.98% on TPC-DS and 100% on SQLProcBench, outperforming the baselines by an average improvement of 24.62% and 238.41%, respectively.

Figures (5)

• Figure 1: An Example of SQL Dialect Translation. PostgreSQL-Specific Dialects are Colored in Red.
• Figure 2: Overview of RISE.
• Figure 3: An Illustrative Example of Translation Rule Generation.
• Figure 4: Differences in ROLLUP Semantics between MySQL and PostgreSQL.
• Figure 5: Efficiency Comparison of Correct Translations per Unit Time Between RISE and LLM-based methods.