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?
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;