Halo数据库跨库查询——DBlink创建及使用

一、Halo数据库之间查询

1、使用dblink扩展跨库查询

1.1 创建测试环境

-- 创建DATABASE、USER、SCHEMA
CREATE DATABASE dblink;
CREATE USER dblink SUPERUSER PASSWORD '123456';
CREATE SCHEMA dblink;
-- 切换连接的库与用户 
\c dblink dblink
-- 创建测试表
CREATE TABLE test_table (
    id INT,
    name VARCHAR(50),
    address VARCHAR(100),
    age INT,
    city VARCHAR(50)
);
-- 插入10条测试数据
INSERT INTO test_table (id,name, address, age, city) 
VALUES 
    (1,'John Doe', '123 Main St', 25, 'New York'),
    (2,'Jane Smith', '456 Elm St', 30, 'Los Angeles'),
    (3,'Michael Johnson', '789 Oak St', 40, 'Chicago'),
    (4,'Emily Davis', '321 Pine St', 35, 'San Francisco'),
    (5,'Sophia Lee', '579 Spruce St', 29, 'Dallas'),
    (6,'David Wilson', '654 Maple St', 28, 'Boston'),
    (7,'Sarah Thompson', '987 Cedar St', 32, 'Seattle'),
    (8,'Daniel Anderson', '135 Walnut St', 38, 'Houston'),
    (9,'Olivia Martinez', '864 Cherry St', 27, 'Miami'),
    (10,'James Taylor', '246 Birch St', 33, 'Denver');
-- 查询插入的数据
SELECT * FROM test_table;

1.2 创建dblink扩展

psql -d oracle
-- 创建dblink扩展
CREATE EXTENSION dblink;

1.3 创建外部服务器连接SERVER

CREATE SERVER link_dblink
FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (dbname 'dblink', host '192.168.87.128', port '1921');

1.4 为本地用户halo创建用户映射USER MAPPING

CREATE USER MAPPING FOR halo
SERVER link_dblink
OPTIONS (user 'dblink', password '123456');

1.5 使用dblink查询测试表test_table，可查询到数据，且数据与源表数据一致

SELECT * FROM dblink('link_dblink', 'SELECT * FROM test_table')
 AS t(
    id INT,
    name VARCHAR(50),
    address VARCHAR(100),
    age INT,
    city VARCHAR(50));

2、 使用postgres_fdw扩展跨库查询

2.1 创建postgres_fdw扩展  

CREATE EXTENSION postgres_fdw;

2.2 创建外部服务器连接SERVER

CREATE SERVER pgfdw_foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.87.128', port '1921', dbname 'dblink');

2.3 为本地用户halo创建用户映射USER MAPPING

CREATE USER MAPPING FOR halo
SERVER pgfdw_foreign_server
OPTIONS (user 'dblink', password '123456');

2.4 创建外部表pgfdw_foreign_table

CREATE FOREIGN TABLE pgfdw_foreign_table (
    id INT,
    name VARCHAR(50),
    address VARCHAR(100),
    age INT,
    city VARCHAR(50)) 
SERVER pgfdw_foreign_server
OPTIONS (schema_name 'public', table_name 'test_table');

2.5 检验外部表pgfdw_foreign_table是否有数据

SELECT * FROM pgfdw_foreign_table@pgfdw_foreign_server;

二、Halo数据库查询Oracle库

1、Oracle库中创建测试环境

--创建测试用户
CREATE USER dblink
  IDENTIFIED BY dblink
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;
GRANT CREATE SESSION, DBA TO dblink;
sqlplus dblink/dblink@ORCL
-- 创建测试表
CREATE TABLE test_table (
    id INT,
    name VARCHAR(50),
    address VARCHAR(100),
    age INT,
    city VARCHAR(50)
);
-- 插入10条测试数据
INSERT INTO test_table (id, name, address, age, city) VALUES (1, 'John Doe', '123 Main St', 25, 'New York');
INSERT INTO test_table (id, name, address, age, city) VALUES (2, 'Jane Smith', '456 Elm St', 30, 'Los Angeles');
INSERT INTO test_table (id, name, address, age, city) VALUES (3, 'Michael Johnson', '789 Oak St', 40, 'Chicago');
INSERT INTO test_table (id, name, address, age, city) VALUES (4, 'Emily Davis', '321 Pine St', 35, 'San Francisco');
INSERT INTO test_table (id, name, address, age, city) VALUES (5, 'Sophia Lee', '579 Spruce St', 29, 'Dallas');
INSERT INTO test_table (id, name, address, age, city) VALUES (6, 'David Wilson', '654 Maple St', 28, 'Boston');
INSERT INTO test_table (id, name, address, age, city) VALUES (7, 'Sarah Thompson', '987 Cedar St', 32, 'Seattle');
INSERT INTO test_table (id, name, address, age, city) VALUES (8, 'Daniel Anderson', '135 Walnut St', 38, 'Houston');
INSERT INTO test_table (id, name, address, age, city) VALUES (9, 'Olivia Martinez', '864 Cherry St', 27, 'Miami');
INSERT INTO test_table (id, name, address, age, city) VALUES (10, 'James Taylor', '246 Birch St', 33, 'Denver');

2、增加halo用户环境变量

export ORACLE_HOME=/u01/app/halo/product/instantclient_12_2
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH=$ORACLE_HOME/sdk/include/lib:$LD_LIBRARY_PATH

3、创建oracle_fdw扩展

CREATE EXTENSION oracle_fdw;

4、创建外部服务器连接SERVER

CREATE SERVER orclfdw_oracle_server
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//10.16.16.212:1521/ORCL');

5、为本地用户halo创建用户映射USER MAPPING

CREATE USER MAPPING FOR halo
SERVER orclfdw_oracle_server
OPTIONS (user 'dblink', password 'dblink');

6、创建外部表orclfdw_foreign_table

CREATE FOREIGN TABLE orclfdw_foreign_table (
    id INT,
    name VARCHAR(50),
    address VARCHAR(100),
    age INT,
    city VARCHAR(50)) 
SERVER orclfdw_oracle_server
OPTIONS (schema 'DBLINK', table 'TEST_TABLE');

7、检验外部表orclfdw_foreign_table是否有数据

SELECT * FROM orclfdw_foreign_table@orclfdw_oracle_server;