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; |
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 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; |
Supported Datatypes
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}} |
Functions
Numeric functions
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. |
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. |
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 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 have 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 |
🚀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!
