User Guide : Avalanche Data Loading Templates : Loading NetSuite Data into Avalanche on Azure
 
Share this page                  
Loading NetSuite Data into Avalanche on Azure
The NetSuite_to_Avalanche_Azure template is used to load data from NetSuite tables to a table in an Actian Avalanche data warehouse running on Azure.
Using the macros, provide the following:
• NetSuite connection information
• Connection string to your Avalanche data warehouse
• Credentials for the database and Azure container
When you run a configuration with this template, it creates a new table if it does not exist or inserts data to an existing table of the same name if it exists.
Before using the Template to load files from Azure Storage to Avalanche, you must perform the following:
• Confirm that the Avalanche data warehouse is running on Azure
• Obtain the credentials for connecting to the Avalanche database (user name and password)
• If Integration Manager on DataCloud is used:
– Obtain the ODBC Connection String for your Avalanche data warehouse. You can obtain this information from Avalanche portal.
• If Integration Manager On-premise is used, do one of the following:
– Create ODBC DSN (as a System DSN) with the ODBC driver shipped with latest client runtime, which can be downloaded from https://esd.actian.com/ (select Product as Actian Avalanche, Release as Client Runtime, and Platform as Windows/Linux)
Setup ODBC DSN (as a System DSN) pointing to the Avalanche Azure instance
– In the connect string, replace the name of the driver based on the operating system:
For Windows, Driver=Actian CR
For Linux, configure a driver entry called Ingres in the odbcinst.ini file (if it is not already available).
To load data from NetSuite to an Avalanche database:
1. Select the NetSuite_to_Avalanche_Azure configuration.
2. Set the values for the following required macros.
Note:  You must provide values for these macros so that the Template has the necessary information to access your data files, Avalanche warehouse, and find or create your table.
You can encrypt sensitive macro values (such as passwords, secret keys, and tokens for token based authentication) from Integration Manager. For information about encrypting, see Enable Macro Encryption section in Integration Manager documentation available at docs.actian.com.
 
Macro Name
Description
$(AVALANCHE_CONNECT_STRING)
ODBC Connection string for connecting to Avalanche database. You can obtain this information from the Avalanche portal.
Note:  This is required only if you are running Integration Manager on DataCloud.
$(AVALANCHE_USERNAME), $(AVALANCHE_PASSWORD)
Credentials used to connect to the Avalanche database.
$(AVALANCHE_TABLE)
Name of the table in Avalanche, where the data will be written to.
$(NETSUITE_ACCOUNT)
NetSuite Account ID
$(NETSUITE_TABLE)
Name of the table in NetSuite.
$(NETSUITE_QUERY)
SQL Statement for fetching NetSuite table data.
Note:  Specify either NETSUITE_TABLE or NETSUITE_QUERY macro.
$(NETSUITE_EMAIL)
$(NETSUITE_PASSWORD)
Credentials to access the NetSuite account.
$(NETSUITE_APPLICATION_ID)
Application ID for accessing NetSuite account using Web service.
$(NETSUITE_TOKEN_ID)
Token ID
$(NETSUITE_TOKEN_SECRET)
Token Secret
$(NETSUITE_CONSUMER_KEY)
Consumer Key
$(NETSUITE_CONSUMER_SECRET)
Consumer Secret
Note:  You can specify either password-based or token-based credentials.
Password-based credentials are $(NETSUITE_EMAIL) and $(NETSUITE_PASSWORD).
Token-based credentials are $(NETSUITE_TOKEN_ID), $(NETSUITE_TOKEN_SECRET), $(NETSUITE_CONSUMER_KEY), and $(NETSUITE_CONSUMER_SECRET).
Following macros are used for temporarily staging the data on azure storage.
 
Macro Name
Description
$(AZURE_CLIENT_ID), $(AZURE_CLIENT_SECRET)
Credentials used to access azure services.
$(AZURE_TENANT_ID)
Tenant ID (Directory ID) assigned by Azure Active Directory service.
$(AZURE_CONTAINER_NAME)
Name of the container in azure storage account which contains data files to pull the data from.
$(AZURE_STORAGE_ACCOUNT)
Name of the azure storage account.
3. If you want to override any of the default settings, you can add the following macros and set the values. Usually, these are not required unless you have a specific data format/encoding or want to increase the sample size, or want to specify the table operation. These macros are not displayed by default. You can manually add the name and set the value to override the default value.
 
Macro Name
Description
$(NETSUITE_SAVED_SEARCHID)
The ID of a saved search in NetSuite Portal. You can obtain the ID from the URL of the saved search configuration on NetSuite portal. For example:
https://tstdrv1330641.app.netsuite.com/app/common/search/searchresults.nl?searchid=681&whence=
$(NETSUITE_USE_UILABEL)
Specifies whether label must be used instead of ID for custom fields. Default is True.
$(NETSUITE_ROLE)
Specifies the NetSuite role ID. A role specifies the permissions required to access different parts of NetSuite.
$(NETSUITE_CUSTOMIZATION)
Level of customization for accessing NetSuite record types. The options are:
• Full: Both standard fields and custom fields will be retrieved
• CustomFieldsOnly: Only custom fields are retrieved.
• None: Fields are not retrieved.
Default is Full.
For more information about these properties, see NetSuite connector section in the Actian DataConnect documentation.
$(AVALANCHE_DSN)
Name of the ODBC data source for connecting to Avalanche database. Specify this macro if you want to use a pre-configured DSN on your system instead of the connect string.
$(AVALANCHE_CREATE_TABLE_QUERY)
Create table statement to use for creating the table. Make sure partitioning is specified. Table name in the query must match the AVALANCHE_TABLE macro value.
$(AVALANCHE_CREATE_TABLE_OPTIONS)
Use this option when you do not want to build the complete query but only want to specify options to pass to "with" clause of create table query.
Note:  Make sure partitioning is specified. Also, this macro is ignored if AVALANCHE_CREATE_TABLE_QUERY macro is defined.
$(OUTPUT_MODE)
Table operations that must be performed before inserting data. The available operations are:
• replace: Drops existing table and creates new table
• delete_append: Truncates table before inserting.
• append: Creates table only if it does not exist and inserts records. By default, the value is append.
$(DEFAULT_TEXT_COL_SIZE)
Set the default size of the text columns in the table. Set it to a reasonable value based on your data to avoid truncations.
This property is also useful for inserting double-byte characters like Japanese or Chinese. Varchar is used for text data types that supports single byte characters. To support double-byte characters in varchar data type, the size of the column must be doubled using this macro.
$(UNICODE_CHARS)
Indicates whether the data contains Unicode characters. If set to True, nvarchar data type is used for text columns.
$(BATCH_SIZE)
Size of the chunk (in terms of number of records), the source is split into. The default value is 50000.
$(COPYVW_PARALLEL_LOAD_SIZE)
Number of chunks to load in parallel using COPY VWLOAD. The default value is 20.
$(AVALANCHE_DBADMIN_GROUP_ACCESS)
Grant table access to for the dbadmingrp group. Only applicable when new table is created. Default is True.
Note:  
You can specify individual column sizes by defining macros in the format COL_SIZE_XXX, where XXX is the source field name in uppercase. This is only applicable for fields of text type.

You can specify the VWLOAD options with macro names in the VW_XXX format, where XXX is the property name listed in the COPY VWLOAD section in the Avalanche documentation. One macro can be added for each property. FDELIM, RDELIM, QUOTE, AZURE credentials must be specified using specific macros for these properties. For example:
VW_NULLS = NULL
VW_STRICTNULLS = STRICTNULLS
4. Run the configuration.
If there are any errors during job execution, see Troubleshooting Template Configuration Execution Errors for the troubleshooting information.