Search code examples
postgresqlhivepostgres-fdw

Connect to Hive database tables using postgresql-fdw from postgresql server

Cant connect from postgresql server to hive databases on remote server.

Tried using the following queries but doenst work. Connection established but no response back from hive server.

--create extension postgres_fdw;
DROP USER MAPPING  IF EXISTS  FOR   CURRENT_USER  SERVER data_db;
drop server data_db;
create server data_db
foreign data wrapper postgres_fdw
options (host 'net.com' , port 'hiveport' , SSLMODE 'allow', dbname 'datah');

create user mapping for current_user
server data_db
options(user 'user', password 'password');

drop schema app;
create schema app;

import foreign schema public
from server data_db
into app;

The result was unknown expecting authentication request from the server (SQL state 08001).

I would like to be able to establish a connection to the hive database clusters using the fdw from postgresql to import selective data.

Thank you very much in advance and best regards!

Solution

  • postgres_fdw is for connecting to other PostgreSQL instances. Hive doesn't use the same wire protocol as PostgreSQL does, so surely postgres_fdw would not be expected to connect to it successfully. Perhaps you can try https://github.com/youngwookim/hive-fdw-for-postgresql or https://sourceforge.net/projects/hadoopfdw/.