Skip to main content

Building SELECT lists with SQL

If you are using SQL for ingestion and you want to rename columns

PascalCase to snake_case

-- PascalCase to snake_case
-- , OtherNFLStats AS other_nfl_stats

SELECT
    CONCAT(', ', column_name, ' AS ', LOWER(
      REGEXP_REPLACE(
        REGEXP_REPLACE(column_name, '([a-z0-9])([A-Z])', '$1_$2'), -- Pass 1: Lower to Upper
        '([A-Z])([A-Z][a-z])', '$1_$2'                             -- Pass 2: Acronym to Word
      )
    )) AS formatted_column
FROM information_schema.columns
WHERE table_name = 'my_table'
    AND table_schema = 'my_schema'
ORDER BY ordinal_position;

PascalCase to snake_case with substitution

-- PascalCase to snake_case
-- , OverageAmount AS overage_amt
WITH RawColumns AS (
    SELECT  column_name
        , LOWER(
            REGEXP_REPLACE(
                REGEXP_REPLACE(column_name, '([a-z0-9])([A-Z])', '$1_$2')
                , '([A-Z])([A-Z][a-z])', '$1_$2'
            )
        ) AS snake_name
    FROM information_schema.columns
    WHERE table_name = 'my_table'
    AND table_schema = 'my_schema'
)
SELECT CONCAT(', ', column_name, ' AS ', 
        AGGREGATE( -- AGGREGATE is Spark-specific (thus applicable to databricks)
            -- Define the list of [pattern, replacement]
            ARRAY(
                  STRUCT('_amount$'      AS pat, '_amt' AS rep)
                , STRUCT('_percent$'     AS pat, '_pct' AS rep)
                , STRUCT('_flag$'        AS pat, '_flg' AS rep)
                , STRUCT('_code$'        AS pat, '_cd'  AS rep)
                , STRUCT('_number$'      AS pat, '_nbr' AS rep)
                , STRUCT('_name$'        AS pat, '_nm'  AS rep)
                , STRUCT('_description$' AS pat, '_dsc' AS rep)
                , STRUCT('_desc$'        AS pat, '_dsc' AS rep)
                , STRUCT('_date$'        AS pat, '_dts' AS rep)
            )
            , snake_name -- Initial value
            , (acc, x) -> REGEXP_REPLACE(acc, x.pat, x.rep) -- Iterative function
        )
    ) AS formatted_column
FROM RawColumns
ORDER BY column_name;

 

Same Case with custom rules + suffix substitution

-- Custom rules + suffix substitution
SELECT
    CASE 
        WHEN column_name = 'meta_updated' THEN CONCAT(', ', column_name, ' AS ', 'EDWLastModifiedDTS') -- custom rename
        WHEN column_name = 'hcsys_file_name' THEN CONCAT(', ', column_name, ' AS ', 'hcsys_file_name') -- don't rename to _NM
        WHEN LEFT(column_name, 5) = 'meta_' THEN CONCAT(', ', column_name, ' AS src_', column_name)    -- prepend src_ to all meta_ columns  
        ELSE 
            CASE 
                WHEN column_name ILIKE '%Amount'      THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Amount$', 'AMT'))
                WHEN column_name ILIKE '%Percent'     THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Percent$', 'PCT'))
                WHEN column_name ILIKE '%Flag'        THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Flag$', 'FLG'))
                WHEN column_name ILIKE '%Code'        THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Code$', 'CD'))
                WHEN column_name ILIKE '%Number'      THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Number$', 'NBR'))
                WHEN column_name ILIKE '%Name'        THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Name$', 'NM'))
                WHEN column_name ILIKE '%Description' THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Description$', 'DSC'))
                WHEN column_name ILIKE '%Desc'        THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Desc$', 'DSC'))
                WHEN column_name ILIKE '%Date'        THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Date$', 'DTS'))
                WHEN column_name ILIKE '%Count'       THEN CONCAT(', ', column_name, ' AS ', REGEXP_REPLACE(column_name, '(?i)Count$', 'CNT'))
                ELSE CONCAT(', ', column_name, ' AS ', column_name)
            END
    END AS c
FROM information_schema.columns
WHERE table_schema = 'my_schema'
    AND table_name = 'my_table'
ORDER BY ordinal_position;

 

Back to top