database 在 Redshift 中获取表架构

Get table schema in Redshift

提问by user3277217

Hello I am trying to retrieve the schema of an existing table. I am mysql developer and am trying to work with amazon redshift. How can I export the schema of an existing table. In mysql we can use the show create table command.

您好，我正在尝试检索现有表的架构。我是 mysql 开发人员，正在尝试与 amazon redshift 合作。如何导出现有表的架构。在 mysql 中，我们可以使用 show create table 命令。

SHOW CREATE TABLE tblName;

采纳答案by Anant

If you want to get the table structure with create statement, constraints and triggers, you can use pg_dump utility

如果你想获得带有 create 语句、约束和触发器的表结构，你可以使用 pg_dump 实用程序

pg_dump -U user_name -s -t table_name -d db_name
Note: -s used for schema only dump
if you want to take the data only dump , you can use -a switch.

This will output the create syntax with all the constraints. Hope this will help you.

这将输出具有所有约束的创建语法。希望这会帮助你。

回答by mike_pdb

This query will give you the complete schema definition including the Redshift specific attributes distribution type/key, sort key, primary key, and column encodings in the form of a create statement as well as providing an alter table statement that sets the owner to the current owner. The only thing it can't tell you are foreign keys. I'm working on the latter, but there's a current privilege issue in RS that prevents us from querying the right tables. This query could use some tuning, but I haven't had time or the need to work it further.

此查询将为您提供完整的架构定义，包括 Redshift 特定属性分布类型/键、排序键、主键和列编码，以 create 语句的形式，并提供将所有者设置为当前的更改表语句所有者。它唯一不能告诉你的是外键。我正在研究后者，但 RS 中当前存在一个权限问题，使我们无法查询正确的表。这个查询可以使用一些调整，但我没有时间或需要进一步工作。

select pk.pkey, tm.schemaname||'.'||tm.tablename, 'create table '||tm.schemaname||'.'||tm.tablename
||' ('
||cp.coldef
-- primary key
||decode(pk.pkey,null,'',pk.pkey)
-- diststyle and dist key
||decode(d.distkey,null,') diststyle '||dist_style||' ',d.distkey)
--sort key 
|| (select decode(skey,null,'',skey) from  (select 
' sortkey(' ||substr(array_to_string(
                 array( select ','||cast(column_name as varchar(100))  as str from
                       (select column_name from information_schema.columns col where  col.table_schema= tm.schemaname and col.table_name=tm.tablename) c2
                        join 
                        (-- gives sort cols
                          select attrelid as tableid, attname as colname, attsortkeyord as sort_col_order from pg_attribute pa where 
                          pa.attnum > 0  AND NOT pa.attisdropped AND pa.attsortkeyord > 0
                        ) st on tm.tableid=st.tableid and c2.column_name=st.colname   order by sort_col_order
                      )
                ,'')
              ,2,10000) || ')' as skey
))
||';'
-- additional alter table queries here to set owner
|| 'alter table '||tm.schemaname||'.'||tm.tablename||' owner to "'||tm.owner||'";'   
from 
-- t  master table list
(
SELECT substring(n.nspname,1,100) as schemaname, substring(c.relname,1,100) as tablename, c.oid as tableid ,use2.usename as owner, decode(c.reldiststyle,0,'EVEN',1,'KEY',8,'ALL') as dist_style
FROM pg_namespace n, pg_class c,  pg_user use2 
WHERE n.oid = c.relnamespace 
AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
AND c.relname <> 'temp_staging_tables_1'
and c.relowner = use2.usesysid
) tm 
-- cp  creates the col params for the create string
join
(select 
substr(str,(charindex('QQQ',str)+3),(charindex('ZZZ',str))-(charindex('QQQ',str)+3)) as tableid
,substr(replace(replace(str,'ZZZ',''),'QQQ'||substr(str,(charindex('QQQ',str)+3),(charindex('ZZZ',str))-(charindex('QQQ',str)+3)),''),2,10000) as coldef
from
( select array_to_string(array(
SELECT  'QQQ'||cast(t.tableid as varchar(10))||'ZZZ'|| ','||column_name||' '|| decode(udt_name,'bpchar','char',udt_name) || decode(character_maximum_length,null,'', '('||cast(character_maximum_length as varchar(9))||')'   )
-- default
|| decode(substr(column_default,2,8),'identity','',null,'',' default '||column_default||' ')
-- nullable
|| decode(is_nullable,'YES',' NULL ','NO',' NOT NULL ') 
-- identity 
|| decode(substr(column_default,2,8),'identity',' identity('||substr(column_default,(charindex('''',column_default)+1), (length(column_default)-charindex('''',reverse(column_default))-charindex('''',column_default)   ) )  ||') ', '')
-- encoding
|| decode(enc,'none','',' encode '||enc)
 as str 
from  
-- ci  all the col info
(
select cast(t.tableid as int), cast(table_schema as varchar(100)), cast(table_name as varchar(100)), cast(column_name as varchar(100)), 
cast(ordinal_position as int), cast(column_default as varchar(100)), cast(is_nullable as varchar(20)) , cast(udt_name as varchar(50))  ,cast(character_maximum_length as int),
 sort_col_order  , decode(d.colname,null,0,1) dist_key , e.enc
from 
(select * from information_schema.columns c where  c.table_schema= t.schemaname and c.table_name=t.tablename) c
left join 
(-- gives sort cols
select attrelid as tableid, attname as colname, attsortkeyord as sort_col_order from pg_attribute a where 
 a.attnum > 0  AND NOT a.attisdropped AND a.attsortkeyord > 0
) s on t.tableid=s.tableid and c.column_name=s.colname
left join 
(-- gives encoding
select attrelid as tableid, attname as colname, format_encoding(a.attencodingtype::integer) AS enc from pg_attribute a where 
 a.attnum > 0  AND NOT a.attisdropped 
) e on t.tableid=e.tableid and c.column_name=e.colname
left join 
-- gives dist col
(select attrelid as tableid, attname as colname from pg_attribute a where
 a.attnum > 0 AND NOT a.attisdropped  AND a.attisdistkey = 't'
) d on t.tableid=d.tableid and c.column_name=d.colname
order by ordinal_position
) ci 
-- for the working array funct
), '') as str
from 
(-- need tableid
 SELECT substring(n.nspname,1,100) as schemaname, substring(c.relname,1,100) as tablename, c.oid as tableid 
 FROM pg_namespace n, pg_class c
 WHERE n.oid = c.relnamespace 
 AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
 ) t 
)) cp on tm.tableid=cp.tableid
-- primary key query here
left join 
(select c.oid as tableid, ', primary key '|| substring(pg_get_indexdef(indexrelid),charindex('(',pg_get_indexdef(indexrelid))-1 ,60) as pkey
 from pg_index i , pg_namespace n, pg_class c 
 where i.indisprimary=true 
 and i.indrelid =c.oid
 and n.oid = c.relnamespace
)  pk on tm.tableid=pk.tableid
-- dist key
left join
(  select 
-- close off the col defs after the primary key 
')' ||
' distkey('|| cast(column_name as varchar(100)) ||')'  as distkey, t.tableid
from information_schema.columns c
join 
(-- need tableid
SELECT substring(n.nspname,1,100) as schemaname, substring(c.relname,1,100) as tablename, c.oid as tableid 
FROM pg_namespace n, pg_class c
WHERE n.oid = c.relnamespace 
AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
) t on c.table_schema= t.schemaname and c.table_name=t.tablename
join 
-- gives dist col
(select attrelid as tableid, attname as colname from pg_attribute a where
a.attnum > 0 AND NOT a.attisdropped  AND a.attisdistkey = 't'
) d on t.tableid=d.tableid and c.column_name=d.colname

) d on tm.tableid=d.tableid 
where tm.schemaname||'.'||tm.tablename='myschema.mytable'

回答by ciphor

Recently I wrote a python script to clone table schemas between redshift clusters. If you only want the columns and column types of a table, you can do it via:

最近我写了一个 python 脚本来克隆 redshift 集群之间的表模式。如果您只需要表的列和列类型，可以通过以下方式进行：

select column_name,
  case
    when data_type = 'integer' then 'integer'
    when data_type = 'bigint' then 'bigint'
    when data_type = 'smallint' then 'smallint'
    when data_type = 'text' then 'text'
    when data_type = 'date' then 'date'
    when data_type = 'real' then 'real'
    when data_type = 'boolean' then 'boolean'
    when data_type = 'double precision' then 'float8'
    when data_type = 'timestamp without time zone' then 'timestamp'
    when data_type = 'character' then 'char('||character_maximum_length||')'
    when data_type = 'character varying' then 'varchar('||character_maximum_length||')'
    when data_type = 'numeric' then 'numeric('||numeric_precision||','||numeric_scale||')'
    else 'unknown'
  end as data_type,
  is_nullable,
  column_default
 from information_schema.columns
 where table_schema = 'xxx' and table_name = 'xxx' order by ordinal_position
;

But if you need the compression types and distkey/sortkeys, you need to query another table:

但是如果你需要压缩类型和 distkey/sortkeys，你需要查询另一个表：

select * from pg_table_def where tablename = 'xxx' and schemaname='xxx';

回答by ciphor

I did not find any complete solutions out there. And wrote a python script:

我没有找到任何完整的解决方案。并写了一个python脚本：

https://github.com/cxmcc/redshift_show_create_table

https://github.com/cxmcc/redshift_show_create_table

It will work like pg_dump, plus dealing with basic redshift features, SORTKEY/DISTKEY/DISTSTYLES etc.

它会像 pg_dump 一样工作，加上处理基本的红移功能，SORTKEY/DISTKEY/DISTSTYLES 等。

回答by Denis de Bernardy

In Postgres, you'd query the catalog.

在 Postgres 中，您将查询目录。

From with psqluse the shorthands to a variety of commands whose list you'll get by using \?(for help). Therefor, either of:

从 withpsql使用速记到各种命令，您可以通过使用\?（寻求帮助）获得其列表。因此，以下任一项：

\d yourtable
\d+ yourtable

For use in an app, you'll need to learn the relevant queries involved. It's relatively straightforward by running psql -E(for echo hidden queries) instead of plain psql.

要在应用程序中使用，您需要了解所涉及的相关查询。通过运行psql -E（用于回显隐藏查询）而不是普通 ，它相对简单psql。

If you need the precise create table statement, see @Anant answer.

如果您需要精确的 create table 语句，请参阅 @Anant 答案。

回答by Navjot Bhardwaj

As show table doesn't work on Redshift:

由于显示表在 Redshift 上不起作用：

show table <YOUR_TABLE>;
ERROR: syntax error at or near "<YOUR_TABLE>"

We can use pg_table_def table to get the schema out:

我们可以使用 pg_table_def 表来获取模式：

select "column", type, encoding, distkey, sortkey, "notnull" 
from pg_table_def
where tablename = '<YOUR_TABLE>';

NOTE: If the schema is not on the search path, add it to search path using:

注意：如果架构不在搜索路径上，请使用以下命令将其添加到搜索路径：

set search_path to '$user', 'public', '<YOUR_SCHEMA>';

回答by conetfun

One easy way to do this is to use the utility provided by AWS. All you need to do is to create the view in your database and then query that view to get any table ddl. The advantage to use this view is that it will give you the sortkey and distkey as well which was used in original create table command.

一种简单的方法是使用 AWS 提供的实用程序。您需要做的就是在数据库中创建视图，然后查询该视图以获取任何表 ddl。使用此视图的优点是，它还会为您提供原始创建表命令中使用的 sortkey 和 distkey。

https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql

https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql

Once the view is created, to get the the ddl of any table. You need to query like this -

创建视图后，获取任何表的 ddl。你需要这样查询 -

select ddl from table where tablename='table_name' and schemaname='schemaname';

Note: Admin schema might not be already there in your cluster. So you can create this view in public schema.

注意：您的集群中可能还没有管理员架构。因此，您可以在公共架构中创建此视图。

回答by LiriB

You can use admin view provided by AWS Redshift - https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql

您可以使用 AWS Redshift 提供的管理视图 - https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql

once you have created the view you can get schema creation script by running:

创建视图后，您可以通过运行以下命令获取架构创建脚本：

select * from <db_schema>.v_generate_tbl_ddl where tablename = '<table_name>'

回答by akhil kailasa

To get the column data and schema of a particular table:

要获取特定表的列数据和架构：

• select * from information_schema.columns where tablename='<<table_name>>'

• select * from information_schema.columns where tablename='<< table_name>>'

To get the information of a table metadata fire the below query

要获取表元数据的信息，请触发以下查询

• select * from information_schema.tables where schema='<<schema_name>>'

• select * from information_schema.tables where schema='<< schema_name>>'

回答by Jai

Below query will generate the DDL of the table for you:

下面的查询将为您生成表的 DDL：

SELECT ddl
FROM admin.v_generate_tbl_ddl
WHERE schemaname = '<schemaname>'
AND tablename in (
'<tablename>');