Phoenix

jdbc连接工具 ——Phoenix

Phoenix、hive、impala，phoenix可能不成熟，但是速度要快

Phoenix安装很简单

1、解压Phoenix，

2、复制下面jar到各个节点的hbase/lib下

phoenix-4.14.1-HBase-1.2-client.jar
phoenix-4.14.1-HBase-1.2-server.jar
phoenix-core-4.14.1-HBase-1.2.jar

3、把以下xml文件复制到phoenix的bin下

hbase-site.xml
hdfs-site.xml
core-site.xml

4、最后再bin下启动./sqlline.py master 

这里的master是zookeeper的主机名

5、记得重启hbase ，这里的phoenix的版本的phoenix-4.14.1-HBase-1.2

期间遇到的问题，ntp没有同步，ha的hmaster和不是ha来回配置导致zk节点有问题，删除重新生成即可。zk可能也会有问题，有时候pioneix连接时会拒绝连接，重启未必好使，不过我重启系统好使了。

 

phoenix使用sql脚本创建表，使用csv格式导入文件，使用sql脚本查询数据

-- 建表文件
CREATE TABLE IF NOT EXISTS WEB_STAT (
     HOST CHAR(2) NOT NULL,
     DOMAIN VARCHAR NOT NULL,
     FEATURE VARCHAR NOT NULL,
     DATE DATE NOT NULL,
     USAGE.CORE BIGINT,
     USAGE.DB BIGINT,
     STATS.ACTIVE_VISITOR INTEGER
     CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE)
);

-- 数据文件
NA,Salesforce.com,Login,2013-01-01 01:01:01,35,42,10
EU,Salesforce.com,Reports,2013-01-02 12:02:01,25,11,2
EU,Salesforce.com,Reports,2013-01-02 14:32:01,125,131,42
NA,Apple.com,Login,2013-01-01 01:01:01,35,22,40
NA,Salesforce.com,Dashboard,2013-01-03 11:01:01,88,66,44
NA,Salesforce.com,Login,2013-01-04 06:01:21,3,52,1
EU,Apple.com,Mac,2013-01-01 01:01:01,35,22,34
NA,Salesforce.com,Login,2013-01-04 11:01:11,23,56,45
EU,Salesforce.com,Reports,2013-01-05 03:11:12,75,22,3
EU,Salesforce.com,Dashboard,2013-01-06 05:04:05,12,22,43


-- 查询文件
SELECT DOMAIN, AVG(CORE) Average_CPU_Usage, AVG(DB) Average_DB_Usage
FROM WEB_STAT
GROUP BY DOMAIN
ORDER BY DOMAIN DESC;
-- Sum, Min and Max CPU usage by Salesforce grouped by day
SELECT TRUNC(DATE,'DAY') DAY, SUM(CORE) TOTAL_CPU_Usage, MIN(CORE) MIN_CPU_Usage, MAX(CORE) MAX_CPU_Usage
FROM WEB_STAT
WHERE DOMAIN LIKE 'Salesforce%'
GROUP BY TRUNC(DATE,'DAY');
-- list host and total active users when core CPU usage is 10X greater than DB usage
SELECT HOST, SUM(ACTIVE_VISITOR) TOTAL_ACTIVE_VISITORS
FROM WEB_STAT
WHERE DB > (CORE * 10)
GROUP BY HOST;

创建表

./sqlline.py master ../mysql_file/create.sql

插入数据 

./psql.py -t WEB_STAT master ../mysql_file/data.csv

查询

./sqlline.py master ../mysql_file/select.sql

注意插入数据用的命令不一样