CODEWITHSUNDEEP
pythondatabasepython-2.7hdf5pytables
Nikola Stevanovic
Nikola Stevanovic

Reputation: 251

Pytables - Delete rows from table by some criteria

I have one table with Time32 column and large number of rows. My problem is next.

When my table reaches thousand million rows, I want start archiving every row older than specified value. For creating query I will use Time32 column which represents timestamp for collected data in row. So,using this query I want delete old rows in working table, and store in other table reserved for storing archive records. Is it possible? If yes, what is most efficient way?

I know for whereAppend() method, but this method only copy records, not delete from actual table. Thaks for advice. Cheers!

Upvotes: 1

Views: 1232

Answers (1)

Zeugma
Zeugma

Reputation: 32105

The general way to archive records from one table of a given database to another one is to copy records into the target table, and then to delete the same records in the origin table.

That said, depending of your database engine and the capabilities of the language built on top of that, you can write atomic query commands that do an atomic 'copy then delete' for you, but it is dependent of your database engine capabilities.

In your case of old records archiving, a robust approach can be to copy the records you want to archive by chunks by copying blocks of n records (n sized to your amount of data you can temporary clone, it is a trade-off between temporary additional size and the overhead of a copy delete action), then deleting those n records, and so on until to archive all the records fulfilling your condition Time32 field older than a given timestamp threshold.

Upvotes: 1

Related Questions