使用JDBC连接操作Kylin

使用JDBC连接操作Kylin

• 使用JDBC链接kylin直接使用。不需要关注kylin中model的名称cube的名称，以及两者之间的关系

需求
通过JDBC方式，查询按照日期、区域、产品维度统计订单总额/总数量结果
开发步骤

• 导入驱动依赖

<dependencies><!-- Kylin --><dependency><groupId>org.apache.kylin</groupId><artifactId>kylin-jdbc</artifactId><version>2.6.3</version></dependency><dependency><groupId>com.google.guava</groupId><artifactId>guava</artifactId><version>27.1-jre</version></dependency>
</dependencies>

编写Java代码

• 1、加载驱动
• 2、创建Connection连接对象
• 3、构建SQL语句
• 4、创建Statement对象，并执行executeQuery
• 5、打印结果

参考代码

public static void main(String[] args) throws Exception {// 1、加载驱动Class.forName("org.apache.kylin.jdbc.Driver");// 2、创建Connection连接对象// 连接字符串：jdbc:kylin://ip地址:7070/项目名称（project）Connection connection =DriverManager.getConnection("jdbc:kylin://node1:7070/itcast_dw", "ADMIN","KYLIN");
​// 3、创建Statement对象，并执行executeQuery，获取ResultSetStatement statement = connection.createStatement();
​// 构建SQL和语句String sql = "select\n" +" t1.date1,\n" +" t2.regionname,\n" +" productname,\n" +" sum(t1.price) as total_money,\n" +" sum(t1.amount) as total_amount\n" +"from\n" +" dw_sales t1\n" +"inner join dim_region t2\n" +"on t1.regionid = t2.regionid\n" +"inner join dim_product t3\n" +"on t1.productid = t3.productid\n" +"group by\n" +" t1.date1,\n" +" t2.regionid,\n" +" t2.regionname,\n" +" t3.productid,\n" +" t3.productname";//  String sql = "select date1, sum(price) as total_money, sum(amount) as total_amount from dw_sales group by date1,channelid";ResultSet resultSet = statement.executeQuery(sql);
​// 4、打印ResultSetwhile(resultSet.next()) {// 4.1 获取时间String date1 = resultSet.getString("date1");// 4.2 获取区域名称String regionname = resultSet.getString("regionname");// 4.3 获取产品名称String productname = resultSet.getString("productname");// 4.4 总金额String total_money = resultSet.getString("total_money");// 4.5 总数量String total_amount = resultSet.getString("total_amount");​//输出结果System.out.println(date1 + " " + regionname + " " + productname + " " + total_money + " " + total_amount);}connection.close();
}

• Kylin的Web页面查询的结果
  
• IDEA，运行任务，控制台打印的结果