Examples: Shredding the CSV Data

Teradata Vantageā„¢ DATASET Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1198-162K

You can import existing CSV data, represented as a VARCHAR or CLOB, to an existing relational table in Teradata Database. This is called "shredding" and uses the DATASET_TABLE table operator.

Example: Shredding the CSV Data to a Teradata Database Table

Convert the CSV data to a DATASET value with the CSV storage format by using CreateDATASET. Specify a schema if the CSV data does not follow the standard CSV format (for example, it has different column or record delimiters). The DATASET_TABLE then shreds the CSV data to a Teradata Database table. Use DATASET_TABLE to specify each column data type in the CSV value by using the 'column_expression_literal USING specification.

CREATE TABLE empTableFromCSV(
 empID INTEGER, 
 empName VARCHAR(20),
 empSalary INTEGER);

Example: Aligning the CSV Columns and Table Columns

*+csv11.txt+*
empID,empName,empSalary
1,John,100000
2,Sam,50000
3,Mary,75000

.import vartext file csv11.txt
USING (c1 VARCHAR(1000))
INSERT INTO empTableFromCSV 
SELECT * FROM DATASET_TABLE (
 ON (SELECT CreateDATASET(NULL, :c1, CSV))
 USING rowexpr('') 
   colexpr(
  '[ {"dotnotation" : "$..empID",
      "type" : "INTEGER"},
     {"dotnotation" : "$..empName",
              "type" : "VARCHAR(20)"} ,
     {"dotnotation" : "$..empSalary",
              "type" : "INTEGER"} ]')
) AS Emp(empID, empName, empSalary);

SELECT * FROM empTableFromCSV ORDER BY 1;
The resulting table has three rows:
empID empName empSalary
1 John 100000
2 Sam 50000
3 Mary 75000

Example: CSV Column and Table Names are the Same, but Do Not Align

*+csv12.txt+*
empID,empSalary,empName
4,100000,Kate
5,50000,Rob
6,75000,Peter
To align the CSV column and table names:
.import vartext file csv12.txt
USING (c1 VARCHAR(1000))
INSERT INTO empTableFromCSV(empID,empSalary,empName) 
SELECT * FROM DATASET_TABLE (
 ON (SELECT CreateDATASET(NULL, :c1, CSV))
 USING rowexpr('') 
   colexpr(
  '[ {"dotnotation" : "$..empID",
      "type" : "INTEGER"},
     {"dotnotation" : "$..empSalary",
              "type" : "INTEGER"} ,
     {"dotnotation" : "$..empName",
              "type" : "VARCHAR(20)"} ]')
) AS Emp(empID, empSalary, empName);

Example: Passing the Schema to CreateDATASET

In this example, the CSV has no header, but the values align perfectly with the target table. You can pass the schema to the CreateDATASET function, indicating the CSV value has no header line.
*+csv13.txt+*
7,Matt,100000
8,Mark,50000
9,Luke,75000

.import vartext file csv13.txt
USING (c1 VARCHAR(1000))
INSERT INTO empTableFromCSV 
SELECT * FROM DATASET_TABLE (
 ON (SELECT CreateDATASET('{"field_names":NULL}':c1, CSV))
 USING rowexpr('')
   colexpr(
  '[ {"dotnotation" : "$..empID",
     "type" : "INTEGER"},
     {"dotnotation" : "$..empName",
              "type" : "VARCHAR(20)"} ,
     {"dotnotation" : "$..empSalary",
              "type" : "INTEGER"} ]')
) AS Emp(empID, empName, empSalary);

Example: Returning All Employees in the Table

SELECT * FROM empTableFromCSV ORDER BY 1;
empID empName empSalary
1 John 100000
2 Sam 50000
3 Mary 75000
4 Kate 100000
5 Rob 50000
6 Peter 75000
7 Matt 100000
8 Mark 50000
9 Luke 75000