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

# 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 ION_TOKEN.
import os
import time
import requests

BASE = "https://mingle-ionapi.inforcloudsuite.com/ACME_PRD/DATAFABRIC/compass/v2"
TOKEN = os.environ["ION_TOKEN"]
PAGE_LIMIT = int("10000")
POLL_SECONDS = float("5")
ACCEPT = "application/x-ndjson"

SQL = """SELECT * FROM YourDataObject LIMIT 100"""

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)

BASE="https://mingle-ionapi.inforcloudsuite.com/ACME_PRD/DATAFABRIC/compass/v2"
TOKEN=$(printenv ION_TOKEN)

cat > query.sql <<'EOF'
SELECT * FROM YourDataObject LIMIT 100
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 5
done

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