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 TEST_TABLE your_schema.your_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