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}}])