如何适配SQLite PEG解析器以支持BigQuery SQL语法？

Got it, let's tackle this issue step by step. The error you're seeing happens because the original SQLite Parser's PEG.js grammar doesn't account for BigQuery's unique function syntax—specifically EXTRACT's FROM separator and DATE_SUB with INTERVAL arguments. Here's how to modify the grammar to fix this:

1. Add Support for BigQuery's EXTRACT Function #

The parser chokes on EXTRACT(MONTH FROM ...) because SQLite uses a different pattern for date part extraction, and the original grammar doesn't expect the FROM keyword inside a function call.

In your grammar.pegjs file, find the functionCall rule (or whatever rule handles function invocations). Add a dedicated rule for EXTRACT, then update the function call rule to include it:

// Define EXTRACT with its unique syntax (case-insensitive for keywords)
extractFunction = "EXTRACT"i "(" identifier "FROM"i expression ")"

// Update the main function call rule to prioritize EXTRACT over standard functions
functionCall = extractFunction / dateSubFunction / standardFunctionCall

// Keep your existing standard function rule (example below)
standardFunctionCall = identifier "(" exprList? ")"

The i suffix makes keywords like EXTRACT and FROM case-insensitive, matching BigQuery's behavior.

2. Handle BigQuery's DATE_SUB with INTERVAL #

BigQuery's DATE_SUB uses an INTERVAL value unit argument, which isn't part of SQLite's syntax. Add these rules to support it:

// Define the INTERVAL literal structure
intervalLiteral = "INTERVAL"i numericLiteral identifier

// Define DATE_SUB to accept a date expression + interval
dateSubFunction = "DATE_SUB"i "(" expression "," intervalLiteral ")"

Don't forget to add dateSubFunction to the functionCall rule as shown above.

3. Fix CURRENT_DATE() Parsing #

SQLite treats CURRENT_DATE as a literal value, but BigQuery uses it as a zero-argument function (CURRENT_DATE()). Make sure your standardFunctionCall rule allows empty argument lists:

standardFunctionCall = identifier "(" (exprList)? ")"

This ensures CURRENT_DATE() is parsed as a function call instead of a literal.

4. Test Your Changes #

Paste your modified grammar into the PEG.js online tool, then test the problematic SQL:

select EXTRACT(MONTH FROM DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH))

It should now parse successfully and output the correct JSON structure.

Extra Tips for More BigQuery Support #

If you run into other BQ-specific functions (like DATE_ADD, TIMESTAMP_DIFF, or aggregate functions with special clauses), you can extend this pattern:

• Add dedicated rules for functions with unique syntax
• Generalize the exprList rule to handle more flexible argument separators if needed

内容的提问来源于stack exchange，提问作者Tamir Klein