[大数据]Kylin快速入门

下载HBase 进行配置

vim hbase-env.sh

export JAVA_HOME=/opt/jdk1.8.0_192
export HBASE_MANAGES_ZK=false

vim hbase-site.xml
<configuration>
<property>
    <name>hbase.rootdir</name>
    <value>hdfs://hdp0311/hbase</value>
</property>
<property>
      <name>hbase.cluster.distributed</name>
      <value>true</value>
</property>
<property>
    <name>hbase.zookeeper.quorum</name>
    <value>hdp1,hdp2,hdp3</value>
</property>
</configuration>

vim regionservers 
hdp1
hdp2
hdp3

配置环境变量

export HBASE_HOME=/opt/hbase-1.3.3

在Master节点启动

start-hbase.sh

在备用master上输入

hbase-daemon.sh start master启动备用服务器

登陆http://hdp1:16010/ 查看hbase

hbase shell 进入hbase命令行
输入help获取命令列表

下载Kylin

环境变量配置

export HBASE_HOME=/opt/hbase-1.3.3
export HIVE_HOME=/opt/apache-hive-1.2.1-bin
export KYLIN_HOME=/opt/apache-kylin-3.1.2-bin-hbase1x

启动hdfs，yarn，hbase，hive metastore，jobhistoryserver
mr-jobhistory-daemon.sh start historyserver

脚本来进行环境检测

/opt/apache-kylin-3.1.2-bin-hbase1x/bin/check-env.sh 

脚本来启动 Kylin

/opt/apache-kylin-3.1.2-bin-hbase1x/bin/kylin.sh start 

http://kylin.apache.org/cn/docs30/install/index.html

测试下载成功后，开始kylin的使用

测试数据如下：

• class_info.txt

1,1812B
2,1812A      

• province_info.txt

1,山西
2,河南
3,河北

• score_info.txt

1,1,66
1,2,55
1,3,77
2,1,46
2,2,90
2,3,78
3,1,29
3,2,87
3,3,99

• stu_info.txt

1,yinxin,man,23,1,1
2,lizhen,man,24,2,3
3,lyy,woman,21,3,2

• subject_info.txt

1,java
2,c++
3,python

hive建表语句如下：

create database kylindemo;
use kylindemo;

create table t_class_info(id int,class_name string)row format delimited fields terminated by ',';
create table t_province_info(id int, province_name string)row format delimited fields terminated by ',';
create table t_score_info(stu_id int,subject_id int,score int)row format delimited fields terminated by ',';
create table t_stu_info(id int,name string,sex string,age int,class_id int, province_id int) row format delimited fields terminated by ',';
create table t_subject_info(id int, subject_name string)row format delimited fields terminated by ',';

导入数据语句如下：

load data local inpath '/root/kylindemo/class_info.txt' into table t_class_info;
load data local inpath '/root/kylindemo/province_info.txt' into table t_province_info;
load data local inpath '/root/kylindemo/score_info.txt' into table t_score_info;
load data local inpath '/root/kylindemo/stu_info.txt' into table t_stu_info;
load data local inpath '/root/kylindemo/subject_info.txt' into table t_subject_info;

进入hdp1:7070页面
创建kylin项目



























sql语句如下：

select
    tti.sex,tpi.province_name,tji.subject_name,tci.class_name ,sum(tsi.score)
from t_score_info tsi
         left join t_stu_info tti on tti.id = tsi.stu_id
         left join t_province_info tpi on tti.province_id = tpi.id
         left join t_subject_info tji on tji.id = tsi.subject_id
         left join t_class_info tci on tci.id = tti.class_id
group by tti.sex,tpi.province_name,tji.subject_name,tci.class_name;

生成请求头

python -c "import base64; print base64.standard_b64encode('ADMIN:KYLIN')"

QURNSU46S1lMSU4=

curl -c /path/to/cookiefile.txt -X POST -H "Authorization: Basic QURNSU46S1lMSU4=" -H 'Content-Type: application/json' http://hdp1:7070/kylin/api/user/authentication

如果登录成功，JSESSIONID将保存到cookie文件中；在后续的http请求中，附上cookie

{“userDetails”:{“username”:“ADMIN”,“password”:"$2a$10$o3ktIWsGYxXNuUWQiYlZXOW5hWcqyNAFQsSSCSEWoC/BRVMAUjL32",“authorities”:[{“authority”:“ROLE_ADMIN”},{“authority”:“ROLE_ANALYST”},{“authority”:“ROLE_MODELER”},{“authority”:“ALL_USERS”}],“disabled”:false,“defaultPassword”:false,“locked”:false,“lockedTime”:0,“wrongTime”:0,“uuid”:“889fd9a1-8b6f-3fe7-2a5c-d7cdf9f958ea”,“last_modified”:1629720431587,“version”:“3.0.0.20500”}}

进行查询 （ -o为输出到文件）

curl -X POST -H "Authorization: Basic QURNSU46S1lMSU4=" -H "Content-Type: application/json" -d '{ "sql":"select tti.sex,tpi.province_name,tji.subject_name,tci.class_name ,sum(tsi.score) from t_score_info tsi left join t_stu_info tti on tti.id = tsi.stu_id left join t_province_info tpi on tti.province_id = tpi.id left join t_subject_info tji on tji.id = tsi.subject_id left join t_class_info tci on tci.id = tti.class_id group by tti.sex,tpi.province_name,tji.subject_name,tci.class_name;", "project":"kylindemo" }' http://hdp1:7070/kylin/api/query   -o a.txt

{“columnMetas”:[{“isNullable”:1,“displaySize”:256,“label”:“SEX”,“name”:“SEX”,“schemaName”:“KYLINDEMO”,“catelogName”:null,“tableName”:“T_STU_INFO”,“precision”:256,“scale”:0,“columnType”:12,“columnTypeName”:“VARCHAR”,“autoIncrement”:false,“caseSensitive”:true,“searchable”:false,“currency”:false,“signed”:true,“writable”:false,“definitelyWritable”:false,“readOnly”:true},{“isNullable”:1,“displaySize”:256,“label”:“PROVINCE_NAME”,“name”:“PROVINCE_NAME”,“schemaName”:“KYLINDEMO”,“catelogName”:null,“tableName”:“T_PROVINCE_INFO”,“precision”:256,“scale”:0,“columnType”:12,“columnTypeName”:“VARCHAR”,“autoIncrement”:false,“caseSensitive”:true,“searchable”:false,“currency”:false,“signed”:true,“writable”:false,“definitelyWritable”:false,“readOnly”:true},{“isNullable”:1,“displaySize”:256,“label”:“SUBJECT_NAME”,“name”:“SUBJECT_NAME”,“schemaName”:“KYLINDEMO”,“catelogName”:null,“tableName”:“T_SUBJECT_INFO”,“precision”:256,“scale”:0,“columnType”:12,“columnTypeName”:“VARCHAR”,“autoIncrement”:false,“caseSensitive”:true,“searchable”:false,“currency”:false,“signed”:true,“writable”:false,“definitelyWritable”:false,“readOnly”:true},{“isNullable”:1,“displaySize”:256,“label”:“CLASS_NAME”,“name”:“CLASS_NAME”,“schemaName”:“KYLINDEMO”,“catelogName”:null,“tableName”:“T_CLASS_INFO”,“precision”:256,“scale”:0,“columnType”:12,“columnTypeName”:“VARCHAR”,“autoIncrement”:false,“caseSensitive”:true,“searchable”:false,“currency”:false,“signed”:true,“writable”:false,“definitelyWritable”:false,“readOnly”:true},{“isNullable”:1,“displaySize”:19,“label”:“EXPR$4”,“name”:“EXPR$4”,“schemaName”:null,“catelogName”:null,“tableName”:null,“precision”:19,“scale”:0,“columnType”:-5,“columnTypeName”:“BIGINT”,“autoIncrement”:false,“caseSensitive”:true,“searchable”:false,“currency”:false,“signed”:true,“writable”:false,“definitelyWritable”:false,“readOnly”:true}],“results”:[[“man”,“河北”,“c++”,“1812A”,“90”],[“man”,“河北”,“python”,“1812A”,“78”],[“man”,“山西”,“python”,“1812B”,“77”],[“woman”,“河南”,“c++”,null,“87”],[“man”,“山西”,“c++”,“1812B”,“55”],[“woman”,“河南”,“python”,null,“99”],[“man”,“河北”,“java”,“1812A”,“46”],[“man”,“山西”,“java”,“1812B”,“66”],[“woman”,“河南”,“java”,null,“29”]],“cube”:“CUBE[name=score_cube]”,“affectedRowCount”:0,“isException”:false,“exceptionMessage”:null,“duration”:5950,“totalScanCount”:9,“totalScanBytes”:396,“hitExceptionCache”:false,“storageCacheUsed”:false,“traceUrl”:null,“pushDown”:false,“partial”:false}

Kylin JDBC

<!-- https://mvnrepository.com/artifact/org.apache.kylin/kylin-jdbc -->
        <dependency>
            <groupId>org.apache.kylin</groupId>
            <artifactId>kylin-jdbc</artifactId>
            <version>3.1.2</version>
        </dependency>

package com.guantengyun.kylin;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;

/**
 * Kylin JDBC
 */
public class KylinClient {
    private static Connection conn = null;
    private static Statement stat = null;
    private static ResultSet rs = null;

    @Before
    public void init() throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.kylin.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:kylin://hdp1:7070/kylindemo", "ADMIN", "KYLIN");
        stat = conn.createStatement();
    }

    @Test
    public void query_test() throws SQLException {
        rs = stat.executeQuery("select tti.sex,tpi.province_name,tji.subject_name,tci.class_name ,sum(tsi.score) from t_score_info tsi left join t_stu_info tti on tti.id = tsi.stu_id left join t_province_info tpi on tti.province_id = tpi.id left join t_subject_info tji on tji.id = tsi.subject_id left join t_class_info tci on tci.id = tti.class_id group by tti.sex,tpi.province_name,tji.subject_name,tci.class_name");
        while (rs.next()) {
            String sex = rs.getString(1);
            String city = rs.getString(2);
            String language = rs.getString(3);
            String clazz = rs.getString(4);
            long score = rs.getLong(5);
            System.out.println(sex + " " + city + " " + language + " " + clazz + " " + score);
        }
        rs.close();
    }

    @After
    public void distinct() throws SQLException {
        if (rs != null)
            rs.close();
        if (stat != null)
            stat.close();
        if (conn != null)
            conn.close();
    }
}

man 河北 c++ 1812A 90
man 河北 python 1812A 78
man 山西 python 1812B 77
woman 河南 c++ null 87
man 山西 c++ 1812B 55
woman 河南 python null 99
man 河北 java 1812A 46
man 山西 java 1812B 66
woman 河南 java null 29