"udaSQL" provides a DevOps focused SQL Execution Engine that allows the developer to focus on their SQL and Procedural algorithms while ensuring that the needs of the Operations team are addressed by embedding operational and query logging directly into the engine.

Many years ago I did a series of Articles on DevX called The Friday Night Project the aim of which was to assist a wide range of users to gain practical experience with developing 'Active' Web, Web service and Portlet applications that are targeted at and make best use of Teradata”.

One aspect of this was the automation of the creation and recreation of all of the SQL driven objects and artifacts that were used by the various Web, Web service and Portlet applications that were being built. While these were all relatively simple table creation (DDL) and population (DML) operations it saved a lot a time (and typing) to wrap their execution in a build automation framework such as Apache Ant, which I did using the standard Ant SQL Task.

Over time we incorporated some of this thinking directly into the IDE in the shape of the SQL Ant Wizard, which could be invoked directly from the SQL Editor within the IDE and would generate all of the standard Ant build file elements and wrap either individual SQL statements “In-Line” or allow for their inclusion as a “Source” file of SQL statements to be executed sequentially.

In late 2013 during discussions with the new SVP for Software at Teradata (Oliver Ratzesberger) we realized that the humble Ant SQL task was not going to cut it in in the new world of DevOps, which is an emerging concept that provides a bridge between traditional development groups and their operations counterparts so they can work as one contiguous unit within the IT environment. This approach emphasizes collaboration and integration between developers and operations professionals to improve predictability, efficiency and effectiveness of operational processes, which are typically automated to some degree.

As one of the first requirements of DevOps is that developers treat even the first line of code they write as being ready for production deployment with end-to-end performance analytics available for every component and application the Ant SQL task has been extended both in terms of being ready for DevOps as well as being able to be used across the Teradata Unified Data Architecture.

Introducing udaSQL

So lets think about the problem we are trying to solve first. We want a SQL Execution Engine that can work across the UDA (meaning Teradata and Aster for now). We want it to be multi-platform (Windows, MAC, Linux), which essentially means Java/JDBC based. We want error handling on by default i.e. it should stop when something goes wrong or an error code is returned (unless we explicitly tell it otherwise) so that’s one less thing for the developer to deal with. We want to execute a sequential set of SQL based operations perhaps with some level of procedural logic available (If, Then Else, Looping behavior). All of this suggests something like Apache Ant and the Ant SQL Task. However, we also want to engage in DevOps, which means Logging and lots of it both at the point of execution and where possible the point of SQL operation. So we have extended the Ant SQL Task into the Teradata udaSQL Execution Engine and the best way to explain how this works is to walk through it’s installation and usage.

Prerequisites for running udaSQL

To use udaSQL you will need to have a recent version of Java and Ant on your machine. For development purposes I use the Eclipse IDE environment, which includes Ant and provides an Ant View from which I can easily test and debug my applications. When combined with the Teradata Plug-in for Eclipse it becomes a Teradata IDE with all of the SQL features like auto complete and grammar checking. While not strictly a pre-requiste (as you may have access to a Teradata or Aster system) it's worth noting that Teradata Express and Aster Express are available for VMWare Player and VMware Fusion so you can test this out on your PC, Laptop or MAC. Currently udaSQL assumes you have git on your machine (generally true for Linux and MAC but you might need to add it if you are using Windows).

Obtaining udaSQL

The udaSQL.zip file can be downloaded from here. Download it to a temporary directory and unzip. What you get is a simple Ant build file called setup.xml, which you run as follows:

MYUSER-mac:temp MYUSER$ ant -f setup.xml
Buildfile: /Users/MYUSER/Projects/temp/setup.xml



     [exec] Result: 128
     [echo] === UDA platform version: fatal: Not a git repository (or any of the parent directories): .git ===

     [copy] Copying 4 files to /Users/MYUSER/uda/lib/runtime
     [copy] Copying 1 file to /Users/MYUSER/uda
     [copy] Copying 1 file to /Users/MYUSER/uda/lib/runtime
     [copy] Copying 1 file to /Users/MYUSER/uda/lib/runtime
     [copy] Copying 1 file to /Users/MYUSER/uda/config
     [copy] Copying 1 file to /Users/MYUSER/uda/config
     [copy] Copying 1 file to /Users/MYUSER/uda/config
     [copy] Copying 1 file to /Users/MYUSER/uda/config

    [input] Please enter the UDA Target Type (Teradata, Aster):
    [input] Please enter the UDA servername:
    [input] Please enter the UDA username:
    [input] Please enter the UDA password:
     [copy] Copying 1 file to /Users/MYUSER/uda/config
     [copy] Copying 1 file to /Users/MYUSER/uda/config
     [copy] Copying 1 file to /Users/MYUSER/uda


Total time: 1 minute 16 seconds

Note how the setup program asks some simple questions such as what UDA Target I want to work with (Teradata or Aster), the UDA Server Name (I use a Teradata Express instance on my MAC for off line work) and the Database credentials.

Setup copies the udaSQL files required into a directory called uda under the current user and creates an example udaSQL exec.xml build file based on the inputs provided which looks like this.

<?xml version="1.0"?>

<project name="HelloWorld4Teradata" basedir="." default="run">

        Import the uda.core.xml file, which provides for the majority of the Developer Platform capabilities
        This file establishes a range of ANT project level properties, macro definitions, task definitions etc
        and acts as the glue logic for UDA / Developer Platform "Applications"
    <import file="./uda.core.xml" />

    <!-- Simple Select against DBCInfo or nc_system.nc_errortable_part -->
    <target name="run" description="DBCInfo" >

        <udaSQL>SELECT * FROM DBC.DBCInfo;</udaSQL> 



Much less code than the equivalent Ant SQL task would have been.

Test udaSQL Setup

To test that udaSQL is setup correctly change to the uda directory under your user directory and run this application as follows:

MYUSER-mac:uda MYUSER$ ant -f exec.xml 
Buildfile: /Users/MYUSER/uda/exec.xml

     [echo] info|2014-07-30 14:54:20.269|Deleting log files created on or before 07/20/2014 14:07 PM ...
     [exec] Result: 128
     [exec] Result: 128
     [echo] info|2014-07-30 14:54:21.861|##
     [echo] info|2014-07-30 14:54:21.863|##     UU      UU  DDDDDD        AA           
     [echo] info|2014-07-30 14:54:21.865|##     UU      UU  DD   DD      AAAA          
     [echo] info|2014-07-30 14:54:21.866|##     UU      UU  DD    DD    AA  AA         
     [echo] info|2014-07-30 14:54:21.868|##     UU      UU  DD    DD   AA    AA        
     [echo] info|2014-07-30 14:54:21.870|##     UU      UU  DD    DD  AAAAAAAAAA       
     [echo] info|2014-07-30 14:54:21.871|##      UU    UU   DD   DD  AA        AA      
     [echo] info|2014-07-30 14:54:21.873|##       UUUUUU    DDDDDD  AA          AA     
     [echo] info|2014-07-30 14:54:21.875|##
     [echo] info|2014-07-30 14:54:21.876|##          UDA Application Platform          
     [echo] info|2014-07-30 14:54:21.879|##         (c) Copyright 2014 Teradata         
     [echo] info|2014-07-30 14:54:21.881|##
     [echo] info|2014-07-30 14:54:21.883|## Host Name:             MYUSER-mac
     [echo] info|2014-07-30 14:54:21.885|## Host Domain:           domain.company.com
     [echo] info|2014-07-30 14:54:21.886|## Host IPv4:   
     [echo] info|2014-07-30 14:54:21.890|## OS Name:               Mac OS X
     [echo] info|2014-07-30 14:54:21.892|## OS Architecture:       x86_64
     [echo] info|2014-07-30 14:54:21.894|## OS Version:            10.7.4
     [echo] info|2014-07-30 14:54:21.895|## Java Version:          1.6.0_65
     [echo] info|2014-07-30 14:54:21.899|## Ant Version:           Apache Ant(TM) version 1.8.2 compiled on June 3 2011
     [echo] info|2014-07-30 14:54:21.900|## Git Version:           git version
     [echo] info|2014-07-30 14:54:21.902|## UDA Platform Version:
     [echo] info|2014-07-30 14:54:21.905|## OS User:               MYUSER
     [echo] info|2014-07-30 14:54:21.908|## UDA Connection User:   dbc
     [echo] info|2014-07-30 14:54:21.913|## Jdbc Url:              jdbc:teradata://MyTeradataExpressInstance
     [echo] info|2014-07-30 14:54:21.917|## Jdbc Driver:           com.teradata.jdbc.TeraDriver
     [echo] info|2014-07-30 14:54:21.920|## Home Directory:        /Users/MYUSER
     [echo] info|2014-07-30 14:54:21.923|## Base Directory:        /Users/MYUSER/uda
     [echo] info|2014-07-30 14:54:21.925|## Tmp Directory:         /var/folders/4v/wgfmmgb169s5qhmp4_wf5n2r0000gn/T/
     [echo] info|2014-07-30 14:54:21.929|## Log Directory:         /Users/MYUSER/uda/log
     [echo] info|2014-07-30 14:54:21.935|## Log File:              /Users/MYUSER/uda/log/exec.xml.20140730145420325-4.log
     [echo] info|2014-07-30 14:54:21.938|## Ant File:              /Users/MYUSER/uda/exec.xml
     [echo] info|2014-07-30 14:54:21.940|## Application:           HelloWorld4Teradata
     [echo] info|2014-07-30 14:54:21.943|## App Version:           unknown
     [echo] info|2014-07-30 14:54:21.946|## App Git Dirty:         true
     [echo] info|2014-07-30 14:54:21.948|## Default Target:        run
     [echo] info|2014-07-30 14:54:21.951|## Invoked Targets:       run
     [echo] info|2014-07-30 14:54:21.954|## UDA App Instance:      20140730145420325-4
     [echo] info|2014-07-30 14:54:21.956|## UDA App Queryband:     UtilityName=udaSQL;UtilityVersion=;ApplicationName=HelloWorld4Teradata;Action=run;ClientUser=MYUSER;JobID=20140730145420325-4;Version=unknown;Production=false;udaAppLogFile=/Users/MYUSER/uda/log/exec.xml.20140730145420325-4.log;
     [echo] info|2014-07-30 14:54:21.959|##
     [echo] info|2014-07-30 14:54:21.961|## app.properties:        /Users/MYUSER/uda/config/app.properties
     [echo] info|2014-07-30 14:54:21.964|## system.properties:     /Users/MYUSER/uda/config/uda.system.properties
     [echo] info|2014-07-30 14:54:21.966|## connection.properties: /Users/MYUSER/uda/config/uda.connection.properties
     [echo] info|2014-07-30 14:54:21.969|##
     [echo] info|2014-07-30 14:54:21.972|## udasql lib:            /Users/MYUSER/uda/lib/runtime/udasql.jar
     [echo] info|2014-07-30 14:54:21.974|## antcontrib lib:        /Users/MYUSER/uda/lib/runtime/ant-contrib.jar
  [UdaInit] main 2014-07-30 14:54:22,089 INFO [Starting UdaInit] 
   [UdaSQL] SET QUERY_BAND = 'UtilityName=udaSQL;UtilityVersion=;ApplicationName=HelloWorld4Teradata;Action=run;ClientUser=MYUSER;JobID=20140730145420325-4;Version=unknown;Production=false;udaAppLogFile=/Users/MYUSER/uda/log/exec.xml.20140730145420325-4.log;' FOR SESSION;
   [UdaSQL] main 2014-07-30 14:54:23,226 INFO [SELECT * FROM DBC.DBCInfo;] 
   [UdaSQL] InfoKey,InfoData
   [UdaSQL] main 2014-07-30 14:54:23,326 INFO [SQL completed in 0 seconds and returned 3 records] 

Total time: 3 seconds

Now that's a lot of very detailed logging information that was automatically generated by the udaSQL Execution Engine (uda.core.xml and udasql.jar).

Dissecting the udaSQL Log

Lets tease the log apart to see what the underlying udaSQL Execution Engine is doing on our behalf.

The run starts by creating a log directory within the MYUSER/uda directory (if required) and deletes any old log files (defaults to those older than 90 days). Ignore the two "Result: 128" lines for now (it's just udaSQL trying to find this project in git, which is the default version control system for now although we will look at how we expand this later).

    [mkdir] Created dir: /Users/MYUSER/uda/log
    [echo] info|2014-07-30 12:07:56.461|Deleting log files created on or before 05/01/2014 12:05 PM ...
    [exec] Result: 128
    [exec] Result: 128

This if followed by a UDA Splash Screen along with display and logging of some local properties such as the Host and Domain names, IP address, OS information, Ant information, etc, which is logged only to the local file system as part of normal debugging practice.

Where the log gets interesting from a broader Opperations perspective is when we see the UDA App Instance, which is the specific timestamp of this run (in yyyyMMddHHmmssSSS format) followed with a locally maintained run number to give a unique instance number for this execution of the application that gets mapped into the JobID within the Queryband that will be applied if this is being executed against a Teradata databaseand UDA App Queryband values:

     [echo] info|2014-07-30 14:54:21.954|## UDA App Instance:      20140730145420325-4     

The Queryband that will be applied to any Teradata session is displayed next and you can see how this follows the normal queryband defaults such as indicating the UtilityName and Version, the ApplicationName and Action, the JobID, etc. Note the Version of the application is unknown as the 

     [echo] info|2014-07-30 14:54:21.956|## UDA App Queryband:     UtilityName=udaSQL;UtilityVersion=;ApplicationName=HelloWorld4Teradata;Action=run;ClientUser=MYUSER;JobID=20140730145420325-4;Version=unknown;Production=false;udaAppLogFile=/Users/MYUSER/uda/log/exec.xml.20140730145420325-4.log;

Finally we get into the actual application execution starting with UdaInit, which establishes all of the environment displayed above:

  [UdaInit] main 2014-07-30 12:07:58,301 INFO [Starting UdaInit]   

UdaSQL establishes the connection to the underlying UDA component, which in this case is a Teradata so it also establishes a Session level Querry Band. 

     [UdaSQL] SET QUERY_BAND = 'UtilityName=udaSQL;UtilityVersion=;ApplicationName=HelloWorld4Teradata;Action=run;ClientUser=MYUSER;JobID=20140730120756515-0;Version=unknown;Production=false;udaAppLogFile=/Users/MYUSER/uda/log/exec.xml.20140730120756515-0.log;' FOR SESSION;

Runing the SQL statement "SELECT * FROM DBC.DBCInfo;" returns the column headers (InfoKey, InfoData) and the DBCInfo for my Teradata Express Instance.

   [UdaSQL] main 2014-07-30 12:07:59,356 INFO [SELECT * FROM DBC.DBCInfo;] 
   [UdaSQL] InfoKey,InfoData
   [UdaSQL] main 2014-07-30 12:07:59,397 INFO [SQL completed in 0 seconds and returned 3 records] 

Best practice has the application calling UdaExit to explicitly remove the query band (as opposed to just closing the connection, which would do it implicitly).


queryband Attribute

If all the default Query Band information is still not enough for your application you can add a Query Band of your own across the entire application as so:

    <udaInit queryband="AppLevelQB=MyAppQB;"/>

Or if you only one it added to an individual udaSQL Task as so:

    <udaSQL queryband="TaskLevelQB=MyTaskQB;">SELECT * FROM TZA_USER.DBCInfo;</udaSQL>

udaSQL Features

OK enough about the DevOps credentials of the udaSQL Execution Engine, lets see what might make it better than just an Ant SQL Task or something like BTEQ.

Src and resultProperty attribute’s combined with Volatile Tables.

Similarly to the Ant SQL Task udaSQL will allow you to specify a “src” file within which you can have any number of SQL statements that will be executed and committed in sequence. So consider the following sql file (CreateAndPopulateVolatileTable.sql) that creates a Volatile table under the TZA_USER with the same format and contents as DBC.DNCInfo and then adds a couple of extra rows to it. 

	SELECT t1.InfoKey, t1.InfoData FROM DBC.DBCInfo t1

INSERT INTO TZA_USER.DBCInfo VALUES ('Quoted Delimiter',';');

To invoke this all that is required is a single udaSQL line like this.

    <udaSQL src="./src/sql/CreateAndPopulateVolatileTable.sql"></udaSQL> 

Not particularly useful on it’s own as if this is all we do then the Volatile Table is simply dropped when the udaSQL session completes. However, if we add in some more operations or tasks into our application such as:


Then we can continue to use this Volatile Table throughout the rest of our application. While a simple select is not particularly interesting note that we are able to get back values from this Volatile Table (or a permanent one) and this can be used in combination with the resultProperty attribute to provide for dynamic configuration of subsequent operations within the application. So adding the following line:

    <udaSQL resultProperty="database.name">SELECT InfoData FROM TZA_USER.DBCInfo WHERE InfoKey LIKE 'database.name';</udaSQL> 

Adds the property 'database.name' to the context of the running application and populates it with the value contained in the InfoData column of the row identified by the clause "WHERE InfoKey LIKE ‘database.name’", which we can clearly manipulate in any way desired. Once this immutable property ‘database.name’ is loaded into the application context we can start to use as shown below:

    <udaSQL>SELECT * FROM ${database.name}.ZipCodeRiskFactors;</udaSQL>

maxRows attribute

maxRows is actually a JDBC property, which has been exposed as a udaSQL attribute and has a couple of uses. Firstly if we simply only want to get some limited number of rows, say 10, because that is all we require from a given query then we might have issued the previous query as:

    <udaSQL maxRows="10">SELECT * FROM ${database.name}.ZipCodeRiskFactors;</udaSQL>

However, a more interesting use case is where we want to conduct some form of Performance analysis or Benchmark on the underlying database, discounting the client side and network aspects as much as possible as so:

    <udaSQL onError="continue">DROP TABLE TZA_DB.terasort;</udaSQL> 
    <udaSQL src="./src/sql/RunTeraSort.sql"></udaSQL> 
    <udaSQL maxRows="1">SELECT * from tza_db.terasort order by sort_rec</udaSQL> 

So here we use the onError=”continue” Ant SQL attribute to ensure we continue even if the TZA_DB.terasort table does not exist (first run through perhaps). We ‘src’ in the RunTeraSort.sql file which uses a UDF to create a suitably large table with a single column of random text and then use a simple select with an ORDER BY to make the database do a lot of processing with a maxRows of 1 so as to minimize the effect of the Client and Network components.

Ant SQL "Print" Attributes

The Ant SQL Task has a number of "Print" properties that can be used for display and output formating purposes, which are best illustrated by example starting with our simple DBCInfo Select:

      <udaSQL >SELECT * from DBC.DBCInfo;</udaSQL> 
 Which displays it's output as so, with the SQL we are executing, the column headers, row data and some trailer info (duration and row count):
      [UdaSQL] Thread-1 2014-07-31 13:24:56,786 INFO [SELECT * from DBC.DBCInfo;] 
      [UdaSQL] InfoKey,InfoData
      [UdaSQL] VERSION,
      [UdaSQL] RELEASE,
      [UdaSQL] Thread-1 2014-07-31 13:24:56,831 INFO [SQL completed in 0 seconds and returned 3 records]   	 

In cases where you might have sensitive data in the SQL that should not be displayed or logged to a file it can be turned off as so:

    <udaSQL showsql="false">SELECT * from DBC.DBCInfo;</udaSQL> 

Which means you only display the header, row and trailer information:

    [UdaSQL] Thread-1 2014-07-31 13:24:56,906 INFO [showsql = false]
    [UdaSQL] InfoKey,InfoData
    [UdaSQL] Thread-1 2014-07-31 13:24:56,936 INFO [SQL completed in 0 seconds and returned 3 records]

If your row data is senisitve that can be turned off as so:

    <udaSQL print="false">SELECT * from DBC.DBCInfo;</udaSQL> 

Giving you a not particualrly interesting display but at least tha fact that the query ran is recorded.

   [UdaSQL] Thread-1 2014-07-31 13:40:24,348 INFO [SELECT * from DBC.DBCInfo;] 
   [UdaSQL] Thread-1 2014-07-31 13:40:24,381 INFO [SQL completed in 0 seconds and returned 3 records] 

The showheaders and showtrailers attributes can be used to further reduce the amount displayed as so:

   <udaSQL showheaders="false" showtrailers="false">SELECT * from DBC.DBCInfo;</udaSQL> 

Which would display as follows:


 But gets really interesting when combined with the ability to output the results of a query to a file as so:

    <udaSQL output="output.csv" showheaders="false">SELECT * from DBC.DBCInfo;</udaSQL> 

Which means you can get a CSV file with the rows from a table in it, essentially load ready as so:



So udaSQL provides an interesting alternative to BTEQ for Teradata and nCLI for Aster and extends the capabilities of the Ant SQL Task into the world of DavOps.

In upcoming articles we will start to create some example applications that will illustrate how udaSQL might be used beyond the Hello World application of SELECT * from DBC.DBCInfo. 

ulrich 51 comments Joined 09/09
06 Aug 2014

Hi Mike,
I am not able to see the attached udaSQL.zip file. 
Is it gone?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

MikeC 21 comments Joined 03/09
06 Aug 2014

Zip file turned out to be slightly larger that is allowed as an attachment so it is now available from the download/tools area of DevX.

pauldancer 4 comments Joined 05/09
18 Aug 2014

Hi Mike,
This looks very interesting!
Can I assume that to create a login for LDAP authentication I'd need to add:


to the Server name when prompted?
[input] Please enter the UDA servername:


That seems to work as it just gets appended to the JDBC URL.

MikeC 21 comments Joined 03/09
18 Aug 2014

That should work as all we are doing in the setup.xml file is prepending the "jdbc:teradata://" to the server name to create the JDBC URL. You could also simply edit the uda.connetion.properties file directly ($USER_HOME}/uda/config) to setup the uda.jdbc.url as you want.
This is an area that I know needs improvement as username and password are currently in clear text (need either localised encryption or centralised password management).

cw185042 1 comment Joined 04/11
15 May 2015

Hi Mike,
Is the current version capable of using TD Wallet?

Chris Weaver

MikeC 21 comments Joined 03/09
17 May 2015

Unfortunately no. TDWalet is C based and does not have a 100% Pure Java Implementation so that is a gap in the implementation at this point.

davidtzoor 2 comments Joined 06/14
11 Nov 2015

This does seem very interesting and useful, but one small request: Could you define UDA at some point in the article? Thank you!

MikeC 21 comments Joined 03/09
11 Nov 2015

There is whole channel on DevX devoted to UDA http://developer.teradata.com/uda, but basically it's a marketing term that encompases the use of a number of different components (Teradata, Aster, Hadoop, Presto, Real Time Processing) in combination in order to create business applications and processes that favour the various streangths of the different components. Not all components will necessarily be involved (2, 3, 4 out of the set) in any given implementaion as it depends on the particualt use case.
udaSQL was named as part of this approach as it currently supports 2 out of the set (Teradata and Aster) and through QueryGrid can reach out beyond this pair to the larger environment. As an Ant (Java) based approach it relies on the existence of a JDBC driver for access and as these become available for other UDA components we can look at updating it's capabilities.
There is another approach based on the Python language the follows the same DevOps principles described above that may also be of interest http://developer.teradata.com/tools/reference/teradata-python-module. 

hxs2296 1 comment Joined 01/16
27 Jan 2016

For LDAP use this variant:

udaExec.connect(method="odbc", system="your_td_server",

        username="your_ldap_id", password="your_ldap_pwd", MechanismName='LDAP')

You must sign in to leave a comment.