I am creating a PySpark 3.4.1 application for development in docker with Python 3.11.5, It should be able to connect to multiple types of databases throught JDBC conections. I am testing the connection with a local Oracle DB that I set up using docker in another container. However, I am getting the following error:
Py4JJavaError: An error occurred while calling o316.load.
: java.sql.SQLException: ORA-12541: Cannot connect. No listener at host 127.0.0.1 port 1521. (CONNECTION_ID=ZHRvV1iVRICqrdGeoIq7BQ==)
When I run (using jar: "ojdbc11.jar"):
connection_opts = {
"driver": "oracle.jdbc.driver.OracleDriver",
"url": "jdbc:oracle:thin:@127.0.0.1:1521/FREEPDB1",
"dbtable": "select * from xtable",
"user": "my_db_admin",
"password": "20pwd23",
}
df = spark.read.format("jdbc").options(**connection_opts).load()
The docker-compose.yml file is as follows:
version: "3.3"
services:
spark-master:
image: my_pyspark_image:latest
tty: true
stdin_open: true
ports:
- "9090:8080"
- "7077:7077"
oracle-localdb:
# Creation reference: https://hub.docker.com/r/gvenzl/oracle-free
image: gvenzl/oracle-free:slim
shm_size: 1g
ports:
- '1521:1521'
environment:
ORACLE_RANDOM_PASSWORD: true
APP_USER: my_db_admin
APP_USER_PASSWORD: 20pwd23
volumes:
- type: volume
source: pyspark_oracle-volume
target: /opt/oracle/oradata
volumes:
pyspark_oracle-volume:
# external: true
When I run lsnrctl status:
LSNRCTL for Linux: Version 23.0.0.0.0 - Developer-Release on 09-SEP-2023 18:49:38
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_FREE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 23.0.0.0.0 - Developer-Release
Start Date 09-SEP-2023 18:47:38
Uptime 0 days 0 hr. 1 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service FREE
Listener Parameter File /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/6b3c2441425c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_FREE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Services Summary...
Service "FREE" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
Service "FREEXDB" has 1 instance(s).
Instance "FREE", status READY, has 0 handler(s) for this service...
Service "fb99f7d127aa0bafe0536402000a43b5" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
Service "freepdb1" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
The command completed successfully
The listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_FREE))
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = FREE
The tnsnames.ora:
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREE)
)
)
FREEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREEPDB1)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_FREE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
I already tried the same connection with other custom created databases and with sys users. Also, I tried creating the container with multiple ports but no luck. Finally, I do not know if this will help but, I tried this and no luck either.
I tried making the connection with the oracledb python library:
import oracledb
connection = oracledb.connect(user="my_db_admin", password='20pwd23',
host="127.0.0.1", port=1521, service_name="freepdb1")
But I get this:
OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=M88go017TW5iuuV35FqWhw==).
[Errno 111] Connection refused
Could somebody explain me what I'm missing, how to solve it and if possible how to better understand the .ora files
I want to start with a question:
Are you running oracle database in your 'spark-master' container?
Because, that is how you have configured your database connection by specifying localhost/127.0.0.1
localhost is local to the container. There is no database running in your 'spark-master' container. It is running as a service/container alongside your 'spark-master' container. When utilizing docker compose, service discovery is happening behind the scenes and you can access them by name.
To contact the oracle database service, you provide the name oracle-localdb
Now, this should work for you:
import oracledb
connection = oracledb.connect(user="my_db_admin", password='20pwd23',
host="oracle-localdb", port=1521, service_name="freepdb1")
Best of luck!