Our Key Features

18min

This article lists our prominent features supported in the current release:
Data Storage, Import & ExportA 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
IntegrationConnect Oxla to external data sources with JDBC.
Support PostgreSQL library.
We are fully integrated with Amazon S3 storage
Supported SQLName
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;
COPY FROM
The COPY FROM statement copies the data from a file into a table.
COPY table_name FROM 'file_path'
﻿
SQL SyntaxSupported ClausesName
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;
Supported DatatypesName
Description
Format
INTEGER
32-bit signed integer
﻿
LONG
64-bit signed integer
﻿
FLOAT
32-bit floating point number
﻿
DOUBLE
64-bit floating point number
﻿
TIMESTAMP
Time with microsecond precision
YYYY-MM-DD [HH:MM:SS[.SSSSSS]]
DATE
Date
YYYY-MM-DD
INTERVAL
Encodes a span of time
year-month (YYYY-MM); day-time (DD HH:MM:SS)
STRING
UTF8 encoded string with Unicode support
﻿
BOOL
Boolean value
True or False
JSON
JSON data type
{"customer":"Dean Smith", "items":{"product:"cup","qty":2}} 
FunctionsNumeric functionsme
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.
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.
Aggregation functionsName
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.
ABS
Calculate absolute value. Input must be numeric.
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 functionsName
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 functionsName
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 functionName
Description
IF
Three arguments function. It expects that the first argument is of type BOOL, 2nd and 3rd have a matching type 
﻿
E. g. IF(2 < 5, “smaller,” “greater”) would produce “smaller.”
JSON functionsName
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 functionsName
Description
CURRENT_SCHEMA
It returns the schema's name first in the search path.
CURRENT_DATABASE
It 
﻿
🚀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 11 May 2023

Did this page help you?

Yes

Oxla vs. Other OLAP Solutions

Getting Started

Docs powered by archbee

TABLE OF CONTENTS

Data Storage, Import & Export

Aggregation functions

Name	Description
IF	Three arguments function. It expects that the first argument is of type BOOL, 2nd and 3rd have 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;
COPY FROM	The COPY FROM statement copies the data from a file into a table.	COPY table_name FROM 'file_path'

Name	Description	Format
INTEGER	32-bit signed integer
LONG	64-bit signed integer
FLOAT	32-bit floating point number
DOUBLE	64-bit floating point number
TIMESTAMP	Time with microsecond precision	YYYY-MM-DD [HH:MM:SS[.SSSSSS]]
DATE	Date	YYYY-MM-DD
INTERVAL	Encodes a span of time	year-month (YYYY-MM); day-time (DD HH:MM:SS)
STRING	UTF8 encoded string with Unicode support
BOOL	Boolean value	True or False
JSON	JSON data type	{"customer":"Dean Smith", "items":{"product:"cup","qty":2}}

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

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.
ABS	Calculate absolute value. Input must be numeric.
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