Does SQLGlot's data design allow for a MDX dialect?

[galenseilisnh]

I decided not to post this as a feature request b/c MDX isn't really a SQL dialect.

I am fine with the idea that SQLGlot only supports SQL dialects. It would be helpful if it did allow some further cross-lang support since transpiling from MDX-to-SQL could be helpful. But I expect supporting cross-language transpiling gets more difficult as different technologies support non-equivalent concepts.

---

I am thinking about tokenizing and parsing MDX. I started a prototype in pure Python, but the thought occurred to me that 'maybe' SQLGlot could do it via defining an MDX dialect file similar to the ones defined within SQLGlot.

• Is this possible?
• Is it recommended?
• Are there good alternatives for tokenizing and parsing MDX in the Python space?

[georgesittas]

You can do many interesting things with SQLGlot, so I don't see why not :)

For example, SQLGlot's execution engine (experimental) leverages the existing dialect infrastructure to transpile SQL to Python and then execute Python code to evaluate queries:

sqlglot/sqlglot/executor/python.py

Lines 420 to 460 in 09882e3

	class Python(Dialect):
	class Tokenizer(tokens.Tokenizer):
	STRING_ESCAPES = ["\\"]
	class Generator(generator.Generator):
	TRANSFORMS = {
	**{klass: _rename for klass in subclasses(exp.__name__, exp.Binary)},
	**{klass: _rename for klass in exp.ALL_FUNCTIONS},
	exp.Case: _case_sql,
	exp.Alias: lambda self, e: self.sql(e.this),
	exp.Array: inline_array_sql,
	exp.And: lambda self, e: self.binary(e, "and"),
	exp.Between: _rename,
	exp.Boolean: lambda self, e: "True" if e.this else "False",
	exp.Cast: lambda self, e: f"CAST({self.sql(e.this)}, exp.DataType.Type.{e.args['to']})",
	exp.Column: lambda self,
	e: f"scope[{self.sql(e, 'table') or None}][{self.sql(e.this)}]",
	exp.Concat: lambda self, e: self.func(
	"SAFECONCAT" if e.args.get("safe") else "CONCAT", *e.expressions
	),
	exp.Distinct: lambda self, e: f"set({self.sql(e, 'this')})",
	exp.Div: _div_sql,
	exp.Extract: lambda self,
	e: f"EXTRACT('{e.name.lower()}', {self.sql(e, 'expression')})",
	exp.In: lambda self,
	e: f"{self.sql(e, 'this')} in {{{self.expressions(e, flat=True)}}}",
	exp.Interval: lambda self, e: f"INTERVAL({self.sql(e.this)}, '{self.sql(e.unit)}')",
	exp.Is: lambda self, e: (
	self.binary(e, "==") if isinstance(e.this, exp.Literal) else self.binary(e, "is")
	),
	exp.JSONExtract: lambda self, e: self.func(e.key, e.this, e.expression, *e.expressions),
	exp.JSONPath: lambda self, e: f"[{','.join(self.sql(p) for p in e.expressions[1:])}]",
	exp.JSONPathKey: lambda self, e: f"'{self.sql(e.this)}'",
	exp.JSONPathSubscript: lambda self, e: f"'{e.this}'",
	exp.Lambda: _lambda_sql,
	exp.Not: lambda self, e: f"not {self.sql(e.this)}",
	exp.Null: lambda *_: "None",
	exp.Or: lambda self, e: self.binary(e, "or"),
	exp.Ordered: _ordered_py,
	exp.Star: lambda *_: "1",
	}

I'm not familiar with MDX, but you could certainly play around with it. The degree of convergence from usual SQL syntax is what determines whether it's recommended. I'm also not familiar of alternatives.

[galenseilisnh]

For example, SQLGlot's execution engine (experimental) leverages the existing dialect infrastructure to transpile SQL to Python and then execute Python code to evaluate queries:

Cool!