1 Reply Latest reply on Jun 1, 2017 2:11 PM by rareddy

    Materialized Views on Teiid 9.2.3

    pringi
    pringi Jun 1, 2017 1:22 PM

      Before Teiid 9.2, it was not necessary to have any PRIMARY KEY in View nor on their Materialized Views counterpart.

       

      When trying to run the following VDB in Teiid Server 9.2 the folowing problem arises:

       

      WARN  [org.teiid.PROCESSOR] (Worker51_QueryProcessorQueue4864) 2pvdXGqiFK9D TEIID30020 Processing exception for request 2pvdXGqiFK9D.0 'TEIID30167 org.teiid.jdbc.TeiidSQLException: Primary key is required on view Xpto.xpto to perform

      materialization load'. Originally ProcedureErrorInstructionException 'Primary key is required on view Xpto.xpto to perform materialization load'. Enable more detailed logging to see the entire stacktrace.

      ....

      Caused by: org.teiid.client.ProcedureErrorInstructionException: TEIID30167 org.teiid.jdbc.TeiidSQLException: Primary key is required on view Xpto.xpto to perform materialization load

       

       

      The VDB is like this:

       

      <model name="Xpto" type="VIRTUAL">
<metadata type="DDL"><![CDATA[


CREATE VIEW xpto (
country varchar(200)
INDEX (country)
)
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'MnomMaterialized.xpto_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
"teiid_rel:MATVIEW_STATUS_TABLE" 'XptoMaterialized.status',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'XptoMaterialized.xpto_cache_staging',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'
)
AS
SELECT A.country
FROM (EXEC FileXpto.getTextFiles('Xpto.csv')) AS f,
TEXTTABLE(f.file COLUMNS country string DELIMITER ',' SKIP 4) AS A;


]]>
</metadata>
</model>

       

       

      I've tried several solutions like creating a column with serial datatype, an auto increment column in MySQL but Teiid does not handle this.

       

      How can, in this case, can be created a primary key so Teiid loads the view?

       

      By the way, country cannot be a PK since its values are not unique (this is only a sample. In reality the table as several columns).