支持的命令如下:
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