大数据最佳实践

# PostgreSQL/Hologres

Hologres 官方文档：https://help.aliyun.com/product/113622.html

Hologres 开发指南：https://help.aliyun.com/document_detail/130512.html

# PiplineDB

1.查看schema

\dn
1

2.设置搜索路径

SET search_path TO  dw_tb;
1

3.显示视图定义

\d+ dw_sys.rt_view_request_domain_stat
1

4.显示schema

select schema_name
from information_schema.schemata
1
2

5.查找表名

SELECT * FROM information_schema.tables where table_name like '%view%query%';
1

6.显示视图

\dv
1

7.创建STREAM

CREATE FOREIGN TABLE IF NOT EXISTS test.streams_track_page_ext_log_utrack (
    data json
)
SERVER pipelinedb;
  
-- 特别情况下，可以对STREAM增加字段(只能增，不能删)
ALTER FOREIGN TABLE test.streams_track_page_ext_log_utrack ADD COLUMN x integer;
-- 删除STREAM
DROP FOREIGN TABLE test.streams_track_page_ext_log_utrack;
1
2
3
4
5
6
7
8
9

8.命令行执行sql

psql -h storm2 -p 1932 -U hadoop -d postgres -c 'select * from db3307_auth2.v_api_list' > a
1

9.命令行执行文件

psql -h storm2 -p 1932 -U hadoop -d postgres -f a.sql > a
1

10.创建schema

psql -h storm2 -p 1932 -U hadoop -d postgres -f a.sql > a
1

11.创建VIEW

CREATE VIEW test.rt_view_stat_daily_utrack WITH (action=materialize) AS
SELECT
    to_date(data->>'server_date', 'YYYY-MM-DD HH24:MI:SS') AS f_ds,
    SUM(COALESCE(cast(data->>'unreal_key', numeric), 0)) AS "testName",  -- 使用SUM时，请务必使用COALESCE把NULL值替换掉，否则会导致严重的数据库后端进程崩溃重启！！！
    COUNT(*) AS pv,
    COUNT(DISTINCT data->>'visit_id') AS uv
FROM test.streams_track_page_ext_log_utrack
GROUP BY
    f_ds
;
  
-- 清空VIEW内数据
SELECT pipelinedb.truncate_continuous_view('test.rt_view_stat_daily_utrack');
  
-- 删除VIEW
DROP VIEW test.rt_view_stat_daily_utrack;
  
-- 创建TTL(Time-To-Live)VIEW表（TTL表可以按照尽量销毁早于指定时间的数据）
CREATE VIEW test.rt_view_stat_daily_ttl_utrack WITH (action=materialize, ttl='1 month', ttl_column='ttl_ds') AS
SELECT
    to_date(data->>'server_date', 'YYYY-MM-DD HH24:MI:SS') AS f_ds,
    day(to_timestamp(data->>'server_date', 'YYYY-MM-DD HH24:MI:SS')) AS ttl_ds,
    COUNT(*) AS pv,
    COUNT(DISTINCT data->>'visit_id') AS uv
FROM test.streams_track_page_ext_log_utrack
GROUP BY
    f_ds,
    ttl_ds
;
-- 可以通过pipelinedb.set_ttl函数对continuous view增加、修改、移除TTL。
-- 具体详见：http://docs.pipelinedb.com/continuous-views.html#modifying-ttls
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

12.创建TRANSFORM

CREATE VIEW test.rt_trans_add_source_name_utrack WITH (action=transform) AS
SELECT
    v1.data->>'source_bu' AS source_bu,
    v1.data->>'source_appd' AS source_appd,
    v1.data->>'source_cate' AS source_cate,
    v1.data->>'source_msg_type' AS source_msg_type,
    v1.data->>'source_msg' AS source_msg,
    v2.source_name,
    v1.data->>'visit_id' AS visit_id,
    v1.data->>'server_date' AS server_date
FROM test.streams_track_page_ext_log_utrack v1
LEFT JOIN dw_setting.dim_source_infos_source v2 ON v1.data->>'source_msg' = v2.source
WHERE v1.data->>'source_msg_type' IN ('uzhi', 'utr', 'zing', 'sem_source')
;
  
-- 从TRANSFORM创建VIEW
CREATE VIEW test.rt_view_source_stat_daily_utrack WITH (action=materialize) AS
SELECT
    to_date(server_date, 'YYYY-MM-DD HH24:MI:SS') AS f_ds,
    source_msg_type,
    source_msg,
    COUNT(*) AS pv
FROM output_of('test.rt_trans_add_source_name_utrack')
GROUP BY
    f_ds,
    source_msg_type,
    source_msg
;
  
-- TRANSFORM后的数据写入到STREAM
CREATE FOREIGN TABLE IF NOT EXISTS test.streams_track_page_from_trans_utrack (
    source_bu       text,
    source_appd     text,
    source_cate     text,
    source_msg_type text,
    source_msg      text,
    source_name     text,
    visit_id        text,
    server_date     text
)
SERVER pipelinedb;
CREATE VIEW test.rt_trans_add_source_name_to_stream_utrack WITH (action=transform, outputfunc=pipelinedb.insert_into_stream('test.streams_track_page_from_trans_utrack')) AS
SELECT
    v1.data->>'source_bu' AS source_bu,
    v1.data->>'source_appd' AS source_appd,
    v1.data->>'source_cate' AS source_cate,
    v1.data->>'source_msg_type' AS source_msg_type,
    v1.data->>'source_msg' AS source_msg,
    v2.source_name,
    v1.data->>'visit_id' AS visit_id,
    v1.data->>'server_date' AS server_date
FROM test.streams_track_page_ext_log_utrack v1
LEFT JOIN dw_setting.dim_source_infos_source v2 ON v1.data->>'source_msg' = v2.source
WHERE v1.data->>'source_msg_type' IN ('uzhi', 'utr', 'zing', 'sem_source')
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

# 使用查询提示（Query Hints）来控制查询执行计划的生成

  1. NO_CACHE 使用这个查询提示可以阻止查询计划缓存的使用，强制 PostgreSQL 每次执行查询时都重新生成执行计划。
SELECT /*+ NO_CACHE */ column1, column2 FROM your_table WHERE ...
1
  1. MATERIALIZED 这个查询提示告诉 PostgreSQL 使用物化视图（Materialized View）来执行查询，而不是使用缓存的执行计划。
SELECT /*+ MATERIALIZED */ column1, column2 FROM your_materialized_view WHERE ...
1
  1. INDEX 这个查询提示鼓励 PostgreSQL 使用索引来执行查询，可以在查询中指定要使用的索引名称。
SELECT /*+ INDEX(index_name) */ column1, column2 FROM your_table WHERE ...
1
  1. ORDERED 这个查询提示告诉 PostgreSQL 在执行连接查询时按照指定的顺序进行连接，而不是根据优化器的判断进行连接。
SELECT /*+ ORDERED */ column1, column2 FROM table1 INNER JOIN table2 ON ...
1
  1. HASH 这个查询提示鼓励 PostgreSQL 使用哈希连接来执行连接查询。
SELECT /*+ HASH */ column1, column2 FROM table1 INNER JOIN table2 ON ...
1

# 禁止字符串转义

set standard_conforming_strings = off ;
1

# 数据表授权

grant all PRIVILEGES on schema.table_name to userid;
1

# 时间日期转小时

TO_CHAR(t01.server_date, 'HH24:00')
1

# 根据参数返回天序列或者小时序列

with t_bridge as
(
    SELECT
        to_char(generate_series(E@cur_start_date::date, E@cur_end_date::date, interval '1 day'),'yyyy-mm-dd') AS cur_ds
        ,to_char(generate_series(E@pre_start_date::date, E@pre_end_date::date, interval '1 day'),'yyyy-mm-dd') AS pre_ds
     where @cur_start_date <> @cur_end_date

    union all

    select
        to_char(generate_series((E@cur_start_date || ' 00:00:00')::TIMESTAMPTZ, (E@cur_end_date|| ' 23:59:59')::TIMESTAMPTZ, interval '1 hour'),'HH24:MI:SS') AS cur_ds
       ,to_char(generate_series((E@pre_start_date || ' 00:00:00')::TIMESTAMPTZ, (E@pre_end_date|| ' 23:59:59')::TIMESTAMPTZ, interval '1 hour'),'HH24:MI:SS') AS pre_ds
    where @cur_start_date = @cur_end_date
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# count distinct 代替（Holo）

select uniq(case when event_code='group_pay_succ' then t01.event_json ->> 'cus__multi_order_id' end)
1

# 除零问题

round(oevent.cur_deal_cnt/nullif(oevent.pre_deal_cnt, 0) * 100 - 100, 2) as deal_cnt_chain
1

# 判断字符串中是否包含数组中的值

case when @indicate_key LIKE ANY (ARRAY['%deal_income%', '%deal_cnt%', '%deal_uv%','%visit_trans_rate%']) then 'group_pay_succ' end
1
更新时间: 9/12/2023, 6:02:23 PM