Sun. Feb 28th, 2021
Snowflake cmd batch

In this short post, we will demonstrate how you can use the cmd to run your recurrent queries.

SnowSQL is a command line client that you can run interactively as a shell, or you can run in batch mode. Commands can be referenced via a -f parameter (common on Windows) or redirected into it via stdin (common on Linux and macOS).

Using SnowSQL is very much like using OSQL or SQLCMD. Like SQLCMD though, it has a rich command language of its own with variables, auto-complete, command line history, and variable substitution.

First create a folder where you can put all your .sql files & one .bat file as the following :

The idea here is to prepare QA sql files for validation purposes, or other analytical queries, rather than executing them through the webapp or any other SQL client manually, you can do this way to automate that and save time.

In the Automated_QA.bat file , you can paste the following , please keep in mind, i’m connecting to snowflake through Okta ( federated SSO ):

@echo off
:: This CMD script provides full QA execution
ECHO ============================
ECHO Start Testing …
ECHO ============================
ECHO Connecting to Snowflake …
ECHO ============================
del tempfile >nul 2>&1
for /F %%i in ('dir /b *.sql') do @echo| set /p output= -f %%i >> tempfile
set /p myvar= < tempfile del tempfile >nul 2>&1
::ECHO %myvar%
SET a=snowsql --authenticator -a -d XXX -s XXX -w XXX -r XXX
SET b=-u
SET c=%a% %b% %myvar%
ECHO ============================
ECHO ============================

By Amine Hallam

Author & blogger on the latest data analytics technologies: Hadoop, Snowflake, Cloud ( AWS ), ETL .. etc

Leave a Reply

Your email address will not be published. Required fields are marked *