SQL Editor is used to sample the data of a table or result of an ad-hoc queries in a database. To connect to databases it utilizes data point created in the project.
To view, write and run a SQL query, follow the below steps.
Step I: Navigate to SQL Editor interface. To open SQL Editor interface refer, Opening SQL Editor.
The left pane displays the list of supported database connections. Click the arrow icon to drill down and view the available database, schemas and tables. (here we are considering Netezza for reference).
1. The Conn Info tab in the right pane displays the following connection information for the chosen connection.
a. The Agent Name displays the agent associated with the connection.
b. The Connection Name displays the name of the connection chosen.
c. Port specifies the hostname or the IP address of the connection.
d. User specifies the user id that will be used to connect to the database connection system. This user should have necessary privileges to access the data in the databases.
- If there is any Global project that the user has access to, then the Project drop-down also displays the available global projects. You can choose the global project from the Project drop-down, and select the global database connection as required.
- TABLE, VIEW and SYNONYM are listed for the selected schema or database.
2. The Query tab displays the Query run by the user. Once you select a table, you can either write a custom query or add a query from Query History to run in editor.
3. The Query History tab displays the list of queries run by the user. You can select a query to add it to the editor.
- To add a query to the editor, select a query and click Add to Editor.
- To delete a query, select single or multiple queries and click Delete.
- To view the list of latest queries run, click Refresh.
Maximum of 20 queries last run are saved and displayed in Query History.
Select table to view and run a query
You can select a table associated with the database, either to view the data in it, or you can write any custom query in the canvas to execute it and view the result.
1. Select the table for which you want to view the data (here we are considering CUSTOMER for reference).
To view the columns of the table chosen, click Columns tab in the lower pane.
2. The table column details are displayed as shown.
3. To view the table data, click View Data tab. The select query is run and View Data tab in lower pane displays the data.
4. To run a custom query for the table chosen, write a customer query, and then click Run.
- To stop the query run, click Stop.
- You can add a query from Query History to run the query in editor.
4. The Query is run and data is displayed in the lower pane.
Export the Data
1. To export the data result for the query executed, click Export.
2. The data object get exported in JSON file format to the default download folder in the local machine.