SQL Delight in Snowflake | EXCLUDE, RENAME, REPLACE, ILIKE, GROUP BY ALL

Data

SQL Delight in Snowflake | EXCLUDE, RENAME, REPLACE, ILIKE, GROUP BY ALL

Snowflake is like that friend who’s always got something new up their sleeve! Snowflake is all about staying up-to-date and cool, regularly spicing up its lineup of awesome features to keep up with the changing times in the world of data. So, in this blog post, get ready for a front-row seat to the show as I walk you through these cool updates and show you exactly how to use them to make your data game even stronger. We’ll be covering the following SQL functions:

  • EXCLUDE
  • RENAME
  • REPLACE
  • ILIKE
  • GROUP BY ALL

Throughout this blog post, we will reference Snowflake’s Tasty Bytes dataset. To learn more about how to set up your Tasty Bytes data, here’s a handy Snowflake quickstart guide: An Introduction to Tasty Bytes.

EXCLUDE

In Snowflake, you have the option of using the EXCLUDE keyword within your SELECT statement to give certain columns the slip, ensuring they stay out of your query results. This trick comes in handy especially when you’re dealing with a large number of columns and only want to give the boot to a select few, making your query outcomes cleaner and more focused. For example, let’s look at the following SQL:

SELECT * FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

EXCLUDE in Snowflake

This dataset contains 11 columns. Typically, if you want to exclude a handful of columns, you’d have to painstakingly list out the chosen columns within your SELECT statement. Now, with EXCLUDE, you can simply select all columns and exclude the one you don’t want in your query results. Let’s say you don’t want your query to contain MENU_TYPE_ID , you can simply add EXCLUDE followed by the MENU_TYPE_ID:

SELECT * EXCLUDE MENU_TYPE_ID 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

EXCLUDE MENU_TYPE_ID in Snowflake

If you want to exclude multiple columns at once, all you have to do is put those columns together within a set of trusty brackets:

SELECT * EXCLUDE (MENU_TYPE_ID, MENU_ITEM_ID) 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

EXCLUDE Multiple Columns in Snowflake

RENAME

Snowflake also allows you to rename specific columns in your SELECT statement. Think of it as a sibling to the exclude feature we talked about earlier; it functions in a similar manner:

SELECT * RENAME TRUCK_BRAND_NAME AS TRUCK_NAME 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

RENAME in Snowflake

Just like you’d gather columns within brackets to exclude them for renaming, you can apply the same principle:

SELECT * RENAME (TRUCK_BRAND_NAME AS TRUCK_NAME, MENU_ITEM_NAME AS ITEM_NAME) 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

RENAME Include in Snowflake

You can also combine them and use EXCLUDE and RENAME in the same statement, for example:

SELECT * 
   EXCLUDE (CC_CLOSED_DATE_SK, CC_CALL_CENTER_ID) 
   RENAME (CC_REC_START_DATE AS CC_START_DATE, CC_REC_END_DATE AS CC_END_DATE) 
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER LIMIT 1000;

EXCLUDE and RENAME in Snowflake

REPLACE

In your Snowflake toolkit, there’s another handy function called REPLACE, which lets you seamlessly switch up the values of particular columns right within your statement. Just as with EXCLUDE and RENAME, this comes to the rescue when you’re aiming to tweak the values of one or more columns in a targeted manner:

SELECT * 
   REPLACE (UPPER(MENU_TYPE) AS MENU_TYPE) 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

REPLACE in Snowflake

You can combine EXCLUDEREPLACE and RENAME; however, EXCLUDE needs to be the first keyword, followed by REPLACE then RENAME, for example:

SELECT * 
   EXCLUDE (MENU_TYPE_ID, MENU_ITEM_ID) 
   REPLACE (UPPER(MENU_TYPE) AS MENU_TYPE) 
   RENAME (TRUCK_BRAND_NAME AS TRUCK_NAME, MENU_ITEM_NAME AS ITEM_NAME) 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

EXCLUDE, REPLACE, RENAME in Snowflake

ILIKE

ILIKE allows matching of strings based on comparison with a pattern and now joins the party in your SELECT statement just like EXCLUDE RENAME and REPLACE. Imagine the dataset we explored earlier. Now, suppose we’re aiming to simplify things by retaining only those column names that include the string “MENU.” The process is as straightforward as follows:

SELECT * 
   ILIKE '%MENU%' 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

ILIKE in Snowflake

You can also combine ILIKE with all the keywords we mentioned before, except EXCLUDE. For example, let’s test this out by specifying ILIKEREPLACE and RENAME in the same SELECT statement:

SELECT * 
   ILIKE '%MENU%' 
   REPLACE (UPPER(MENU_TYPE) AS MENU_TYPE) 
   RENAME (MENU_TYPE AS ITEM_TYPE, MENU_ITEM_NAME AS ITEM_NAME)
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

ILIKE, REPLACE, RENAME in Snowflake

GROUP BY ALL

This new function liberates you from the task of explicitly listing every column in your GROUP BY statement. Let’s kick off by setting the stage with a straightforward product sales table and insert into it some mock data to get things rolling:

CREATE OR REPLACE TABLE PRODUCT_SALES (
    PRODUCT_ID NUMBER
    ,PRICE NUMBER
    ,QUANTITY NUMBER
    ,STORE_NAME STRING
    ,COUNTRY STRING
);

INSERT INTO PRODUCT_SALES
VALUES
(1, '50', 3, 'Store 1', 'Australia')
,(1, '50', 1, 'Store 2', 'Australia')
,(1, '50', 2, 'Store 3', 'New Zealand')
,(2, '25', 1, 'Store 1', 'Australia')
,(2, '25', 6, 'Store 2', 'Australia')
,(2, '25', 1, 'Store 4', 'Australia')
,(3, '15', 4, 'Store 4', 'Australia')
,(4, '33', 1, 'Store 4', 'Australia');

Suppose we’re aiming to capture the sales (PRODUCT * QUANTITY) across different stores and countries. In such a scenario, the benefit of utilising GROUP BY ALL becomes apparent – it spares us the effort of individually listing each column in the group by statement, as demonstrated in the following instance:

SELECT
    STORE_NAME
    ,COUNTRY
    ,SUM(PRICE * QUANTITY) AS SALES
FROM PRODUCT_SALES
GROUP BY ALL
;

GROUP BY ALL in Snowflake

While this example may appear straightforward, consider the potential convenience when dealing with a large number of columns within your SELECT statement. Just envision the efficiency boost that awaits in such scenarios.

You can also put GROUP BY ALL to work alongside the keywords we explored earlier. For instance:

SELECT * 
   ILIKE '%TYPE%' 
   REPLACE (UPPER(MENU_TYPE) AS MENU_TYPE) 
   RENAME (MENU_TYPE AS TYPE) 
 , COUNT(*) AS RECORD_COUNT 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU GROUP BY ALL 
;

GROUP BY ALL + All Keywords in Snowflake

Considerations

Be aware that when specifying a combination of keywords after SELECT * you cannot specify both ILIKE and EXCLUDE.

When specifying EXCLUDE with REPLACE or RENAME you must specify EXCLUDE before any other keyword:

SELECT * EXCLUDE ... REPLACE ...
SELECT * EXCLUDE ... RENAME ...
SELECT * EXCLUDE ... REPLACE ... RENAME ...

When specifying ILIKE with REPLACE or RENAME , you must specify ILIKE before any other keyword:

SELECT * ILIKE ... REPLACE
SELECT * ILIKE ... RENAME ...
SELECT * ILIKE... REPLACE ... RENAME

When specifying REPLACE and RENAME, you must specify REPLACE first:

SELECT * REPLACE ... RENAME ...

Wrap Up

Thanks for taking the time to learn more about Snowflake’s new features! We’ve introduced some awesome functions like EXCLUDERENAMEREPLACEILIKE and GROUP BY ALL, each ready to turbocharge your data management. Snowflake’s got your back with these slick functions, making data challenges a breeze. Now, armed with these tricks, you’re all set to dive into your data world with newfound confidence!

More About the Author

Guilherme Rampani

Data Engineer
Which Snowflake Table Type Should You Use? As Snowflake has developed over the years, we have seen the introduction of more and more table types, and it isn’t always ...
Snowflake External Tables: Connect Efficiently to Your Data Lake In this blog post, we will dive into the realm of Snowflake External Tables, a feature redefining data management and analysis. If you ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!