Apache Doris常用命令

一.配置

Ⅰ).BE

vi be.conf

# INFO,WARNING,ERROR,FATAL
sys_log_level=INFO

# ports for admin,web,heartbeat service
be_port=9060
be_rpc_port=9070
webserver_port=8040
heartbeat_service_port=9050
brpc_port=8060

# Choose one if there are more than one ip except loopback address
# 24为网关
priority_networks=hostname/24

# storage_root_path=/home/disk1/palo.HDD,50;/home/disk2/palo.SSD,1;/home/disk2/palo
# 目录需手动创建
storage_root_path=/home/doris/be/palo           

Ⅱ).FE

vi fe.conf

# INFO,WARNING,ERROR,FATAL
sys_log_level=INFO

# store metadata,create it if it is not exist
meta_dir=${DORIS_HOME}/palo-meta
http_port=8030
rpc_port=9020
query_port=9030
edit_log_port=9010

# Choose one if there are more than one ip except loopback address
# 24为网关
priority_networks=hostname/24           

Ⅲ).网络通信端口

二.服务命令

Ⅰ).编译

a).第三方依赖

sh thirdparth/build/thirdparth.sh           

b).FE/BE

sh build.sh           

c).broker

sh build.sh           

Ⅱ).启停

a).启动服务

sh ./doris/fe/bin/start_fe.sh --daemon
sh ./doris/be/bin/start_be.sh --daemon           

b).停止服务

sh ./doris/fe/bin/stop_fe.sh
sh ./doris/be/bin/stop_be.sh           

Ⅲ).验证

URL: http://hostname:8030/system 查看FE节点: http://hostname:8030/system?path=//frontends

二.业务命令

Ⅰ).jdbc连接URL

jdbc:mysql://hostname:9030/doris           

Ⅱ).创建数据库

create database doris;           

Ⅲ).创建用户

create user 'doris' identified by 'password';           

Ⅳ).赋权

grant all on doris to doris;           

Ⅴ).添加BE

ALTER SYSTEM ADD BACKEND "hostname:9050";           

Ⅵ).查看BE状态

SHOW PROC '/backends';           

Ⅶ).删除BE

ALTER SYSTEM DROPP BACKEND "hostname:9050";           

Ⅷ).添加FE

ALTER SYSTEM ADD FOLLOWER "hostname:9050";           

Ⅸ).查看FE状态

SHOW PROC '/frontends';           

Ⅹ).删除FE

ALTER SYSTEM DROPP FOLLOWER "hostname:9050";           

Ⅺ).创建表

CREATE TABLE doris.table2
(
  event_day DATE,
  siteid INT DEFAULT '10',
  citycode SMALLINT,
  username VARCHAR(32) DEFAULT '',
  pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(event_day,citycode,username)
PARTITION BY RANGE(event_day)
(
  PARTITION p1 VALUES LESS THAN ('2019-06-30'),
  PARTITION p1 VALUES LESS THAN ('2019-07-31'),
  PARTITION p1 VALUES LESS THAN ('2019-08-31')
)
DISTRIBUTED BY HASH(siteid) BUCKETS 10
PROPERTIES("replication_num"="1")           

Ⅻ).导入数据

1.database: doris

2.tablename: table2

3.label: 每个批次不可重复

4.column_separator: 数据文件中字段之间特殊符号分割(|-%7c, ,-%2c, t-%09, x01-%01)

分割符	对应值
竖线	%7c
,	%2c
\t	%09
\x01	%01

curl --location-trusted -u db_user -T path/filename.txt http://hostname/8030/api/doris/table2/_load?label=100\&column_separator=%7c           

XIII).查询数据

a).全量查询

b).order by查询

c).join查询

d).子查询

XIV).添加列

ALTER TABLE table1 ADD COLUMN uv BIGINT SUM DEFAULT '0' AFTER pv;           

XV).查看添加列任务

SHOW ALTER TABLE COLUMN;           

XVI).添加ROLLUP

ALTER TABLE table1 ADD ROLLUP rollup_city(citycode, pv);           

XVII).查看ROLLUP任务

SHOW ALTER TABLE ROLLUP;           

XVIII).查询表结构

DESC table1 ALL;           

XIX).查询资源使用

show variables like "mem_limit%";           

XX).查询超时

show variables like "query_timeout%";           

XXI).创建MySQL引擎表

CREATE TABLE IF NOT EXISTES doris.table_name
(
  'agent_id' INT NOT NULL,
  'accept_time' DATETIME NOT NULL,
  'signature' VARCHAR(770) NOT NULL,
  'bad_app_count' BIGINT,
  'total_count' BIGINT,
  'version' INT DEFAULT "0",
  'method_type' VARCHAR(20)
)
ENGINE=mysql
PROPERTIES
(
  "host"="db_hostname",
  "port"="3306",
  "user"="db_user",
  "password"="password",
  "database"="database",
  "table"="table_name"
)