Tables created through the SQL API not showing up in the Carto Builder

[Evan Miller]

I am attempting to use the SQL API to create a new table, and am having a little trouble. The command I am using seems to work, and does create a new table, but it is not visible through the carto builder interface. I have followed the installation guide and created a development account. Using pgadmin, when I connect to and view all of the entries in the user_tables table associated with the 'carto_db_development' database's schema, I only see tables that were created through the builder itself. There is also another database that was created in postgres named 'cartodb_dev_user_25d25140-7e60-412d-92c5-35ff0a3239f2_db' and when I view the tables under the public schema there, I see all of the tables created through the SQL API (even if they don't appear in the builder GUI) as well as tables created in the builder GUI.

Can anybody help give some insight into what I am doing wrong?

SQL API command:

http://cartotest.localhost.lan:8080/api/v2/sql?q=CREATE TABLE test_table (permitid integer); SELECT cdb_cartodbfytable('test_table')&api_key=APIKEY

I also tried using the 'cartotest' as the user account in conjunction with cdb_cartodbfytable in accordance with the documentation (https://github.com/CartoDB/cartodb/wiki/creating-tables-though-the-SQL-API), although I get an error indicating that cartotest is an invalid schema.

http://cartotest.localhost.lan:8080/api/v2/sql?q=CREATE TABLE test_table (permitid integer); SELECT cdb_cartodbfytable('cartotest', 'test_table')&api_key=APIKEY

The following is two code snippets, one from the test_table which is created using the SQL API and does NOT show up in the builder, and one from the untitled_table that was created using the builder and shows up in the builder correctly...

The only noticeable difference I can see when diffing the two snippets is that the table which works correctly has a few additional GRANT statements. When I apply similar rules to the table I've created through the SQL API afterwards, I am still unable to see the table in the builder:

GRANT ALL ON TABLE public.untitled_table TO "development_cartodb_user_25d25140-7e60-412d-92c5-35ff0a3239f2";
GRANT SELECT ON TABLE public.untitled_table TO tileuser;

Code from the SQL API used to generate my table that does NOT show up in the carto builder:

-- Table: public.test_table


-- DROP TABLE public.test_table;


CREATE TABLE public.test_table
(
    cartodb_id bigint NOT NULL DEFAULT nextval('test_table_cartodb_id_seq'::regclass),
    the_geom geometry(Geometry,4326),
    the_geom_webmercator geometry(Geometry,3857),
    permitid integer,
    CONSTRAINT test_table_pkey PRIMARY KEY (cartodb_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;


ALTER TABLE public.test_table
    OWNER to "development_cartodb_user_25d25140-7e60-412d-92c5-35ff0a3239f2";


-- Index: test_table_the_geom_idx


-- DROP INDEX public.test_table_the_geom_idx;


CREATE INDEX test_table_the_geom_idx
    ON public.test_table USING gist
    (the_geom)
    TABLESPACE pg_default;


-- Index: test_table_the_geom_webmercator_idx


-- DROP INDEX public.test_table_the_geom_webmercator_idx;


CREATE INDEX test_table_the_geom_webmercator_idx
    ON public.test_table USING gist
    (the_geom_webmercator)
    TABLESPACE pg_default;


-- Trigger: test_quota


-- DROP TRIGGER test_quota ON public.test_table;


CREATE TRIGGER test_quota
    BEFORE INSERT OR UPDATE
    ON public.test_table
    FOR EACH STATEMENT
    EXECUTE PROCEDURE cartodb.cdb_checkquota('0.1', '-1', 'public');


-- Trigger: test_quota_per_row


-- DROP TRIGGER test_quota_per_row ON public.test_table;


CREATE TRIGGER test_quota_per_row
    BEFORE INSERT OR UPDATE
    ON public.test_table
    FOR EACH ROW
    EXECUTE PROCEDURE cartodb.cdb_checkquota('0.001', '-1', 'public');


-- Trigger: track_updates


-- DROP TRIGGER track_updates ON public.test_table;


CREATE TRIGGER track_updates
    AFTER INSERT OR DELETE OR TRUNCATE OR UPDATE
    ON public.test_table
    FOR EACH STATEMENT
    EXECUTE PROCEDURE cartodb.cdb_tablemetadata_trigger();


-- Trigger: update_the_geom_webmercator_trigger


-- DROP TRIGGER update_the_geom_webmercator_trigger ON public.test_table;


CREATE TRIGGER update_the_geom_webmercator_trigger
    BEFORE INSERT OR UPDATE OF the_geom
    ON public.test_table
    FOR EACH ROW
    EXECUTE PROCEDURE cartodb._cdb_update_the_geom_webmercator();

Code from the builder used to generate the table that DOES show up correctly in the builder:

-- Table: public.untitled_table


-- DROP TABLE public.untitled_table;


CREATE TABLE public.untitled_table
(
    cartodb_id integer NOT NULL DEFAULT nextval('untitled_table_cartodb_id_seq0'::regclass),
    the_geom geometry(Geometry,4326),
    the_geom_webmercator geometry(Geometry,3857),
    name text COLLATE pg_catalog."default",
    description text COLLATE pg_catalog."default",
    CONSTRAINT untitled_table_pkey PRIMARY KEY (cartodb_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;


ALTER TABLE public.untitled_table
    OWNER to "development_cartodb_user_25d25140-7e60-412d-92c5-35ff0a3239f2";


GRANT ALL ON TABLE public.untitled_table TO "development_cartodb_user_25d25140-7e60-412d-92c5-35ff0a3239f2";


GRANT SELECT ON TABLE public.untitled_table TO tileuser;


-- Index: untitled_table_the_geom_idx


-- DROP INDEX public.untitled_table_the_geom_idx;


CREATE INDEX untitled_table_the_geom_idx
    ON public.untitled_table USING gist
    (the_geom)
    TABLESPACE pg_default;


-- Index: untitled_table_the_geom_webmercator_idx


-- DROP INDEX public.untitled_table_the_geom_webmercator_idx;


CREATE INDEX untitled_table_the_geom_webmercator_idx
    ON public.untitled_table USING gist
    (the_geom_webmercator)
    TABLESPACE pg_default;


-- Trigger: test_quota


-- DROP TRIGGER test_quota ON public.untitled_table;


CREATE TRIGGER test_quota
    BEFORE INSERT OR UPDATE
    ON public.untitled_table
    FOR EACH STATEMENT
    EXECUTE PROCEDURE cartodb.cdb_checkquota('0.1', '-1', 'public');


-- Trigger: test_quota_per_row


-- DROP TRIGGER test_quota_per_row ON public.untitled_table;


CREATE TRIGGER test_quota_per_row
    BEFORE INSERT OR UPDATE
    ON public.untitled_table
    FOR EACH ROW
    EXECUTE PROCEDURE cartodb.cdb_checkquota('0.001', '-1', 'public');


-- Trigger: track_updates


-- DROP TRIGGER track_updates ON public.untitled_table;


CREATE TRIGGER track_updates
    AFTER INSERT OR DELETE OR TRUNCATE OR UPDATE
    ON public.untitled_table
    FOR EACH STATEMENT
    EXECUTE PROCEDURE cartodb.cdb_tablemetadata_trigger();


-- Trigger: update_the_geom_webmercator_trigger


-- DROP TRIGGER update_the_geom_webmercator_trigger ON public.untitled_table;


CREATE TRIGGER update_the_geom_webmercator_trigger
    BEFORE INSERT OR UPDATE OF the_geom
    ON public.untitled_table
    FOR EACH ROW
    EXECUTE PROCEDURE cartodb._cdb_update_the_geom_webmercator();

Thank you in advance for any input on this!

[Nacho Sanchez]

Adding tables that have been created through the SQL API to the dashboard is handled by "Ghost Tables" feature, which is protected behind a feature flag.

In order to enable it, run this at `carto_db_development` database: `insert into feature_flags (id, name, restricted) values (-1, 'ghost_tables', 'f')`

--

---
You received this message because you are subscribed to the Google Groups "cartodb" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cartodb+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Juan Ignacio Sánchez Lara

CARTO Backend Tech Lead

Twitter: @juanignaciosl

CARTO — Predict Through Location

Live sessions to learn CARTO

[Evan Miller]

I tried the insert command, and have confirmed it was successfully inserted into the carto_db_development table feature_flags. Although it looks to have been added correctly, I am still not seeing new tables added through the SQL API in the builder. I've tried creating new tables, and restarting the entire stack.

Any idea what could still be preventing the ghost tables from showing up?

To unsubscribe from this group and stop receiving emails from it, send an email to cartodb+u...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Evan Miller]

For what its worth, I'm not sure if it matters but this was returned after calling the SQL API to create the new table:

notices:

0 "trigger \"track_updates\" for relation \"test_table1\" does not exist, skipping"

1 "trigger \"update_the_geom_webmercator_trigger\" for relation \"test_table1\" does not exist, skipping"

2 "trigger \"test_quota\" for relation \"test_table1\" does not exist, skipping"

3 "trigger \"test_quota_per_row\" for relation \"test_table1\" does not exist, skipping"

[Nacho Sanchez]

 If I want to modify a user's platform limits, can I also do so by modifying the users table directly? For example, if I want to change an import rule, or execution run-time limit, can I just modify that value, or does it also need to be modified elsewhere?

In general, do it in a Rails console. When changes need to be propagated (to Redis storage, for example), the models will take care of it. I suggested SQL for the feature flag because I know that it's enough, but it's not the best suggestion ever. As a rule of thumb, use the Rails console. For users:

$ bundle exec rails c development

> u = User.where(username: 'yourusername').first

> u.whateveryouwanttochange = newvalue

> u.save

Any idea what could still be preventing the ghost tables from showing up?

No idea. If the flag is inserted, the table is cartodbfied and you've restarted both Rails and Resque, it should work™. If you want to debug, begin at VisualizationsController#link_ghost_tables.

 

 

To unsubscribe from this group and stop receiving emails from it, send an email to cartodb+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Evan Miller]

Thanks for the thorough response. I have to apologize, as I am the least familiar with ruby, so it's not entirely clear to me how to utilize the controllers, and various other ruby tools in the link you pasted. If you could provide any insight into that at all, it would be really appreciated.

That being said, I did look at the carto_db_development table 'user_tables' and interestingly, the only table I see there is the table created through the builder, that shows up correctly in the builder. I'm wondering if the issue is related to what I had first mentioned. Referencing the site's documentation, for multi user accounts I think I am supposed to provide the account name (in this case, 'cartotest') in the cdb_cartodbfytable command: SELECT cdb_cartodbfytable('cartotest', 'test_table')&api_key=APIKEY

When I do include the account name 'cartotest' however, I get an error in the response: Invalid schema name "cartotest"

I'm not exactly sure at what point a schema should've been created for the user that was added, but it is not there in either the main 'carto_db_development' database, nor the individual user database. I don't see anything that looks indicative in the create_dev_user script. Do you happen to know if that sounds like something isn't setup correctly?

Thank you!

[Nacho Sanchez]

If I want to modify a user's platform limits, can I also do so by modifying the users table directly? For example, if I want to change an import rule, or execution run-time limit, can I just modify that value, or does it also need to be modified elsewhere?

I have another loosely related question... If I want to modify a user's platform limits, can I also do so by modifying the users table directly? I see in a previous post it was suggested to use the rake command: rake cartodb:set_custom_limits_for_user[username,import_file_size,table_row_count,concurrent_imports]. Is there any reason I cannot modify the table directly?

 

 

Thanks for the thorough response. I have to apologize, as I am the least familiar with ruby, so it's not entirely clear to me how to utilize the controllers, and various other ruby tools in the link you pasted. If you could provide any insight into that at all, it would be really appreciated.

That cartodb:set_custom_limits_for_user rake task is needed in order to change limit values and propagate them. If you just change the values at the table, it might not work, because some of them are also stored elsewhere.

If you wanted to manually change just one value (c, for example), you have to do this:

1. Run Rails console:

$ bundle exec rails c development

2. Inside Rails console, search the user, change the value and save

> u = User.where(username: 'yourusername').first

> u.user_render_timeout = u.user_render_timeout * 10

> u.save

That being said, I did look at the carto_db_development table 'user_tables' and interestingly, the only table I see there is the table created through the builder, that shows up correctly in the builder. I'm wondering if the issue is related to what I had first mentioned.

Yes, that means that your table is not registered at metadata, either because Ghost Tables is not running or because it's not detecting it.

Referencing the site's documentation, for multi user accounts I think I am supposed to provide the account name (in this case, 'cartotest') in the cdb_cartodbfytable command: SELECT cdb_cartodbfytable('cartotest', 'test_table')&api_key=APIKEY
When I do include the account name 'cartotest' however, I get an error in the response: Invalid schema name "cartotest"

You're probably using a normal user (not an organization one), so the schema is `public`. `SELECT cdb_cartodbfytable('test_table')` or `SELECT cdb_cartodbfytable('public', 'test_table')` will work.

I'm not exactly sure at what point a schema should've been created for the user that was added, but it is not there in either the main 'carto_db_development' database, nor the individual user database. I don't see anything that looks indicative in the create_dev_user script. Do you happen to know if that sounds like something isn't setup correctly?

if you run `select database_name, database_schema from users where username = 'USERNAME';` you'll get the database and schema for that user.

This just looks like a problem with automatic registration of tables ("ghost tables"). There's some documentation about it at SQL API doc. If you do that and enable "ghost_tables" flag, it will work. You have more information about feature flags in the manual.

To unsubscribe from this group and stop receiving emails from it, send an email to cartodb+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.