Skip to content
Hive Queries to Amazon Athena
C C++ Java Other
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
Test/Cli
.gitignore
AthenaJDBCLoader.java
AthenaJDBCUtils.java
DATATYPES.md
HDP_CLOUDERA_INSTRUCTIONS.md
IMPORT_FOREIGN_SCHEMA.md
JOIN_PUSHDOWN.md
LOGGING.md
META.json
Makefile
README.md
athena_fdw--3.1.sql
athena_fdw.c
athena_fdw.control
athena_fdw.h
deparse.c
hive_funcs.c
jni.h
jni_md.h

README.md

ATHENA_FDW

Foreign Data Wrapper (FDW) that facilitates access to Hive & Amazon Athena from within PostgreSQL.

Key Features

Prepare

In addition to normal PostgreSQL FDW pre-reqs, the primary specific requirements for this FDW are a JDK (we test with JDK 8) and a set of Hive client JAR files for the Hadoop distribution you are connecting with.

Building from Source

First, download the source code under the contrib subdirectory of the PostgreSQL source tree and then build and install the FDW as below:

  1. Create a link to your JVM in the PostgreSQL lib folder with commands something like
cd ~/pg/10.9/lib
ln -s /etc/alternatives/jre_1.8.0/lib/amd64/server/libjvm.so libjvm.so
   or
ln -s /usr/java/latest/jre/lib/amd64/server/libjvm.so libjvm.so
  1. Build the FDW source
cd athena_fdw
make
make install

To execute the FDW

  1. Set the environment variables PGHOME,HIVE_HOME,HADOOP_HOME & HADOOP_FDW_CLASSPATH before starting up PG. These environment variables are read at JVM initialization time.

    PGHOME = Path to the PostgreSQL installation. HIVECLIENT_JAR_HOME = The path containing the Hive JDBC client jar files required for the FDW to run successfully. ATHENA_FDW_CLASSPATH = .:$(echo $HIVECLIENT_JAR_HOME/*.jar | tr ' ' :):/PathToFile/hadoop-core-1.2.1.jar

Usage

The following parameters can be set on a Hive2 foreign server object:

  • host: the address or hostname of the Hive2 server, Examples: "localhost" "127.0.0.1" "server1.domain.com".
  • port: the port number of the Hive2 server.

The following parameters can be set on a Hadoop foreign table object:

  • schema_name: the name of the schema in which the table exists. Defaults to "default".
  • table_name: the name of the Hive table to query. Defaults to the foreign table name used in the relevant CREATE command.

Here is an example:

-- load EXTENSION first time after install.
CREATE EXTENSION athena_fdw;

    -- create server object
CREATE SERVER athena_serv FOREIGN DATA WRAPPER athena_fdw
	OPTIONS(host 'localhost', port '10000');

-- Create a user mapping for the server.
CREATE USER MAPPING FOR public SERVER athena_serv OPTIONS(username 'test', password 'test');

-- Create a foreign table on the server.
CREATE FOREIGN TABLE test (id int) SERVER athena_serv OPTIONS (schema 'exmaple',table 'oorder');

-- Query the foreign table.
SELECT * FROM test limit 5;
You can’t perform that action at this time.