Specifying Input Values - Analytics Database - Teradata Vantage

DATASET Data Type

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
bka1628112240653.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ekk1458586304878
lifecycle
latest
Product Category
Teradata Vantageā„¢

The first input value specified via the ON clause must be character based (for example, a CHAR/VARCHAR/CLOB or a DATASET STORAGE FORMAT CSV value) and be composed of data that conforms to the specified format (that is, it uses the specified field/record delimiters, or defaults if not specified). Any subsequent input values do not affect the result of conversion to Avro or JSON, but are passed through the table operator and given as additional output columns. This allows the resulting Avro or JSON documents to be associated with the source CSV data, which is very important when converting multiple CSV inputs.

CREATE TABLE my_table(id int, csvData VARCHAR(500));
INSERT INTO my_table(1, 'a,b,c,d,e,f\1,2,3,4,5,6\7,8,9,10,11,12');
INSERT INTO my_table(2, 'a,b,c,d,e,f\13,14,15,16,17,18
\19,20,21,22,23,24');
INSERT INTO my_table(3, 'a,b,c,d,e,f\25,26,27,28,29,30
\31,32,33,34,35,36');

SELECT id, data.toJSON() FROM CSV_TO_AVRO
(
    ON (SELECT csvData, id FROM my_table)
    USING SCHEMA('{"record_delimiter":"\\"}')
) as csvAvro
ORDER BY id, data.a;

Result:

         id data.TOJSON()
----------- --------------------------------------------------------------
          1 {"a":"1","b":"2","c":"3","d":"4","e":"5","f":"6"}
          1 {"a":"7","b":"8","c":"9","d":"10","e":"11","f":"12"}
          2 {"a":"13","b":"14","c":"15","d":"16","e":"17","f":"18"}
          2 {"a":"19","b":"20","c":"21","d":"22","e":"23","f":"24"}
          3 {"a":"25","b":"26","c":"27","d":"28","e":"29","f":"30"}
          3 {"a":"31","b":"32","c":"33","d":"34","e":"35","f":"36"}