Understand the functions you can use – Numberly

The following is a non-exhaustive list of functions that can be used in the query editor. In general, it is not possible to use functions that create, alter or delete data.

Common commands

Clause/Command	Description	Available
`SELECT`	Specifies the columns to be retrieved from the database.	✅
`FROM`	Specifies the table(s) from which to retrieve the data.	✅
`WHERE`	Sorts the results to include only those rows that meet the specified condition.	✅
`GROUP BY`	Groups rows with the same values in the specified columns into aggregated data.	✅
`HAVING`	Filter groups according to a specified condition, used with GROUP BY.	✅
`ORDER BY`	Specifies sort order for results.	✅
`LIMIT`	Limits number of rows returned in results.	✅

Join commands

Command	Description	Available
`JOIN`	Fetches data from multiple tables based on a join condition.	✅
`LEFT JOIN`	Fetches all rows from the table on the left and the corresponding rows from the table on the right.	✅
`RIGHT JOIN`	Recovers all rows in the right-hand table and corresponding rows in the left-hand table.	✅
`FULL JOIN`	Fetches all rows when there is a match in one of the tables.	✅
`CROSS JOIN`	Produces the Cartesian product of two tables.	✅
`SELF JOIN`	Produces a join of a table with itself.	✅

Aggregation functions

Order	Description	Available
`COUNT`	Returns the number of lines corresponding to a condition.	✅
`MAX`	Returns the maximum value of a column.	✅
`MIN`	Returns the minimum value of a column.	✅
`SUM`	Returns the sum of values in a column.	✅
`AVG`	Returns the average of the values in a column.	✅
`STDDEV`	Returns the standard deviation of the values in a column.	✅
`VARIANCE`	Returns the variance of the values in a column.	✅
`COLLECT_SET`	Returns a set of distinct values for a column.	✅
`COLLECT_LIST`	Returns a list of values for a column.	✅

Window functions

Order	Description	Available
`RANK`	Returns the rank of a value in a set of values.	✅
`DENSE_RANK`	Returns the dense rank of a value in a set of values.	✅
`ROW_NUMBER`	Returns the number of a line in a set of values.	✅
`NTILE`	Divides a set of values into a specified number of groups and returns the group number for each value.	✅
`LEAD`	Returns the value of a line after the current line in a set of values.	✅
`LAG`	Returns the value of a line before the current line in a set of values.	✅

Mathematical functions

Function	Description	Available
`ABS(number)`	Returns the absolute value of a number.	✅
`CEIL(number)`	Returns the smallest integer greater than or equal to a number.	✅
`FLOOR(number)`	Returns the largest integer less than or equal to a number.	✅
`ROUND(number, d)`	Rounds a number to d decimal places.	✅
`SQRT(number)`	Returns the square root of a number.	✅
`POWER(base, exp)`	Returns the base value raised to the power exp.	✅
`RAND()`	Returns a random number between 0 and 1.	✅
`SIN(number)`	Returns the sine of a number (in radians).	✅
`COS(number)`	Returns the cosine of a number (in radians).	✅
`TAN(number)`	Returns the tangent of a number (in radians).	✅
`EXP(number)`	Returns the exponential of a number.	✅
`LN(number)`	Returns the natural logarithm of a number.	✅
`LOG10(number)`	Returns the base-10 logarithm of a number.	✅

String functions

Function	Description	Available
`CONCAT(string1, string2)`	Concatenates two strings.	✅
`LENGTH(string)`	Returns the length of a string.	✅
`LOWER(string)`	Converts a string to lowercase.	✅
`UPPER(string)`	Converts a string to uppercase.	✅
`TRIM(string)`	Suppresses spaces at the beginning and end of a string.	✅
`SUBSTRING(string, start, length)`	Returns a substring.	✅
`REPLACE(string, search, replace)`	Replaces all occurrences of one substring with another.	✅
`REVERSE(string)`	Reverses the order of characters in a string.	✅
`REGEXP_REPLACE(string, pattern, replacement)`	Replaces parts of the string corresponding to a pattern.	✅
`SPLIT(string, delimiter)`	Divides a string using a delimiter.	✅

Date functions

Function	Description	Available
`CURRENT_DATE()`	Returns current date.	✅
`CURRENT_TIMESTAMP()`	Returns current date and time.	✅
`DATE_ADD(date, days)`	Add a number of days to a date.	✅
`DATE_SUB(date, days)`	Subtracts a number of days from a date.	✅
`DATEDIFF(date1, date2)`	Returns the difference in days between two dates.	✅
`YEAR(date)`	Returns the year of a date.	✅
`MONTH(date)`	Returns the month of a date.	✅
`DAY(date)`	Returns the day of a date.	✅
`HOUR(timestamp)`	Returns the time of a timestamp.	✅
`MINUTE(timestamp)`	Returns the minutes of a timestamp.	✅
`SECOND(timestamp)`	Returns the seconds of a timestamp.	✅
`UNIX_TIMESTAMP()`	Returns the current Unix timestamp.	✅
`FROM_UNIXTIME(epoch)`	Converts a Unix timestamp to date format.	✅

Type management functions

Function	Description	Available
`CAST(expression AS type)`	Converts an expression to a specified type.	✅
`COALESCE(value1, value2, ...)`	Returns the first non-zero value among the arguments.	✅
`ISNULL(expression)`	Checks if an expression is NULL.	✅
`NVL(value1, value2)`	Replaces a NULL value with a specified value.	✅

Collection functions

Function	Description	Available
`ARRAY(array_elements)`	Creates an array from the specified elements.	✅
`MAP(key, value, ...)`	Creates a map using the specified keys and values.	✅
`SIZE(collection)`	Returns the size of a collection (table, map).	✅
`INDEX(array, index)`	Returns the element to the specified index in an array.	✅

DDL (Data Definition Language)

Order	Description	Available
`CREATE DATABASE`	Creates a new database.	❌
`DROP DATABASE`	Deletes an existing database.	❌
`CREATE TABLE`	Creates a new table with the specified columns.	❌
`DROP TABLE`	Deletes an existing table.	❌
`ALTER TABLE`	Modifies the structure of a table, for example by adding columns.	❌
`CREATE INDEX`	Creates an index on a table to speed up queries.	❌
`DROP INDEX`	Removes an existing index.	❌
`CREATE VIEW`	Creates a virtual view based on the result of a SELECT query.	❌
`DROP VIEW`	Deletes an existing view.	❌
`DESCRIBE`	Displays the structure of a table or view.	❌
`SHOW DATABASES`	Displays all available databases.	❌
`SHOW TABLES`	Displays all tables in the current database.	❌
`SHOW PARTITIONS`	Displays the partitions of a table.	❌
`TRUNCATE TABLE`	Deletes all rows in a table without deleting the table itself.	❌

DML (Data Manipulation Language)

Order	Description	Available
`INSERT INTO`	Inserts new rows into a table.	❌
`INSERT OVERWRITE`	Replaces the data in the target table with the results of the SELECT query.	❌
`LOAD DATA`	Load data into a table from a file.	❌
`EXPORT TABLE`	Exports table data to a file.	❌
`IMPORT TABLE`	Imports data from a file into a table.	❌
`UPDATE`	Updates column values for rows that meet the condition.	❌
`DELETE`	Deletes lines that meet the specified condition.	❌
`MERGE`	Performs INSERT, UPDATE or DELETE operations based on a specified condition.	❌

DCL (Data Control Language)commands

Order	Description	Available
`GRANT`	Award privileges to users or roles.	❌
`REVOKE`	Revokes privileges granted to users or roles.	❌

Commands to manage files and configuration

Order	Description	Available
`SET`	Defines a configuration variable or displays all configuration variables.	❌
`RESET`	Resets configuration variables to their default values.	❌
`ADD FILE`	Add a file to the list of resources in the distributed cache.	❌
`ADD JAR`	Add a JAR file to the list of resources in the distributed cache.	❌
`LIST FILES`	Displays the list of files added to the distributed cache.	❌
`LIST JARS`	Displays the list of JAR files added to the distributed cache.	❌
`DELETE FILE`	Removes a file from the distributed cache.	❌
`DELETE JAR`	Removes a JAR file from the distributed cache.	❌
`! <command>`	Executes a shell command from the Hive interface.	❌
`DFS <dfs&gt command;`	Executes a DFS command from the Hive interface.	❌
`SOURCE <path file>`	Executes a script from a file in the CLI interface.	❌
`QUIT or EXIT`	Exit the Hive interactive interface.	❌

Transaction commands

Command	Description	Available
`START TRANSACTION`	Starts an explicit transaction.	❌
`COMMIT`	Validates changes made in the current transaction.	❌
`ROLLBACK`	Cancels changes made in the current transaction.	❌

Common commands

Join commands

Aggregation functions

Window functions

Mathematical functions

String functions

Date functions

Type management functions

Collection functions

DDL (Data Definition Language)

DML (Data Manipulation Language)

DCL (Data Control Language)commands

Commands to manage files and configuration

Transaction commands

Related articles