AthenaFDW: PostgreSQL Foreign Data Wrapper for accessing Athena

AthenaFDW is currently available on our Linux distribution. This Foreign Data Wrapper helps you to leverage the power of Athena from within PostgreSQL. It uses many of the latest FDW features of PostgreSQL to give you native access to Hive data.

PostgreSQL is the worlds most advanced open source RDBMS for transactionally intense OLTP applications. Athena is the worlds most advanced open source system for big data. The AthenaFDW bridges these two worlds.

Install and Enable AthenaFDW

You can install the AthenaFDW extension via pgc command line and then enable the extension in the database you want to use AthenaFDW functions. First, install AthenaFDW for your version of Postgres. For example, to install AthenaFDW with PostgreSQL v10 run the following:


$ ./pgc install athena_fdw
  Unpacking athena_fdw-pg11-3.1-2-linux64.tar.bz2
AthenaFDW is developed and tested with AWS Athena. Below is small sample to get started.

CREATE EXTENSION athena_fdw;

CREATE SERVER athena_server FOREIGN DATA WRAPPER athena_fdw
  OPTIONS (HOST 'hdp-vm', PORT '10000');

CREATE USER MAPPING FOR PUBLIC SERVER athena_server;

CREATE FOREIGN TABLE sample_07 (
    code                   TEXT,
    description            TEXT,
    total_emp              INT,
    salary                 INT
) SERVER athena_server OPTIONS (TABLE 'sample_07');
With the FOREIGN TABLE in place, run a query against it to retrieve data from Hive:

postgres=# SELECT code, total_emp FROM sample_07 ORDER BY code LIMIT 3;
  code   | total_emp
---------+-----------
 00-0000 | 134354250
 11-0000 |   6003930
 11-1011 |    299160
(3 rows)

For more complete information on the pre-req's, take a look at our documentation.