17.10 - Examples: Shredding the CSV Data - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - DATASET Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1198-171K
Language
English (United States)

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

Example: Shredding the CSV Data to a 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 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