Our Key Features

This article lists our prominent features supported in the current release:

Data Storage, Import & Export

• A distributed storage system that provides high availability and redundancy.
• Support for large data loading and unloading into/out of tables.
• Exporting/ importing data from .csv files.
• Decoupled storage and computing.
• Internal metastore detection to provide information about the database

Integration

• Connect Oxla to external data sources with JDBC.
• Support PostgreSQL library.
• We are fully integrated with Amazon S3 storage

Supported SQL

Name	Description	Example
SELECT	The SELECT statement is used to select data from a database. The data returned is stored in a result table called the result set.	The following query will select all the columns from the Products table: SELECT * FROM Products;
INSERT INTO	The INSERT INTO query adds new rows to an existing table using a SELECT statement or explicitly stating input values.	INSERT INTO distance_table (distance, unit) VALUES (2000, 'kilometers'), (1000, 'meters'), (5, 'miles');
DROP	The DROP statement is used to delete objects like an existing database or table.	The following query will delete a table named testTB: DROP TABLE testTB; The following query will delete a database named testTB: DROP DATABASE testDB;
CREATE TABLE	The CREATE TABLE statement is used to create a table in a database with a defined column and each column’s data type.	CREATE TABLE book (bookID int, bookTitle text, bookCategory text, bookPublisher text, bookPages int;)
CREATE INDEX	The CREATE INDEX is used to create a single index on an empty table.
SHOW TABLES	The Show statements allow you to obtain information about existing tables.	SHOW TABLES;
DESCRIBE	The Describe statement show columns within a table and tables within a database.	DESCRIBE TABLE part; DESCRIBE DATABASE;
SHOW NODES	The SHOW NODES returns the current state of the cluster.	SHOW NODES;
SET/SHOW	The SET statement lets you set specific options, and the SHOW statement helps you see the current values in Oxla.	SET extra_float_digits TO 2;``SHOW timezone;
COPY FROM	The COPY FROM statement copies the data from a file into a table.	COPY table_name FROM 'file_path'
COPY TO	The COPY TO statement is used to export tables, specific columns, or results of select queries into .csv files.	COPY salary TO '/path/to/exportsalary.csv';

SQL Syntax

Supported Clauses

Name	Description	Example
FROM	The FROM clause can return a row set, a table, or a function from any ‘join’ statement.	SELECT * FROM cars;
JOIN	A JOIN clause combines records from two or more tables based on a related column between those tables.	SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
LEFT JOIN	The LEFT JOIN joins two or more tables, returns all records from the left table, and matches records from the right table that fulfill the specified join condition. It will return null if there is no matching record from the right table.	SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
RIGHT JOIN	The RIGHT JOIN joins two or more tables and returns all records from the right table and the matched records from the left table that fulfill the specified join condition. It will return null if there is no matching record from the left table.	SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
OUTER JOIN	The OUTER JOIN joins and returns matched and unmatched values from either one or both tables.	SELECT Customers.customerName, Orders.orderID FROM Customers OUTER JOIN Orders ON Customers.customerID=Orders.customerID
WHERE	The WHERE clause is used to filter records from a single table or by joining with multiple tables that fulfill the specified condition.	SELECT * FROM Customers WHERE Country='France'; SELECT * FROM Customers WHERE CustomerID=1;
GROUP BY	The GROUP BY clause groups rows with the same values into summary rows.	SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
HAVING	The HAVING clause is used to filter data according to the conditions provided. The HAVING clause is usually executed with the GROUP BY clause.	SELECT Department, sum(Salary) as Salary FROM employee GROUP BY department HAVING SUM(Salary) >= 50000;
ORDER BY	The ORDER BY sorts the rows of the result set from a SELECT statement.	SELECT emp_name, emp_div FROM salaryemp ORDER BY emp_div;
LIMIT	The LIMIT clause is an optional clause of the SELECT statement that constrains the number of rows the query returns.	SELECT order_id, prod_name, prod_price FROM comporders ORDER BY order_id LIMIT 4;
OFFSET	The OFFSET is a clause that skips some records from the result set.	SELECT * FROM oxlafunctions ORDER BY func_name LIMIT 5 OFFSET 2;
Set Operations	Set operations are used for comparisons, combinations, and distinctions among sets.	It includes the following operations: UNION, EXCEPT, INTERSECT.

Supported Datatypes

Data Types	Definition	Format
BOOL	Boolean value.	True or False.
TEXT	UTF8 encoded string with Unicode support.	‘text’
DATE	Date value.	YYYY-MM-DD
TIMESTAMP WITHOUT TIME ZONE	Time and date values without a time zone.	YYYY-MM-DD [HH:MM:SS[.SSSSSS]]
TIMESTAMP WITH TIME ZONE	Date and time values, including the time zone information.	YYYY-MM-DD HH:MM:SS.SSSSSS+TZ
TIME	Time values without any date information.	HH:MM:SS[.SSSSSS]
JSON	A value in JSON standard format.	variable_name JSON
INTERVAL	Encodes a span of time	year-month (YYYY-MM); day-time (DD HH:MM:SS)
INT	32-bit signed integer	one or more digits “0” to “9”
BIGINT	64-bit signed integer	large numeric/decimal value
REAL	32-bit floating point number	float(n)
DOUBLE PRECISION	64-bit floating point number	decimal(p, s)

Functions

Numeric functions

Name	Description
CEIL	This function rounds up to the nearest positive or negative integer value greater than or equal to the argument.
ABS	This function returns the absolute value of an argument, regardless of whether it is positive or negative.
SQRT	This function returns the square root of its argument.
CBRT	The function calculates and returns the cube root of a given number.
RANDOM	This function returns a random number between 0 (inclusive) and 1 (exclusive).
FLOOR	This function returns a number rounded down that is less than or equal to the specified argument.
LN	This function returns the exponential value of its argument.
SIN	This function returns the trigonometric sine value of a specified angle in radians.
GREATEST	This function extracts the greatest or largest value from a set of values.
LEAST	This function returns the least or smallest value in a list of values.
ROUND	This function rounds numbers to the nearest integer or to a specified number of decimal places.
EXP	This function returns the exponential value of a number specified in the argument.
LOG	The function returns the base-10 logarithm or logarithm of the specified base of a given number.
POWER	This function returns the value of a number raised to the power of another number specified in the arguments.
TO_CHAR from number	This function formats a number into a string using a given format.

Aggregation functions

Name	Description
SUM	Calculates the sum of values. Input must be numeric. If the input type is 32-bit, then the result will be 64-bit.
MIN	Returns the smallest value. Input must be numeric.
FOR_MIN	Returns a value corresponding to the minimal metric in the same row from a set of values.
MAX	Returns the largest value. Input must be numeric
FOR_MAX	Returns a value corresponding to the maximum metric in the same row from a set of values.
AVG	Calculates the average of values. Input must be numeric. If the input type is 32-bit, then the result will be 64-bit.
COUNT	Counts the number of rows.
BOOL_AND	False if at least one of aggregated rows is False. Input must be bool.
BOOL_OR	True if at least one of aggregated rows is True. Input must be bool.

String functions

Name	Description
LENGTH	Returns the number of characters in the string. The number of characters might be different from the byte length.
LOWER	Makes string lowercase. It supports Unicode, so ß is changed into ss.
UPPER	Makes string upper case. Supports Unicode.
STARTS_WITH	It returns true if the first argument starts with the second argument. E. g. STARTS_WITH(“abc,” “ab”) returns true.
ENDS_WITH	It returns true if the first argument ends with the second argument. E. g. STARTS_WITH(“abc,” “bc”) returns true.
CONCAT	Concatenates all inputs e. g. CONCAT(123, “abc”) returns “123abc”. Inputs might be of any type.
SUBSTR	Retrieves substring. E. g. SUBSTR(“abcd”, 2) returns “cd”, SUBSTR(“abcd”, 2, 1) returns “c”.
STRPOS	Returns position at which the second argument starts within the first argument or 0 if the first argument does not contain the second argument E. g. STRPOS(“abcd”, “ab”) return 2, STRPOS(“abcd”, “ac”) returns 0.
REGEXP_REPLACE	Substitutes new text for substrings that match POSIX regular expression patterns.
REPLACE	Finds and replaces a substring with a new one in a string.
POSITION	Returns the position of the first occurrence of a substring in a string.

Timestamp functions

Name	Description
CURRENT_TIMESTAMP	Returns current timestamp.
FORMAT_TIMESTAMP	Formats timestamp. Format string documentation: date. Example: FORMAT_TIMESTAMP('the date is: %F', CURRENT_TIMESTAMP()) would produce “the date is: 2022-12-19”
UNIX_SECONDS	Converts the given timestamp to Unix timestamp in seconds.
UNIX_MILLIS	Converts the given timestamp to Unix timestamp in milliseconds.
UNIX_MICROS	Converts the given timestamp to Unix timestamp in microseconds.
TIMESTAMP_SECONDS	Converts Unix timestamp in seconds to timestamp.
TIMESTAMP_MILLIS	Converts Unix timestamp in milliseconds to timestamp.
TIMESTAMP_MICROS	Converts Unix timestamp in microseconds to timestamp.
TIMESTAMP_TRUNC()	Truncates a given timestamp to the nearest time parts. Supported time parts are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND
EXTRACT	Extracts are given time part from Timestamp. Supported time parts are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. e. g. EXTRACT(YEAR FROM TIMESTAMP ‘2020-10-15 10:15:23’) will return 2020.
TO_TIMESTAMP	Converts a string into a timestamp based on the provided format.
DATE_TRUNC	Truncates intervals or timestamps/time zones to a specified field.
TO_CHAR from timestamp	Formats a timestamp into a string using a given format.

Boolean function

Name	Description
IF	Three arguments function. It expects that the first argument is of type BOOL, 2nd and 3rd has a matching type E. g. IF(2 < 5, “smaller,” “greater”) would produce “smaller.”

JSON functions

Name	Description
JSON_EXTRACT_PATH	It extracts JSON sub-object at the specified path.
JSON_EXTRACT_PATH_TEXT	It returns text referenced by a series of path elements in a JSON string or JSON body.
JSON_ARRAY_LENGTH	It returns the number of elements in the outer array of a JSON string or JSON body.
JSON_ARRAY_EXTRACT	It returns the JSON array as a set of JSON values.

Other functions

Name	Description
CURRENT_SCHEMA	It returns the schema’s name first in the search path.
CURRENT_DATABASE	It returns the current database’s name.
COALESCE	It returns the first argument that is not null, while the remaining arguments from the first non-null argument are not evaluated.
NULLIF	It replaces a given value with null if it matches a specific criterion.
pg_total_relation_size	It retrieves the size of a table.
has_schema_privilege	It checks whether the current user has specific privileges on a schema.
pg_get_expr	It retrieves the internal form of an individual expression, such as the default value for a column.
pg_typeof	It retrieves the data type of any given value.

Trigonometric functions

Functions	Description
acos	It calculates the inverse cosine of a given argument, where the output is expressed in radians.
acosd	It calculates the inverse cosine of a given argument, where the output is expressed in degrees.
asin	It calculates the inverse sine of a given argument, where the output is expressed in radians.
asind	It calculates the inverse sine of a given argument, where the output is expressed in degrees.
atan	It calculates the inverse tangent of a given argument, where the output is expressed in radians.
atand	It calculates the inverse tangent of a given argument, where the output is expressed in degrees.
atan2	It calculates the inverse tangent of y/x, where the output is expressed in radians.
atan2d	It calculates the inverse tangent of y/x, where the output is expressed in degrees.
cos	It calculates the cosine of a given argument, where the argument is in radians.
cosd	It calculates the cosine of a given argument, where the argument is in degrees.
cot	It calculates the cotangent of a given argument, where the argument is in radians.
cotd	It calculates the cotangent of a given argument, where the argument is in degrees.
sin	It calculates the sine of a given argument, where the argument is in radians.
sind	It calculates the sine of a given argument, where the argument is in degrees.
tan	It calculates the tangent of a given argument, where the argument is in radians.
tand	It calculates the tangent of a given argument, where the argument is in degrees.

We still have a long road to go. We will keep you posted with our soon-to-be-released features to boost your experience. Stay tuned!