Skip to content

CartoDB onpremise FDW

Jump to bottom
Jorge Sanz edited this page Jul 9, 2015 · 8 revisions

CartoDB On-Premise FDW Guide

This guide shows how data from an external database can be rendered using the Maps API. SQLite is used in the example to simplify setup, but any database (or datasource) that is supported by an FDW for PostgreSQL could be used instead.

Before Starting

In order to access the on-premise PostgreSQL you need to login in the internal CartoDB container. First login in the VM:

ssh ubuntu@onpremise_ip
# pass: ubuntu

Then, login in the container:

cartodb-ctl -c

Setup the remote database

In a production situation, the remote database would already be running on a server somewhere in the customer's network. For this example, we will create a new SQLite database.

# sqlite3 /tmp/test.db
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE test (a int);
sqlite> insert into test values (10);
sqlite> insert into test values (10);
sqlite> insert into test values (10);

Setup FDW in CartoDB

First you need to figure out what internal CartoDB database name has been assigned to your CartoDB user name. In order to know the database name you can do:

# psql \
    -U postgres \
    -d cartodb_production \
    -c "select database_name from users where username = 'YOURUSERNAME'";

                      database_name
----------------------------------------------------------
 cartodb_dev_user_b7861beb-21d4-46bc-ae00-5f09f77701a6_db

Then connect to the database:

# psql -U postgres cartodb_dev_user_b7861beb-21d4-46bc-ae00-5f09f77701a6_db

Now add FDW extension (this changes if you are not using sqlite) and create the FDW table. Each FDW documents the way the external table can be created

CREATE EXTENSION sqlite_fdw;

CREATE SERVER sqlite_server
    FOREIGN DATA WRAPPER sqlite_fdw
    OPTIONS (database '/tmp/test.db');

CREATE FOREIGN TABLE local_table
    (a int)
    SERVER sqlite_server
    OPTIONS (table 'test');

Then test that it works:

SELECT * FROM local_table;

In order to provide public access to an FDW table, you need to ensure that the publicuser has been granted the SELECT privilege to the foreign tables:

GRANT SELECT ON local_table TO publicuser;
GRANT ALL ON local_table TO development_cartodb_user_b7861beb-21d4-46bc-ae00-5f09f77701a6;

Now you can use that table in any CartoDB Map by including it in a SQL query.

Potential Problems

The main problem you will find is that after you update your data in your remote database the tiles are not updated. This is because the CartoDB cache system uses table names and a special trigger to know when a table is updated to invalidate caches but in case of remote tables this is not possible. We can work around this in two ways:

  • Manually invoke cache invalidation, using CDB_TableMetadataTouch('local_table') every time we need to invalidate cache for that table.
  • Avoid the cache altogether, by adding a function that hides the tables you are using. When CartoDB does not know what tables are being used, cache headers are not set so the maps API always hits the database.

See also, https://github.com/CartoDB/Windshaft-cartodb/pull/297

PostgreSQL FDW

The postgres_fdw extension in included with the PostgreSQL source code. Enter the contrib directory, make and make install and it is ready to go. Note the use of options to control what remote table the foreign table is mapped to, what columns are mapped to, and whether a foreign table is writeable or not.

CREATE EXTENSION postgres_fdw;

CREATE SERVER film_server 
    FOREIGN DATA WRAPPER postgres_fdw 
    OPTIONS (
        host 'localhost', 
        dbname 'foodb', 
        port '5432',
        use_remote_estimate 'false',
        updatable 'true'
);

CREATE USER MAPPING 
    FOR bob 
    SERVER film_server 
    OPTIONS (
        user 'bob', 
        password 'secret'
);

CREATE FOREIGN TABLE films (
    code        char(5) NOT NULL,
    title       varchar(40) OPTIONS ( column_name 'title_en' ) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
    )
    SERVER film_server
    OPTIONS (
        table_name 'films_en',
        schema_name 'webapps',
        use_postgis 'true', -- See below
        updatable 'false'
    );

Spatial Enhancement

We have created a patched version of PostgreSQL that includes a postgres_fdw with support for PostGIS functions and operators. This version will pass spatial restrictions and functions to the remote database, making efficient mapping of remote spatial tables a possibility.

Retrieve and build the appropriate branch for the version of PostgreSQL you want. Everything is exactly the same except there is an additional table option.

  • use_postgis: When enabled on a foreign table will pass down any PostGIS operators or functions it receives for execution on the remote server. Naturally the remote server must have PostGIS enabled or this won't work at all.

Oracle FDW

See CartoDB Oracle FDW.

Clone this wiki locally