def test_cte_in_source():
cte = select(source.c.s1).cte("cte")
sub = select(cte.c.s1).select_from(cte).subquery()
query = MergeInto(target=target,
source=sub,
onclause=target.c.t1 == sub.c.s1,
when_clauses=[
WhenMatched(
update(target).values({target.c.t1: sub.c.s1})),
])
expected = """\
MERGE INTO `target`
USING (WITH `cte` AS
(SELECT `source`.`s1` AS `s1`
FROM `source`)
SELECT `cte`.`s1` AS `s1`
FROM `cte`) AS `anon_1`
ON `target`.`t1` = `anon_1`.`s1`
WHEN MATCHED THEN
\tUPDATE SET `t1`=`anon_1`.`s1`
"""
assert str(query.compile(dialect=BigQueryDialect())) == dedent(expected)
def test_example_2():
expected = """\
MERGE INTO dataset.Inventory AS T
USING dataset.NewArrivals AS S
ON T.product = S.product
WHEN MATCHED THEN
UPDATE SET quantity=(T.quantity + S.quantity)
WHEN NOT MATCHED BY TARGET THEN
INSERT (product, quantity) VALUES (S.product, S.quantity)
"""
T = inventory.alias("T")
S = new_arrivals.alias("S")
query = MergeInto(target=T,
source=S,
onclause=T.c.product == S.c.product,
when_clauses=[
WhenMatched(
update(T).values({
T.c.quantity:
T.c.quantity + S.c.quantity,
}), ),
WhenNotMatched(
insert(T).values({
T.c.product: S.c.product,
T.c.quantity: S.c.quantity,
}), )
])
return query, expected
def test_example_6():
expected = """\
MERGE INTO dataset.Inventory AS T
USING (SELECT t1.product AS product, t1.quantity AS quantity, t2.state AS state FROM dataset.NewArrivals AS t1 JOIN dataset.Warehouse AS t2 ON t1.warehouse = t2.warehouse) AS S
ON T.product = S.product
WHEN MATCHED AND S.state = 'CA' THEN
UPDATE SET quantity=(T.quantity + S.quantity)
WHEN MATCHED THEN
DELETE
"""
T = inventory.alias("T")
NA = new_arrivals.alias("t1")
W = warehouse.alias("t2")
S = select(NA.c.product, NA.c.quantity, W.c.state).select_from(
join(NA, W, NA.c.warehouse == W.c.warehouse)).alias("S")
query = MergeInto(target=T,
source=S,
onclause=T.c.product == S.c.product,
when_clauses=[
WhenMatched(update(T).values({
T.c.quantity:
T.c.quantity + S.c.quantity,
}),
condition=S.c.state == "CA"),
WhenMatched(delete(T))
])
return query, expected
def test_example_8():
expected = """\
MERGE INTO dataset.NewArrivals
USING (SELECT * FROM unnest([('microwave', 10, 'warehouse #1'), ('dryer', 30, 'warehouse #1'), ('oven', 20, 'warehouse #2')])) AS anon_1
ON false
WHEN NOT MATCHED BY TARGET THEN
INSERT ROW
WHEN NOT MATCHED BY SOURCE THEN
DELETE
"""
values = [
str(("microwave", 10, "warehouse #1")),
str(("dryer", 30, "warehouse #1")),
str(("oven", 20, "warehouse #2")),
]
values = ", ".join(values)
T = new_arrivals
S = select("*").select_from(func.UNNEST(text(f"[{values}]"))).subquery(
) # can't get `func.UNNEST(values)` to work, renders as `UNNEST(NULL)`?
query = MergeInto(target=T,
source=S,
onclause=literal(False),
when_clauses=[
WhenNotMatched(insert(T)),
WhenNotMatchedBySource(delete(T))
])
return query, expected
def test_example_3():
expected = """\
MERGE INTO dataset.NewArrivals AS T
USING (SELECT dataset.NewArrivals.product AS product FROM dataset.NewArrivals WHERE dataset.NewArrivals.warehouse != 'warehouse #2') AS S
ON T.product = S.product
WHEN MATCHED AND T.warehouse = 'warehouse #1' THEN
UPDATE SET quantity=(T.quantity + 20)
WHEN MATCHED THEN
DELETE
"""
T = new_arrivals.alias("T")
S = select(new_arrivals.c.product).where(
new_arrivals.c.warehouse != "warehouse #2").alias("S")
query = MergeInto(target=T,
source=S,
onclause=T.c.product == S.c.product,
when_clauses=[
WhenMatched(
update(T).values({
T.c.quantity: T.c.quantity + 20,
}),
condition=T.c.warehouse == "warehouse #1"),
WhenMatched(delete(T))
])
return query, expected
def test_conditions(connection, target, source):
query = MergeInto(
target=target,
source=source,
onclause=target.c.t1 == source.c.s1,
when_clauses=[
WhenNotMatchedBySource(delete(target), condition=target.c.t2 > date.today()),
]
)
connection.execute(query)
def test_constant_onclause(connection, target, source):
query = MergeInto(
target=target,
source=source,
onclause=literal(False),
when_clauses=[
WhenNotMatchedBySource(delete(target)),
]
)
connection.execute(query)
def test_when_not_matched_by_source(connection, target, source):
query = MergeInto(
target=target,
source=source,
onclause=target.c.t1 == source.c.s1,
when_clauses=[
WhenNotMatchedBySource(delete(target)),
]
)
connection.execute(query)
def test_brackets_in_actions():
query = MergeInto(target=target,
source=source,
onclause=target.c.t1 == source.c.s1,
when_clauses=[
WhenMatched(
update(target).values({target.c.t1: "{}"})),
])
expected = """\
MERGE INTO `target`
USING `source`
ON `target`.`t1` = `source`.`s1`
WHEN MATCHED THEN
\tUPDATE SET `t1`='{}'
"""
assert str(
query.compile(dialect=BigQueryDialect(),
compile_kwargs={'literal_binds':
True})) == dedent(expected)
def test_when_not_matched():
query = MergeInto(target=target,
source=source,
onclause=target.c.t1 == source.c.s1,
when_clauses=[
WhenNotMatched(
insert(target).values({
target.c.t2:
source.c.s2 + timedelta(days=1).days
})),
])
expected = """\
MERGE INTO `target`
USING `source`
ON `target`.`t1` = `source`.`s1`
WHEN NOT MATCHED BY TARGET THEN
\tINSERT (`t2`) VALUES ((`source`.`s2` + :s2_1))
"""
assert str(query.compile(dialect=BigQueryDialect())) == dedent(expected)
def test_when_not_matched(connection, target, source):
query = MergeInto(
target=target,
source=source,
onclause=target.c.t1 == source.c.s1,
when_clauses=[
WhenNotMatched(insert(target).values({
target.c.t2: source.c.s2 + timedelta(days=1).days
})),
]
)
connection.execute(query)
def test_parameterless_insert(connection, target, source):
query = MergeInto(
target=target,
source=source,
onclause=target.c.t1 == source.c.s1,
when_clauses=[
WhenNotMatched(insert(target), condition=source.c.s1 != None),
WhenNotMatched(insert(target).values(t1="dummy"), condition=source.c.s2 < date.today()),
WhenNotMatched(insert(target)),
]
)
connection.execute(query)
def test_alias_on_source(connection, target, source):
alias = source.alias("alias_s")
query = MergeInto(
target=target,
source=alias,
onclause=target.c.t1 == alias.c.s1,
when_clauses=[
WhenNotMatchedBySource(delete(target)),
WhenNotMatched(insert(target).values(t1=alias.c.s1)),
WhenMatched(update(target).values(t2=alias.c.s2)),
]
)
connection.execute(query)
def test_alias_on_target(connection, target, source):
alias = target.alias("alias_t")
query = MergeInto(
target=alias,
source=source,
onclause=alias.c.t1 == source.c.s1,
when_clauses=[
WhenNotMatchedBySource(delete(alias), condition=alias.c.t2 > date.today()),
WhenNotMatched(insert(alias).values(t1="dummy")),
WhenMatched(update(alias).values(t2=date.today())),
]
)
connection.execute(query)
def test_multiple_when_clauses(connection, target, source):
query = MergeInto(
target=target,
source=source,
onclause=target.c.t1 == source.c.s1,
when_clauses=[
WhenMatched(update(target).values({
target.c.t2: source.c.s2 + timedelta(days=1).days
})),
WhenNotMatchedBySource(delete(target), condition=target.c.t2 > date.today()),
]
)
connection.execute(query)
def test_when_matched():
query = MergeInto(target=target,
source=source,
onclause=target.c.t1 == source.c.s1,
when_clauses=[
WhenMatched(
update(target).values({
target.c.t2:
source.c.s2 + timedelta(days=1).days
}))
])
# sneaky double space in the last line (between UPDATE & SET)
# due to how the UPDATE clause is transformed (direct string manipulation)
expected = """\
MERGE INTO `target`
USING `source`
ON `target`.`t1` = `source`.`s1`
WHEN MATCHED THEN
\tUPDATE SET `t2`=(`source`.`s2` + :s2_1)
"""
assert str(query.compile(dialect=BigQueryDialect())) == dedent(expected)
def test_update_shared_columns(connection, target, source):
sub = select(source.c.s1.label("t1")).alias("sub")
query = MergeInto(
target=target,
source=sub,
onclause=target.c.t1 == sub.c.t1,
when_clauses=[
WhenMatched(update(target).values({
target.c.t1: sub.c.t1
})),
]
)
connection.execute(query)
def test_subquery_in_source(connection, target, source):
sub = select((source.c.s1 + "_sub").label("s3")).alias("sub")
query = MergeInto(
target=target,
source=sub,
onclause=target.c.t1 == sub.c.s3,
when_clauses=[
WhenMatched(update(target).values({
target.c.t1: sub.c.s3
})),
WhenNotMatchedBySource(delete(target)),
]
)
connection.execute(query)
def test_cte_in_source(connection, target, source):
cte = select(source.c.s1).cte("cte")
sub = select(cte.c.s1).select_from(cte).subquery()
query = MergeInto(
target=target,
source=sub,
onclause=target.c.t1 == sub.c.s1,
when_clauses=[
WhenMatched(update(target).values({
target.c.t1: sub.c.s1
})),
]
)
connection.execute(query)
def test_example_1():
expected = """\
MERGE INTO dataset.DetailedInventory AS T
USING dataset.Inventory AS S
ON T.product = S.product
WHEN NOT MATCHED BY TARGET AND T.quantity < 20 THEN
INSERT (product, quantity, supply_constrained, comments)
VALUES (S.product, S.quantity, true, ARRAY<STRUCT<created DATE, comment STRING>>[(DATE('2016-01-01'), 'comment1')])
WHEN NOT MATCHED BY TARGET THEN
INSERT (product, quantity, supply_constrained)
VALUES (S.product, S.quantity, false)
"""
T = detailed_inventory.alias("T")
S = inventory.alias("S")
# without a meaningful variable name this is more confusing than anything really,
# but it lets me check this sort of factoring out works too.
default_insert = insert(T).values({
T.c.product: S.c.product,
T.c.quantity: S.c.quantity,
T.c.supply_constrained: literal(False),
})
query = MergeInto(
target=T,
source=S,
onclause=T.c.product == S.c.product,
when_clauses=[
WhenNotMatched(
default_insert.values({
T.c.supply_constrained:
literal(True),
# can't figure this one out without cheating a bit
T.c.comments:
text(
"ARRAY<STRUCT<created DATE, comment STRING>>[(DATE('2016-01-01'), 'comment1')]"
)
}),
condition=T.c.quantity < 20),
WhenNotMatched(default_insert)
])
return query, expected