aidp-postgresql

# `aidp-postgresql` — PostgreSQL via AIDP `aidataplatform` ## When to use - User wants to read or write a PostgreSQL database from an AIDP notebook. - Mentioned: "PostgreSQL", "Postgres", "psql". ## When NOT to use - For MySQL / HeatWave → [`aidp-mysql`](../aidp-mysql/SKILL.md). - For SQL Server → [`aidp-sqlserver`](../aidp-sqlserver/SKILL.md). - For arbitrary JDBC-only DBs → [`aidp-jdbc-custom`](../aidp-jdbc-custom/SKILL.md). ## Read ### Option A: AIDP `aidataplatform` format (default) Use the official AIDP PostgreSQL connector sample first. ```python import os from oracle_ai_data_platform_connectors.aidataplatform import ( AIDP_FORMAT, aidataplatform_options, ) opts = aidataplatform_options( type="POSTGRESQL", host=os.environ["PG_HOST"], port=int(os.environ.get("PG_PORT", "5432")), user=os.environ["PG_USER"], password=os.environ["PG_PASSWORD"], schema=os.environ.get("PG_SCHEMA", "public"), table=os.environ["PG_TABLE"], ) df = spark.read.format(AIDP_FORMAT).options(**opts).load() df.show() ``` ## Write ```python opts = aidataplatform_options( type="POSTGRESQL", host=os.environ["PG_HOST"], port=int(os.environ.get("PG_PORT", "5432")), user=os.environ["PG_USER"], password=os.environ["PG_PASSWORD"], schema=os.environ.get("PG_SCHEMA", "public"), table=os.environ["PG_TARGET_TABLE"], extra={"write.mode": "CREATE"}, # CREATE | APPEND | OVERWRITE ) df.write.format(AIDP_FORMAT).options(**opts).save() ``` ## Pushdown SQL ```python opts = aidataplatform_options( type="POSTGRESQL", host=os.environ["PG_HOST"], port=int(os.environ.get("PG_PORT", "5432")), user=os.environ["PG_USER"], password=os.environ["PG_PASSWORD"], extra={ "pushdown.sql": ( f"SELECT * FROM {os.environ.get('PG_SCHEMA', 'public')}.{os.environ['PG_TABLE']} " "WHERE updated_at >= DATE '2024-01-01'" ), }, ) df = spark.read.format(AIDP_FORMAT).options(**opts).load() df.show() ``` ### Option B: Native Spark JDBC fallback for SSL-required endpoints Use this only when the target PostgreSQL service rejects non-TLS connections and the AIDP connector cannot pass the required SSL option. The observed failure mode is `[PostgreSQL]connection is insecure (try using sslmode=require)`. In that case, use Spark native JDBC with URL-embedded `sslmode=require`. The cluster may not have `org.postgresql.Driver` pre-installed, so runtime-load it the same way `aidp-jdbc-custom` does. ```python import os, urllib.request from py4j.java_gateway import java_import JAR_PATH = "/tmp/postgresql-42.7.4.jar" if not os.path.exists(JAR_PATH): urllib.request.urlretrieve( "https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.4/postgresql-42.7.4.jar", JAR_PATH, ) # Register driver on driver JVM gw = spark._sc._gateway url = spark._jvm.java.io.File(JAR_PATH).toURI().toURL() arr = gw.new_array(spark._jvm.java.net.URL, 1); arr[0] = url ucl = spark._jvm.java.net.URLClassLoader(arr, spark._jvm.java.lang.ClassLoader.getSystemClassLoader()) spark._jvm.java.lang.Thread.currentThread().setContextClassLoader(ucl) DriverCls = spark._jvm.java.lang.Class.forName("org.postgresql.Driver", True, ucl) spark._jvm.java.sql.DriverManager.registerDriver(DriverCls.newInstance()) spark._jsc.addJar(JAR_PATH) # distribute to executors # Now read — note sslmode=require URL-embedded JDBC_URL = ( f"jdbc:postgresql://{os.environ['PG_HOST']}:{os.environ.get('PG_PORT','5432')}" f"/{os.environ['PG_DB']}?sslmode=require" ) df = (spark.read.format("jdbc") .option("url", JDBC_URL) .option("driver", "org.postgresql.Driver") .option("user", os.environ["PG_USER"]) .option("password", os.environ["PG_PASSWORD"]) .option("dbtable", f"{os.environ.get('PG_SCHEMA','public')}.{os.environ['PG_TABLE']}") .load()) df.show(5) ``` ## Gotchas - **Use `aidataplatform` first.** It is the official sample path and has its own bundled driver. - **SSL fallback** — if the AIDP `POSTGRESQL` handler cannot pass SSL options and the server requires `sslmode=require`, use Option B with native Spark JDBC. Verified live 2026-04-27 against Neon serverless 17.8. - **No bundled driver for native Spark JDBC** — if you use Option B, runtime-load the PostgreSQL jar and register it via `URLClassLoader` + `DriverManager.registerDriver` + `spark._jsc.addJar`. - **Network reachability** — Postgres must be reachable from the AIDP cluster's NAT egress IP. Public-internet endpoints (Neon, Supabase, RDS public) work via the cluster's NAT path. Self-hosted Postgres in user-managed VCNs typically does NOT work — the cluster's pod CIDR has no route to user VCNs without explicit VCN peering. Smoke-test with a Python socket: `socket.create_connection((host, 5432), timeout=8)`. - **`schema`** is the Postgres logical schema (e.g. `public`), not the database name. The database name is a separate `PG_DB` env var that goes into the JDBC URL. - **Write modes** — `CREATE` (fail if exists), `APPEND`, `OVERWRITE`. Default is `CREATE`. ## References - Helper: [scripts/oracle_ai_data_platform_connectors/aidataplatform.py](../../scripts/oracle_ai_data_platform_connectors/aidataplatform.py) - Official sample: [data-engineering/ingestion/Read_Write_External_Ecosystem_Connectors/PostgreSQL.ipynb](../../../../../data-engineering/ingestion/Read_Write_External_Ecosystem_Connectors/PostgreSQL.ipynb)