Apache Hbase Day4

Apache Hbase Day4

phoenix集成

Phoenix是构建在HBase上的一个SQL层，能让我们用标准的JDBC APIs而不是HBase客户端APIs来创建表，插入数据和对HBase数据进行查询。Phoenix完全使用Java编写，作为HBase内嵌的JDBC驱动。Phoenix查询引擎会将SQL查询转换为一个或多个HBase扫描，并编排执行以生成标准的JDBC结果集。下载apache-phoenix-4.10.0-HBase-1.2-bin.tar.gz,注意下载的Phoenix版本必须和hbase目标版本保持一致。

安装

1、确保HDFS/HBase正常运行

2、解压Phoenix的安装包，将phoenix-[version]-server.jar和phoenix-[version]-client.jar拷贝到所有运行HBase的节点的lib目录下

[root@CentOS ~]# tar -zxf apache-phoenix-4.10.0-HBase-1.2-bin.tar.gz -C /usr/
[root@CentOS ~]# mv  /usr/apache-phoenix-4.10.0-HBase-1.2-bin/ /usr/phoenix-4.10.0
[root@CentOS phoenix-4.10.0]# cp phoenix-4.10.0-HBase-1.2-client.jar /usr/hbase-1.2.4/lib/
[root@CentOS phoenix-4.10.0]# cp phoenix-4.10.0-HBase-1.2-server.jar /usr/hbase-1.2.4/lib/
[root@CentOS phoenix-4.10.0]#

3、强烈建议大家将HBase的历史残余数据给清楚之后再启动HBase

[root@CentOS ~]# hbase clean --cleanAll
[root@CentOS ~]# rm -rf /usr/hbase-1.2.4/logs/*
[root@CentOS ~]# start-hbase.sh

4、通过sqlline.py链接Hbase

[root@CentOS phoenix-4.10.0]# ./bin/sqlline.py CentOS
Setting property: [incremental, false]
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:CentOS none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:CentOS
....
Connected to: Phoenix (version 4.10)
Driver: PhoenixEmbeddedDriver (version 4.10)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
91/91 (100%) Done
Done
sqlline version 1.2.0
0: jdbc:phoenix:CentOS>

5、退出交互窗口

0: jdbc:phoenix:CentOS> !quit

基本使用

1、查看所有表

0: jdbc:phoenix:CentOS> !tables

2、创建表

0: jdbc:phoenix:CentOS> create table t_user(
. . . . . . . . . . . >   id integer primary key,
. . . . . . . . . . . >   name varchar(32),
. . . . . . . . . . . >   age integer,
. . . . . . . . . . . >   sex boolean
. . . . . . . . . . . > );
No rows affected (1.348 seconds)

3、查看表的字段信息

0: jdbc:phoenix:CentOS> !column t_user

4、插入/更新数据

0: jdbc:phoenix:CentOS> upsert into t_user values(1,'jiangzz',18,false);
1 row affected (0.057 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user values(1,'jiangzz',18,true);
1 row affected (0.006 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user values(2,'lisi',20,true);
1 row affected (0.023 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user values(3,'wangwu',18,false);
1 row affected (0.006 seconds)

0: jdbc:phoenix:CentOS> select * from t_user;
+-----+----------+------+--------+
| ID  |   NAME   | AGE  |  SEX   |
+-----+----------+------+--------+
| 1   | jiangzz  | 18   | true   |
| 2   | lisi     | 20   | true   |
| 3   | wangwu   | 18   | false  |
+-----+----------+------+--------+
3 rows selected (0.085 seconds)

5、更改某个字段值

0: jdbc:phoenix:CentOS> upsert into  t_user(id,name) values(1,'win7');
1 row affected (0.024 seconds)

0: jdbc:phoenix:CentOS> select * from t_user;
+-----+---------+------+--------+
| ID  |  NAME   | AGE  |  SEX   |
+-----+---------+------+--------+
| 1   | win7    | 18   | true   |
| 2   | lisi    | 20   | true   |
| 3   | wangwu  | 18   | false  |
+-----+---------+------+--------+
3 rows selected (0.201 seconds)

6、执行某些统计操作

0: jdbc:phoenix:CentOS> select sex,avg(age),max(age),min(age),sum(age) from t_user group by sex;
+--------+-----------+-----------+-----------+-----------+
|  SEX   | AVG(AGE)  | MAX(AGE)  | MIN(AGE)  | SUM(AGE)  |
+--------+-----------+-----------+-----------+-----------+
| false  | 18        | 18        | 18        | 18        |
| true   | 19        | 20        | 18        | 38        |
+--------+-----------+-----------+-----------+-----------+
2 rows selected (0.123 seconds)
0: jdbc:phoenix:CentOS> select sex,avg(age),max(age),min(age),sum(age) total from t_user group by sex order by total  desc;
+--------+-----------+-----------+-----------+--------+
|  SEX   | AVG(AGE)  | MAX(AGE)  | MIN(AGE)  | TOTAL  |
+--------+-----------+-----------+-----------+--------+
| true   | 19        | 20        | 18        | 38     |
| false  | 18        | 18        | 18        | 18     |
+--------+-----------+-----------+-----------+--------+
2 rows selected (0.072 seconds)
0: jdbc:phoenix:CentOS>

7、数据库操作

0: jdbc:phoenix:CentOS> create schema if not exists baizhi;

提示必须同时修改HASE_HOME/conf/hbase-site.xml文件和 ​PHOENIX_HOME/bin/hbase-site.xml文件，修改完成重启Hbase服务

<property>
  <name>phoenix.schema.isNamespaceMappingEnabled</name>
  <value>true</value>
</property>
<property>
  <name>phoenix.schema.mapSystemTablesToNamespace</name>
  <value>true</value>
</property>

0: jdbc:phoenix:CentOS> create schema if not exists baizhi;
No rows affected (0.046 seconds)
0: jdbc:phoenix:CentOS> use baizhi;
No rows affected (0.049 seconds)
0: jdbc:phoenix:CentOS> create table if not exists t_user(
. . . . . . . . . . . >  id integer primary key ,
. . . . . . . . . . . >  name varchar(128),
. . . . . . . . . . . >  sex boolean,
. . . . . . . . . . . >  birthDay date,
. . . . . . . . . . . >  salary decimal(7,2)
. . . . . . . . . . . > );

如果用户不指定schema，默认使用的是default数据库

8、查看建表详情，等价!column

0: jdbc:phoenix:CentOS> !desc baizhi.t_user;

9、删除表

0: jdbc:phoenix:CentOS> drop table if exists baizhi.t_user;
No rows affected (3.638 seconds)

如果有其他表指向该表，我们可以在删除的表时候添加cascade关键字

0: jdbc:phoenix:CentOS> drop table if exists baizhi.t_user cascade;
No rows affected (0.004 seconds)

10、修改表

①添加字段

0: jdbc:phoenix:CentOS> alter table t_user add age integer;
No rows affected (5.994 seconds)

②删除字段

0: jdbc:phoenix:CentOS> alter table t_user drop column age;
No rows affected (1.059 seconds)

③设置表的TimeToLive

0: jdbc:phoenix:CentOS> alter table t_user set TTL=100;
No rows affected (5.907 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user(id,name,sex,birthDay,salary) values(1,'jiangzz',true,'1990-12-16',5000.00);
1 row affected (0.031 seconds)
0: jdbc:phoenix:CentOS> select * from t_user;
+-----+----------+-------+--------------------------+---------+
| ID  |   NAME   |  SEX  |         BIRTHDAY         | SALARY  |
+-----+----------+-------+--------------------------+---------+
| 1   | jiangzz  | true  | 1990-12-16 00:00:00.000  | 5E+3    |
+-----+----------+-------+--------------------------+---------+
1 row selected (0.074 seconds)

11、数据DML

①插入&更新

0: jdbc:phoenix:CentOS> upsert into t_user(id,name,sex,birthDay,salary) values(1,'jiangzz',true,'1990-12-16',5000.00);
1 row affected (0.014 seconds)

②删除记录

0: jdbc:phoenix:CentOS> delete from t_user where name='jiangzz';
1 row affected (0.014 seconds)
0: jdbc:phoenix:CentOS> select * from t_user;
+-----+-------+------+-----------+---------+
| ID  | NAME  | SEX  | BIRTHDAY  | SALARY  |
+-----+-------+------+-----------+---------+
+-----+-------+------+-----------+---------+
No rows selected (0.094 seconds)

③查询数据

0: jdbc:phoenix:CentOS> select * from t_user;
+-----+-----------+--------+--------------------------+---------+
| ID  |   NAME    |  SEX   |         BIRTHDAY         | SALARY  |
+-----+-----------+--------+--------------------------+---------+
| 1   | jiangzz   | true   | 1990-12-16 00:00:00.000  | 5E+3    |
| 2   | zhangsan  | false  | 1990-12-16 00:00:00.000  | 6E+3    |
+-----+-----------+--------+--------------------------+---------+
2 rows selected (0.055 seconds)
0: jdbc:phoenix:CentOS> select * from t_user where name like '%an%' order by salary desc limit 10;
+-----+-----------+--------+--------------------------+---------+
| ID  |   NAME    |  SEX   |         BIRTHDAY         | SALARY  |
+-----+-----------+--------+--------------------------+---------+
| 2   | zhangsan  | false  | 1990-12-16 00:00:00.000  | 6E+3    |
| 1   | jiangzz   | true   | 1990-12-16 00:00:00.000  | 5E+3    |
+-----+-----------+--------+--------------------------+---------+
2 rows selected (0.136 seconds)

√代码集成Phoenix

JDBC集成

①将phoenix-{version}-client.jar驱动jar安装到本地maven仓库

C:\Users\513jiaoshiji>mvn  install:install-file   -DgroupId=org.apche.phoenix    -DartifactId=phoenix   -Dversion=phoenix-4.10-hbase-1.2   -Dpackaging=jar    -Dfile=C:\Users\513jiaoshiji\Desktop\phoenix-4.10.0-HBase-1.2-client.jar
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------< org.apache.maven:standalone-pom >-------------------
[INFO] Building Maven Stub Project (No POM) 1
[INFO] --------------------------------[ pom ]---------------------------------
[INFO]
[INFO] --- maven-install-plugin:2.4:install-file (default-cli) @ standalone-pom ---
[INFO] Installing C:\Users\513jiaoshiji\Desktop\phoenix-4.10.0-HBase-1.2-client.jar to D:\m2\org\apche\phoenix\phoenix\phoenix-4.10-hbase-1.2\phoenix-phoenix-4.10-hbase-1.2.jar
[INFO] Installing C:\Users\513JIA~1\AppData\Local\Temp\mvninstall6381038564796043649.pom to D:\m2\org\apche\phoenix\phoenix\phoenix-4.10-hbase-1.2\phoenix-phoenix-4.10-hbase-1.2.pom
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  0.657 s
[INFO] Finished at: 2020-10-13T11:39:16+08:00
[INFO] ------------------------------------------------------------------------

mvn install:install-file -DgroupId=groupID -DartifactId=artifactID -Dversion=版本 -Dpackaging=jar -Dfile=jar路径

②将hbase-site.xml文件导入到项目的Resource资源目录

③编写jdbc代码

Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
Connection conn = DriverManager.getConnection("jdbc:phoenix:CentOS:2181");
PreparedStatement pstm = conn.prepareStatement("select * from baizhi.t_user");
ResultSet resultSet = pstm.executeQuery();
while(resultSet.next()){
    String name = resultSet.getString("name");
    Integer id = resultSet.getInt("id");
    System.out.println(id+"\t"+name);
}
resultSet.close();
pstm.close();
conn.close();

需要注意Phoenix的JDBC在执行修改的时候，默认情况下自动提交时false，这一点和MySQL或者Oracle不同。

Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
Connection conn = DriverManager.getConnection("jdbc:phoenix:CentOS:2181");
conn.setAutoCommit(true);//必须设置，否则数据不提交！

PreparedStatement pstm = conn.prepareStatement("upsert into baizhi.t_user(id,name,sex,birthDay,salary) values(?,?,?,?,?)");
pstm.setInt(1,2);
pstm.setString(2,"张三1");
pstm.setBoolean(3,true);
pstm.setDate(4,new Date(System.currentTimeMillis()));
pstm.setBigDecimal(5,new BigDecimal(1000.0));
pstm.execute();


pstm.close();
conn.close();

MapReduce集成

①准备输入表/输出表

CREATE TABLE IF NOT EXISTS STOCK (
    STOCK_NAME VARCHAR NOT NULL ,
    RECORDING_YEAR INTEGER NOT  NULL, 
    RECORDINGS_QUARTER DOUBLE array[] CONSTRAINT pk PRIMARY KEY (STOCK_NAME , RECORDING_YEAR)
);

CREATE TABLE IF NOT EXISTS STOCK_STATS (
    STOCK_NAME VARCHAR NOT NULL , 
    MAX_RECORDING DOUBLE CONSTRAINT pk PRIMARY KEY (STOCK_NAME)
);

②插入模拟数据

UPSERT into STOCK values ('AAPL',2009,ARRAY[85.88,91.04,88.5,90.3]);
UPSERT into STOCK values ('AAPL',2008,ARRAY[199.27,200.26,192.55,194.84]);
UPSERT into STOCK values ('AAPL',2007,ARRAY[86.29,86.58,81.90,83.80]);
UPSERT into STOCK values ('CSCO',2009,ARRAY[16.41,17.00,16.25,16.96]);
UPSERT into STOCK values ('CSCO',2008,ARRAY[27.00,27.30,26.21,26.54]);
UPSERT into STOCK values ('CSCO',2007,ARRAY[27.46,27.98,27.33,27.73]);
UPSERT into STOCK values ('CSCO',2006,ARRAY[17.21,17.49,17.18,17.45]);
UPSERT into STOCK values ('GOOG',2009,ARRAY[308.60,321.82,305.50,321.32]);
UPSERT into STOCK values ('GOOG',2008,ARRAY[692.87,697.37,677.73,685.19]);
UPSERT into STOCK values ('GOOG',2007,ARRAY[466.00,476.66,461.11,467.59]);
UPSERT into STOCK values ('GOOG',2006,ARRAY[422.52,435.67,418.22,435.23]);
UPSERT into STOCK values ('MSFT',2009,ARRAY[19.53,20.40,19.37,20.33]);
UPSERT into STOCK values ('MSFT',2008,ARRAY[35.79,35.96,35.00,35.22]);
UPSERT into STOCK values ('MSFT',2007,ARRAY[29.91,30.25,29.40,29.86]);
UPSERT into STOCK values ('MSFT',2006,ARRAY[26.25,27.00,26.10,26.84]);
UPSERT into STOCK values ('YHOO',2009,ARRAY[12.17,12.85,12.12,12.85]);
UPSERT into STOCK values ('YHOO',2008,ARRAY[23.80,24.15,23.60,23.72]);
UPSERT into STOCK values ('YHOO',2007,ARRAY[25.85,26.26,25.26,25.61]);
UPSERT into STOCK values ('YHOO',2006,ARRAY[39.69,41.22,38.79,40.91]);

③编写代码

public class PhoenixStockApplication extends Configured implements Tool {

    public int run(String[] strings) throws Exception {
        //1.创建job
        Configuration conf = getConf();
        conf.set(HConstants.ZOOKEEPER_QUORUM,"CentOS");
        conf= HBaseConfiguration.create(conf);
        Job job= Job.getInstance(conf,"PhoenixStockApplication");
        job.setJarByClass(PhoenixStockApplication.class);

        //2.设置输入输出格式
        job.setInputFormatClass(PhoenixInputFormat.class);
        job.setOutputFormatClass(PhoenixOutputFormat.class);

        //3.设置数据读入和写出路径
        String selectQuery = "SELECT STOCK_NAME,RECORDING_YEAR,RECORDINGS_QUARTER FROM STOCK ";
        PhoenixMapReduceUtil.setInput(job, StockWritable.class, "STOCK",  selectQuery);
        PhoenixMapReduceUtil.setOutput(job, "STOCK_STATS", "STOCK_NAME,MAX_RECORDING");

        //4.设置代码片段
        job.setMapperClass(StockMapper.class);
        job.setReducerClass(StockReducer.class);

        //5.设置Mapper和Reducer端的输出key,value类型
        job.setMapOutputKeyClass(Text.class);
        job.setMapOutputValueClass(DoubleWritable.class);
        job.setOutputKeyClass(NullWritable.class);
        job.setOutputValueClass(StockWritable.class);

        TableMapReduceUtil.addDependencyJars(job);

        //6.任务提交
        return job.waitForCompletion(true)?0:1;
    }

    public static void main(String[] args) throws Exception {
        ToolRunner.run(new PhoenixStockApplication(),args);
    }
}

public class StockWritable implements DBWritable {
    private String stockName;
    private int year;
    private double[] recordings;

    private double maxPrice;

    public void write(PreparedStatement pstmt) throws SQLException {
        pstmt.setString(1, stockName);
        pstmt.setDouble(2, maxPrice);
    }

    public void readFields(ResultSet rs) throws SQLException {
        stockName = rs.getString("STOCK_NAME");
        year = rs.getInt("RECORDING_YEAR");
        Array recordingsArray = rs.getArray("RECORDINGS_QUARTER");
        recordings = (double[])recordingsArray.getArray();
    }

    //get/set
}

public class StockMapper extends Mapper<NullWritable,StockWritable, Text, DoubleWritable> {

    @Override
    protected void map(NullWritable key, StockWritable value, Context context) throws IOException, InterruptedException {
        double[] recordings = value.getRecordings();
        double maxPrice = Double.MIN_VALUE;
        for (double recording : recordings) {
            if(maxPrice<recording){
                maxPrice=recording;
            }
        }
        context.write(new Text(value.getStockName()),new DoubleWritable(maxPrice));
    }
}

public class StockReducer extends Reducer<Text, DoubleWritable, NullWritable,StockWritable> {
    @Override
    protected void reduce(Text key, Iterable<DoubleWritable> values, Context context) throws IOException, InterruptedException {
      Double maxPrice=Double.MIN_VALUE;
        for (DoubleWritable value : values) {
            double v = value.get();
            if(maxPrice<v){
                maxPrice=v;
            }
        }
        StockWritable stockWritable = new StockWritable();
        stockWritable.setStockName(key.toString());
        stockWritable.setMaxPrice(maxPrice);

        context.write(NullWritable.get(),stockWritable);
    }
}

hdfs dfs -put MapReduce02-1.0-SNAPSHOT.jar /lib
需要将运行的jar添加到hadoop的类路径下。
HADOOP_CLASSPATH=$HBASE_CLASSPATH:/root/phoenix-4.10.0-HBase-1.2-client.jar
运行:hadoop jar MapReduce02-1.0-SNAPSHOT.jar phoenix.PhoenixStockApplication -libjars phoenix-4.10.0-HBase-1.2-client.jar

Phoenix GUI使用

如果您希望使用客户端GUI与Phoenix进行交互，请下载并安装SQuirrel。由于Phoenix是JDBC驱动程序，因此与此类工具的集成是无缝的。以下是下载和安装步骤：
点击：http://squirrel-sql.sourceforge.net/

1、下载客户端软件包,然后解压

2、将phoenix-{version}-client.jar拷贝到该软件的lib目录下

3、直接点击该软件下的squirrel-sql.bat 如果是mac或者linux系统用户，可以直接运行squirrel-sql.sh

4、点击Dirver选项卡，点击＋号，添加驱动

5、填写相关模板参数

6、点击Aliasses选项卡，添加＋号，添加驱动

7、点击Test按钮，确保能够连接成功！

该客户端存在缺陷，不支持自定义Schema映射，因此需要将Hbase的schame映射给关闭才可以使用。