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 Databa databa dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = fal;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET arch_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = fal;
--
梅花简单画法
-- 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 p2******* START ('2012-08-11'::date) END ('2012-08-12'::date) WITH
(tablename='tb1_partition_range_yyyymmdd_1_prt_p2*******', orientation=row , appendonly=true, compresslevel=5 ),
PARTITION p2******* START ('2012-08-12'::date) END ('2012-08-13'::date) WITH
(tablename='tb1_partition_range_yyyymmdd_1_prt_p2*******', 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 Databa databa 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_p2*******,
tb1_partition_range_yyyymmdd_1_prt_p2*******
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 'Plea in put "tableschema.table_name"'
get_table_oid="lect oid,reloptions,relkind from pg_class where oid='%s'::regclass"%(tablename)
try:
rv_ute(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':
<('%s is not table or view'%(tablename));
elif rv_relkind=='v':
get_view_def="lect pg_get_viewdef(%s,'t') as viewdef;" % (table_oid)
rv_ute(get_view_def);
create_sql='create view %s as \n' % (tablename)
create_sql += rv_viewdef[0]['viewdef']+'\n';
table_kind='view'
el:
get_columns="lect a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod),\
(lect 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_ute(get_columns)
get_table_distribution1="lect attrnums from pg_catalog.gp_distribution_policy t where localoid = '" + table_oid + "' "
rv_ute(get_table_distribution1,500)
rv_distribution2=''
if rv_distribution1 and rv_distribution1[0]['attrnums']:
get_table_distribution2="lect attname from pg_attribute where attrelid='"+table_oid+"' and attnum in (" +
str(rv_distribution1[0]['attrnums']).strip('{').strip('}').strip('[').strip(']')+")"
rv_ute(get_table_distribution2,500)
create_sql='create table %s (\n' % (tablename)
get_index="lect pg_get_indexdef(indexrelid) as indexdef from pg_index where indrelid=%s" % (table_oid);
rv_ute(get_index);
get_parinfo1="lect attname as columnname from pg_attribute where attnum =(lect paratts[0] from pg_partition where parrelid=%s) and attrelid=%s;"%(table_oid,table_oid);
get_parinfo2=""" lect pp.parrelid,prl.parchildrelid,ca when pp.parkind='h'::"char" then 'hash'::text when
pp.parkind='r'::"char" then 'range'::text when pp.parkind='l'::"char" then 'list'::text el null::text end as
partitiontype,pg_get_partition_rule_def(prl.oid,true) as partitionboundary from pg_partition pp,pg_partition_rule prl where pp.paristemplate=fal and pp.parrelid = %s and prl.paroid = pp.oid order by prl.parname; """ % (table_oid)
v_par_ute(get_parinfo1);
v_par_ute(get_parinfo2);
max_column_len=10
max_type_len=4
max_modifiers_len=4
max_default_len=4
for i in rv_columns:
if i['attname']:
if max_column_len < i['attname'].__len__():
max_column_len=i['attname'].__len__()
if i['format_type']:
if max_type_len < i['format_type'].__len__():
max_type_len=i['format_type'].__len__()
if i['default']:
if max_type_len < i['default'].__len__():
max_default_len=i['default'].__len__()
first=True
for i in rv_columns:
if first==True:
split_char=' ';
first=Fal
el:
split_char=',';
if i['attname']:
create_sql += " " + split_char + i['attname'].ljust(max_column_len+6)+''
el:
create_sql += "" + split_char + ' '.ljust(max_column_len+6)
if i['format_type']:
create_sql += ' ' + i['format_type'].ljust(max_type_len +2)
el:
create_sql += ' ' + ' '.ljust(max_type_len+2)
if i['isnull'] and i['isnull']:给我5分钟
create_sql += ' ' + ' not null '.ljust(8)
妨的拼音if i['default']:
create_sql += ' default ' + i['default'].ljust(max_default_len+6)
create_sql += "\n"
create_sql += ")"
if rv_reloptions:
create_sql +=" with ("+str(rv_reloptions).strip('{').strip('}').strip('[').strip(']') +")\n"
create_sql = place("'",'')
if rv_distribution2:
create_sql += 'Distributed by ('
for i in rv_distribution2:
create_sql += i['attname'] + ','
create_sql =create_sql.strip(',')+')'
elif rv_distribution1:开学省份
create_sql += 'Distributed randomly\n'
if v_par_parent:
partitiontype=v_par_info[0]['partitiontype'];
create_sql +='\nPARTITION BY '+ partitiontype + "("+v_par_parent[0]['columnname']+")\n(\n"; for i in v_par_info:
create_sql +=" " +i['partitionboundary']+',\n';
create_sql=create_sql.strip(',\n');
create_sql+="\n)"
create_sql+=";\n\n"
