def to_bq(self, schema, not_null=False, boolean=False):
lhs = BQLang[self.lhs].partial_eval()
rhs = BQLang[self.rhs].partial_eval()
lhs_sql = lhs.to_bq(schema, not_null=True)
rhs_sql = rhs.to_bq(schema, not_null=True)
if is_literal(rhs) and lhs_sql[0].sql.b != None and rhs.value in ("T",
"F"):
rhs_sql = BooleanOp(rhs).to_bq(schema)
if is_literal(lhs) and rhs_sql[0].sql.b != None and lhs.value in ("T",
"F"):
lhs_sql = BooleanOp(lhs).to_bq(schema)
if len(lhs_sql) != len(rhs_sql):
Log.error("lhs and rhs have different dimensionality!?")
acc = []
for l, r in zip(lhs_sql, rhs_sql):
for t in "bsnj":
if r.sql[t] == None:
if l.sql[t] == None:
pass
else:
acc.append(ConcatSQL(l.sql[t], SQL_IS_NULL))
elif l.sql[t] == None:
acc.append(ConcatSQL(r.sql[t], SQL_IS_NULL))
else:
acc.append(
ConcatSQL(sql_iso(l.sql[t]), SQL_EQ,
sql_iso(r.sql[t])))
if not acc:
return FALSE.to_bq(schema)
else:
return wrap([{"name": ".", "sql": {"b": JoinSQL(SQL_OR, acc)}}])
def to_bq(self, schema, not_null=False, boolean=False):
value = self.value.to_bq(schema, not_null=True)[0].sql.s
start = self.start.to_bq(schema, not_null=True)[0].sql.n
if self.length is NULL:
sql = "SUBSTR" + sql_iso(sql_list([value, start]))
else:
length = self.length.to_bq(schema, not_null=True)[0].sql.n
sql = "SUBSTR" + sql_iso(sql_list([value, start, length]))
return wrap([{"name": ".", "sql": sql}])
def to_bq(self, schema, not_null=False, boolean=False):
lhs = BQLang[self.lhs].to_bq(schema)
rhs = BQLang[self.rhs].to_bq(schema)
if lhs.type in STRUCT or rhs.type in STRUCT:
Log.error("not supported yet")
return BQLScript(data_type=BOOLEAN,
expr=ConcatSQL(sql_iso(lhs), SQL_EQ, sql_iso(rhs)),
frum=self,
miss=FALSE,
many=False,
schema=schema)
def _inequality_to_bq(self, schema, not_null=False, boolean=False, many=True):
op, identity = _sql_operators[self.op]
lhs = NumberOp(self.lhs).partial_eval().to_bq(schema, not_null=True)
rhs = NumberOp(self.rhs).partial_eval().to_bq(schema, not_null=True)
sql = sql_iso(lhs) + op + sql_iso(rhs)
output = BQLScript(
data_type=BOOLEAN,
expr=sql,
frum=self,
miss=OrOp([self.lhs.missing(), self.rhs.missing()]),
schema=schema,
)
return output
def to_bq(self, schema, not_null=False, boolean=False):
acc = []
for term in self.terms:
sqls = BQLang[term].to_bq(schema)
if len(sqls) > 1:
acc.append(SQL_TRUE)
else:
for t, v in sqls[0].sql.items():
if t in ["b", "s", "n"]:
acc.append(
ConcatSQL(
SQL_CASE,
SQL_WHEN,
sql_iso(v),
SQL_IS_NULL,
SQL_THEN,
SQL_ZERO,
SQL_ELSE,
SQL_ONE,
SQL_END,
))
else:
acc.append(SQL_TRUE)
if not acc:
return wrap([{}])
else:
return wrap([{"nanme": ".", "sql": {"n": SQL("+").join(acc)}}])
def to_bq(self, schema, not_null=False, boolean=False):
value = self.value.to_bq(schema, not_null=True)[0].sql.s
find = self.find.to_bq(schema, not_null=True)[0].sql.s
return wrap(
[{"name": ".", "sql": {"n": "INSTR" + sql_iso(sql_list([value, find]))}}]
)
def to_bq(self, schema, not_null=False, boolean=False):
value = BQLang[self.value].partial_eval().to_bq(schema)[0].sql.s
find = BQLang[self.find].partial_eval().to_bq(schema)[0].sql.s
start = BQLang[self.start].partial_eval().to_bq(schema)[0].sql.n
default = coalesce(
BQLang[self.default].partial_eval().to_bq(schema)[0].sql.n, SQL_NULL
)
if start.sql != SQL_ZERO.sql.strip():
value = NotRightOp([self.value, self.start]).to_bq(schema)[0].sql.s
index = "INSTR" + sql_iso(sql_list([value, find]))
sql = (
SQL_CASE
+ SQL_WHEN
+ index
+ SQL_THEN
+ index
+ SQL("-1+")
+ start
+ SQL_ELSE
+ default
+ SQL_END
)
return wrap([{"name": ".", "sql": {"n": sql}}])
def to_bq(self, schema, not_null=False, boolean=False):
default = self.default.to_bq(schema)
if len(self.terms) == 0:
return default
default = coalesce(default[0].sql.s, SQL_NULL)
sep = BQLang[self.separator].to_bq(schema)[0].sql.s
acc = []
for t in self.terms:
t = BQLang[t]
missing = t.missing().partial_eval()
term = t.to_bq(schema, not_null=True)[0].sql
if term.s:
term_sql = term.s
elif term.n:
term_sql = "cast(" + term.n + " as text)"
else:
term_sql = (SQL_CASE + SQL_WHEN + term.b + SQL_THEN +
quote_value("true") + SQL_ELSE +
quote_value("false") + SQL_END)
if isinstance(missing, TrueOp):
acc.append(SQL_EMPTY_STRING)
elif missing:
acc.append(
SQL_CASE + SQL_WHEN +
sql_iso(missing.to_bq(schema, boolean=True)[0].sql.b) +
SQL_THEN + SQL_EMPTY_STRING + SQL_ELSE +
sql_iso(sql_concat_text([sep, term_sql])) + SQL_END)
else:
acc.append(sql_concat_text([sep, term_sql]))
expr_ = "SUBSTR" + sql_iso(
sql_list([
sql_concat_text(acc),
LengthOp(self.separator).to_bq(schema)[0].sql.n + SQL("+1"),
]))
return BQLScript(
expr=expr_,
data_type=STRING,
frum=self,
miss=self.missing(),
many=False,
schema=schema,
)
def to_bq(self, schema, not_null=False, boolean=False):
v = self.value.to_bq(schema)[0].sql
return wrap([{
"name": ".",
"sql": {
"n": "UNIX_TIMESTAMP" + sql_iso(v.n)
}
}])
def basic_multiop_to_bq(self,
schema,
not_null=False,
boolean=False,
many=False):
op, identity = _sql_operators[self.op.split("basic.")[1]]
sql = op.join(
sql_iso(BQLang[t].to_bq(schema)[0].sql.n) for t in self.terms)
return wrap([{"name": ".", "sql": {"n": sql}}])
def to_bq(self, schema, not_null=False, boolean=False):
not_expr = self.lang[self.term].to_bq(schema)
return BQLScript(
expr=ConcatSQL(SQL_NOT, sql_iso(not_expr)),
data_type=BOOLEAN,
frum=self,
miss=FALSE,
schema=schema,
)
def _binaryop_to_bq(self, schema, not_null=False, boolean=False, many=True):
op, identity = _sql_operators[self.op]
lhs = NumberOp(self.lhs).partial_eval().to_bq(schema,
not_null=True)[0].sql.n
rhs = NumberOp(self.rhs).partial_eval().to_bq(schema,
not_null=True)[0].sql.n
script = sql_iso(lhs) + op + sql_iso(rhs)
if not_null:
sql = script
else:
missing = OrOp([self.lhs.missing(), self.rhs.missing()]).partial_eval()
if missing is FALSE:
sql = script
else:
sql = ("CASE WHEN " +
missing.to_bq(schema, boolean=True)[0].sql.b +
" THEN NULL ELSE " + script + " END")
return wrap([{"name": ".", "sql": {"n": sql}}])
def to_bq(self, schema, not_null=False, boolean=False):
terms = [
BQLang[t].partial_eval().to_bq(schema)[0].sql.n for t in self.terms
]
return wrap([{
"name": ".",
"sql": {
"n": "min" + sql_iso((sql_list(terms)))
}
}])
def to_bq(self, schema, not_null=False, boolean=False):
test = BQLang[self.term].missing().to_bq(schema, boolean=True)[0].sql.b
value = BQLang[self.term].to_bq(schema, not_null=True)[0].sql
acc = []
for t, v in value.items():
if t == "b":
acc.append(SQL_CASE + SQL_WHEN + sql_iso(test) + SQL_THEN +
SQL_NULL + SQL_WHEN + sql_iso(v) + SQL_THEN +
"'true'" + SQL_ELSE + "'false'" + SQL_END)
elif t == "s":
acc.append(v)
else:
acc.append("RTRIM(RTRIM(CAST" +
sql_iso(v + " as TEXT), " + quote_value("0")) +
", " + quote_value(".") + ")")
if not acc:
return wrap([{}])
elif len(acc) == 1:
return wrap([{"name": ".", "sql": {"s": acc[0]}}])
else:
return wrap([{"name": ".", "sql": {"s": sql_coalesce(acc)}}])
def to_bq(self, schema, not_null=False, boolean=False):
value = self.value.to_bq(schema)[0].sql.s
find = self.find.to_bq(schema)[0].sql.s
start = self.start
if isinstance(start, Literal) and start.value == 0:
return wrap(
[
{
"name": ".",
"sql": {"n": "INSTR" + sql_iso(value + "," + find) + "-1"},
}
]
)
else:
start_index = start.to_bq(schema)[0].sql.n
found = "INSTR(SUBSTR" + sql_iso(value + "," + start_index + "+1)," + find)
return wrap(
[
{
"name": ".",
"sql": {
"n": (
SQL_CASE
+ SQL_WHEN
+ found
+ SQL_THEN
+ found
+ "+"
+ start_index
+ "-1"
+ SQL_ELSE
+ "-1"
+ SQL_END
)
},
}
]
)
def to_bq(self, schema, not_null=False, boolean=False):
lhs = BQLang[self.lhs].to_bq(schema)[0].sql.n
rhs = BQLang[self.rhs].to_bq(schema)[0].sql.n
d = BQLang[self.default].to_bq(schema)[0].sql.n
if lhs and rhs:
if d == None:
return wrap([{
"name": ".",
"sql": {
"n": sql_iso(lhs) + " / " + sql_iso(rhs)
}
}])
else:
return wrap([{
"name": ".",
"sql": {
"n":
sql_coalesce([sql_iso(lhs) + " / " + sql_iso(rhs), d])
},
}])
else:
return Null
def to_bq(self, schema, not_null=False, boolean=False):
lhs = BQLang[self.lhs].to_bq(schema)[0].sql.n
rhs = BQLang[self.rhs].to_bq(schema)[0].sql.n
modifier = lhs + " < 0 "
if text(rhs).strip() != "1":
floor = "CAST" + sql_iso(lhs + "/" + rhs + " AS INTEGER")
sql = sql_iso(sql_iso(floor) + "-" + sql_iso(modifier)) + "*" + rhs
else:
floor = "CAST" + sql_iso(lhs + " AS INTEGER")
sql = sql_iso(floor) + "-" + sql_iso(modifier)
return wrap([{"name": ".", "sql": {"n": sql}}])
def to_bq(self, schema, not_null=False, boolean=False):
term = BQLang[self.term].partial_eval()
if is_literal(term):
from jx_bigquery.sql import quote_value
val = term.value
if isinstance(val, text):
sql = quote_value(len(val))
elif isinstance(val, (float, int)):
sql = quote_value(len(value2json(val)))
else:
return Null
else:
value = term.to_bq(schema, not_null=not_null)[0].sql.s
sql = ConcatSQL(SQL("LENGTH"), sql_iso(value))
return wrap([{"name": ".", "sql": {"n": sql}}])
def to_bq(self, schema, not_null=False, boolean=False):
value = BQLang[self.value].to_bq(schema, not_null=True)[0].sql.s
start = (
AddOp([self.start, Literal(1)])
.partial_eval()
.to_bq(schema, not_null=True)[0]
.sql.n
)
length = (
SubOp([self.end, self.start])
.partial_eval()
.to_bq(schema, not_null=True)[0]
.sql.n
)
sql = "SUBSTR" + sql_iso(value + "," + start + ", " + length)
return wrap([{"name": ".", "sql": {"s": sql}}])
def to_bq(self, schema, not_null=False, boolean=False):
return wrap(
[
{
"name": ".",
"sql": {
"b": JoinSQL(
SQL_OR,
[
sql_iso(BQLang[t].to_bq(schema, boolean=True)[0].sql.b)
for t in self.terms
],
)
},
}
]
)
def sql(self):
self.miss = self.miss.partial_eval()
if self.miss is TRUE:
return wrap({json_type_to_bq_type[self.data_type]: SQL_NULL})
elif self.miss is FALSE:
return wrap({json_type_to_bq_type[self.data_type]: self.expr})
else:
return wrap({
json_type_to_bq_type[self.data_type]:
ConcatSQL(
SQL_CASE,
SQL_WHEN,
SQL_NOT,
sql_iso(BQLang[self.miss].to_bq(self.schema)),
SQL_THEN,
self.expr,
SQL_END,
)
})
def to_bq(self, schema, not_null=False, boolean=False):
if not self.expr:
return BQLScript(
expr=SQL_TRUE,
data_type=BOOLEAN,
frum=self,
miss=FALSE,
schema=schema,
)
else:
expr = BQLang[self.expr].to_bq(schema)
prefix = BQLang[self.prefix].to_bq(schema)
return BQLScript(
expr=ConcatSQL(sql_iso(expr), SQL_IS_NOT_NULL, SQL_AND,
sql_call("STARTS_WITH", expr, prefix)),
data_type=BOOLEAN,
frum=self,
miss=FALSE,
schema=schema,
)
def to_bq(self, schema, not_null=False, boolean=False):
value = BQLang[self.expr].partial_eval()
missing_value = value.missing().partial_eval()
if not is_op(missing_value, MissingOp):
return missing_value.to_bq(schema)
value_sql = value.to_bq(schema)
if value_sql.type == OBJECT:
return FALSE.to_bq(schema)
return BQLScript(
data_type=BOOLEAN,
expr=ConcatSQL(sql_iso(value_sql), SQL_IS_NULL),
frum=self,
miss=FALSE,
many=False,
schema=schema
)
def to_bq(self, schema, not_null=False, boolean=False):
v = self.value.to_bq(schema, not_null=True)[0].sql.s
r = self.length.to_bq(schema, not_null=True)[0].sql.n
l = "max(0, length" + sql_iso(v) + "-max(0, " + r + "))"
expr = "SUBSTR" + sql_iso(v + ", 1, " + l)
return wrap([{"name": ".", "sql": {"s": expr}}])
