SEPURI-SAI-KRISHNA opened a new issue, #13189:
URL: https://github.com/apache/seatunnel/issues/13189

   ### Search before asking
   
   - [X] I had searched in the 
[feature](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22Feature%22)
 and found no similar feature requirement. I checked `dev`, every open PR, and 
all issues and PRs since June 2025 for NetSuite and SuiteQL: there is no 
module, plugin mapping, docs, claim or PR. #10753 lists the row as "Todo".
   
   ### Description
   
   ## Overview
   
   Design proposal for a new **NetSuite source** (Connector V2) for the 
"NetSuite SuiteAnalytics" row in #10753. It reads NetSuite records with SuiteQL 
through NetSuite's REST web services, as a `connector-http-netsuite` module on 
`connector-http-base`, like the Mailchimp, Typeform and Intercom sources. 
SuiteQL covers every record type (customers, transactions, items, custom 
records) with one connector, so I would not build one reader per record type. 
The row is named after SuiteAnalytics; this design reads through SuiteQL over 
REST, not through SuiteAnalytics Connect (ODBC/JDBC).
   
   I have no NetSuite account, and NetSuite has no free developer account. 
Every API fact below comes from Oracle's documentation, with links. The points 
the documentation does not settle are listed in section 9, and the design 
handles them by failing loudly instead of guessing.
   
   ## 1. v1 scope
   
   - **Bounded only**, one table per job, one split. Streaming is rejected.
   - **Endpoint:** `POST 
https://<account>.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=<n>&offset=<n>`,
 with the header `Prefer: transient` and the body `{"q": "<SuiteQL>"}` 
([Executing SuiteQL Queries Through 
REST](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_157909186990.html)).
   - **Host:** built from `account_id` ([account-specific 
URLs](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_1546938065.html):
 "if your account ID is 123456, your account-specific domain for REST web 
services is 123456.suitetalk.api.netsuite.com"). An optional `base_url` 
overrides it, which the tests also use.
   
   ## 2. Reading more than 100,000 rows
   
   This is the main design point. Oracle states: "Using SuiteQL queries, you 
can return a maximum of 100,000 results." Offset paging over one query 
therefore stops at 100,000 rows. A connector that only follows `hasMore` would 
load a partial table with no error.
   
   v1 has two modes:
   
   - **Table mode (default):** options `table`, `key_column` (default `id`), 
and an optional `filter` (a SuiteQL condition). The columns come from the 
declared `schema`. Each page is a new query:
     `SELECT <columns> FROM <table> WHERE (<filter>) AND <key_column> > <last 
key> ORDER BY <key_column>`, with `limit=<page_size>` and `offset=0`.
     No query ever reaches the cap, and `ORDER BY` makes the order explicit. 
NetSuite 2026.2 changed the default sort of generic transaction queries, so the 
connector cannot rely on a default order. The last key is a number the 
connector parsed, so it is written into the query as a number. Bound `params` 
arrived only in NetSuite 2026.2, so v1 does not depend on them. The key must be 
a unique integer, which the default `id` (the internal ID) is; with a repeated 
key, rows could be skipped at a page boundary. The reader fails if a key is not 
an integer, or if a page's keys are not strictly increasing.
   - **Query mode:** option `query`, a full SuiteQL statement, read with 
`limit`/`offset` paging. If the response reports `totalResults` above 100,000, 
the job fails before reading and points to table mode. It never stops silently 
at the cap.
   
   `page_size` defaults to 1000, the page size NetSuite's [collection 
paging](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_156414087576.html)
 uses.
   
   ## 3. Authentication
   
   - **OAuth 2.0 client credentials (machine to machine) only** ([token 
endpoint](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_162755359851.html),
 [JWT 
structure](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_162790605110.html)).
 The connector signs a JWT (header `typ`, `alg`, `kid` = certificate ID; claims 
`iss` = client ID, `scope` = `rest_webservices`, `aud` = the token URL, `iat`, 
`exp`) and posts it as `client_assertion` with `grant_type=client_credentials`.
   - Options: `client_id`, `certificate_id`, `private_key` (PKCS#8 PEM), 
`algorithm` (`PS256` default, or `ES256`).
   - The access token is valid for 60 minutes and there is no refresh token. 
The connector requests a new one shortly before expiry, and once on a `401`.
   - **NetSuite setup, in the docs:** the REST Web Services and OAuth 2.0 
features enabled; an integration record with "Client Credentials (Machine to 
Machine) Grant" and the REST Web Services scope; a certificate mapped to an 
entity, role and application ([OAuth 2.0 Client Credentials 
Setup](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_162686838198.html));
 and a role with REST Web Services, Log in using OAuth 2.0 Access Tokens and 
SuiteAnalytics Workbook ([Roles and Permission Considerations for 
APIs](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/article_0320025211.html)).
 A `401` or `403` from NetSuite fails the job with a message that points to 
this setup.
   - **No TBA (OAuth 1.0).** Oracle's [authentication 
overview](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/chapter_4369651411.html)
 says "you should use OAuth 2.0 for REST web services authentication" and "TBA 
support ends in NetSuite 2028.2."
   - **No new dependency for signing.** The JDK has both algorithms 
(`RSASSA-PSS` and `SHA256withECDSA`; ES256 needs the DER signature converted to 
the raw 64-byte form). I checked this with a standalone probe on JDK 11: 20 
PS256 and 60 ES256 tokens signed with JDK classes only all verified with PyJWT, 
and a token with a changed signature was rejected.
   
   ## 4. Values and schema
   
   The documented responses return every column value as a JSON string, for 
example `"creditlimit": "8000"` and `"id": "1725"`. The user declares the 
schema, and the reader converts each value:
   
   - Numbers and decimals are parsed strictly; a value that does not parse 
fails the job with the table, column and value.
   - **Booleans:** NetSuite checkbox values are not shown in Oracle's examples. 
The shared JSON format would read `"T"` as `false` (`Boolean.parseBoolean`), 
which would be silent data loss, so the reader converts booleans itself. It 
accepts `T`/`F` and `true`/`false`, and fails on anything else.
   - **Dates:** Oracle's examples show no date column, so I do not rely on any 
default format. In table mode the connector selects a `DATE` column as 
`TO_CHAR(<col>, 'YYYY-MM-DD') AS <col>` and a `TIMESTAMP` column as 
`TO_CHAR(<col>, 'YYYY-MM-DD HH24:MI:SS') AS <col>`. In query mode the docs ask 
users to do the same. The reader accepts only those two formats and fails on 
anything else.
   - **Column names:** Oracle's [SuiteQL 
limitations](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_156257796125.html)
 say that the casing of field names in results "may be inconsistent and can 
change after a NetSuite release". The reader matches columns 
case-insensitively. A column missing from an item is read as NULL.
   
   ## 5. Secret boundary
   
   The private key is only in the `private_key` option, the JWT only in the 
token request, and the access token only in the `Authorization` header. None of 
them are logged, put in error messages, or stored in checkpoint state. Errors 
include the HTTP status and NetSuite's error body, never request headers. A 
unit test checks that no log line or exception message contains the key or 
token.
   
   ## 6. Retries, limits and memory
   
   - NetSuite limits concurrent requests per account. [Concurrency 
governance](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_1500275531.html)
 gives a base of 5 for development and partner accounts. v1 sends one request 
at a time.
   - The reader retries `429`, `5xx` and I/O errors with bounded exponential 
backoff, using the existing retry options, then fails with a clear error. The 
status NetSuite returns for a concurrency rejection on this endpoint is not 
documented (section 9).
   - Requests time out after the configured timeout. Oracle's limit for one 
request is 15 minutes.
   - One page at a time is held in memory.
   
   ## 7. Checkpoint and delivery
   
   Single split, nothing persisted in v1. After a failure the read starts again 
from the beginning, so rows can be read twice, and every row carries its key 
for an upsert or deduplication downstream. No exactly-once claim. Persisting 
the last key, so a restored job continues where it stopped, and reading key 
ranges in parallel would be a later slice, if maintainers want them.
   
   ## 8. Testing without an account
   
   - **Unit tests with `MockWebServer`:** a token endpoint that verifies the 
JWT signature, claims and form fields against a test key pair; keyset paging 
across several pages; a terminal empty page; the 100,000 guard in query mode; 
non-increasing keys; value conversion (including `T`/`F`, bad numbers and 
missing columns); case-insensitive columns; token renewal on `401`; retries on 
`429` and `5xx`; no secret in logs or errors.
   - **E2E:** a SeaTunnel job on every engine against a mock HTTP server 
serving NetSuite-shaped responses, with an injected `429`. No account or 
network access needed.
   - The PR will separate what the mocks prove from what needs a real account, 
and I would be glad if someone with a NetSuite sandbox could run the example 
config once.
   
   ## 9. Not settled by the documentation
   
   1. Sandbox host names (account IDs like `123456_SB1`). `base_url` covers 
them in any case.
   2. How checkbox and date values look in SuiteQL REST results, and whether a 
NULL column is omitted or sent as `null`. Section 4 handles each case strictly.
   3. Whether `TO_CHAR` with those two formats works in SuiteQL as in Oracle 
SQL, and which time zone a formatted timestamp is in. v1 maps it to `TIMESTAMP` 
without a zone, and the docs will say so.
   4. The HTTP status for a concurrency rejection on the SuiteQL endpoint. 
RESTlets return `400` with `SSS_REQUEST_LIMIT_EXCEEDED`. If the endpoint uses 
`400` too, the reader should retry that error code and nothing else.
   
   ## 10. Dependencies, license and deliverables
   
   No new runtime dependency. Test scope only: `mockwebserver`, as in the other 
HTTP sources. Deliverables: the module and its parent pom entry, 
`plugin-mapping.properties` (`seatunnel.source.NetSuite = 
connector-http-netsuite`), `plugin_config` and `seatunnel-dist/pom.xml`, EN and 
ZH docs with an example, unit tests and the E2E.
   
   ## Out of scope for v1
   
   TBA, SOAP, RESTlets, SuiteAnalytics Connect (ODBC/JDBC), saved searches, 
sinks, incremental state between runs, resume from the last key, parallel 
splits, and streaming.
   
   ### Usage Scenario
   
   Finance and operations teams keep their ERP data in NetSuite and want 
customers, transactions, items or custom records in a warehouse or database for 
reporting, without CSV exports. NetSuite's REST paging stops at 100,000 rows 
per query, which is often too few for transaction tables, so the source has to 
page past it.
   
   ### Related issues
   
   Tracking issue #10753 (NetSuite SuiteAnalytics row). Same pattern as the 
Typeform (#12822, #12844) and Intercom (#12823, #12831) sources.
   
   ### Are you willing to submit a PR?
   
   - [X] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of 
Conduct](https://www.apache.org/foundation/policies/conduct)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to