Skip to content

Latest commit

 

History

History
982 lines (965 loc) · 16.9 KB

README.md

File metadata and controls

982 lines (965 loc) · 16.9 KB

cel2sql

cel2sql converts CEL (Common Expression Language) to SQL condition. It is specifically targeting BigQuery standard SQL.

Usage

import (
    "context"
    "fmt"
    
    "cloud.google.com/go/bigquery"
    "github.com/cockscomb/cel2sql"
    "github.com/cockscomb/cel2sql/bq"
    "github.com/cockscomb/cel2sql/sqltypes"
    "github.com/google/cel-go/cel"
    "github.com/google/cel-go/checker/decls"
)

// BigQuery table metadata
var client *bigquery.Client = ...
tableMetadata, _ := client.Dataset("your_dataset").Table("employees").Metadata(context.TODO())

// Prepare CEL environment
env, _ := cel.NewEnv(
    cel.CustomTypeProvider(bq.NewTypeProvider(map[string]bigquery.Schema{
        "Employee": tableMetadata.Schema,
    })),
    sqltypes.SQLTypeDeclarations,
    cel.Declarations(
        decls.NewVar("employee", decls.NewObjectType("Employee")),
    ),
)

// Convert CEL to SQL
ast, _ := env.Compile(`employee.name == "John Doe" && employee.hired_at >= current_timestamp() - duration("24h")`)
sqlCondition, _ := cel2sql.Convert(ast)

fmt.Println(sqlCondition) // `employee`.`name` = "John Doe" AND `employee`.`hired_at` >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)

Type Conversion

CEL Type BigQuery Standard SQL Data Type
int INT64
uint Unsupported but treated as INT64
double FLOAT64
bool BOOL
string STRING
bytes BYTES
list ARRAY
map STRUCT
null_type NULL
timestamp TIMESTAMP
duration INTERVAL

Supported CEL Operators/Functions

Symbol Type SQL
!_ (bool) -> bool NOT bool
-_ (int) -> int -int
(double) -> double -double
_!=_ (A, A) -> bool A != A
(bool, bool) -> bool bool IS NOT bool
(A, null) -> bool A IS NOT NULL
_%_ (int, int) -> int MOD(int, int)
_&&_ (bool, bool) -> bool bool AND bool
_*_ (int, int) -> int int * int
(double, double) -> double double * double
_+_ (int, int) -> int int + int
(double, double) -> double double + double
(string, string) -> string string || string
(bytes, bytes) -> bytes bytes || bytes
(list(A), list(A)) -> list(A) list(A) || list(A)
(google.protobuf.Timestamp, google.protobuf.Duration) -> google.protobuf.Timestamp TIMESTAMP_ADD(timestamp, INTERVAL duration date_part)
(google.protobuf.Duration, google.protobuf.Timestamp) -> google.protobuf.Timestamp TIMESTAMP_ADD(timestamp, INTERVAL duration date_part)
_-_ (int, int) -> int int - int
(double, double) -> double double - double
(google.protobuf.Timestamp, google.protobuf.Duration) -> google.protobuf.Timestamp TIMESTAMP_SUB(timestamp, INTERVAL duration date_part)
_/_ (int, int) -> int int / int
(double, double) -> double double / double
_<=_ (bool, bool) -> bool bool <= bool
(int, int) -> bool int <= int
(double, double) -> bool double <= double
(string, string) -> bool string <= string
(bytes, bytes) -> bool bytes <= bytes
(google.protobuf.Timestamp, google.protobuf.Timestamp) -> bool timestamp <= timestamp
_<_ (bool, bool) -> bool bool < bool
(int, int) -> bool int < int
(double, double) -> bool double < double
(string, string) -> bool string < string
(bytes, bytes) -> bool bytes < bytes
(google.protobuf.Timestamp, google.protobuf.Timestamp) -> bool timestamp < timestamp
_==_ (A, A) -> bool A = A
(bool, bool) -> bool A IS A
(A, null) -> bool A IS NULL
_>=_ (bool, bool) -> bool bool >= bool
(int, int) -> bool int >= int
(double, double) -> bool double >= double
(string, string) -> bool string >= string
(bytes, bytes) -> bool bytes >= bytes
(google.protobuf.Timestamp, google.protobuf.Timestamp) -> bool timestamp >= timestamp
_>_ (bool, bool) -> bool bool > bool
(int, int) -> bool int > int
(double, double) -> bool double > double
(string, string) -> bool string > string
(bytes, bytes) -> bool bytes > bytes
(google.protobuf.Timestamp, google.protobuf.Timestamp) -> bool timestamp > timestamp
_?_:_ (bool, A, A) -> A IF(bool, A, A)
_[_] (list(A), int) -> A list[OFFSET(int)]
(map(A, B), A) -> B map.`A`
in (A, list(A)) -> bool A IN UNNEST(list)
_||_ (bool, bool) -> bool bool OR bool
bool (int) -> bool CAST(int AS BOOL)
(string) -> bool CAST(string AS BOOL)
bytes (string) -> bytes CAST(stringAS BYTES)
contains string.(string) -> bool INSTR(string, string) != 0
double (int) -> double CAST(int AS FLOAT64)
(string) -> double CAST(string AS FLOAT64)
duration (string) -> google.protobuf.Duration INTERVAL duration date_part
endsWith string.(string) -> bool ENDS_WITH(string, string)
getDate google.protobuf.Timestamp.() -> int EXTRACT(DAY FROM timestamp)
google.protobuf.Timestamp.(string) -> int EXTRACT(DAY FROM timestamp AT string)
getDayOfMonth google.protobuf.Timestamp.() -> int EXTRACT(DAY FROM timestamp) - 1
google.protobuf.Timestamp.(string) -> int EXTRACT(DAY FROM timestamp AT string) - 1
getDayOfWeek google.protobuf.Timestamp.() -> int EXTRACT(DAYOFWEEK FROM timestamp) - 1
google.protobuf.Timestamp.(string) -> int EXTRACT(DAYOFWEEK FROM timestamp AT string) - 1
getDayOfYear google.protobuf.Timestamp.() -> int EXTRACT(DAYOFYEAR FROM timestamp) - 1
google.protobuf.Timestamp.(string) -> int EXTRACT(DAYOFYEAR FROM timestamp AT string) - 1
getFullYear google.protobuf.Timestamp.() -> int EXTRACT(YEAR FROM timestamp)
google.protobuf.Timestamp.(string) -> int EXTRACT(YEAR FROM timestamp AT string)
getHours google.protobuf.Timestamp.() -> int EXTRACT(HOUR FROM timestamp)
google.protobuf.Timestamp.(string) -> int EXTRACT(HOUR FROM timestamp AT string)
getMilliseconds google.protobuf.Timestamp.() -> int EXTRACT(MILLISECOND FROM timestamp)
google.protobuf.Timestamp.(string) -> int EXTRACT(MILLISECOND FROM timestamp AT string)
getMinutes google.protobuf.Timestamp.() -> int EXTRACT(MINUTE FROM timestamp)
google.protobuf.Timestamp.(string) -> int EXTRACT(MINUTE FROM timestamp AT string)
getMonth google.protobuf.Timestamp.() -> int EXTRACT(MONTH FROM timestamp) - 1
google.protobuf.Timestamp.(string) -> int EXTRACT(MONTH FROM timestamp AT string) - 1
getSeconds google.protobuf.Timestamp.() -> int EXTRACT(SECOND FROM timestamp)
google.protobuf.Timestamp.(string) -> int EXTRACT(SECOND FROM timestamp AT string)
int (bool) -> int CAST(bool AS INT64)
(double) -> int CAST(double AS INT64)
(string) -> int CAST(string AS INT64)
(google.protobuf.Timestamp) -> int UNIX_SECONDS(timestamp)
matches string.(string) -> bool REGEXP_CONTAINS(string, string)
size (string) -> int CHAR_LENGTH(string)
(bytes) -> int BYTE_LENGTH(bytes)
(list(A)) -> int ARRAY_LENGTH(list)
startsWith string.(string) -> bool STARTS_WITHstring, string)
string (bool) -> string CAST(bool AS STRING)
(int) -> string CAST(int AS STRING)
(double) -> string CAST(double AS STRING)
(bytes) -> string CAST(bytes AS STRING)
(timestamp) -> string CAST(timestamp AS STRING)
timestamp (string) -> google.protobuf.Timestamp TIMESTAMP(string)

Standard SQL Types/Functions

cel2sql supports time related types bellow.

  • DATE
  • TIME
  • DATETIME

cel2sql contains time related functions bellow.

  • current_date()
  • current_time()
  • current_datetime()
  • current_timestamp()
  • interval(N, date_part)