# PostgreSQL

# 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

# 官方文档

PipelineDB文档地址:http://docs.pipelinedb.com/

PostgreSQL文档地址:https://www.postgresql.org/docs/11/index.html

更新时间: 5/11/2021, 3:01:37 PM