hologres 常用命令

hologres 常用命令设置binlog:查看表结构查日志查binlog筛选已过期的binlog日期函数查询慢sql 查cpu耗时切换表结构日期转换yyyymmdd 转 yyyy-mm-dd查cpu占用2. 表属性查询（DK / SK / CK / TG）3. WHERE 字段分布采集（判断 DK 是否命中查询模式）4. 视图链路追踪（确认查询实际落到的物理底表）5. 表门店字段确认6. 单表查询性能明细（avg / max / rows）设置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 = 1and query_start ‘2024-10-26 08:00:00+08’and query_start ‘2024-10-26 10:00:00+08’and cpu_time_ms 0group by digestorder by sum(cpu_time_ms) desc limit 100;selectquery,count(*) as total_cntfrom hologres.hg_query_logwherequery_start BETWEEN ‘2024-12-13 17:30:00+08’ and ‘2024-12-13 18:00:00+08’group by 1order by 2 desclimit 100查binlogSELECT hg_binlog_lsn,hg_binlog_event_type,hg_binlog_timestamp_us,* FROM test_message_src;查看开启binlog的表的listSELECT*FROMhologres.hg_table_propertiesWHEREproperty_key = ‘binlog.level’AND property_value = ‘replica’;SELECT *FROMwarehouse_bi_car.dws_sale_clue_final_rt WHERE clue_id = 12746690;SELECThg_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,*FROMtable WHEREORDER BYhg_binlog_timestamp_us desc, hg_binlog_lsn desc筛选已过期的binlogWITH log AS (SELECTMAX( hg_binlog_lsn ) hg_binlog_lsn,$update_keysFROM $table_name$filter_sqlGROUP BY $update_keys),res AS (SELECT$update_keysFROM $table_name$filter_sqlGROUP BYu 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.updatek​eys),lmtas(SELECTr.relation_key::text as uk,hg_binlog_lsnFROM res rLEFT JOIN log l ON r.r e l a t i o n k e y = l . relation_key = l.re