Te damos la solución a este conflicto, o por lo menos eso creemos. Si tienes inquietudes puedes escribirlo en el apartado de comentarios, que para nosotros será un placer responderte
Solución:
En Redshift, las otras formas en las que puede obtener el tiempo de creación de su tabla buscando el tiempo de inicio y finalización de cualquier ejecución de sql de creación de tabla en el svl_qlog. Hay otras tablas que puede consultar para obtener datos similares, pero el problema de esta forma es que solo se conservan durante un par de días (3-5). Aunque a todo el mundo le gustaría que los metadatos se almacenaran junto con la propia tabla para realizar consultas. Amazon recomienda conservar estos datos para exportar los datos a S3 desde los registros que desea conservar a S3. Entonces, en mi opinión, podría importar estos archivos s3 nuevamente a una tabla permanente que desee llamada aws_table_history o algo así para que estos datos especiales los guarde para siempre.
select * from svl_qlog where substring ilike 'create table%' order by starttime desc limit 100;
select * from stl_query a, stl_querytext b where a.query = b.query and b.text ilike 'create table%' order by a.starttime desc limit 100;
O obtenga solo el nombre de la tabla y la fecha como esta:
select split_part(split_part(b.text,'table ', 2), ' ', 1) as tablename,
starttime as createdate
from stl_query a, stl_querytext b
where a.query = b.query and b.text ilike 'create table%' order by a.starttime desc;
Exporte el historial de datos de Crear tabla que desea a su bucket de S3 creado con su keys. La siguiente declaración de selección generará el nombre de la tabla creada y la fecha y hora en que se creó.
Cree una tabla temporal con los datos que desea exportar a S3.
create table temp_history as
(select split_part(split_part(b.text,'table ', 2), ' ', 1) as tablename, starttime as createdate
from stl_query a, stl_querytext b
where a.query = b.query
and b.text ilike 'create table%' order by a.starttime desc);
Luego cargue esta tabla en S3.
unload ('select * from temp_history')
to 's3://tablehistory' credentials 'aws_access_key_id=myaccesskey;aws_secret_access_key=mysecretkey'
DELIMITER '|' NULL AS '' ESCAPE ALLOWOVERWRITE;
Cree una nueva tabla en AWS Redshift.
CREATE TABLE aws_table_history
(
tablename VARCHAR(150),
createdate DATETIME
);
Luego, vuelva a importarlo a su tabla personalizada.
copy aws_table_history from 's3://tablehistory' credentials 'aws_access_key_id=MYKEY;aws_secret_access_key=MYID'
emptyasnull
blanksasnull
removequotes
escape
dateformat 'YYYY-MM-DD'
timeformat 'YYYY-MM-DD HH:MI:SS'
maxerror 20;
delimiter '|';
Probé todo esto y nos funciona. Espero que esto ayude a algunas personas. Por último, un método más sencillo sería utilizar Talend Big Data Open Studio y crear un nuevo trabajo, tomar el componente tRedshiftRow y pegar el siguiente SQL en él. Luego, cree el trabajo y puede programar la ejecución de .bat (windows) o .sh (unix) en cualquier entorno que desee.
INSERT INTO temp_history
(select split_part(split_part(b.text,'table ', 2), ' ', 1) as tablename, starttime as createdate
from stl_query a, stl_querytext b
where a.query = b.query
and b.text ilike 'create table%' order by a.starttime desc);
COMMIT;
insert into historytable
select distinct s.*
from temp_history s;
COMMIT;
--remove duplicates
DELETE FROM historytable USING historytable a2
WHERE historytable.tablename = a2.tablename AND
historytable.createdate < a2.createdate;
COMMIT;
---clear everything from prestage
TRUNCATE temp_history;
COMMIT;
Existe una forma adecuada de obtener la fecha y hora de creación de la tabla en Redshift, que no se basa en el registro de consultas:
SELECT
TRIM(nspname) AS schema_name,
TRIM(relname) AS table_name,
relcreationtime AS creation_time
FROM pg_class_info
LEFT JOIN pg_namespace ON pg_class_info.relnamespace = pg_namespace.oid
WHERE reltype != 0
AND TRIM(nspname) = 'my_schema';
Por alguna razón, no funciona para tablas muy antiguas. La fecha más antigua que pude encontrar en un grupo mío fue en noviembre de 2018. Quizás la fecha de creación de las tablas no se registró en pg_class_info antes de esa fecha.
Parece que no hay forma de obtener la marca de tiempo de creación de tablas en Redshift. Una solución alternativa es utilizar la tabla STL_DDLTEXT que registra un historial de DDL que incluye CREATE TABLE.
Aquí hay un ejemplo (test_table es un nombre de tabla):
dev=> select starttime, endtime, trim(text) as ddl from stl_ddltext where text ilike '%create%table%test_table%' order by endtime desc limit 1;
starttime | endtime | ddl
----------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------
2016-04-25 05:38:11.666338 | 2016-04-25 05:38:11.674947 | CREATE TABLE "test_table" (id int primary key, value varchar(24));
(1 row)
En el caso anterior, starttime o endtime será una marca de tiempo del test_table creación de tablas.
NOTA:
- Redshift no mantiene STL_DDLTEXT durante mucho tiempo, por lo que no puede utilizar esta forma de forma permanente.
- No puede utilizar esta forma si la tabla se crea de otras formas, como cambiar el nombre de una tabla.
Si conservas algún titubeo o capacidad de arreglar nuestro escrito eres capaz de dejar una reseña y con mucho gusto lo estudiaremos.