DataAbstract: How to map to database tables with rotated names?

The back-end database server will have the table names rotated by the month, for example, detector_data_202003, detector_data_202004 refers to March and April data, respectively. The tables are of the same structure.

Now, question - from the Client Side, I just want the client only sees the table as detector_data, and if the current month is March, then it will be automatically mapped to detector_data_202003, so on as so forth, and if the current month is March, but detector_data_202003 does not exist, then the table will be automatically created.

How can I accomplish that dynamic table creation with rotated names, using Data Abstract?

Or, if I understand correctly, Data Abstract can only map existing tables in the back-end database server?

Hi there,

You can define whatever name you want for the table to be seen on the client side, the mapping is done on the server side. So the client doesn’t have to be aware of the changes, you could achieve your goal by modifying the schema and reloading it on the client side every month. The schema can be load as a resource/file, if you are not changing the field names you should be fine, even then you can do a lot of manipulation prior to loading the schema.

Just dropping ideas. Good luck.

1 Like

Data Abstract won’t automatically create a table for you. The easiest way to achieve what you need is to implement a server in such way:

On startup server checks its Schema by iterating on all Schema tables:
On each table it checks its TargetTable property. It it contains a placeholder (f.e. $MONTH$ ) then server replaces this placeholder with a correct name and tries to access this table. If access fails than server creates this table
Table can be recreated either based on its Schema representation or using a predefined SQL script from server resources.

The only disadvantage of this approach is that the server has to be restarted every month.

1 Like

Thank you.

Still, not a perfect solution for my use case :frowning:

Would it be possible to have sort of server side “schema” event, so we can take care of this “dynamic physical table name mapping”?

For example, signature of the event:

  • OnGetPhysicalTableName(aOriginalNameAsDefinedInSchema: String; out aDecoratedName: String);

The OriginalNameAsDefinedInSchema is the table name used when developing the Data Abstract Schema. The table itself is just an empty table, with its utility being just providing the table definition (rows, columns, indexes etc). For example, table detector_data.

The DecoratedName is the name of the physical table for actual data - the name itself is rotated based on the OriginalTableName, like detector_data_202003. And if the physical table is not existing, DA should automatically create it (may be based on some preset command?)

I have a feeling this should be possible but just may requires some advanced customization, and I just don’t know DA internals good enough to begin with.

1 Like

Seems you are trying to achieve something like this https://www.postgresql.org/docs/10/ddl-partitioning.html ?

I will log this issue for investigation.

Just create a set of tables beforehand. DA has no support of DDL dialects of different DB servers, as well it has no internal ways to create a table in such way. Checking if the table exists on every data access attempt (read or write) will have very significant performance impact.

Also such solution won’t be reliable enough anyway because it won’t be able to properly support SQL statement or stored procedure-based tables.

Thanks, logged as bugs://84040

Yes. :grinning: for partitioning / sharding databases with ever increasing tables of fast growing real-time data. I am exploring how to utilize DA

And, Thank you for the prompt response and looking into this. Your great support gives me confidence using the product. Appreciated.

Another question - when loading a different schema each time, do I need to stop and restart the hosting DA server?

Does the DA server have any way to support “HOT” schema loading? If not, possible to make Schema hot “ hot loadable”?

There is no need to restart the server to provide different Schema for different clients. F.e. Relativity Server provides different Schemas for different clients based on values stored in their sessions.

Thank you but this was not the use case I had in mind

I have a database that will have new tables created constantly.

I am looking to creat and load new schema so new Client connections (eg, a Tableau) can see those new tables (via ODATA)

Old tables remain unchanged so existing clients connections won’t get impacted (presumably)

Doable?

Yes.
However please note that Data Abstract exposes OData v1, so please check if your clients can work with such old version of OData data sources.

Ah thank you for the heads up

I checked - the client is tableau, and it supports ODATA v1-v4 ; so we are good

Just curious - I guess Data Abstract won’t support (no need?) ODATA v4 soon, right?