You can create an import into NetSuite Analytics Warehouse (NSAW) in either of these ways:
-
as part of a flow while designing or building your flow (Tools > Flow builder > Add destination app)
– or –
-
as a standalone resource that you can later attach to a flow (Resources > Imports > + Create import)
When you create your import, configure the general and required settings. If required, configure any other settings for your import.
To configure the general import settings,
-
Choose either of the following navigation paths,
-
In Flow builder (Tools > Flow builder ), click the plus sign () next to Destinations & Lookups. The Create destination / lookup page opens.
-
In the Imports page Resources > Imports, click + Create import. The Create import page opens.
-
-
Based on the path you choose, enter the required details. (While all the general settings are listed below, some of them are shown after you provide the initial details and click Create flow step or Next.)
-
In Application, select the application to which you want to import data, that is, NetSuite Analytics Warehouse (NSAW).
-
In What would you like to do?, select Import records into destination application.
-
In Describe use case to find similar flow steps, enter details if you want to find and reuse an existing step.
-
In Connection, select a previously created NetSuite Analytics Warehouse (NSAW) connection from the drop-down list. It saves time if you've already created your connection and only have to select it. However, you can create a connection if you haven't done so already.
-
In Name or Name your import, enter a meaningful name based on the purpose, function, or any other aspect.
-
In Description, provide additional details for your import to provide other users clarity or context on the import.
-
Note
When importing records, the default for One to many is No. If you've got a scenario where you want to use One to many, that is, you have a single record that internally needs to create multiple records, see Create a one to many import including nested arrays
NetSuite Analytics Warehouse (NSAW) uses standard SQL queries to import and modify data. If your flow requires multiple SQL queries, you must create one import for each query. You can't use multiple SQL queries on a single import.
Now, configure the required import settings
Note
The CLOB (Character Large Object) datatype has a maximum limit of 255 characters.
-
Under How would you like the fields to be imported? for Choose type , select any of the following options based on your requirements.
Note
You should not use special characters, such as
. @ $
or the like in your destination table name.Setting
Description
Use optimized bulk load (recommended for larger imports)
This optimized bulk load option is ideal for large data volumes as it aggregates data before loading, reducing the need for numerous batch inserts. Additionally, integrator.io automatically generates insert or merge queries based on the destination table and primary keys, allowing you to update existing rows seamlessly without the need for a separate deduplication flow.
Destination table: Select the destination table into which the data must be inserted in either of these ways:
-
By validated table name: Begin typing the table name in the Destination table field, and any table that already exists in your Snowflake environment will display in a list filtered by the text you enter.
-
By referencing a dynamic lookup with a handlebars expression that identifies the destination table: Use a handlebars expression to reference the lookup using the following format:
{{lookup.<LookupName>}}
Example:
{{lookup.OrdersLookup}}
Primary keys: Select the primary keys so that your data can be matched based on these fields and then merged in the destination table. If your table already has primary keys, they will be automatically shown in this setting. You can select one or more fields to designate as primary keys. These fields uniquely identify each record in your table.
Tip
-
If you leave the Primary keys setting blank, an insert query is created.
-
If you provide values in the Primary keys setting, a merge query is created. (After you create your import, map these values (fields) that you've provided in your Primary keys setting.)
-
Mapping un-stringified JSON to columns is not supported. Stay tuned for this support in one of the next releases.
With this option, based on the query, after the data processing is completed, it is pushed as a single request to NSAW.
Use batch insert SQL query
The batch insert data option is helpful for moderate data volumes. integrator.io builds the insert query for you automatically for each batch of records.
Destination table: Select the destination table into which the data must be inserted in either of these ways:
-
By validated table name: Begin typing the table name in the Destination table field, and any table that already exists in your Snowflake environment will display in a list filtered by the text you enter.
-
By referencing a dynamic lookup with a handlebars expression that identifies the destination table: Use a handlebars expression to reference the lookup using the following format:
{{lookup.<LookupName>}}
Example:
{{lookup.OrdersLookup}}
The default number of records bound in each batch is 100, but you can use the Batch size setting in the Advanced section to tune your imports as needed.
Use SQL query once per record
Execute a SQL query once for each record. You can write your SQL command in the SQL query text box. Click Edit () to the right of the text box to open the SQL Query builder AFE. In the query, if you're using dates, you must provide the data type explicitly. For example,
INSERT INTO REST_API_TEST_1 (ID, NAME, AGE, ADDRESS, SALARY, EXPORTED, JOINING) values ({{record.ID}}, {{record.NAME}}, {{record.AGE}}, {{record.ADDRESS}}, {{record.SALARY}}, {{record.EXPORTED}}, TO_DATE({{record.JOINING}},'YYYY-MM-DD"T"HH24:MI:SS"Z"'))
Use SQL query once per page of records
Execute a SQL query once per page of records. You can write your SQL command in the SQL query text box. Click Edit () to the right of the text field to open the SQL Query builder AFE.
-
-
Click Preview to check the import. If the SQL query is not executed successfully, revise the query.
-
Click Save.
When an import is saved,
-
if you've created it in a flow, the import is added to your flow in Flow builder.
-
if you've created it as a resource, then it is added under Resources > Imports.
Optional sections/settings |
Instructions |
---|---|
Mock response |
|
Mock response |
|
Advanced |
|
Batch |
The Batch setting is shown only for a batch insert. BatchSize indicates the number of records that will be imported in one request. The default value is 100. |
Concurrency ID lock template |
See Build concurrency ID lock template setting. |
Data URI template |
See Data URI template setting. |
Invoke |
Copy the URL if you want to invoke this resource via an HTTP request. |
Comments
Please sign in to leave a comment.