SnowConvert Scripts Helpers

SnowConvert Helpers is a set of classes with functions designed to facilitate the conversion of Teradata script files to Python files that Snowflake can interpret.

SnowConvert for Teradata can take in any Teradata SQL or scripts (BTEQ, FastLoad, MultiLoad, and TPump) and convert them to functionally equivalent Snowflake SQL, JavaScript embedded in Snowflake SQL, and Python. Any output Python code from SnowConvert will call functions from these helper classes to complete the conversion and create a functionally equivalent output in Snowflake.

The Snowflake Connector for Python will also be called in order to connect to your Snowflake account and run the output Python code created by SnowConvert.

The latest version information of the package can be found here.

The Python packagesnowconvert-helpers supports Python versions 3.6, 3.7, 3.8, and 3.9.

Script Migration

Source

Suppose you have the following BTEQ code to be migrated.

IN -> Teradata_01.bteq

insert into table1 values(1, 2);
insert into table1 values(3, 4);
insert into table1 values(5, 6);

Output

You should get an output like the one below.

The log_onfunction parameters ('user', 'password', 'account', 'database', 'warehouse', 'role', 'token') should be defined by the user.

OUT -> Teradata_01.py

#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***
 
import os
import sys
import snowconvert.helpers
from snowconvert.helpers import Export
from snowconvert.helpers import exec
from snowconvert.helpers import BeginLoading
con = None
def main():
  snowconvert.helpers.configure_log()
  con = snowconvert.helpers.log_on()
  exec("""
    INSERT INTO table1
    VALUES (1, 2)
    """)
  exec("""
    INSERT INTO table1
    VALUES (3, 4)
    """)
  exec("""
    INSERT INTO table1
    VALUES (5, 6)
    """)
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

Getting Started

To install the package, you should run the following command in your python environment. If you're not familiar with installing packages in Python, visit the following page on python packages (https://packaging.python.org/tutorials/installing-packages/).

pip install snowconvert-helpers

Once your package is installed, you will be able to run the script migrated code in Python.

Run the code

To run the migrated code, you just have to open the Command Prompt or the Terminal and execute the following command.

python sample_BTEQ.py

If the script has no errors, you will get in your console an output like the one below.

Executing: INSERT INTO PUBLIC.table1 VALUES (1, 2).
Printing Result Set:
number of rows inserted
1

Executing: INSERT INTO PUBLIC.table1 VALUES (3, 4).
Printing Result Set:
number of rows inserted
1

Executing: INSERT INTO PUBLIC.table1 VALUES (5, 6).
Printing Result Set:
number of rows inserted
1

Error Code 0
Script done >>>>>>>>>>>>>>>>>>>>
Error Code 0

Passing connection parameters

There are several ways to pass the connection parameters to the connection of the database:

• As parameters in the function call snowconvert.helpers.log_on inside the python file.

• As positional parameters with the specific order of user, password, account, database, warehouse, and role when the python is being executed from the command line.

• As named parameters with no order restriction of SNOW_USER, SNOW_PASSWORD, SNOW_ACCOUNT, SNOW_DATABASE, SNOW_WAREHOUSE, SNOW_ROLE, SNOW_QUERYTAG, SNOWAUTHENTICATOR and SNOWTOKEN when the python is being executed from the command line and any of them are passed like --param-VARNAME=VALUE.

• As environment variables named SNOW_USER, SNOW_PASSWORD, SNOW_ACCOUNT, SNOW_DATABASE, SNOW_WAREHOUSE, SNOW_ROLE, SNOW_QUERYTAG, SNOWAUTHENTICATOR and SNOWTOKEN before python execution.

The previous order specified is the way to determine the precedence.

Parameters in the function call

They can be set as positional parameters in the function call as follows.

sample_BTEQ.py

   .....
   con = snowconvert.helpers.log_on(
     'myuser',
     'mypassword',
     'myaccount',
     'mydatabase',
     'mywarehouse',
     'myrole',
     5,
     'myauthenticator',
     'mytoken')
   .....

Or they can be set any of the named parameters in any order in the function call as follows.

sample_BTEQ.py

   .....
   con = snowconvert.helpers.log_on(
     account:'myaccount',
     password:'mypassword',
     user:'myuser',     
     warehouse:'mywarehouse',
     login_timeout:5,
     authenticator:'myauthenticator',
     toke:'mytoken')
   .....

Positional parameters

They need to be set in the specific order in the command line as follows.

python sample_BTEQ.py myuser mypassword myaccount mydatabase mywarehouse myrole myauthenticator mytokenr

Or they can be set only some of the parameters but always starting with the user parameter as follows.

python sample_BTEQ.py myuser mypassword myaccount

Named parameters

They can be set any of the named parameters in any order in the command line as follows (use a single line, multiline shown for readability reasons).

python sample_BTEQ.py --param-SNOW_WAREHOUSE=mywarehouse 
  --param-SNOW_ROLE=myrole 
  --param-SNOW_PASSWORD=mypassword 
  --param-SNOW_USER=myuser 
  --param-SNOW_QUERYTAG=mytag 
  --param-SNOW_ACCOUNT=myaccount
  --param-SNOW_DATABASE=mydatabase 
  --param-SNOW_AUTHENTICATOR=myauthenticator
  --param-SNOW_TOKEN=mytoken
  --param-PRIVATE_KEY_PATH=myprivatekey
  --param-PRIVATE_KEY_PASSWORD=myprivatekeypassword

Environment variables

Before calling the python script, any of the following environment variables can be set:

• SNOW_USER

• SNOW_PASSWORD

• SNOW_ACCOUNT

• SNOW_DATABASE

• SNOW_WAREHOUSE

• SNOW_ROLE

• SNOW_QUERYTAG

• SNOW_AUTHENTICATOR

• SNOW_TOKEN

• PRIVATE_KEY_PATH

• PRIVATE_KEY_PASSWORD

Key Pair Authentication

The log_on function can also support the key pair authetication process. Review the following Snowflake documentation for more information about the key creation . Please notice the required parameters:

log_on(

user='YOUR_USER',

account='YOUR_ACCOUNT',

role = 'YOUR_ROLE',

warehouse = 'YOUR_WAREHOUSE',

database = 'YOUR_DATABASE',

private_key_path='/YOUR_PATH/rsa_key.p8',

private_key_password='YOUR_PASSWORD')

Example of passing environment variables

Here is an example of using SNOW_AUTHENTICATOR, SNOW_USER and SNOW_PASSWORD. They must be defined before running the output python file and then run the python generated file.

Windows

SET SNOW_AUTHENTICATOR=VALUE
SET SNOW_USER=myuser
SET SNOW_PASSWORD=mypassword
python sample_BTEQ.py

Linux/Mac

export SNOW_AUTHENTICATOR=VALUE
export SNOW_USER=myuser
export SNOW_PASSWORD=mypassword
python3 sample_BTEQ.py

Enabling Logging

To enable logging, you should enable an environment variable called SNOW_LOGGING set as true.

Then, if you want to customize the logging configuration you can pass a parameter to the snowconvert.helpers.configure_log() method like this:

snowconvert.helpers.configure_log("SOMEPATH.conf")

The configuration file should contain the next structure. For more information about python logging, click here

[loggers]
keys=root

[handlers]
keys=consoleHandler

[formatters]
keys=simpleFormatter

[logger_root]
level=DEBUG
handlers=consoleHandler

[logger_simpleExample]
level=DEBUG
handlers=consoleHandler
qualname=simpleExample
propagate=0

[handler_consoleHandler]
class=FileHandler
level=DEBUG
formatter=simpleFormatter
args=('python2.log', 'w')

[formatter_simpleFormatter]
format=%(asctime)s -%(levelname)s - %(message)s

Snowflake

Once any migrated code you have been executed, you can go to Snowflake and check your changes or deployments.

select * from PUBLIC.table1;

You will be able to see the rows you have inserted in the example above.

Query result

Local Helpers Documentation

First of all, it is required to install the python package named pydoc (Available since version 2.0.2 of snowconvert-helpers).

pip install pydoc

Then in order to display the python documentation of the package snowconvert-helpers, you should go to a folder where you have the converted output code and you have a python output.

D:\bteq\Output>dir

 Volume in drive D is Storage
 Volume Serial Number is 203C-168C

 Directory of D:\bteq\Output

05/25/2021  03:55 PM    <DIR>          .
05/25/2021  03:55 PM    <DIR>          ..
05/25/2021  03:55 PM               630 input_BTEQ.py
               1 File(s)            630 bytes
               2 Dir(s)  1,510,686,502,912 bytes free

Located in this directory you need to run:

python -m pydoc -b

The console will open your preferred browser with the HTML help of the documentation for all the installed packages.

D:\bteq\Output>python -m pydoc -b
Server ready at http://localhost:61355/
Server commands: [b]rowser, [q]uit
server>

This will open the browser with the documentation of your code like:

Home page for the generated local documentation

Scroll thru the end of the page to see the installed packages. And you will see something similar to:

Local installed packages documentation index

Clicking in the SnowConvert(package) you will see something like:

Home page for the snowconvert-helpers documentation

Clicking in the module helpers will display a screen similar to:

Home page for helpers module

Then you can scroll thru the functions and classes of the module.

Functions documentation

Known Issues

No issues were found.

Related EWIs

No related EWIs.