SQLLine

Updated on 2022-08-16 GMT+08:00

View PDF

You can run sqlline.py on the server to perform SQL operations on HBase. The SQLLine APIs of Phoenix are the same as those of the open-source community. For details, see http://phoenix.apache.org/.

Table 1 lists common SQLline syntax, Table 2 lists common functions, and Phoenix Command Line describes how to use commands.

**Table 1** Common syntax
Command	Description	Example
CREATE TABLE	Creates a table.	CREATE TABLE MY_TABLE(id BIGINT not null primary key, name VARCHAR);
ALTER	Alters a table or a view.	ALTER TABLE MY_TABLE DROP COLUMN name;
DROP TABLE	Deletes a table.	DROP TABLE MY_TABLE;
UPSERT VALUES	Inserts or changes data.	UPSERT INTO MY_TABLE VALUES(1,'abc');
SELECT	Queries data.	SELECT * FROM MY_TABLE;
CREATE INDEX	Creates a global index.	CREATE INDEX MY_IDX ON MY_TABLE(name);
CREATE LOCAL INDEX	Creates a local index.	CREATE LOCAL INDEX MY_LOCAL_IDX ON MY_TABLE(id,name);
ALTER INDEX	Changes the index status.	ALTER INDEX MY_IDX ON MY_TABLE DISABLE;
DROP INDEX	Deletes an index.	DROP INDEX MY_IDX ON MY_TABLE;
EXPLAIN	Displays an execution plan.	EXPLAIN SELECT name FROM MY_TABLE;
CREATE SEQUENCE	Creates a sequence.	CREATE SEQUENCE MY_SEQUENCE;
DROP SEQUENCE	Deletes a sequence.	DROP SEQUENCE MY_SEQUENCE;
CREATE VIEW	Creates a view.	CREATE VIEW MY_VIEW AS SELECT * FROM MY_TABLE;
DROP VIEW	Deletes a view.	DROP VIEW MY_VIEW;
CREATE SCHEMA	Creates a schema.	CREATE SCHEMA MY_SCHEMA;
USE	Modifies the default schema.	USE MY_SCHEMA;
DROP SCHEMA	Deletes a schema.	DROP SCHEMA MY_SCHEMA;

**Table 2** Common functions
Function Type	Function	Description	Example
String functions	SUBSTR	Extracts a part of a string.	SUBSTR('[Hello]', 2, 5)
	INSTR	Queries the position of the first occurrence of a string in another string.	INSTR('Hello World', 'World')
	TRIM	Removes whitespaces from both ends of a string.	TRIM(' Hello ')
	LTRIM	Removes whitespaces found on the left-hand side of the string.	LTRIM(' Hello')
	RTRIM	Removes whitespaces found on the right-hand side of the string.	RTRIM('Hello ')
	LPAD	Left-pads a string with another string.	LPAD('John',30,'*')
	LENGTH	Gets the length of a given string.	LENGTH('Hello')
	UPPER	Converts all the characters in a string to uppercase characters.	UPPER('Hello')
	LOWER	Converts all the characters in a string to lowercase characters.	LOWER('HELLO')
	REVERSE	Reverses a string.	REVERSE('Hello')
	REGEXP_SPLIT	Splits a string using a regular expression.	REGEXP_SPLIT('ONE,TWO,THREE', ',')
	REGEXP_REPLACE	Returns a string by applying a regular expression and replacing the matches with the replacement string.	REGEXP_REPLACE('abc123ABC', '[0-9]+', '#')
	REGEXP_SUBSTR	Returns a substring from a string based on a regular expression pattern.	REGEXP_SUBSTR('na1-appsrv35-sj35', '[^-]+')
Aggregate functions	AVG	Gets the average value.	AVG(X)
	COUNT	Gets the number of data records.	COUNT(*)
	MAX	Gets the maximum value.	MAX(NAME)
	MIN	Gets the minimum value.	MIN(NAME)
	SUM	Gets the sum of all values.	SUM(X)
	STDDEV_POP	Gets the population standard deviation of all values.	STDDEV_POP( X )
	STDDEV_SAMP	Gets the sample standard deviation of all values.	STDDEV_SAMP( X )
	NTH_VALUE	Gets the Nth value in each distinct group ordered according to the ORDER BY specification.	NTH_VALUE( name, 2 ) WITHIN GROUP (ORDER BY salary DESC)
Time and date functions	NOW	Returns the current date.	NOW()
	CURRENT_TIME	Returns the current time.	CURRENT_TIME()
	CURRENT_DATE	Returns the current date.	CURRENT_DATE()
	TO_DATE	Parses a string and returns a date.	TO_DATE('1970-01-01', 'yyyy-MM-dd', 'GMT+1')
	TO_TIME	Converts the given string into a TIME instance.	TO_TIME('1970-01-01', 'yyyy-MM-dd', 'GMT+1')
	TO_TIMESTAMP	Converts the given string into a TIMESTAMP instance.	TO_TIMESTAMP('1970-01-01', 'yyyy-MM-dd', 'GMT+1')
	YEAR	Returns the year of the specified date.	YEAR(TO_DATE('2015-6-05'))
	MONTH	Returns the month of the specified date.	MONTH(TO_TIMESTAMP('2015-6-05'))
	WEEK	Returns the week of the specified date.	WEEK(TO_TIME('2010-6-15'))
	HOUR	Returns the hour of the specified date.	HOUR(TO_TIMESTAMP('2015-6-05'))
	MINUTE	Returns the minute of the specified date.	MINUTE(TO_TIME('2015-6-05'))
	SECOND	Returns the second of the specified date.	SECOND(TO_DATE('2015-6-05'))
Numeric functions	ROUND	Rounds the numeric or timestamp expression to the nearest scale or time unit specified.	ROUND(2.56)
	CEIL	Rounds any fractional value up to the next even multiple.	CEIL(2.34)
	FLOOR	Rounds any fractional value down to the previous even multiple.	FLOOR(2.34)
	TRUNC	Rounds any fractional value down to the previous even multiple (same as FLOOR).	TRUNC(2.34)
	TO_NUMBER	Formats a string as a number.	TO_NUMBER('-123.33')
	RAND	Returns a random number.	RAND()
Math functions	ABS	Returns the absolute value of the given numeric expression.	ABS(-1)
	SQRT	$\text{[math]}$	SQRT(1.1)
	EXP	$\text{[math]}$	EXP(-1)
	POWER	$\text{[math]}$	POWER(2, 3)
	LN	$\text{[math]}$	LN(3)
	LOG	$\text{[math]}$	LOG(2, 3)
Array functions	ARRAY_ELEM	Uses the array subscript notation to access an array element.	ARRAY_ELEM(ARRAY[1,2,3], 1)
	ARRAY_PREPEND	Appends the given element to the beginning of the array.	ARRAY_APPEND(ARRAY[1,2,3], 4)
	ARRAY_CAT	Concatenates the input arrays and returns the result.	ARRAY_CAT(ARRAY[1,2], ARRAY[3,4])
	ARRAY_FILL	Fills an array with values.	ARRAY_FILL(1, 3)
	ARRAY_TO_STRING	Converts the elements of an array to a string and joins them using the specified delimiter.	ARRAY_TO_STRING(ARRAY['a','b','c'], ',')
	ANY	Used on the right-hand side of a comparison expression to test that any array element satisfies the comparison expression against the left-hand side.	10 > ANY(my_array)
	ALL	Used on the right-hand side of a comparison expression to test that all array elements satisfy the comparison expression against the left-hand side.	10 > ALL(my_array)
Other functions	MD5	Computes the MD5 hash of the argument.	MD5(my_column)
	ENCODE	Encodes the expression according to the encoding format provided.	ENCODE(myNumber, 'BASE62')
	DECODE	Decodes the expression according to the encoding format provided.	DECODE('000000008512af277ffffff8', 'HEX')