CC00014.kylin——|Hadoop&OLAP_Kylin.V14|——|Kylin.v14|Kylin构建Cube|Segment管理|

一、使用JDBC连接操作Kylin

### --- 使用JDBC连接操作Kylin

~~~     要将数据以可视化方式展示出来，需要使用Kylin的JDBC方式连接执行SQL，获取Kylin的执行结果
~~~     使用Kylin的JDBC与JDBC操作MySQL一致
~~~     jdbc url：jdbc:kylin://hadoo02:7070/yanqi_sales_olap       (# yanqi_sales_olap：表示项目名称)
~~~     用户名密码：ADMIN/KYLIN

### --- 需求

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

二、编程实现

### --- 创建一个maven项目：SparkBigData
### --- 在pom.xml下导入驱动依赖

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

### --- 编程实现流程

~~~     创建Connection连接对象
~~~     构建SQL语句
~~~     创建Statement对象，并执行executeQuery
~~~     打印结果

三、编程代码实现

### --- 编程代码实现：写java或者scala程序都可以，跟语言的关系不是很大；使用scala程序：KylinJDBC

package cn.yanqi.kylin

import java.sql.{DriverManager, ResultSet}

object KylinJDBC {
  def main(args: Array[String]): Unit = {
    //1、 创建Connection连接对象
    val url = "jdbc:kylin://hadoop02:7070/yanqi_sales_olap"
    val connection = DriverManager.getConnection(url, "ADMIN", "KYLIN")

    //2、 构建SQL和语句
    val sql =
      """
        |select
        | t1.dt,
        | t2.regionid,
        | t2.regionname,
        | t3.productid,
        | t3.productname,
        | sum(t1.price) as total_money,
        | sum(t1.amount) as total_amount
        |from
        | dw_sales1 t1
        |inner join dim_region t2
        |on t1.regionid = t2.regionid
        |inner join dim_product t3
        |on t1.productid = t3.productid
        |group by
        | t1.dt,
        | t2.regionid,
        | t2.regionname,
        | t3.productid,
        | t3.productname
        |order by
        | t1.dt,
        | t2.regionname,
        | t3.productname
        |""".stripMargin

    // 3、创建Statement对象，并执行executeQuery;获取ResultSet
    val statement = connection.createStatement()
    val resultSet: ResultSet = statement.executeQuery(sql)

    // 4、打印输出结果
    while (resultSet.next()) {
      // 获取时间
      val dt: String = resultSet.getString("dt")
      // 获取区域名称
      val regionname: String = resultSet.getString("regionname")
      // 获取产品名称
      val productname: String = resultSet.getString("productname")
      // 总金额
      val total_money: String = resultSet.getString("total_money")
      // 总数量
      val total_amount: String = resultSet.getString("total_amount")

      //println("dt   regionname   productname   total_money   total_amount   ")
      println(f"$dt   $regionname   $productname%-10s   $total_money%10s    $total_amount%10s")
    }

    connection.close
  }
}

### --- 编译打印

~~~     # 输出参数
D:\JAVA\jdk1.8.0_231\bin\java.exe "-javaagent:D:\IntelliJIDEA\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar=62473:D:\IntelliJIDEA\IntelliJ IDEA 2019.3.3\bin" -Dfile.encoding=UTF-8 -classpath D:\JAVA\jdk1.8.0_231\jre\lib\charsets.jar;D:\JAVA\jdk1.8.0_231\jre\lib\deploy.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\access-bridge-64.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\cldrdata.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\dnsns.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\jaccess.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\jfxrt.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\localedata.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\nashorn.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunec.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunjce_provider.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunmscapi.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunpkcs11.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\zipfs.jar;D:\JAVA\jdk1.8.0_231\jre\lib\javaws.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jce.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jfr.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jfxswt.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jsse.jar;D:\JAVA\jdk1.8.0_231\jre\lib\management-agent.jar;D:\JAVA\jdk1.8.0_231\jre\lib\plugin.jar;D:\JAVA\jdk1.8.0_231\jre\lib\resources.jar;D:\JAVA\jdk1.8.0_231\jre\lib\rt.jar;E:\NO.Z.80000.Hadoop.project\SparkBigData\target\classes;C:\Users\Administrator\.m2\repository\org\scala-lang\scala-library\2.12.10\scala-library-2.12.10.jar;C:\Users\Administrator\.m2\repository\org\apache\spark\spark-core_2.12\2.4.5\spark-core_2.12-2.4.5.jar;C:\Users\Administrator\.m2\repository\com\thoughtworks\paranamer\paranamer\2.8\paranamer-2.8.jar;C:\Users\Administrator\.m2\repository\org\apache\avro\avro\1.8.2\avro-1.8.2.jar;C:\Users\Administrator\.m2\repository\org\codehaus\jackson\jackson-core-asl\1.9.13\jackson-core-asl-1.9.13.jar;C:\Users\Administrator\.m2\repository\org\apache\commons\commons-compress\1.8.1\commons-compress-1.8.1.jar;C:\Users\Administrator\.m