COUNT_ESTIMATE

An aggregate function that returns the estimated total number of rows in an HDFS read/write table (rows in operational memory as well as rows persisted to HDFS).

COUNT_ESTIMATE does not operate on an expression, but requires you to specify the fully-qualified table name. Note that the reported number of rows can differ from the actual number of rows by an error margin of approximately 2%.

You can obtain an accurate count of the rows in an HDFS table by using COUNT(*) and including the queryHDFS=true query hint. However, that method iterates over the entire table in HDFS and can take a long time to complete, depending on the size of the table.

Syntax

COUNT_ESTIMATE('schemaname.tablename')

The resulting data type is INTEGER.

Example

For an HDFS read/write table, FLIGHTAVAILABILITY, using COUNT(*) returns results only for table's in-memory, operational data:

gfxd> select count(*) as operational from flightavailability;
OPERATIONAL          
-----------
400    

1 row selected
Using COUNT_ESTIMATE provides an estimate of the HDFS-persistent rows:
gfxd> select count(*) as operational, count_estimate('app.flightavailability') as estimate from flightavailability;
OPERATIONAL|ESTIMATE            
--------------------------------
400        |513                 

1 row selected
Using COUNT(*) with queryHDFS=true provides an accurate count of HDFS-persistent, but may take additional time to iterate over the table's data:
gfxd> select count(*) as actual, count_estimate('app.flightavailability') as estimate from flightavailability --GEMFIREXD-PROPERTIES queryHDFS=true \n ;
ACTUAL     |ESTIMATE            
--------------------------------
518        |513                 

1 row selected