Kylin BI工具集成(JDBC+Zepplin)

不念不忘少年蓝@ 2023-03-06 05:50 20阅读 0赞

### 目录 ###

*   *  Kylin BI工具集成
     *   *   *  JDBC
             *  Zepplin

## Kylin BI工具集成 ##

可以与Kylin结合使用的可视化工具很多，例如：  
ODBC：与Tableau、Excel、PowerBI等工具集成  
**`JDBC`**：与Saiku、BIRT等Java工具集成  
RestAPI：与JavaScript、Web网页集成  
Kylin开发团队还贡献了`Zepplin的插件`，也可以使用Zepplin来访问Kylin服务。

#### JDBC ####

1）新建项目并导入依赖

<dependencies>
            <dependency>
                <groupId>org.apache.kylin</groupId>
                <artifactId>kylin-jdbc</artifactId>
                <version>2.5.1</version>
            </dependency>
        </dependencies>

2）编码

import java.sql.*;
    
    public class TestKylin { 
    
        public static void main(String[] args) throws Exception { 
    
            //Kylin_JDBC 驱动
            String KYLIN_DRIVER = "org.apache.kylin.jdbc.Driver";
    
            //Kylin_URL
            String KYLIN_URL = "jdbc:kylin://hadoop12:7070/gmall";
    
            //Kylin的用户名
            String KYLIN_USER = "ADMIN";
    
            //Kylin的密码
            String KYLIN_PASSWD = "KYLIN";
    
            //添加驱动信息
            Class.forName(KYLIN_DRIVER);
    
            //获取连接
            Connection connection = DriverManager.getConnection(KYLIN_URL, KYLIN_USER, KYLIN_PASSWD);
    
            //预编译SQL
            PreparedStatement ps = connection.prepareStatement("select l.province_name,sum(payment_amount) from " +
                    "dwd_fact_payment_info p left join\n" +
                    "DWD_DIM_BASE_PROVINCE l on p.province_id=l.id group by l.province_name;");
    
            //执行查询
            ResultSet resultSet = ps.executeQuery();
    
            //遍历打印
            while (resultSet.next()) { 
                System.out.println(resultSet.getObject(1)+":"+resultSet.getObject(2));
            }
        }
    }

3）结果展示  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70]

#### Zepplin ####

1）Zepplin安装与启动  
（1）将zeppelin-0.8.0-bin-all.tgz上传至Linux  
（2）解压zeppelin-0.8.0-bin-all.tgz之/export/servers

tar -zxvf zeppelin-0.8.0-bin-all.tgz -C /export/servers/

（3）修改名称

mv zeppelin-0.8.0-bin-all/ zeppelin

（4）启动

bin/zeppelin-daemon.sh start

可登录网页查看，web默认端口号为8080  
http://hadoop102:8080  
**`如果Zepplin网页打不开，也不报错 请设置`**  
打开conf/zeppelin-site.xml.template修改  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 1]  
配置Zepplin支持Kylin  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 6]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 7]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 8]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70]: /images/20230208/a4afe2bc3fbc4c78b7bff202609a674c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 1]: /images/20230208/22ea6efd132f45dc98dda967743ce737.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 2]: /images/20230208/8f10e953edcb4d73ba2621ac032d17d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 3]: /images/20230208/de3f3f66337f4dc194e7071c67d50879.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 4]: /images/20230208/977eb389f9e3449087ab6ba211e85a55.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 5]: /images/20230208/4e1b600cd6cb467494e2e2089cb97fae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 6]: /images/20230208/a42f5e6e744942e39b0ce36cf43c033b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 7]: /images/20230208/a25ef1fc673c415bb3feb7279a905fb6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 8]: /images/20230208/f0441b12dc7a4d45a295ab79d03f73bb.png