Analytical queries on Entities (queries mapped to file)
In Onesait Platform, an Entity on the RealTime database can store a large amount of information depending on the purpose of the Entity, such as an Entity that receives events from a network of hundreds of devices.
To avoid problems of memory collapse with queries that do not apply limit criteria on Entities with a large number of records, the maximum number of records that will be returned by such queries is configured at installation level.
A generic example is found with the following statement:
select * from MyEntity;
A maximum of 2,000 records will be returned as a response, regardless of whether the Entity has millions of records.
To facilitate the processing of this type of sentences in projects, from version 6.3.1-Yoshi of Onesait Platform, an operation will be included in the Digital Broker to allow the execution of sentences without limit clauses or with very high limits, the result of which will be dumped to a file that will be made available to users.
How is it used?
The operation available in the Digital Broker in the path /rest/ontology/{ontology}/file
has the following interface, with the parameters:

- ontology: Entity on which it is performed by adding a parameter for the type of response.
- query: query to be executed.
- queryType: type of query (SQL or Native).
- responseType: destination of the file to be generated:
- DISK: the file shall be generated in an installation-configurable directory within the Semantic Inf Broker container, which may be mapped to a shared volume so that the file is available for other modules, such as DataFlow, Notebooks, etc.
- S3_MINIO: the file is generated in the MinIO bucket of the Digital Client owner.
- URL: the file is generated in a directory mapped to a shared folder in the Platform loadbalancer, which allows it to be downloaded via a URL.
The response from the service will be a JSON with the information where to locate the file:
- Type Disk:

- Type URL:

- Type S3 (minIO):

Where the file is accessible in the user’s Bucket:

Asynchronous Response
The response of the service is asynchronous, since in queries on large Entities, the query time can be long. To this end, the response JSON includes an identifier that allows the execution status of the sentence to be consulted, which can be in:
- IN_PROGRESS: the sentence is being executed.
- FINISHED: the instruction has finished and its result has been completely dumped to the file.
The operation that allows consulting the status is available in the Digital Broker in the path /rest/ontology/file/{queryId}/status has the following interface:

For example:

Deletion of files
If the result is not stored in S3 (MinIO), the file will remain accessible for a maximum of one hour. After this time, the file will be deleted and if it has not been downloaded, it will be necessary to re-launch the sentence.
Header Image: Lukas Blazek at Unsplash