Obtenir un schéma de table en Redshift

Bonjour, je suis en train de récupérer le schéma d'une table existante. Je suis mysql développeur et suis en train de travailler avec amazon redshift. Comment puis-je exporter le schéma d'une table existante. Dans mysql, nous pouvons utiliser le show de la commande create table.

SHOW CREATE TABLE tblName;

OriginalL'auteur user3277217 | 2014-04-30

5

Si vous voulez obtenir la structure de la table avec instruction de création, des contraintes et des déclencheurs, vous pouvez utiliser l'utilitaire 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.
```
Ce sera la sortie de la créer syntaxe avec toutes les contraintes. Espérons que cela vous aidera.

Il ressemble pour Postgres 7.3+ le -d drapeau a disparu de pg_dump, et la base de données est régulièrement l'argument de ligne de commande. Qui est: pg_dump -U user_name -s -t table_name db_name.
pg_dump ne comprendront pas d'info sur sortkeys, distkeys, diststyle, de colonne ou de codages si

OriginalL'auteur Anant

Cette requête va vous donner le schéma complet de la définition, y compris le Décalage vers le rouge des attributs spécifiques au type de distribution de clé, clé de tri, clé primaire, et la colonne des codages dans la forme d'une instruction de création ainsi que de fournir une instruction alter table qui définit le propriétaire du propriétaire actuel. La seule chose qu'il ne peut pas vous dire sont des clés étrangères. Je suis en train de travailler sur ce dernier, mais il est actuellement question du privilège de la RS qui nous empêche d'interroger les tables de droite. Cette requête pourrait utiliser un peu de tuning, mais je n'ai pas eu le temps ou le besoin de travailler davantage.

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'

AWS a publié ce cool utilitaire, github.com/awslabs/amazon-redshift-utils, pour la migration vers le bon codage de compression. Il contient toutes les requêtes AWS utilise pour décrire les structures de la table, à l'exception de subventions (voir stackoverflow.com/questions/18741334/... pour ce composant).

OriginalL'auteur mike_pdb

Récemment, j'ai écrit un script python pour cloner des schémas de table entre le décalage vers le rouge des clusters. Si vous souhaitez uniquement les colonnes et les types de colonne d'un tableau, vous pouvez le faire via:

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
;

Mais si vous avez besoin de les types de compression et distkey/sortkeys, vous avez besoin d'interroger une autre table:

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

OriginalL'auteur ciphor

4

Je n'ai pas trouvé de solutions complètes.
Et a écrit un script python:

https://github.com/cxmcc/redshift_show_create_table

Il fonctionnera comme pg_dump, plus de base redshift fonctionnalités, SORTKEY/DISTKEY/DISTSTYLES etc.

OriginalL'auteur cxmcc
2

Dans Postgres, vous souhaitez interroger le catalogue.

De avec psql utiliser les abréviations à une variété de commandes dont la liste que vous aurez obtenir en utilisant \? (de l'aide). À cet effet, soit de:
```
\d yourtable
\d+ yourtable
```
Pour une utilisation dans une application, vous aurez besoin d'apprendre les les questions en cause. C'est relativement simple en exécutant psql -E (pour echo caché requêtes) au lieu de la plaine psql.

Si vous avez besoin précis de l'instruction create table, voir @Anant réponse.

OriginalL'auteur Denis de Bernardy
1

Comme le montrent le tableau ne fonctionne pas sur Redshift:
```
show table <YOUR_TABLE>;
ERROR: syntax error at or near "<YOUR_TABLE>"
```
Nous pouvons utiliser pg_table_def tableau pour obtenir le schéma:
```
select "column", type, encoding, distkey, sortkey, "notnull" 
from pg_table_def
where tablename = '<YOUR_TABLE>';
```
REMARQUE: Si le schéma n'est pas sur le chemin de la recherche, de l'ajouter à chemin de recherche à l'aide de:
```
set search_path to '$user', 'public', '<YOUR_SCHEMA>';
```
OriginalL'auteur Navjot Bhardwaj
0

Sont que vous avez besoin de récupérer par programmation ou de la psql invite?

Dans psql utilisation : \d+ tablename

Par programmation, vous pouvez interroger la norme ANSI INFORMATION_SCHEMA points de vue documenté ici:
```
http://www.postgresql.org/docs/9.1/static/information-schema.html
```
La INFORMATION_SCHEMA.Des TABLES et des INFORMATION_SCHEMA.Des COLONNES de points de vue devraient avoir ce dont vous avez besoin.

OriginalL'auteur codenheim
0

Un moyen facile de faire cela est d'utiliser l'utilitaire fourni par AWS. Tout ce que vous devez faire est de créer la vue dans votre base de données, puis de requête pour obtenir une table ddl. L'avantage d'utiliser ce point de vue est qu'il vous donne la sortkey et distkey ainsi utilisé dans l'original de la commande create table.

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

Une fois que la vue est créé, pour obtenir le ddl d'une table. Vous avez besoin d'interroger ce
```
select ddl from table where tablename='table_name' and schemaname='schemaname';
```
Note: Admin schéma peut ne pas être déjà dans votre cluster. Ainsi, vous pouvez créer ce point de vue dans le schéma public.

OriginalL'auteur Aashish Ola
0

Vous pouvez utiliser admin view fournies par AWS Redshift - https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql

une fois que vous avez créé le point de vue, vous pouvez obtenir de création du schéma de script en cours d'exécution:
```
select * from <db_schema>.v_generate_tbl_ddl where tablename = '<table_name>'
```
OriginalL'auteur LiriB

-1

La commande ci-dessous:

mysql > show create table test.users_info;
Redshift/postgress >pg_dump -U root-w --no-password -h 62.36.11.547 -p 5439  -s -t test.users_info ;

OriginalL'auteur user2086435

Vous devez vous connecter pour publier un commentaire.