下载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 10 10o3ktIWsGYxXNuUWQiYlZXOW5hWcqyNAFQsSSCSEWoC/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