README
¶
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.
DATETIMEDATETIME
cel2sql contains time related functions bellow.
current_date()current_time()current_datetime()current_timestamp()interval(N, date_part)
Documentation
¶
Source Files
Directories
Click to show internal directories.
Click to hide internal directories.