Create a proof of concept for cross database queries between Oracle and Postres

TickleThePanda commented 3 years ago

See #7

/overwrite opened 2020-09-04

TickleThePanda commented 3 years ago

This assumes that database services can do heterogeneous connections.

Scott-James-Hurley commented 3 years ago

Originally commented on UserOfficeProject/stfc-user-office-project#7:

It seems the two most popular ways of doing this are Oracle's heterogeneous connections and data warehousing.

Heterogeneous connections:

Allows data in non-Oracle database to be queried using SQL. Supported in the form of transparent gateways, which are vendor specific, or generic connectivity which uses ODBC or OLEDB to make the connections
Generic connectivity is typically more limited than that possible when using vendor specific gateways, but it is quick and simple to configure
Vendor specific gateways cost money, generic connectivity seems preferable
Queries should be automatically modified to work with Postgres, need to create process for converting queries to work with ESS's different database structure or could instead make views of tables

Data Warehouse:

Central repository of integrated data from one or more disparate sources
Time-consuming to make
Rigid
Could transform data into long format using staging area, no need to alter queries?
Could use data marts to separate categories
Could use three-tiered architecture
Could also create virtual data warehouse using views
- Faster, easier to set up
- Less performant

Scott-James-Hurley commented 3 years ago

The DB team has confirmed that Transparent Gateways isn't possible as a license would have to be purchased.

Scott-James-Hurley commented 3 years ago

The DB team has said they'd be willing to configure generic connectivity on a development server, possibly Orisa, to test functionality. After they're satisfied they can configure the ODBC on Tyche.

Scott-James-Hurley commented 3 years ago

Some useful links.

Generic Connectivity: https://docs.oracle.com/cd/A91202_01/901_doc/server.901/a88789/gencon.htm#1656 https://docs.oracle.com/cd/B19306_01/server.102/b14232/toc.htm https://docs.oracle.com/cd/A87860_01/doc/server.817/a76960/hs_genco.htm#173

Data Warehousing: https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/database-data-warehousing-guide.pdf https://www.geeksforgeeks.org/data-warehouse-architecture/ https://www.javatpoint.com/data-warehouse-architecture#:~:text=A%20data%20warehouse%20architecture%20is,characterized%20by%20standard%20vital%20components. https://www.guru99.com/data-warehousing.html#2

Scott-James-Hurley commented 3 years ago

I think generic connectivity is preferable to data warehousing. It's a lot quicker and easier to set up and seems to fulfil all the requirements of postgres and Oracle cross-database querying the team has, judging by the proof of concept.

Scott-James-Hurley commented 3 years ago

The council has spoken: data warehouses are out; generic connectivity is in.

The gang prefers the idea of creating views of each of the tables held in the postgres database over creating a public link to it or having other users create database links to it. It more closely resembles how a single user manages each database and how permissions are already granted between users. Finer grain control of permission is also possible with views. It will require more setup for each new table and the views will have to be maintained when changes are made to tables, however.

Scott-James-Hurley commented 3 years ago

This is the design that was decided on. It's the most straightforward and fits with the existing design.

An Oracle database sits in front of the postgres DB, communicating with it via generic connectivity. The reporting tool queries the Oracle database like it would any other. Other databases query the postgres one via views created by its Oracle user.

Scott-James-Hurley commented 3 years ago

Alex raised a concern that views might not provide up-to-date information. I tested this by updating the database and seeing if the view reflected this change, which it did.

Scott-James-Hurley commented 3 years ago

The following are the steps to create an example PostgreSQL database in the localDB and connect Oracle to it with heterogeneous connectivity.

First download the latest psqlodbc msi version from here: https://www.postgresql.org/ftp/odbc/versions/msi/

Then download the PostgreSQL installer, available here: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

The example database used is available here: https://www.postgresqltutorial.com/postgresql-sample-database/

Next, transfer these files to the localDB docker image using the following commands: docker cp <path-to-psqlodbc>\psqlodbc_x64.msi <localDB-image-id>:/psqodbc_x64.msi

docker cp <path-to-psql>\postgresql-<version>-windows-x64.exe <localDB-image-id>:/postgresql-<version>-windows-x64.exe

docker cp <path-to-example-db>\dvdrental.tar <localDB-image-id>:/dvdrental.tar

docker cp <path-to-listener>\listener.ora <localDB-image-id>:C:\oracle\dbhomeXE\network\admin\listener.ora

docker cp <path-to-initmypostgresqldb>\initmypostgresqldb.ora <localDB-image-id>:C:\oracle\dbhomeXE\hs\admin\initmypostgresqldb.ora

docker cp <path-to-tnsnames>\tnsnames.ora <localDB-image-id>:C:\oracle\dbhomeXE\network\admin\tnsnames.ora

Now start the localDB container and open its command prompt.

Install PostgreSQL using the following command: postgresql-<version>-windows-x64.exe --unattendedmodeui minimal --mode unattended --superpassword "password" --servicename "postgreSQL" --servicepassword "password" --serverport 5432

Next, install the psqlodbc by running this command: msiexec /i "C:\psqodbc_x64.msi"

Then add psql to the PATH environment variable: setx /M PATH "%PATH%;C:\Program Files\PostgreSQL\13\bin"

Launch the PSQL tool by logging in as the postgres user: psql -U postgres

And provide the following password when prompted: password

The next step is to create a psql database with name "dvdrental": CREATE DATABASE dvdrental;

Exit the PSQL tool using: exit

Navigate to the bin folder of the PSQL installation directory: cd C:\Program Files\PostgreSQL\13\bin

To load the data into the database, use the pg_restore tool: pg_restore -U postgres -d dvdrental C:\dvdrental.tar

Create an ODBC DSN for Postgres: powershell -command "Add-OdbcDsn -Name 'PostgreSQL' -DriverName 'PostgreSQL Unicode' -DsnType 'System' -SetPropertyValue @('Server=localhost', 'Port=5432', 'Database=dvdrental', 'Password=password', 'SSL Mode=disable', 'Description=test', 'User Name=postgres', 'Data Source=PostgreSQL')"

Enter the SQL Plus tool using this command: sqlplus system/pa55w0rdTolocalDB@PDB

Alternatively, this is functionally equivalent to the above: sqlplus system/pa55w0rdTolocalDB@//localdbhostpc/xepdb

Enter the following command to enable the creation of new users (NOTE: this is not recommended to do in production as it may invalidate Oracle support contract): alter session set "_ORACLE_SCRIPT"=true;

To create a user for the Postgres database and grant it permissions enter the following commands:

GRANT CREATE SESSION TO DVD_RENTAL;
GRANT CREATE DATABASE LINK TO DVD_RENTAL;
GRANT UNLIMITED TABLESPACE TO DVD_RENTAL;
GRANT CREATE TABLE TO DVD_RENTAL;
GRANT CREATE SYNONYM TO DVD_RENTAL;
GRANT CREATE TABLESPACE TO DVD_RENTAL;
GRANT CREATE SEQUENCE TO DVD_RENTAL;
GRANT CREATE VIEW TO DVD_RENTAL;
GRANT CREATE PROCEDURE TO DVD_RENTAL;
GRANT CREATE TRIGGER TO DVD_RENTAL;
GRANT CREATE TYPE TO DVD_RENTAL;

Exit the sqlplus tool using: exit

Next, sign in to the FACILITY_ERAS user with this command: sqlplus FACILITY_ERAS/pa55w0rdTolocalDB@PDB

And grant the new user permission to view this user's tables: GRANT SELECT ON FACILITY_USER TO DVD_RENTAL;

Then: exit

Sign in to the user you've just created using: sqlplus DVD_RENTAL/pa55w0rdTolocalDB@PDB

Link the database to Postgres: CREATE DATABASE LINK dvdrental CONNECT TO "postgres" IDENTIFIED BY "password" USING 'mypostgresqldb';

Create a view of the actor table: create view ACTOR as select * from "actor"@dvdrental;

Finally, grant the FACILITY_ERAS user permission to select data in the view: grant select on ACTOR to FACILITY_ERAS;

You can now query the tables in the database like so: SELECT * FROM "actor"@dvdrental;

Scott-James-Hurley commented 3 years ago

tnsnames.ora:

# tnsnames.ora Network Configuration File: C:\oracle\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localdbhostpc)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localdbhostpc)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xepdb)
    )
  )

mypostgresqldb =
  (DESCRIPTION =
    (ADDRESS=(PROTOCOL=tcp)(HOST=localdbhostpc)(PORT=1521))
    (CONNECT_DATA =
      (SID = mypostgresqldb)
    )
    (HS=OK)
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localdbhostpc)(PORT = 1521))

Scott-James-Hurley commented 3 years ago

listener.ora:

# listener.ora Network Configuration File: C:\oracle\dbhomeXE\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = mypostgresqldb)
      (ORACLE_HOME=C:\oracle\dbhomeXE)
      (PROGRAM = dg4odbc)
    )
  )

DEFAULT_SERVICE_LISTENER = XE

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localdbhostpc)(PORT = 1521))
  )

Scott-James-Hurley commented 3 years ago

initmypostgresqldb.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = PostgreSQL
HS_FDS_TRACE_LEVEL = ON

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

Scott-James-Hurley commented 3 years ago

TODO

Create a proof of concept using the local ESS postgres database instead of the example one currently used.
Create a Dockerfile for a localDB with heterogeneous connectivity

Scott-James-Hurley commented 3 years ago

Branch of the reporting tool with postgres queries: https://github.com/isisbusapps/reporting-tool/tree/76_hs_proof_of_concept

Scott-James-Hurley commented 3 years ago

The name of the branch of BISAppSettings with the connection string for the postgres db is: 76_hs_proof_of_concept

TickleThePanda commented 3 years ago

TODO

Create a proof of concept using the local ESS postgres database instead of the example one currently used.

Create a Dockerfile for a localDB with heterogeneous connectivity

I think https://github.com/UserOfficeProject/stfc-user-office-project/issues/77 can cover these two.

UserOfficeProject / issue-tracker

Create a proof of concept for cross database queries between Oracle and Postres #76