Hi Everybory,
How can I know what was the last date that anybody inserted data in a Table ?
Can I see this stadistics ?
Bes Regards!
The last data inserted in a Table
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: The last data inserted in a Table
There's no inherent audit trail for data that was loaded in. You would have to manually sift through requests against the table. You should add a column to your table that tracks the time of the change (and perhaps who made the change) if you need this type of audit.
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: The last data inserted in a Table
Hi,
I wonder if the EPOCHS system table would help?
Example:
I wonder if the EPOCHS system table would help?
Example:
Code: Select all
dbadmin=> SELECT NOW();
NOW
-------------------------------
2014-05-19 06:30:20.355969-04
(1 row)
dbadmin=> CREATE TABLE test (c1 INT);
CREATE TABLE
dbadmin=> INSERT INTO test VALUES (1);
OUTPUT
--------
1
(1 row)
dbadmin=> COMMIT;
COMMIT
dbadmin=> SELECT epoch epoch_number, epoch_close_time "Last DML"
dbadmin-> FROM test
dbadmin-> JOIN epochs
dbadmin-> ON epoch_number = epoch
dbadmin-> ORDER BY 1 DESC;
epoch_number | Last DML
--------------+-------------------------------
1567841 | 2014-05-19 06:30:20.514635-04
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: The last data inserted in a Table
Hi Jim,
I believe the epochs table flushes out the data on regular intervals as I cannot find much information or records on older epochs.
So if the data inserted was today for a table surely, we can check what was the last data inserted in table today, but if the data was inserted 3 days back, it hard to join with the epochs table as the epoch table will not be having the epoch number we are looking for.
Is there any other table which keeps a track of epoch on database from beginning.
I believe the epochs table flushes out the data on regular intervals as I cannot find much information or records on older epochs.
So if the data inserted was today for a table surely, we can check what was the last data inserted in table today, but if the data was inserted 3 days back, it hard to join with the epochs table as the epoch table will not be having the epoch number we are looking for.
Is there any other table which keeps a track of epoch on database from beginning.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: The last data inserted in a Table
Maybe you can create your own epochs table and update it every hour or so vis a cron job?
Code: Select all
dbadmin=> create table my_epochs as select epoch_close_time, epoch_number from epochs;
CREATE TABLE
dbadmin=> select max(epoch_close_time), max(epoch_number) from my_epochs;
max | max
-------------------------------+--------
2014-09-05 15:13:03.144453-04 | 252131
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> insert into my_epochs select epoch_close_time, epoch_number from epochs b where b.epoch_number > (select max(epoch_number) from my_epochs);
OUTPUT
--------
1
(1 row)
dbadmin=> select max(epoch_close_time), max(epoch_number) from my_epochs;
max | max
-------------------------------+--------
2014-09-05 15:13:16.270286-04 | 252132
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.