aidp-alh

# `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`](../aidp-exacs/SKILL.md). - For non-Autonomous Oracle Database → use [`aidp-oracle-db`](../aidp-oracle-db/SKILL.md). ## 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) ```python 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` ```python 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) ```python 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`) ```python 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: ```python 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 ```python 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: ```python 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](../../scripts/oracle_ai_data_platform_connectors/aidataplatform.py) - Official ALH sample: [data-engineering/ingestion/Read_Write_Oracle_Ecosystem_Connectors/Autonomous_AI_Lakehouse.ipynb](../../../../../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](../../../../../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`