Support for UUID columns in Cassandra Translator

[vishnusv]

Environment: Teiid 9.1, JDK 8, Cassandra 2.1.15.1416

We have a table in cassandra

CREATE TABLE tableX (

rowkey text,

tableid uuid,

dtimcreation timestamp,

dtimlastchange timestamp,

name text,

PRIMARY KEY (rowkey, tableid)

)

One of the primary keys is of datatype UUID.

The table has millions of records. In the VDB the tableid data type is represented as an object.

When I try to query the data using a JDBC client, we are getting the  values for tableid column as <UnknownType (2,000)> . We can cast the data to string and see the values.

However we have a problem applying a criteria on the uuid column.

select * from tableX where tableid=267ccc50-ee79-3f51-8fb6-65d587b2bc56 doesn't work.

Alternatively

select * from tableX where convert(tableid,string)='267ccc50-ee79-3f51-8fb6-65d587b2bc56'  works, but won't push down the query and tries to fetch all the data in tableX before filtering the records. This takes a lot of time compared to directly going against CQL.

Any suggestions on how to handle this scenario.

Thanks,

Vishnu

[rareddy]

Vishnu,

You are correct, this should have modeled as "string" rather than "object". You can log a JIRA? we will fix. As a workaround, you can add like following to ALTER the metadata

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="users" version="1">    
    <property name="cache-metadata" value="true" />
    <model name="Users" visible="false">
        <source name="cassandra" translator-name="translator-cassandra" connection-jndi-name="java:/demoCassandra" />
        <metadata type="DDL"><![CDATA[
            ALTER FOREIGN TABLE "Customer" ALTER COLUMN "id" TYPE string;
        ]]> </metadata>
    </model>
</vdb>

see in above I changed the column "id" type to string. Let us know if that works for you.

Ramesh..

[vishnusv]

Ramesh,

Thanks for the recommendation.  However TYPE keyword on "ALTER FOREIGN TABLE "Customer" ALTER COLUMN "id" TYPE string;" doesn't seem to be recognized in Teiid 9.1.  The documentation seems to suggest that this has been introduced in Teiid 9.2?.  I will upgrade to Teiid 9.2 and check this behaviour. Is there an alternative recommendation against Teiid 9.1?.

Thanks,

Vishnu

[rareddy]

Sure, we can kind of workaround with different name for the column name. Try like

ALTER FOREIGN TABLE "Customer" ALTER COLUMN "id" OPTIONS(SET SELECTABLE FALSE)

ALTER FOREIGN TABLE "Customer" ADD COLUMN idtoo varchar(50) OPTIONS(SELECTABLE true, NAMEINSOURCE 'id');

where the first lime makes the previous one not selectable, then second one adds a new column with different name, but NAMEINSOURCE points to original column.