My Gateway is setup to partition historian data on a monthly basis. This in turn creates a new database table for that period. Each table is labeled with a date stamp such as "sqlt_data_1_2023_09". I understand the "sqlth_partitions" table manages these partitions. Additionally, the older tables will get deleted after 2 years.
I would like to create a MS SQL "View" that joins these tables dynamically by extracting the names from the sqlth_partitions table. The goal is to be able to query all partitions under a single "View Query". I am using Dream Reports for reporting and need a way to query all partitions in a single query/view with a static table name. I am able to query data but I have to use the actual table name "sqlt_data_1_2023_09" but this does not query/join the tables before or after "sqlt_data_1_2023_08" or "sqlt_data_1_2023_10".
I know how to create a SQL View by using the join statement, but I have to use the actual partition names. I cant use this method as new partitions are created monthly and old partitions are deleted monthly. I think the key is to query the partition names from the sqlth_partitions table and dynamically build the SQL View.
Any recommendations on how to join the partitioned historian tables for 3rd party apps would be greatly appreciated.
Thank you Phil. You are correct with using UNION. I misspoke in my original post stating a JOIN. I know how to do the UNION by using the fixed partition table names, but I don't want to use the fixed names, as they will change every month when Ignition rotates the table names. I don't know how to create the UNION by pulling the names from the sqlth_partitions table.
I am somewhat familiar with APIs from the .NET world, but lack the experience in the Ignition/Java world. I will do my due diligence and take closer look at the WebDev Module.
You can't make a simple view that does this. Table names are structure, not data, so cannot be substituted. How to make dynamic SQL varies by DB brand, and you'll need to study that. Or, perhaps, write a script that executes CREATE VIEW ... after retrieving the current set of table names. Run a scheduled event to do this shortly after new partitions are normally created.
(By the way, UNION is often un-optimized in various platforms. The fastest queries would apply the union after each partition's data is filtered by the desired time frame.)
Good point. Like you suggested, I can use ignitions Python to query the sqlth_partitions table and build/execute the CREATE VIEW on a scheduled basis. Thanks for pointing me in the right direction.