Apache Phoenix Tutorial: Quick Start, Data Types, DML, Indexes, Salted Tables, and Advanced Features

Apache Phoenix is an open‑source SQL layer on HBase that enables standard JDBC access, supports secondary indexes, salted tables, and multiple SQL optimizations.

The article provides a quick‑start guide with example CREATE TABLE and UPSERT scripts, explains the 24 supported data types and their Java mappings, and demonstrates SELECT, UPSERT VALUES, UPSERT SELECT, and DELETE operations with sample SQL.

It describes how to create salted tables to avoid hotspot writes, including the SALT_BUCKETS property and the hashing algorithm used to generate new row keys.

Different secondary index types—global, local, covering, and function indexes—are introduced with CREATE INDEX examples, the structure of index row keys, and tips for index building (synchronous vs asynchronous).

Bulk‑load techniques using MapReduce tools such as CsvBulkLoadTool, JsonBulkLoadTool, and ODPSBulkLoadTool are covered, along with HDFS configuration for cloud HBase.

Guidance on using auto‑increment sequences, dynamic columns, pagination (LIMIT/OFFSET/FETCH), and best‑practice recommendations for global index design are included.

The article explains how to view and interpret Phoenix query plans, the meaning of operators like RANGE SCAN and SKIP SCAN, and how to obtain plan estimates via the JDBC API.

Finally, data migration options are discussed, including BulkLoad tools and DataX integration for importing and exporting data.