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';