# Execute SQL Query — Incorta (OAuth)

> Executes a custom Spark SQL query against an Incorta tenant via the Advanced SQL Interface (Advanced SQLi), which exposes the tenant's physical schemas and verified business views as Spark SQL tables. Use this for ad hoc reporting or data…

- Key: `incorta_oauth-execute-sql-query`
- Type: Action (Write)
- Version: 0.0.1
- App: Incorta (OAuth) (`incorta_oauth`) — https://pipedream.com/apps/incorta-oauth.md
- This page (HTML): https://pipedream.com/apps/incorta-oauth/actions/execute-sql-query
- Hints: open-world
- Source: https://github.com/PipedreamHQ/pipedream/blob/master/components/incorta_oauth/actions/execute-sql-query/execute-sql-query.mjs

## Description

Executes a custom Spark SQL query against an Incorta tenant via the **Advanced SQL Interface** (Advanced SQLi), which exposes the tenant's physical schemas and verified business views as Spark SQL tables. Use this for ad hoc reporting or data exploration. Queries that resolve workflow references to bound parameters are not supported; inline literal values in the SQL text instead. It is a read/query interface (not a general-purpose transactional database) — expect `SELECT` statements with Spark SQL syntax: joins, aggregates, `GROUP BY`, window functions, and CTEs all work. Use **List Schemas**, **List Tables**, and **List Columns** first if you don't already know the available schema/table/column names, and reference tables as `schema.table`, e.g. `pipedream_store.customer`. Always include a `LIMIT` (and select only the columns you need) for exploratory queries: results are capped at `Max Rows` (default 500), and the response reports `truncated: true` when more rows existed than were returned, so a truncated result is partial (the total row count is not known; run a `COUNT(*)` if you need it). Narrow the query with `LIMIT`, filters, or aggregates rather than relying on the cap. To read the next page of a large result, either use `LIMIT n OFFSET m` with a stable `ORDER BY`, or keyset paging (`ORDER BY <unique column>` and `WHERE <column> > <last value seen>` with `LIMIT`), e.g. `SELECT * FROM sales.orders WHERE order_id > 1000 ORDER BY order_id LIMIT 100`; keyset paging is faster on large tables. Example: `SELECT region, SUM(amount) AS total_sales FROM sales.sales_fact GROUP BY region ORDER BY total_sales DESC LIMIT 50` returns `{rows: [{region, total_sales}, ...], rowCount, truncated: false}`, one row per region. Authenticates with the connected Incorta account's OAuth access token, so no separate SQL password is needed, and discovers the cluster's Advanced SQLi connection settings automatically from the account's base URL; the tenant must have Advanced SQLi enabled (not available on Azure deployments). The first query after a quiet period can take several seconds while the connection initializes. [See the documentation](https://docs.incorta.com/latest/references-advanced-sqli)

## Props

| Prop | Type | Required | Description |
|---|---|---|---|
| `sql` | `sql` | Yes | The Spark SQL query to execute against Incorta via Advanced SQLi, e.g. SELECT * FROM sales.sales_fact LIMIT 50. See our docs to learn more about working with SQL in Pipedream |
| `maxRows` | `integer` | No | Maximum number of rows to return, e.g. 100. The action stops reading once more rows than this have arrived and reports truncated. It does not change or limit the query itself, so add a LIMIT to the SQL as well. |

## Run it

**MCP**

```ts
import { Client } from "@modelcontextprotocol/sdk/client/index.js"
import { StreamableHTTPClientTransport } from "@modelcontextprotocol/sdk/client/streamableHttp.js"
import { PipedreamClient } from "@pipedream/sdk"

const pd = new PipedreamClient({
  projectId: process.env.PIPEDREAM_PROJECT_ID!,
  clientId: process.env.PIPEDREAM_CLIENT_ID!,
  clientSecret: process.env.PIPEDREAM_CLIENT_SECRET!,
  projectEnvironment: "production",
})

const accessToken = await pd.rawAccessToken

const transport = new StreamableHTTPClientTransport(
  new URL("https://remote.mcp.pipedream.net/v3"),
  {
    requestInit: {
      headers: {
        Authorization: `Bearer ${accessToken}`,
        "x-pd-project-id": process.env.PIPEDREAM_PROJECT_ID!,
        "x-pd-environment": "production",
        "x-pd-external-user-id": "{external_user_id}", // any stable ID for this user in your system
        "x-pd-app-slug": "incorta_oauth",
      },
    },
  },
)

const mcp = new Client({ name: "my-agent", version: "1.0.0" })
await mcp.connect(transport)

const { tools } = await mcp.listTools()

// listTools() hands your model this tool's input schema, so it can
// fill the arguments itself:
const result = await mcp.callTool({
  name: "incorta_oauth-execute-sql-query",
  arguments: {
    sql: "SQL Query",
    maxRows: 10,
  },
})
```

**TypeScript**

```ts
import { PipedreamClient } from "@pipedream/sdk"

const pd = new PipedreamClient({
  projectId: process.env.PIPEDREAM_PROJECT_ID!,
  clientId: process.env.PIPEDREAM_CLIENT_ID!,
  clientSecret: process.env.PIPEDREAM_CLIENT_SECRET!,
  projectEnvironment: "production",
})

const result = await pd.actions.run({
  id: "incorta_oauth-execute-sql-query",
  externalUserId: "{external_user_id}", // any stable ID for this user in your system
  configuredProps: {
    incorta_oauth: { authProvisionId: "apn_xxxxxxx" },
    sql: "SQL Query",
    maxRows: 10,
  },
})

console.log(result)
```

**cURL**

```bash
curl -X POST https://api.pipedream.com/v1/connect/{project_id}/actions/run \
  -H "Content-Type: application/json" \
  -H "X-PD-Environment: production" \
  -H "Authorization: Bearer {access_token}" \
  -d '{
    "external_user_id": "{external_user_id}",
    "id": "incorta_oauth-execute-sql-query",
    "configured_props": {
      "incorta_oauth": { "authProvisionId": "apn_xxxxxxx" },
      "sql": "SQL Query",
      "maxRows": 10
    }
  }'
```

---

- App: https://pipedream.com/apps/incorta-oauth.md · All apps: https://pipedream.com/apps
