Bodo SQL¶

Bodo SQL provides high performance and scalable SQL query execution using Bodo’s HPC capabilities and optimizations. It also provides native Python/SQL integration as well as SQL to Pandas conversion for the first time. BodoSQL is in early stages and its capabilities are expanding rapidly.

Getting Started¶

Installation¶

Bodo SQL is currently in an Alpha release. Please us if you are interested in becoming a trial user.

Using Bodo SQL¶

The example below demonstrates using Bodo SQL in Python programs. It loads data into a dataframe, runs a SQL query on the data, and runs Python/Pandas code on query results:

import pandas as pd
import bodo
import bodosql

@bodo.jit
def f(filename):
    df1 = pd.read_parquet(filename)
    bc = bodosql.BodoSQLContext({"table1": df1})
    df2 = bc.sql("SELECT A FROM table1 WHERE B > 4")
    print(df2.A.sum())

f("my_data.pq")

This program is fully type checked, optimized and parallelized by Bodo end-to-end. BodoSQLContext creates a SQL environment with tables created from dataframes. BodoSQLContext.sql() runs a SQL query and returns the results as a dataframe. BodoSQLContext can be used outside Bodo JIT functions if necessary as well.

You can run this example by creating my_data.pq:

import pandas as pd
import numpy as np

NUM_GROUPS = 30
NUM_ROWS = 20_000_000
df = pd.DataFrame({
    "A": np.arange(NUM_ROWS) % NUM_GROUPS,
    "B": np.arange(NUM_ROWS)
})
df.to_parquet("my_data.pq")

SQL to Pandas Conversion¶

Bodo SQL can generate Pandas code from SQL queries automatically. To view the code generated, you can use the convert_to_pandas method, which returns the generated code as a string. For example:

print(bc.convert_to_pandas("SELECT A FROM table1 WHERE B > 4"))

outputs:

def impl(table1):
    df1 = table1[(table1["B"] > np.int32(4))]
    df2 = pd.DataFrame({"A": df1["A"], })
    return df2

Using Python/Pandas code instead of SQL can simplify existing data science applications and improve code maintenance.

Note: convert_to_pandas can only be executed outside Bodo JIT functions.

Aliasing¶

In all but the most trivial cases, Bodo SQL generates internal names to avoid conflicts in the intermediate dataframes. By default, Bodo SQL does not rename the columns for the final output of a query using a consistent approach. For example the query:

bc.sql("SELECT SUM(A) FROM table1 WHERE B > 4")

Results in an output column named $EXPR0. To reliably reference this column later in your code, we highly recommend using aliases for all columns that are the final outputs of a query, such as:

bc.sql("SELECT SUM(A) as sum_col FROM table1 WHERE B > 4")

Note: BodoSQL supports using aliases generated in SELECT inside GROUP BY and HAVING in the same query, but you cannot do so with WHERE.

Supported Operations¶

We currently support the following SQL query statements and clauses with Bodo SQL, and are continuously adding support towards completeness. Note that Bodo SQL ignores casing of keywords, and column and table names. Therefore, select a from table1 is treated the same as SELECT A FROM TABLE1.

SELECT
The SELECT statement is used to select data in the form of columns. The data returned from Bodo SQL is stored in a dataframe. Example usage:
SELECT <COLUMN_NAMES> FROM <TABLE_NAME>
For Example:
SELECT A FROM TABLE1
The SELECT DISTINCT statement is used to return only distinct (different) values:
SELECT DISTINCT <COLUMN_NAMES> FROM <TABLE_NAME>
DISTINCT can be used in a SELECT statement or inside an aggregate function. For example:
SELECT DISTINCT A FROM TABLE1 SELECT COUNT DISTINCT A FROM TABLE1
WHERE
The WHERE clause on columns can be used to filter records that satisfy specific conditions:
SELECT <COLUMN_NAMES> FROM <TABLE_NAME> WHERE <CONDITION>
For Example:
SELECT A FROM TABLE1 WHERE B > 4
ORDER BY
The ORDER BY keyword sorts the resulting dataframe in ascending or descending order. By default, it sorts the records in ascending order. For descending order, the DESC keyword can be used:
SELECT <COLUMN_NAMES> FROM <TABLE_NAME> ORDER BY <ORDERED_COLUMN_NAMES> ASC|DESC
For Example:
SELECT A, B FROM TABLE1 ORDER BY B, A DESC

LIMIT

Bodo SQL supports the LIMIT keyword to select a limited number of rows:
SELECT <COLUMN_NAMES>
FROM <TABLE_NAME>
WHERE <CONDITION>
LIMIT <NUMBER>
For Example:
SELECT A FROM TABLE1 LIMIT 5

BETWEEN
The BETWEEN operator selects values within a given range. The values can be numbers, text, or datetimes. The BETWEEN operator is inclusive: begin and end values are included:
SELECT <COLUMN_NAMES> FROM <TABLE_NAME> WHERE <COLUMN_NAME> BETWEEN <VALUE1> AND <VALUE2>
For example:
Select A from Table1 where A between 10 and 100
JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them:
SELECT <COLUMN_NAMES> FROM <LEFT_TABLE_NAME> <JOIN_TYPE> <RIGHT_TABLE_NAME> ON <LEFT_TABLE_COLUMN_NAME> = <RIGHT_TABLE_COLUMN_NAME>
For example:
Select table1.A, table1.B from table1 join table2 on table1.A = table2.C
Here are the different types of the joins in SQL:
- (INNER) JOIN: returns records that have matching values in both tables
- LEFT (OUTER) JOIN: returns all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: returns all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: returns all records when there is a match in either left or right table
Bodo SQL currently support inner join on all conditions, but all outer joins are only support on an equality between columns.
UNION
The UNION operator is used to combine the result-set of two SELECT statements:
SELECT <COLUMN_NAMES> FROM <TABLE1> UNION SELECT <COLUMN_NAMES> FROM <TABLE2>;
Each SELECT statement within the UNION caluse must have the same number of columns. The columns must also have similar data types. The output of the UNION is the set of rows which are present in either of the input select statements.

The UNION operator selects only the distinct values from the inputs by default. To allow duplicate values, use UNION ALL:
SELECT <COLUMN_NAMES> FROM <TABLE1> UNION ALL SELECT <COLUMN_NAMES> FROM <TABLE2>;
INTERSECT
The INTERSECT operator is used to calculate the intersection of two SELECT statements:
SELECT <COLUMN_NAMES> FROM <TABLE1> INTERSECT SELECT <COLUMN_NAMES> FROM <TABLE2>;
Each SELECT statement within the INTERSECT clause must have the same number of columns. The columns must also have similar data types. The output of the INTERSECT is the set of rows which are present in both of the input select statements. The INTERSECT operator selects only the distinct values from the inputs.
GROUP BY
The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”. The GROUP BY statement is often used with aggregate functions to group the result-set by one or more columns:
SELECT <COLUMN_NAMES> FROM <TABLE_NAME> WHERE <CONDITION> GROUP BY <COLUMN_NAMES> ORDER BY <COLUMN_NAMES>
For example:
Select MAX(A) from table1 Group By B
GROUP BY statements also referring to columns by alias or column number:
Select MAX(A), B - 1 as val from table1 Group By val Select MAX(A), B from table1 Group By 2
HAVING
The HAVING clause is used for filtering with GROUP BY. HAVING applies the filter after generating the groups, whereas WHERE applies the filter before generating any groups:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition
For example:
Select MAX(A) from table1 Group By B HAVING C < 0
HAVING statements also referring to columns by aliases used in the GROUP BY:
Select MAX(A), B - 1 as val from table1 Group By val having val > 5
CASE
The CASE statement goes through conditions and returns a value when the first condition is met:
SELECT CASE WHEN cond1 THEN value1 WHEN cond2 THEN value2 ... ELSE valueN END
For example:
Select (CASE WHEN A > 1 THEN A ELSE B END) as mycol from table1
If the types of the possible return values are different, BodoSQL will attempt to cast them all to a common type, which is currently undefined behavior. The last else clause can optionally be excluded, in which case, the CASE statement will return null if none of the conditions are met. For example:
Select (CASE WHEN A < 0 THEN 0 END) as mycol from table1
is equivalent to:
Select (CASE WHEN A < 0 THEN 0 ELSE NULL END) as mycol from table1
LIKE
The LIKE clause is used to select the strings in a column that matches a pattern:
SELECT column_name(s) FROM table_name WHERE column LIKE pattern
In the pattern we support the wildcards % and _. For example:
Select A from table1 where B like '%py'

GREATEST

The GREATEST clause is used to return the greatest value from a list of columns:
SELECT GREATEST(col1, col2, ..., colN) from table_name
For example:
SELECT GREATEST(A, B, C) from table1

With

The WITH clause can be used to name subqueries:

WITH sub_table AS (SELECT column_name(s) FROM table_name)
SELECT column_name(s) FROM sub_table

For example:

WITH subtable as (Select Max(A) as max_al FROM table1 group by B)
Select Max(max_val) from subtable

Aliasing
SQL aliases are used to give a table, or a column in a table, a temporary name:
SELECT <COLUMN_NAME> AS <ALIAS> FROM <TABLE_NAME>
For example:
Select SUM(A) as total from table1
Operators
- Bodo SQL currently supports the following arithmetic operators:
  + (addition)
  
  - (subtraction)
  
  * (multiplication)
  
  / (true division)
- Bodo SQL currently supports the following comparision operators:
  = (equal to)
  
  > (greater than)
  
  < (less than)
  
  >= (greater than or equal t)o
  
  <= (less than or equal to)
  
  <> (not equal to)
  
  != (not equal to)
- Bodo SQL currently supports the following logical operators:
  AND
  
  OR
  
  NOT
- Bodo SQL currently supports the following string operators:
  || (string concatination)
Numeric Functions
Except where otherwise specified, the inputs to each of these functions can be any numeric type, column or scalar. Here is an example using MOD:
SELECT MOD(12.2, A) FROM table1
Bodo SQL Currently supports the following Numeric Functions:
- ABS(n)
  
  Returns the absolute value of n
- COS(n)
  
  Calculates the Cosine of n
- SIN(n)
  
  Calculates the Sine of n
- TAN(n)
  
  Calculates the Tangent of n
- ACOS(n)
  
  Calculates the Arccosine of n
- ASIN(n)
  
  Calculates the Arcsine of n
- ATAN(n)
  
  Calculates the Arctangent of n
- ATAN2(A, B)
  
  Calculates the Arctangent of A divided by B
- COTAN(X)
  
  Calculates the Cotangent of X
- CEIL(X)
  Converts X to an integer, rounding towards positive infinity
- CEILING(X)
  
  Equivalent to CEIL
- FLOOR(X)
  
  Converts X to an integer, rounding towards negative infinity
- DEGREES(X)
  
  Converts a value in radians to the corresponding value in degrees
- RADIANS(X)
  
  Converts a value in radians to the corresponding value in degrees
- LOG10(X)
  
  Computes Log base 10 of x. Returns NaN for negative inputs, and -inf for 0 inputs.
- LOG(X)
  
  Equivalent to LOG10(x)
- LOG10(X)
  
  Computes Log base 2 of x. Returns NaN for negative inputs, and -inf for 0 inputs.
- LN(X)
  
  Computes the natural log of x. Returns NaN for negative inputs, and -inf for 0 inputs.
- MOD(A,B)
  
  Computes A modulo B.
- CONV(X, current_base, new_base)
  CONV takes a string representation of an integer value, it’s current_base, and the base to convert that argument to. CONV returns a new string, that represents the value in the new base. CONV is only supported for converting to/from base 2, 8, 10, and 16.
  
  For example:
  
  CONV('10', 10, 2) ==> '1010' CONV('10', 2, 10) ==> '2' CONV('FA', 16, 10) ==> '250'
- SQRT(X)
  
  Computes the square root of x. Returns NaN for negative inputs, and -inf for 0 inputs.
- PI()
  
  Returns the value of PI
- POW(A, B), POWER(A, B)
  
  Returns A to the power of B. Returns NaN if A is negative, and B is a float. POW(0,0) is 1
- EXP(X)
  
  Returns e to the power of X
- SIGN(X)
  
  Returns 1 if X > 0, -1 if X < 0, and 0 if X = 0
- ROUND(X, num_decimal_places)
  
  Rounds X to the specified number of decimal places
- TRUNCATE(X, num_decimal_places)
  
  Equivalent to ROUND(X, num_decimal_places)
Aggregation Functions
Bodo SQL Currently supports the following Aggregation Functions on all types:
- COUNT
  
  Count the number of elements in a column or group.
In addition, Bodo SQL also supports the following functions on numeric types:
- AVG
  
  Compute the mean for a column.
- MAX
  
  Compute the max value for a column.
- MIN
  
  Compute the min value for a column.
- STDDEV
  
  Compute the standard deviation for a column with N - 1 degrees of freedom.
- STDDEV_SAMP
  
  Compute the standard deviation for a column with N - 1 degrees of freedom.
- STDDEV_POP
  
  Compute the standard deviation for a column with N degrees of freedom.
- SUM
  
  Compute the sum for a column.
- VARIANCE
  
  Compute the variance for a column with N - 1 degrees of freedom.
- VAR_SAMP
  
  Compute the variance for a column with N - 1 degrees of freedom.
- VAR_POP
  
  Compute the variance for a column with N degrees of freedom.
All aggregate functions have the syntax:
SELECT AGGREGATE_FUNCTION(<COLUMN_EXPRESSION>) FROM <TABLE_NAME> GROUP BY <COLUMN_NAMES>
These functions can be used either in a groupby clause, where they will be computed for each group, or by itself on an entire column expression. For example:
Select AVG(A) from table1 Group By B Select Count(Distinct A) from table1
Timestamp Functions
Bodo SQL currently supports the following Timestamp functions:
DATEDIFF(timestamp_val1, timestamp_val2)

Computes the difference in days between two Timestamp values

STR_TO_DATE(str_val, literal_format_string)

Converts a string value to a Timestamp value given a literal format string. If a year, month, and day value is not specified, they default to 1900, 01, and 01 respectively. Will throw a runtime error if the string cannot be parsed into the expected values. See DATE_FORMAT for Recognized formatting characters.

For example:

STR_TO_DATE('2020 01 12', '%Y %m %d') ==> Timestamp '2020-01-12' STR_TO_DATE('01 12', '%m %d') ==> Timestamp '1900-01-12' STR_TO_DATE('hello world', '%Y %m %d') ==> RUNTIME ERROR

DATE_FORMAT(timestamp_val, literal_format_string)

Converts a timestamp value to a String value given a scalar format string.

Recognized formatting character:

%i Minutes, zero padded (00 to 59)

%M Full month name (January to December)

%r Time in format in the format (hh:mm:ss AM/PM)

%s Seconds, zero padded (00 to 59)

%T Time in format in the format (hh:mm:ss)

%T Time in format in the format (hh:mm:ss)

%u week of year, where monday is the first day of the week (00 to 53)

%a Abbreviated weekday name (sun-sat)

%b Abbreviated month name (jan-dec)

%f Microseconds, left padded with 0’s, (000000 to 999999)

%H Hour, zero padded (00 to 23)

%j Day Of Year, left padded with 0’s (001 to 366)

%m Month number (00 to 12)

%p AM or PM, depending on the time of day

%d Day of month, zero padded (01 to 31)

%Y Year as a 4 digit value

%y Year as a 2 digit value, zero padded (00 to 99)

%U Week of year where sunday is the first day of the week (00 to 53)

%S Seconds, zero padded (00 to 59)

For example:

DATE_FORMAT(Timestamp '2020-01-12', '%Y %m %d') ==> '2020 01 12' DATE_FORMAT(Timestamp '2020-01-12 13:39:12', 'The time was %T %p. It was a %u') ==> 'The time was 13:39:12 PM. It was a Sunday'

DATE_ADD(timestamp_val, interval)

Computes a timestamp column by adding an interval column/scalar to a timestamp value

DATE_SUB(timestamp_val, interval)

Computes a timestamp column by subtracting an interval column/scalar to a timestamp value

NOW()

Computes a timestamp equal to the current system time

LOCALTIMESTAMP()

Equivalent to NOW

CURDATE()

Computes a timestamp equal to the current system time, excluding the time information

CURRENT_DATE()

Equivalent to CURDATE

Extract(TimeUnit from timestamp_val)

Extracts the specified TimeUnit from the supplied date.

allowed TimeUnits are:

MICROSECOND

SECOND

MINUTE

HOUR

DAY (Day of Month)

DOY (Day of Year)

DOW (Day of week)

WEEK

MONTH

QUARTER

YEAR

TimeUnits are not case sensitive.

MICROSECOND(timestamp_val),

Equivalent to EXTRACT(MICROSECOND from timestamp_val)

SECOND(timestamp_val)

Equivalent to EXTRACT(SECOND from timestamp_val)

MINUTE(timestamp_val)

Equivalent to EXTRACT(MINUTE from timestamp_val)

HOUR(timestamp_val)

Equivalent to EXTRACT(HOUR from timestamp_val)

WEEK(timestamp_val)

Equivalent to EXTRACT(WEEK from timestamp_val)

WEEKOFYEAR(timestamp_val)

Equivalent to EXTRACT(WEEK from timestamp_val)

MONTH(timestamp_val)

Equivalent to EXTRACT(MONTH from timestamp_val)

QUARTER(timestamp_val)

Equivalent to EXTRACT(QUARTER from timestamp_val)

YEAR(timestamp_val)

Equivalent to EXTRACT(YEAR from timestamp_val)

MAKEDATE(integer_years_val, integer_days_val)

Computes a timestamp value that is the specified number of days after the specified year.

DAYNAME(timestamp_val)

Computes the string name of the day of the timestamp value.

MONTHNAME(timestamp_val)

Computes the string name of the month of the timestamp value.

TO_DAYS(timestamp_val)

Computes the difference in days between the input timestamp, and year 0 of the Gregorian calendar

TO_SECONDS(timestamp_val)

Computes the number of seconds since year 0 of the Gregorian calendar

FROM_DAYS(n)

Returns a timestamp values that is n days after year 0 of the Gregorian calendar

UNIX_TIMESTAMP()

Computes the number of seconds since the unix epoch

FROM_UNIXTIME(n)

Returns a Timestamp value that is n seconds after the unix epoch
String Functions
Bodo SQL currently supports the following string functions:
LOWER(str)

Converts the string scalar/column to lower case.

LCASE(str)

Same as LOWER.

UPPER(str)

Converts the string scalar/column to upper case.

UCASE(str)

Same as UPPER.

CONCAT(str_0, str_1, …)

Concatinates the strings together. Requires at least two arguments.

CONCAT_WS(str_separator, str_0, str_1, …)

Concatinates the strings together, with the specified separator. Requires at least three arguments

SUBSTRING(str, start_index, len)

Takes a substring of the specified string, starting at the specified index, of the specified length. Start_index = 1 specfies the first character of the string, start_index = -1 specfies the last character of the string. Start_index = 0 causes the function to return empty string. If start_index is positive and greater then the length of the string, returns an empty string. If start_index is negative, and has an absolute value greater then the length of the string, the behavior is equivalent to start_index = 1.

For example:

SUBSTRING('hello world', 1, 5) ==> 'hello' SUBSTRING('hello world', -5, 7) ==> 'world' SUBSTRING('hello world', -20, 8) ==> 'hello wo' SUBSTRING('hello world', 0, 10) ==> ''

MID(str, start_index, len)

Equivalent to SUBSTRING

SUBSTR(str, start_index, len)

Equivalent to SUBSTRING

LEFT(str, n)

Takes a substring of the specified string consisting of the leftmost n characters

RIGHT(str, n)

Takes a substring of the specified string consisting of the rightmost n characters

REPEAT(str, len)

Extends the specified string to the specified length by repeating the string. Will truncate the string If the string’s length is less then the len argument

For example:

REPEAT('abc', 7) ==> 'abcabca' REPEAT('hello world', 5) ==> 'hello'

STRCMP(str1, str2)

Compares the two strings lexographically. If str1 > str2, return 1. If str1 < str2, returns -1. If str1 = str2, returns 0.

REVERSE(str)

Returns the reversed string.

ORD(str)

Returns the integer value of the unicode representation of the first charecter of the input string. returns 0 when passed the empty string

CHR(int)

Returns the charecter of the corresponding unicode value. Currently only supported for ASCII charecters (0 to 127, inclusive)

SPACE(int)

Returns a string containing the specified number of spaces.

LTRIM(str)

returns the input string, will all spaces removed from the left of the string

RTRIM(str)

returns the input string, will all spaces removed from the right of the string

TRIM(str)

returns the input string, will all spaces removed from the left and right of the string

SUBSTRING_INDEX(str, delimiter_str, n)

Returns a substring of the input string, which contains all characters that occur before n occurances of the delimiter string. if n is negative, it will return all characters that occur after the last n occurances of the delimiter string. If num_occurances is 0, it will return the empty string

For example:

SUBSTRING_INDEX('1,2,3,4,5', ',', 2) ==> '1,2' SUBSTRING_INDEX('1,2,3,4,5', ',', -2) ==> '4,5' SUBSTRING_INDEX('1,2,3,4,5', ',', 0) ==> ''

LPAD(string, len, padstring)

Extends the input string to the specified length, by appending copies of the padstring to the left of the string. If the input string’s length is less then the len argument, it will truncate the input string.

For example:

LPAD('hello', 10, 'abc') ==> 'abcabhello' LPAD('hello', 1, 'abc') ==> 'h'

RPAD(string, len, padstring)

Extends the input string to the specified length, by appending copies of the padstring to the right of the string. If the input string’s length is less then the len argument, it will truncate the input string.

For example:

LPAD('hello', 10, 'abc') ==> 'helloabcab' LPAD('hello', 1, 'abc') ==> 'h'

REPLACE(base_string, substring_to_remove, string_to_substitute)

Replaces all occurances of the specified substring with the substitute string.

For example:

REPLACE('hello world', 'hello' 'hi') ==> 'hi world'
Control flow Functions
- IF(Cond, TrueValue, FalseValue)
  Returns TrueValue if cond is True, and FalseValue if cond is false. Loigcally equivalent to:
  
  CASE WHEN Cond THEN TrueValue ELSE FalseValue END
- IFNULL(Arg0, Arg1)
  
  Returns Arg1 if Arg0 is null, and otherwise returns Arg1. If Arguments do not have the same type, Bodo SQL will attempt to cast them all to a common type, which is currently undefined behavior.
- NVL(Arg0, Arg1)
  
  Equivalent to IFNULL
- NULLIF(Arg0, Arg1)
  
  Returns null if the Arg0 evaluates to true, and otherwise returns Arg1
- COALESCE(A, B, C, …)
  
  Returns the first non NULL argument, or NULL if no non NULL argument is found. Requires at least two arguments. If Arguments do noth have the same type, Bodo SQL will attempt to cast them to a common datatype, which is currently undefined behavior.

Supported Data Types¶

BodoSQL uses Pandas DataFrames to represent SQL tables in memory and converts SQL types to corresponding Python types which are used by Bodo. Below is a table mapping SQL types used in BodoSQL to their respective Python types and Bodo data types.

SQL Type(s)	Equivalent Python Type	Bodo Data Type
`TINYINT`	`np.int8`	`bodo.int8`
`SMALLINT`	`np.int16`	`bodo.int16`
`INT`	`np.int32`	`bodo.int32`
`BIGINT`	`np.int64`	`bodo.int64`
`FLOAT`	`np.float32`	`bodo.float32`
`DECIMAL`, `DOUBLE`	`np.float64`	`bodo.float64`
`VARCHAR`, `CHAR`	`str`	`bodo.string_type`
`TIMESTAMP`, `DATE`	`np.datetime64[ns]`	`bodo.datetime64ns`
`INTERVAL(day-time)`	`np.timedelta64[ns]`	`bodo.timedelta64ns`
`BOOLEAN`	`np.bool_`	`bodo.bool_`

Nullable and Unsigned Types¶

Although SQL does not explicitly support unsigned types, by default, BodoSQL maintains the exact types of the existing DataFrames registered in a BodoSQLContext, including unsigned and non-nullable type behavior. If an operation has the possibility of creating null values or requires casting data, BodoSQL will convert the input of that operation to a nullable, signed version of the type.

Supported Literals¶

BodoSQL supports the following literal types:

Boolean Literal
Datetime Literal
Float Literal
Integer Literal
Interval Literal
String Literal

Boolean Literal¶

Syntax:

TRUE | FALSE

Boolean literals are case insensitive.

Datetime Literal¶

Syntax:

DATE 'yyyy-mm-dd' |
TIMESTAMP 'yyyy-mm-dd' |
TIMESTAMP 'yyyy-mm-dd HH:mm:ss'

Float Literal¶

Syntax:

[ + | - ] { digit [ ... ] . [ digit [ ... ] ] | . digit [ ... ] }

where digit is any numeral from 0 to 9

Integer Literal¶

Syntax:

[ + | - ] digit [ ... ]

where digit is any numeral from 0 to 9

Interval Literal¶

Syntax:

INTERVAL integer_literal interval_type

Where integer_literal is a valid integer literal and interval type is one of:

DAY[S] |
HOUR[S] |
MINUTE[S] |
SECOND[S]

String Literal¶

Syntax:

'char [ ... ]'

Where char is a character literal in a Python string.

NULL SEMANTICS¶

Bodo SQL converts SQL queries to Pandas code that executes inside Bodo. As a result, NULL behavior aligns with Pandas and may be slightly different than other SQL systems. This is currently an area of active development to ensure compatibility with other SQL systems.

Most operators with a NULL input return NULL. However, there a couple notable places where Bodo SQL may not match other SQL systems:

Bodo SQL treats NaN the same as NULL

Is (NOT) False and Is (NOT) True return NULL when used on a null expression

AND will return NULL if any of the inputs is NULL