13.2 kylin的初步使用对比hive,impala

1.执行官方的demo 

导入数据

[root@mini2 bin]# ./sample.sh 
Retrieving hadoop conf dir...
Loading sample data into HDFS tmp path: /tmp/kylin/sample_cube/data
Going to create sample tables in hive to database DEFAULT by cli
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.1.1-1.cdh6.1.1.p0.875250/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.1.1-1.cdh6.1.1.p0.875250/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]

Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-6.1.1-1.cdh6.1.1.p0.875250/jars/hive-common-2.1.1-cdh6.1.1.jar!/hive-log4j2.properties Async: false






2019-08-02 13:33:21,174 DEBUG [pool-7-thread-1] persistence.ResourceParallelCopier:313 : Copying group /*
Progress: 100.0%, 18 resource, 0 error; copied 0.0 MB in 0.0 min, 324.5 KB/s -- Slow network or storage?
2019-08-02 13:33:21,279 INFO  [main] persistence.ResourceParallelCopier:92 : Done copy / from /opt/app/apache-kylin-2.6.3-bin-cdh60/sample_cube/metadata to kylin_metadata@hbase
Metadata restored from /opt/app/apache-kylin-2.6.3-bin-cdh60/sample_cube/metadata
2019-08-02 13:33:21,286 INFO  [close-hbase-conn] hbase.HBaseConnection:137 : Closing HBase connections...
2019-08-02 13:33:21,287 INFO  [close-hbase-conn] client.ConnectionImplementation:1779 : Closing master protocol: MasterService
2019-08-02 13:33:21,288 INFO  [close-hbase-conn] zookeeper.ReadOnlyZKClient:350 : Close zookeeper connection 0x75ed9710 to master:2181,mini2:2181
Sample cube is created successfully in project 'learn_kylin'.
Restart Kylin Server or click Web UI => System Tab => Reload Metadata to take effect

 

重新加载元数据

 

 

 

选择 learn_kylin

构建Cube

 

选择 Model，kylin_sales_cube,选择build

 

选择时间段数据

 

状态100%

(选择时间太长停了一个)

build成功后，回到Insight界面，此时已经成功构建出5张表了。

执行 select count(1) from kylin_sales 

执行2次查询

2.26s

0.01s

 

---

 

执行复杂的语句

1.29s

0.63s

0.23s

 

select sum(KYLIN_SALES.PRICE)
as price_sum,KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME,KYLIN_CATEGORY_GROUPINGS.CATEG_LVL2_NAME
from KYLIN_SALES inner join KYLIN_CATEGORY_GROUPINGS
on KYLIN_SALES.LEAF_CATEG_ID = KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID and
KYLIN_SALES.LSTG_SITE_ID = KYLIN_CATEGORY_GROUPINGS.SITE_ID
group by KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME,KYLIN_CATEGORY_GROUPINGS.CATEG_LVL2_NAME
order by KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME asc,KYLIN_CATEGORY_GROUPINGS.CATEG_LVL2_NAME desc
 

 

 

选择不同纬度可以生成图

 

 

 

在hive中使用

时间(..)

 

 

其数据以csv格式保存在hive中

 

 

在impala中使用

select sum(KYLIN_SALES.PRICE)
as price_sum,KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME,KYLIN_CATEGORY_GROUPINGS.CATEG_LVL2_NAME
from KYLIN_SALES inner join KYLIN_CATEGORY_GROUPINGS
on KYLIN_SALES.LEAF_CATEG_ID = KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID and
KYLIN_SALES.LSTG_SITE_ID = KYLIN_CATEGORY_GROUPINGS.SITE_ID
group by KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME,KYLIN_CATEGORY_GROUPINGS.CATEG_LVL2_NAME
order by KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME asc,KYLIN_CATEGORY_GROUPINGS.CATEG_LVL2_NAME desc

Fetched 104 row(s) in 5.70s

Fetched 104 row(s) in 0.69s

 

 

 

 

 

 

 

 

 

样例 Cube 快速入门

Kylin 提供了一个创建样例 Cube 脚本；脚本会创建五个样例 hive 表:

1. 运行 ${KYLIN_HOME}/bin/sample.sh ；重启 kylin 服务器刷新缓存;
2. 用默认的用户名和密码 ADMIN/KYLIN 登陆 Kylin 网站，选择 project 下拉框（左上角）中的 “learn_kylin” 工程;
3. 选择名为 “kylin_sales_cube” 的样例 cube，点击 “Actions” -> “Build”，选择一个在 2014-01-01 之后的日期（覆盖所有的 10000 样例记录);
4. 点击 “Monitor” 标签，查看 build 进度直至 100%;
5. 点击 “Insight” 标签，执行 SQLs，例如:
   select part_dt，sum(price) as total_selled，count(distinct seller_id) as sellers from kylin_sales group by part_dt order by part_dt
6. 您可以验证查询结果且与 hive 的响应时间进行比较;