topic Re: Lakebase Data API in Data Engineering

Lakebase Data API

Jack17 — Thu, 12 Nov 2026 02:23:27 GMT

We need to bring the json data from external API and need to load it into lakebase API tables.
Please suggest in connecting this.

Re: Lakebase Data API

Divya_Pathak — Thu, 12 Nov 2026 05:54:27 GMT

Hi,
You can ingest JSON data from an external REST API into Databricks Lakebase (PostgreSQL) using a Databricks notebook.
I would suggest the following approach:
Extract data: Use Python's request library to connect to the external API and retrieve the JSON response. Configure the required authentication (API key, OAuth, etc.).
Transform data: Parse the JSON response and map the fields to your target Lakebase table schema.
Load into Lakebase: You can use the Lakebase Data API, which supports inserting records through HTTP POST requests, or connect directly using a PostgreSQL driver such as psycopg.
Automate ingestion: Schedule the notebook using Databricks Workflows. For larger datasets, consider pagination, batching, incremental loads, and error handling.

Suggested flow:
External REST API → Databricks Notebook (Python) → JSON Processing → Lakebase PostgreSQL Table

If you are using Lakebase Autoscaling, the Data API is a convenient option. Make sure it is enabled and the authenticated identity has the required table permissions.

Reference:
https://docs.databricks.com/aws/en/oltp/projects/data-api

Hope this helps!

Re: Lakebase Data API

DoTA — Thu, 12 Nov 2026 13:12:45 GMT

Adding to the notebook approach above, two things to plan for. The Data API wants an OAuth bearer token on every request, and those tokens are short lived (about an hour per the docs), so a scheduled job should fetch a fresh one at the start of each run instead of storing one. I also couldn't find any documented payload or rate limit for POST inserts, so for a large JSON load I'd send it in batches and test with your real volume first. If that turns out slow, a normal Postgres driver with batched inserts is the fallback. Either way, the identity the job runs as needs INSERT on the target table, and a service principal is easier to manage for that than your own user.

Re: Lakebase Data API

Louis_Frolio — Fri, 13 Nov 2026 19:16:29 GMT

Hello Jack17, I took a look at both internal and external documentation and here is what I found.

Divya_Pathak's flow (pull with requests, flatten, write to Lakebase, schedule it as a job) is the right skeleton, and DoTA's cautions are the ones that bite: the OAuth token expires after an hour, there's no documented payload or rate limit on Data API POSTs, and a service principal is the identity to run under. One question decides the rest of the design, and the post doesn't say: where will the code that pulls from the external API run?

If it runs outside Databricks (a small service, a serverless function, or the source system pushing to you), the Data API fits. It's PostgREST-compatible, so POST a JSON array to /public/<table> and you get one row per element in a single request. Add the header Prefer: resolution=merge-duplicates (optionally with on_conflict=<key_column>) and re-pulling overlapping data becomes insert-or-update instead of a duplicate-key error. The API URL is on the API tab in the Lakebase App; the token comes from the OAuth flow on the authentication page, not from that tab. Setup gotchas from the docs: it needs an Autoscaling project; don't use the project owner identity, because authenticator can't assume it; create the role with SELECT databricks_create_role(...) in the SQL editor rather than the Roles & Databases UI, or GRANT "<identity>" TO authenticator fails with SQLSTATE 42501; and click Refresh schema cache after you create or alter the table.

If it runs as a Databricks notebook or job, skip the HTTP hop and use a Postgres driver. Mint the same token with the SDK, connect with psycopg over SSL, and use executemany or COPY. If the JSON is nested, store the raw payload in a jsonb column and flatten it with SQL later:

import psycopg, requests
from databricks.sdk import WorkspaceClient
from psycopg.types.json import Jsonb

records = requests.get("https://api.example.com/items", timeout=60).json()
cred = WorkspaceClient().postgres.generate_database_credential(
    endpoint="projects/<project>/branches/<branch>/endpoints/<endpoint>")

with psycopg.connect(host="<host>", dbname="databricks_postgres",
                     user="<identity>", password=cred.token, sslmode="require") as conn:
    conn.cursor().executemany(
        """INSERT INTO public.api_data (id, payload) VALUES (%s, %s)
           ON CONFLICT (id) DO UPDATE SET payload = EXCLUDED.payload""",
        [(r["id"], Jsonb(r)) for r in records])

Generate a fresh credential each time you open a connection, keep the service principal secret in a Databricks secret scope, and give that principal INSERT on the target table.

If the JSON also belongs in the lakehouse, there's a third route: write it to a Delta table first, then create a synced table from Unity Catalog into your Lakebase project. The Postgres copy is read-only, which is fine if the app only reads it, and you keep the Delta history.

Short version: Data API when the writer lives outside Databricks, a Postgres driver when it lives inside, synced tables when the data needs to exist in both places anyway.

References:

Lakebase Data API: https://docs.databricks.com/aws/en/oltp/projects/data-api
Lakebase authentication and token rotation: https://docs.databricks.com/aws/en/oltp/projects/authentication
Connect an external app with the SDK (service principal plus psycopg): https://docs.databricks.com/aws/en/oltp/projects/external-apps-connect
Synced tables for Autoscaling projects: https://docs.databricks.com/aws/en/oltp/projects/sync-tables
PostgREST API reference (bulk insert, upsert, Prefer headers): https://postgrest.org/en/stable/references/api.html

Regards,
Louis.

Re: Lakebase Data API

suranga — Fri, 13 Nov 2026 21:33:24 GMT

Hi @Jack17,

One small addition to the service principal advice above, because it makes testing a lot easier. Since 12 November, OAuth on-behalf-of for service principals is generally available, so you can test the Data API locally as the same service principal your job will run as, without creating an OAuth secret for it:

databricks auth login --host https://your-workspace-url
# in the browser, pick the service principal from the dropdown and authorize
databricks auth token --host https://your-workspace-url

You need the Service Principal Manager role on that service principal (you get it automatically if you created it). Use the token as the Bearer token for your test POSTs. If the inserts work locally with it, you know the Postgres role and grants for the service principal are right before you schedule anything, and the scheduled job then runs as that same service principal.

Cheers,
Suranga