Add support in ER Modeler for the new version.

Remember when adding code for the "Script Object to Window As" examples mentioned above to also apply those changes to "Script Object to New Window As" and "Script Object to File As" See here.

Skew added for teradata 16 and above:
 

Case 1:

Added support for DATASET
 

CREATE SET TABLE avroCompressTable ,
   FALLBACK,
   LOG,
   DEFAULT MERGEBLOCKRATIO,
   CHECKSUM = DEFAULT ( 
   id                     INTEGER,
   compressibleAvroFile   DATASET STORAGE FORMAT AVRO 
 COMPRESS USING TD_SYSFNLIB.SNAPPY_COMPRESS DECOMPRESS USING TD_SYSFNLIB.SNAPPY_DECOMPRESS 
   )
   PRIMARY INDEX _ADS_INDEX_1 (id)
GO

Added support for BLOCKCOMPRESSIONALGORITHM and BLOCKCOMPRESSIONLEVEL . for above teradata 16.

CASE 1

CREATE SET TABLE avroCompressTable ,
   FALLBACK,
   LOG,
   DEFAULT MERGEBLOCKRATIO,
   BLOCKCOMPRESSION = DEFAULT,
   CHECKSUM = DEFAULT,
   BLOCKCOMPRESSIONALGORITHM = DEFAULT,
   BLOCKCOMPRESSIONLEVEL = DEFAULT ( 
   id                     INTEGER,
   compressibleAvroFile   DATASET STORAGE FORMAT AVRO 
 COMPRESS USING TD_SYSFNLIB.SNAPPY_COMPRESS DECOMPRESS USING TD_SYSFNLIB.SNAPPY_DECOMPRESS 
   )
   PRIMARY INDEX _ADS_INDEX_1 (id)
GO


CASE 2

CREATE SET TABLE t_blc ,
   FALLBACK,
   LOG,
   DEFAULT MERGEBLOCKRATIO,
   BLOCKCOMPRESSION = ALWAYS,
   CHECKSUM = DEFAULT,
   BLOCKCOMPRESSIONALGORITHM = ZLIB,
   BLOCKCOMPRESSIONLEVEL = 8 ( 
   c1 INTEGER 
   )
   UNIQUE PRIMARY INDEX _ADS_INDEX_1 (c1)
   PARTITION BY (RANGE_N(c1  BETWEEN 1  AND 500  EACH 5 ))
GO

Hi Pankaj,

Have a look at SQLTeradataSyntax class. This is where we set the colors. There are suppose to be some differences in the colors. You can add any new keywords there.

Thanks, Tom

Hi Pankaj,

Looks good... Please have the QA team test and verify it also.

Thanks, Tom

Verified the ticket. Issues found:

Scenario 1:

1.Right click database

2.Select create database from context menu

3.Enter db name and permanent space

4.Navigate to preview SQL

Result: Throwing null pointer exception

Scenario 2:

Same issue on navigating to DBA tools>>storage manager>>create database

Screenshots attached.

Both the issues are fixed and will be verify in next build.

Hi Juhi, 

Looks like this is fixed in ADS 19.5.0-dev-33. Please retest.

Thanks, Tom

Verified above two issues regarding null pointer exception on new build Dev-33. Both are fixed.

Screenshot attached.

Hi Tom,

I have few observations regarding script generator:

Scenario 1:

1.Create table with BLOCKCOMPRESSIONALGORITHM and BLOCKCOMPRESSIONLEVEL

2.Navigate to Tools>>Schema script generator>>Preview tab

Result: Partition statement is missing from script

NOTE: Same behavior is there on : Right click table name>>script object to new window as>>CREATE

So, please let me know your inputs whether it is an issue and needs to be fixed.

Screenshot attached.

Hi Juhi, 

This is working ok for me. See here. For the script object scenario, you have to use create(full) and for the schema script generator, make sure "include table storage" is checked. 

Thanks, Tom

Hi Tom,

Thanks for your input. Verified schema script generator the way you suggested and it is working expected.

Hi Tom,

I have few observations regarding alter table and data insertion in table having column of DATASET datatype :

Scenario 1:

1.Click on table with column having DATASET datatype>> alter table

2.Dont do any changes and navigate to Preview SQL tab [it already shows alter table statement, ideally it should not as we have not done any changes to table]

3.Navigate to general tab

4.Add a new column

5.Again navigate to preview SQL tab and save changes

Result: One more alter table statement is added here. Changes are not getting saved.

Error:  [Error 3706] [SQLState 42000] Syntax error: STORAGE FORMAT must be specified for the DATASET type

Scenario 2:

1.Create table with with column having DATASET datatype. I used query: CREATE SET TABLE avroCompressTable ,
   FALLBACK,
   LOG,
   DEFAULT MERGEBLOCKRATIO,
   CHECKSUM = DEFAULT (
   id                     INTEGER,
   compressibleAvroFile   DATASET STORAGE FORMAT AVRO
 COMPRESS USING TD_SYSFNLIB.SNAPPY_COMPRESS DECOMPRESS USING TD_SYSFNLIB.SNAPPY_DECOMPRESS
   )
   PRIMARY INDEX _ADS_INDEX_1 (id)
GO

2.Try to insert data : table name>>right click>>script to object window>>Insert

Insert statement: INSERT INTO test_dataset(id, compressibleAvroFile)
    VALUES(0, NULL)
GO

3. Execute query

Result: Data is not getting inserted to table.

Tried queries like:

1.UPDATE "test_skew_value"."test_dataset"
SET "compressibleAvroFile"={"type" : "record"}
WHERE "id"=1

Error: [Error 1074] [SQLState HY000] Invalid Escape Clause "UPDATE "test_skew_value"."test_dataset"
SET "compressibleAvroFile"={"type" : "record"}
WHERE "id"=1"

2.INSERT INTO my_table (1,
'7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473
223A5B7B226E616D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616
D65223A22616765222C2274797065223A22696E74227D2C7B226E616D65223A227363686F6F6C73
222C2274797065223A7B2274797065223A226172726179222C226974656D73223A7B22747970652
23A227265636F7264222C226E616D65223A227265635F31222C226669656C6473223A5B7B226E61
6D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A2274797
065222C2274797065223A22737472696E67227D5D7D7D7D2C7B226E616D65223A226A6F62222C22
74797065223A22737472696E67227D5D7D000E43616D65726F6E3008084C616B6514656C656D656
E746172790E4D616469736F6E0C6D6964646C650C52616E63686F0868696768065543490E636F6C
6C656765001470726F6772616D6D6572'xb);

Error: [Error 3775] [SQLState 42000] Invalid hexadecimal constant.

So, I am not sure, how can we insert data into column with DATASET datatype? Please provide your inputs.

I have attached one screenshot for alter table issue and a pdf which I used for reference for data insertion.

Hi Pankaj, 

Adding the following to AFTableColumnInitTeradata should fix scenario 1 above. We don't want to support dataset in the create table though.

Hi Juhi, 

I tested the insert from scenario 2 and it looks like it is working. See here. Remember, when you cut and paste wrapped data into the ADS QA window, you will have spaces in the data. You will need to manually remove those.

Update works also. See here.

Hope that helps, Tom

Hi Tom,

Thanks for your input. Now, Insert and update table data is working fine. I only see difference in the format of output data for "compressibleAvroFile" column. The screenshot you have attached shows data in Avro file format. I am getting the same hexadecimal value as output which I used in insert query.

I have attached screenshot. Please let me know if it looks good to you.

Alter table issue is fixed and will be verified in next build.

<Juhi> I found answer to my question. There is an option on File>>Options>>Results>>Data retrieval>>'Convert binary to hex'. I just disabled that option and now I am getting results in Avro file format.

QA Verified. All the issues are fixed and working expected. Build: ADS-19.5.0-dev-37. Teradata Version:16.20

Scenarios verified:
1.Support for version 16.0 for ERModeler
2.Support for dataset
3.Support for skew
4.Support for blockcompression
5.Syntax highlighting
6.Verified changes implemented on ScriptObject, SchemaScriptGenerator and SchemaTree, Script Object to Window As, Script Object to New Window As and Script Object to File As.

7.Regression testing on Teradata v16.20.
8.Sanity test on older Teradata v15.10.

Screenshots attached.