jdbc,elasticsearch,elasticsearch-plugin,elasticsearch-jdbc-river,elastic , Elastic Search JDBC River Plugin SQL Server Integrated Security


Elastic Search JDBC River Plugin SQL Server Integrated Security

Question:

Tag: jdbc,elasticsearch,elasticsearch-plugin,elasticsearch-jdbc-river,elastic

So I've been working on implementing elastic search using the JDBC River plugin to get data from our SQL Server DB into elastic search.

I've got it working fine using the SQL Server credentials, but trying to use integrated security doesn't work. It will create the index, but it doesn't have data in it. The parameters I've been using are:

  PUT /_river/test_river/_meta 
    {  
       "type":"jdbc",
       "jdbc":
             {
                "driver":"com.microsoft.sqlserver.jdbc.SQLServerDriver", 
                "url":"jdbc:sqlserver://testServer:1433;databaseName=TestDb;
                integratedSecurity=true;",
                "user":"",
                "password":"",
                "sql": "select * from users",
                "poll":"30s",
                "index":"testindex",
                "type":"testusers"
             }
     }

I've tried quite a few things, including removing the user and password fields completely, removing integratedSecurity=true, but it gave the same result. I've checked on their github for the river plugin and it says this issue was fixed back in January, but it still doesn't seem to be working.

Also I'm using elastic search version: 1.5.1 and jdbc river plugin version : 1.4.0.10

Any help would be much appreciated


Answer:

Get rid of the user and password options. You're not gonna need them.

Check the console when running elasticserch.bat, you should see an error message when it tries to update the river. I'm going to go out on a limb and assume you're probably seeing an error stating that the file sqljdbc_auth.dll can't be found. If this is the case, you can download this file from here and copy the x64 version of sqljdbc_auth.dll to your java lib folder. For me, this folder is C:\ProgramData\Oracle\Java\javapath but you can type echo %path% in a console window to find yours.

Once you have followed these steps, restart elasticsearch.bat, and it should start processing your river. If not, post back with the output you're seeing when running elasticsearch.bat.


Related:


How to have multiple regex based on or condition in elasticsearch?


elasticsearch
I want to get all 000ANT and 0BBNTA from id, is there something similar to terms which works with regexp or is there any other way? Otherwise I will have to query elasticsearch for each item say 000ANT and 0BBNTA. Please help. Below is something that I am trying out...

Elasticsearch: How to query using partial phrases in quotation marks


elasticsearch
I am trying to implement a search behavior that supports partial phrases. A possible search input could look like this: example "hello world" elasticsearch Now I want to get all documents, that contain the words example and elasticsearch as well as the phrase hello world. As this is a very...

Postgresql Update JDBC


java,postgresql,jdbc
I want to update table: id integer NOT NULL, "first" character varying(255), "last" character varying(255), age integer, CONSTRAINT registration_pkey PRIMARY KEY (id) using method: void updateTable(String tableName, String columnName, String value, String columnName2, String value2) { try { String sql = "UPDATE " + tableName + " SET " +...

How to read data in logs using logstash?


elasticsearch,logstash
I have just started log stash, i have log files in that log file whole object is printed in the logs, Since my object is huge i cant write the grok patterns to the whole object and also i expecting only two values out of those object. Can you please...

ElasticSearch: How to search on different fields that are not related that are arrays of objects


elasticsearch
I want to search on different fields that are not related that are arrays of objects. I cannot find out how. Given the following mapping and data entry: I want to give the user the ability to search all possible fields in any combination. The user would use a form...

Parsing Google Custom Search API for Elasticsearch Documents


json,python-2.7,elasticsearch,google-search-api
After retrieving results from the Google Custom Search API and writing it to JSON, I want to parse that JSON to make valid Elasticsearch documents. You can configure a parent - child relationship for nested results. However, this relationship seems to not be inferred by the data structure itself. I've...

java.sql.SQLException: No suitable driver found for CONNECTION_URL


java,jdbc
i m getting error java.sql.SQLException: No suitable driver found for CONNECTION_URL This is a connection provider java file. package login; import java.sql.*; import java.sql.SQLException; public class ConnectionProvider { static Connection con = null; String database = "game"; String CONNECTION_URL = "jdbc:mysql://localhost:3306/" + database; String USERNAME = "root"; String PASSWORD =...

Query returns both documents instead of just one


c#,.net,elasticsearch,nest
var res = esclient.Search<MyClass>(q => q .Query(fq => fq .Filtered(fqq => fqq .Query(qq => qq.MatchAll()) .Filter(ff => ff .Bool(b => b .Must(m1 => m1.Term("macaddress", "mac")) .Must(m2 => m2.Term("another_field", 123)) ) ) ) ) ); As far as I can understand the bool and must together are the equivalent of the...

Elasticsearch boost per field with function score


elasticsearch,lucene,solr-boost
I have a query with different query data for different fields and ORed results. I also want to favor hits with certain fields. Ideally this would only increase ranking but would not cause results that did not contain some of the terms in the other fields. This would skew results...

Get elasticsearch result based on two keys


elasticsearch,elastic
I want to get all docs who's "PayerAccountId" should equal to "123" and "UsageStartDate" should be in range [2015-05-01 TO 2015-05-10] I am expecting something to run like this, curl -X GET http://192.168.1.3:9200/_all/_search -d '{"query" : {"match" : { "PayerAccountId:\"156023466485\" AND UsageStartDate:[2015-01-01 TO 2015-01-10]" }}}' Obviously it's not working any...

How to get duplicate field values in elastic search by field name without knowing its value


elasticsearch
I have a field "EmployeeName" in an elastic search index - and I would like to execute a query that will return me all the cases where there are duplicate values of "EmployeeName". Can this be done? I found more_like_this but this requires field value for "like_text". But my requirement...

Docker container http requests limit


http,elasticsearch,docker
I'm new to Docker so, most likely, I'm missing something. I'm running a container with Elasticsearch, using this image. I'm able to setup everyhing correctly. After that I was a using a script developed by a collegue in order to insert some data, basically querying a MySQL database and making...

Elasticsearch and C# - query to find exact matches over strings


c#,.net,database,elasticsearch,nest
I need a way to search documents using a plain exact match over two or multiple fields which are of type "string" and "integer". I'd like to avoid standard query as I don't care about scoring or best match, just a yes/no outcome if both the fields match or not....

Elasticsearch geospatial search, problems with index setup


elasticsearch,geospatial
I'm trying to search for documents previously added to an index, which has been configured to allow geospatial queries (or so I think). My elasticsearch instance is hosted on qbox.io. This is the code I wrote to create an index from the command line curl -XPOST username:[email protected]/events -d '{ "settings"...

Elasticsearch NumberFormatException when running two consecutive java tests


java,date,elasticsearch,numberformatexception,spring-data-elasticsearch
I have two test in a class, each of them containing the following query: SearchQuery searchQuery = new NativeSearchQueryBuilder().withQuery(matchAllQuery()).withFilter(rangeFilter("publishDate").lt(date)).build(); In one of the tests, the number of the results elasticsearchTemplate.count(searchQuery, Article.class), in the other one the returned values are verified elasticsearchTemplate.queryForPage(searchQuery,Article.class) If I run any of these two tests separately,...

ElasticSearch- “No query registered for…”


search,indexing,elasticsearch
ElasticSearch returns me "No query registered for [likes_count]" error when trying to look up entries using the following query. The field likes_count is a new field of documents and does not exist in every document. The same query works without the sort part. Why does this error appear? Thanks {...

How to write search queries in kibana using Query DSL for Elasticsearch aggregation


elasticsearch,querydsl,kibana-4
I am working on ELK stack to process Apache access logs. Spent a lot of time understanding Query DSL format so that more complex queries can be written. Currently am facing issues with running the queries in kibana interface but the same queries work just fine when posted using curl...

Get the value of the last inserted record


java,jdbc
I am trying to get the stop_name of the last inserted row in the table with preparedStatement. How can I get the last inserted one? I appreciate any help. behavoiur table: CREATE TABLE IF NOT EXISTS behaviour( behaviour_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, mac VARCHAR(30) NOT NULL, stop_name VARCHAR(30)...

NEST - Using GET instead of POST/PUT for searching


c#,elasticsearch,nest
Is there a way to tell NEST to use GET instead of POST when performing searches? Similar to how the ElasticSearch documentation shows CURL using GET I'd like to use GET when using NEST instead of using POST as it currently does.

MultiMatch query with Nest and Field Suffix


c#,elasticsearch,nest
Using Elasticsearch I have a field with a suffix - string field with a .english suffix with an english analyser on it as shown in the following mapping ... "valueString": { "type": "string", "fields": { "english": { "type": "string", "analyzer": "english" } } } ... The following query snippet won't...

How to compute the scores based on field data in elasticsearch


elasticsearch
I have the following fields in documents { name: "Pearl", age : 43, weight: 54, bodyWeight : 103, height : 1.8 } Now i want to get scores for the documents based on the bodyWeight to height ratio of the documents. How to do that?...

Why does my Tomcat only open 8 JDBC connections


java,jdbc,tomcat8
When setting up the database connections in Tomcat 8, for some reason Tomcat is not following what I configured in the context.xml, with as result that I run out of connections, leading to resource contentions at the application server side (BLOCKED/WAITING threads). I always have 8 connections (show processlist in...

Elasticsearch aggregations over regex matching in a list


regex,elasticsearch
My documents in elasticsearch are of the form { ... dimensions : list[string] ... } I'd like to find all dimensions over all the documents that match a regex. I feel like an aggregation would probably do the trick, but I'm having trouble formulating it. For example, suppose I have...

Can't insert or update record to database jsp


java,jsp,jdbc
I am creating a form that is used to insert, update and delete data in a database, managed to do everything right but update and insert are not functioning. Delete works fine. Any help with solution will be highly appreciated FirstController package com.firstmohform.controller; import com.firstmohform.dao.FirstDAO; import java.io.IOException; import java.io.PrintWriter; import...

ElasticSearch - Configuration to Analyse a document on Indexing


elasticsearch
In a single request, I want to retrieve documents from a SOR, store them in ElasticSearch, and then search those documents using the ES search API. There seems to be some lag from the time the document is indexed and the time it is analyzed and ready to be searched....

Get document on some condition in elastic search java API


java,elasticsearch,elasticsearch-plugin
As I know we can parse document in elastic search, And when we search for a keyword, It will return the document using this code of java API:- org.elasticsearch.action.search.SearchResponse searchHits = node.client() .prepareSearch() .setIndices("indices") .setQuery(qb) .setFrom(0).setSize(1000) .addHighlightedField("file.filename") .addHighlightedField("content") .addHighlightedField("meta.title") .setHighlighterPreTags("<span class='badge badge-info'>") .setHighlighterPostTags("</span>") .addFields("*", "_source")...

Elasticsearch - Query document missing an array value


elasticsearch
I would like to query my elasticsearch index in order to retrieve the documents that don't contain a specific value in an array. For instance, if my query is : { "query": { "bool": { "must": [ { "match_all": {} } ], "must_not": [], "should": [] } }, "from": 0,...

Strange behaviour of limit in Elasticsearch


python,elasticsearch
I tried two queries. First one looks like this (it simply lists all data): # listing 1 from elasticsearch import Elasticsearch from elasticsearch_dsl import Search, Q, F .... .... connection etc s = Search(using=db,index="reestr") rows = s.execute() for r in rows: print(r) listing 1 prints out all documents from the...

Javascript: Altering an object where dot notation is used [duplicate]


javascript,jquery,elasticsearch
This question already has an answer here: How to access object properties containing special characters? 1 answer I'm building an Elasticsearch search interface. My method is to build the initial query object, and then alter it depending on the user input. In the filter part of my object, I...

elastic search sort in aggs by column


sorting,elasticsearch,group-by,order
I am trying to sort in elastic search in aggs, equivalent in mysql "ORDER BY Title ASC/DESC". Here is the index structure: 'body' => array( 'mappings' => array( 'test_type' => array( '_source' => array( 'enabled' => true ), 'properties' => array( 'ProductId' => array( 'type' => 'integer', 'index' => 'not_analyzed'...

ElasticSearch REST - insert JSON string without using class


elasticsearch,elastic,elasticsearch-net
I am looking for an example where we can push below sample JSON string to ElasticSearch without using classes in REST api. { "UserID":1, "Username": "Test", "EmailID": "[email protected]" } We get the input as xml and we convert it to JSON string using NewtonSoft.JSON dll. I know REST api is...

indexing names in json using elasticsearch in couchdb


json,elasticsearch,couchdb,elasticsearch-river
I am trying to implement full-text query for my json documents. I want to search by title. My json is as follows: { "release":{ "genres":{ "genre":"Electronic" }, "identifiers":{ "identifier":[ { "description":"A-Side", "value":"MPO SK 032 A1 G PHRUPMASTERGENERAL T27 LONDON", "type":"Matrix / Runout" }, { "description":"B-Side", "value":"MPO SK 032 B1", "type":"Matrix...

Re-index object with new fields


elasticsearch,nest
It seems like as long as the id field is maintained, its super easy to re-index a document by simply calling Index(), but is there a way to given an object was updated and new fields were added, to have it include these new fields in the index? I'm not...

How to configure JDBC Connection Pool in Tomcat 5.5 for Oracle R12?


jdbc,connection-pooling,tomcat5.5
My Database is an Oracle R12 instance i.e 11g. The Tutorial for configuring the JDBC Connection pool shows examples up to Oracle 10g only. Link Here How do I configure a JDBC connection pool for Oracle R12 in a Tomcat 5.5 instance ? More info:: My Machine is HP-UX PA-RISC...

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver [Eclipse, Tomcat]


java,mysql,eclipse,tomcat,jdbc
I know that's an over-asked question, but still I can't get this thing right. I'm running a web application on Eclipse who has to interact with a mysql database. It all runs on a tomcat 7.0.27 server on localhost. As much as I try to add my my mysql-connector-java-5.1.21-bin.jar to...

MS SQL JDBC connection with MS SQL Server 2012


sql-server,jdbc
I use to use MYSQL for my database needs. I switched to MSSQL because somebody wanted me to develop an application on it. I used MS SQL Server 2008 earlier and then upgraded to MS SQL Server 2012. In JDBC, I used the following: String connectionUrl="jdbc:sqlserver://"+sname+";DatabaseName="+dbname; where sname is my...

ElasticSearch asynchronous post


database,post,asynchronous,elasticsearch,get
I'm posting data on my ElasticSearch database. I've noticed that data is not immediately available, it requires some milliseconds to show up in a GET request. I can live with that (after all, the calls are asynchronous so this behavior is expected) but in my test code I need to...

How to use arrays in lambda expressions?


c#,elasticsearch,nest
I am writing a program with NEST library of ElasticSearch. I want to write a lambda expression for a function with this argument: HighlighDescriptor<parentdocument> HighlighDescriptor.onFields (param Action<HighlightFieldDescriptor<ParentDocument>>[] fieldHighlighters) I don't know what is the array in the function argument?...

Operator '??' cannot be applied to operands of type IQueryContainer and lambda expression


c#,elasticsearch,nest
I am trying to create a method to process a certain query. I follow an example posted on the Nest repository (line 60), but still the MatchAll is not recognized by the compiler and if I try to build the solution, the error that shows is: Operator '??' cannot be...

Elasticsearch standard analyser stopwords


elasticsearch
I am trying to guess what is the default stopwords list in standard analyzer in elasticsearch. I run version 1.3.1, and it seems to me that the English list is used, because running a wildcard query like this { "wildcard" : { "name" : { "wildcard" : "*in*" } }...

Insert date and get it in wrong format


java,sqlite,datetime,jdbc
I insert the current date in a table. I have a method to get the current datet: private static java.sql.Date getCurrentDate() { java.util.Date today = new java.util.Date(); return new java.sql.Date(today.getTime()); } Or in some cases I get the date from a from in a String format e.g. 2016-10-12 and then...

elasticsearch aggregation group by null key


elasticsearch
here is the data in my elasticsearch server: {"system": "aaa"}, {"system": "bbb"}, {"system": null} I want to get the statistics for system. then I did the query: { "aggs" : { "myAggrs" : { "terms" : { "field" : "system" } } } it gives me the result: { "key":...

How to determine which type of sql query is entered by user in java


java,mysql,jdbc
I was working on simple project which takes sql query from user and displays the results in JTable I want to determine if select statement was entered or any other DML/DDL statement was entered so I can call executeQuery() or executeUpdate() accordingly. I thought of checking the query if it...

Not able to access Kibana running in a Docker container on port 5601


elasticsearch,docker,dockerfile,kibana-4
I have built a docker image with the following Docker file. # gunicorn-flask FROM devdb/kibana MAINTAINER John Doe <[email protected]> ENV DEBIAN_FRONTEND noninteractive RUN apt-get update RUN apt-get install -y python python-pip python-virtualenv gunicorn # Setup flask application RUN mkdir -p /deploy/app COPY gunicorn_config.py /deploy/gunicorn_config.py COPY app /deploy/app RUN pip install...

NEST ElasticSearch.NET Escape Special Characters


c#,elasticsearch,nest
I have been experimenting with the use of the NEST client for Elastic Search, but seem to have hit a barrier when filtering on a term which contains special/reserved characters such as '/' Below is a JSON representation of my model.. "categories": { "count": 1, "default": "root/Hello/World/Category", } When submitting...

Create a variable Date in format “yyyy-mm-dd”


java,sqlite,date,jdbc
I am trying to insert to my table, a date in format "yyyy-mm-dd". My table has a field date type DATETIME and I use SQLite DB SYSTEM. I need to be in this format, so later to have the option to select dates from - then. My table: CREATE TABLE...

Check if value exists in MySQL DB in Java?


java,mysql,database,jdbc
I'm creating a game right now that stores quest values into a DB when a player completes his or her quest. The issue I'm having right now is writing a method that searches the MySQL table for the player id and the quest id to check if he or she...

Get the last record even when the table contains one record


java,mysql,jdbc
I am trying to get the last record in my table with prepared statement. I have tried both quieries below but I am facing problem to get it when the table just contains one record in it. I am getting result when I execute the query alone but when I...

Bad scoring due to different maxDocs of IDF


elasticsearch
I have two documents with a field title of: News New Website If I search for the term new website the score for the News document is much higher than the other one which is obviously not what I want. I wrapped an explain around it and got: 'hits': [{'_explanation':...

All I want is to access a H2 mem database in Websphere V8 using JPA 2.0


java,jpa,jdbc,websphere,persistence.xml
I've been struggling with this for days now and I searched dozens of articles in StackOverflow and other dev sites. I used the Deployment Manager interface to configure a Websphere Application Server 8.0.0.3 with a H2 JDBC provider and a Data Source for my application using a tutorial I've found...