ExportIntoTable – breaking changes

Version: 5.10.13 

This article summarizes:

• Changes in ExportIntoTable where the exported dataset may be empty.  
• Changes in ExportIntoTable default behaviour when appending data 

Background

As an analytical platform, DataJet has limited support for empty tables (i.e., tables with no records).    Scripts and processes must be developed to manage these necessary limitations.

• Greater control is required over append processes – this is to better support upserts (where data is both updated and inserted) on very large and very wide tables.
• New default flags have been added to ExportIntoTable to add greater control and visibility over the creation of empty tables and the appending of data.

Change in default behaviour

Empty Datasets

Previously, an empty dataset would create a table with no records and no fields.   The default behaviour is now to create a table with no records, but with fields.  It is possible to keep legacy behaviour using flags.

NOTE: This may take noticeably longer, depending on the number of fields in the table.

NOTE: This may impact existing scripts.

Appending

Previously, appending data would allow unbalanced tables to be created without raising an error. (An unbalanced table is a table where the field definition in the base table does not exactly match the field definition in the data that is being appended).   Appending mismatched field definitions will now raise an error.   

It is still possible to create an unbalanced table (there are engineering scenarios in which this is desirable) but it must now be explicitly configured.

NOTE: This may result in existing scripts raising an error where previously they did not.

Tables with no records

DataJet does not support tables with 0 records, even though some API methods allow them to be created.   

Any operations involving tables with no records are unsupported and scripts which allow empty tables to be created should be modified to use conditional processing to either avoid their creation or avoid subsequent operations.

If this is not possible, the ignoreIfEmpty and abandonOnEmpty flags can be used to mimic legacy behaviour.   However, there remains the risk of subsequent methods failing and any such scripts should be re-tested accordingly.

Conditional processing

Use IF…ELSE…ENDIF methods, along with:

• empty:  If Empty(Table)
• exists: Exists([Objectname])
• Count([Datasetname],1) ~~ X   (~~ = operator,  X = value)

{empty example}

{exists example}

{count example}

ExportIntoTable with domain of 0 records

Existing Scripts

Existing scripts which are required to operate exactly as before will need to add “abandonOnEmpty” =true to ExportIntoTable methods.   This will create a table with no records and no fields (same behaviour as v8.16 and earlier).

Upsert Scripts

Upsert scripts should do the following: 

Load BASE table

Load DELTA Table

Join BASE to DELTA

Create “RecordsToUpsert” domain on BASE table

If Count([BASE.RecordsToUpsert],1)=0

     ExportToTable  APPEND DELTA to BASE

Else

     ExportToTable  [BASE.RecordsToUpsert] into TEMPTABLE

     Delete BASE

     Rename TEMPTABLE to BASE

     ExportToTable  APPEND DELTA to BASE

End If

No flags are required – default ExportIntoTable behaviour is sufficient.

General Principle for new scripts

New scripts should use the if(exists([TableName])) conditional statement to explicitly handle scenarios where a table may or may not be created:

To force creation of an empty table with no records but with fields, do the following:

ExportIntoTable TABLE1, all=true,[empty dataset]

NOTE: The resulting table is not valid for analysis.

To only create a table if there are records in the dataset, do the following 

ExportIntoTable TABLE1, ignoreIfEmpty=false, empty dataset]

If Exists([TABLE1])

     //Do something on TABLE1

Else

     //Handle scenario where table doesn’t exist

End If

To create a table with no records and no fields, do the following:

ExportIntoTable TABLE1, [empty dataset], abandonOnEmpty=true

NOTE: The resulting table is not valid for analysis.

Appending Data with mismatched field definitions