大数据-159 Apache Kylin 构建Cube 准备和测试数据(二)

# 设置参数
output_file = 'dim_product_data.txt'

# 定义产品ID和产品名称
products = [
    ('P001', 'Smartphone'),
    ('P002', 'Laptop'),
    ('P003', 'Tablet'),
    ('P004', 'Smartwatch'),
    ('P005', 'Camera'),
    ('P006', 'Headphones'),
    ('P007', 'Monitor'),
    ('P008', 'Keyboard'),
    ('P009', 'Mouse'),
    ('P010', 'Printer')
]

# 生成数据
with open(output_file, 'w') as f:
    for product_id, product_name in products:
        line = f"{product_id},{product_name}\n"
        f.write(line)

print(f"Product data has been written to {output_file}")

# 设置参数
output_file = 'dim_region_data.txt'

# 定义区域ID和区域名称
regions = [
    ('R001', 'North America'),
    ('R002', 'Europe'),
    ('R003', 'Asia'),
    ('R004', 'South America'),
    ('R005', 'Africa'),
    ('R006', 'Australia'),
    ('R007', 'Antarctica')
]

# 生成数据
with open(output_file, 'w') as f:
    for region_id, region_name in regions:
        line = f"{region_id},{region_name}\n"
        f.write(line)

print(f"Region data has been written to {output_file}")

-- 创建订单数据库、表结构
create database if not exists `wzk_kylin`;
-- 1、销售表：dw_sales
-- id 唯一标识
-- date1 日期
-- channelId 渠道ID
-- productId 产品ID
-- regionId 区域ID
-- amount 数量
-- price 金额
create table wzk_kylin.dw_sales(
  id string,
  date1 string,
  channelId string,
  productId string,
  regionId string,
  amount int,
  price double
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 2、渠道表：dim_channel
-- channelId 渠道ID
-- channelName 渠道名称
create table wzk_kylin.dim_channel(
  channelId string,
  channelName string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 3、产品表：dim_product
create table wzk_kylin.dim_product(
  productId string,
  productName string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
--4、区域表：dim_region
create table wzk_kylin.dim_region(
  regionId string,
  regionName string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

-- 导入数据
LOAD DATA LOCAL INPATH '/opt/wzk/kylin_test/dw_sales_data.txt'
OVERWRITE INTO TABLE wzk_kylin.dw_sales;
LOAD DATA LOCAL INPATH '/opt/wzk/kylin_test/dim_channel_data.txt'
OVERWRITE INTO TABLE wzk_kylin.dim_channel;
LOAD DATA LOCAL INPATH '/opt/wzk/kylin_test/dim_product_data.txt'
OVERWRITE INTO TABLE wzk_kylin.dim_product;
LOAD DATA LOCAL INPATH '/opt/wzk/kylin_test/dim_region_data.txt'
OVERWRITE INTO TABLE wzk_kylin.dim_region;