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

Question

question

Selek asked Dec 9, '20 Selek action Dec 10, '20

Вызов 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.

It works with quotation marks.

I want to use a UDF in the request. 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".

Removing the filter for ID. Error.

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!

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.

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

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

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

DataStax Enterprise is powered by the best distribution of Apache Cassandra ™

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries.

Privacy Policy Terms of Use

Answer 1 · 2020-12-09T05:26:22Z

Erick Ramirez answered Dec 9, '20 Selek commented Apr 21, '21

[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 Share

10 |1000

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

Selek commented · Dec 10, 2020 at 01:57 AM

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

Turned on the logging request

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''')

In log file:

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

0 ·

1607564400906.png (9.0 KiB)

1607565388070.png (17.3 KiB)

Erick Ramirez ♦♦ commented · Dec 10, 2020 at 03:07 AM

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 ·

Selek Erick Ramirez ♦♦ commented · Dec 10, 2020 at 04:19 AM

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 ·

log.zip (6.0 KiB)

question

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

1 Answer

question details

OTHER RESOURCES