Hbase入门到实战-连载六(Phoenix)

最新推荐文章于 2024-04-08 10:20:43 发布

工作变成艺术

最新推荐文章于 2024-04-08 10:20:43 发布

阅读量836

点赞数 2

分类专栏： Hbase 文章标签： hbase 数据库大数据

本文链接：https://blog.csdn.net/S1124654/article/details/125586358

版权

Hbase 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Phoenix简介

Phoenix 是 HBase 的开源 SQL 中间层，它允许你使用标准 JDBC 的方式来操作 HBase 上的数据。在 Phoenix 之前，如果你要访问 HBase，只能调用它的 Java API，但相比于使用一行 SQL 就能实现数据查询，HBase 的 API 还是过于复杂。Phoenix 的理念是 we put sql SQL back in NOSQL，即你可以使用标准的 SQL 就能完成对 HBase 上数据的操作。同时这也意味着你可以通过集成 Spring Data JPA 或 Mybatis 等常用的持久层框架来操作 HBase。

其次 Phoenix 的性能表现也非常优异，Phoenix 查询引擎会将 SQL 查询转换为一个或多个 HBase Scan，通过并行执行来生成标准的 JDBC 结果集。它通过直接使用 HBase API 以及协处理器和自定义过滤器，可以为小型数据查询提供毫秒级的性能，为千万行数据的查询提供秒级的性能。同时 Phoenix 还拥有二级索引等 HBase 不具备的特性，因为以上的优点，所以 Phoenix 成为了 HBase 最优秀的 SQL 中间层。

Phoenix安装

我们可以按照官方安装说明进行安装，官方说明如下：

download and expand our installation tar
copy the phoenix server jar that is compatible with your HBase installation into the lib directory of every region server
restart the region servers
add the phoenix client jar to the classpath of your HBase client
download and setup SQuirrel as your SQL client so you can issue adhoc SQL against your HBase cluster

下载

Phoenix Downloads | Apache Phoenix

当然如果一直看我专栏的小伙伴也知道我一般都会提供安装包

链接：https://pan.baidu.com/s/1UknmkVVPpFPKV7C53gxkTA
提取码：yyds

tar -zxvf phoenix-hbase-2.4-5.1.2-bin.tar.gz

拷贝Jar包

按照官方文档的说明，需要将 phoenix server jar 添加到所有 Region Servers 的安装目录的 lib 目录下。

cp phoenix-server-hbase-2.4-5.1.2.jar /home/bigdata/module/hbase-2.4.9/lib

分发到其他的 Region Servers

./xsync /home/bigdata/module/hbase-2.4.9/lib/phoenix-server-hbase-2.4-5.1.2.jar

修改phoenix中的hbase-site.xml配置文件

vi /home/bigdata/module/phoenix-hbase-2.4-5.1.2-bin/bin/hbase-site.xml

添加配置

<!-- 注意：为了开启hbase的namespace和phoenix的schema的映射，在程序中需要加这个配置文件，另外在linux服务上，也需要在hbase以及phoenix的hbase-site.xml配置文件中，加上以上两个配置，并使用xsync进行同步-->
<property>
    <name>phoenix.schema.isNamespaceMappingEnabled</name>
    <value>true</value>
</property>

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

重启hbase集群

创建一个phoenix启动脚本

vi phoenix.sh

#!/bin/bash
/home/bigdata/module/phoenix-hbase-2.4-5.1.2-bin/bin/sqlline.py hadoop102,hadoop103,hadoop104:2181

chmod 744 phoenix.sh

./phoenix.sh

测试

create schema test;

如果没有报错说明安装成功

Phoenix 简单使用

创建表

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

新建的表会按照特定的规则转换为 HBase 上的表，关于表的信息，可以通过 Hbase Web UI 进行查看：

插入数据

Phoenix 中插入数据采用的是 UPSERT 而不是 INSERT,因为 Phoenix 并没有更新操作，插入相同主键的数据就视为更新，所以 UPSERT 就相当于 UPDATE+INSERT

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

修改数据

-- 插入主键相同的数据就视为更新
UPSERT INTO us_population VALUES('NY','New York',999999);

删除数据

DELETE FROM us_population WHERE city='Dallas';

查询数据

SELECT state as "州",count(city) as "市",sum(population) as "热度"
FROM us_population
GROUP BY state
ORDER BY sum(population) DESC;

退出命令

!quit

Phoenix和Hbase的表映射

准备数据

先在hbase准备数据

create_namespace 'school';

create 'school:stu','info';

put 'school:stu','1001','info:id','1';
put 'school:stu','1001','info:name','lishi';
put 'school:stu','1001','info:age','18';

put 'school:stu','1002','info:id','2';
put 'school:stu','1002','info:name','zhangsan';
put 'school:stu','1002','info:age','20' ;

scan 'school:stu';

效果如下

Phoenix创建视图对应Hbase表(常用)

create view "school"."stu"
("id" varchar primary key,
"info"."name" varchar,
"info"."age" varchar
);

select * from "school"."stu";

创建视图的好处

不用先创建schema
删除视图不会影响Hbase里面的数据

比如我们执行下面的操作

drop view "school"."stu";

如图Hbase里面的数据还在，那么我们就可以在用上面的数据进行数据的查询（缺点就是不能够删除数据）

Phoenix创建表对应Hbase表

创建表对应的话那么就必须先创建schema

create schema "school";

create table "school"."stu"
("id" varchar primary key,
"info"."name" varchar,
"info"."age" varchar
)
column_encoded_bytes=0;

4.10版本之后phoenix默认会对字段值进行编码，在创建表中加入column_encoded_bytes=0属

select * from "school"."stu";

删除一条数据看下

delete from "school"."stu" where "id"='1001';

看到数据已经删除了

下面删除下表

drop table "school"."stu";

看到Hbase里面的表也删除了

总结：

对于OLAP的应用程序来说，推荐使用视图的方式

Phoenix Java API

maven添加下面的依赖

       <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>5.0.0-HBase-2.0</version>
        </dependency>
        <!-- phoenix 客户端 -->
        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-queryserver-client</artifactId>
            <version>5.0.0-HBase-2.0</version>
        </dependency>

在resources创建一个hbase-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!--
/**
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
-->
<configuration>
    <property>
        <name>hbase.regionserver.wal.codec</name>
        <value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
    </property>


    <!-- 注意：为了开启hbase的namespace和phoenix的schema的映射，在程序中需要加这个配置文件，另外在linux服务上，也需要在hbase以及phoenix的hbase-site.xml配置文件中，加上以上两个配置，并使用xsync进行同步-->
    <property>
        <name>phoenix.schema.isNamespaceMappingEnabled</name>
        <value>true</value>
    </property>

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

代码如下

public class PhoenixOps {
    private static Connection connection = null;
    private static PreparedStatement statement = null;

    public static void main(String[] args) {
        try {
            Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
            //master是zookeeper的地址
            connection = DriverManager.getConnection("jdbc:phoenix:hadoop102:2181");
            System.out.println(connection);

            statement = connection.prepareStatement("select * from \"school\".\"stu\"");

            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                System.out.println(resultSet.getString("name") + " "
                        + resultSet.getString("age")+resultSet.getString("id"));
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("加载Phoenix驱动失败!");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

结果如图

Spring Boot 整合 Mybatis + Phoenix

创建一个springboot应用

添加依赖pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>springbootphoenix</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.5.RELEASE</version>
    </parent>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--spring 1.5 x 以上版本对应 mybatis 1.3.x (1.3.1)
        关于更多 spring-boot 与 mybatis 的版本对应可以参见 <a href="http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/">-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--phoenix core-->
        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>5.0.0-HBase-2.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!--        测试-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common -->
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>2.9.2</version>
        </dependency>


    </dependencies>

</project>

application.yml

spring:
  datasource:
    #zookeeper 地址
    url: jdbc:phoenix:hadoop102:2181
    driver-class-name: org.apache.phoenix.jdbc.PhoenixDriver

    # 如果不想配置对数据库连接池做特殊配置的话,以下关于连接池的配置就不是必须的
    # spring-boot 2.X 默认采用高性能的 Hikari 作为连接池 更多配置可以参考 https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      # 池中维护的最小空闲连接数
      minimum-idle: 10
      # 池中最大连接数，包括闲置和使用中的连接
      maximum-pool-size: 20
      # 此属性控制从池返回的连接的默认自动提交行为。默认为 true
      auto-commit: true
      # 允许最长空闲时间
      idle-timeout: 30000
      # 此属性表示连接池的用户定义名称，主要显示在日志记录和 JMX 管理控制台中，以标识池和池配置。 默认值：自动生成
      pool-name: custom-hikari
      #此属性控制池中连接的最长生命周期，值 0 表示无限生命周期，默认 1800000 即 30 分钟
      max-lifetime: 1800000
      # 数据库连接超时时间,默认 30 秒，即 30000
      connection-timeout: 30000
      # 连接测试 sql 这个地方需要根据数据库方言差异而配置 例如 oracle 就应该写成  select 1 from dual
      connection-test-query: SELECT 1

# mybatis 相关配置
mybatis:
  mapper-locations: classpath*:mapper/*.xml
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl是否打印 sql 语句 调试的时候可以开启

hbase-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!--
/**
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
-->
<configuration>
    <property>
        <name>hbase.regionserver.wal.codec</name>
        <value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
    </property>


    <!-- 注意：为了开启hbase的namespace和phoenix的schema的映射，在程序中需要加这个配置文件，另外在linux服务上，也需要在hbase以及phoenix的hbase-site.xml配置文件中，加上以上两个配置，并使用xsync进行同步-->
    <property>
        <name>phoenix.schema.isNamespaceMappingEnabled</name>
        <value>true</value>
    </property>

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

StudentMapper.xml

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.hbase.phoenix.mapper.StudentMapper">
    <select id="queryAll" resultType="com.hbase.phoenix.pojo.Student">
        SELECT * FROM "school"."stu"
    </select>

    <insert id="save">
        UPSERT INTO "school"."stu" VALUES( #{id}, #{name}, #{age} )
    </insert>

    <select id="queryById" resultType="com.hbase.phoenix.pojo.Student">
        SELECT * FROM "school"."stu" WHERE id=#{id}
    </select>

    <delete id="deleteById">
        DELETE FROM "school"."stu" WHERE id=#{id}
    </delete>
</mapper>

PhoeinxApplication

package com.hbase.phoenix;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.hbase.phoenix.mapper")
public class PhoeinxApplication {
    public static void main(String[] args) {
        SpringApplication.run(PhoeinxApplication.class);
    }
}

Student

package com.hbase.phoenix.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private String id;

    private String name;

    private String age;
}

StudentMapper

package com.hbase.phoenix.mapper;

import com.hbase.phoenix.pojo.Student;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;
@Mapper
public interface StudentMapper {
    List<Student> queryAll();

    void save(Student student);

    Student queryById(String id);

    void deleteById(String id);
}

PhoenixTest

package com.hbase.phoenix.test;

import com.hbase.phoenix.mapper.StudentMapper;
import com.hbase.phoenix.pojo.Student;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class PhoenixTest {
    @Autowired
    private StudentMapper studentMapper;

    @Test
    public void nihao(){
        System.out.println("fjaidsfj");
    }

    @Test
    public void queryAll() {
        List<Student> StudentList = studentMapper.queryAll();
        if (StudentList != null) {
            for (Student student : StudentList) {
                System.out.println(student);
            }
        }
    }

    @Test
    public void save() {
        studentMapper.save(new Student("1003", "Dallas", "6666"));
        Student Student = studentMapper.queryById("1003");
        System.out.println(Student);
    }

    @Test
    public void update() {
        studentMapper.save(new Student("1001", "小五", "99999"));
        Student Student = studentMapper.queryById("1001");
        System.out.println(Student);
    }


    @Test
    public void delete() {
        studentMapper.deleteById("1003");
        Student Student = studentMapper.queryById("1001");
        System.out.println(Student);
    }
}

项目结构