Migrating Teradata BTEQ to Snowflake Scripting

And by that I mean, do you have some BTEQ (Basic Teradata Query) scripts that you want to convert easily to Snowflake? Not sure what output language you should choose? Snowflake Scripting is a great option that won't add another language to your stack. By the end of this blog, you will know all the advantages and limitations of migrating BTEQ to Snowflake Scripting using SnowConvert for Teradata.

What is BTEQ?

It is a general-purpose, command-driven utility that lets users communicate with one or more Teradata Database systems, import and export data, and execute DML and DDL statements. On the other hand, Snowflake Scripting is a procedural logic extension for Snowflake SQL.

How can we convert it to Snowflake?

But how can we migrate a general-purpose program using a language extension such as Snowflake Scripting? Well, the truth is that there are several BTEQ commands related to CLI interaction that are not relevant in Snowflake, so we don't need to migrate them. In order to preserve the same procedural functionality, we should focus on the commands about branching the execution flow. These determine which SQL queries will be executed and which will not. For example, look at the following BTEQ script migrated using SnowConvert as a part of BlackDiamond Studio:

.LOGON dbc,dbc;

INSERT INTO PROPERTIES VALUES ("Miami", "Deluxe", 17000000);

.IF activitycount = 0 then GOTO SECTIONA
.IF activitycount >= 1 then GOTO SECTIONB

.label SECTIONA
.REMARK 'No properties inserted'
.GOTO SECTIONC

.label SECTIONB
INSERT PROPERTIES_LOG VALUES("Miami", CURRENT_TIMESTAMP)
.REMARK 'Properties inserted'

.label SECTIONC

.logoff     
.exit

As shown above, BTEQ uses GOTO commands to transfer the control to the following label with the same name depending on the if condition. BTEQ also utilizes the exit command to quit the program and return a default value. These are examples of commands used to branch the execution flow. As mentioned before, these are the most relevant for the migration to Snowflake scripting. With just these commands, we can migrate a BTEQ script to functionally equivalent output code in Snowflake for most cases.

Let's take a look at the output of this conversion could look like in Snowflake Scripting:

EXECUTE IMMEDIATE
$$
  BEGIN
	/*.LOGON dbc,dbc;*/
	INSERT INTO PUBLIC.MY_VALUES VALUES (1), (2), (3);
 
	IF (SQLROWCOUNT = 0) THEN
  	/*.label SECTIONA*/
  	/*.REMARK 'No properties inserted'*/
  	/*.label SECTIONC*/
  	/*.logoff*/
  	RETURN 0;
	END IF;
	IF (SQLROWCOUNT >= 1) THEN
  	/*.label SECTIONB*/
  	INSERT PROPERTIES_LOG VALUES("Miami", CURRENT_TIMESTAMP)
  	/*.REMARK 'Properties inserted'*/
  	/*.label SECTIONC*/
  	/*.logoff*/
  	RETURN 0;
	END IF;
 
	/*.label SECTIONA*/
	/*.REMARK 'No properties inserted'*/
	/*.label SECTIONC*/s	/*.logoff*/
    RETURN 0;
  END
$$

So what are the limitations of migrating BTEQ to Snowflake Scripting? The most significant drawback is that several BTEQ commands are related to CLI interaction such as setting configuration, executing files, and printing information. These commands are irrelevant in Snowflake Scripting and cannot be converted. Another limitation is that some features cannot be transformed easily. As an example, the error handling and the goto command features duplicate a lot of code to achieve the same functionality.

Fortunately, we can reduce the limitations by using other strategies. In the converted code above, the functionality of the source is preserved, but the BTEQ specific commands written to the CLI are removed. Furthermore, if we are sure we will run the output code in the SnowSQL console, then we can execute SnowSQL commands at the beginning or the end of the output code. In this way, we can convert the logon command, replicate variable name substitution, set output files, and configure formats.

There will be limitations though of any translation from one code language to another. When considering all possible output languages that Snowflake supports, Snowflake Scripting is one of the best options because we can run the transformed scripts natively in the database. That means a measurable improvement in the execution time. It also means the code is easier to maintain than scripts in other languages, and you can take advantage of the built-in support and maintenance provided by Snowflake.

In conclusion, not all BTEQ commands can be migrated to Snowflake Scripting because they are related to CLI interaction or stream connection. But we can get an output script functionally equivalent only by transforming the branching commands with the advantage that it can be executed natively in Snowflake.

With SnowConvert for Teradata and BlackDiamond Studio, we can convert BTEQ files to either Snowflake Scripting or Python code (utilizing the Snowflake Connector for Python).