3 Replies Latest reply on Apr 26, 2017 12:47 PM by Ramesh Reddy

    Support for UUID columns in Cassandra Translator

    VISHNU SWAMINATHAN Newbie

      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

        • 1. Re: Support for UUID columns in Cassandra Translator
          Ramesh Reddy Master

          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..

          • 2. Re: Support for UUID columns in Cassandra Translator
            VISHNU SWAMINATHAN Newbie

            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

            • 3. Re: Support for UUID columns in Cassandra Translator
              Ramesh Reddy Master

              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.