# 查看Catalog
presto> show catalogs;
Catalog
———
hive
hudi
iceberg
mysql
system
(5 rows)

# 使用Hive Catalog
presto> use hive.default;

# 查看表
presto> show tables;
Table
——————-
fgedu_user
fgedu_order
fgedu_product
(3 rows)

# 基础查询
presto> select count(*) from fgedu_user;
_col0
———
1000000
(1 row)

# 过滤查询
presto> select user_id, name, age, city
from fgedu_user
where city = ‘北京’
limit 10;

# 聚合查询
presto> select city, count(*) as user_count, avg(age) as avg_age
from fgedu_user
group by city
order by user_count desc
limit 10;

# Join查询
presto> select u.user_id, u.name, o.order_id, o.amount
from fgedu_user u
join fgedu_order o on u.user_id = o.user_id
where u.city = ‘北京’
limit 100;

# 时间范围查询
presto> select dt, count(*) as order_count, sum(amount) as total_amount
from fgedu_order
where dt between ‘2024-04-01’ and ‘2024-04-07’
group by dt
order by dt;

# 联邦查询（跨Catalog）
presto> select h.user_id, h.name, m.phone
from hive.default.fgedu_user h
join mysql.fgedu.user_profile m on h.user_id = m.user_id
limit 10;

# 优化2：小表前置
# 不推荐
select * from big_table a join small_table b on a.id = b.id;
# 推荐
select * from small_table b join big_table a on a.id = b.id;

# 优化3：使用LIMIT
select * from fgedu_order limit 1000;

# 优化4：只查询需要的列
# 不推荐
select * from fgedu_user;
# 推荐
select user_id, name, age from fgedu_user;

# 优化5：使用EXPLAIN查看执行计划
presto> explain select * from fgedu_user where city = ‘北京’;

# 优化6：配置会话属性
presto> set session query_max_memory = ’20GB’;
presto> set session task_concurrency = 16;
presto> set session optimizer_join_distribution_type = ‘AUTOMATIC’;

# 优化7：资源组配置
# etc/resource-groups.json
{
“rootGroups”: [
{
“name”: “global”,
“softMemoryLimit”: “80%”,
“hardConcurrencyLimit”: 100,
“maxQueued”: 1000,
“schedulingPolicy”: “fair”,
“subGroups”: [
{
“name”: “admin”,
“softMemoryLimit”: “20%”,
“hardConcurrencyLimit”: 10,
“maxQueued”: 100,
“schedulingWeight”: 10
},
{
“name”: “user”,
“softMemoryLimit”: “60%”,
“hardConcurrencyLimit”: 50,
“maxQueued”: 500,
“schedulingWeight”: 1
}
]
}
]
}

# 主要监控页面
– 首页：集群概览、Running Queries、Completed Queries
– 查询详情：SQL、执行计划、性能指标
– Worker列表：Worker节点状态
– 性能指标：CPU、内存、IO

# Prometheus监控
# 配置etc/config.properties
jmx.rmiregistry.port=9080
jmx.server.port=9081

# 使用jmx_exporter
wget https://repo1.maven.org/maven2/io/prometheus/jmx/jmx_prometheus_javaagent/0.19.0/jmx_prometheus_javaagent-0.19.0.jar

# 修改jvm.config
-javaagent:/bigdata/app/jmx_prometheus_javaagent-0.19.0.jar=9404:/bigdata/app/presto/etc/jmx_exporter.yaml

# 配置Prometheus
scrape_configs:
– job_name: ‘presto’
static_configs:
– targets: [‘fgedu-presto-coordinator:9404’, ‘fgedu-presto-worker-01:9404’]

# Grafana Dashboard
# 导入Presto Dashboard
# 关键指标：
– 查询数量（QPS）
– 查询延迟
– 失败查询数
– Worker节点状态
– 内存使用
– CPU使用