Teradata User Installed File (UIF)

VMPlayer/VMWare Workstation

Teradata Express for VMWare Player

PS S:\Virtual_Machines\Teradata\TDExpress16.20_Sles11>Name
--------------------------------------------------
TDExpress16.20_Sles11-disk1.vmdk
TDExpress16.20_Sles11-disk2.vmdk
TDExpress16.20_Sles11-disk3.vmdk
TDExpress16.20_Sles11.vmx

Secure Network Access to the Teradata VM

Start Teradata VM and Test Connectivity

TDExpress1620_Sles11:~ # bteq
Teradata BTEQ 16.20.00.04 for LINUX. PID: 25476
Copyright 1984–2018, Teradata Corporation. ALL RIGHTS RESERVED.
Enter your logon or BTEQ command:
Password:dbc*** Logon successfully completed.
*** Teradata Database Release is 16.20.32.01
*** Teradata Database Version is 16.20.32.01
*** Transaction Semantics are BTET.
*** Session Character Set Name is ‘ASCII’.
*** Total elapsed time was 1 second.
BTEQ — Enter your SQL request or BTEQ command:select date*** Query completed. One row found. One column returned.** Total elapsed time was 1 second.

TERADATA SCRIPT Table Operator

SELECT DISTINCT *SCRIPT_COMMAND('whoami')RETURNS ('stdout VARCHAR(50)');*** Query completed. One row found. One column returned.
*** Total elapsed time was 3 seconds.
stdout
--------------------------------------------------
tdatuser
"The text included in the SCRIPT_COMMAND clause runs in its own shell inside a temporary working directory.........installed script files run in protected mode (outside of the Teradata Database). The scripts are run as Tdatuser."
SELECT DISTINCT *FROM SCRIPT (SCRIPT_COMMAND('echo "import sys
print(sys.version)" | /usr/bin/python'
RETURNS (‘stdout VARCHAR(100)’);*** Query completed. One row found. One column returned.
*** Total elapsed time was 4 seconds.
stdout
--------------------------------------------------
(2, 6, 9, 'final', 0)

TERADATA SCRIPT Operator and Python

Creating a Teradata SHA256 Python UIF

CREATE DATABASE MYPROJ FROM DBC AS PERM=2E9;
--This grants all rights on the sandpit.  In a Prod environment, --grants would not be this openGRANT ALL ON MYPROJ TO DBC;--Required for UIF Maintenance/InstallationGRANT CREATE EXTERNAL PROCEDURE ON MYPROJ TO DBC;
GRANT EXECUTE FUNCTION on TD_SYSFNLIB.SCRIPT TO DBC;
CREATE SET TABLE MYPROJ.EMAILS,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
EMAIL_ADDR VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFICPRIMARY INDEX ( EMAIL_ADDR );
INSERT INTO MYPROJ.EMAILS VALUES ('myemail@test.com') ;
INSERT INTO MYPROJ.EMAILS VALUES ('anotheremail@test.com') ;
import hashlibdef sha256_hash(raw_data):
data = []
for rec in raw_data:
rec = rec.strip()
if rec:
data.append("\t".join([rec,hashlib.sha256(rec).hexdigest()]))
return data
if __name__ == "__main__":
emails = ["myemail@test.com", "anotheremail@test.com"]
print("\n".join(sha256_hash(emails)))
myemail@test.com f1614ba6bcc183e5836483c0c48d1bf932ab1fe338d0ef097d18555a3a2614bdanotheremail@test.com   c23cd2934c7912140abf66f3f2d6e0f7cb7c17de14393a0daa5d9fe618a1714d
#!/usr/bin/python
import sys
import hashlib
for rec in sys.stdin:
rec = rec.strip().split("\t")
if rec:
print("\t".join([rec[0],hashlib.sha256(rec[0]).hexdigest()]))
.logon dbc;
<enter password>
DATABASE MYPROJ;
SET SESSION SEARCHUIFDBPATH = MYPROJ;
CALL SYSUIF.INSTALL_FILE('sha256','sha256.py','cz!sha256.py');
HELP DATABASE MYPROJ;
TDExpress1620_Sles11:/ # cd /root/Desktop/uif_scriptsTDExpress1620_Sles11:~/Desktop/uif_scripts # bteqTeradata BTEQ 16.20.00.04 for LINUX. PID: 12730Copyright 1984-2018, Teradata Corporation. ALL RIGHTS RESERVED.
Enter your logon or BTEQ command:
.logon dbc
Password:
*** Logon successfully completed.
*** Teradata Database Release is 16.20.32.01
*** Teradata Database Version is 16.20.32.01
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command: DATABASE MYPROJ;*** New default database accepted.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command: SET SESSION SEARCHUIFDBPATH = MYPROJ;*** Session UIF Search Database Path has been set.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command: CALL SYSUIF.INSTALL_FILE('sha256','sha256.py','cz!sha256.py');*** Procedure has been executed.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command: HELP DATABASE MYPROJ;*** Help information returned. 3 rows.
*** Total elapsed time was 1 second.
Table/View/Macro name Kind Comment
------------------------------ ---- -------
EMAILS T ?
sha256 Z ?
SET SESSION SEARCHUIFDBPATH = MYPROJ;
SEL 
SCR_OPER.RAW_EMAIL
,SCR_OPER.HASHED_EMAIL
FROM SCRIPT (ON (sel email_addr FROM myproj.emails)
SCRIPT_COMMAND('./MYPROJ/sha256.py')
RETURNS ('RAW_EMAIL VARCHAR(55)' ,' HASHED_EMAIL VARCHAR(100)')
) AS SCR_OPER
RAW_EMAIL               HASHED_EMAIL
anotheremail@test.com c23cd2934c7912140abf66f3f2d6e0f7cb7c17de14393a0daa5d9fe618a1714d
myemail@test.com f1614ba6bcc183e5836483c0c48d1bf932ab1fe338d0ef097d18555a3a2614bd

Conclusion and Extended Topics

--

--

Learner. Interests include Cloud and Devops technologies.

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store