Auto-refreshing materialized views does not happen...

[m.ardito]

I have an issue similar to this post https://developer.jboss.org/thread/249038

that is about auto-refreshing materialized views, but even if I am already doing what solved that issue... my materialized view does not refresh automatically.

I'm working on teiid 8.12.3, and this is the vdb, which I am using to test this feature... at my best.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="viewtest2" version="1">

    <description>viewtest2 mydb</description>

    <property name="UseConnectorMetadata" value="true" />

    <model name="mydb">

        <property name="importer.useFullSchemaName" value="false"/>

        <source name="mysql5_mydb" translator-name="mysql5" connection-jndi-name="java:/mydb"/>

    </model>

    <model name="views" type="VIRTUAL">

        <metadata type="DDL"><![CDATA[

        CREATE VIEW dati_mat (

            id long,

            rag_soc string,

            cod_ass string,

            pi string

            )

                OPTIONS (materialized true, "teiid_rel:MATVIEW_TTL" 120000,

               "teiid_rel:MATVIEW_PREFER_MEMORY" 'true',

               "teiid_rel:MATVIEW_UPDATABLE" 'true',

               "teiid_rel:MATVIEW_SCOPE" 'vdb')

            AS

                /*+ cache(ttl:120000) */

                  SELECT

                  ag.id,

                  ag.rag_soc,

                  ag.cod_ass,

                  ag.pi

                FROM mydb.ag as ag

        ]]>

        </metadata>

    </model>

</vdb>

- once vdb deploys, the snapshot is invalid, ok.

- any select query to the view, created the snapshot, ok.

- in the logs I find lines like

13:26:48,115 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 3)  TEIID50030 VDB viewtest2.1 model "mydb" metadata loaded. End Time: 02/03/16 13.26

13:26:48,121 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (teiid-async-threads - 3)  TEIID40003 VDB viewtest2.1 is set to ACTIVE

13:28:15,789 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker104_QueryProcessorQueue2929) FwEos8wrrb/v TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

13:28:15,923 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker104_QueryProcessorQueue2985) FwEos8wrrb/v TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.863.

but if no select query happens, the snapshot is never updated.

an old post: https://developer.jboss.org/wiki/AHowToGuideForMaterializationcachingViewsInTeiid

suggested to use system scripting to issue a "EXEC SYSADMIN.refreshMatView(viewname=>'Portfolio.UpdateTable', invalidate=>true)" like command,

but from the caching guide (Internal Materialization - Teiid 8.12 - Project Documentation Editor), I assumed this should happen aumatically now, without any scripting...

what is wrong here, what can I try?

I would also try "lazy-invalidate=true" but I find no examples, how is this to be used?

Thanks,

Marco

[shawkins]

> but if no select query happens, the snapshot is never updated.

That is expected.  Internal materialization is not proactive in performing refreshes.  See the limitation:

• The non-managed ttl refresh is performed lazily, that is it is only trigger by using the table after the ttl has expired. For infrequently used tables with long load times, this means that data may be used well past the intended ttl.

What you want is to enable materialization management with the teiid_rel:ALLOW_MATVIEW_MANAGEMENT property.  Starting with 8.11, this allows the ttl refresh for internal matviews to be performed on schedule - [TEIID-2469] Pre-Load Materialised Views - JBoss Issue Tracker

[m.ardito]

Thanks, now I added that property too:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="viewtest2" version="1">

    <description>viewtest2 mydb</description>

    <property name="UseConnectorMetadata" value="true" />

    <model name="mydb">

        <property name="importer.useFullSchemaName" value="false"/>

        <source name="mysql5_mydb" translator-name="mysql5" connection-jndi-name="java:/mydb"/>

    </model>

    <model name="views" type="VIRTUAL">

        <metadata type="DDL"><![CDATA[

        CREATE VIEW dati_mat (

            id long,

            rag_soc string,

            cod_ass string,

            pi string

            )

                OPTIONS (materialized true, "teiid_rel:MATVIEW_TTL" 120000,

               "teiid_rel:MATVIEW_PREFER_MEMORY" 'true',

               "teiid_rel:MATVIEW_UPDATABLE" 'true',

               "teiid_rel:MATVIEW_SCOPE" 'vdb',

               "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true')

            AS

                /*+ cache(ttl:120000) */

                                (

                                SELECT

                  ag.id,

                  ag.rag_soc,

                  ag.cod_ass,

                  ag.pi

                FROM mydb.ag as ag

                                )

        ]]>

        </metadata>

    </model>

</vdb>

but now in the logs I find this, every 2 minutes...

17:52:24,876 WARN  [org.teiid.PROCESSOR] (Worker110_QueryProcessorQueue3115) vFig6nvfmjs4 TEIID30020 Processing exception for request vFig6nvfmjs4.0 'TEIID31100 Parsing error: Encountered "[*]SYSADMIN.refreshMatView[*](\"views\".\"dati_mat\"" at line 1, column 1.

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...'. Originally QueryParserException QueryParser.java:214. Enable more detailed logging to see the entire stacktrace.

[edit] while, if I reload from the web console, it works instantly, and the log shows:

18:00:24,876 WARN  [org.teiid.PROCESSOR] (Worker110_QueryProcessorQueue3121) KbqgRXX20t5r TEIID30020 Processing exception for request KbqgRXX20t5r.0 'TEIID31100 Parsing error: Encountered "[*]SYSADMIN.refreshMatView[*](\"views\".\"dati_mat\"" at line 1, column 1. <== this is the last error every 2 minute like above... then I reload the snapshot manually from the web console

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...'. Originally QueryParserException QueryParser.java:214. Enable more detailed logging to see the entire stacktrace.

18:00:38,918 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker110_QueryProcessorQueue3122) UNz0p+PBYFyz TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

18:00:39,045 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker110_QueryProcessorQueue3178) UNz0p+PBYFyz TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.863.

[shawkins]

Yes that is an issue.  The initial load will work if no ttl is specified, but the sql for the continual refresh is not formed correct.  Can you log something for that?  We'll get that into 8.13.2 which will be out early next week.

[m.ardito]

Steven Hawkins wrote:

Yes that is an issue.  The initial load will work if no ttl is specified, but the sql for the continual refresh is not formed correct.  Can you log something for that?  We'll get that into 8.13.2 which will be out early next week.

Here it is [TEIID-4011] automatic/autonomous refresh of materialized views does not work - JBoss Issue Tracker

Thanks, Marco

[m.ardito]

And I can confirm that in latest 9.0.0.Alpha2 also this now works...

As for other issues, I'll wait next 8.13.3 stable to mark this as solved.

So many thanks, Teiid devs.

Marco

[m.ardito]

Yesterday (night) I was at home remotely playing with some new matview, which was nicely auto-refreshing itself every 2 minutes (it's just a test autorefresh interval), when something strange happened, I don't know if it could bew related to this thread topic, I could open I new thread if needed.

What happened is that I noticed in the web console that two matviews I was testing (working nicely, in squirrelsql over vpn) stopped refreshing just before midnight (they are still unrefreshed today, as you can see below).

At first I thought there was a "midnight bug", but another test matview, in another vdb, which I was not using, was instead refreshed even after midnight.

Today I checked the logs, and got this, where I noticed thay stopped just before midnight (with the expected 2 minute delay should have been happening after midnight) and also that they were not even refreshed, before, every 2 minutes... apparently.

those 2 matviews are from huge CSV files, by the way, if this can help understanding the logs in any way. Below the following logs lines, you'll find the whole vdb, and the .cli script I used to create the CSV datasource

(btw I didn't remove any log line in the time interval, but can provide more log lines before/after)

2016-03-22 23:52:01,443 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker94_QueryProcessorQueue25930) M6ke1kme9Sqo TEIID30013 Loading materialized view table #MAT_PFI.SCHEDEAPP

2016-03-22 23:52:02,956 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker96_QueryProcessorQueue25990) M6ke1kme9Sqo TEIID30014 Loaded materialized view table #MAT_SCHEDE.SCHEDEAPP with row count 4.130.

2016-03-22 23:52:03,870 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker95_QueryProcessorQueue26011) M6ke1kme9Sqo TEIID30014 Loaded materialized view table #MAT_PFI.SCHEDEAPP with row count 26.526.

2016-03-22 23:53:24,692 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker92_QueryProcessorQueue26028) NL5LaBYq3nbj TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-22 23:53:24,854 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker92_QueryProcessorQueue26085) NL5LaBYq3nbj TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

2016-03-22 23:55:24,692 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker92_QueryProcessorQueue26090) wFTGpSdbk3Wb TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-22 23:55:24,826 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker92_QueryProcessorQueue26146) wFTGpSdbk3Wb TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

2016-03-22 23:57:24,693 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker92_QueryProcessorQueue26148) eFVxee3EEB+O TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-22 23:57:24,822 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker92_QueryProcessorQueue26204) eFVxee3EEB+O TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

2016-03-22 23:58:49,233 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker92_QueryProcessorQueue26206) M6ke1kme9Sqo TEIID30013 Loading materialized view table #MAT_SCHEDE.SCHEDEAPP

2016-03-22 23:58:49,241 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker92_QueryProcessorQueue26206) M6ke1kme9Sqo TEIID30013 Loading materialized view table #MAT_PFI.SCHEDEAPP

2016-03-22 23:58:50,781 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker95_QueryProcessorQueue26252) M6ke1kme9Sqo TEIID30014 Loaded materialized view table #MAT_SCHEDE.SCHEDEAPP with row count 4.130.

2016-03-22 23:58:51,822 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker98_QueryProcessorQueue26293) M6ke1kme9Sqo TEIID30014 Loaded materialized view table #MAT_PFI.SCHEDEAPP with row count 26.526.

2016-03-22 23:59:24,692 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker98_QueryProcessorQueue26295) cexVEb9y2Q/D TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-22 23:59:24,822 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker98_QueryProcessorQueue26351) cexVEb9y2Q/D TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

===========

2016-03-23 00:01:24,693 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker98_QueryProcessorQueue26353) qa7mURe7k+2i TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-23 00:01:24,852 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker98_QueryProcessorQueue26409) qa7mURe7k+2i TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

2016-03-23 00:03:24,693 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker98_QueryProcessorQueue26411) zMmaUZmBeBc3 TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-23 00:03:24,868 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker95_QueryProcessorQueue26469) zMmaUZmBeBc3 TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

2016-03-23 00:05:24,692 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker95_QueryProcessorQueue26470) +WJO/hXvtFLf TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-23 00:05:24,835 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker95_QueryProcessorQueue26526) +WJO/hXvtFLf TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

2016-03-23 00:07:24,693 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker95_QueryProcessorQueue26528) az/RFQS7mzGZ TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-23 00:07:24,832 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker100_QueryProcessorQueue26583) az/RFQS7mzGZ TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

2016-03-23 00:09:24,693 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker100_QueryProcessorQueue26585) SFRI/JQn3tm1 TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-23 00:09:24,847 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker100_QueryProcessorQueue26643) SFRI/JQn3tm1 TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

2016-03-23 00:11:24,693 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker100_QueryProcessorQueue26644) NGLtLAVIuXhq TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

2016-03-23 00:11:24,830 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker100_QueryProcessorQueue26700) NGLtLAVIuXhq TEIID30014 Loaded materialized view table #MAT_VIEWS.DATI_MAT with row count 28.913.

2016-03-23 00:13:24,692 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker100_QueryProcessorQueue26707) 6UjobNleyUF4 TEIID30013 Loading materialized view table #MAT_VIEWS.DATI_MAT

====================

set ra_name=file

set cd_module=org.jboss.teiid.resource-adapter.file

set cd_classname=org.teiid.resource.adapter.file.FileManagedConnectionFactory

set cd_AllowParentPaths=true

set cd_name=apprendisti

set cd_ParentDirectory=/mnt/common/TEST/sp

/subsystem=resource-adapters/resource-adapter=$ra_name/connection-definitions=$cd_name:add(jndi-name=java:/$cd_name, class-name=$cd_classname, enabled=true, use-java-context=true)

/subsystem=resource-adapters/resource-adapter=$ra_name/connection-definitions=$cd_name/config-properties=ParentDirectory:add(value=$cd_ParentDirectory)

/subsystem=resource-adapters/resource-adapter=$ra_name/connection-definitions=$cd_name/config-properties=AllowParentPaths:add(value=$cd_AllowParentPaths)

/subsystem=resource-adapters/resource-adapter=$ra_name:activate

====================

====================

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="ApprendistiAnsi" version="1">

    <description>Apprendisti</description>

    <property name="UseConnectorMetadata" value="true" />

    <model name="CsvApprendisti">

        <source name="text-connector" translator-name="file-override" connection-jndi-name="java:/apprendisti"/>

    </model>

    <model name="schede" type="VIRTUAL">

        <metadata type="DDL"><![CDATA[

            CREATE VIEW schedeapp (

            COD_FISC_IMP string,

            DENOM_IMP string,

            COD_FISC_APPR string,

            COGNOME_APPR string,

            NOME_APPR string

            )

        OPTIONS (materialized true, "teiid_rel:MATVIEW_TTL" 120000,

               "teiid_rel:MATVIEW_PREFER_MEMORY" 'true',

               "teiid_rel:MATVIEW_UPDATABLE" 'true',

               "teiid_rel:MATVIEW_SCOPE" 'vdb')

            AS

                /*+ cache(ttl:120000) */

               SELECT

               x.COD_FISC_IMP,

               x.DENOM_IMP,

               x.COD_FISC_APPR,

               x.COGNOME_APPR,

               x.NOME_APPR

                FROM (EXEC CsvApprendisti.getTextFiles('apptestAnsi.CSV')) AS f,

                    TEXTTABLE(f.file COLUMNS

            COD_FISC_IMP string,

            DENOM_IMP string,

            COD_FISC_APPR string,

            COGNOME_APPR string,

            NOME_APPR string

            HEADER

        ) AS x;

        ]]>

    </metadata>

    </model>

    <model name="pfi" type="VIRTUAL">

        <metadata type="DDL"><![CDATA[

            CREATE VIEW schedeapp (

            COD_FISC_APPR string,

            COGNOME_APPR string,

        IMPRESA string

            )

    OPTIONS (materialized true, "teiid_rel:MATVIEW_TTL" 120000,

               "teiid_rel:MATVIEW_PREFER_MEMORY" 'true',

               "teiid_rel:MATVIEW_UPDATABLE" 'true',

               "teiid_rel:MATVIEW_SCOPE" 'vdb')

            AS

                /*+ cache(ttl:120000) */

               SELECT x.COD_FISC_APPR, x.COGNOME_APPR, x.IMPRESA

                FROM (EXEC CsvApprendisti.getTextFiles('appPfiAnsi.CSV')) AS f,

                    TEXTTABLE(f.file COLUMNS COD_FISC_APPR string, COGNOME_APPR string, IMPRESA string HEADER) AS x;

        ]]>

    </metadata>

    </model>

    <translator name="file-override" type="file">

    <property name="Encoding" value="Windows-1252"/>

    </translator>

</vdb>

====================

As said above, if this is unrelated, I'll move this in a new thread.

Marco

[m.ardito]

Adding to the above post that even clicking "refresh" in the web console does not work, obviously, and also does not reflect anything in the logs. I didn't click "reload" though, to keep this particular state (atm I don't know if/how can I reproduce this), and be able later to do some test, just in case.

Marco

[asmigala]

Marco, according to the docs, "The non-managed ttl refresh is performed lazily, that is it is only trigger by using the table after the ttl has expired. For infrequently used tables with long load times, this means that data may be used well past the intended ttl."

Have you ran any queries against the views after midnight? If not, this is the expected behaviour, as the reload is only performed when the view is accessed and the ttl has expired.

[m.ardito]

No, I was using them until a few minutes before...  so I didn't think immediately that only using them would trigger a new "refresh cycle".

And another matview (other vdb) is still refreshing very two minute... that was puzzling me.

I've read those docs some time ago, I need to refresh my knowledge, thanks.

Tthe fact is also that those CSV views are huge, and I was hoping to keep them as "fresh" as possible in order to reduce lag into applications, etc

I'll experiment more.

Marco

[m.ardito]

No, I remembered that part of the docs right. What I was missing was the first suggestion of Steve above: my vdb was missing  "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true' but I thought it was there... the other matview that was still updating had that property enabled but I forgot to check both xml files for that. The new view was missing that property, probably I recycled an old xml file to create it. My fault. Teiid is magic but needs much more concentration I guess: always double check.

Now all matviews update nicely every 2 minutes even without any connection to the vdb...

they even survived midnight

Now relax and wait for 8.13.3...

Marco

[m.ardito]

Confirmed working on 8.13.3 stable

Thanks

Marco