DataRobot Datastore SQL
Run SQL through a data connection the user already set up in DataRobot; the API calls it a datastore. DataRobot resolves the connection and its stored credential server-side, so no database secrets pass through the agent.
For a JDBC URL and credentials instead of a DataRobot connection, use datarobot-connectivity-jdbc-sql.
Prerequisites
- Auth: set both
DATAROBOT_ENDPOINT (ends in /api/v2) and DATAROBOT_API_TOKEN, or neither, in which case both come from ~/.config/datarobot/drconfig.yaml. If auth fails, invoke datarobot-setup.
- Python 3.9+, standard library only. Run the script by its path in this skill's folder:
python3 <skill-dir>/scripts/datastore_sql.py.
Script
datastore_sql.py <command> -h lists every option.
| Command | Purpose |
|---|
datastores [--name TEXT] | Every connection you can access. role is your access (OWNER, EDITOR, CONSUMER). sql is what DataRobot supports for the driver (read (fast) + write, read (fast), read (legacy), no SQL (file/object connector)); it is the write gate, not a grants check |
credentials --datastore-id ID | Your stored credentials for the connection, with the default marked |
routes --datastore-id ID | Whether your account can use the newer read and write routes, and whether this connection's driver can write. Sends no SQL |
schemas · tables [--schema S --like TEXT] · columns --table T | Discovery. --like matches part of the name, any case |
preview --table T [--schema S] | First rows of a table (default --max-rows 20) |
query --sql "…" | --sql-file F [--bind '[42, "a"]'] | One read-only statement (default --max-rows 100, max 10000). Binds fill ? placeholders in order |
plan --sql "…" | --sql-file F [--bind …] | DML/DDL plan and confirm token. Changes nothing |
execute <same arguments> --confirm TOKEN | Runs the approved plan |
Commands that act on one connection take --datastore-id and an optional --credential-id. Without --credential-id, the connection's default credential is used.
Output options:
--format table|csv|json: CSV sends the # notes to stderr.--show N: rows printed, default 20 for preview and query, 100 for listings.--width N: cell width.--out FILE: saves every fetched row (JSON for .json, else CSV); stdout then shows only a preview.
Exit codes: 0 ok · 1 usage · 2 API error, or a write whose outcome is unknown · 3 the confirm token no longer matches · 4 the database reported a write failure · 5 not possible here (route unavailable, or the connection cannot write).
Workflow
- [ ] 1. Find the connection: `datastores --name <text>`
- [ ] 2. Discover: `tables --schema <s> --like <text>`, `columns --table <t>`
- [ ] 3. Read: `preview`, or `query` with WHERE/LIMIT/aggregation
- [ ] 4. Write, only if the user asked: checks → `plan` → approval → `execute --confirm` → verify
- Several matches: prefer the exact name the user gave. When the user says "my", prefer a connection with role OWNER. If it is still ambiguous, ask.
- No match: run
datastores without --name. A no SQL connection is a file or object store and cannot be queried.
- Credentials: run
credentials to see which credential will be used, or when the user names another one.
- Names: Datasphere spaces and BigQuery datasets are schemas. In SQL, quote names that are mixed-case, start with a digit, or contain special characters. Use
"…" for Snowflake, Datasphere/HANA, Postgres, Trino and Oracle, and `…` for MySQL, BigQuery and Databricks. See references/connector-support.md.
- Wide tables: select the columns you need with
query, or save with --out and summarize the file.
- Empty discovery: discovery shows only objects the credential can access. Check the schema name, its case, and the grants.
Writes:
- Check write support. The connection's
sql must be read (fast) + write; plan refuses others (exit 5). If it isn't, stop: DataRobot cannot write there. Offer datarobot-connectivity-jdbc-sql with the database's own JDBC URL.
- Check the target and impact. Confirm the target exists and is a table, not a view (
tables, columns). If it doesn't, stop and ask the user. Measure the impact with a read: SELECT COUNT(*) FROM t WHERE <same condition> for UPDATE or DELETE; required columns and existing keys for INSERT.
- Plan.
plan --datastore-id <id> --sql "<sql>" prints WRITE PLAN (nothing has run) and a token. It only reads connection metadata. Show the plan to the user and ask for an explicit yes.
- Execute. Run
execute with the same arguments plus --confirm <token>. The token covers the connection, credential, SQL and binds. Any change means a new plan and a new approval (exit 3).
- Verify. No row count comes back, so check with
query for DML or tables/columns for DDL.
Several statements in one request:
- Snowflake runs them in one transaction.
- Databricks runs them without one, so earlier statements stay applied if a later one fails.
- Other connectors: send one statement per
execute.
Rules
- Never pass
--confirm until the user has approved that exact plan.
- Never retry a write after exit 2 until a read shows its effect: the outcome is unknown.
- Writes run with your credential for the connection, and anyone with access to it can write, even a CONSUMER. State the target and impact plainly.
query and preview run only reads (SELECT, WITH, VALUES, SHOW, DESCRIBE, EXPLAIN). The check cannot see side effects of functions a query calls, so prefer a read-only credential for exploration.- Keep results small: aggregate, filter, LIMIT. Save large results with
--out.
- Tell the user which route answered:
preview and query print a # route: line. verifySQL (legacy) results have no column types and stop at 999 rows.
Routes
The script picks the route and falls back by itself. Details are in references/api-reference.md.
Route (POST /externalDataStores/{id}/…/) | Use | If unavailable |
|---|
previewQuery/ | Read SQL, typed results, bind parameters | Falls back to verifySQL/ (≤999 rows, no types) |
preview/ | Table sample | Falls back to verifySQL/ with SELECT * FROM <table> |
executeUpdate/ | DML/DDL | No fallback. A 403 … (Enable the Future Public API) means writes are not enabled for the account: ask a DataRobot admin, or use the JDBC skill |
routes shows which of these the account can use. Discovery commands use separate metadata endpoints and print no # route: line.
Common errors
| Error | Fix |
|---|
422 Credentials were not provided… | Pass --credential-id or set a default. A wrong connection id causes the same error, so verify it with datastores |
403 You do not have the required permissions. Details: … | The database rejected the stored login or a privilege; fix the credential or the grants |
| 400 with a database message | A missing object, identifier case or quoting, or the dialect; check tables/columns |
408 Request timed out | Reads: a warehouse is still starting or the query is heavy (300 s limit); retry later or narrow the query. Writes: the outcome is unknown; check with query |
Full table: references/troubleshooting.md.
Using DataRobot SDK
The script calls REST directly. That avoids SDK client retries, which could run a write twice. In application code, datarobot>=3.19 wraps the same routes:
import datarobot as dr
store = dr.DataStore.get("<data-store-id>")
rows = store.preview_query("SELECT region, COUNT(*) AS n FROM sales.orders GROUP BY region", max_rows=100)
message = store.execute_update("UPDATE t SET flag = TRUE WHERE id = ?", bind_parameters=[42]) # "OK" on success
Raw REST, fallback logic, and SDK caveats are in references/api-reference.md.
References
references/api-reference.md: routes, bodies, responses, value formatting, limits, bind parameters, timeouts, discovery routes, app-code snippetsreferences/connector-support.md: capability and transactions by connector, dialects and quoting, DDL differences, discovery SQLreferences/troubleshooting.md: every known error, its cause, and the fix
Related skills
datarobot-connectivity-jdbc-sql: SQL through a caller-supplied JDBC URL; also writes where a DataRobot connection cannotdatarobot-setup: authentication and SDK setupdatarobot-data-preparation: register data as a DataRobot dataset for modeling