website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Getting Started
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
Docs powered by archbee 

Our Key Features

18min

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!

Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
Oxla vs. Other OLAP Solutions
NEXT
Getting Started
Docs powered by archbee 
TABLE OF CONTENTS
Data Storage, Import & Export
Integration
Supported SQL
SQL Syntax
Supported Clauses
Supported Datatypes
Functions
Numeric functions
Aggregation functions
String functions
Timestamp functions
Boolean function
JSON functions
Other functions

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2022 Oxla