准备测试数据
创建数据库、表、加载数据
create table dw_sales(id string,date1 string,channelId string, productId string, regionId string,amount int,price double)ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ stored as textfile;
– 2、渠道表:dim_channel
– channelId 渠道ID
– channelName 渠道名称
create table dim_channel(channelId string, channelName string )ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ stored as textfile;
– 3、产品表:dim_product
create table dim_product(productId string, productName string )ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ stored as textfile;
–4、区域表:dim_region
create table dim_region(regionId string,regionName string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ stored as textfile;
– 导入数据
LOAD DATA LOCAL INPATH ‘/opt/kylindatas/dw_sales_data.txt’ OVERWRITE INTO TABLE dw_sales;
LOAD DATA LOCAL INPATH ‘/opt/kylindatas/dim_channel_data.txt’ OVERWRITE INTO TABLE dim_channel;
LOAD DATA LOCAL INPATH ‘/opt/kylindatas/dim_product_data.txt’ OVERWRITE INTO TABLE dim_product;
LOAD DATA LOCAL INPATH ‘/opt/kylindatas/dim_region_data.txt’ OVERWRITE INTO TABLE dim_region;
hive建表语句
– 1、销售表:dw_sales
– id 唯一标识
– date1 日期
– channelId 渠道 ID
– productId 产品 ID
– regionId 区域 ID
– amount 数量
– price 金额
create table dw_sales(id string,date1 string,channelId string, productId string, regionId string,amount int,price double)ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ stored as textfile;
– 2、渠道表:dim_channel
– channelId 渠道ID
– channelName 渠道名称
create table dim_channel(channelId string, channelName string )ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ stored as textfile;
– 3、产品表:dim_product
create table dim_product(productId string, productName string )ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ stored as textfile;
–4、区域表:dim_region
create table dim_region(regionId string,regionName string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ stored as textfile;
– 导入数据
LOAD DATA LOCAL INPATH ‘/opt/kylindatas/dw_sales_data.txt’ OVERWRITE INTO TABLE dw_sales;
LOAD DATA LOCAL INPATH ‘/opt/kylindatas/dim_channel_data.txt’ OVERWRITE INTO TABLE dim_channel;
LOAD DATA LOCAL INPATH ‘/opt/kylindatas/dim_product_data.txt’ OVERWRITE INTO TABLE dim_product;
LOAD DATA LOCAL INPATH ‘/opt/kylindatas/dim_region_data.txt’ OVERWRITE INTO TABLE dim_region;
kylin实战建表语句
链接: https://pan.baidu.com/s/1iIZu0Kdxaxhdzjqz7ldqQQ 提取码: 8qea
使用kylin对数据进行预计算流程
1、创建项目(Project)
2、创建数据源(DataSource)
指定有哪些数据需要进行数据分析
3、创建模型(Model)
指定具体要对哪个事实表、那些维度进行数据分析
4、创建立方体(Cube)
指定对哪个数据模型执行数据预处理,生成不同维度的数据
5、执行构建、等待构建完成
6、再执行SQL查询,获取结果
从Cube中查询数据
实施:
1、创建项目(Project)
2、创建数据源(DataSource)
3、创建模型(Model)
设置model名称
4、创建立方体(Cube)