Export data from NetSuite JDBC

To create a NetSuite JDBC export or lookup from NetSuite SuiteAnalytics, ensure you have a connection ready. There are various ways to create an export.

• Navigate to Resources → Export and click + Create export.
• Navigate to Home and click Create → Flow or Integration. Name your flow or integration and click Add source.

After you’ve created an export, you can create a lookup and map your results. However, we recommend that you configure your initial export query to export all the necessary data. While lookups are supported, they could hamper performance.

For both exports and lookups, select your application, NetSuite JDBC, and either create a connection or use an existing connection. Add a name and description to your new export or lookup. After naming and describing the connection, create the SQL query that will export your data. NetSuite provides helpful information for query language compliance using both SuiteQL and SQL.

Important: For exports triggering the error message: QUERY_SYNTAX ERROR. Handlebar expressions wrapped in two brackets {{...}} will now result in a value wrapped with single quotes. To resolve the error, wrap handlebar expressions in three brackets instead {{{...}}}
Example: {{settings.export.table}} to {{{settings.export.table}}}

For example, you may want to get several transaction columns from your Transactions table and import the data into Snowflake. You might use this SQL query

SELECT internalId, tranDate, tranId, entity, amount FROM Transaction

The Connect Schema in NetSuite documentation lists all the available tables. After you’ve created your SQL query, determine your export type. There are three export types:

• All – Export all data returned in the search each time the export is run
• Delta – Export all data with a date/time value for the selected field that is greater than the last time the data flow was run.
  • Currently, you can’t directly use LastExportDateTime in a delta export. See more in Known limitations.
• Limit – Export a set number of records

You can also sort and group your records, provide mock data, and manage your advanced settings.

Known limitations

If you need to convert a string value to a date format, use some of the SuiteQL functions listed here. If you need to convert the lastExportDateTime into a date format, use the following:

to_timestamp('{{{lastExportDateTime}}}', 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"')

instead of

to_date('{{{dateFormat "YYYY/MM/DD hh:mm:ss" lastExportDateTime}}}', 'yyyy/mm/dd HH24:mi:SS')

In some cases, exports with large data sets may fail. If you create an export that fails, you can circumvent the error by splitting your query into two exports. For example:

SELECT internalId, tranDate, tranId, entity, amount FROM customer

Split into two exports

SELECT internalId, tranDate, tranId, entity, amount FROM customer where id<2000000
SELECT internalId, tranDate, tranId, entity, amount FROM customer where id>=2000000