It’s been few years since I have been working on HIVE, Impala, Spark SQL, PySpark, Redshift and in the journey so far I have migrated many applications in different RDBMS like Teradata, Netezza, Oracle to cloud or on-premise Hadoop environment for various clients. I have seen the change in client preferences from Hive to ACID Hive to Spark SQL to PySpark. In this post I will share my observation so far when it comes to migrating existing Teradata application to Hadoop. First question that shall come to your mind is that if you really want to move to Hadoop ? Few common reasons are: 1)
While calculating measures you may want to convert seconds to TIME. Say you want to know how many HOURS,MINUTES & Seconds constitute this number. Example you may want to convert 500 seconds to hh:mi:ss format. This can be done easily by using “INTERVALs”. Let’s quickly see how to do it This means 5000 seconds means 1 hour 23 minutes and 20 seconds.Another way of writing same command: Let’s increase the value of number (i.e. seconds) This means 500000 seconds constitutes 5 Days 18 Hours 53 Minutes and 20 Seconds.You can change the INTERVAL RANGE to get output in desired format. Like I just want output
If writing a typical TPT script is tedious task for you then you may try using existing TPT templates to quickly write a TPT script. Teradata provides list of templates for all the TPT operators which you can use to quickly create a TPT script. In this post, we will see how to export data from a Teradata table using default templates available in tbuild. Below is the TPT script content [exp_Customer.tpt]. I am using just 2 “OPERATOR” in the TPT script and will use below variable file to pass parameters for corresponding operators. Below is the variable file content [var_Customer.txt]. You can check out
Recently someone in my team asked me how to fetch column list along with datatype from VIEW definition via jdbc connection. I proposed couple of solutions to him however due to existing code base he was looking for solution using “HELP” command only. Now if you know there are multiple HELP command which can give insight into object details. For more details you can read this post- Teradata HELP commands. However for views you may get column names but datatype may be missing. Since the expected solution is via HELP command only, I proposed to create a volatile table from view and use HELP TABLE
“An owner referenced by user does not have SELECT WITH GRANT OPTION access to DB_Name.Table_Name.Col_Name “. This is very common error related with privileges granted to objects in Teradata. So first thing you must understand is this error related with insufficient privileges. In this post, we will see how this error can come up while working with views on Teradata. Whenever we create VIEWS in Teradata on top of base Tables, we must grant permission to VIEW database on Table database. This is required because VIEW has to access underlying TABLE and if does not have proper permissions it won’t be able to access it.
In most of the Teradata environment I have seen the preferred medium to call BTEQ is shell script. It means that Teradata developer will write a shell script which will invoke BTEQ utility to run SQL commands on Teradata. In this post, we will see how to write a sample BTEQ script using Shell and run it on Teradata. To start with, I would like to discuss why we shall pick Shell script to call BTEQ and why not directly the BTEQ script. BTEQ supports plain ANSI SQL with few TD extension to SQL. This means that PL/SQL constructs like CURSOR , conditional statements, logical