Load Data from an SFTP Source into NetSuite Analytics Warehouse (Preview)

As a service administrator, you can use the NetSuite Analytics Warehouse extract service to acquire data from a secure FTP source (SFTP) and use it to create data augmentations.

Ensure that SFTP is enabled on the Enable Features page prior to creating this connection. See Make Preview Features Available.

1. Check the port number for your secure FTP database and create a service request to get the port opened.

   Note:

   You must provide the IP address of the SFTP server, which should be a public IP and can’t be host name and a fully qualified domain name (FQDN) or a class A private IP.
2. In NetSuite Analytics Warehouse, create the SFTP data connection using these instructions:
   1. In NetSuite Analytics Warehouse Console, click Data Configuration under Application Administration.
   2. On the Data Configuration page, click Manage Connections.
   3. On the Manage Connections page, click Create and then click Connection.
   4. In Create Connection, select Data Extraction in Usage Type, and then select SFTP as the connection type.
   5. In the dialog for the SFTP connection, provide applicable values in Remote Host, User Name, Private Key, Remote Host Extract Files Directory, File Type, CSV Delimiter, CSV Date Format, and CSV Timestamp Format. Ensure that the value in Remote Host Extract Files Directory begins with a leading "/" to avoid issues. In Lock Metadata, specify whether you want to turn off the metadata extracts after first refresh if metadata isn't going to change. This option is useful if the flag to derive metadata from data files using the metadata utility is turned on in your source. In Remote Host, ensure that you specify an SFTP Server that supports FIPS Compliant key exchange algorithms.
      Ensure the following:

      • The table name and file name in your SFTP source needs to be the same.
      • The private key you provide is in the valid OpenSSH format and the minimum number of bits in the key should be 2048.
      • Date format for the data files must match the date format in your SFTP source; for example, if you've used MM/dd/yyyy (01/23/1998) in your source, then you must specify the same format in CSV Date Format. See About Date and Timestamp Formatting for CSV File-based Extractors.
      • Timestamp format for the data files must match the timestamp format in your SFTP source; for example, if you've used MM/dd/yyyy hh:mm:ss (01/23/1998 12:00:20) in your source, then you must specify the same format in CSV Timestamp Format.

      
      

      

      
      
   6. Verify that the Refresh Metadata toggle is enabled to ensure that the metadata is refreshed when you save the connection.
      You can later refresh the metadata from the Actions menu on the Manage Connections page, if required.

      Note:

      You can’t create augmentations for SFTP unless you perform a metadata extract.
   7. Click Save.
3. Specify the file layout mode in your SFTP source.

   You can organize the source files in standard folders or flat files.

   Layout	Enable Flat File Layout	Source Layout
   Standard folder	false	Remote Host Extract Files Directory/datastore-name/data-files
   Flat file	true	Remote Host Extract Files Directory/data-files

   In the Enable Flat File Layout drop-down:

   • For standard folder layout, select false when each data store has its own folder.
   • For flat file layout, select true when all data store files are placed directly in the configured root directory.
4. On the Manage Connections page, select Actions for the SFTP connection and then select Test Connection.
5. To delete a connection, on the Manage Connections page, click Actions for the applicable connection and select Delete.

   Note:

   Ensure that you delete the functional areas, data augmentations, and data applications related to the data connection before deleting it. You can't update or load data from deleted data connections to the warehouse.
6. After the connections are successfully established, navigate to the Data Augmentation tile on the Data Configuration page, select the connection in Data Source, and create a data augmentation using the SFTP data. Select the applicable secure FTP source tables. See Augment Your Data.