Skip to content

Support calling SAFE_CAST via sqlalchemy #1099

Open
@suhas-dc

Description

@suhas-dc

Is your feature request related to a problem? Please describe.
I could not find a way to use SAFE_CAST which returns NULL if the cast fails rather than failing the query.

Describe the solution you'd like
A custom safe_cast function provided by the library that compiles to SAFE_CAST.

Describe alternatives you've considered
I can implement a custom safe_cast function within my codebase. But, I would have to subclass sqlalchemy.sql.Cast (say SafeCast) and override the visit_cast function in the bigquery dialect to compile SafeCast into SAFE_CAST(...) instead of `CAST(...).

class SafeCast(Cast):
    pass

def safe_cast(col: ColumnElement, type_: TypeEngine)) -> SafeCast:
    return SafeCast(col, type_)

# Define custom Bigquery dialect and compiler with
def visit_cast(self, cast, **kwargs):
    if isinstance(cast, SafeCast):
      return "SAFE_CAST(%s AS %s)" % (
          cast.clause._compiler_dispatch(self, **kwargs),
          cast.typeclause._compiler_dispatch(self, **kwargs),
      )
    else:
      return super().visit_cast(cast, **kwargs)

Additional context
Add any other context or screenshots about the feature request here.

Metadata

Metadata

Assignees

Labels

api: bigqueryIssues related to the googleapis/python-bigquery-sqlalchemy API.

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions