`aidp-alh` — Oracle AI Lakehouse / ADW / ATP via Spark JDBC

aidp-alh — Oracle AI Lakehouse / ADW / ATP via Spark JDBC

This skill covers the entire Oracle Autonomous Database family — ALH, ADW, ATP — because they are all Oracle 26ai under the hood. Same JDBC driver (oracle.jdbc.OracleDriver), same URL pattern (jdbc:oracle:thin:@tcps://...:1522/...), same TNS service-name shape (_high/_medium/_low), same wallet flow, same IAM DB-Token flow.

If the user names ATP or ADW specifically, just use this skill — substitute the env-var prefix (ATP_* / ADW_*) for ALH_* and proceed identically.

When to use

• User wants to read or write an ALH, ADW, or ATP table from an AIDP notebook.
• User mentions: "ALH", "AI Lakehouse", "ADW", "Autonomous Data Warehouse", "ATP", "Autonomous Transaction Processing", "Autonomous Database", "26ai lakehouse", "lakehouse external catalog".
• User has an Autonomous DB wallet, an Autonomous DB connection string, or an externally-cataloged Autonomous DB in AIDP.

When NOT to use

• For ExaCS / on-prem Oracle → use aidp-exacs.

Prerequisites in the AIDP notebook

1. The Oracle JDBC driver (ojdbc11.jar or ojdbc8.jar) must be on the cluster classpath. AIDP tpcds cluster ships it; if not, attach via Cluster → Libraries.
2. Add the helper package to sys.path (one-time, paste at the top of your notebook):

   import sys
   sys.path.insert(0, "/Workspace/Shared/oracle_ai_data_platform_connectors/scripts")
   

   (Adjust the path to wherever you've uploaded this plugin's scripts/ dir, or pip-install the package once it's published.)
3. Have one of: an ALH wallet ZIP (Option A), DB-token compartment OCID + API key (Option B), or admin OCI config (Option C).

Background: ALH is plain Oracle 26ai under the hood. The oracle.jdbc.OracleDriver, the URL pattern (jdbc:oracle:thin:@tcps://...:1522/...), and the TNS service-name shape (_high/_medium/_low) all transfer 1:1 from ATP. If you've used ATP from AIDP before, this looks identical.

Auth: pick one

Option A — Wallet (mTLS, recommended default)

import os
from oracle_ai_data_platform_connectors.auth import write_wallet_to_tmp
from oracle_ai_data_platform_connectors.jdbc import (
    build_oracle_jdbc_url, spark_jdbc_options_wallet,
)

# Write wallet to /tmp (NEVER /Workspace — FUSE breaks the JDBC driver's reads)
tns_admin = write_wallet_to_tmp(
    wallet="/path/to/alh-wallet.zip",      # or pass bytes from OCI Vault
    target_dir="/tmp/wallet/alh",
)

url = build_oracle_jdbc_url(
    tns_alias=os.environ["ALH_TNS_SERVICE"],   # e.g. "alh_high"
    tns_admin=tns_admin,
)

opts = spark_jdbc_options_wallet(
    url=url,
    user=os.environ["ALH_USER"],
    password=os.environ["ALH_PASSWORD"],
)
df = spark.read.format("jdbc").options(**opts).option("dbtable", "MY_SCHEMA.MY_TABLE").load()
df.show(5)

Option B — IAM DB-Token (validated against ATP via IMFA IoT, 3/3 successful)

import os
from oracle_ai_data_platform_connectors.auth import generate_db_token
from oracle_ai_data_platform_connectors.auth.dbtoken import refresh_on_executors
from oracle_ai_data_platform_connectors.jdbc import (
    build_oracle_jdbc_url, spark_jdbc_options_dbtoken,
)

token_dir = generate_db_token(
    compartment_ocid=os.environ["ALH_COMPARTMENT_OCID"],
    target_dir="/tmp/dbcred_alh",
)

url = build_oracle_jdbc_url(
    tns_alias=os.environ["ALH_TNS_SERVICE"],
    tns_admin=os.environ.get("ALH_WALLET_PATH", "/tmp/wallet/alh"),
)
opts = spark_jdbc_options_dbtoken(url=url, token_dir=token_dir)
df = spark.read.format("jdbc").options(**opts).option("dbtable", "MY_TABLE").load()

# For long-running jobs (>25 min), wrap your partition logic with refresh_on_executors:
refresh = refresh_on_executors(spark, os.environ["ALH_COMPARTMENT_OCID"], "/tmp/dbcred_alh")
result = df.rdd.mapPartitions(lambda part: refresh(part)).toDF(df.schema)

Option C — API Key + inline OCI config (catalog-sync side only)

The catalog-sync metadata harvest from ALH into the AIDP external catalog uses OCI control-plane APIs, not JDBC. For that side use:

from oracle_ai_data_platform_connectors.auth import from_inline_pem
config = from_inline_pem(
    user_ocid=os.environ["OCI_USER_OCID"],
    tenancy_ocid=os.environ["OCI_TENANCY_OCID"],
    fingerprint=os.environ["OCI_FINGERPRINT"],
    private_key_pem=os.environ["OCI_PRIVATE_KEY_PEM"],
    region=os.environ["OCI_REGION"],
)
# pass `config` to oci.<service>Client(config=config) — never write the PEM to disk

Run the query

Standard Spark JDBC. For large reads, partition the read:

df = (
    spark.read.format("jdbc").options(**opts)
        .option("dbtable", "(SELECT id, x, y FROM big_table) t")
        .option("partitionColumn", "id")
        .option("lowerBound", "1")
        .option("upperBound", "10000000")
        .option("numPartitions", "16")
        .load()
)

For writeback (overwrite only — external catalogs don't support DDL):

(df.write.format("jdbc").options(**opts)
   .option("dbtable", "MY_SCHEMA.TARGET_TABLE")
   .mode("overwrite")
   .save())

Gotchas

• Wallet must live under /tmp. /Workspace is FUSE-mounted; the JDBC driver process can't read it reliably (Errno 107).
• os.chmod is a no-op on FUSE. The helper uses os.open(..., O_WRONLY|O_CREAT, 0o666) so the JDBC driver process (different UID) can read the wallet/token.
• oracle.jdbc.timezoneAsRegion=false — set in helper defaults; avoids the well-known TZ region warning.
• External catalog refresh uses CREATE EXTERNAL CATALOG ... OPTIONS('wallet.content'=base64, 'user.name'=..., 'password'=..., 'wallet.password'=...). Same DB credentials as Option A.
• TNS service name — confirm via the wallet's tnsnames.ora. Not always alh_high.
• Instance Principal / Resource Principal are blocked in AIDP notebooks today (IMDS unreachable, RP tokens not provided). Don't try InstancePrincipalsSecurityTokenSigner().

References

• Helpers: scripts/oracle_ai_data_platform_connectors/jdbc/oracle.py
• Wallet helper: scripts/oracle_ai_data_platform_connectors/auth/wallet.py
• AIDP notebook auth limits: Claude context/AIDP/AIDP Context/AIDP/aidp-notebook-authentication.md