ODBC connection to elastic

Hi,
installed version details :
logstash-6.6.1-1.noarch
elasticsearch-6.7.0-1.noarch
kibana-6.6.1-1.x86_64

I am trying to configure a ODBC connection to my elastic node but I get error when setting up the connection , I tried to put "xpack.security.enabled: false" in elasticsearch.yml but no luck

error trace is as follows:
Wed Mar 27 10:44:45 2019 - [INFO] filelog_new()@log.c:248 driver version: 6.7.0(1b41af1+,u,r).
Wed Mar 27 10:44:45 2019 - [INFO] config_dbc()@connect.c:895 [DBC@0x0000005D9CD89610] connection security level: 0.
Wed Mar 27 10:44:45 2019 - [INFO] config_dbc()@connect.c:937 [DBC@0x0000005D9CD89610] connection SQL cusor closing URL: http://inn-elk-vm:9200/_xpack/sql/close.
Wed Mar 27 10:44:45 2019 - [INFO] config_dbc()@connect.c:948 [DBC@0x0000005D9CD89610] connection SQL query URL: http://inn-elk-vm:9200/_xpack/sql.
Wed Mar 27 10:44:45 2019 - [INFO] config_dbc()@connect.c:974 [DBC@0x0000005D9CD89610] connection root URL: http://inn-elk-vm:9200/.
Wed Mar 27 10:44:45 2019 - [INFO] config_dbc()@connect.c:1000 [DBC@0x0000005D9CD89610] follow: true.
Wed Mar 27 10:44:45 2019 - [INFO] config_dbc()@connect.c:1018 [DBC@0x0000005D9CD89610] timeout: 0.
Wed Mar 27 10:44:45 2019 - [INFO] config_dbc()@connect.c:1043 [DBC@0x0000005D9CD89610] max body size: 104857600.
Wed Mar 27 10:44:45 2019 - [INFO] config_dbc()@connect.c:1076 [DBC@0x0000005D9CD89610] fetch_size: none.
Wed Mar 27 10:44:45 2019 - [INFO] config_dbc()@connect.c:1093 [DBC@0x0000005D9CD89610] pack JSON: true.
Wed Mar 27 10:44:45 2019 - [DEBUG] config_dbc()@connect.c:1108 [DBC@0x0000005D9CD89610] version checking mode: s.
Wed Mar 27 10:44:45 2019 - [DEBUG] dbc_curl_init()@connect.c:620 [DBC@0x0000005D9CD89610] libcurl: new handle 0x000001A2E786DDC0.
Wed Mar 27 10:44:45 2019 - [DEBUG] dbc_curl_set_url()@connect.c:451 [DBC@0x0000005D9CD89610] URL type set to: 3.
Wed Mar 27 10:44:45 2019 - [DEBUG] write_callback()@connect.c:291 [DBC@0x0000005D9CD89610] libcurl: new data chunk of size [498] x 1 arrived; available buffer: 0/0.
Wed Mar 27 10:44:45 2019 - [DEBUG] write_callback()@connect.c:302 [DBC@0x0000005D9CD89610] libcurl: need to grow buffer for new chunk of 498 from 0 to 4096 bytes.
Wed Mar 27 10:44:45 2019 - [DEBUG] write_callback()@connect.c:335 [DBC@0x0000005D9CD89610] libcurl: copied 498B: { "name" : "elknode", "cluster_name" : "ELK-CLUSTER", "cluster_uuid" : ".......", "version" : { "number" : "6.7.0", "build_flavor" : "default", "build_type" : "rpm", "build_hash" : "8453f77", "build_date" : "2019-03-21T15:32:29.844721Z", "build_snapshot" : false, "lucene_version" : "7.7.0", "minimum_wire_compatibility_version" : "5.6.0", "minimum_index_compatibility_version" : "5.0.0" }, "tagline" : "You Know, for Search" }.

Wed Mar 27 10:44:45 2019 - [DEBUG] dbc_curl_perform()@connect.c:656 [DBC@0x0000005D9CD89610] libcurl: request answered, received code 200 and 498 bytes back.
Wed Mar 27 10:44:45 2019 - [DEBUG] check_server_version()@connect.c:1275 [DBC@0x0000005D9CD89610] read version number: [5] 6.7.0.
Wed Mar 27 10:44:45 2019 - [INFO] check_server_version()@connect.c:1300 [DBC@0x0000005D9CD89610] server and driver versions aligned to: 6.7.0.
Wed Mar 27 10:44:45 2019 - [DEBUG] do_connect()@connect.c:1372 [DBC@0x0000005D9CD89610] server version check at URL http://elknode:9200/_xpack/sql: OK.
Wed Mar 27 10:44:45 2019 - [DEBUG] EsSQLAllocHandle()@handles.c:303 [DBC@0x0000005D9CD89610] new Statement @0x000001A2EB6D0E30.
Wed Mar 27 10:44:45 2019 - [DEBUG] EsSQLGetTypeInfoW()@info.c:1217 [STMT@0x000001A2EB6D0E30] requested type description for type 0.
Wed Mar 27 10:44:45 2019 - [DEBUG] EsSQLFreeStmt()@handles.c:447 [STMT@0x000001A2EB6D0E30] ES-closing.
Wed Mar 27 10:44:45 2019 - [DEBUG] EsSQLFreeStmt()@handles.c:453 [STMT@0x000001A2EB6D0E30] closing.
Wed Mar 27 10:44:45 2019 - [DEBUG] clear_desc()@handles.c:120 [DESC@0x000001A2EB6D1738] clearing desc type 2.
Wed Mar 27 10:44:45 2019 - [DEBUG] attach_sql()@queries.c:488 [STMT@0x000001A2EB6D0E30] attaching SQL [11] SYS TYPES 0.
Wed Mar 27 10:44:45 2019 - [DEBUG] EsSQLExecute()@queries.c:2114 [STMT@0x000001A2EB6D0E30] executing SQL: [11] SYS TYPES 0.
Wed Mar 27 10:44:45 2019 - [DEBUG] serialize_statement()@queries.c:2063 [STMT@0x000001A2EB6D0E30] JSON serialized to: [63] {"query": "SYS TYPES 0", "mode": "ODBC", "client_id": "odbc64"}.
Wed Mar 27 10:44:45 2019 - [DEBUG] post_json()@connect.c:722 [STMT@0x000001A2EB6D0E30] POSTing JSON type 1: [63] {"query": "SYS TYPES 0", "mode": "ODBC", "client_id": "odbc64"}.
Wed Mar 27 10:44:45 2019 - [DEBUG] dbc_curl_set_url()@connect.c:451 [DBC@0x0000005D9CD89610] URL type set to: 1.
Wed Mar 27 10:44:45 2019 - [DEBUG] write_callback()@connect.c:291 [DBC@0x0000005D9CD89610] libcurl: new data chunk of size [273] x 1 arrived; available buffer: 0/0.
Wed Mar 27 10:44:45 2019 - [DEBUG] write_callback()@connect.c:302 [DBC@0x0000005D9CD89610] libcurl: need to grow buffer for new chunk of 273 from 0 to 4096 bytes.
Wed Mar 27 10:44:45 2019 - [DEBUG] write_callback()@connect.c:335 [DBC@0x0000005D9CD89610] libcurl: copied 273B: {"error":{"root_cause":[{"type":"security_exception","reason":"current license is non-compliant for [odbc]","license.expired.feature":"odbc"}],"type":"security_exception","reason":"current license is non-compliant for [odbc]","license.expired.feature":"odbc"},"status":403}.
Wed Mar 27 10:44:45 2019 - [DEBUG] dbc_curl_perform()@connect.c:656 [DBC@0x0000005D9CD89610] libcurl: request answered, received code 403 and 273 bytes back.
Wed Mar 27 10:44:45 2019 - [DEBUG] cleanup_curl()@connect.c:398 [DBC@0x0000005D9CD89610] libcurl: handle 0x000001A2E786DDC0 cleanup.
Wed Mar 27 10:44:45 2019 - [ERROR] attach_error()@queries.c:458 [STMT@0x000001A2EB6D0E30] request failure 403 body: len: 273, content: {"error":{"root_cause":[{"type":"security_exception","reason":"current license is non-compliant for [odbc]","license.expired.feature":"odbc"}],"type":"security_exception","reason":"current license is non-compliant for [odbc]","license.expired.feature":"odbc"},"status":403}.
Wed Mar 27 10:44:45 2019 - [DEBUG] attach_sql_error()@queries.c:388 [STMT@0x000001A2EB6D0E30] root cause(s) received: 1.
Wed Mar 27 10:44:45 2019 - [DEBUG] attach_sql_error()@queries.c:415 [STMT@0x000001A2EB6D0E30] reported failure: type: [18] security_exception, reason: [43] current license is non-compliant for [odbc], status: 403.
Wed Mar 27 10:44:45 2019 - [ERROR] attach_sql_error()@queries.c:436 [STMT@0x000001A2EB6D0E30] request failure reason: [63] security_exception: current license is non-compliant for [odbc].
Wed Mar 27 10:44:45 2019 - [DEBUG] post_diagnostic()@error.c:53 [STMT@0x000001A2EB6D0E30] diagnostic message: [Elastic][EsODBC 6.7.0(1b41af1+,u,r) Driver]security_exception: current license is non-compliant for [odbc] [107], native code: 403.
Wed Mar 27 10:44:45 2019 - [ERROR] load_es_types()@connect.c:2127 [STMT@0x000001A2EB6D0E30] failed to query Elasticsearch.
Wed Mar 27 10:44:45 2019 - [DEBUG] EsSQLFreeStmt()@handles.c:432 [STMT@0x000001A2EB6D0E30] unbinding.
Wed Mar 27 10:44:45 2019 - [DEBUG] check_access()@handles.c:1371 [DESC@0x000001A2EB6D12B0] Descriptor type: 1, Field ID: 1001, mode=read/write => grant: OK.
Wed Mar 27 10:44:45 2019 - [DEBUG] update_rec_count()@handles.c:1428 [DESC@0x000001A2EB6D12B0] new descriptor count equals old one, 0.
Wed Mar 27 10:44:45 2019 - [DEBUG] clear_desc()@handles.c:120 [DESC@0x000001A2EB6D12B0] clearing desc type 1.
Wed Mar 27 10:44:45 2019 - [DEBUG] clear_desc()@handles.c:120 [DESC@0x000001A2EB6D1738] clearing desc type 2.
Wed Mar 27 10:44:45 2019 - [DEBUG] clear_desc()@handles.c:120 [DESC@0x000001A2EB6D1BC0] clearing desc type 3.
Wed Mar 27 10:44:45 2019 - [DEBUG] clear_desc()@handles.c:120 [DESC@0x000001A2EB6D2048] clearing desc type 4.
Wed Mar 27 10:44:45 2019 - [ERROR] do_connect()@connect.c:1378 [DBC@0x0000005D9CD89610] failed to load Elasticsearch/SQL data types.
Wed Mar 27 10:44:45 2019 - [DEBUG] write_wstr()@util.c:539 [DBC@0x0000005D9CD89610] copying 107 wchars ([Elastic][EsODBC 6.7.0(1b41af1+,u,r) Driver]security_exception: current license is non-compliant for [odbc]) into buffer @0x0000005D9CD94700, of 2048B len; out-len @0x0000000000000000.
Wed Mar 27 10:44:45 2019 - [INFO] write_wstr()@util.c:547 [DBC@0x0000005D9CD89610] NULL required-space-buffer provided.

SQL access seems to be also enabled:

curl -X GET "elknode:9200/_xpack/"

"sql":{"description":"SQL access to Elasticsearch","available":true,"enabled":true}

Please format your code, logs or configuration files using </> icon as explained in this guide and not the citation button. It will make your post more readable.

Or use markdown style like:

```
CODE
```

This is the icon to use if you are not using markdown format:

There's a live preview panel for exactly this reasons.

Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.

Anyway, you need a platinum license to run ODBC driver. Or a trial.

I don't know how other people feel, but I strongly feel that the ODBC driver shouldn't be tied to any particular licence levels...........

I suspect that many will avoid the entire platform for this reason alone!

Thanks for the post readability suggestion.
Is trial license worth some cost or it can be attained free for some period of time. If free then can you please let me know the procedure to include the license for ODBC.

It's free for 30 days.
You just have to activate the license from Kibana management tab.
That's all.

Thanks, was able to enable the 30 days trail license as advised.

I was successfully able to connect to elastic via my BI tool and pull the tables data into my tool.
However surprisingly when I tried to join two tables I got below error:

[Elastic][EsODBC 6.7.0(1b41af1+,u,r) Driver]parsing_exception: line 43:4: Queries with JOIN are not yet supported
Unable to create extract

Is this something not supported in elastic SQL layer yet ? If not supported then is there any development going around this feature.

Elasticsearch does not support joins unless you are using the parent / child feature.

Sorry for being late to reply on this !
I have an index with data as :
{
"_index" : "qstat-2019.03.29",
"_type" : "doc",
"_source" : {
"Job_Number" : "150153525",
"Job_State" : "pending",
"hard_req_queue" : "all.q",
"Submission_time" :
}

As you could see that "Submission_time" field is empty which I would get from another dataset and was getting that data set into elastic into a different index and trying to create join between both the indexes based on "Job_Number" field. But as you said joins are not supported so is there a way that I can stream in the second data source and update the value of "Submission_time" in above shown index by matching the "job_Number" field.

Kindly excuse if I am asking some stupid question, as I am pretty new in elastic world and might be my thoughts are generating by comparing to relational databases.

My both the data sources are xml files so I am parsing the xml file in logstash before writing the data into elastic. So, I would parse the second xml in logstash to extract "job_Number" and "Submission_time" and write the later to the index doc shown above based on the matching "job_Number"

The best thing to do if possible is to do joins at index time. Index in the same document data coming from both sources for example.
But all that topic is definitely a new question so I suggest that you open a new discussion regarding this as it's not anymore "ODBC connection to elastic".

Sure , makes sense , will open a new thread.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.