-
Notifications
You must be signed in to change notification settings - Fork 555
/
query_builder.py
663 lines (531 loc) · 20.4 KB
/
query_builder.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
from __future__ import annotations
from io import StringIO
from typing import TYPE_CHECKING
import sqlglot as sg
import toolz
import ibis.common.exceptions as com
import ibis.expr.operations as ops
import ibis.expr.types as ir
from ibis import util
from ibis.backends.base.sql.compiler.base import DML, QueryAST, SetOp
from ibis.backends.base.sql.compiler.select_builder import SelectBuilder, _LimitSpec
from ibis.backends.base.sql.compiler.translator import ExprTranslator, QueryContext
from ibis.backends.base.sql.registry import quote_identifier
from ibis.common.grounds import Comparable
from ibis.config import options
from ibis.expr.rewrites import rewrite_dropna, rewrite_fillna
if TYPE_CHECKING:
from collections.abc import Iterable
class TableSetFormatter:
_join_names = {
ops.InnerJoin: "INNER JOIN",
ops.LeftJoin: "LEFT OUTER JOIN",
ops.RightJoin: "RIGHT OUTER JOIN",
ops.OuterJoin: "FULL OUTER JOIN",
ops.LeftAntiJoin: "LEFT ANTI JOIN",
ops.LeftSemiJoin: "LEFT SEMI JOIN",
ops.CrossJoin: "CROSS JOIN",
}
def __init__(self, parent, node, indent=2):
# `parent` is a `Select` instance, not a `TableSetFormatter`
self.parent = parent
self.context = parent.context
self.node = node
self.indent = indent
self.join_tables = []
self.join_types = []
self.join_predicates = []
def _translate(self, expr):
return self.parent._translate(expr)
# TODO(kszucs): could use lin.traverse here
def _walk_join_tree(self, op):
if util.all_of([op.left, op.right], ops.Join):
raise NotImplementedError("Do not support joins between joins yet")
jname = self._get_join_type(op)
# Read off tables and join predicates left-to-right in
# depth-first order
if isinstance(op.left, ops.Join):
self._walk_join_tree(op.left)
self.join_tables.append(self._format_table(op.right))
elif isinstance(op.right, ops.Join):
self.join_tables.append(self._format_table(op.left))
self._walk_join_tree(op.right)
else:
# Both tables
self.join_tables.append(self._format_table(op.left))
self.join_tables.append(self._format_table(op.right))
self.join_types.append(jname)
self.join_predicates.append(op.predicates)
def _get_join_type(self, op):
return self._join_names[type(op)]
def _quote_identifier(self, name):
return quote_identifier(name)
def _format_in_memory_table(self, op):
if self.context.compiler.cheap_in_memory_tables:
return op.name
names = op.schema.names
raw_rows = []
for row in op.data.to_frame().itertuples(index=False):
raw_row = []
for val, name in zip(row, names):
lit = ops.Literal(val, dtype=op.schema[name])
raw_row.append(
f"{self._translate(lit)} AS {self._quote_identifier(name)}"
)
raw_rows.append(", ".join(raw_row))
if self.context.compiler.support_values_syntax_in_select:
rows = ", ".join(f"({raw_row})" for raw_row in raw_rows)
return f"(VALUES {rows})"
else:
rows = " UNION ALL ".join(f"(SELECT {raw_row})" for raw_row in raw_rows)
return f"({rows})"
def _format_table(self, op):
# TODO: This could probably go in a class and be significantly nicer
ctx = self.context
orig_op = op
if isinstance(op, (ops.SelfReference, ops.Sample)):
op = op.table
alias = ctx.get_ref(orig_op)
if isinstance(op, ops.InMemoryTable):
result = self._format_in_memory_table(op)
elif isinstance(op, ops.PhysicalTable):
# TODO(kszucs): add a mandatory `name` field to the base
# PhyisicalTable instead of the child classes, this should prevent
# this error scenario
if (name := op.name) is None:
raise com.RelationError(f"Table did not have a name: {op!r}")
namespace = getattr(op, "namespace", None)
catalog = getattr(namespace, "database", None)
db = getattr(namespace, "schema", None)
result = sg.table(
name,
db=db,
catalog=catalog,
quoted=self.parent.translator_class._quote_identifiers,
).sql(dialect=self.parent.translator_class._dialect_name)
elif ctx.is_extracted(op):
if isinstance(orig_op, ops.SelfReference):
result = ctx.get_ref(op)
else:
result = alias
else:
subquery = ctx.get_compiled_expr(op)
result = f"(\n{util.indent(subquery, self.indent)}\n)"
if result != alias:
result = f"{result} {alias}"
if isinstance(orig_op, ops.Sample):
result = self._format_sample(orig_op, result)
return result
def _format_sample(self, op, table):
# Should never be hit in practice, as Sample operations should be rewritten
# before this point for all backends without TABLESAMPLE support
raise com.UnsupportedOperationError("`Table.sample` is not supported")
def get_result(self):
# Got to unravel the join stack; the nesting order could be
# arbitrary, so we do a depth first search and push the join tokens
# and predicates onto a flat list, then format them
op = self.node
if isinstance(op, ops.Join):
self._walk_join_tree(op)
else:
self.join_tables.append(self._format_table(op))
# TODO: Now actually format the things
buf = StringIO()
buf.write(self.join_tables[0])
for jtype, table, preds in zip(
self.join_types, self.join_tables[1:], self.join_predicates
):
buf.write("\n")
buf.write(util.indent(f"{jtype} {table}", self.indent))
fmt_preds = []
npreds = len(preds)
for pred in preds:
new_pred = self._translate(pred)
if npreds > 1:
new_pred = f"({new_pred})"
fmt_preds.append(new_pred)
if len(fmt_preds):
buf.write("\n")
conj = " AND\n{}".format(" " * 3)
fmt_preds = util.indent("ON " + conj.join(fmt_preds), self.indent * 2)
buf.write(fmt_preds)
return buf.getvalue()
class Select(DML, Comparable):
"""A SELECT statement."""
def __init__(
self,
table_set,
select_set,
translator_class,
table_set_formatter_class,
context,
subqueries=None,
where=None,
group_by=None,
having=None,
order_by=None,
limit=None,
distinct=False,
indent=2,
parent_op=None,
):
self.translator_class = translator_class
self.table_set_formatter_class = table_set_formatter_class
self.context = context
self.select_set = select_set
self.table_set = table_set
self.distinct = distinct
self.parent_op = parent_op
self.where = where or []
# Group keys and post-predicates for aggregations
self.group_by = group_by or []
self.having = having or []
self.order_by = order_by or []
self.limit = limit
self.subqueries = subqueries or []
self.indent = indent
def _translate(self, expr, named=False, permit_subquery=False, within_where=False):
translator = self.translator_class(
expr,
context=self.context,
named=named,
permit_subquery=permit_subquery,
within_where=within_where,
)
return translator.get_result()
def __equals__(self, other: Select) -> bool:
return self.limit == other.limit and self._all_exprs() == other._all_exprs()
def _all_exprs(self):
return tuple(
*self.select_set,
self.table_set,
*self.where,
*self.group_by,
*self.having,
*self.order_by,
*self.subqueries,
)
def compile(self):
"""Compile a query.
This method isn't yet idempotent; calling multiple times may yield
unexpected results.
"""
# Can't tell if this is a hack or not. Revisit later
self.context.set_query(self)
# If any subqueries, translate them and add to beginning of query as
# part of the WITH section
with_frag = self.format_subqueries()
# SELECT
select_frag = self.format_select_set()
# FROM, JOIN, UNION
from_frag = self.format_table_set()
# WHERE
where_frag = self.format_where()
# GROUP BY and HAVING
groupby_frag = self.format_group_by()
# ORDER BY
order_frag = self.format_order_by()
# LIMIT
limit_frag = self.format_limit()
# Glue together the query fragments and return
query = "\n".join(
filter(
None,
[
with_frag,
select_frag,
from_frag,
where_frag,
groupby_frag,
order_frag,
limit_frag,
],
)
)
return query
def format_subqueries(self):
if not self.subqueries:
return None
context = self.context
buf = []
for expr in self.subqueries:
formatted = util.indent(context.get_compiled_expr(expr), 2)
alias = context.get_ref(expr)
buf.append(f"{alias} AS (\n{formatted}\n)")
return "WITH {}".format(",\n".join(buf))
def format_select_set(self):
# TODO:
context = self.context
formatted = []
for node in self.select_set:
if isinstance(node, ops.Value):
expr_str = self._translate(node, named=True, permit_subquery=True)
elif isinstance(node, ops.TableNode):
alias = context.get_ref(node)
expr_str = f"{alias}.*" if alias else "*"
else:
raise TypeError(node)
formatted.append(expr_str)
buf = StringIO()
line_length = 0
max_length = 70
tokens = 0
for i, val in enumerate(formatted):
# always line-break for multi-line expressions
if val.count("\n"):
if i:
buf.write(",")
buf.write("\n")
indented = util.indent(val, self.indent)
buf.write(indented)
# set length of last line
line_length = len(indented.split("\n")[-1])
tokens = 1
elif tokens > 0 and line_length and len(val) + line_length > max_length:
# There is an expr, and adding this new one will make the line
# too long
buf.write(",\n ") if i else buf.write("\n")
buf.write(val)
line_length = len(val) + 7
tokens = 1
else:
if i:
buf.write(",")
buf.write(" ")
buf.write(val)
tokens += 1
line_length += len(val) + 2
if self.distinct:
select_key = "SELECT DISTINCT"
else:
select_key = "SELECT"
return f"{select_key}{buf.getvalue()}"
def format_table_set(self):
if self.table_set is None:
return None
fragment = "FROM "
helper = self.table_set_formatter_class(self, self.table_set)
fragment += helper.get_result()
return fragment
def format_group_by(self):
if not len(self.group_by):
# There is no aggregation, nothing to see here
return None
lines = []
if len(self.group_by) > 0:
clause = "GROUP BY {}".format(
", ".join([str(x + 1) for x in self.group_by])
)
lines.append(clause)
if len(self.having) > 0:
trans_exprs = []
for expr in self.having:
translated = self._translate(expr)
trans_exprs.append(translated)
lines.append("HAVING {}".format(" AND ".join(trans_exprs)))
return "\n".join(lines)
def format_where(self):
if not self.where:
return None
buf = StringIO()
buf.write("WHERE ")
fmt_preds = []
npreds = len(self.where)
for pred in self.where:
new_pred = self._translate(pred, permit_subquery=True, within_where=True)
if npreds > 1:
new_pred = f"({new_pred})"
fmt_preds.append(new_pred)
conj = " AND\n{}".format(" " * 6)
buf.write(conj.join(fmt_preds))
return buf.getvalue()
def format_order_by(self):
if not self.order_by:
return None
buf = StringIO()
buf.write("ORDER BY ")
formatted = []
for key in self.order_by:
translated = self._translate(key.expr)
suffix = "ASC" if key.ascending else "DESC"
translated += f" {suffix}"
formatted.append(translated)
buf.write(", ".join(formatted))
return buf.getvalue()
def format_limit(self):
if self.limit is None:
return None
buf = StringIO()
n = self.limit.n
if n is None:
n = self.context.compiler.null_limit
elif not isinstance(n, int):
n = f"(SELECT {self._translate(n)} {self.format_table_set()})"
if n is not None:
buf.write(f"LIMIT {n}")
offset = self.limit.offset
if not isinstance(offset, int):
offset = f"(SELECT {self._translate(offset)} {self.format_table_set()})"
if offset != 0 and n != 0:
buf.write(f" OFFSET {offset}")
return buf.getvalue()
class Union(SetOp):
_keyword = "UNION"
class Intersection(SetOp):
_keyword = "INTERSECT"
class Difference(SetOp):
_keyword = "EXCEPT"
def flatten_set_op(op) -> Iterable[ops.Table | bool]:
"""Extract all union queries from `table`.
Parameters
----------
op
Set operation to flatten
Returns
-------
Iterable[Table | bool]
Iterable of tables and `bool`s indicating `distinct`.
"""
if isinstance(op, ops.SetOp):
# For some reason mypy considers `op.left` and `op.right`
# of `Argument` type, and fails the validation. While in
# `flatten` types are the same, and it works
return toolz.concatv(
flatten_set_op(op.left), # type: ignore
[op.distinct],
flatten_set_op(op.right), # type: ignore
)
return [op]
def flatten(op: ops.TableNode):
"""Extract all intersection or difference queries from `table`.
Parameters
----------
op
Table operation to flatten
Returns
-------
Iterable[Table | bool]
Iterable of tables and `bool`s indicating `distinct`.
"""
return list(toolz.concatv(flatten_set_op(op.left), flatten_set_op(op.right)))
class Compiler:
translator_class = ExprTranslator
context_class = QueryContext
select_builder_class = SelectBuilder
table_set_formatter_class = TableSetFormatter
select_class = Select
union_class = Union
intersect_class = Intersection
difference_class = Difference
cheap_in_memory_tables = False
support_values_syntax_in_select = True
null_limit = None
rewrites = rewrite_fillna | rewrite_dropna
@classmethod
def make_context(cls, params=None):
params = params or {}
unaliased_params = {}
for expr, value in params.items():
op = expr.op()
if isinstance(op, ops.Alias):
op = op.arg
unaliased_params[op] = value
return cls.context_class(compiler=cls, params=unaliased_params)
@classmethod
def to_ast(cls, node, context=None):
# TODO(kszucs): consider to support a single type only
if isinstance(node, ir.Expr):
node = node.op()
if cls.rewrites:
node = node.replace(cls.rewrites)
if context is None:
context = cls.make_context()
# collect setup and teardown queries
setup_queries = cls._generate_setup_queries(node, context)
teardown_queries = cls._generate_teardown_queries(node, context)
# TODO: any setup / teardown DDL statements will need to be done prior
# to building the result set-generating statements.
if isinstance(node, ops.Union):
query = cls._make_union(node, context)
elif isinstance(node, ops.Intersection):
query = cls._make_intersect(node, context)
elif isinstance(node, ops.Difference):
query = cls._make_difference(node, context)
else:
query = cls.select_builder_class().to_select(
select_class=cls.select_class,
table_set_formatter_class=cls.table_set_formatter_class,
node=node,
context=context,
translator_class=cls.translator_class,
)
return QueryAST(
context,
query,
setup_queries=setup_queries,
teardown_queries=teardown_queries,
)
@classmethod
def to_ast_ensure_limit(cls, expr, limit=None, params=None):
context = cls.make_context(params=params)
query_ast = cls.to_ast(expr, context)
# note: limit can still be None at this point, if the global
# default_limit is None
for query in reversed(query_ast.queries):
if (
isinstance(query, Select)
and not isinstance(expr, ir.Scalar)
and query.table_set is not None
):
if query.limit is None:
if limit == "default":
query_limit = options.sql.default_limit
else:
query_limit = limit
if query_limit:
query.limit = _LimitSpec(query_limit, offset=0)
elif limit is not None and limit != "default":
query.limit = _LimitSpec(limit, query.limit.offset)
return query_ast
@classmethod
def to_sql(cls, node, context=None, params=None):
# TODO(kszucs): consider to support a single type only
if isinstance(node, ir.Expr):
node = node.op()
assert isinstance(node, ops.Node)
if context is None:
context = cls.make_context(params=params)
return cls.to_ast(node, context).queries[0].compile()
@staticmethod
def _generate_setup_queries(expr, context):
return []
@staticmethod
def _generate_teardown_queries(expr, context):
return []
@staticmethod
def _make_set_op(cls, op, context):
# flatten unions so that we can codegen them all at once
set_op_info = list(flatten_set_op(op))
# since op is a union, we have at least 3 elements in union_info (left
# distinct right) and if there is more than a single union we have an
# additional two elements per union (distinct right) which means the
# total number of elements is at least 3 + (2 * number of unions - 1)
# and is therefore an odd number
npieces = len(set_op_info)
assert npieces >= 3 and npieces % 2 != 0, "Invalid set operation expression"
# 1. every other object starting from 0 is a Table instance
# 2. every other object starting from 1 is a bool indicating the type
# of $set_op (distinct or not distinct)
table_exprs, distincts = set_op_info[::2], set_op_info[1::2]
return cls(table_exprs, op, distincts=distincts, context=context)
@classmethod
def _make_union(cls, op, context):
return cls._make_set_op(cls.union_class, op, context)
@classmethod
def _make_intersect(cls, op, context):
# flatten intersections so that we can codegen them all at once
return cls._make_set_op(cls.intersect_class, op, context)
@classmethod
def _make_difference(cls, op, context):
# flatten differences so that we can codegen them all at once
return cls._make_set_op(cls.difference_class, op, context)