Upload table and use in query

[MRead (Mike Read)]

This is somewhat related to

Query objects providing a list of IDs

Hi, I would like to execute a query in ordert to select all the entries in a certain table by providing a list as input. Basically, I want to obtain nothing different that an inner join between the selected table and the input list itself. The task would be very easy by using pandas, but putting an entire Rubin table into a dataframe is crazy. I was trying a work-around with a loop like this, but is extremely slow and unefficient. Is there a smarter way to do it? Thanks! %%time object_list…

but didn’t what to hijack that thread.
Within the RSP is it currently possible to upload a file to table and use that in a joint query with the database.
i.e upload a list of objectIds and find all matching records in the Object table. Or is @fritzm suggestion in the other thread of forming a where objectId in (… list of Ids) query the only current solution. We have a use case that will involve millions of objectIds, I presume there is a limit in the string length of query in a TAP request so millions of objectIds would have to be split into thousands of WHERE… IN TAP queries? (What is the maximum string length of a TAP query?).

Thanks
Mike

[fritzm (Fritz Mueller)]

Hi Mike,

Sadly, SELECT WHERE IN is indeed all that we have available for this right now. We are presently working on adding end-user table upload facilities for these sorts of use cases, though, and expect to have that into production in the upcoming year. Until then, the only other available option would be to ingest the table of ObjectIds using the existing administrative ingest facilities.

[MRead (Mike Read)]

Thanks @fritzm . We’ll see how we go with the SELECT WHERE IN. If that proves unwieldy, as user table upload is “in the works”, we can maybe ingest the user table as “one-off” to mimic future functionality.

[fritzm (Fritz Mueller)]

Yes, that would be the recommendation at this time.

Per the other part of your question, I would expect the current query length limit to be generally capped by the setting of the MariaDB max_allowed_packet server variable on the czar and worker nodes. In the current production instances at IDF, this seems to be set at 16 Mb