Creenplum操作小技巧

会飞的鸡毛

已于 2023-04-06 15:03:43 修改

阅读量346

点赞数

分类专栏：数据库文章标签：数据库 postgresql

于 2023-01-16 09:45:18 首次发布

本文链接：https://blog.youkuaiyun.com/qq_39744051/article/details/128700537

版权

数据库专栏收录该内容

6 篇文章

订阅专栏

Creenplum相当于是一个pgsql的分布式版本数据库，适用于绝大多数的pgsql语法操作，以下是一些不一样的操作记录；

查询每个节点数据的分布情况

select gp_segment_id,count(*) from tablename group by 1 order by 1;

分析数据表：

analyze tablename;

查询锁表

select*from gp_toolkit.gp_locks_on_relation WHERE lorrelname like'%表名%';

解锁：

select pg_cancel_backend('#{pid}')

建表，根据需求修改添加

drop table if exists rpt.rpt_bill_m ; 
create table rpt.rpt_bill_m (
user_id SERIAL, ----自增序列
acct_month varchar(6),
bill_fee numeric(16,2) ,
user_info text 
)
WITH (
appendonly=true, -- 对于压缩表跟列存储来说，前提必须是appendonly表
orientation=column,-- 列存 row
compresstype=zlib,-- 压缩格式 --QUICKLZ
COMPRESSLEVEL=5, -- 压缩等级 0--9 一般为5足够 压缩表占用存储空间小，读磁盘操作少，查询速度快
OIDS=FALSE
)
DISTRIBUTED BY (user_id) -- 分布键
PARTITION BY LIST("acct_month") -- 分区键
(
PARTITION p_201810 VALUES ('201810'),
PARTITION p_201811 VALUES ('201811'),
PARTITION p_201812 VALUES ('201812'),
default partition other --容错没有分区键在此表
/* PARTITION p_20170801 START('20170801'::DATE) END ('20170831'::DATE)
EVERY ('1 month'::INTERVAL) */
);
comment on column rpt.rpt_bill_m.user_info is '员工备注信息'; -- 注解

查询分布键

SELECT
    aaa.oid,
    aaa.nspname AS "模式名",
    aaa.relname AS "表名",
    aaa.table_comment AS "中文表名",
    ccc.attname AS "分布键" 
FROM
    (
SELECT
    aa.oid,
    aa.relname,
    obj_description ( aa.oid ) AS table_comment,
    bb.localoid,
    bb.distkey,
    regexp_split_to_table( array_to_string( bb.distkey, ' ' ), ' ' ) att,
    dd.nspname 
FROM
    pg_class aa
    LEFT JOIN pg_catalog.gp_distribution_policy bb ON bb.localoid = aa.oid
    LEFT JOIN pg_namespace dd ON dd.oid = aa.relnamespace --模式
    LEFT JOIN pg_inherits hh ON aa.oid = hh.inhrelid --继承表
WHERE
    dd.nspname = 'schemaname' -- 替换成需要的模式schema
    AND hh.inhrelid IS NULL 
    ) aaa
    LEFT JOIN pg_attribute ccc ON ccc.attrelid = aaa.oid 
    AND CAST ( ccc.attnum AS TEXT ) = aaa.att 
WHERE
    ccc.attnum > 0 
AND aaa.relname = 'tablename' --需要查询的表名

    SELECT att.nspname,att.relname,string_agg (a.attname, ',') attby 
  FROM 
  (
   SELECT c.oid,n.nspname,c.relname,regexp_split_to_table (array_to_string (d.distkey, ' '),' ')::int as attnu
   FROM gp_distribution_policy d 
   LEFT JOIN pg_class c ON c.oid = d.localoid 
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
   WHERE c.oid = 'tablename'::regclass
  ) att
  LEFT JOIN pg_attribute a ON a.attrelid = att.oid
  WHERE att.attnu = a.attnum
  GROUP BY 1,2;