NetSuite Query Studio for VS Code

About

NetSuite Query Studio is a light weight wrapper for making JDBC connections via SuiteAnalytics Connect (NetSuite2). It's designed to be used in VS Code with Jupyter notebooks and make developing DB queries easier. It can run queries, display outputs, and includes a DB Object Explorer.

Set Up

Requirements

1. Download Java 9 - 11.
2. Clone this repository.
3. Download Python 3.7 - 3.10, the latest version will not work. Then run the following command.
4. pip install -r requirements.txt
5. Install the Juypter notebook extension to VSCode.
6. Download the .jar file for your OS. Place it in the system folder of the repository.
   • Windows
   • Linux
   • OS X

.env

Create a .env file in the system folder of the repository with the following contents. Supply your own access token variables.

#access token variables
USER=TBA
ACCOUNTID=1234567
ROLEID=...
CONSUMERKEY=...
CONSUMERSECRET=...
TOKENID=...
TOKENSECRET=...

Getting Started

Running Queries (query.ipynb)

1. Add a query to the queries folder. (Use the .sql file extension).
2. Open query.ipynb
3. Specify the name of the query file.
4. Hit Run All
5. Look for the results in the results folder.

Query Options

• If you want to query an additional environment (like a sandbox) create a second .env file with a seperate name (like .env.sb2). Then simply type the name of the .env file you want to use in the `environment variable.
• To specify a name for the results csv, enter it into the results_file variable. Leave it as the empty string to skip writing a file.
• Use should_print to control whether the query output displays to the screen.

Schema Browsing (schema.ipynb)

1. Open schema.ipynb.
2. Type a table name to view, or leave it blank to view a list of every table.
3. Hit Run All

Sample Outputs:

All Tables	Transaction Table

Browser Options

• Enter a regex search pattern in the pattern variable to find columns more easily.
• To see dataype or table definitions, copy paste columns/tables from the schema browser output to the drilldown variable. Then hit Run All again.
• Hit open in a text editor to see the full output.

Sample Options:

# regex search pattern, matching columns will be sorted to the top
pattern = "cust"

# copy paste the columns you want to drill into below
drilldown = """
  └─🗝️ category: customerCategory.id
  └─ comments
  └─ companyname
"""