# Oracle Data into Elastic

**URL:** <https://discuss.elastic.co/t/oracle-data-into-elastic/286719>\
**Category:** Elasticsearch\
**Created:** [October 14, 2021, 12:24pm UTC](https://discuss.elastic.co/t/oracle-data-into-elastic/286719 "2021-10-14T12:24:27Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![tractor\_boy](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@tractor\_boy](https://discuss.elastic.co/u/tractor_boy)\
**Post date:** [October 14, 2021, 12:24pm UTC](https://discuss.elastic.co/t/oracle-data-into-elastic/286719/1 "2021-10-14T12:24:28Z")

</div>

Hi, I have been butting my head against a brick wall for over a week now trying to get the jdbc driver working in logstash, and have rethought the approach for getting data out of Oracle.

I am currently trying to prototype approaches for visualising data with kibana, and thought I am likely to have the requirement for hundreds of these jdbc configs for a final solution, all on there own schedule, which is unlikely to match the databases data generation schedule. So what would be a better thing to do than get oracle to send data directly into an elastic data store.

This would remove extraneous layers but also line up the schedules.

Are there an examples of how many this could be set up? preferably without interacting with logstash, but if that's not possible then can handle going via logstash, or some other mid tier interface.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 14, 2021, 12:50pm UTC](https://discuss.elastic.co/t/oracle-data-into-elastic/286719/2 "2021-10-14T12:50:14Z")

</div>

You need to send your existing data to Elasticsearch.  
That means:

- Read from the database (`SELECT * from TABLE`)
- Convert each record to a JSON Document
- Send the json document to Elasticsearch, preferably using the `_bulk` API.

Logstash can help for that. But I'd recommend modifying the application layer if possible and send data to Elasticsearch in the same "transaction" as you are sending your data to the database.

I shared most of my thoughts there: [Advanced Search for Your Legacy Application - -Xmx128gb -Xms128gb](http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/)

Have also a look at this ["live coding" recording](https://www.elastic.co/fr/blog/how-to-add-powerful-search-existing-sql-applications-elasticsearch-video-tutorial).

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [October 14, 2021, 12:58pm UTC](https://discuss.elastic.co/t/oracle-data-into-elastic/286719/3 "2021-10-14T12:58:50Z")

</div>

if you don't want to deal with logstash then use python. I have many python code which are pulling data out from oracle,mysql,sqlserver

you can use one of these method pyodbc or cx\_oracle  
here are sample example. In my personal view python is the way to go. I have many logstash pipeline as well. but once I started using python I like it better.

```auto
import pyodbc
#### connect to database
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
sql = """ select * from table """
    # Read data from SQLSearver
    df = pandas.read_sql(sql, cnxn)
    # Close sqlserver connection
    del cnxn

```

or you can use cx\_oracle

```auto
import cx_Oracle
## make connection to oracle
 dsn_tns = cx_Oracle.makedsn(db_server, '1521', service_name=db_name)
 sql=""" select * from table """
 # Open oracle connection and read all the records
 conn = cx_Oracle.connect(user=username, password=password, dsn=dsn_tns)
 # create cursor
 c = conn.cursor()
c.execute(sql)
for row in c:
   print (row[0], row[1]) # this will print first and second column

```

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [October 14, 2021, 1:12pm UTC](https://discuss.elastic.co/t/oracle-data-into-elastic/286719/4 "2021-10-14T13:12:56Z")

</div>

> [@dadoonet](#):
>
> Have also a look at this ["live coding" recording](https://www.elastic.co/fr/blog/how-to-add-powerful-search-existing-sql-applications-elasticsearch-video-tutorial)

Good artical. Thanks David

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 11, 2021, 1:13pm UTC](https://discuss.elastic.co/t/oracle-data-into-elastic/286719/5 "2021-11-11T13:13:46Z")

</div>

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