def test_dag6(self):
"""
makefile with 2 input with external table
"""
sql1 = """
create or replace table `p.d.t1` as
select * from `p.d.t3`
"""
sql2 = """
create or replace table `p.d.t2` as
select * from `p.d.t1`
"""
ds = [
bqrun.Dependency(*bqrun.parse(sql1), "1.sql"),
bqrun.Dependency(*bqrun.parse(sql2), "2.sql")
]
dag = bqrun.Dag(ds)
sio = StringIO()
dag.create_makefile(sio)
mf_act = sio.getvalue()
mf_exp = """
.PHONY: all
all: done.1 done.2
done.1: 1.sql
\tcat 1.sql | bq query
\ttouch $@
done.2: 2.sql done.1
\tcat 2.sql | bq query
\ttouch $@
"""
self.assertEqual(remove_blank(mf_act), remove_blank(mf_exp))
self.assertEqual(len(dag.orig_deps), 2)
self.assertEqual(dag.orig_deps[0].sources, ["p.d.t3"])
def test_dag4(self):
"""
makefile with 2 input
"""
sql1 = """
create or replace table `p.d.t1` as
select * from unnest([1,2,3])
"""
sql2 = """
create or replace table `p.d.t2` as
select * from `p.d.t1`
"""
ds = [
bqrun.Dependency(*bqrun.parse(sql1), "1.sql"),
bqrun.Dependency(*bqrun.parse(sql2), "2.sql")
]
dag = bqrun.Dag(ds)
sio = StringIO()
dag.create_makefile(sio)
mf_act = sio.getvalue()
mf_exp = """
.PHONY: all
all: done.1 done.2
done.1: 1.sql
\tcat 1.sql | bq query
\ttouch $@
done.2: 2.sql done.1
\tcat 2.sql | bq query
\ttouch $@
"""
self.assertEqual(remove_blank(mf_act), remove_blank(mf_exp))
def test_dag1(self):
"""
build dag of 2 targets
"""
sql1 = """
create or replace table `p.d.t1` as
select * from unnest([1,2,3])
"""
sql2 = """
create or replace table `p.d.t2` as
select * from `p.d.t1`
"""
ds = [
bqrun.Dependency(*bqrun.parse(sql1), "1.sql"),
bqrun.Dependency(*bqrun.parse(sql2), "2.sql")
]
dag = bqrun.Dag(ds)
self.assertEqual(set(dag.targets), set(["done.1", "done.2"]))
self.assertEqual(set(dag.targets["done.1"]), set(["1.sql"]))
self.assertEqual(set(dag.targets["done.2"]), set(["2.sql", "done.1"]))
def test_dag5(self):
"""
read tables that is not created by other sql
"""
sql1 = """
create or replace table `p.d.t1` as
select * from `x`
"""
ds = [bqrun.Dependency(*bqrun.parse(sql1), "1.sql")]
dag = bqrun.Dag(ds)
self.assertEqual(set(dag.targets), set(["done.1"]))
self.assertEqual(set(dag.targets["done.1"]), {"1.sql"})
def test_parse(self):
"""
simple select
"""
sql = """
select
*
from
`p.d.t`
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, [])
self.assertEqual(s, ["p.d.t"])
def test_parse2(self):
"""
select from join
"""
sql = """
select
*
from
`p.d.t1` left join `p.d.t2`
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, [])
self.assertEqual(set(s), set(["p.d.t1", "p.d.t2"]))
def test_dag2(self):
"""
select table which created in the same file
"""
sql1 = """
create or replace table `p.d.t1` as
select * from unnest([1,2,3]);
create or replace table `p.d.t2` as
select * from `p.d.t1`
"""
ds = [bqrun.Dependency(*bqrun.parse(sql1), "1.sql")]
dag = bqrun.Dag(ds)
self.assertEqual(set(dag.targets), set(["done.1"]))
self.assertEqual(set(dag.targets["done.1"]), set(["1.sql"]))
def test_parse6(self):
"""
nested query
"""
sql = """
create table `p.d.t3` as
select
*
from
(select * from `p.d.t1`)
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, ["p.d.t3"])
self.assertEqual(set(s), set(["p.d.t1"]))
def test_parse5(self):
"""
create sentence
"""
sql = """
create table `p.d.t3` as
select
*
from
`p.d.t1` left join `p.d.t2`
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, ["p.d.t3"])
self.assertEqual(set(s), set(["p.d.t2", "p.d.t1"]))
def test_parse3(self):
"""
create or replace with join
"""
sql = """
create or replace table `p.d.t3` as
select
*
from
`p.d.t1` left join `p.d.t2`
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, ["p.d.t3"])
self.assertEqual(set(s), set(["p.d.t1", "p.d.t2"]))
def test_parse7(self):
"""
with clause
"""
sql = """
create table `p.d.t3` as
with temp1 as
(select * from select * from `p.d.t1`),
temp2 as
(select * from select * from `p.d.t2`)
select * from temp1 left join temp2
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, ["p.d.t3"])
self.assertEqual(set(s), set(["p.d.t1", "p.d.t2"]))
def test_parse8(self):
"""
cluster by and partition by
"""
sql = """
create or replace table `p.d.t4`
partition by date(timestamp)
cluster by uuid
options(require_partition_filter=true) as
select
timestamp,
uuid
from
`p.d.t5`
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, ["p.d.t4"])
self.assertEqual(set(s), set(["p.d.t5"]))
def test_parse10(self):
"""
declare set
"""
sql = """
declare pi float64 default acos(-1);
declare pi2 float64;
set pi2 = 2 * pi;
create or replace table `p.d.t4` as
select
pi as pi,
pi2 as pi2
from
`p.d.t5`
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, ["p.d.t4"])
self.assertEqual(set(s), set(["p.d.t5"]))
def test_parse4(self):
"""
multiple sentences
"""
sql = """
create or replace table `p.d.t3` as
select
*
from
`p.d.t1` left join `p.d.t2`;
create or replace table `p.d.t4` as
select
*
from
`p.d.t1` left join `p.d.t2`;
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, ["p.d.t3", "p.d.t4"])
self.assertEqual(set(s), set(["p.d.t2", "p.d.t1"]))
def test_parse9(self):
"""
temporary function
"""
sql = """
create function pi() as (acos(-1));
create or replace function pi2() as (acos(-1));
create temporary function pi3() as (acos(-1));
create temp function pi4() as (acos(-1));
create or replace table `p.d.t4` as
select
timestamp,
uuid
from
`p.d.t5`
"""
t, s = bqrun.parse(sql)
self.assertEqual(t, ["p.d.t4"])
self.assertEqual(set(s), set(["p.d.t5"]))