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 supported files in the VS Code Explorer (for .sql, .hql, or .snowql files).

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 SQL file(s) to the internal DnA style (adds DNA-STYLE- prefix to file names).

• 2. SQL → Prettier Format

  • Converts selected SQL file(s) to Prettier formatting (adds PRETTIER- prefix to file names).

2. DnA-2: Compare Two SQL Files

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

  • Select two SQL 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_.

• 2. Dev → Prod

  • Transforms USERDB_[USERNAME].tmp_ back to stg..

• 3. HQL → SparkSQL

  • Converts HQL syntax (e.g., CREATE TEMPORARY TABLE) to SparkSQL (CREATE OR REPLACE TEMP VIEW).

• 4. SparkSQL → HQL

  • Converts SparkSQL temporary view syntax back to HQL.

• 5. Copy Files

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

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: This requires 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

sql-formatter

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

License

• MIT License – see LICENSE.

Supported dna-query-magic File Types

• Right-click files: .sql, .hql, .snowql
• Convert SQL in-file should work 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.