How to use Query Results as the source data for other Queries?

1

Query results are currently returned as paged data for a job. What is the recommended way to use the result set of a query as the input data for a new query?

For example: “search within the query results” kind of functionality.

The first query completes and the results are available as job result data. How to specify that job result data as the query input data for an entirely different new query? (all happening programmatically)

2

@KrishnaPG If you do not want to view the first query results then you can add a CTAS to the SQL and then query the CTAS table

3

Thank you @balaji.ramaswamy

The requirement is to provide “run some filter query on a table” first and then “search within results” of the first query results (say on a eCommerce web front end).

Both queries are dynamic in nature (constructed from UI filter params and search params).

CTAS - if we do that on the first query, it can lead to many such tables be created (for many filter queries from various different users at different time), which are essentially temporary tables and may need to be deleted later (probably after the session is over etc.)

Question is:

  1. Is it ok to create many such temporary tables (performance wise)? Can Dremio handle that kind of large number of tables scenarios? Is there any limit on the maximum number of tables allowed?
  2. Is it ok to delete those numerous temporary CTAS tables without degrading the performance of other queries or overall system?

In other words, is Dremio designed to handle “create and delete many temporary tables on the fly” kind of scenarios. (Because usually databases are more optimized for record/data manipulations than table manipulations)