Allow executing arbitrary SQL for analysis preparation about camshaft HOT 7 CLOSED

@rochoa, I think that you're aware of this, but we need a plan for this, because it's a blocker for DO through CartoDB/bigmetadata/issues/227.

As this is a blocker, if we can't do it, we'll have to do a workaround either by doing an extra request from Builder and passing type parameters, or hardcoding the known types. Both solutions are not desiderable, though. We think that the best approach for the problem would be allowing execution of arbitrary SQL, as the issue says. We'd like to know if engine team can address this, or at least help @ethervoid or @javitonino with it so we can fix it by ourselves.

cc @jorgesancha @noguerol

from camshaft.

Just to be clear: this issue will track and solve the situation with CartoDB/bigmetadata#227. So it is possible to retrieve some required metadata or do basic queries to prepare the node to be executed.

However, the validation of geometries cannot happen at the node creation phase, as this a hot execution path. For that, the work @ethervoid is doing in #317 is better suited.

I'm gonna update the title for this issue accordingly.

from camshaft.

I've asked @dgaubert about some doubts and we've been talking about this change. He made a very important point which is that the code executed in camshaft in the node creation is going to affect the instantiation map time.

So if we make queries or operations that could be a bit heavy, we're going to introduce an overhead in the map instantiation time (and we don't want that)

For me we are able to overcome this situation with two measures:

• Include a column_types here or make the column_names a tuple with name/type
• For the validation pre-check we have two options:
  • Add a new object precheckQuery in the node options and call it here. This let us return an error message through an exception
  • Include the pre-check here and raise and the exception that rollback the transaction.

To avoid include something new that seems to be unused, I'll try to inject the query inside the current DO query (option 2)

What do you think @javitonino @rochoa?

from camshaft.

Oh, I think we were talking about option 2a all the time. Meaning adding a new hook to the node, and calling it during the node execution.

from camshaft.

But this only allows SQL code, nothing more and you can't get the data returned by that query so is a bit useless to include it there. Could be great to abstract the pre-check from the main query but that's all.

from camshaft.

I have some kind of PoC working.

For camshaft I've done this and I have a precheck function in dataservices like this

Here is an example of an error:

from camshaft.

Done in #323

from camshaft.