PostgreSQL/Hologres
PostgreSQL/Hologres
Hologres 官方文档:https://help.aliyun.com/product/113622.html
Hologres 开发指南:https://help.aliyun.com/document_detail/130512.html
PiplineDB
1.查看schema
\dn
2.设置搜索路径
SET search_path TO dw_tb;
3.显示视图定义
\d+ dw_sys.rt_view_request_domain_stat
4.显示schema
select schema_name
from information_schema.schemata
5.查找表名
SELECT * FROM information_schema.tables where table_name like '%view%query%';
6.显示视图
\dv
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;
8.命令行执行sql
psql -h storm2 -p 1932 -U hadoop -d postgres -c 'select * from db3307_auth2.v_api_list' > a
9.命令行执行文件
psql -h storm2 -p 1932 -U hadoop -d postgres -f a.sql > a
10.创建schema
psql -h storm2 -p 1932 -U hadoop -d postgres -f a.sql > a
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
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')
;
使用查询提示(Query Hints)来控制查询执行计划的生成
- NO_CACHE 使用这个查询提示可以阻止查询计划缓存的使用,强制 PostgreSQL 每次执行查询时都重新生成执行计划。
SELECT /*+ NO_CACHE */ column1, column2 FROM your_table WHERE ...
- MATERIALIZED 这个查询提示告诉 PostgreSQL 使用物化视图(Materialized View)来执行查询,而不是使用缓存的执行计划。
SELECT /*+ MATERIALIZED */ column1, column2 FROM your_materialized_view WHERE ...
- INDEX 这个查询提示鼓励 PostgreSQL 使用索引来执行查询,可以在查询中指定要使用的索引名称。
SELECT /*+ INDEX(index_name) */ column1, column2 FROM your_table WHERE ...
- ORDERED 这个查询提示告诉 PostgreSQL 在执行连接查询时按照指定的顺序进行连接,而不是根据优化器的判断进行连接。
SELECT /*+ ORDERED */ column1, column2 FROM table1 INNER JOIN table2 ON ...
- HASH 这个查询提示鼓励 PostgreSQL 使用哈希连接来执行连接查询。
SELECT /*+ HASH */ column1, column2 FROM table1 INNER JOIN table2 ON ...
禁止字符串转义
set standard_conforming_strings = off ;
数据表授权
grant all PRIVILEGES on schema.table_name to userid;
时间日期转小时
TO_CHAR(t01.server_date, 'HH24:00')
根据参数返回天序列或者小时序列
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
)
count distinct 代替(Holo)
select uniq(case when event_code='group_pay_succ' then t01.event_json ->> 'cus__multi_order_id' end)
除零问题
round(oevent.cur_deal_cnt/nullif(oevent.pre_deal_cnt, 0) * 100 - 100, 2) as deal_cnt_chain
判断字符串中是否包含数组中的值
case when @indicate_key LIKE ANY (ARRAY['%deal_income%', '%deal_cnt%', '%deal_uv%','%visit_trans_rate%']) then 'group_pay_succ' end