Does Purging actually clear the data out of compass tables?

Hello. Recently I've had a requirement where I need to clear the data out of a large number of data lake tables for a refresh. I was hoping to use the Purge capability to Purge By Filter, and select all of the Data objects to clear out. When i do this for a data object, I get back results in the purge log indicating that a number of objects were purged. When I go to review the data using a compass query though, I don't see any changes. All of my data is still there.

It seems like I need to use the EXEC INFOR.CLEAR_TABLE(''data object name', 'True') command to clear out the data.

I don't understand though why Purge doesn't accomplish the same? Anyone know?

Thanks!

Find more posts tagged with

Infor Data Fabric

Accepted answers

Legacy Contributor

Hey rpow,

Those stored procedures work across any of the Compass interfaces including Compass APIs and the Compass JDBC driver. Can you walk through the steps you took?

For example, I just ran a test and submitted the following via the v1/compass/jobs method & resultFormat set to text/csv:

EXEC INFOR.CLEAR_TABLE('ACME_Products', 'True')

I grabbed the queryID and ran a check on the status (v1/compass/jobs/{queryID}/status):

I was also able to pass the queryID into the result method (v1/compass/jobs/{queryID}/result):

Querying the table afterwards results in no data for ACME_Products

As for your understanding, I think you've largely got it right. The Purge UI/APIs only manage the file storage layer so even if you purge the raw files in Data Lake, Compass has its own local cache that needs to be handled. The CLEAR_TABLES command takes care of this but if you don't run it, it's possible you'll see results based on files that no longer exist in Data Lake.

Down the road, we will tether these features together so it's a bit more seamless (e.g. purging an object should purge the cache as well).

All comments

Legacy Contributor

FYI, opened a ticket on this and got an answer:

The Purge data function in Data Lake does remove data from the data lake, but unfortunately it does not clear out the Compass formatted storage area. This is by design. You must use the clear_table command for each object to clear out the Compass data.

I also don't know of any way to run multiple clear_table commands at once. It must be done one at a time if using the Compass query window, and the API is just as tedious manually. You would likely have to write some code using the API to achieve any automation.

Legacy Contributor

Hey rpow,

Data Lake is really two components: a storage layer and a compute layer (Compass SQL). The purge management features are working with data stored in the file system. Compass has a separate cache it generates to optimize query performance which isn't part of the storage layer.

As a result, you need to run the admin SPs which will dump the cache in this particular case.

There are a few other areas where management of Compass is "disconnected," as it were. Updating metadata in Data Catalog, for example, means having to run an SP to resync Compass with the Data Catalog.

Admittedly, this is a slightly annoying thing and we're well aware

I don't have dates as of yet but we will be working on better connecting some of these management events in the storage and catalog layers with Compass to reduce the manual management.

Legacy Contributor

Thanks Mike. Can you confirm which api call to make to accomplish the dumping of the cache ?

When I execute this query statement in the compass editor, it works:
EXEC INFOR.CLEAR_TABLE('LN_ticst001', 'True')

But when i execute this in the api call using ION API's /v1/compass/jobs API call, it does not. I get a 200 status response from the api call, and no errors. But I don't see any data cleared out when i attempt to validate in the compass query editor in ION Desk.

One other question independent of the ION API call question:

It seems like I need to both purge the table, and then after that run the EXEC INFOR.CLEAR_TABLE command in order to really get the data lake data to clear out for a given object. I think this may be because the purge clears out the data storage, and the CLEAR TABLE command clears out the optimized compass cache. If the purge doesn’t happen, the compass EXEC INFOR.CLEAR_TABLE command runs but I still see data. I think maybe because the cache rebuilds itself when it does not exist? Is that true?

Legacy Contributor

Hey rpow,

Those stored procedures work across any of the Compass interfaces including Compass APIs and the Compass JDBC driver. Can you walk through the steps you took?

For example, I just ran a test and submitted the following via the v1/compass/jobs method & resultFormat set to text/csv:

EXEC INFOR.CLEAR_TABLE('ACME_Products', 'True')

I grabbed the queryID and ran a check on the status (v1/compass/jobs/{queryID}/status):

I was also able to pass the queryID into the result method (v1/compass/jobs/{queryID}/result):

Querying the table afterwards results in no data for ACME_Products

Down the road, we will tether these features together so it's a bit more seamless (e.g. purging an object should purge the cache as well).

Legacy Contributor

Hey Mike, things look like they're all working ok. I think I had 2 different tenants up when comparing data earlier, as the api calls seem to be working fine while testing today. For me, i definitely have to run the purge first on the data objects, and then the CLEAR_TABLES command in order to get back 0 rows in the compass query editor, but no big deal. Thanks again!

Legacy Contributor

Hey Mike,

Last time I tried the purge and clear, can be done one table at a time. Is there a plan in place to do this for more than one table?

Legacy Contributor

It's something you would need to script at this time. That's one of the other challenges raised from some internal Infor users as well so we're looking at how best to address that natively.

Copyright © 2025 Infor. All rights reserved.