一,访问kylin数据库表
访问数据表有两种方式,一种是通过kylin标准JDBC驱动,一种是通过kylin API接口。下面分别予以介绍。
A,标准JDBC驱动方式
首先需要在项目中引入kylin-jdbc-2.1.0.jar(该文件在apache kylin安装包lib目录下)
使用jdbc驱动并返回结果集示例代码如下:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
//实例化驱动类
Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
Properties info = new Properties();
info.put("user", "ADMIN");//认证登录用户名
info.put("password", "KYLIN");//认证登录密码
Connection conn = driver.connect("jdbc:kylin://192.168.46.15:7070/Test_P", info);
PreparedStatement state = conn.prepareStatement("select * from LOGDAY where AREACODE='440488'");
// state.setInt(1, 440488); 不要使用这种方式来赋值参数、
最好使用字符串拼装SQL语句
// state.setNString(1, "440488");
ResultSet resultSet = state.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getString(1));
System.out.println(resultSet.getString(2));
System.out.println(resultSet.getString("ROUTETYPE"));
}
resultSet.close();
state.close();
B,使用API接口执行查询
其实JDBC方式的底层也是调用kylin的API查询接口来获取数据,只不过将接口通过标准jdbc进行了一层封装。
接口名称:
POST /kylin/api/query
接口Post的json数据格式:
{
"sql":"select * from TEST_KYLIN_FACT",--标准SQL语句,必须
"offset":0,--可选,偏移量,用来分页
"limit":50000,--可选,返回最大记录,用来分页
"acceptPartial":false,--接受部分结果,默认false,可选
"project":"DEFAULT",--项目名称,默认DEFAULT,可选
}
返回结果示例
{
"columnMetas"
:[
{
"isNullable"
:1,
"displaySize"
:0,
"label"
:
"CAL_DT",
"name"
:
"CAL_DT",
"schemaName"
:null,
"catelogName"
:null,
"tableName"
:null,
"precision"
:0,
"scale"
:0,
"columnType"
:91,
"columnTypeName"
:
"DATE",
"readOnly"
:true,
"writable"
:false,
"caseSensitive"
:true,
"searchable"
:false,
"currency"
:false,
"signed"
:true,
"autoIncrement"
:false,
"definitelyWritable"
:false
}
,
{
"isNullable"
:1,
"displaySize"
:10,
"label"
:
"LEAF_CATEG_ID",
"name"
:
"LEAF_CATEG_ID",
"schemaName"
:null,
"catelogName"
:null,
"tableName"
:null,
"precision"
:10,
"scale"
:0,
"columnType"
:4,
"columnTypeName"
:
"INTEGER",
"readOnly"
:true,
"writable"
:false,
"caseSensitive"
:true,
"searchable"
:false,
"currency"
:false,
"signed"
:true,
"autoIncrement"
:false,
"definitelyWritable"
:false
}
]
,
"results"
:[
[
"2013-08-07"
,
"32996"
,
"15"
,
"15"
,
"Auction"
,
"10000000"
,
"49.048952730908745"
,
"49.048952730908745"
,
"49.048952730908745"
,
"1"
]
,
[
"2013-08-07"
,
"43398"
,
"0"
,
"14"
,
"ABIN"
,
"10000633"
,
"85.78317064220418"
,
"85.78317064220418"
,
"85.78317064220418"
,
"1"
]
]
,
"cube"
:
"test_kylin_cube_with_slr_desc",
"affectedRowCount"
:0,
"isException"
:false,
"exceptionMessage"
:null,
"duration"
:3451,
"partial"
:false
}
建议使用jdbc方式获取数据,使用API方式会比较繁琐。
二,用RestAPI构建Cubes
接口URL:PUT /kylin/api/cubes/{cubeName}/build
其中{cubeName}路径变量为cube的名字
{
"startTime":'1423526400000',--
开始时间时间戳
"endTime":'1423526400', --
结束时间时间戳
"buildType":"BUILD" --
构建类型(
‘BUILD’, ‘MERGE’, ‘REFRESH’)
}
使用curl命令执行构建示例:
curl -X PUT -H "Authorization: Basic ADMIN:KYLIN" -H 'Content-Type: application/json' -d '{"startTime":'1423526400000', "endTime":'1423526400', "buildType":"BUILD"}' http://<host>:<port>/kylin/api/cubes/{cubeName}/build
构建过程需要进行基础认证。使用用户名密码认证即可
详见下面链接:
http://kylin.apache.org/docs21/howto/howto_use_restapi.html
已知问题汇总
1,登录失败
该问题一般在长时间未登录或者操作kylin时出现,解决方法:重启kylin服务(升级到2.1.0版本后问题解决)
2,cube构建失败
请查看mapreduce的日志,发现错误原因,多数错误为hbase-site.xml和hive-xml.xml配置不当引起
另外,如果kafka client版本不正确,会造成kafka读取数据失败
-
- Kylin定时任务设置
[root@node3 opt]#
[root@node3 opt]#
[root@node3 opt]#
[root@node3 opt]# vim /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/
# For details see man 4 crontabs
# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed
30 00 * * * root /opt/kylin/bin/kylin-BaseStat-task.sh
30 01 * * * root /opt/kylin/bin/kylin-Customer-task.sh
30 02 * * * root /opt/kylin/bin/kylin-LogStat-task.sh
30 03 * * * root /opt/kylin/bin/kylin-ViolationCard-task.sh
[root@node3 opt]#