Page 1 of 2
@@Rowcount
Posted: Tue Jul 23, 2013 11:30 am
by rajesh.pu
Hello,
do we have a similar function for @@rowcount in vertica?
i've a statement in my sql server query " WHILE @@ROWCOUNT > 1". i would like to perform similar action in vertica aswell
Kindly suggest
Rgds
Rajesh
Re: @@Rowcount
Posted: Tue Jul 23, 2013 1:21 pm
by id10t
Hi!
>> do we have a similar function for @@rowcount in vertica?
No, Vertica has no.
>> i would like to perform similar action in vertica aswell
programmatically only
Re: @@Rowcount
Posted: Thu Jul 25, 2013 6:20 am
by BoMBaY
As I know @@ROWCOUNT is a Built-in System Function (Transact-SQL) of SQL Server that returns the number of rows affected by the last statement. And Vertica currently doesn't support programming language in Database like PL/SQL (Oracle) or Transact-SQL (SQL Server).
So If you want to know the number of rows affected by the last statement. It will depend on a language that you use to write you program.
The following example demonstrates using Java with the JDBC to load a file name customers.txt into a new database table. then use getUpdateCount() to get the number of rows affected by the COPY statement.
Code: Select all
import java.sql.*;
import java.util.Properties;
import com.vertica.jdbc.*;
public class COPYFromFile {
public static void main(String[] args) {
try {
Class.forName("com.vertica.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.err.println("Could not find the JDBC driver class.");
e.printStackTrace();
return;
}
Properties myProp = new Properties();
myProp.put("user", "ExampleAdmin"); // Must be superuser
myProp.put("password", "password123");
Connection conn;
try {
conn = DriverManager.getConnection(
"jdbc:vertica://VerticaHost:5433/ExampleDB",myProp);
// Disable AutoCommit
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
// Create a table to hold data.
stmt.execute("DROP TABLE IF EXISTS customers;");
stmt.execute("CREATE TABLE IF NOT EXISTS customers (Last_Name char(50) "
+ "NOT NULL, First_Name char(50),Email char(50), "
+ "Phone_Number char(15))");
// Use the COPY command to load data. Load directly into ROS, since
// this load could be over 100MB. Use ENFORCELENGTH to reject
// strings too wide for their columns.
boolean result = stmt.execute("COPY customers FROM "
+ " '/data/customers.txt' DIRECT ENFORCELENGTH");
// Determine if execution returned a count value, or a full result
// set.
if (result) {
System.out.println("Got result set");
} else {
// Count will usually return the count of rows inserted.
System.out.println("Got count");
int rowCount = stmt.getUpdateCount();
System.out.println("Number of accepted rows = " + rowCount);
}
// Commit the data load
conn.commit();
} catch (SQLException e) {
System.out.print("Error: ");
System.out.println(e.toString());
}
}
}
The example prints the following out to the system console when run (assuming that the customers.txt file contained two million valid rows):
Re: @@Rowcount
Posted: Mon Feb 12, 2018 5:22 pm
by micha034
Hi,
I know its pretty old thread but...
How can I get number of accepted rows while performing COPY from HDFS via hdfs:// protocol?
Re: @@Rowcount
Posted: Mon Feb 12, 2018 9:21 pm
by sKwa
Hi!
@micha034
1. Vertica version?
2. type of HDFS connector?
Re: @@Rowcount
Posted: Mon Feb 12, 2018 10:44 pm
by micha034
Thanks for quick reply.
Vertica version 8.1.1
Using JDBC.
Tried following but returns 0 although data is loaded:
int numAccepted1 = copyStatement.executeUpdate();
int numAccepted2 = copyStatement.getUpdateCount();
Performing COPY command from JDBC like following:
COPY tests.table
FROM 'hdfs://hdfs-nn/path/to/data/*'
DELIMITER E'\t' NULL AS 'null' REJECTED DATA AS TABLE loader.rejected_5JE8wAoWyX DIRECT;
Re: @@Rowcount
Posted: Tue Feb 13, 2018 12:18 am
by sKwa
Hi!
1. do COPY with no COMMIT
2. with same cursor/statement run "select get_num_accepted_rows() as 'ACCEPTED', get_num_rejected_rows() as 'REJECTED';"
3. observe results and commit or rollback
Statement stmt = conn.createStatement();
stmt.execute("COPY ... FROM ...");
rs = stmt.executeQuery("select get_num_accepted_rows() as 'ACCEPTED', get_num_rejected_rows() as 'REJECTED';");
...