Use PostgreSQL file_fdw to Access External Data
Loading external data is a core task for many database administrators. With Postgres we have the powerful option of using Foreign Data Wrappers (FDW) to bring in external data sources. FDWs allow us to access data that is external to the database by using SQL from within the database. These external data sources can be in a number of formats, including other relational databases (Postgres, Oracle, MySQL), NoSQL sources (MongoDB, Redis), raw data files (csv, JSON) and many more.
This post shows how to use
to load remote data from CSV files available from GitHub. Sharing data
via public source control tools like GitHub has become a common way
to make data sets widely available. Other public data is available
from various government and non-profit sites, so this is a handy tool to have available and reuse.
External data source
For this post I am using the
COVID-19 data John Hopkins University is curating. See
the main GitHub page for full attribution
and meta-details about the data.
The specific files I want to load are under
with one file per date and each file containing the latest details by region. The format of the
CSV file has changed at least twice since January, and the level of detail has become more
granular as the virus has spread and more data has been collected.
The current file format started on 3/21/2020 and includes data with the following structure. Below is the first record from the 3/26/2020 file. The U.S. data goes down to the county level and includes a latitude/longitude that can be fed into PostGIS for mapping purposes.
┌─[ RECORD 1 ]───┬──────────────────────────────────────────────┐ │ fips │ 45001 │ │ admin2 │ Abbeville │ │ province_state │ South Carolina │ │ country_region │ US │ │ last_update │ 2020-03-26 23:48:35-06 │ │ lat │ 34.22333378 │ │ lon │ -82.46170658 │ │ confirmed │ 3 │ │ deaths │ 0 │ │ recovered │ 0 │ │ active │ 0 │ │ combined_key │ Abbeville, South Carolina, US │
FDWs are enabled by creating the appropriate extension in the database you need it in.
For accessing data in files we use
CREATE EXTENSION file_fdw;
Next, create the
FOREIGN TABLE requires a server.
The command for
file_fdw is relatively simple, no connection parameters are needed in this case.
CREATE SERVER fdw_files FOREIGN DATA WRAPPER file_fdw;
The following SQL creates the foreign table to connect to the raw CSV file on GitHub. The first line uses
CREATE FOREIGN TABLE, adding the
FOREIGN keyword in the middle of the standard sytnax.
The main declaration of the table itself is like any standard table, name the columns
how we like and provide data types matching the source file. The line with
SERVER fdw_files OPTIONS is where the magic begins.
program option, we can use a wget command to stream
the CSV data directly from GitHub (or any URL returning data!) into Postgres.
CREATE FOREIGN TABLE public.covid_staging ( fips TEXT, admin2 TEXT, province_state TEXT, country_region TEXT, last_update TIMESTAMPTZ, lat FLOAT, lon FLOAT, confirmed INT, deaths INT, recovered INT, active INT, combined_key TEXT ) SERVER fdw_files OPTIONS ( program 'wget -q -O - "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/03-26-2020.csv"', format 'csv', header 'true' );
Verify the table was created and includes data with a quick
([local] 🐘) postgres@covid=# SELECT COUNT(*) FROM public.covid_staging; ┌─[ RECORD 1 ]─┐ │ count │ 3421 │ └───────┴──────┘
Neat! The remote CSV data is now available. What next?
Persist the data
When loading remote data I normally want to persist the data internally. This JHU data source has a new
file for every date of data, so I will create a standard table and load each day's data into that as needed.
This is a good time to take advantage of a new feature in Postgres 12,
I use this to provide a
way geometry column converted from the included longitude (x) / latitude (y) values.
CREATE SCHEMA covid19; CREATE TABLE covid19.daily ( id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, fips TEXT NULL, admin2 TEXT NULL, province_state TEXT NULL, country_region TEXT NOT NULL, last_update TIMESTAMPTZ NOT NULL, lat FLOAT NULL, lon FLOAT NULL, confirmed INT, deaths INT, recovered INT, active INT, combined_key TEXT, source_date DATE, way GEOMETRY(POINT, 3857) GENERATED ALWAYS AS ( ST_Transform(ST_SetSRID(ST_Point(lon, lat), 4326), 3857 )) STORED , CONSTRAINT pk_covid_daily_id PRIMARY KEY (id) );
Create a spatial index on the generated geometry column.
CREATE INDEX gix_covid_daily ON covid19.daily USING GIST (way); COMMENT ON TABLE covid19.daily IS 'COVID-19 daily data loaded from John Hopkins University GitHub repo. See https://github.com/CSSEGISandData/COVID-19'; COMMENT ON COLUMN covid19.daily.source_date IS 'Date used in filename of remote file. e.g. https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/03-26-2020.csv';
INSERT query will load the data from the foreign staging table.
INSERT INTO covid19.daily (fips, admin2, province_state, country_region, last_update, lat, lon, confirmed, deaths, recovered, source_date) SELECT fips, admin2, province_state, country_region, last_update, lat, lon, confirmed, deaths, recovered, '2020-03-26'::DATE FROM public.covid_staging;
With the data loaded to this table it is easy to start visualizing the results in DBeaver. The first thing that is apparent is the data inside the U.S. is available at a relatively granular level and the data outside is mostly by country. The popup dialog is displaying the values from a single point I clicked on within Maine.
Rinse and Repeat
With the remote data for one day loaded permanently to Postgres, use
DROP FOREIGN TABLE public.covid_staging; to
remove the foreign table. The foreign table can be quickly recreated linking to a new file for a new date
and run the
INSERT again to continue loading to the
This post has walked through how to use
file_fdw to link Postgres to a remote file and load it into
a database table. This is a powerful and handy tool for integrating and managing data from a huge number
of public data sources.
Need help with your PostgreSQL servers or databases? Contact us to start the conversation!
Published March 27, 2020
Last Updated March 27, 2020