The external connectivity feature of the PieCloudDB database

Question

Does the PieCloudDB database provide the functionality to directly load data from external databases?

Can I connect to an external database without any intermediary steps or data transformations and import the data directly into the PieCloudDB database?

Answer 1

PieCloudDB database can directly load data from external databases. Taking Oracle database as an example:

1.Configure the oracle_fdw dependency on PieCloudDB.

The above files should be extracted and placed in the /home/openpie/instantclient_12_2/ directory on all nodes of PieCloudDB. The openpie user's .bash_profile on the operating system should be configured with the following environment variables, which should be set on all nodes.

vim ~/.bash_profile
export ORACLE_HOME=/opt/instantclient_21_9
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin

2.Create fdw in PieCloudDB

psql -p 6003 testdb
drop extension oracle_fdw cascade;
create extension oracle_fdw;

3.Create an external service in PieCloudDB

psql -p 6003 testdb
create server ora_srv_test foreign data wrapper oracle_fdw options(dbserver'ip address:1521/testdb');

4.Create user mapping

create user mapping for openpie server ora_srv_test options (user 'test_user', password 'test_user');

5.Create foreign table

create FOREIGN table test_tb_oracle(id integer,name varchar(100),age integer) server ora_srv_test options (schema 'TEST_USER',table 'TEST_TB');

6.query table data in Oracle through a foreign table in PieCloudDB

select * from test_tb_oracle;