aidp-alh — Oracle AI Lakehouse / ADW / ATP via AIDP aidataplatform

This skill covers the entire Oracle Autonomous Database family using the official AIDP ingestion connectors. Use ORACLE_ALH for Oracle AI Lakehouse / ADW-style connections and ORACLE_ATP when the user specifically wants ATP.

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 wallet content/path, a TNS alias, DB username/password, or an existing AIDP external catalog id.

When NOT to use

• For ExaCS → use aidp-exacs.
• For non-Autonomous Oracle Database → use aidp-oracle-db.

Prerequisites in the AIDP notebook

1. AIDP cluster with the built-in aidataplatform Spark format handler.
2. One of: base64 wallet content, wallet zip at a Workspace/Volume path, an existing external catalog id, or IAM DB-token requirements for the raw JDBC exception path.
3. Env vars / OCI Vault secrets:
   • ALH_WALLET_CONTENT or ALH_WALLET_PATH
   • ALH_TNS, ALH_USER, ALH_PASSWORD
   • ALH_SCHEMA, ALH_TABLE
   • optional ALH_CATALOG_ID

Read (inline connector options)

import os
from oracle_ai_data_platform_connectors.aidataplatform import (
    AIDP_FORMAT, aidataplatform_options,
)

opts = aidataplatform_options(
    type=os.environ.get("ALH_CONNECTOR_TYPE", "ORACLE_ALH"),  # use ORACLE_ATP for ATP
    user=os.environ["ALH_USER"],
    password=os.environ["ALH_PASSWORD"],
    schema=os.environ["ALH_SCHEMA"],
    table=os.environ["ALH_TABLE"],
    extra={
        "wallet.content": os.environ["ALH_WALLET_CONTENT"],
        "tns": os.environ["ALH_TNS"],
    },
)
df = spark.read.format(AIDP_FORMAT).options(**opts).load()
df.show()

Read using wallet.path

opts = aidataplatform_options(
    type="ORACLE_ALH",
    user=os.environ["ALH_USER"],
    password=os.environ["ALH_PASSWORD"],
    schema=os.environ["ALH_SCHEMA"],
    table=os.environ["ALH_TABLE"],
    extra={
        "wallet.path": os.environ["ALH_WALLET_PATH"],  # /Workspace/... or /Volumes/...
        "tns": os.environ["ALH_TNS"],
    },
)
df = spark.read.format(AIDP_FORMAT).options(**opts).load()

Write (inline connector options)

opts["table"] = os.environ["ALH_TARGET_TABLE"]
opts["write.mode"] = os.environ.get("ALH_WRITE_MODE", "APPEND")  # CREATE | APPEND | OVERWRITE | MERGE
df.write.format(AIDP_FORMAT).options(**opts).save()

Use an existing external catalog (catalog.id)

catalog_opts = aidataplatform_options(
    type="ORACLE_ALH",
    schema=os.environ["ALH_SCHEMA"],
    table=os.environ["ALH_TABLE"],
    extra={"catalog.id": os.environ["ALH_CATALOG_ID"]},
)
df = spark.read.format(AIDP_FORMAT).options(**catalog_opts).load()

df.write.format(AIDP_FORMAT).options(
    **{
        "catalog.id": os.environ["ALH_CATALOG_ID"],
        "schema": os.environ["ALH_SCHEMA"],
        "table": os.environ["ALH_TARGET_TABLE"],
        "write.mode": "APPEND",
    }
).save()

For external-catalog table access, three-part names are also supported:

df = spark.table(f"{os.environ['ALH_CATALOG_ID']}.{os.environ['ALH_SCHEMA']}.{os.environ['ALH_TABLE']}")
df.write.mode("append").insertInto(
    f"{os.environ['ALH_CATALOG_ID']}.{os.environ['ALH_SCHEMA']}.{os.environ['ALH_TARGET_TABLE']}"
)

Pushdown

pushdown_opts = aidataplatform_options(
    type="ORACLE_ALH",
    user=os.environ["ALH_USER"],
    password=os.environ["ALH_PASSWORD"],
    extra={
        "wallet.content": os.environ["ALH_WALLET_CONTENT"],
        "tns": os.environ["ALH_TNS"],
        "pushdown.sql": "SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 10",
    },
)
df = spark.read.format(AIDP_FORMAT).options(**pushdown_opts).load()

IAM DB-token exception (raw Spark JDBC)

AIDP's built-in aidataplatform connector does not support IAM DB-token auth yet. If the user explicitly needs DB-token, use raw Spark JDBC with the helper package:

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"],
    tns_admin=os.environ["ALH_WALLET_DIR"],  # extracted wallet directory under /tmp
)
opts = spark_jdbc_options_dbtoken(url=url, token_dir=token_dir)

df = (
    spark.read.format("jdbc")
    .options(**opts)
    .option("dbtable", f"{os.environ['ALH_SCHEMA']}.{os.environ['ALH_TABLE']}")
    .load()
)
df.show()

# For long-running jobs, refresh the DB token on executors before partition work.
refresh = refresh_on_executors(spark, os.environ["ALH_COMPARTMENT_OCID"], token_dir)

Gotchas

• Prefer catalog.id when the connection already exists in AIDP; it keeps host, wallet, and credentials out of generated notebooks.
• For source-side filtering, prefer pushdown.sql for precise Oracle SQL semantics, or DataFrame .select(...).filter(...).limit(...) when automatic pushdown is enough.
• Use wallet.content or wallet.path with the built-in connector; do not materialize wallets into /tmp unless you are intentionally falling back to raw Spark JDBC.
• IAM DB-token is the main intentional raw JDBC fallback because aidataplatform does not support it yet.
• ORACLE_ALH and ORACLE_ATP are separate connector type literals. Use ORACLE_ATP when adapting the ATP sample exactly.
• Instance Principal / Resource Principal are blocked in AIDP notebooks today (IMDS unreachable, RP tokens not provided). Do not try InstancePrincipalsSecurityTokenSigner().

References

• Helper: scripts/oracle_ai_data_platform_connectors/aidataplatform.py
• Official ALH sample: data-engineering/ingestion/Read_Write_Oracle_Ecosystem_Connectors/Autonomous_AI_Lakehouse.ipynb
• Official ATP sample: data-engineering/ingestion/Read_Write_Oracle_Ecosystem_Connectors/Autonomous_Transaction_Processing.ipynb
• AIDP notebook auth limits: Claude context/AIDP/AIDP Context/AIDP/aidp-notebook-authentication.md