I have a time series data historian that I have established an ODBC connection to successfully. I can pull tables in and have filtered the data by time to reduce the records while I am testing. The table I am attempting to bring has a field called _index. I am not sure why they used a reserved word, much less started a column name with an underscore. It is causing FME to choke every time, understandably so. Is it possible to remove or have FME ignore that field and bring the rest of the data into workbench?
Page 1 / 1
How are you reading the data? In many Readers these days there is an option in the Reader Feature Type User Parameters to only read Exposed Attributes, if you uncheck the checkbox for _index, FME won't read the attribute
In this screenshot I'm not reading attribute '1':If this is not available, perhaps a sql query might work. It all depends on how you are reading the data and what format is it in.
I was connecting to other tables in the historian through an ODBC reader. I saw the parameter for the exposed attributes, but that is for tables that have already been imported. When I selected it only the tables that were successfully imported showed up. The error is occurring when I attempt to import a reader feature type only for that one table. They did two things that are not smart. They started the field with an underscore and used a reserved word. All the other tables seem to import just fine. Is there a way in the import to exclude certain fields? I guess through the SQL to run before read option?
Here is the exact error I am seeing when importing the feature type through the ODBC reader:
Database Generated Error Information:
Database Error Message: Unsupported column data type
Database SQL Statement Text: SELECT * FROM "picomp2" WHERE 1=0
SQL State:
Variable Information: Column: 3 / value <UNKNOWN>
The 3rd column is a field named _index.
Ok. I figured it out. I had to create a view on the linked historian table in SQL Server removing the bad field (_index). I then connect to the view like it was a table using a SQL Server reader in FME, not the ODBC connection like I did for the other tables in the historian that didn't have cruddy field names. That was the ticket. Now I am filtering on billions of records. Thank you for the ideas as it helped me work through the process.
My apologies @wisegis, I thought you meant reading the data not importing the feature type definition itself. I'm glad my suggestions were able to help you figure out a solution.
Going one step further you could have used a SQLCreator/Executor with the general query
SELECT <list of columns excluding _index> FROM <tablename>to achieve the same thing without needing the view