RISE: Rule-Driven SQL Dialect Translation via Query Reduction

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 Qc that contains a SQL dialect d, we first employ a dialect-aware query reduction technique to derive a simplified query Qs by removing d-irrelevant SQL elements from Qc. Subsequently, we utilize LLMs to translate Qs into Q_s', and automatically extract the translation rule rd for dialect d based on the relationship between Qs and Q_s'. By applying rd to Qc, we can effectively translate the dialect d within Qc, thereby bypassing the complexity of the source query Qc. 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.