update and delete with WHERE condition not working with Delimited flat file(header true)

[rujutas]

Hi Team,

We have observed that in case of Delimited Header True files at FTP/Sftp location, we are unable to execute update and delete queries specifically having where conditions. We are able to execute simple queries(without where clause) successfully.  Also we are able to execute queries successfully with where clause in case of Delimited Header False and Fixed width files.

Given below is the query which we are unable to execute on Header True file along with error message.

Query - Update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.acct_number='123';

Error Message - TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates.

Regards,

Rujuta S

[shawkins]

There isn't quite enough information here to tell what is occurring.  How is TRANSACTION_DTL_new defined - is it a table or a view?  If it's a table, what translator is being used?  If it's a view, what is the view definition and associated translators?

The error message may be entirely valid if the source does not in fact support any searchability.

[rujutas]

TRANSACTION_DTL_new.txt is my delimited (header true) file. Which doesn't have schema file.

Update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.acct_number='123';

In above query ,

flatfileDeli : connection name

TRANSACTION_DTL_new : file name (which teiid internally refer as table name)

Below is data stored in TRANSACTION_DTL_new.txt 

acct_number,product_type,prod_desc

123,Sports,sports related prod

124,Beauty,beauty related prod

translator used is jdbc-ansi

<translator name="jdbc-ansi" module="org.jboss.teiid.translator.jdbc"/>

Please let me know if any further information required.

[rareddy]

How is your VDB defined as? You can also take look at these below examples as to how to read data from text files

Connect to a Flat File using Teiid

Text to Table with Teiid

BTW, Teiid does not support Updates/Deletes on text-based data, only SELECT.  If you want updates/deletes maybe you should look into bringing in H2 CSV support for that portion of it.

Ramesh..

[rujutas]

Thank you. Just to clear, when you are saying H2 CSV support are you referring to use h2 as a translator? We are currently using

Translator - jdbc-ansi

Driver - hxtt

As I mentioned before we are able to fire Update/Delete for delimited flat-file(header false) and fixed width flat file successfully. Issue is only with WHERE clause in case of delimited flat-file(header true).

[rareddy]

You can use "h2" as the translator. However, note that when you are using the H2 for managing the CSV as a table, Teiid does not know any details of what is going on underneath of H2, all it knows is it is working with H2 database.

> As I mentioned before we are able to fire Update/Delete for delimited flat-file(header false) and fixed width flat file successfully. Issue is only with WHERE clause in case of delimited flat-file(header true).

Can you explain how you are doing this? Are you using "saveFile" procedure in Teiid?

[rujutas]

We are not using saveFile()

We are passing teiid specific query directly to stmt.executeUpdate(query), where statement is created with teiid specific conncetion

Class.forName("org.teiid.jdbc.TeiidDriver");

connection = DriverManager.getConnection("jdbc:teiid:" + VDBName + "@mm://" + IPAddress + ":" + port + ";",

userName, password);

stmt = connection.createStatement();

stmt.executeUpdate("Update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports';");

Above statement running successfully.

If we use below code then it throws error : TEIID30253 Source UPDATE or DELETE command "UPDATE "flatfileDeli".TRANSACTION_DTL_new SET product_type= ? WHERE "flatfileDeli".TRANSACTION_DTL_new.acct_number= ?" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates.

Class.forName("org.teiid.jdbc.TeiidDriver");

connection = DriverManager.getConnection("jdbc:teiid:" + VDBName + "@mm://" + IPAddress + ":" + port + ";",

userName, password);

stmt = connection.createStatement();

stmt.executeUpdate("Update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.acct_number='123';");

[shawkins]

If there is no particular reason you using the jdbc-ansi translator, you should use the h2 translator as Ramesh is suggesting.  The jdbc-ansi translator has limited capabilities for maximum support.  In particular even casts as not supported.  So that's why you can easily get an exception about non-pushdown constructs.

It's not entirely clear from what you are showing if cast support is the issue as '123' should auto convert to a numeric value if acct_number is not typed as string.

So try the h2 translator and/or provide the full ddl for your scenario and we can determine if there is an issue that needs addressed.

[rujutas]

We tried H2 translator but no luck still facing same issue. we just connect to file which is attached, no schema available.

We tried different scenarios and found below points.

1. Delimited(header false)

In this case we can fire (Delete and Update) queries with WHERE clause but column used for condition should be of numeric datatype.

Data type is mentioned in schema file so its woking fine when we fullfil above condition.

eg.

update conn.TRANSACTION_DTL_new

    set TRANSACTION_DTL_new.product_type='sports'

where TRANSACTION_DTL_new.acct_number='123';

Here id has to be of numeric datatype(integer, float etc)

1. Delimited(header true)

In this case we don't have schema file so datatype is not mentioned, by default it takes every column as LONGVARCHAR.

So we are facing push-down issue.

eg.

update conn.TRANSACTION_DTL_new

    set TRANSACTION_DTL_new.product_type='sports'

where TRANSACTION_DTL_new.acct_number='123';

Here id is populated by dafault as longvarchar because schema is not available.

[shawkins]

> We tried H2 translator but no luck still facing same issue. we just connect to file which is attached, no schema available.

The difference in these scenarios will come down to the metadata being reported by the H2 driver.  Can you export the schema for the source model?  Or provide the datasource configuration h2 driver version, vdb, etc.?

For the ddl you can use the Admin method getSchema, or even use a sql client (dbeaver, squirrel) to observe the differences between these scenarios.