Skip to content

cockscomb/cel2sql

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

50 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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)

About

CEL to SQL condition

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages