@Rocky - is there a way i can count number of rows in each table, view and materialized views.

In StarRocks, you can retrieve row counts for tables, views, and materialized views using several methods depending on whether you need a quick metadata-based estimate or an exact count. 1. For Tables and Materialized Views (Metadata/Approximate) The easiest way to see row counts for all tables and materialized views in a database is to query the

information_schema

. Using `information_schema.tables`: This provides row counts for both base tables and asynchronous materialized views (which are physically stored as tables).

sql
SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    TABLE_TYPE, 
    TABLE_ROWS 
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'your_database_name';

_Note:

TABLE_ROWS

in this view is based on approximate background statistics._ Using `information_schema.materialized_views`: For more specific details on Materialized Views (both synchronous and asynchronous):

sql
SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    TABLE_ROWS 
FROM information_schema.materialized_views;

Using

SHOW

commands: * For Tables:

SHOW DATA FROM your_database_name.your_table_name;

(provides row count and storage size). * For Materialized Views:

SHOW MATERIALIZED VIEWS;

(includes a

rows

column). ────────── 2. For Views (Virtual) Standard views in StarRocks are virtual and do not store data physically. Therefore, they do not have a pre-calculated row count in metadata. To count rows in a view, you must execute a query:

sql
SELECT COUNT(*) FROM your_view_name;

────────── 3. For Exact Counts (All Types) If you need the exact number of rows (since metadata can sometimes be slightly delayed or approximate), you must run a

COUNT(*)

query for each object. If you want to automate this for many tables, you can generate the SQL using a query like this:

sql
SELECT 
    CONCAT('SELECT "', TABLE_NAME, '" as table_name, COUNT(*) FROM ', TABLE_SCHEMA, '.', TABLE_NAME, ' UNION ALL') as query
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'your_database_name';

You would then copy the output, remove the trailing

UNION ALL

from the last line, and execute it. Summary Table Object Type | Metadata Count (Approx) | Exact Count Table |

information_schema.tables

|

SELECT COUNT(*)

Materialized View |

SHOW MATERIALIZED VIEWS

|

SELECT COUNT(*)

View | N/A |

SELECT COUNT(*)

References • using_starrocks/Materialized_view-single_table.md • sql-reference/information_schema/materialized_views.md • sql-reference/information_schema/information_schema.md • sql-reference/information_schema/tables.md • using_starrocks/async_mv/Materialized_view.md • table_design/table_design.md

@Rocky is it possible to get in a single query? or a task to achieve this?

Hi there! I'm Rocky. To give you the most accurate answer, could you tell me a bit more about what you are trying to achieve? In StarRocks, the answer is often yes to both, but it depends on your specific use case. Here are the common ways we handle complex operations: 1. The "Single Query" Approach StarRocks is designed for high-performance complex analytics. You can often achieve "multi-step" logic in a single SQL statement using: * Common Table Expressions (CTEs): To organize complex multi-stage transformations. * Window Functions & Lateral Joins: For advanced row-level calculations. * INSERT INTO ... SELECT: To transform and load data from one table to another (or from an external data lake) in one atomic operation. * Pivot/Unpivot: (Available from v3.1+) To rotate data within a single query. 2. The "Task" Approach (

SUBMIT TASK

) If "this" refers to a long-running job, a scheduled process, or an asynchronous operation, you can use the SUBMIT TASK feature (available from v2.5 for background tasks and v3.3 for scheduling): * Asynchronous Execution: You can submit an

INSERT

or

CREATE TABLE AS SELECT (CTAS)

statement to run in the background. * Scheduling: You can schedule these tasks to run at specific intervals (e.g.,

EVERY (INTERVAL 1 HOUR)

). * Management: You can monitor them via

INFORMATION_SCHEMA.tasks

and

task_runs

. 3. Materialized Views If you are looking to "get" a complex result set that stays updated automatically, a Materialized View is often the best "task" to achieve this. It handles the refresh logic for you behind the scenes. Could you share your specific SQL or the workflow you're trying to simplify? I'd be happy to help you write the query or set up the task! References • best_practices/query_tuning/query_planning.md • quick_start/shared-nothing.md • faq/Others.md • introduction/Features.md • introduction/Architecture.md • administration/management/resource_management/query_queues.md