# 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
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
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
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
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