SnowConvert - Translation Spec
  • For Teradata
  • Translation Reference
    • Teradata
      • Scripts to Snowflake SQL Translation Reference
        • COMMON STATEMENTS
          • ERROR HANDLING
          • EXIT or QUIT
          • GOTO
          • IF... THEN...
        • BTEQ
        • MLOAD
          • Import
      • Scripts To Python Translation Reference
        • BTEQ
          • REPEAT
          • USING REQUEST MODIFIER
        • FLOAD
          • BEGIN LOADING
        • MLOAD
          • BEGIN MLOAD
        • TPT
        • SnowConvert Scripts Helpers
          • Technical Documentation
      • SQL Translation Reference
        • Built-in Functions
          • CURRENT_TIMESTAMP
          • Cast to DATE using { }
          • Cast to INTERVAL datatype
          • COALESCE
          • DAYNUMBER_OF_MONTH
          • FROM_BYTES
          • GETQUERYBANDVALUE
          • JSON_CHECK
          • JSON_EXTRACT
          • JSON_TABLE
          • NEW JSON
          • NVP
          • OVERLAPS
          • PIVOT
          • P_INTERSECT
          • RANK
          • Regex functions
          • STRTOK_SPLIT_TO_TABLE
          • SUBSTRING
          • TD_UNPIVOT
          • TO_CHAR
          • XMLAGG
        • Database DBC
        • Data Types
        • DDL
          • Tables
            • WITH DEFAULT
          • Index
          • Views
        • DML
          • Delete Statement
          • Insert Statement
            • LOGGING ERRORS
          • Select Statement
            • Expand On Clause
            • Normalize
            • Reset When
          • Set Operators
          • Update Statement
          • With Modifier
        • SnowConvert Procedures Helpers
          • Cursor Helper
          • Exec Helper
          • Functional Equivalence Helpers
          • Into Helper
      • Teradata to JavaScript Translation Reference
        • GET DIAGNOSTICS EXCEPTION
        • Macros
        • Procedures
      • Teradata to Snowflake Scripting Translation Reference
        • ABORT and ROLLBACK
        • BEGIN END
        • CASE
        • CREATE MACRO
        • CREATE PROCEDURE
        • CURSOR
        • DECLARE
        • DECLARE CONDITION HANDLER
        • DML and DDL Objects
        • EXCEPTION HANDLERS
        • EXECUTE IMMEDIATE
        • EXECUTE/EXEC
        • FUNCTION OPTIONS OR DATA ACCESS
        • GET DIAGNOSTICS EXCEPTION
        • IF
        • LOCKING FOR ACCESS
        • LOOP
        • OUTPUT PARAMETERS
        • PREPARE
        • REPEAT
        • SET
        • SYSTEM_DEFINED
        • WHILE
    • Oracle
      • Sample data
      • Basic Elements of Oracle SQL
        • Data Types
          • Oracle Built-in Data Types
            • Character Data Types
              • CHAR Data type
              • NCHAR Data Type
              • VARCHAR2 Data Type
              • VARCHAR Data Type
              • NVARCHAR2 Data Type
            • Numeric Data Types
              • NUMBER Data Type
              • FLOAT Data Type
              • Floating-Point Numbers
                • BINARY_FLOAT
                • BINARY_DOUBLE
            • LONG Data Type
            • Datetime and Interval Data Types
              • DATE Data Type
              • TIMESTAMP Data Type
              • TIMESTAMP WITH TIME ZONE Data Type
              • TIMESTAMP WITH LOCAL TIME ZONE Data Type
              • INTERVAL YEAR TO MONTH Data Type
              • INTERVAL DAY TO SECOND Data Type
              • Datetime Arithmetic
                • Interval UDFs vs Snowflake native interval operation
            • LOB Data Types
              • BFILE Data Type
              • BLOB Data Type
              • CLOB Data Type
              • NCLOB Data type
            • JSON Data Type
            • Extended Data Types
            • RAW and LONG RAW Data types
            • PL SQL Data Types
              • PLS_INTEGER Data Type
              • BINARY_INTEGER Data Type
          • Rowid Data Type
            • ROWID DataType
            • UROWID Data Type
          • ANSI Data Types
          • User-Defined Types
            • REF Data Types
          • Any Types
            • ANYTYPE
            • ANYDATA
            • ANYDATASET
          • XML Types
            • XMLType
            • URI Data Types
              • HTTPURIType
              • XDBURIType
              • DBURIType
            • URIFactory Package
          • Spatial Types
            • SDO_GEOMETRY
            • SDO_TOPO_GEOMETRY
            • SDO_GEORASTER
        • Literals
          • Interval Literal
          • Interval Type and Date Type
          • Text literals
      • Pseudocolumns
        • ROWID
        • ROWNUM
      • Built-in functions
        • SnowConvert Custom UDFs
          • BFILENAME UDF
          • DATE_TO_JULIANDAYS_UDF
          • DATEADD UDF
          • DATEDIFF UDF
          • INTERVAL UDFs
            • DATEADD UDF INTERVAL
            • DATEDIFF UDF INTERVAL
          • CAST_DATE UDF
          • JSON_VALUE UDF
          • JULIAN TO GREGORIAN DATE UDF
          • MONTHS BETWEEN UDF [DEPRECATED]
          • REGEXP LIKE UDF
          • TIMESTAMP DIFF UDF
          • TRUNC (date) UDF
          • TRUNC (number) UDF
        • TO_NUMBER
        • NLSSORT
      • Built-In packages
        • DBMS_LOB
          • SUBSTR Function
        • DBMS_RANDOM
          • VALUE functions
        • DBMS_OUTPUT
          • PUT_LINE procedure
        • UTL_FILE
          • FOPEN procedure
          • PUT_LINE procedure
          • FCLOSE procedure
      • SQL Queries and Subqueries
        • Select
          • Select Flashback Query
        • Joins
          • Equijoin
          • Band Join
          • Self Join
          • Cartesian Products
          • Inner Join
          • Outer Join
          • Antijoin
          • Semijoin
      • SQL Statements
        • Alter Session
        • Alter Table
        • Create Materialized Views
        • Create Database Link
        • Create Index
        • Create Sequence
        • Create Synonym
        • Create Table
        • Create Type
          • Object Type Definition
          • Subtype Definition
          • Array Type Definition
          • Nested Table Type Definition
          • Member Function Definitions
        • Create View
        • Drop Table
      • PL/SQL to Snowflake Scripting
        • ASSIGNMENT STATEMENT
        • CALL
        • CASE
        • COLLECTIONS AND RECORDS
          • Associative Array Type Definition
          • Varray Type Definition
          • Nested Table Array Type Definition
          • Collection Methods
          • Collection Bulk Operations
            • WITH, SELECT, and BULK COLLECT INTO statements
          • Record Type Definition
        • COMPOUND STATEMENTS
        • CONTINUE
        • CREATE PROCEDURE
        • CURSOR
          • PARAMETRIZED CURSOR
          • CURSOR DECLARATION
          • Workaround for cursors using parameters or procedure variables
          • Cursor Variables
        • DECLARE
        • DEFAULT PARAMETERS
        • DML STATEMENTS
          • INSERT Statement Extension
          • MERGE Statement
          • SELECT INTO Statement
          • Work around to simulate the use of Records
        • EXIT
        • EXPRESSIONS
        • EXECUTE IMMEDIATE
        • FORALL
        • FOR LOOP
        • HELPERS
          • Bulk Cursor Helpers
        • IF
        • IS EMPTY
        • LOCK TABLE
        • LOG ERROR
        • LOOP
        • OUTPUT PARAMETERS
        • PACKAGES
          • DECLARATION
          • BODY
          • VARIABLES
          • Constants
        • PROCEDURE CALL
        • RAISE
        • RAISE_APPICATION_ERROR
        • UDF CALL
        • WHILE
      • PL/SQL to Javascript
        • Procedures
        • User defined functions
        • Packages
        • Helpers
          • EXEC Helper
          • Cursor Helper
          • Raise Helper
          • ROWTYPE Helper
          • Between operator helper
          • Like operator Helper
          • IS NULL Helper
          • Concat Value Helper
          • Package variables helper
          • Implicit Cursor attribute helper
        • Declarations
        • Control Statements
        • Conditional Compilation
        • Collections & Records
        • DDL - DML Statements
        • SQL Language Elements
        • Expressions and operators
        • Synonyms
        • Triggers
        • TYPE attribute
      • SQL*Plus
        • Archive Log
        • Attribute
        • Break
        • Btitle
        • Change
        • Column
        • Define
        • Host
        • Prompt
        • Remark
        • Set
        • Spool
        • Start
        • Whenever oserror
        • Whenever sqlerror
        • Show
        • Append
        • Accept
      • Wrapped objects
    • SQLServer
      • General Language Elements
        • COMPUTED COLUMN
        • EXECUTE
          • System Store Procedures
            • SP_RENAME
        • Collate
        • USE
        • OUTER APPLY
      • DDLs
        • Tables
        • Index
        • Views
        • Procedures
        • Functions
      • DMLs
        • Set Operators
        • Between
        • Update
        • Select
        • Insert
        • Delete
        • Merge
        • Exists
        • IN
        • Truncate
        • Drop
        • Bulk Insert
        • Common Table Expression (CTE)
        • Drops
      • Data Types
      • Statements
        • ALTER
          • TABLE
            • ADD
              • COLUMN DEFINITION
                • COLUMN CONSTRAINT
                  • FOREIGN KEY
                  • PRIMARY KEY / UNIQUE
                  • CHECK
              • TABLE CONSTRAINT
                • FOREIGN KEY
                • PRIMARY KEY
                • CHECK CONSTRAINT
                • CHECK
                • CONNECTION
                • DEFAULT
                • ON PARTITION
        • CREATE
          • FUNCTION
            • SCALAR
            • INLINE TABLE-VALUED
            • MULTI-STATEMENT TABLE-VALUED
      • Built-in functions
        • SnowConvert custom UDFs
          • OPENXML UDF
          • STR UDF
          • SWITCHOFFSET_UDF
        • Aggregate functions
          • COUNT
          • COUNT_BIG
          • SUM
        • Analytic Functions
          • LAG
        • Conversion functions
          • CONVERT
          • TRY_CONVERT
        • Data Type functions
          • DATALENGTH
        • Date & Time functions
          • AT TIME ZONE
          • SWITCHOFFSET
          • DATEADD
          • DATEDIFF
          • DATEPART
          • DATEFROMPARTS
          • DATENAME
          • DAY
          • EOMONTH
          • GETDATE
          • MONTH
          • SYSDATETIME
          • SYSUTCDATETIME
          • YEAR
        • Logical functions
          • IIF
        • Mathematical functions
          • ABS
          • ACOS
            • ACOS in JS
          • ASIN
            • ASIN in JS
          • ATAN
            • ATAN in JS
          • ATN2
            • ATAN2 in JS
          • AVG
          • CEILING
          • COS
            • COS in JS
          • COT
            • COT in JS
          • DEGREES
            • DEGREES in JS
          • EXP
            • EXP in JS
          • FLOOR
          • LOG
            • LOG in JS
          • LOG10
            • LOG10 in JS
          • PI
            • PI in JS
          • POWER
            • POW in JS
          • SQUARE
          • STDEV
          • STDEVP
          • VAR
          • POWER
          • RADIANS
            • RADIANS in JS
          • ROUND
          • SQRT
        • Metadata functions
          • DB_NAME
          • OBJECT_ID
        • Ranking functions
          • DENSE_RANK
          • RANK
          • ROW_NUMBER
        • String functions
          • ASCII
            • ASCII in JS
          • CHAR
          • CHARINDEX
          • COALESCE
          • CONCAT
          • CONCAT_WS
            • Join in JS
          • DIFFERENCE
            • DIFFERENCE in JS
          • FORMAT
            • FORMAT in JS
          • LEFT
          • LEN
          • LOWER
          • LTRIM
            • LTRIM in JS
          • NCHAR
          • PATINDEX
            • search in JS
          • QUOTENAME
            • QUOTENAME in JS
          • REPLACE
          • REPLICATE
          • REVERSE
            • reverse in JS
          • RIGHT
          • RTRIM
          • SOUNDEX
            • SOUNDEX in JS
          • SPACE
          • STR
            • STR in JS
          • STRING_ESCAPE
            • stringify in JS
          • SUBSTRING
          • TRIM
            • trim in JS
          • UPPER
        • System functions
          • FORMATMESSAGE
            • FORMATMESSAGE_UDF
          • ISNULL
          • NEWID
          • NULLIF
        • XML Functions
          • Value
          • Query
      • Built-in procedures
        • Custom User Defined Procedures
          • SP_ADDEXTENDEDPROPERTY_UDP
      • Snowflake Scripting
        • CREATE PROCEDURE
        • CASE
        • CURSOR
        • DECLARE
        • EXECUTE
        • IF
        • SET
        • DMLs
        • CALL
        • WHILE
        • DROPs
        • BEGIN and COMMIT Transaction
        • OUTPUT PARAMETERS
        • LABEL and GOTO
        • SELECT
        • TEXTIMAGE_ON
        • TRY CATCH
      • System Tables
        • SYS.FOREIGN_KEYS
      • Queries
        • TOP
Powered by GitBook
On this page
  • Functions
  • access
  • at_exit_helpers
  • colored
  • configure_log
  • drop_transient_table
  • exception_hook
  • exec
  • exec_file
  • exec_os
  • exec_sql_statement
  • expands_using_params
  • expandvar
  • expandvars
  • fast_load
  • file_exists_and_readable
  • get_argkey
  • get_error_position
  • get_from_vars_or_args_or_environment
  • import_data_to_temptable
  • import_file
  • import_file_to_temptable
  • import_reset
  • log
  • log_on
  • os
  • print_table
  • quit_application
  • read_params_args
  • readrun
  • remark
  • repeat_previous_sql_statement
  • set_default_error_level
  • set_error_level
  • simple_fast_load
  • stat
  • system
  • using
  • Classes
  • BeginLoading Class
  • Export Class
  • Import Class
  • Parameters Class
  1. Translation Reference
  2. Teradata
  3. Scripts To Python Translation Reference
  4. SnowConvert Scripts Helpers

Technical Documentation

This page contains the internal technical documentation of the snowconvert-helpers project, generated automatically by pydoc

PreviousSnowConvert Scripts HelpersNextSQL Translation Reference

Last updated 1 year ago

Functions

All the functions defined in the project.

access

access(path, mode, *, dir_fd=None, effective_ids=False, follow_symlinks=True)

Description:

Use the real uid/gid to test for access to a path.

dir_fd, effective_ids, and follow_symlinks may not be implemented on your platform. If they are unavailable, using them will raise a NotImplementedError.

Note that most operations will use the effective uid/gid, therefore this routine can be used in a suid/sgid environment to test if the invoking user has the specified access to the path.

Parameters:

  • path, Path to be tested; can be string, bytes, or a path-like

  • mode, Operating-system mode bitfield. Can be F_OK to test existence, or the inclusive-OR of R_OK, W_OK, and X_OK

  • dir_fd, If not None, it should be a file descriptor open to a directory, and path should be relative; path will then be relative to that directory

  • effective_ids, If True, access will use the effective uid/gid instead of the real uid/gid

  • follow_symlinks, If False, and the last element of the path is a symbolic link, access will examine the symbolic link itself instead of the file the link points to

at_exit_helpers

at_exit_helpers()

Description:

Executes at the exit of the execution of the script.

colored

colored(text, color='blue')

Description:

Prints colored text from the specified color.

Parameters:

  • text, The text to be printed

  • color="blue", The color to print

configure_log

configure_log(configuration_path)

Description:

Configures the logging that will be performed for any data-related execution on the snowflake connection. The log file is named 'snowflake_python_connector.log' by default.

Parameters:

  • configuration_path, The configuration path of the file that contains all the settings desired for the logging

drop_transient_table

drop_transient_table(tempTableName, con=None)

Description:

Drops the transient table with the specified name.

Parameters:

  • tempTableName, The name of the temporary table

  • con=None, The connection to be used, if None is passed it will use the last connection performed

exception_hook

exception_hook(exctype, value, tback)

Description:

Parameters:

  • exctype

  • value

  • tback

exec

exec(sql_string, using=None, con=None)

Description:

Executes a sql string using the last connection, optionally it uses arguments or an specific connection. Examples:

  • exec("SELECT * FROM USER")

  • exec("SELECT * FROM USER", con)

  • exec("SELECT * FROM CUSTOMER WHERE CUSTOMERID= %S", customer)

Parameters:

  • sql_string, The definition of the sql

  • using=None, The optional parameter that can be used in the sql passed

  • con=None, The connection to be used, if None is passed it will use the last connection performed

exec_file

exec_file(filename, con=None)

Description:

Reads the content of a file and executes the sql statements contained with the specified connection.

Parameters:

  • filename, The filename to be read and executed

  • con=None, The connection to be used, if None is passed it will use the last connection performed

exec_os

exec_os(command)

Description:

Executes a command in the operative system.

exec_sql_statement

exec_sql_statement(sql_string, con, using=None)

Description:

Executes a sql statement in the connection passed, with the optional arguments.

Parameters:

  • sql_string, The sql containing the string to be executed

  • con, The connection to be used

  • using, The optional parameters to be used in the sql execution

expands_using_params

expands_using_params(statement, params)

Description:

Expands the statement passed with the parameters.

Parameters:

  • statement, The sql containing the string to be executed

  • params, The parameters of the sql statement

expandvar

expandvar(str)

Description:

Expands the variable from the string passed.

Parameters:

  • str, The string to be expanded with the variables

expandvars

expandvars(path, params, skip_escaped=False)

Description:

Expand environment variables of form $var and ${var}. If parameter 'skip_escaped' is True, all escaped variable references (i.e. preceded by backslashes) are skipped. Unknown variables are set to 'default'. If 'default' is None, they are left unchanged.

Parameters:

  • path,

  • params,

  • skip_escaped=False,

fast_load

fast_load(target_schema, filepath, stagename, target_table_name, con=None)

Description:

Executes the fast load with the passed parameters target_schema, filepath, stagename and target_table_name.

Parameters:

  • target_schema, The name of the schema to be used in the fast load

  • filepath, The filename path to be loaded in the table

  • target_table_name, The name of the table that will have the data loaded

  • con=None, The connection to be used, if None is passed it will use the last connection performed

file_exists_and_readable

file_exists_and_readable(filename)

Description:

Parameters:

  • filename,

get_argkey

get_argkey(astr)

Description:

Gets the argument key value from the passed string. It must start with the string '--param-'

Parameters:

  • astr, The argument string to be used. The string should have a value similar to --param-column=32 and the returned string will be '32

get_error_position

get_error_position()

Description:

Gets the error position from the file using the information of the stack of the produced error.

get_from_vars_or_args_or_environment

get_from_vars_or_args_or_environment(arg_pos, variable_name, vars, args)

Description:

Gets the argument from the position specified or gets the value from the table vars or gets the environment variable name passed.

Parameters:

  • arg_pos, The argument position to be used from the arguments parameter

  • variable_name, The name of the variable to be obtained

  • vars, The hash with the variables names and values

  • args, The arguments array parameter

import_data_to_temptable

import_data_to_temptable(tempTableName, inputDataPlaceholder, con)

Description:

Imports data to a temporary table using an input data place holder.

Parameters:

  • tempTableName, The temporary table name.

  • inputDataPlaceholder, The input place holder used that is a stage in the snowflake database

  • con, The connection to be used

import_file

import_file(filename, separator=' ')

Description:

Imports the passed filename with the optional separator.

Parameters:

  • filename, The filename path to be imported

  • separator=' ', The optional separator

import_file_to_temptable

import_file_to_temptable(filename, tempTableName, columnDefinition)

Description:

Imports the file passed to a temporary table. It will use a public stage named as the temporary table with the prefix Stage_. At the end of the loading to the temporary table, it will delete the stage that was used in the process.

Parameters:

  • filename, The name of the file to be read

  • tempTableName, The name of the temporary table

  • columnDefinition, The definition of all the fields that will have the temporary table

import_reset

import_reset()

Description:

log

log(*msg, level=20, writter=None)

Description:

Prints a message to the console (standard output) or to the log file, depending on if logging is enabled

Parameters:

  • *msg, The message to print or log

  • level=20,

  • writter=None,

log_on

log_on(user=None, password=None, account=None, database=None, warehouse=None, role=None, login_timeout=10, authenticator=None)

Description:

Logs on the snowflake database with the credentials, database, warehouse, role, login_timeout and authenticator passed parameters.

Parameters:

  • user, The user of the database

  • password The password of the user of the database

  • database, The database to be connected

  • warehouse, The warehouse of the database to be connected

  • role, The role to be connected

  • login_timeout, The maximum timeout before giving error if the connection is taking too long to connect

  • authenticator, The authenticator supported value to use like SNOWFLAKE, EXTERNALBROWSER, SNOWFLAKE_JWT or OAUTH

  • token, The OAUTH or JWT token

os

os(args)

Description:

Parameters:

  • args,

print_table

print_table(dictionary)

Description:

Prints the dictionary without exposing user and password values.

Parameters:

  • dictionary,

quit_application

quit_application(code=None)

Description:

Quits the application and optionally returns the passed code.

Parameters:

  • code=None, The code to be returned after it quits

read_params_args

read_param_args(args)

Description:

Reads the parameter arguments from the passed array.

Parameters:

  • args, The arguments to be used

readrun

readrun(line, skip=0)

Description:

Reads the given filename lines and optionally skips some lines at the beginning of the file.

Parameters:

  • line, The filename to be read

  • skip=0, The lines to be skipped

remark

remark(arg)

Description:

Prints the argument.

Parameters:

  • arg, The argument to be printed

repeat_previous_sql_statement

repeat_previous_sql_statement(con=None, n=1)

Description:

Repeats the previous executed sql statement(s).

Parameters:

  • con=None, Connection if specified. If it is not passed it will use the last connection performed

  • n=1, The number of previous statements to be executed again

set_default_error_level

set_default_error_level(severity_value)

Description:

Parameters:

  • severity_value,

set_error_level

set_error_level(arg, severity_value)

Description:

Parameters:

  • arg,

  • severity_value,

simple_fast_load

simple_fast_load(con, target_schema, filepath, stagename, target_table_name)

Description:

Executes a simple fast load in the connection and the passed parameter target_schema, filepath, stagename and target table name.

Parameters:

  • arg, The connection to be used

  • target_schema, The name of the schema to be used in the fast load

  • filepath, The filename path to be loaded in the table

  • target_table_name, The name of the table that will have the data loaded

stat

stat(path, *, dir_fd=None, follow_symlinks=True)

Description:

Perform a stat system call on the given path. dir_fd and follow_symlinks may not be implemented on your platform. If they are unavailable, using them will raise a NotImplementedError. It's an error to use dir_fd or follow_symlinks when specifying path as an open file descriptor

Parameters:

  • dir_fd, If not None, it should be a file descriptor open to a directory, and path should be a relative string; path will then be relative to that directory

  • follow_symlinks, If False, and the last element of the path is a symbolic link, stat will examine the symbolic link itself instead of the file the link points to

system

system(command)

Description:

Execute the command in a subshell.

Parameters:

  • command,

using

using(*argv)

Description:

Parameters:

  • *argv,

Classes

All the classes defined in the project

BeginLoading Class

This class contains the import_file_to_tab static function which provides support for the BEGIN LOADING and associated commands in FastLoad.

import_file_to_tab()

Parameters:

  1. target_schema_table

    • the target schema (optional) and table name

  2. define_file

    • The name of the file to be read

  3. define_columns

    • The definition of all the columns for the temporary table

  4. begin_loading_columns

    • The column names to insert. Dictates the order in which values are inserted

  5. begin_loading_values

    • The list of raw insert values to convert

  6. field_delimiter

    • The field delimiter

  7. (optional) skip_header

    • The number of rows to skip

  8. (optional) input_data_place_holder

    • The location of the file in a supported cloud provider. Set parameter when the file is not stored locally

  9. (optional) con

    • The connection to be used

Export Class

Static methods in the class

  • defaults()

  • null(value=None)

  • record_mode(value=None)

  • report(file, separator=' ')

  • reset()

  • separator_string(value=None)

  • separator_width(value=None)

  • side_titles(value=None)

  • title_dashes(value=None, withValue=None)

  • title_dashes_with(value=None)

  • width(value=None)

Data and other attributes defined here

  • expandedfilename = None

  • separator = ''

Import Class

Methods in the class

  • reset()

Static methods in the class

  • file(file, separator=' ')

  • using(globals, *argv)

Data and other attributes defined in the class

  • expandedfilename = None

  • no_more_rows = False

  • read_obj = None

  • reader = None

  • separator = ' '

Parameters Class

Data and other attributes defined in the class

  • passed_variables = {}

path, Path to be examined; can be string, bytes, a path-like or open-file-descriptor int

object
object