Apache Phoenix的Array类型

Apache Phoenix支持JDBC ARRAY类型，任何原生的数据类型就可以在ARRAY中使用。下面我介绍一下在创建的表中使用ARRAY类型。

 

先看一下创建表的SQL语句：

CREATE TABLE regions (

    region_name VARCHAR,

   zips VARCHAR ARRAY[10],

CONSTRAINT pk PRIMARY KEY (region_name)

);

 

或者创建ARRAY类型时不指定大小，如下：

CREATE TABLE regions (

    region_name VARCHAR,

   zips VARCHAR[],

CONSTRAINT pk PRIMARY KEY (region_name)

);

 

接着我们插入一些数据：

UPSERT INTO regions(region_name,zips)  VALUES('SFBay Area',ARRAY['94115','94030','94125']);

UPSERT INTO regions(region_name,zips) VALUES('PalaArea',ARRAY['94030','98030','95125']);

 

或者通过JDBC编程方式插入数据：

package com.pingan.phoenix;

 

import java.sql.Array;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

public class ConnPhoenixOp {

 

    public static void main(String[] args) throws SQLException {

      

       Statement stmt = null;

       ResultSet rset = null;

       PreparedStatement stmt2 = null;

      

       Connection conn = DriverManager.getConnection("jdbc:phoenix:10.20.18.24:2181:/hbase114");

       stmt = conn.createStatement();

      

       stmt.executeUpdate("DROPTABLE IF EXISTS regions");

       stmt.executeUpdate("CREATETABLE regions (region_name VARCHAR, zips VARCHAR[], CONSTRAINT pk PRIMARY KEY(region_name))");

       stmt.executeUpdate("UPSERTINTO regions(region_name,zips) VALUES('SF Bay Area', ARRAY['94115','94030','94125'])");

       conn.commit();

      

       stmt2 = conn.prepareStatement("UPSERT INTO regions VALUES(?,?)");

       stmt2.setString(1,"Pala Area");

       String[] zips =  new String[] {"94030","98030","95125"};

       Array array = conn.createArrayOf("VARCHAR", zips);

       stmt2.setArray(2, array);

       stmt2.executeUpdate();

       conn.commit();

      

       stmt2 = conn.prepareStatement("SELECT region_name FROM regions WHERE zips[1] = '94030' OR zips[2] ='94030' OR zips[3] = '94030'");

       rset = stmt2.executeQuery();

       while (rset.next()) {

           System.out.println(rset.getString("region_name"));

       }

       stmt2.close();

       stmt.close();

       conn.close();

    }

}

 

我们查看一下regions的全部数据：

 

过滤部分数据：

查询Array的部分内容：

SELECT zips[1]  FROM regions WHERE region_name = 'SF Bay Area';

结果：

+---------------------------------+

| ARRAY_ELEM(ZIPS, 1)  |

+----------------------------------+

| 94115                                 |

+----------------------------------+

 

SELECT region_name FROM regions WHERE zips[1] = '94030' OR zips[2] = '94030'  OR zips[3] = '94030';

结果为：

+-------------------------+

| REGION_NAME     |

+-------------------------+

| Pala Area                |

| SF Bay Area           |

+-------------------------+

 

查看Array中元素个数：

SELECT ARRAY_LENGTH(zips) FROM regions;

结果为：

+---------------------------------+

| ARRAY_LENGTH(ZIPS)  |

+----------------------------------+

| 3                                           |

| 3                                           |

+----------------------------------+

 

在Array中搜索相关内容，可以使用ANY和ALL内置函数：

SELECT region_name FROM regions WHERE '94030' = ANY(zips);

返回：

+--------------------------+

| REGION_NAME     |

+--------------------------+

| Pala Area                  |

| SF Bay Area             |

+-------------------------+

 

SELECT region_name FROM regions WHERE '94030' = ALL(zips);

没有结果。

 

上面使用ANY函数的SQL等价于：

SELECT region_name FROM regions WHERE zips[1] = '94030' OR zips[2] = '94030' OR zips[3] = '94030';

 

使用ALL函数的SQL等价于：

SELECT region_name FROM regions WHERE zips[1] = '94030' AND zips[2] = '94030' AND zips[3] = '94030';