presto sql 常用函数、性能优化 、常见问题（更新ING）

presto介绍

Proesto 官网文档

Presto查询引擎是一个Master-Slave的架构，由一个Coordinator节点，一个Discovery Server节点，多个Worker节点组成，Discovery Server通常内嵌于Coordinator节点中。Coordinator负责解析SQL语句，生成执行计划，分发执行任务给Worker节点执行。Worker节点负责实际执行查询任务。Worker节点启动后向Discovery Server服务注册，Coordinator从Discovery Server获得可以正常工作的Worker节点。如果配置了Hive Connector，需要配置一个Hive MetaStore服务为Presto提供Hive元信息，Worker节点与HDFS交互读取数据。

特点：
1、presto对于数组的取值是从1开始的，hive是从0开始的
2、presto不支持hive视图
3、完全基于内存的并行计算、动态编译执行计划、GC控制
参考
参考-部署

基本语法

1.DDL

• 添加/修改字段名
  ALTER TABLE 表名 ADD COLUMN 字段名 numeric(18,0);
  alter table 表名 rename column A to B
• 修改字段类型
  alter table 表名 alter column 字段名 type not null
  ALTER TABLE 表名 ALTER COLUMN 字段名 numeric(18,0)
• 修改字段默认值
  alter table 表名 add default (0) for 字段名 with values

-- 如果字段有默认值，则需要先删除字段的约束，在添加新的默认值
select c.name from sysconstraints a
　　inner join syscolumns b on a.colid=b.colid
　　inner join sysobjects c on a.constid=c.id
　　where a.id=object_id('表名')
　　and b.name='字段名'
-- 根据约束名称删除约束
alter table 表名 drop constraint 约束名
-- 根据表名向字段中增加新的默认值
alter table 表名 add default (0) for 字段名 with values

• 删除字段
  alter table 表名 drop column 字段名;　
  更多请参考

2.SHOW

• SHOW COLUMNS FROM table：列出 表 中的列及其数据类型和其他属性
• Select current_user
• SHOW TABLES [ FROM schema ] [LIKE pattern ：列出指定 库 或当前库中的表。 可以用 LIKE 子句控制列出的表名
• SHOW SCHEMAS [ FROM catalog ]：列出 catalog 或当前catalog中的库
• SHOW SESSION（列出当前会话属性）
• SHOW CATALOGS
• DESCRIBE table_name：查看一个已经存在的表结构
• SHOW FUNCTIONS：列出全部可用于查询的函数
• EXPLAIN [ ( option [, …] ) ] statement：显示一个语句的逻辑或分布式执行方案。其中option为FORMAT { TEXT | GRAPHVIZ } 或TYPE { LOGICAL | DISTRIBUTED }

3.动态SQL：where 1=1

where 1=1主要作用是拼凑动态的sql语句，即为了避免where 关键字后面的第一个词直接就是 “and”而导致语法错误，从而方便程序逻辑处理，当然，使用不当也会影响效率。

--使用场景:
lv_string = 'select tbl_name,tbl_desc from tbl_test where 1=1' +l_condition;
--当用户选择了查询的名称'abc'时
l_condition ='and tbl_name = ''abc'''
--但是当用户没有选择名称查询时l_condition就为空串''这样运行也不会出错，相当于没有限制名称条件。
lv_string = 'select tbl_name,tbl_desc from tbl_test where 1=1 '
--但如果没有1=1的条件，则报错
lv_string = 'select tbl_name,tbl_desc from tbl_test where '

详细的解释参考

4. 1<>1 用法

用于只取结构不取数据的场合
例如：
create table table_temp tablespace tbs_temp as
select * from table_ori where 1<>1
建成一个与table_ori 结构相同的表table_temp，但是不要table_ori 里的数据。（除了表结构，其它结构也同理)

5.insert into & insert overwrite

overwrite会覆盖现有的数据（先删除 hive 表的数据，再执行写入操作），而into是直接将数据写入库（向 hive 表尾部追加数据）。
hive 动态分区

6.Select Condition条件查询模拟解析

常用函数

1.窗口函数及其搭配

例：sum(a)over(partition by x)
根据x分组后对a求和
rank()over(partition by x order by y desc)
根据x分组后按y的值降序排列并生成排名

• rank()会考虑重复情况（返回值就是小于等于该行值的行数），如果字段值相同，则返回序号相同，下一条记录需要则需顺延一位，即跳跃排序，如果不想重复可以使用dence_rank()， 用法相同，结果为连续排序
• row_number()：为查询出来的每一行记录生成一个序号，依次排序
• cume_dist() ：计算某个值在该组中的累积分布（即值在改组中的相对位置），返回小于等于(该行值的行数 -1)/ (总行数-1)，结果的取值范围为（0,1]
• precent_rank()： 计算该行值在该组中的百分比位置（返回小于等于该行值的行数 / 总行数 ）

2.数据转换cast（）

cast(x as bigint)
bigint整数/varchar字符/decimal(10,2)小数

3.日期函数

date_format(date_add(‘day’,-1,CURRENT_DATE), ‘%y%m%d’)
获得昨天的日期，-1位置可以更改，day可换成month、year

4.连接字符串

• 横向连接：concat(a,b,c,…)，这里abc可以是变量也可以是自定义的连接符，如’-’、’,’、’；’；
• 纵向连接：array_join(array_agg(a),’,’) ，可以搭配group by实现组内连接；mysql对应使用
  group_concat(t1.response_body separator ‘,’)
• 还有一种：SELECT 'hello ’ || ‘presto’

5.正则匹配函数

• regexp_like(string pattern,‘GET|POST|PUT|DELETE’)
• 正则替换（字符串替换）：regexp_place((string subject, string pattern, string str))，返回替换后的结果
• 正则解析（捕获分组）：regexp_extract(string subject, pattern[, int index])
  str是被解析的字符串或字段名；regexp 是正则表达式；idx是分组序号，取值范围是0~n(n为捕获分组个数)，返回pattern中第n个捕获分组匹配到的结果；默认值为1。（0返回全部对应的结果；1返回第一个() 对应的结果；以此类推）

常用的一些模式：
"." 任意单个字符
"*" 匹配前面的字符0次或多次
"+" 匹配前面的字符1次或多次
"?" 匹配前面的字符0次或1次
"\d" 等于 [0-9]，使用的时候写成'\\d'
"\D" 等于 [^0-9]，使用的时候写成'\\D'
([0-9]+) 正则表达式，意为筛选数字，+意为多次重复。

注：如果 subject为空，或者pattern为空，或者pattern匹配不到字符串，则返回值为空

6.with语句

可以在在查询语句非常复杂且有多层嵌套的时候使用；也可以在在create table中使用，例如：

CREATE TABLE tbl_new AS WITH tbl_alias1 AS (SELECT a, MAX(b) AS b, MIN(c) AS c FROM tbl1),
                             tbl_alias2 AS (SELECT a, AVG(d) AS d FROM tbl2)
SELECT 
tbl_alias1.*, 
tbl2_alias.* 
FROM tbl_alias1 
JOIN tbl_alias2 
ON tbl_alias1.a = tbl_alias2.a

7.Group by

Group by语句的对象可用数字代替，如：

SELECT TD_TIME_FORMAT(time, 'yyyy-MM-dd HH', 'PDT') hour, 
count(*) cnt
FROM my_table
GROUP BY 1

但要注意这些数字是从1开始的（有别于程序从0开始的思维）。

8.greatest（）& least （）

找出最大最小值：SELECT greatest(5, 10)

9.coalesce()

COALESCE (expression_1, expression_2, …,expression_n)
依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。
用途：返回第一个非空值；假如某个字段默认是null，你想其返回的不是null，而是比如0或其他值，可以使用这个函数
coalesce(1, 2,3); --return 1
coalesce(null,null,3,null,5); --return 3
COALESCE(NULL, NULL, NULL, NULL); – Return NULL
SELECT COALESCE(field_name,0) as value from table;

提高性能——调试

常见的但内存密集型的语句块有:distinct、UNION、ORDER BY、GROUP BY (许多字段的情况)、joins (各种JOIN)，使用的时候可以有针对的做一些优化

1.指定需要返回的字段，不要直接SELECT *

2.合理使用GROUP BY

• 当过滤条件作用在分区字段上面时，可以减少数据扫描的范围，有效提升查询性能；
• 当group by的数值型字段将比字符型更节省内存空间；
• 还应考虑统计字段基数（某字段拥有不同值的个数），将基数大的字段放在前面
• 减少GROUP BY的字段

3.order by 和 limit 配合使用

• order by 将所有数据放到一个worker中进行排序，这将消耗大量的内存空间，搭配limit，可以有效减小使用的内存空间，提升查询性能；
• 另外，可以只使用size=N的优先级队列所占用内存空间非常小，所以在使用topN就能满足需求时优先使用topN

4.join的时候把大表放在左边

• presto在join的时候采用的是broadcast join，意思是右边的表将全部数据send到各个worker和左边的表（每个worker持有一部分左边表的数据）进行关联查
• 有的时候如果右边的表确实很大，那么有可能遇到“ERROR：Exceeded max memory xxGB”，这个xxGB是配置文件中指定的每次查询worker使用的最大内存空间。超过这个阈值将报这个异常信息。这种情况要不就调整参数，要不就使用“distributed hash join”。
• 另，如果左表和右表都比较大，则：
  修改配置distributed-joins-enabled (presto version >=0.196)，然后在每次查询开始使用distributed_join的session选项

  -- set session distributed_join = 'true'
  

5.不考虑去重的情况下，用UNION ALL替代UNION

6.数据库归档

当有一些表数据量很大但是又不会经常用的的时候，就可以考虑数据归档，这部分我还没整特别明白，下次再补

presto查询SQL报错汇总

remote page is too large

错误原因：body.length+content.length长度不能超过102400