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 = 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):
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):
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):
prefix = BQLang[self.prefix].partial_eval()
if is_literal(prefix):
value = BQLang[self.value].partial_eval().to_bq(schema)[0].sql.s
prefix = prefix.to_bq(schema)[0].sql.s
if "%" in prefix or "_" in prefix:
for r in "\\_%":
prefix = prefix.replaceAll(r, "\\" + r)
sql = ConcatSQL(value, SQL_LIKE, prefix, SQL_ESCAPE, SQL("\\"))
else:
sql = ConcatSQL(value, SQL_LIKE, prefix)
return wrap([{"name": ".", "sql": {"b": sql}}])
else:
return (SqlEqOp(
[SqlSubstrOp([self.value, ONE,
LengthOp(prefix)]),
prefix]).partial_eval().to_bq())
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}}])
BQLang = Language("BQLang")
_sql_operators = {
# (operator, zero-array default value) PAIR
"add": (SQL_PLUS, SQL_ZERO),
"sum": (SQL_PLUS, SQL_ZERO),
"mul": (SQL_STAR, SQL_ONE),
"sub": (SQL(" - "), None),
"div": (SQL(" / "), None),
"exp": (SQL(" ** "), None),
"mod": (SQL(" % "), None),
"gt": (SQL(" > "), None),
"gte": (SQL(" >= "), None),
"lte": (SQL(" <= "), None),
"lt": (SQL_LT, None),
}
json_type_to_bq_type = {
IS_NULL: "0",
BOOLEAN: "b",
NUMBER: "n",
STRING: "s",
OBJECT: "j",