Get started with HBase SQL (Phoenix)

Prerequisites

Before you begin, ensure that you have:

• A Phoenix runtime environment. If not, see Prepare an environment

Quick start

The following steps use the Phoenix SQL shell to create a table, insert data, and run a query. To connect from a Java application instead, see Phoenix JDBC API.

1. Create the us_population table.

   CREATE TABLE IF NOT EXISTS us_population (
      state CHAR(2) NOT NULL,
      city VARCHAR NOT NULL,
      population BIGINT
      CONSTRAINT my_pk PRIMARY KEY (state, city));

2. Insert data using UPSERT INTO.

   UPSERT INTO us_population VALUES('NY','New York',8143197);
   UPSERT INTO us_population VALUES('CA','Los Angeles',3844829);
   UPSERT INTO us_population VALUES('IL','Chicago',2842518);
   UPSERT INTO us_population VALUES('TX','Houston',2016582);
   UPSERT INTO us_population VALUES('PA','Philadelphia',1463281);
   UPSERT INTO us_population VALUES('AZ','Phoenix',1461575);
   UPSERT INTO us_population VALUES('TX','San Antonio',1256509);
   UPSERT INTO us_population VALUES('CA','San Diego',1255540);
   UPSERT INTO us_population VALUES('TX','Dallas',1213825);
   UPSERT INTO us_population VALUES('CA','San Jose',912332);

3. Query the data. The following example aggregates population by state in descending order.

   SELECT state as "State",count(city) as "City Count",sum(population) as "Population Sum"
   FROM us_population
   GROUP BY state
   ORDER BY sum(population) DESC;

4. Verify the result. Expected output:

   csv columns from database.
   CSV Upsert complete. 10 rows upserted
   Time: 0.159 sec(s)
   St                                     City Count                                   Population Sum
   -- ---------------------------------------- ---- -----------------------------------------------
   NY                                          1                                            8143197
   CA                                          3                                            6012701
   TX                                          3                                            4486916
   IL                                          1                                            2842518
   PA                                          1                                            1463281
   AZ                                          1                                            1461575
   Time: 0.16 sec(s)

Phoenix JDBC API

• Phoenix 5.x SDK Maven dependency:

  <dependency>
    <groupId>com.aliyun.phoenix</groupId>
    <artifactId>ali-phoenix-queryserver-client</artifactId>
    <version>5.2.1-HBase-2.x</version>
  </dependency>

• Phoenix 4.x SDK Maven dependency:

  <dependency>
      <groupId>com.aliyun.phoenix</groupId>
      <artifactId>ali-phoenix-core</artifactId>
      <version>${See the FAQ for the latest version}</version>
  </dependency>

References

For code examples, see Examples.