# Ingest data from Relational DB to Elasticsearch
**URL:** https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376
**Category:** Elasticsearch
**Created:** [August 31, 2022, 3:53pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376 "2022-08-31T15:53:18Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@lcui\_dxc](https://discuss.elastic.co/u/lcui_dxc)
#### Post date: [August 31, 2022, 3:53pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/1 "2022-08-31T15:53:18Z")
Hello there:
We are building a search engine with Elastic, the source data is on a relational DB. We need to ingest data from the DB to Elasticsearch. We know it is doable using JDBC Input Plugin on Logstash to ingest data to ELK. Are there any other options? I'm thinking to use Python?
BTW, this is self-managed ELK stack not Elastic Cloud.
Please advise
Thank you in advance
Li
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [August 31, 2022, 7:04pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/2 "2022-08-31T19:04:34Z")
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: [https://david.pilato.fr/blog/2015-05-09-advanced-search-for-your-legacy-application/](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).
---
### Author:  [@lcui\_dxc](https://discuss.elastic.co/u/lcui_dxc)
#### Post date: [September 1, 2022, 6:19pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/3 "2022-09-01T18:19:27Z")
Thanks a lot...
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [September 1, 2022, 7:17pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/4 "2022-09-01T19:17:11Z")
I have started using logstash in beginning. but on later stage I have started liking python. and most all my pipeline now converted to python.
python gives me more control on transformation of data then logstash.
---
### Author:  [@lcui\_dxc](https://discuss.elastic.co/u/lcui_dxc)
#### Post date: [September 1, 2022, 7:33pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/5 "2022-09-01T19:33:03Z")
Hi Sachin,
Could you elaborate a little bit on how to? Would you please share some documents if possible?
Thanks
Li
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [September 1, 2022, 7:44pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/6 "2022-09-01T19:44:35Z")
it all depends on what database you use and what are your use cases are.
for example logstash was little harder to learn for me as it sometime requires grok pattern and all new filters. but there is a document in elk which are very good. I have a logstash code with 400 lines.
While python was easy for me to learn. you can repet the function that you use in one pipeline to second without copying to code.
---
### Author:  [@lcui\_dxc](https://discuss.elastic.co/u/lcui_dxc)
#### Post date: [September 1, 2022, 8:29pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/7 "2022-09-01T20:29:04Z")
The DB would be Sql Server....
I will check out the elastic documents using phython to ingest data from RDBMS, it seemed that I saw one but it was for ELK on Elastic cloud... I will check again.
Thanks
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [September 2, 2022, 12:40pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/8 "2022-09-02T12:40:28Z")
what you looking for? how to make connection to sql server using python?
```auto
### Now read from SQLSErver and process and write back to ELK
server = 'tcp:'
database = ''
username = ''
password = ''
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
your_sql = """ select * from your_table_name """
# Read data from SQLSearver to dataframe
df = pandas.read_sql(your_sql, cnxn)
```
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [September 2, 2022, 12:57pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/9 "2022-09-02T12:57:45Z")
and this is how you make connection to elk, this is for elasticsearch 7.x python library
```auto
es = Elasticsearch(elastic_hostnames, http_auth=(elastic_admin_user, elastic_admin_passwd), port=9200,
sniff_on_start=True,
sniff_on_connection_fail=True,
sniffer_timeout=3600,
timeout=3600, max_retries=4, retry_on_timeout=True
)
```
this is how you use bulk write. where mylist is ( list of dictionaries)
`pb = helpers.bulk(es, mylist)`
---
### Author:  [@lcui\_dxc](https://discuss.elastic.co/u/lcui_dxc)
#### Post date: [September 2, 2022, 1:14pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/10 "2022-09-02T13:14:49Z")
Hi Sachin,
Many thanks.. indeed. I'm trying to find the way using Python Library to read data from SQL DB to ELK and compare it with using JDBC on Logstash.
Thank you very much for your information, it really helps a lot..
Li
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 30, 2022, 1:15pm UTC](https://discuss.elastic.co/t/ingest-data-from-relational-db-to-elasticsearch/313376/11 "2022-09-30T13:15:31Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.