Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

Selek avatar image
Selek asked Selek action

Вызов Cassandra UDF через ODBC для MSSQL.

Installed:

  • Cassandra 3.11.9
  • ODBC DataStax Cassandra 2.6 64-bit (Win10)
  • MSSQL 2019

In MSSQL Linked Server to Cassandra.

Create simple table in COMMUNITY keyspace.

cqlsh> create KEYSPACE community
             ... WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};
cqlsh> use community ;
cqlsh:community> create TABLE odbctest(
             ... id uuid PRIMARY KEY,
             ... dt date,
             ... vl int
             ... );
cqlsh:community> insert INTO odbctest(id,dt,vl)
             ... values(91451832-74f7-4709-b180-7cb526184280,'2020-04-16',1);
 
cqlsh:community> select *
             ... from odbctest
             ... where id=91451832-74f7-4709-b180-7cb526184280;

 id                                   | dt         | vl
--------------------------------------+------------+----
 91451832-74f7-4709-b180-7cb526184280 | 2020-04-16 |  1

Edit cassandra.yaml

...
enable_user_defined_functions: true
enable_scripted_user_defined_functions: true
...

Create simple UDF.

cqlsh:community> CREATE FUNCTION myUDF(input int)
             ... CALLED ON NULL INPUT RETURNS int
             ... LANGUAGE javascript
             ... AS 'input';

cqlsh:community> select id,dt,vl,myUDF(vl) as udf_result from odbctest  ;

 id                                   | dt         | vl | udf_result
--------------------------------------+------------+----+------------
 91451832-74f7-4709-b180-7cb526184280 | 2020-04-16 |  1 |          1

Everything works

Now on the MSSQL side.

The first problem I encountered was quotation marks. MSSQL doesn't understand UUIDs without quotes.

1607560856000.png

It works with quotation marks.

1607560951860.png

I want to use a UDF in the request. Error.

1607561262697.png

OLE DB provider "MSDASQL" for linked server "CASSANDRA" returned message "[DataStax][CassandraODBC] (10) Error while executing a query in Cassandra: [33563136] : Invalid STRING constant (91451832-74F7-4709-B180-7CB526184280) for "id" of type uuid".

Removing the filter for ID. Error.

1607561407048.png

I tried using MIN(), MAX(), AVG()

cqlsh:community> select
              ... min(vl) as minFn,
              ... max(vl) as maxFn,
              ... avg(vl) as avgFn
              ... from odbctest
              ... where id=91451832-74f7-4709-b180-7cb526184280; 
 minfn | maxfn | avgfn 
-------+-------+-------
     1 |     1 |     1

In MSSQL it works!

1607561551670.png

I tried using WRITETIME()

cqlsh:community> select
              ... id, dt, vl,
              ... writetime(vl) as wt_vl
              ... from odbctest
              ... where id=91451832-74f7-4709-b180-7cb526184280; 
 id                                   | dt         | vl | wt_vl
--------------------------------------+------------+----+------------------
 91451832-74f7-4709-b180-7cb526184280 | 2020-04-16 |  1 | 1607555089708000

MSSQL - Error.

1607561649823.png

The entire text of the error:

OLE DB provider "MSDASQL" for linked server "CASSANDRA" returned message "[DataStax][CassandraODBC] (10) Error while executing a query in Cassandra: [33563136] : Invalid STRING constant (91451832-74f7-4709-b180-7cb526184280) for "id" of type uuid".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "
select
id, dt, vl,
        writetime(vl) as wt_vl
    from community.odbctest
    where id='91451832-74f7-4709-b180-7cb526184280'
" for execution against OLE DB provider "MSDASQL" for linked server "CASSANDRA". 

My ODBC settings

1607561973871.png

Changing the "query mode" parameter does not give results.


Как решить проблему вызова UDF?

odbc driver
1607560856000.png (53.1 KiB)
1607560951860.png (16.0 KiB)
1607561129133.png (25.5 KiB)
1607561262697.png (28.7 KiB)
1607561407048.png (29.1 KiB)
1607561551670.png (17.0 KiB)
1607561649823.png (28.3 KiB)
1607561948353.png (42.7 KiB)
1607561973871.png (42.7 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered Selek commented

[EDITED] Проблема выглядит как проблема с кавычками в UUID.

Добавляет ли OPENQUERY эти кавычки автоматически? Работает без кавычек, если их можно убрать.

The problem looks like an issue with the quotes on the UUID.

Here's my example schema:

CREATE TABLE community.odbctest (
    id uuid PRIMARY KEY,
    dt date
)

If I try to read the data with quotes on the UUID, I get the same error:

cqlsh:community> SELECT * FROM odbctest WHERE id = '91451832-74F7-4709-B180-7CB526184280';
InvalidRequest: Error from server: code=2200 [Invalid query] message="Invalid STRING constant (91451832-74F7-4709-B180-7CB526184280) for "id" of type uuid"

But it works without the quotes:

cqlsh:community> SELECT * FROM odbctest WHERE id = 91451832-74F7-4709-B180-7CB526184280;

 id                                   | dt
--------------------------------------+------------
 91451832-74f7-4709-b180-7cb526184280 | 2020-04-16

Does OPENQUERY add those quotes automatically? Can you try without the quotes? Cheers!

[Credit: Thanks to Aleksandr Sorokoumov (@aleksandr.sorokoumov_186950) for the solution.]

3 comments Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

OPENQUERY doesn't automatically add anything. Passes the request text as is.

Turned on the logging request

1607564400906.png

Query from OPENQUERY

select * from openquery(CASSANDRA,'My Simple Query String')

In log file:

...
...Simba::ODBC::StatementState::InternalPrepare: Preparing query: My Simple Query String
...
...Simba::CassandraODBC::CODataEngine::Prepare: The query failed to be parsed as SQL: [DataStax][SQLEngine] (31480) syntax error near 'My<<< ??? >>> Simple Query String'.. Falling back to trying CQL.
...

This query returns the result

select * from openquery(CASSANDRA,'select * from community.odbctest where id=''91451832-74f7-4709-b180-7cb526184280''')

1607565388070.png

In log file:

...
...Simba::ODBC::StatementState::InternalPrepare: Preparing query: select * from community.odbctest where id='91451832-74f7-4709-b180-7cb526184280'
...


0 Likes 0 ·
1607564400906.png (9.0 KiB)
1607565388070.png (17.3 KiB)

I don't know if functions are supported by the ODBC driver (Simba is the company that maintains the driver, not DataStax) and I don't have an environment where I can test this.

Can you please try it with query mode set to CQL?

0 Likes 0 ·
Selek avatar image Selek Erick Ramirez ♦♦ ·

Of course.

In this mode, requests are not answered

Log.

...LoadSettings: DataStax Cassandra ODBC DSN setting: "QueryMode" = "1"
...
...Preparing query: select * from community.odbctest where id='91451832-74f7-4709-b180-7cb526184280'
...SQLPrepareW: [DataStax][CassandraODBC] (10) Error while executing a query in Cassandra: [33563136] : Invalid STRING constant (91451832-74f7-4709-b180-7cb526184280) for "id" of type uuid

Log

...
...InternalPrepare: Preparing query: select * from community.odbctest where id=91451832-74f7-4709-b180-7cb526184280
...
...CQLQueryExecutor::ExecuteQuery: select * from community.odbctest where id=91451832-74f7-4709-b180-7cb526184280
...
...QueryExecutor::CheckExecutionFailed: No parameter sets succeeded for non-multi-parameter-set execution, considering an error...

Log.zip

0 Likes 0 ·
log.zip (6.0 KiB)