Deleting Data from an OTF Table - Teradata Vantage

Apache Iceberg and Delta Lake Open Table Format on VantageCloud Lake Getting Started

Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
December 2024
ft:locale
en-US
ft:lastEdition
2025-01-03
dita:mapPath
bsr1702324250454.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
bsr1702324250454

The DELETE FROM SQL can be used to delete rows from an OTF table. The WHERE clause can be used to delete a subset of the rows in the table.

Syntax

DELETE FROM <datalake_name>.<OTFdatabase_name>.<OTFtable_name>
    [WHERE <where_clause> | ALL];
<datalake_name> ::= !! name of the data lake object
<OTFdatabase_name> ::= !! name of the database in the OTF catalog
<OTFtable_name> ::= !! name of the OTF table
<database_name> ::= !! Teradata identifier

Example

DELETE FROM datalake_glue.MyDatabase.Tab1 
  WHERE country='France';

Usage Considerations

Expressions with VARCHAR values in write operations are case-sensitive. For example, ..where firstname='MaRk' differs from ..where firstname='Mark' and the evaluation will be case-sensitive. OTF tables are always case-specific (since parquet files are case-sensitive) and there is no workaround for this at the moment.

Usage Considerations: Iceberg

Unsupported write expressions that are allowed by Read operations:

  • substr
  • like