Apache Phoenix SQL command reference

You can use these data definition language (DDL) and data manipulation language (DML) commands to perform database operations on Apache Phoenix tables.

Table 1. Apache Phoenix commands
Command	Description	Simple example
CREATE TABLE	Creates a new table	CREATE TABLE your_schema.TEST_TABLE ( id BIGINT not null primary key, date Date);
DROP TABLE	Drops an existing table	DROP TABLE your_schema.your_table;
CREATE VIEW	Creates a new view over an existing Apache HBase or Phoenix table	CREATE VIEW "hbase table" ( k VARCHAR primary key, "h" UNSIGNED_LONG) default_column_family='cf1';
DROP VIEW	Drops an exisiting view. When you drop a view, the underlying table data is not affected; the index data for the view is deleted	DROP VIEW your_view;
SELECT	Selects data from one or more tables	SELECT * FROM TEST_TABLE LIMIT 1000;
UPSERT VALUES	Inserts value if the value is not present in the table and updates the value in the table if the value is already present. The list of columns is optional and if not present, the data is mapped to the column in the order they are declared in the schema	UPSERT INTO TEST_TABLE VALUES('A','B',4);
UPSERT SELECT	Inserts value if the value is not already present and updates the rows in the table based on the results of running the next query	UPSERT INTO test.targetTable(cq1, cq2) SELECT cq3, cq4 FROM test.sourceTable WHERE cq5 < 100