Presto【实践 01】Presto查询性能优化（数据存储+SQL优化+无缝替换Hive表+注意事项）及9个实践问题分享

-- 对于经常需要过滤的字段可以预先排序
INSERT INTO TABLE table_name PARTITION ( field_name )
SELECT * FROM nation_file SORT BY sort_field_name;
-- 如果通过 sort_field_name 字段对数据进行过滤则性能将提升
SELECT COUNT(*) FROM table_name WHERE sort_field_name='xxx';

-- GOOD 
SELECT field_name_1, field_name_2, field_name_3 FROM table_name;
-- NOT GOOD
SELECT * FROM table_name;

-- GOOD 
SELECT field_name_1, field_name_2 FROM table_name WHERE partition_time='xxx';
-- NORMAL
SELECT field_name_1, field_name_2 FROM table_name WHERE normal_time='xxx';

-- GOOD 
SELECT field_name_1, field_name_2 FROM table_name GROUP BY id, type;
-- NOT GOOD
SELECT field_name_1, field_name_2 FROM table_name GROUP BY type, id;

-- GOOD 
SELECT field_name_1, field_name_2 FROM table_name ORDER BY sort_field_name LIMIT 100;
-- NOT GOOD
SELECT field_name_1, field_name_2 FROM table_name ORDER BY sort_field_name;

SELECT APPROX_DISTINCT(field_name) FROM table_name;

-- GOOD 
SELECT field_name_1, field_name_2 FROM table_name 
WHERE REGEXP_LIKE(field_name, 'GET|POST|PUT|DELETE');
-- NOT GOOD
SELECT field_name_1, field_name_2 FROM table_name
WHERE
  field_name LIKE '%GET%' OR field_name LIKE '%POST%' OR
  field_name LIKE '%PUT%' OR field_name LIKE '%DELETE%';

-- GOOD 
SELECT field_name_1, field_name_2 FROM large_table l JOIN small_table s ON l.id = s.id;
-- NOT GOOD
SELECT field_name_1, field_name_2 FROM small_table s JOIN large_table l ON l.id = s.id;

-- GOOD 
SELECT checksum(rnk)
FROM (
  SELECT RANK() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
  FROM lineitem
) t
WHERE rnk = 1
-- NOT GOOD
SELECT checksum(rnk)
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
  FROM lineitem
) t
WHERE rnk = 1

CREATE TABLE bdc_dm.res_category(
channel_id1 int comment '1级渠道id',
province string COMMENT '省',
city string comment '市', 
uv int comment 'uv'
)
comment 'example'
partitioned by (landing_date int COMMENT '日期:yyyymmdd')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '\n';

CREATE TABLE bdc_dm.res_category_orc(
channel_id1 int comment '1级渠道id',
province string COMMENT '省',
city string comment '市', 
uv int comment 'uv'
)
comment 'example'
partitioned by (landing_date int COMMENT '日期:yyyymmdd')
row format delimited fields terminated by '\t'
stored as orc 
TBLPROPERTIES ("orc.compress"="SNAPPY");

insert overwrite table bdc_dm.res_category_orc partition(landing_date)
select * from bdc_dm.res_category where landing_date >= 20171001;
ALTER TABLE bdc_dm.res_category RENAME TO bdc_dm.res_category_tmp;
ALTER TABLE bdc_dm.res_category_orc RENAME TO bdc_dm.res_category;

SELECT COUNT(id) FROM table_name WHERE condition_field=1;

SELECT id FROM table_name WHERE condition_field=1;

-- 创建归档数据库的SQL语句如下
CREATE TABLE IF NOT EXISTS table_name (
id INTEGER,
........
partition_date INTEGER
) WITH ( format = 'ORC', partitioned_by = ARRAY['partition_date'] );
-- 查看创建的库结构（只适用于 Presto）
SHOW CREATE TABLE table_name;

SHOW PARTITIONS FROM table_name;

-- 子查询 subquery_1 注意：多个子查询需要用逗号分隔
WITH 
subquery_1 AS (
    SELECT a1, a2, a3 
    FROM Table_a 
    WHERE a3 between 20180101 and 20180131
),      
-- 最后一个子查询后不要带逗号   
subquery_2 AS (
    SELECT b1, b2, b3
    FROM Table_b
    WHERE b3 between 20180101 and 20180131
)
SELECT subquery_1.a1, subquery_1.a2, subquery_2.b1, subquery_2.b2
FROM subquery_1 JOIN subquery_2 ON subquery_1.a3 = subquery_2.b3;

SELECT field_name AS "fieldName" FROM table_name;

-- MySQL的写法
SELECT time_field  FROM table_name WHERE time_field > '2017-01-01 00:00:00'; 
-- Presto中的写法
SELECT time_field  FROM table_name  WHERE time_field > timestamp '2017-01-01 00:00:00';

SELECT TO_HEX(MD5(TO_UTF8('1212')));