Our Key Features

15min

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;
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;
COPY FROM
The COPY FROM statement copies the data from a file into a table.
COPY table_name FROM 'file_path'
﻿
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. 
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.
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!
﻿

Updated 30 Nov 2023

Did this page help you?

Oxla vs. Other OLAP Solutions

Run Oxla in 2 minutes

Docs powered by Archbee

TABLE OF CONTENTS

Data Storage, Import & Export

Aggregation functions

Trigonometric functions

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.”

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;
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;
COPY FROM	The COPY FROM statement copies the data from a file into a table.	COPY table_name FROM 'file_path'

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)

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.

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.

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.

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.

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.

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.

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.