Indexing into Elasticsearch from Oracle with PLSQL

I started to play around with Elasticsearch and one of the use cases I thought that I could apply in my job was to remove all heavy searches from the Oracle database and make the web front end quicker when searching and filtering complex data grids.
As we keep multiple logs in XML when entities ara changing, I realized that I could take some of these XMLs, transform them to json and index them into an Elasticsearch cluster having synchronized the Oracle database with the search engine (ES) based on different database events that would fired a trigger when desired.
Notes:
  • You may need some sys privileges for executing
  • Off course your database server needs to be able to make http or https requests to your Elasticsearch cluster ( http://yourelasticserver:9200)
At the end the solution is quite simple: Trigger an event and launch a html post request for indexing some data.
First at all you need to be able to use the utl_html package. 2 single steps:
1. Grant html privileges to your database user:
sql> grant execute on sys.utl_html to dbuser;
2. Create the ACL and grant it to your dbuser
begin
dbms_network_acl_admin.create_acl (‘ACL_elastic.xml’ ,’Network Access Control for Html Service’ ,’DBUSER’,TRUE,’connect’);
dbms_network_acl_admin.assign_acl (‘ACL_elastic.xml’,’yourelasticserver’, 9200, 9200);
commit;
end;
With the following package you should be able to send a HTML request. The public interfaces are 2 functions for indexing and bulk indexing.
create or replace package pac_elasticsearch as
function f_index( p_host varchar2
, p_port number
, p_index varchar2
, p_type varchar2
, p_message clob
, p_id varchar2 default null
)
return clob;
function f_index_bulk( p_host varchar2
, p_port number
, p_index varchar2
, p_type varchar2
, p_bulk_body clob
)
return clob;
end pac_elasticsearch;
create or replace package body pac_elasticsearch as


function post_request ( p_url varchar2, p_request_body clob)
return clob as
utl_req utl_http.req;
utl_resp utl_http.resp;
req_length binary_integer;
response_body clob;
res_length binary_integer;
buffer varchar2(2000);
amount pls_integer := 2000;
offset pls_integer := 1;
begin
utl_req := utl_http.begin_request (p_url, ‘POST’, ‘HTTP/1.1’);
utl_http.set_header(utl_req, ‘content-type’, ‘application/json’);
req_length := dbms_lob.getLength( p_request_body );
if req_length <= 32767 then
utl_http.set_header(utl_req, ‘content-length’, req_length );
utl_http.write_text(utl_req, p_request_body);
elsif req_length > 32767 then
utl_http.set_header ( utl_req, ‘Transfer-Encoding’, ‘chunked’ );
while (offset < req_length)
loop
dbms_lob.read( p_request_body, amount, offset, buffer);
utl_http.write_text ( utl_req, buffer );
offset := offset + amount;
end loop;
end if;
utl_resp := utl_http.get_response( utl_req);
— utl_http.read_text( utl_resp, response_body, 32767);
utl_http.read_text( utl_resp, response_body);
utl_http.end_response( utl_resp );
return response_body;
end post_request;



function f_index( p_host varchar2
, p_port number
, p_index varchar2
, p_type varchar2
, p_message clob
, p_id varchar2 default null
)
return clob
is
v_url clob;
begin
v_url := ‘http://&#8217;||p_host||’:’||p_port||’/’||p_index||’/’||p_type;
if ( p_id is not null) then
v_url := v_url||’/’||p_id;
end if;
return request ( v_url, p_message );
end f_index;



function f_index_bulk( p_host varchar2
, p_port number
, p_index varchar2
, p_type varchar2
, p_bulk_body clob
)
return clob
is
v_url clob;
begin
v_url := ‘http://&#8217;||p_host||’:’||p_port||’/’||p_index||’/’||p_type||’/_bulk’;
return post_request ( v_url, p_bulk_body );
end f_index_bulk;
end pac_elasticsearch;
Now you should be able to directly index data into Elastcisearch from Oracle.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s