Doris简单使用
大约 2 分钟
提示
Doris作为比较火的分析数据库,学习一下
1. Doris搭建
version: '3'
services:
docker-fe:
image: "apache/doris:1.2.1-fe-x86_64"
container_name: "doris-fe"
hostname: "fe"
environment:
- FE_SERVERS=fe1:172.20.80.2:9010
- FE_ID=1
ports:
- 8030:8030
- 9030:9030
volumes:
- /data/doris/fe/doris-meta:/opt/apache-doris/fe/doris-meta
- /data/doris/fe/conf:/opt/apache-doris/fe/conf
- /data/doris/fe/log:/opt/apache-doris/fe/log
networks:
doris_net:
ipv4_address: 172.20.80.2
docker-be:
image: "apache/doris:1.2.1-be-x86_64"
container_name: "doris-be"
hostname: "be"
depends_on:
- docker-fe
environment:
- FE_SERVERS=fe1:172.20.80.2:9010
- BE_ADDR=172.20.80.3:9050
ports:
- 8040:8040
volumes:
- /data/doris/be/storage:/opt/apache-doris/be/storage
- /data/doris/be/conf:/opt/apache-doris/be/conf
- /data/doris/be/script:/docker-entrypoint-initdb.d
- /data/doris/be/log:/opt/apache-doris/be/log
networks:
doris_net:
ipv4_address: 172.20.80.3
networks:
doris_net:
ipam:
config:
- subnet: 172.20.80.0/16
2. 使用
访问 http://172.20.80.2:8030/ 账号root. 密码为空
2.1 创建mysql外部资源
CREATE CATALOG `orders`
PROPERTIES (
"type"="jdbc",
"user"="root",
"password"="123456",
"jdbc_url"="jdbc:mysql://xx,xx,xx,xx:3388/?useUnicode=true&characterEncoding=utf-8&trustServerCertificate=true",
"driver_url"="http://xx,xx,xx,xx:11081/repository/maven-central/mysql/mysql-connector-java/8.0.30/mysql-connector-java-8.0.30.jar",
"driver_class"="com.mysql.cj.jdbc.Driver"
);
2.2 访问外部表
select * from orders.m_order.o_order_info limit 10
2.3 创建聚合表
CREATE TABLE IF NOT EXISTS tenant_order_agg
(
tenant_id bigint not null comment '店铺ID',
pay_date date not null comment '订单支付日期',
pay_hour int not null comment '订单支付小时',
total_amount decimal(18, 4) SUM DEFAULT "0" comment '总价金额,订单商品的原价*数量',
subtotal_amount decimal(18, 4) SUM DEFAULT "0" comment '小计总金额,改价后的小计合计',
offer_amount decimal(18, 4) SUM DEFAULT "0" comment '优惠金额,所有优惠金额综合',
receive_amount decimal(18, 4) SUM DEFAULT "0" comment '收到金额,实际支付金额',
payment_amount decimal(18, 4) SUM DEFAULT "0" comment '应收金额',
express_fee decimal(18, 4) SUM DEFAULT "0" comment '运费金额',
pack_fee decimal(18, 4) SUM DEFAULT "0" comment '订单维度的打包袋金额,单位是元',
refund_amount decimal(18, 4) SUM DEFAULT "0" comment '已退款金额',
spot_count int SUM DEFAULT "0" comment '现场购买',
applet_count int SUM DEFAULT "0" comment '小程序下单',
mt_count int SUM DEFAULT "0" comment '美团购买',
purchase_count int SUM DEFAULT "0" comment '订单消费',
foster_count int SUM DEFAULT "0" comment '寄养消费',
card_count int SUM DEFAULT "0" comment '卡消费',
mt_ol_count int SUM DEFAULT "0" comment '美团消费',
applet_ol_count int SUM DEFAULT "0" comment '小程序消费'
)
AGGREGATE KEY(`tenant_id`, `pay_date`, `pay_hour`)
DISTRIBUTED BY HASH(`tenant_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
2.4 插入数据
insert into tenant_order_agg
select tenant_id,
to_date(pay_time) as pay_date,
extract(hour from pay_time) as pay_hour,
total_amount,
subtotal_amount,
offer_amount,
receive_amount,
payment_amount,
express_fee,
pack_fee,
refund_amount,
if(source = 'SPOT', 1, 0) as spot_count,
if(source = 'APPLET', 1, 0) as applet_count,
if(source = 'MT_ONLINE', 1, 0) as mt_count,
if(business_type = 'PURCHASE', 1, 0) as mt_count,
if(business_type = 'FOSTER', 1, 0) as mt_count,
if(business_type = 'CARD', 1, 0) as mt_count,
if(business_type = 'MT_OL_ORDER', 1, 0) as mt_count,
if(business_type = 'APPLE_ORDER', 1, 0) as mt_count
from pets_dev.pets_order.o_order_info;
2.5 外部资源
CREATE EXTERNAL RESOURCE `customer_dev_odbc`
PROPERTIES (
"type" = "odbc_catalog",
"host" = "xx.xx.xx.xx",
"port" = "3388",
"user" = "root",
"password" = "123456",
"database" = "m_customer",
"odbc_type" = "mysql",
"driver" = "mysql"
);