Cartodb: Import API performance: Importing 'nothing' takes 5 seconds

[andy-esch]

Context

I'm trying to create an easy, programmatic way to create a table on CARTO that's builder friendly and engine friendly (for use in cartoframes), so I tried a function like this out:

import requests
import time

APIKEY = 'abcdefg'

def _table_touch(table_name, schema):
    resp = requests.post(
        'https://andycopy2.carto-staging.com/api/v1/imports',
        params={'sql': 'SELECT {} LIMIT 0'.format(schema), 'table_name': table_name, 'api_key': APIKEY}
    )
    while True:
        r = requests.get(
            'https://andycopy2.carto-staging.com/api/v1/imports/{}'.format(resp.json()['item_queue_id']),
            params={'api_key': APIKEY}
        )
        if r.json()['state'] == 'complete':
            break
        elif r.json()['state'] == 'failure':
            raise Exception(str(r))
        time.sleep(0.1)
    return r.json()['table_name']

This create a table with a specific schema and a specific name using the Import API's sql and table_name args. The problem is it takes ~4-10+ seconds to create a table with no rows. I would expect this type of operation to be on the order of 0.5 seconds instead.

Steps to Reproduce

1. Update that script above with an API key and correct base url
2. Run it with a custom table name and 'schema' like so:
   python schema = 'null::numeric as mag, null::text as place, null::text as net, null::numeric as depth, null::text as id, null::numeric as longitude, null::numeric as latitude, null::text as magtype, null::numeric as rms' t_start = time.time() _table_touch('abcdefghij', schema) print(time.time() - t_start)
3. Observe the time to create the table (in my tests ~4-10+ seconds)

Current Result

Creating a table with no rows over the Import API takes ~5 seconds

Expected result

That creating a table with no rows would only take a small fraction of a second

Browser and version

n/a

.carto file

n/a

Additional info

nope, thanks

cc @pramsey

[pramsey]

There needs to be a fast, reliable, programmatic way to create a table that is registered to the UI, to support CartoFrames and anyone else who wants to bring data automatically. Otherwise the data is imported but users cannot tell, as it doesn't show up in the UI until (pages are refreshed|a timer tickets over|something).

[rafatower]

What you are testing there is the average waiting time in the queue, at the moment of sending the tests.

Most likely those 5 seconds are related to the time between checks for new stuff in resque

You cannot have an API-fits-all the cases, that is, an API that can swallow files of arbitrary formats, contents and sizes. The tradeoff is an async API, with a bound-but-not-known-up-front waiting time.

At least initially, I don't think what you're trying to do fits in the purpose of the import api.

[rafatower]

And with that I'm not saying that I oppose to what you are trying to do, not at all.

I'd say you need a new editor/builder small endpoint. A synchronous one to register tables. A new one that shouldn't be needed for a new dashboard.

But I cannot spend a second on it until I close the first phase of the COPY.

I wish we could reframe these tickets and conversations in a more productive form... really.

[pramsey]

It's OK, @rafatower! 😄
If we agree there's a functional need, let's talk about what the best place to meet that functional need is? We can discuss it in this ticket, or open a new feature somewhere, but let's have the discussion at least. I'd say there's currently some options on the table:

• Another HTTP end point, in cartodb (maybe?) or in SQL API (probably not?)
• (Optionally) A SQL function that hits that end point, so that registration is available via SQL?

Question which I don't know the answer to: is there already an HTTP end point to force a scan of tables and register unregistered ones? Does the editor/builder already have that lying around? Presumably there is already a method to call in rubyland that does what we want, yes?

[andy-esch]

No problem @rafatower, thanks for your perspective 🙂 I agree this isn't in the spirit of what the Import API is best used for. I'm only doing it this way because I can't find an easier way to create a table with a specified schema that's also registered with the dashboard/has a persistent URL.

I'm also happy to provide input if a new 'dataset' end point is created.

[rafatower]

I'd think about this request in the lights of a new dashboard vs editor/builder, plus the ability to instantiate maps (isn't that right, @andy-esch ?) , @inigomedina and @rochoa

[inigomedina]

Yes, we should look at this request from the new dashboard perspective. Similar situation, @andy-esch, we have when talking about ghost tables.

@jorgesancha and @alonsogarciapablo are leading the new dashboard initiative. You need to bring them into this conversation.

[jorgesancha]

Great discussion, guys. I agree with Paul that there should absolutely be a straightforward way to create a table and make it visible in the UI.

Tangentially, one of the things we want to enable as part of the OAuth autorization is the ability to create datasets and share them with users of the same organization. That way applications could on the fly check if a required dataset for the app to run exists and, if not, create it and share it on the fly

That is a little bit outside of the scope of this discussion but it was in that context that some us (@javitonino @luisbosque @alonsogarciapablo and I) were discussing that it is probably a good moment to review whether we want to expose some of those until now private/builder APIs and how these should look.

We are going to be creating an issue for the project soon

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.