dna-query-magic

dna-query-magic is a VS Code extension for SQL/HQL/SparkSQL formatting, functional comparison, and relevant file transformations, built for specific Hadoop and multi-environment team workflows.

⚠️ Important Notice

This extension builds on sql-formatter, but includes customizations intended primarily for users or teams already familiar with the publisher or the team's workflows.

If you're not part of the intended audience but are curious, feel free to explore–just note that this tool is designed around custom workflows and internal frameworks for SQL syntax management, and may not suit all use cases. Otherwise, we highly recommend reviewing the linked sql-formatter GitHub repository, for references to currently supported VS Code Extensions. More on this parser can be found in the Appendix of this file.

Index

• Primary Function: VS Code Explorer, Right-Click Functions

  • How the Right-Click SQL Comparison Works

• Secondary Function: VS Code SQL Editor, Right-Click Functions

• Appendix

Features

Primary Function: VS Code Explorer, Right-Click Functions

Below are the actual context menu commands and submenus available when you right-click files in the VS Code Explorer. Commands work on any text file, not just SQL-specific extensions.

All actions support single file or batch selection when multiple files are selected.

1. DnA-1: Convert to Style Guides (submenu)

Opens a submenu with:

• 1. SQL → DnA Style Guide

  • Converts selected file(s) to the internal DnA style guide format.
  • Enhanced UX: Choose to transform files in place or create copies with custom prefixes.

• 2. SQL → Prettier Format

  • Converts selected file(s) to Prettier formatting.
  • Enhanced UX: Choose to transform files in place or create copies with custom prefixes.

New Interactive Experience: When you select either option, you can:

• Choose "Transform file(s) directly" to modify originals in place
• Choose "Create a copy of file(s)" and specify a custom prefix
• Power User Tip: Type a prefix directly (e.g., "formatted", "pretty") to quickly create copies

2. DnA-2: Compare Two SQL Files

• Compare two files for functional (not just formatting) differences.

  • Select two files, right-click, and choose this command to see if they are functionally identical.
  • See How the Right-Click SQL Comparison Works below for more detail.

3. DnA-3: Convert HQL, and Copy Files (submenu)

Opens a submenu with:

• 1. Prod → Dev

  • Transforms table references from stg. to USERDB_[USERNAME].tmp_.
  • Enhanced UX: Choose to transform files in place or create copies with custom prefixes.

• 2. Dev → Prod

  • Transforms USERDB_[USERNAME].tmp_ back to stg..
  • Enhanced UX: Choose to transform files in place or create copies with custom prefixes.

• 3. HQL → SparkSQL

  • Converts HQL syntax (e.g., CREATE TEMPORARY TABLE) to SparkSQL (CREATE OR REPLACE TEMP VIEW).
  • Enhanced UX: Choose to transform files in place or create copies with custom prefixes.

• 4. SparkSQL → HQL

  • Converts SparkSQL temporary view syntax back to HQL.
  • Enhanced UX: Choose to transform files in place or create copies with custom prefixes.

• 5. Copy Files

  • Copies file(s) with a COPY- prefix.

• 6. CAST → RLIKE Transform (temporary)

  • Converts CAST(column AS INT) IS [NOT] NULL patterns to RLIKE '^[0-9]+$' for EMR 5→7 migration.
  • Enhanced UX: Choose to transform files in place or create copies with custom prefixes.
  • Addresses Hive 2.x→3.x compatibility issues.

• 7. Boolean → CAST Transform (temporary)

  • Converts column = 1 patterns to CAST(column AS INT) = 1 for Hive 2.x→3.x compatibility.
  • Enhanced UX: Choose to transform files in place or create copies with custom prefixes.
  • Includes smart column exclusion (skips obvious integer columns like id, count, revenue, etc.).

• 8. Remove File Prefixes

  • Utility to remove file name prefixes like COPY-, PRETTIER-, formatted-, etc.
  • Includes batching capability, allowing for multi-file operations at once.
  • Cross-platform compatibility: Works reliably on Windows, WSL/Linux, and macOS.

Use case: This supports workflow for copying or cloning HiveQL scripts from production repositories to development environments, enabling quick transformations and smooth code review and merge processes.

Note: Commands 1-4 above require quick configuration in User Settings. VS Code → "Preferences: Open User Settings (JSON)"

Add this line to your JSON file:

"queryMagic.username": "YOUR_USERNAME"

Example, if hypothetically your username is "SKEMP":

"git.defaultCloneDirectory": "C:\\Users\\skemp\\cloned\\",
"queryMagic.username": "SKEMP",
"sqlfluff.executablePath": "C:\\Users\\skemp\\AppData\\Roaming\\Python\\Python312\\Scripts\\sqlfluff.exe",
"editor.formatOnSave": true,

The default username is set to MJORDAN. After updating to your username, if this is still appearing, restart VS Code and try again.

How the Right-Click SQL Comparison Works

• Ignores whitespace, formatting, comments
• Focuses on functional SQL logic
• Normalizes dialect, punctuation, spacing
• Notifies if identical, or opens diff for differences

Example:

SELECT
    *
FROM
    table
WHERE
    col = 'value'

-- functionally identical to
select * from table where col = 'value'

Secondary Function: VS Code SQL Editor, Right-Click Functions

Within an open SQL file in the editor, you can right-click to access:

• DnA: SQL → DnA Style Guide

  • Applies team-specific DnA SQL formatting to either:

    • your highlighted selection (for formatting a specific query or block), or
    • the entire file (if all content is highlighted).

• DnA: SQL → Prettier

  • Formats your highlighted selection or the entire file using generic Prettier rules (via sql-formatter).

Highlighting is key: these commands work on whatever code is currently selected. If nothing is selected, no formatting is applied.

This is convenient for on-the-fly code cleanup, standardization, and legacy formatting alignment – without leaving your editing context.

Appendix

Configuration Settings

LDH Asset List Processing (Optional Feature)

Configuration-gated feature for generating SQL and Looker URLs from asset lists.

Enable: Set "queryMagic.enableLDHmenu": true in VS Code settings.

Usage: Organize a list of asset ids, within vs code, highlight then right-click and select the ldh-relevant menu.

Configuration Settings via JSON settings

Enhanced File Operations

New in this version: All transformation commands now offer an enhanced user experience:

1. Transform in Place: Modify original files directly
2. Create Copies: Generate new files with custom prefixes
3. Type-to-Copy Shortcut: Type a prefix directly in the dialog (e.g., "backup", "test", "formatted") to quickly create copies
4. Cross-Platform Support: All file operations work reliably across Windows, WSL/Linux, and macOS
5. Universal File Support: Commands work on any text file, not limited to specific SQL extensions

sql-formatter

• Built with sql-formatter, TypeScript, VS Code Extension API.

License

• MIT License – see LICENSE.

Supported dna-query-magic File Types

• Right-click file operations: Work on any text file (previously limited to .sql, .hql, .snowql)
• In-editor SQL formatting: Works on most SQL-related documents, including plain text files opened in VS Code
• LDH asset list processing: Works in any open editor with selected text