# Connecting to Oracle NetSuite
Oracle NetSuite is an all-in-one cloud business management solution that helps organizations operate more effectively by automating core processes and providing real-time visibility into operational and financial performance. With a single, integrated suite of applications for managing accounting, order processing, inventory management, production, supply chain and warehouse operations, Oracle NetSuite gives companies clear visibility into their data and tighter control over their businesses.
**Topics**
+ [AWS Glue support for Oracle NetSuite](oracle-netsuite-support.md)
+ [Policies containing the API operations for creating and using connections](oracle-netsuite-configuring-iam-permissions.md)
+ [Configuring Oracle NetSuite](oracle-netsuite-configuring.md)
+ [Configuring Oracle NetSuite connections](oracle-netsuite-configuring-connections.md)
+ [Reading from Oracle NetSuite entities](oracle-netsuite-reading-from-entities.md)
+ [Oracle NetSuite connection options](oracle-netsuite-connection-options.md)
+ [Limitations and notes for Oracle NetSuite connector](oracle-netsuite-connector-limitations.md)
# AWS Glue support for Oracle NetSuite
AWS Glue supports Oracle NetSuite as follows:
**Supported as a source?**
Yes. You can use AWS Glue ETL jobs to query data from Oracle NetSuite.
**Supported as a target?**
No.
**Supported Oracle NetSuite API versions**
The following Oracle NetSuite API versions are supported:
+ v1
For entity support per version specific, see Supported entities for Source.
# Policies containing the API operations for creating and using connections
The following sample policy describes the required AWS IAM permissions for creating and using connections. If you are creating a new role, create a policy that contains the following:
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"glue:ListConnectionTypes",
"glue:DescribeConnectionType",
"glue:RefreshOAuth2Tokens",
"glue:ListEntities",
"glue:DescribeEntity"
],
"Resource": "*"
}
]
}
```
------
If you don't want to use the above method, alternatively use the following managed IAM policies:
+ [AWSGlueServiceRole](https://console.aws.amazon.com/iam/home#policies/arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole) – Grants access to resources that various AWS Glue processes require to run on your behalf. These resources include AWS Glue, Amazon S3, IAM, CloudWatch Logs, and Amazon EC2. If you follow the naming convention for resources specified in this policy, AWS Glue processes have the required permissions. This policy is typically attached to roles specified when defining crawlers, jobs, and development endpoints.
+ [AWSGlueConsoleFullAccess](https://console.aws.amazon.com/iam/home#policies/arn:aws:iam::aws:policy/AWSGlueConsoleFullAccess) – Grants full access to AWS Glue resources when an identity that the policy is attached to uses the AWS Management Console. If you follow the naming convention for resources specified in this policy, users have full console capabilities. This policy is typically attached to users of the AWS Glue console.
# Configuring Oracle NetSuite
Before you can use AWS Glue to transfer data from Oracle NetSuite, you must meet these requirements:
## Minimum requirements
The following are minimum requirements:
+ You have a Oracle NetSuite account. For more information, see [Creating a Oracle NetSuite account](#oracle-netsuite-configuring-creating-oracle-netsuite-account).
+ Your Oracle NetSuite account is enabled for API access.
+ You have created an OAuth 2.0 API integration in your Oracle NetSuite developer account. This integration provides the client credentials that AWS Glue uses to access your data securely when it makes authenticated calls to your account. For more information, see [Creating a Oracle NetSuite client app and OAuth 2.0 credentials](#oracle-netsuite-configuring-creating-oracle-netsuite-client-app).
If you meet these requirements, you’re ready to connect AWS Glue to your Oracle NetSuite account.
## Creating a Oracle NetSuite account
Navigate to [Oracle NetSuite](https://www.netsuite.com/portal/home.shtml), and choose **Free Product Tour**. Fill in the required details to get a free product tour, through which you can contact a vendor. The process for procuring an account is as follows:
+ The procurement of a NetSuite account is done via a vendor, who provides a form/quote which has to be legally reviewed.
+ The account to be procured for Oracle NetSuite connector is of **Standard Cloud Service**.
+ This account is created by the vendor and temporary credentials are shared by them. You will receive a welcome mail from NetSuite with the details such as your username, and a link to set your password.
+ Use the **Set your password** link to set the password for the username provided by the vendor.
## Creating a Oracle NetSuite client app and OAuth 2.0 credentials
To get the Client ID and Client Secret you create a Oracle NetSuite client app:
1. Log into your NetSuite account through the [NetSuite customer login](https://system.netsuite.com/pages/customerlogin.jsp).
1. Choose **Setup** > **Company** > **Enable features**.
1. Navigate to the **SuiteCloud** section and select the **REST WEB SERVICES** checkbox under **SuiteTalk (Web Services)**.
1. Select the **OAUTH 2.0** checkbox under **Manage Authentication**. Click **Save**.
1. Go to **Setup** > **Integration** > **Manage Integrations** and choose **New** to create an OAuth2.0 application.
1. Enter a name of your choice and keep the **STATE** as Enabled.
1. If checked, uncheck the **TBA: AUTHORIZATION FLOW** and **TOKEN-BASED AUTHENTICATION** checkboxes displayed under **Token-based Authentication**.
1. Select the **AUTHORIZATION CODE GRANT** and **PUBLIC CLIENT** checkboxes under **OAuth 2.0**.
1. Under Auth, note the Client ID and Client Secret.
1. Enter a **REDIRECT URI**. For example, https://us-east-1.console.aws.amazon.com/gluestudio/oauth
1. Select the **REST WEB SERVICES** checkbox under **SCOPE**.
1. Select the **USER CREDENTIALS** checkbox under **User Credentials**. Choose **Save**.
1. Note the CONSUMER KEY/CLIENT ID and CONSUMER SECRET/CLIENT SECRET under **Client Credentials**. These values are displayed only once.
1. Create an ADMINISTRATOR role if needed by navigating to **User/Roles** > **Manage Roles** > **New**.
1. While creating a custom role, add full access under the **Permissions** tab for the following entities/functionalities:
+ "Deposit", "Items", "Item Fulfillment", "Make Journal Entry", "Purchase Order", "Subsidiaries", "Vendors", "Bills", "Vendor Return Authorization", "Track Time", "Customer Payment", "Custom Record Entries", "Custom Record Types", "REST Web Services", "OAuth 2.0 Authorized Applications Management", "Custom Entity Fields", "Log in using OAuth 2.0 Access Tokens".
For more information see [OAuth 2.0](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/chapter_157769826287.html) in the NetSuite Applications Suite documentation.
# Configuring Oracle NetSuite connections
Oracle NetSuite supports the AUTHORIZATION\$1CODE grant type for OAuth2. The grant type determines how AWS Glue communicates with Oracle NetSuite to request access to your data.
+ This grant type is considered "three-legged" OAuth as it relies on redirecting users to a third-party authorization server to authenticate the user. It is used when creating connections via the AWS Glue console. The user creating a connection may by default rely on an AWS Glue-owned connected app (AWS Glue managed client application) where they do not need to provide any OAuth-related information except for their Oracle NetSuite instance URL. The AWS Glue console will redirect the user to Oracle NetSuite where the user must log in and allow AWS Glue the requested permissions to access their Oracle NetSuite instance.
+ Users may still opt to create their own connected app in Oracle NetSuite and provide their own client id and client secret when creating connections through the AWS Glue console. In this scenario, they will still be redirected to Oracle NetSuite to login and authorize AWS Glue to access their resources.
+ This grant type results in a refresh token and access token. The access token is short lived, and may be refreshed automatically without user interaction using the refresh token.
+ For public Oracle NetSuite documentation on creating a connected app for Authorization Code OAuth flow, see [Public apps](https://developers.oracle-netsuite.com/docs/api/creating-an-app).
To configure a Oracle NetSuite connection:
1. In AWS Secrets Manager, create a secret with the following details:
1. For the customer managed connected app, the Secret should contain the connected app Consumer Secret with `USER_MANAGED_CLIENT_APPLICATION_CLIENT_SECRET` as key.
1. Note: You must create a secret for your connection in AWS Glue.
1. In AWS Glue Glue Studio, create a connection under **Data Connections** by following the steps below:
1. When selecting a **Connection type**, select Oracle NetSuite.
1. Provide the Oracle NetSuite environment.
1. Select the AWS IAM role which AWS Glue can assume and has permissions for following actions:
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"secretsmanager:DescribeSecret",
"secretsmanager:GetSecretValue",
"secretsmanager:PutSecretValue",
"ec2:CreateNetworkInterface",
"ec2:DescribeNetworkInterfaces",
"ec2:DeleteNetworkInterface"
],
"Resource": "*"
}
]
}
```
------
1. Select the `secretName` which you want to use for this connection in AWS Glue to put the tokens.
1. Select the network options if you want to use your network.
1. Grant the IAM role associated with your AWS Glue job permission to read `secretName`.
# Reading from Oracle NetSuite entities
**Prerequisite**
A Oracle NetSuite object you would like to read from. You will need the object name such as `deposit` or `timebill`. The following table shows the supported entities.
**Supported entities for source**:
| Entity | Can be filtered | Supports Order By | Supports Limit | Supports SELECT \$1 | Supports partitioning |
| --- | --- | --- | --- | --- | --- |
| Deposit | Yes | No | Yes | Yes | Yes |
| Description Item | Yes | No | Yes | Yes | Yes |
| Inventory Item | Yes | No | Yes | Yes | Yes |
| Item Fulfillment | Yes | No | Yes | Yes | Yes |
| Item Group | Yes | No | Yes | Yes | Yes |
| Journal Entry | Yes | No | Yes | Yes | Yes |
| Non-Inventory Purchase Item | Yes | No | Yes | Yes | Yes |
| Non-Inventory Resale Item | Yes | No | Yes | Yes | Yes |
| Non-Inventory Sale Item | Yes | No | Yes | Yes | Yes |
| Purchase Order | Yes | No | Yes | Yes | Yes |
| Subsidiary | Yes | No | Yes | Yes | Yes |
| Vendor | Yes | No | Yes | Yes | Yes |
| Vendor Bill | Yes | No | Yes | Yes | Yes |
| Vendor Return Authorization | Yes | No | Yes | Yes | Yes |
| Time Bill | Yes | No | Yes | Yes | Yes |
| Customer Payment | Yes | No | Yes | Yes | Yes |
| Fulfillment Request | Yes | No | Yes | Yes | Yes |
| Item | Yes | Yes | Yes | Yes | Yes |
| Transaction Line | Yes | Yes | Yes | Yes | Yes |
| Transaction Accounting Line | Yes | Yes | Yes | Yes | Yes |
| Custom Record Types (Dynamic) | Yes | Yes | Yes | Yes | Yes |
**Example**:
```
netsuiteerp_read = glueContext.create_dynamic_frame.from_options(
connection_type="netsuiteerp",
connection_options={
"connectionName": "connectionName",
"ENTITY_NAME": "deposit",
"API_VERSION": "v1"
}
)
```
**Oracle NetSuite entity and field details**:
Oracle NetSuite dynamically loads available fields under selected entity. Depending on the data type of the field, it supports the following filter operators.
| Field data type | Supported filter operators |
| --- | --- |
| String | LIKE, =, \$1= |
| Date | BETWEEN, =, <, <=, >, >= |
| DateTime | BETWEEN, <, <=, >, >= |
| Numeric | =, \$1=, <, <=, >, >= |
| Boolean | =, \$1= |
**Expected input format for Boolean values in Filter Expression**:
| Entity | Boolean "true" value format | Boolean "false" value format | Example |
| --- | --- | --- | --- |
| Item, Transaction Line, Transaction Accounting Line, and Custom Record Type entities | T or t | F or f | isinactive = "T" or isinactive = "t" |
| All other entities | true | false | isinactive = true |
## Partitioning queries
**Field-based partitioning**
The Oracle NetSuite connector has dynamic metadata so that supported fields for field based partitioning are chosen dynamically. Field based partitioning is supported on fields having the data type Integer, BigInteger, Date or DateTime.
You can provide the additional Spark options `PARTITION_FIELD`, `LOWER_BOUND`, `UPPER_BOUND`, and `NUM_PARTITIONS` if you want to utilize concurrency in Spark. With these parameters, the original query would be split into `NUM_PARTITIONS` number of sub-queries that can be executed by Spark tasks concurrently.
+ `PARTITION_FIELD`: the name of the field to be used to partition the query.
+ `LOWER_BOUND`: an **inclusive** lower bound value of the chosen partition field.
For the timestamp field, we accept the Spark timestamp format used in Spark SQL queries.
Examples of valid values:
```
"TIMESTAMP \"1707256978123\""
"TIMESTAMP \"1702600882\""
"TIMESTAMP '2024-02-06T22:00:00:00.000Z'"
"TIMESTAMP '2024-02-06T22:00:00:00Z'"
"TIMESTAMP '2024-02-06'"
```
+ `UPPER_BOUND`: an **exclusive** upper bound value of the chosen partition field.
+ `NUM_PARTITIONS`: the number of partitions.
Example:
```
netsuiteerp_read = glueContext.create_dynamic_frame.from_options(
connection_type="netsuiteerp",
connection_options={
"connectionName": "connectionName",
"ENTITY_NAME": "deposit",
"API_VERSION": "v1",
"PARTITION_FIELD": "id",
"LOWER_BOUND": "1",
"UPPER_BOUND": "10000",
"NUM_PARTITIONS": "10"
}
```
**Record-based partitioning**
You can provide the additional Spark option `NUM_PARTITIONS` if you want to utilize concurrency in Spark. With this parameter, the original query would be split into `NUM_PARTITIONS` number of sub-queries that can be executed by Spark tasks concurrently.
In record based partitioning, the total number of records present is queried from Oracle NetSuite API, and it is divided by the `NUM_PARTITIONS` number provided, the resulting number of records are then concurrently fetched by each sub-query.
+ `NUM_PARTITIONS`: the number of partitions.
Example:
```
netsuiteerp_read = glueContext.create_dynamic_frame.from_options(
connection_type="netsuiteerp",
connection_options={
"connectionName": "connectionName",
"ENTITY_NAME": "deposit",
"API_VERSION": "v1",
"NUM_PARTITIONS": "3"
}
```
# Oracle NetSuite connection options
The following are connection options for Oracle NetSuite:
+ `ENTITY_NAME`(String) - (Required) Used for Read. The name of the Oracle NetSuite entity. Example: deposit.
+ `API_VERSION`(String) - (Required) Used for Read. Oracle NetSuite Rest API version you want to use. The value will be v1, as Oracle NetSuite currently supports only version v1.
+ `SELECTED_FIELDS`(List) - Default: empty(SELECT \$1). Used for Read. Comma-separated list of columns you want to select for the selected entity.
+ `FILTER_PREDICATE`(String) - Default: empty. Used for Read. It should be in the Spark SQL format.
+ `QUERY`(String) - Default: empty. Used for Read. Full Spark SQL query.
+ `PARTITION_FIELD`(String) - Used for Read. Field to be used to partition the query (field-based partitioning).
+ `LOWER_BOUND`(String)- Used for Read. An inclusive lower bound value of the chosen partition field, used in field-based partitioning.
+ `UPPER_BOUND`(String) - Used for Read. An exclusive upper bound value of the chosen partition field, used in field-based partitioning.
+ `NUM_PARTITIONS`(Integer) - Default: 1. Used for Read. Number of partitions for read. Used in both field- and record- based partitioning.
+ `INSTANCEE_URL`(String) - A valid NetSuite instance URL with format https://\$1account-id\$1.suitetalk.api.netsuite.com.
# Limitations and notes for Oracle NetSuite connector
The following are limitations or notes for the Oracle NetSuite connector:
+ The values of the access\$1token and refresh\$1token parameters are in JSON Web Token (JWT) format. The access token is valid for 60 minutes whereas the refresh\$1token is valid for seven days.
+ During client ID and client secret generation, if you select "PUBLIC CLIENT" along with "AUTHORIZATION CODE GRANT", then the refresh token is only valid for three hours and is for one-time use only.
+ You can fetch at most 1,00,000 records using the connector. For more information, refer to [Executing SuiteQL Queries Through REST Web Services](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_157909186990.html).
+ Partitions are created such that each partition will fetch records in multiples of 1000, except possibly the last one which will fetch the remaining records.
+ For Item, Transaction Line and Transaction Accounting Line objects, the connector will not support a few operators due to following reasons:
+ Applying the `EQUAL_TO`, `NOT_EQUAL_TO` filter operators to fields of type Date gives unreliable results.
+ Applying the `LESS_THAN_OR_EQUAL_TO` filter operator to fields of type Date gives unreliable results and behaves similar to the `LESS_THAN` operator.
+ Applying the `GREATER_THAN` filter operator to fields of type Date= gives unreliable results and behaves similar to `GREATER_THAN_OR_EQUAL_TO` operator.
+ For Item, Transaction Line, Transaction Accounting Line, and Custom Record Type objects, boolean values come in the format T/F instead of the standard true/false. The connector maps the t/f values to true/false to ensure consistency in data.