Count rows for view

[devopg]

Hello. Dont understand how can i select count() for view ?
I try
SELECT count(*) FROM public.market_orders;
buy get error:

Okey, i try use AS OF

SELECT count(*) FROM public.market_orders as of ts;
buy got error about column dont exists

okey i make query for take table inforamtion

SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
    table_name = 'market_orders';

column ts exists and have timestamp type

My other try:
SELECT * FROM public.market_orders as of 1

[bobbyiliev]

Hi there,

It looks like that the market_orders view is not materialized. You can verify this with the following query:

SHOW FULL VIEWS;

You can almost think the NON-materialized views as a reusable template to be used in other materialized views. The NON-materialized views do not actually process any data.

What you can do is create a materialized view with the following:

CREATE MATERIALIZED VIEW market_orders_count AS SELECT count(*) FROM public.market_orders;

The above will create a materialized view called market_orders_count which will contain the result of your query and the results will be incrementally updated as the data changes.

For more information on materialized views and how they work, I would suggest the following documentation:

https://materialize.com/docs/sql/create-materialized-view/

In case that you want to learn more about the non-materialized views, you can check out the following:

https://materialize.com/docs/sql/create-view/

Hope that this helps and let me know if you have any questions!

[devopg]

yes its VIEW (non mater).
But how i can ceate temp query for show result for next make MATERIALIZED VIEW ?
Or i should

1. create MATERIALIZED VIEW
2. select result
3. if wrong remove query
4. goto 1.

[bobbyiliev]

There are a couple of ways to do it:

• Use TAIL:

COPY ( TAIL ( SELECT count(*) FROM public.market_orders; ) ) TO STDOUT;

Basically, you can use tail to pass any query which will start a dataflow.

• Create a temp view so that it is not persisted and will be destroyed at the end of your session:

CREATE TEMPORARY MATERIALIZED VIEW market_orders_count_temp AS SELECT count(*) FROM public.market_orders;

Hope that this helps!

[devopg]

@bobbyiliev
One more question. I watched video "Materialize Hack Day" on youtube from Materialize channel and saw how "Anreas Fuch" create source from kafka, create VIEW (non mat) and use "select count() from view" and its work. Is it removed functionality?

with time:
https://youtu.be/Kv9FCfZjgy0?t=3623

[bobbyiliev]

Hi there @devopg

This is because in the demo the source is actually materialized. So the data is already ingested.

You can take a look a the DDL statements here:

https://github.com/antifuchs/mz-certificate-transparency/blob/master/SCHEMA.md

Hope that this helps!