Saturno - Impala SQL Tools via ODBC

Saturno is a Visual Studio Code extension designed to facilitate connectivity and querying of Impala databases using ODBC. It provides a suite of tools to execute SQL queries, visualize results, and manage database interactions directly within the VS Code environment.

Features

• Impala Connectivity: Seamlessly connect to Impala databases using an ODBC Data Source Name (DSN), with the ability to add or remove DSNs as needed.
• Connection Management: Monitor and manage connection status via a status bar indicator, with manual connect and disconnect options.
• SQL Execution: Execute entire .sql files or individual SQL queries with highlighted code execution.
• SQL Explain Queries: Execute EXPLAIN queries on SQL statements without manually typing the EXPLAIN keyword, providing query execution plan analysis.
• Result Visualization: View query results in an interactive, sortable table powered by DataTables with features including column sorting, search filtering, pagination, and copy-to-clipboard functionality for individual cells or entire datasets.
• CSV Export: Export query results from individual queries to CSV format.
• Query History: Access a reverse chronological list of executed queries with the ability to copy them to the clipboard.
• Database Explorer: Browse Impala databases, tables, and fields in a tree view, with options to copy table names and column lists to avoid using SELECT *.
• Parameterized Queries: Define and manage parameters in a dedicated view, using them in queries with {param} syntax for dynamic SQL execution.
• SQL Best Practices Validation: Automatically or manually validate SQL queries against a set of best practices to ensure code quality and performance standards.
• Snippets: Includes a collection of SQL snippets for common Impala commands, such as CREATE, INSERT, SELECT, and more, to speed up development.
• Keyboard Shortcuts: Provides convenient keyboard shortcuts for quick access to commands, enhancing productivity.
• Logs: Access Saturno logs for debugging and monitoring purposes.

Requirements

• An installed ODBC driver for Impala compatible with your system.
• A properly configured ODBC Data Source Name (DSN) for your Impala database connection.
• Visual Studio Code version 1.87.0 or higher.

Installation

1. Install the extension from the Visual Studio Code Marketplace.
2. Configure the ODBC DSN in the VS Code settings (see Configuration below).

Configuration

Saturno can be configured through VS Code settings:

• saturno.dsn: Specifies the ODBC DSN used to connect to Impala.
  • Type: String
  • Default: "impala-prod"
  • Description: The DSN configured in your ODBC driver for Impala connectivity.

To configure:

1. Open VS Code settings (Ctrl+, or Cmd+, on macOS).
2. Search for saturno.dsn and enter your DSN value.

Usage

1. Connect to Impala:

   • Click the status bar icon ($(plug) Impala: Desconectado) to establish a connection.
   • Alternatively, use Alt + . to connect.

2. Disconnect from Impala:

   • Use Alt + , to close the active connection.

3. Execute Queries:

   • Open any file in the editor.
   • Saturno: Ejecutar archivo SQL: Runs all queries in the file, separated by ;, in sequence. Use Ctrl + Shift + Enter.
   • Saturno: Ejecutar query SQL: Executes the SQL query at the cursor position and displays results in a table. Use Ctrl + Enter.
   • Saturno: Exportar query SQL a CSV: Executes the SQL query at the cursor and exports results to a CSV file. Use Alt + Enter.
   • Saturno: Explicar query SQL: Shows the query execution plan for the SQL query at the cursor position. Use Ctrl + Alt + Enter.

4. Manage Parameters:

   • Open the "Saturno" view in the Activity Bar and go to the "Parameters" section.
   • Add parameters using the "+" button, edit, or delete them as needed.
   • Use parameters in your queries with the {param} syntax, e.g., SELECT field1, field2 FROM table WHERE year = {anio}.

5. Manage DSN:

   • Use Saturno: Cambiar DSN to update the DSN manually.

6. Explore Database:

   • Open the "Saturno" view in the Activity Bar:
     • Tree: Navigate through databases, tables, and fields.
       • Fields: View fields of a selected table and copy the list of fields to the clipboard.
       • Copy Table Name: Right-click on a table to copy its name.

7. Review Query History: - Access the "Saturno" view in the Activity Bar and navigate to the "Query History" section. - Browse through previously executed queries in reverse chronological order. - Click on any query to copy it to the clipboard for reuse.

8. SQL Best Practices Validation

   Saturno includes built-in SQL best practices validation to help maintain code quality and performance standards:

   • Automatic Validation: Configure validation through the saturno.validateGoodPractices setting to automatically check queries against best practices.
   • Manual Validation: Use dedicated commands to validate individual queries or entire documents on demand.

   Validation Rules

   When enabled, Saturno validates queries against the following SQL best practices:

   • SELECT * Restrictions: Prevents the use of SELECT * on result zone or raw data tables (tables starting with resultados_ or s_)
   • Table Wildcard Prevention: Blocks table wildcard selections like table.*
   • Subquery Restrictions: Prohibits subqueries within IN () clauses
   • INSERT VALUES Prevention: Blocks INSERT VALUES statements to maintain data integrity
   • JOIN Limitations: Limits JOIN operations to a maximum of 5 per query
   • UNION Limitations: Restricts UNION operations to a maximum of 5 per query
   • WITH Clause Limits: Limits WITH clauses to a maximum of 5 per query
   • CASE Statement Limits: Restricts CASE statements to a maximum of 50 per query
   • WHEN Clause Limits: Limits WHEN clauses to a maximum of 20 per CASE statement

Keyboard Shortcuts

Saturno provides the following keyboard shortcuts for quick access to its commands:

Snippets

Below is a summary table of the available SQL snippets included with Saturno:

Logs

Logs for Saturno can be found in the Output panel of Visual Studio Code. To view logs:

1. Open the Command Palette (Ctrl + Shift + P or Cmd + Shift + P on macOS).
2. Type Output: Focus on Output View and select it.
3. In the Output panel, select Saturno logs from the dropdown to view logs related to Saturno operations.

Known Issues

• Performance may degrade with extremely large datasets.
• Some advanced Impala features might not be fully supported via ODBC.
• The extension may not handle all edge cases in SQL syntax, particularly with complex queries.

Contributing

Contributions are welcome! If you find a bug or have a feature request.

Additional Resources

• Impala Documentation
• ODBC Specification

Enhance your Impala workflow with Saturno in Visual Studio Code.