ADO.NET Provider for Apache Hive

Build 21.0.7930

DELETE Statements

To delete from a table, use DELETE statements.

DELETE Syntax

The DELETE statement requires the table name in the FROM clause and the row's primary key in the WHERE clause.

<delete_statement> ::= DELETE FROM <table_name> WHERE { _id = <expression> } [ { AND | OR } ... ]

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

You can use the ExecuteNonQuery method to execute data manipulation commands and retrieve the number of affected rows:

C#

String connectionString = "Server=127.0.0.1;Port=10000;TransportMode=BINARY";
using (ApacheHiveConnection connection = new ApacheHiveConnection(connectionString)) {
  int rowsAffected;
  ApacheHiveCommand cmd = new ApacheHiveCommand("DELETE FROM [CData].[Default].Customers WHERE _id = @my_id", connection);
  cmd.Parameters.Add(new ApacheHiveParameter("my_id","22"));
  rowsAffected = cmd.ExecuteNonQuery();
}

VB.NET

  
Dim connectionString As [String] = "Server=127.0.0.1;Port=10000;TransportMode=BINARY"
Using connection As New ApacheHiveConnection(connectionString)
  Dim rowsAffected As Integer
  Dim cmd As New ApacheHiveCommand("DELETE FROM [CData].[Default].Customers WHERE _id = @my_id", connection)
  cmd.Parameters.Add(New ApacheHiveParameter("my_id", "22"))
  rowsAffected = cmd.ExecuteNonQuery()
End Using

NOTE: DELETE is available starting in Hive 0.14. Deletes can only be performed on tables that support ACID. Please refer to the Hive Transactions documentation for further information: https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

If one of the below errors are encountered, this refers to the table not supporting ACID and must be configured in order to perform DELETE operations. Please refer to the Hive Transactions documentation for further information about how to properly configure your Hive table.

  • This command is not allowed on an acid table with a non-acid transaction manager. This error occurs when the 'hive.txn.manager' parameter is not set to 'org.apache.hadoop.hive.ql.lockmgr.DbTxnManager'.
  • [table] does not use an AcidOutputFormat or is not bucketed. This error occurs when your table is not bucketed or if it is not stored using a supported storage format (ORC file format).

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930