Spring Boot实现jdbc访问impala

1.什么是impala？

Impala组成

1. 客户端：包括JDBC、ODBC、Hue、Impala Shell等，用于执行查询或完成管理任务；
2. Hive Metastore：存储可用于Impala数据的信息，包括可用数据库及其结构。当执行Impala Sql语句进行schema对象的创建、修改及删除，或加载数据到表中等操作时，相关元数据的变化，通过单独的catalog服务自动广播到所有Impala节点；
3. Cloudera Impala（Impalad进程）：运行于数据节点的Impala程序，用于协调和执行查询。每一个Impala的实例可以获取、解析以及协调Impala客户端传来的查询。查询是被分布到各Impala节点间，这些节点作为workers，并行执行查询片段；
4. HDFS、HBase、kudu：数据的实际存储位置。

impala查询执处理过程

1. 用户程序通过JDBC、ODBC、Impala Shell等Impala 客户端发送Sql语句给Impala；
2. 用户程序连接到集群中任意Impalad进程，这一进程作为整个查询的协调器；
3. Impala解析、分析查询，确定哪些任务由集群中哪一Impalad实例执行，并生成最优执行计划；
4. Impalad实例访问对应HDFS、HBase服务，获取数据；
5. 每一个Impalad实例将数据返回给协调器Impalad，由其发送结果给客户端。

2.环境准备

install kudu

install impala

docker run -d --name kudu-impala --add-host  kudu-master-1:{ip} --add-host  kudu-master-2:{ip} --add-host  kudu-master-3:{ip} -p 21000:21000 -p 21050:21050 -p 25000:25000 -p 25010:25010 -p 25020:25020   --memory=4096m apache/kudu:impala-latest impala

run impala-shell

docker exec -it kudu-impala impala-shell

Create a Kudu Table

CREATE TABLE my_first_table
(
id BIGINT,
name STRING,
PRIMARY KEY(id)
)
PARTITION BY HASH PARTITIONS 4
STORED AS KUDU;

DESCRIBE my_first_table;

Insert and Modify Data

-- Insert a row.
INSERT INTO my_first_table VALUES (99, "sarah");
SELECT * FROM my_first_table;

-- Insert multiple rows.
INSERT INTO my_first_table VALUES (1, "john"), (2, "jane"), (3, "jim");
SELECT * FROM my_first_table;

-- Update a row.
UPDATE my_first_table SET name="bob" where id = 3;
SELECT * FROM my_first_table;

-- Use upsert to insert a new row and update another.
UPSERT INTO my_first_table VALUES (3, "bobby"), (4, "grant");
SELECT * FROM my_first_table;

-- Delete a row.
DELETE FROM my_first_table WHERE id = 99;
SELECT * FROM my_first_table;

-- Delete multiple rows.
DELETE FROM my_first_table WHERE id < 3;
SELECT * FROM my_first_table;

install hue

docker run -it -p 8888:8888 gethue/hue:latest

docker cpb72dfc588c76:/usr/share/hue/desktop/conf/z-hue-overrides.ini ./z-hue-overrides.ini

[[database]]
engine=mysql
host=xxx.xxx.xxx.xxx
port=3306
user=xxx
password=xxx
name=database

[impala]
server_host=xxx.xxx.xxx.xxx
server_port=21050

将修改好的文件放回去

docker cp./z-hue-overrides.ini b72dfc588c76:/usr/share/hue/desktop/conf/z-hue-overrides.ini

docker stop ${container_id}
docker start ${container_id}

3.代码工程

 实验目标

实现JDBC访问impala

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">
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.1</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>impala</artifactId>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.cloudera.impala.jdbc</groupId>
            <artifactId>ImpalaJDBC41</artifactId>
            <version>2.6.4.1005</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/src/lib/ImpalaJDBC41.jar</systemPath>
        </dependency>
    </dependencies>

</project>

测试类

DataSource ds =new com.cloudera.impala.jdbc41.DataSource();
ds.setURL(<CONNECTION_URL>);
connection = ds.getConnection();

String IMPALA_URL="jdbc:impala://<impala_server>:21050/<database>"
Connection connection = DriverManager.getConnection(IMPALA_URL);

package com.et.impala;

import com.cloudera.impala.jdbc41.DataSource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class ImpalaTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.cloudera.impala.jdbc41.Driver");
            Properties p = new Properties();
            p.setProperty( "user", "System" );
            p.setProperty( "password", "Hyd20240531" );
            p.setProperty( "latency", "0" );
            p.setProperty( "communicationtimeout", "0" );
            String currentschema = "default";
            String url = "jdbc:impala://172.24.4.35:21050/"+currentschema;
            //DataSource
            DataSource ds = new com.cloudera.impala.jdbc41.DataSource();
            ds.setURL(url);
            connection = ds.getConnection();
            //don't use DataSource
            //connection = DriverManager.getConnection( url, p );

            System.out.println("Schema: " + connection.getSchema());

            String sqlQuery = "SELECT * FROM my_first_table";

            statement = connection.createStatement();

            resultSet = statement.executeQuery(sqlQuery);

            while (resultSet.next()) {
                int column1 = resultSet.getInt("id");
                String column2 = resultSet.getString("name");
                System.out.println("id: " + column1 + ", name: " + column2);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

以上只是一些关键代码，所有代码请参见下面代码仓库

代码仓库

• https://github.com/Harries/springboot-demo(impala）

4.测试

Schema: 
id: 99, name: sarah
id: 1, name: john
id: 2, name: jane
id: 3, name: bobby
id: 4, name: grant

5.引用

• https://tech-spaghetti.com/2019/01/27/jdbc-connection-to-impala/
• https://www.cloudera.com/downloads/connectors/impala/jdbc/2-6-4.html