# Infor Data Lake Compass SQL API Request Generator (Python and curl)

> Source: https://netray.co/tools/infor-data-lake-compass-sql-api-request-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your ION API base URL, tenant and SQL to get working Python and curl code for the Compass v2 job, status and paged result calls.

## Category

developer

## Erp

Infor ION

## Audience

- Integration developer
- Data engineer
- Infor OS administrator

## Tool

**Kind:** template

**Output:** code

**Language:** python

**Fields:**

#### ION API base URL (iu from the .ionapi file)

**Key:** iu

**Type:** text

**Default:** https://mingle-ionapi.inforcloudsuite.com

#### Tenant (ti from the .ionapi file)

**Key:** ti

**Type:** text

**Default:** ACME_PRD

#### Environment variable holding the bearer token

**Key:** token_var

**Type:** text

**Default:** ION_TOKEN

#### Compass SQL statement

**Key:** sql

**Type:** textarea

**Default:** SELECT * FROM YourDataObject LIMIT 100

**Help:** Use the data object name as shown in Data Lake Atlas or the Data Catalog.

#### Rows per result page (limit, max 100000)

**Key:** limit

**Type:** number

**Default:** 10000

#### Status poll interval (seconds)

**Key:** poll

**Type:** number

**Default:** 5

#### Result format (Accept header)

**Key:** accept

**Type:** select

**Options:**

- application/x-ndjson
- text/csv

**Default:** application/x-ndjson

**Blocks:**

#### Python: submit, poll and page a Compass v2 query

**Body:** # Requires: pip install requests
# Token: create an ION API backend service client and get a bearer token
# from the token endpoint in the .ionapi file (pu + ot), then export it as {{token_var}}.
import os
import time
import requests

BASE = "{{iu}}/{{ti}}/DATAFABRIC/compass/v2"
TOKEN = os.environ["{{token_var}}"]
PAGE_LIMIT = int("{{limit}}")
POLL_SECONDS = float("{{poll}}")
ACCEPT = "{{accept}}"

SQL = """{{sql}}"""

session = requests.Session()
session.headers.update({"Authorization": "Bearer " + TOKEN})

def call(method, url, **kwargs):
    """One HTTP call with backoff on 429 and a clear message on 404."""
    for attempt in range(6):
        resp = session.request(method, url, timeout=300, **kwargs)
        if resp.status_code == 429:
            # Per-tenant per-minute quota hit: back off and retry
            time.sleep(min(60, 2 ** attempt))
            continue
        if resp.status_code == 404:
            raise RuntimeError(
                "404 Query not found. Check the queryId, the tenant in the URL, that the "
                "same principal (client) submitted the job, that results have not expired, "
                "and the Accept header. Body: " + resp.text[:500]
            )
        resp.raise_for_status()
        return resp
    raise RuntimeError("Still rate limited (429) after retries: " + url)

# 1) Submit the SQL as a job (body is plain text)
submit = call(
    "POST",
    BASE + "/jobs/",
    data=SQL.encode("utf-8"),
    headers={"Content-Type": "text/plain"},
)
query_id = submit.json()["queryId"]
print("queryId:", query_id)

# 2) Poll status until the job ends
while True:
    st = call("GET", BASE + "/jobs/" + query_id + "/status/").json()
    status = st.get("status")
    if status == "FINISHED":
        row_count = st.get("rowCount")
        print("FINISHED, rowCount:", row_count)
        break
    if status in ("FAILED", "CANCELLED"):
        # For FAILED jobs the result call returns the error details
        detail = call("GET", BASE + "/jobs/" + query_id + "/result/", headers={"Accept": ACCEPT})
        raise RuntimeError(status + ": " + detail.text[:2000])
    time.sleep(POLL_SECONDS)

# 3) Fetch result pages with offset and limit
extension = ".csv" if ACCEPT == "text/csv" else ".ndjson"
out_path = "compass_" + query_id + extension
offset = 0
header = None
written = 0
with open(out_path, "w", encoding="utf-8", newline="") as out:
    while True:
        page = call(
            "GET",
            BASE + "/jobs/" + query_id + "/result/",
            params={"offset": offset, "limit": PAGE_LIMIT},
            headers={"Accept": ACCEPT},
        )
        lines = [ln for ln in page.text.splitlines() if ln.strip()]
        if ACCEPT == "text/csv" and lines:
            if header is None:
                header = lines[0]
                out.write(header + "\n")
            if lines[0] == header:
                lines = lines[1:]
        for ln in lines:
            out.write(ln + "\n")
        written += len(lines)
        offset += PAGE_LIMIT
        if len(lines) < PAGE_LIMIT:
            break
        if row_count is not None and offset >= int(row_count):
            break

print("Wrote", written, "rows to", out_path)

#### curl equivalent (bash, needs jq)

**Body:** BASE="{{iu}}/{{ti}}/DATAFABRIC/compass/v2"
TOKEN=$(printenv {{token_var}})

cat > query.sql <<'EOF'
{{sql}}
EOF

# 1) Submit
QUERY_ID=$(curl -s -X POST "$BASE/jobs/" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: text/plain" \
  --data-binary @query.sql | jq -r '.queryId')
echo "queryId: $QUERY_ID"

# 2) Poll status
while true; do
  STATUS=$(curl -s "$BASE/jobs/$QUERY_ID/status/" -H "Authorization: Bearer $TOKEN" | jq -r '.status')
  echo "status: $STATUS"
  case "$STATUS" in
    FINISHED) break ;;
    FAILED|CANCELLED) curl -s "$BASE/jobs/$QUERY_ID/result/" -H "Authorization: Bearer $TOKEN"; exit 1 ;;
  esac
  sleep {{poll}}
done

# 3) First result page (repeat with offset={{limit}}, 2x{{limit}} ... until a short page)
curl -s "$BASE/jobs/$QUERY_ID/result/?offset=0&limit={{limit}}" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Accept: {{accept}}" \
  -o page_0.out

#### Limits to design around

**Table:**

**Columns:**

- Item
- Documented behaviour

**Rows:**

**0:** Result page size

**1:** limit up to 100,000 rows per call, with a 10 MB cap per page

**0:** Result retention

**1:** Roughly 20 hours after the job reaches FINISHED

**0:** Who can read results

**1:** Only the tenant and principal that submitted the job

**0:** Rate limits

**1:** Per-tenant per-minute quotas apply to the jobs, status and result endpoints; a 429 means back off. Check the current values in the Data Fabric API documentation

**0:** Status values handled here

**1:** RUNNING, FINISHED, FAILED, CANCELLED (any other value is treated as still in progress)

**Notes:** Verify on your tenant before production use: open the Infor Data Fabric suite in API Gateway and compare the Compass v2 paths, verbs and parameters with this code (newer tenants expose Compass under the Data Fabric suite; older material may show it under the ION API suite). Page limits, quotas and retention come from the Infor OS Data Fabric documentation and can change between releases.

## Content

**Intro:** This generator writes Python and curl code for the Infor Data Lake Compass v2 query API: submit a SQL job, poll its status, then download the result in pages with offset and limit. It targets Infor OS tenants that call Compass through the Data Fabric suite of the ION API Gateway.

**Sections:**

#### How to use it

**Body:** Open your downloaded .ionapi file and copy the iu value (the ION API base URL) and ti value (the tenant). Get a bearer token for a backend service client from the token endpoint in the same file, export it into the environment variable you named, and paste your SQL. The SQL runs against Data Lake data objects, so use object names as they appear in Atlas or the Data Catalog. Pick NDJSON when you want one JSON object per row for loading into Python or a warehouse, or CSV for spreadsheets. Keep the page size well below 100,000 if rows are wide, because the 10 MB page cap applies first.

**Bullets:**

- Run a COUNT(*) version of the query first to see how many pages you will pull.
- Store the queryId: you can re-read pages for about 20 hours without re-running the SQL.
- Run the script under the same API client that submitted the job.

#### How the Compass v2 flow works

**Body:** Compass is asynchronous. The jobs call accepts the SQL as a text/plain body and returns a queryId straight away. The status call reports RUNNING until the engine ends with FINISHED, FAILED or CANCELLED; a FINISHED status includes the total rowCount. Only then do you call result. Version 2 differs from version 1 in that the result is returned in row sets: offset is a 0-based row position and limit is the page size, and both apply only to the result call. The script loops offset by the page size and stops on a short page or when it reaches rowCount. For a FAILED job, the result call returns the error text, which is usually a SQL or data object name problem.

**Bullets:**

- POST .../jobs/ submits, GET .../jobs/{queryId}/status/ polls, GET .../jobs/{queryId}/result/ pages.
- Content-Type text/plain is required on submit; a different type is rejected.
- Accept selects text/csv or application/x-ndjson for the result.

#### Errors you will actually see

**Body:** A 404 Query not found on status or result nearly always means one of four things: the queryId was mistyped or truncated, the tenant in the URL differs from the one that submitted, a different API client (principal) is asking, or the result expired after about 20 hours. A 429 means a per-tenant per-minute quota was hit, so batch work into fewer, larger queries rather than one query per record. A 401 means the token expired; tokens are short-lived, so long downloads should refresh the token between pages.

**Bullets:**

- Poll status every few seconds, not in a tight loop, to stay inside the status quota.
- Add WHERE clauses on partition or date fields to keep jobs short.

#### When Compass is the wrong tool

**Body:** Compass is built for analytical pulls and extracts from the Data Lake, not for transactional lookups. If you need one record in real time, call the source application API through ION API instead. If you need a recurring feed into another system, an ION data flow or Data Lake flow is usually easier to monitor than a scheduled script. For BI tools there is also a Compass JDBC driver, which handles paging for you. Use this generated script for one-off extracts, data science notebooks and lightweight scheduled jobs where you control the code.

**Cta:** If you want people to ask questions of your Infor Data Lake in plain English on infrastructure you control, we can build that layer on top of these same Compass calls.

**Faq:**

#### What is Infor Data Lake?

**Answer:** Infor Data Lake is the storage layer of Infor OS Data Fabric. ION and other publishers load data objects from Infor applications such as M3, LN and CloudSuite Industrial into it, and you query them with Compass SQL through the Compass UI, the JDBC driver or the Compass REST API shown here.

#### What is the difference between Compass v1 and v2?

**Answer:** Version 1 returns the whole result set in one result call. Version 2 returns it in pages, with offset and limit parameters on the result call, so large extracts do not need one huge response. In both versions the SQL is the body of the jobs call.

#### How do I get a token for the ION API Gateway?

**Answer:** In Infor OS, register an authorized app as a backend service in ION API, download its .ionapi credentials file, then request a token from the token endpoint built from the pu and ot values using the client and service account credentials in that file. Pass it as a Bearer token.

#### Why do I get 404 Query not found right after submitting?

**Answer:** Check that the status call uses exactly the returned queryId and the same tenant, and that the same API client makes both calls, because results are visible only to the submitting principal. If it worked earlier and fails now, the roughly 20 hour retention window may have passed.

## Search terms

infor data lake compass api, infor compass sql api v2, what is infor data lake, infor ion api examples, infor os api gateway, compass query result paging, infor data fabric compass python, infor ion dataflow

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json