Our Key Features - Oxla Developer Hub

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)`

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.

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!

Oxla vs. Other OLAP Solutions Run Oxla in 2 minutes

Data Storage, Import & Export
Integration
Supported SQL
SQL Syntax
Supported Clauses
Supported Datatypes
Supported Datatypes
Functions
Numeric functions
Aggregation functions
String functions
Timestamp functions
Boolean function
JSON functions
Other functions
Trigonometric functions