There are two main classes of external, third-party tools that connect to Nitro and Nitro data.
- Business intelligence tools
- SQL query tools
Business Intelligence Tools
Nitro supports connections from business intelligence (BI) tools that support JDBC/ODBC connections to Amazon Redshift. Examples of some popular business intelligence tools with pre-built connectors to Amazon Redshift include:
BI tools access Nitro data using a special, purpose-built integration user known as the BI User. This user has read-only access to the reporting layer in Nitro, which allows Nitro data to be loaded into the BI tool. At the time of provisioning, one or more BI user accounts are created and issued depending on the number of BI tools in-use. The connection string is also provided.
In order for a connection to be established between Nitro and the BI tool, configure the necessary network security items on the Nitro side and if required, on the BI tool side:
- The public-facing IP address of the BI tool application must be allowlisted in Nitro configuration. Submit a support ticket with the IP(s) representing the network from which the BI tool is connecting.If a corporate firewall is in place, outbound/inbound traffic to the Nitro database IP and port may need to be allowlisted/opened. If this is needed, submit a support ticket to get the right connection info needed for the Nitro database.
SQL Query Tools
Nitro supports connections from SQL query tools that support JDBC/ODBC connections to Amazon Redshift. Similar to BI tools, Nitro is agnostic to the type of SQL query tool connecting to Nitro’s database.
Both Nitro and Amazon recommend the use of SQL Workbench/j. This tool supports the type of connectivity and connection options needed for Nitro connections.
The Nitro support team issues special, purpose-built users for use with SQL Query tools. The breadth and type of access to Nitro depends on the specific user tasks. Nitro Support provides two types of users:
- Reporting User - Like the BI user used by BI tools, this user may also be used to connect to Nitro from a SQL query tool. In this context, the this user only has access to the reporting and dds layers of the Nitro database.
- ReadAll User - This type of user is for the Nitro Administrator or the Nitro Data Analyst. This user has read-only access to all layers in the Nitro database).
When issuing queries against the Nitro database using a SQL client, the client must be configured so each individual database interaction is automatically executed in its own transaction.
If autocommit is not configured for every interaction, database locks could be created and can disrupt the normal operation of Nitro ETL.
This configuration is often referred to as autocommit configuration. SQL Workbench/j supports this configuration in the connection settings.
Nitro has introduced a query timeout setting that will terminate any query being run by a named database user that takes 20 minutes or longer to fully execute. This setting was introduced in order to protect against erroneous, long running queries (LRQs) that are issued either unwittingly or in error and subsequently block other critical processes from executing.