hologres 常用命令

设置binlog:

– 设置表属性开启Binlog功能
call set_table_property(‘t.t’, ‘binlog.level’, ‘replica’);

– 设置表属性，配置Binlog TTL时间，单位秒
call set_table_property(‘t.t’, ‘binlog.ttl’, ‘259200’);

查看表结构

select hg_dump_script(‘t’)

查日志

select digest, count(1), avg(cpu_time_ms), sum(cpu_time_ms)
from hologres.hg_query_log where 1 = 1
and query_start > ‘2024-10-26 08:00:00+08’
and query_start < ‘2024-10-26 10:00:00+08’
and cpu_time_ms > 0
group by digest
order by sum(cpu_time_ms) desc limit 100;

select
query,
count(*) as total_cnt
from hologres.hg_query_log
where
query_start BETWEEN ‘2024-12-13 17:30:00+08’ and ‘2024-12-13 18:00:00+08’
group by 1
order by 2 desc
limit 100

查binlog

SELECT hg_binlog_lsn,hg_binlog_event_type,hg_binlog_timestamp_us,* FROM test_message_src;

查看开启binlog的表的list

SELECT
*
FROM
hologres.hg_table_properties
WHERE
property_key = ‘binlog.level’
AND property_value = ‘replica’;

SELECT *
FROM
warehouse_bi_car.dws_sale_clue_final_rt WHERE clue_id = 12746690;

SELECT
hg_binlog_lsn,
case when hg_binlog_event_type= 2 then ‘-D’
when hg_binlog_event_type= 3 then ‘-U’
when hg_binlog_event_type= 5 then ‘+I’
when hg_binlog_event_type= 7 then ‘+U’
else ‘null’ end log_type,
hg_binlog_timestamp_us,
to_timestamp(hg_binlog_timestamp_us/1000000) as hg_binlog_time,
*
FROM
table WHERE
ORDER BY
hg_binlog_timestamp_us desc, hg_binlog_lsn desc

筛选已过期的binlog

WITH log AS (
SELECT
MAX( hg_binlog_lsn ) hg_binlog_lsn,
$update_keys
FROM $table_name
$filter_sql
GROUP BY $update_keys
),
res AS (
SELECT
$update_keys
FROM $table_name
$filter_sql
GROUP BY u p d a t e k e y s ) , l m t a s ( S E L E C T r . update_keys ), lmt as( SELECT r. update