Implementing the CData JDBC Connector for Elasticsearch

To implement the JDBC connector, you must run a script that leverages the Siren Investigate APIs and the CData JDBC Connector for Elasticsearch.

Prerequisites

Before you begin, ensure that you have downloaded the CData JDBC Connector for Elasticsearch by following the instructions on the CData website.

Preparing to run the script

The sample Python script must be tailored to your environment, by setting the following values:

  • INVESTIGATE_VERSION

  • INVESTIGATE_PROTOCOL

  • INVESTIGATE_SERVER

  • INVESTIGATE_PORT

  • INVESTIGATE_USER

  • INVESTIGATE_PASS

  • ES_USER

  • ES_PASS

You must also edit the jaydebeapi.connect parameters.

If authentication at Siren Investigate-level is not used, then INVESTIGATE_USER and INVESTIGATE_PASS can be set to any value.

If authentication at Elasticsearch-level is not used, then ES_USER and ES_PASS can be set to any value.

A file called requirements.txt is required to properly run the script. Create the file and insert the following parameters:

certifi==2020.4.5.1
chardet==3.0.4
idna==2.9
JayDeBeApi==1.1.1
JPype1==0.6.3
requests==2.23.0
urllib3==1.25.9

Sample script

import jaydebeapi
import json
import requests

INVESTIGATE_VERSION = '10.5.0-SNAPSHOT'
INVESTIGATE_PROTOCOL = 'http'
INVESTIGATE_SERVER = '127.0.0.1'
INVESTIGATE_PORT = 5606
INVESTIGATE_USER = 'sirenadmin'
INVESTIGATE_PASS = 'password'
ES_USER = 'sirenserver'
ES_PASS = 'password'

def sendRequest(urlPath):
    """
    :param urlPath: must include leading '/'
    :return: returns None on error and the answer from Investigate otherwise
    """
    global INVESTIGATE_PROTOCOL
    global INVESTIGATE_SERVER
    global INVESTIGATE_PORT
    global INVESTIGATE_USER
    global INVESTIGATE_PASS
    result = None
    session = requests.Session()
    url = '{0}://{1}:{2}{3}'.format(INVESTIGATE_PROTOCOL, INVESTIGATE_SERVER,
                                    INVESTIGATE_PORT, urlPath)
    headers = {'Content-Type': 'application/json;charset=UTF-8'}
    # try to send the URL with just authorization parameters
    r = session.get(url, auth=(INVESTIGATE_USER, INVESTIGATE_PASS), headers=headers)

    if r.status_code != 200:
        print('Status code: {0}\n{1}'.format(r.status_code, r.text))
    else:
        result = r.json()
    return result

def getAllDashboards():
    result = sendRequest('/api/generate-query/dashboard')
    return result

def getAllSearches():
    result = sendRequest('/api/generate-query/search')
    return result

searches = getAllSearches()
print('Searches found:')
for s in searches:
    print('  - {0}'.format(s['title']))

dashboards = getAllDashboards()
print('Dashboards found:')
for d in dashboards:
    print('  - {0}'.format(d['title']))

# pick query and index from the 'all companies' dashboard
for d in dashboards:
    if d['title'] == 'all companies':
        index = ','.join(d['index'])
        q = d['query']
        break

print('index: {0}'.format(index))
print('query: {0}'.format(q))

conn = jaydebeapi.connect("cdata.jdbc.elasticsearch.ElasticsearchDriver",
                          "jdbc:elasticsearch:Server=https://127.0.0.1;Port=9220;",
                          {'Other': "SearchURLPrefix=siren",
                           'SSLServerCert': "*",
                           'user': ES_USER, 'password': ES_PASS},
                           '/path/to/cdata.jdbc.elasticsearch.jar')

curs = conn.cursor()
query = "SELECT city, label, founded_date FROM {0} WHERE DSLQuery('{1}') LIMIT 2".format(index, json.dumps(q))

curs.execute(query)
fieldNames = []
for fieldData in curs.description:
    fieldNames.append(fieldData[0])
done = False
while not done:
    rec = curs.fetchone()
    if rec:
        print('{0}'.format(dict(zip(fieldNames, rec))))
    else:
        done = True
curs.close()
conn.close()

Results

If you downloaded the Siren Platform Preloaded Demo, started Siren, and executed the script above, the resulting output would be as follows:

Searches found:
  - Articles
  - Companies
  - Companies with a deadpooled date
  - Investments
  - Investors
  - searchall
Dashboards found:
  - Topic explorer
  - Dashboard 360 Investors
  - all articles
  - all companies
  - Companies timeline analysis
  - Graph Browser
  - Investments
  - Investors
  - Search Engine
  - Getting Started
index: company
query: {'bool': {'must': [{'match_all': {}}, {'range': {'founded_date': {'gte': -3144663300000, 'lte': 1588929480000, 'format': 'epoch_millis'}}}], 'must_not': []}}
{'city': None, 'label': 'Aeropostale', 'founded_date': '2013-03-22 01:00:00'}
{'city': 'Sunnyvale', 'label': 'Bag of goodies', 'founded_date': '1997-11-30 01:00:00'}