Phoenix对QL的支持

支持的命令如下：

SELECT：查询语句
Example:

#获取1000条数据
SELECT * FROM TEST LIMIT 1000;

#获取1000条数据，从第100条数据开始
SELECT * FROM TEST LIMIT 1000 OFFSET 100;

#UNION 操作符用于合并两个或多个SELECT语句的结果集
#UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
SELECT full_name FROM SALES_PERSON WHERE ranking >= 5.0 UNION ALL SELECT reviewer_name FROM CUSTOMER_REVIEW WHERE score >= 8.0

UPSERT VALUES ：插入或更新语句
phoenix中不存在insert和update语句，upsert可以实现插入和更新操作。
Example:

#插入
UPSERT INTO TEST VALUES('foo','bar',3);

#更新（只需要在upsert语句中制定存在的id即可实现更新）
UPSERT INTO TEST(NAME,ID) VALUES('foo',123);

UPSERT SELECT：根据查询结果更新或插入
Example:

#1
UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM test.sourceTable WHERE col5 < 100;

#2
UPSERT INTO foo SELECT * FROM bar;

DELETE：删除数据
Example:

#删除TEST表中所有的数据
DELETE FROM TEST;

#删除TEST表中ID等于123的数据
DELETE FROM TEST WHERE ID=123;

#删除TEST表中NAME以‘foo’开头的数据
DELETE FROM TEST WHERE NAME LIKE 'foo%';

CREATE TABLE：创建表
Example:

#在my_schema中创建my_table表
CREATE TABLE my_schema.my_table ( id BIGINT not null primary key, date);

#创建my_table表
CREATE TABLE my_table ( id INTEGER not null primary key desc, date DATE not null,m.db_utilization DECIMAL, i.db_utilization) m.DATA_BLOCK_ENCODING='DIFF'

CREATE TABLE stats.prod_metrics ( host char(50) not null, created_date date not null,txn_count bigint CONSTRAINT pk PRIMARY KEY (host, created_date) )

CREATE TABLE IF NOT EXISTS "my_case_sensitive_table"
    ( "id" char(10) not null primary key, "value" integer)
 DATA_BLOCK_ENCODING='NONE',VERSIONS=5,MAX_FILESIZE=2000000 split on (?, ?, ?)

CREATE TABLE IF NOT EXISTS my_schema.my_table (org_id CHAR(15), entity_id CHAR(15), payload binary(1000),CONSTRAINT pk PRIMARY KEY (org_id, entity_id) )TTL=86400

DROP TABLE
Example:

DROP TABLE my_schema.my_table;
DROP TABLE IF EXISTS my_table;
DROP TABLE my_schema.my_table CASCADE;

CREATE FUNCTION
Example:

CREATE FUNCTION my_reverse(varchar) returns varchar as 'com.mypackage.MyReverseFunction' using jar 'hdfs:/localhost:8080/hbase/lib/myjar.jar'
CREATE FUNCTION my_reverse(varchar) returns varchar as 'com.mypackage.MyReverseFunction'
CREATE FUNCTION my_increment(integer, integer constant defaultvalue='10') returns integer as 'com.mypackage.MyIncrementFunction' using jar '/hbase/lib/myincrement.jar'
CREATE TEMPORARY FUNCTION my_reverse(varchar) returns varchar as 'com.mypackage.MyReverseFunction' using jar 'hdfs:/localhost:8080/hbase/lib/myjar.jar'

DROP FUNCTION
Example:

DROP FUNCTION IF EXISTS my_reverse
DROP FUNCTION my_reverse

CREATE VIEW
Example:

CREATE VIEW "my_hbase_table"( k VARCHAR primary key, "v" UNSIGNED_LONG) default_column_family='a';
CREATE VIEW my_view ( new_col SMALLINT ) AS SELECT * FROM my_table WHERE k = 100;
CREATE VIEW my_view_on_view AS SELECT * FROM my_view WHERE new_col > 70;

DROP VIEW
Example:

DROP VIEW my_view
DROP VIEW IF EXISTS my_schema.my_view
DROP VIEW IF EXISTS my_schema.my_view CASCADE

CREATE SEQUENCE
Example:

CREATE SEQUENCE my_sequence;
CREATE SEQUENCE my_sequence START WITH -1000
CREATE SEQUENCE my_sequence INCREMENT BY 10
CREATE SEQUENCE my_schema.my_sequence START 0 CACHE 10

DROP SEQUENCE
Example:

DROP SEQUENCE my_sequence
DROP SEQUENCE IF EXISTS my_schema.my_sequence

ALTER
Example:

ALTER TABLE my_schema.my_table ADD d.dept_id char(10) VERSIONS=10
ALTER TABLE my_table ADD dept_name char(50), parent_id char(15) null primary key
ALTER TABLE my_table DROP COLUMN d.dept_id, parent_id;
ALTER VIEW my_view DROP COLUMN new_col;
ALTER TABLE my_table SET IMMUTABLE_ROWS=true,DISABLE_WAL=true;

CREATE INDEX
Example:

CREATE INDEX my_idx ON sales.opportunity(last_updated_date DESC)
CREATE INDEX my_idx ON log.event(created_date DESC) INCLUDE (name, payload) SALT_BUCKETS=10
CREATE INDEX IF NOT EXISTS my_comp_idx ON server_metrics ( gc_time DESC, created_date DESC ) DATA_BLOCK_ENCODING='NONE',VERSIONS=?,MAX_FILESIZE=2000000 split on (?, ?, ?)
CREATE INDEX my_idx ON sales.opportunity(UPPER(contact_name))

DROP INDEX
Example:

DROP INDEX my_idx ON sales.opportunity
DROP INDEX IF EXISTS my_idx ON server_metrics

ALTER INDEX
Example:

ALTER INDEX my_idx ON sales.opportunity DISABLE
ALTER INDEX IF EXISTS my_idx ON server_metrics REBUILD

EXPLAIN
Example:

EXPLAIN SELECT NAME, COUNT(*) FROM TEST GROUP BY NAME HAVING COUNT(*) > 2;
EXPLAIN SELECT entity_id FROM CORE.CUSTOM_ENTITY_DATA WHERE organization_id='00D300000000XHP' AND SUBSTR(entity_id,1,3) = '002' AND created_date < CURRENT_DATE()-1;

UPDATE STATISTICS
Example:

UPDATE STATISTICS my_table
UPDATE STATISTICS my_schema.my_table INDEX
UPDATE STATISTICS my_index
UPDATE STATISTICS my_table COLUMNS
UPDATE STATISTICS my_table SET phoenix.stats.guidepost.width=50000000

CREATE SCHEMA
Example:

CREATE SCHEMA IF NOT EXISTS my_schema
CREATE SCHEMA my_schema

USE
Example:

USE my_schema
USE DEFAULT

DROP SCHEMA
Example:

DROP SCHEMA IF EXISTS my_schema
DROP SCHEMA my_schema

参考：https://phoenix.apache.org/language/index.html