Invalid Property Name Reference

Attempting to use Compass to query data in Data Lake. For the specific field we need, receiving ERROR 401: Invalid property name reference(s). Have checked Object Schema, table is there and field is defined in table. Have checked Atlas details on table, field data is being replicated (i.e. we see the field name and we see field data in the Atlas details). How do we correct this error?

Find more posts tagged with

Infor Data Fabric

Accepted answers

All comments

Prabakaran Nallayagounder

Can you simply run the SELECT statement and see what are columns available?

unknown

Hey @jeffrey-henn ,

Compass SQL isn't automatically aware of schema changes that may happen to data objects over time. In short, this is by design and allows for data administrators to manage schema changes using UAT cycles before promoting changes in higher level environments. What you will notice is that running a SELECT * query will return results which is due to the fact that Compass is retrieving the data from the columns it's aware of. It's only when including new column references in the query that it will produce a 401 error.

To update Compass SQL, you need to run a stored procedure that'll sync Compass with the latest schema available. You can check the documentation here which outlines the infor.clear_table() and infor.clear_data() stored procedures.

The easiest method - though, not always the "fastest" - is to run the clear_table stored procedure with a 'true' argument.

EXEC INFOR.CLEAR_TABLE(&#39;pv&#39;, &#39;true&#39;)

This will drop Compass' cache as well as resyncing with the data model in Data Catalog. When you run your next query, it might take some additional time to cache the data in storage but you'll see your UserName column.

If you were to run the same stored procedure using the 'false' argument, Compass will become aware of the new column(s) but you'll notice that your new columns are returning NULL values for any data residing in Data Lake. Basically, Compass is now aware and caching data from new objects landing in storage but wasn't previously aware of them until now. For that reason, running it with a 'true' flag might be the simplest way to experiment with the platform.

There's a more surgical approach you can take to update your metadata definition without having to eliminate the entirety of your Compass SQL cache by using the infor.clear_data() stored procedure in conjunction with infor.clear_table('pv', 'false').

Lastly, we're looking at potentially adding configuration options for administrators to configure on a per-table basis that would automatically keep Compass in sync with metadata changes upstream. While it's not a timeline commitment today, this thread certainly scores some additional points towards making that a reality for 2024

Copyright © 2025 Infor. All rights reserved.