Saltar al contenido

Obtener esquema de tabla en Redshift

Por fin luego de mucho batallar hemos dado con la solución de este conflicto que agunos lectores de esta web presentan. Si tienes algún detalle que aportar no dejes de aportar tu información.

Solución:

Esta consulta le dará la definición de esquema completa, incluido el específico de Redshift attributes tipo de distribución /key, clasificar key, primario keyy codificaciones de columna en forma de declaración de creación, además de proporcionar una declaración de tabla de modificación que establece el propietario como propietario actual. Lo único que no puede decirte que eres extranjero keys. Estoy trabajando en este último, pero hay un problema de privilegios actual en RS que nos impide consultar las tablas correctas. Esta consulta podría necesitar algunos ajustes, pero no he tenido tiempo ni la necesidad de trabajar más en ella.

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'

Recientemente escribí un script de Python para clonar esquemas de tablas entre clústeres de desplazamiento al rojo. Si solo desea las columnas y los tipos de columna de una tabla, puede hacerlo a través de:

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
;

Pero si necesita los tipos de compresión y distkey / sortkeys, necesita consultar otra tabla:

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

Si desea obtener la estructura de la tabla con la declaración de creación, restricciones y desencadenadores, puede usar la utilidad 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.

Esto generará la sintaxis de creación con todas las restricciones. Espero que esto te ayudará.

Recuerda que tienes la opción de añadir una puntuación objetiva si topaste tu asunto en el momento puntual.

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *