Greenplum获取表结构

Greenplum获取表结构

最近在折腾greenplum，遇到⼀个蛋疼的问题，那就是获取表结构，也就是建表语句。⼤家都知道在MySQL⾥⾯是⾮常easy的，show create table table_name 就搞定了，在

gpdb⾥⾯就没这么容易，在查询⼤量资料以后终于找到了⽅法。那就是⾃⼰定义⼀个函数去获取，函数中可以嵌套python代码，⾮常的⽅便。但是资料中的代码有⼤量错误，在

经过⼏番调试以后终于可以使⽤了。

如果没有这个函数其实也可以获取表结构，那就是只能导出这个表的结构进⾏查看了。导出表结构的命令是：

pg_dump -s --table=tb1_partition_range_yyyymmdd testdb > tb1_partition_range_yyyymmdd.sql

查看表结构：

[gpadmin@mdw ~]$ cat tb1_partition_range_yyyymmdd.sql

--

-- Greenplum Database database dump

--

SET statement_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = off;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--

-- Name: tb1_partition_range_yyyymmdd; Type: TABLE; Schema: public; Owner: gpadmin; Tablespace:

--

CREATE TABLE tb1_partition_range_yyyymmdd (

id numeric,

yyyymmdd date

)

WITH (appendonly=true, compresslevel=5) DISTRIBUTED BY (id) PARTITION BY RANGE(yyyymmdd)

(

PARTITION p20120811 START ('2012-08-11'::date) END ('2012-08-12'::date) WITH (tablename='tb1_partition_range_yyyymmdd_1_prt_p20120811', orientation=row , appendonly=true, compresslevel=5 ),

PARTITION p20120812 START ('2012-08-12'::date) END ('2012-08-13'::date) WITH (tablename='tb1_partition_range_yyyymmdd_1_prt_p20120812', orientation=row , appendonly=true, compresslevel=5 )

);

ALTER TABLE public.tb1_partition_range_yyyymmdd OWNER TO gpadmin;

--

-- Name: idx_yyyymmdd; Type: INDEX; Schema: public; Owner: gpadmin; Tablespace:

--

CREATE INDEX idx_yyyymmdd ON tb1_partition_range_yyyymmdd USING btree (yyyymmdd);

--

-- Greenplum Database database dump complete

--

还有⼀种⽅法就是\d table_name查看。这个只能查看字段信息啥的。

testdb=# \d+ tb1_partition_range_yyyymmdd

Append-Only Table "public.tb1_partition_range_yyyymmdd"

Column | Type | Modifiers | Storage | Description

----------+---------+-----------+---------+-------------

id | numeric | | main |

yyyymmdd | date | | plain |

Compression Type: zlib

Compression Level: 5

Block Size: 32768

Checksum: t

Indexes:

"idx_yyyymmdd" btree (yyyymmdd)

Child tables: tb1_partition_range_yyyymmdd_1_prt_p20120811,

tb1_partition_range_yyyymmdd_1_prt_p20120812

Has OIDs: no

Options: appendonly=true, compresslevel=5

Distributed by: (id)

Partition by: (yyyymmdd)

testdb=#

总体来说还是不太⽅便，下⾯创建⼀个⾃定义的函数进⾏查看：

1. 创建语⾔

CREATE PROCEDURAL LANGUAGE plpythonu;

2. 创建函数（代码如下）：vim get_table_structure.sql

create or replace function get_table_structure(tablename text)

returns text

as $$

try:

table_name = tablename.lower().split('.')[1]

talbe_schema=tablename.lower().split('.')[0]

except (IndexError):

return 'Please in put "tableschema.table_name"'

get_table_oid="select oid,reloptions,relkind from pg_class where oid='%s'::regclass"%(tablename)

try:

rv_oid=plpy.execute(get_table_oid,5)

if not rv_oid:

return 'Did not find any relation named"'+tablename +'".'

except (Error):

return 'Did not find any relation named"'+tablename +'".'

table_oid=rv_oid[0]['oid']

rv_reloptions=rv_oid[0]['reloptions']

rv_relkind=rv_oid[0]['relkind']

create_sql="";

table_kind='table';

if rv_relkind !='r' and rv_relkind !='v':

plpy.error('%s is not table or view'%(tablename));

elif rv_relkind=='v':

get_view_def="select pg_get_viewdef(%s,'t') as viewdef;" % (table_oid)

rv_viewdef=plpy.execute(get_view_def);

create_sql='create view %s as \n' % (tablename)

create_sql += rv_viewdef[0]['viewdef']+'\n'; table_kind='view'

else:

get_columns="select a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod),\

(select substring(pg_catalog.pg_get_expr(d.adbin,d.adrelid) for 128) \

from pg_catalog.pg_attrdef d where d.adrelid=a.attrelid and d.adnum=a.attnum and a.atthasdef) \

as default,a.attnotnull as isnull from pg_catalog.pg_attribute \

a where a.attrelid= %s and a.attnum >0 and not a.attisdropped order by a.attnum;" % (table_oid);

rv_columns=plpy.execute(get_columns)

get_table_distribution1="select attrnums from pg_catalog.gp_distribution_policy t where localoid = '" + table_oid + "' "

rv_distribution1=plpy.execute(get_table_distribution1,500)

rv_distribution2=''

if rv_distribution1 and rv_distribution1[0]['attrnums']:

get_table_distribution2="select attname from pg_attribute where attrelid='"+table_oid+"' and attnum in (" + str(rv_distribution1[0]['attrnums']).strip('{').strip('}').strip('[').strip(']')+")"

rv_distribution2=plpy.execute(get_table_distribution2,500)

create_sql='create table %s (\n' % (tablename)

get_index="select pg_get_indexdef(indexrelid) as indexdef from pg_index where indrelid=%s" % (table_oid);

rv_index=plpy.execute(get_index);

get_parinfo1="select attname as columnname from pg_attribute where attnum =(select paratts[0] from pg_partition where parrelid=%s) and attrelid=%s;"%(table_oid,table_oid);