# Error during bulk import while inserting around 200K documents from SQL Server

**URL:** <https://discuss.elastic.co/t/error-during-bulk-import-while-inserting-around-200k-documents-from-sql-server/297942>\
**Category:** Elasticsearch\
**Created:** [February 22, 2022, 7:00pm UTC](https://discuss.elastic.co/t/error-during-bulk-import-while-inserting-around-200k-documents-from-sql-server/297942 "2022-02-22T19:00:34Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![girishraghu](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@girishraghu](https://discuss.elastic.co/u/girishraghu)\
**Post date:** [February 22, 2022, 7:00pm UTC](https://discuss.elastic.co/t/error-during-bulk-import-while-inserting-around-200k-documents-from-sql-server/297942/1 "2022-02-22T19:00:34Z")

</div>

Hi team,  
I am relatively new to the ELK world. I am writing a simple Python script to fetch around 200K records from a local instance of SQL Server and index them in my local ELK instance running on my laptop. The script fails with a read timeout error every time after inserting around 22K rows or so (this number varies every time).  
I am completely lost, any pointers would help.  
Error message -  
elasticsearch.exceptions.ConnectionTimeout: ConnectionTimeout caused by - ReadTimeoutError(HTTPConnectionPool(host='localhost', port=9200): Read timed out. (read timeout=10))

Code snippet -  
\<  
from collections import deque  
from http.client import REQUEST\_TIMEOUT  
import elasticsearch  
from elasticsearch import helpers  
import pandas as pd  
import sqlalchemy as sa  
from sqlalchemy.dialects.mssql import NVARCHAR, BIGINT  
import logging  
from settings import \*  
import time

# Set logging

logging.basicConfig(level=logging.DEBUG,  
format=' %(asctime)s - %(levelname)s - %(message)s')

# Database connect

conn\_smi = f"mssql+pyodbc://{SQL\_DB\_USER}:{SQL\_DB\_PASSWORD}@{SQL\_DB\_HOST}:{SQL\_DB\_PORT}/{SQL\_DB\_NAME}"   
f"?driver=ODBC Driver 17 for SQL Server"

# Main class.

class Instagram:  
def create\_index(self):  
logging.info(f" SMI =\> {conn\_smi}")  
sql\_db\_engine\_smi = sa.create\_engine(conn\_smi)

```
    # Data type dictionary
    col_types_dict={
    'Id':sa.types.BIGINT,
    'Name':sa.types.NVARCHAR(length=256),
    'Email':sa.types.NVARCHAR(length=256),
    'Category':sa.types.NVARCHAR(length=256),
    'ProfilePictureUrl':sa.types.NVARCHAR(length=2048),
    'HashTags':sa.types.NVARCHAR(length=4000),
    'UserId':sa.types.NVARCHAR(length=64),
    'UserName':sa.types.NVARCHAR(length=256),
    'Biography':sa.types.NVARCHAR(length=2048),
    'Followers':sa.types.FLOAT,
    'Following':sa.types.FLOAT,
    'EngagementRate':sa.types.REAL,
    'TotalEngagement':sa.types.REAL,
    'EngagementPerPost':sa.types.REAL,
    'Posts':sa.types.INT
    }
    
    str_sql = """SELECT ..............."""

    df_insta = pd.read_sql(sql=str_sql,
            con=sql_db_engine_smi)

    # Loop through the data frame.
    for index, record in df_insta.iterrows():
        insta_account = {}
        logging.info(f"Adding user => {record['UserId']}")
        insta_account['AccountId'] = int(record['Id'])
        insta_account['Name'] = str(record['Name'])
        insta_account['Email'] = str(record['Email'])
        insta_account['Category'] = str(record['Category'])
        insta_account['Profilepicturelink'] = str(record['ProfilePictureUrl'])
        insta_account['Hashtag'] = str(record['HashTags'])
        insta_account['Userid'] = str(record['UserId'])
        insta_account['Username'] = str(record['UserName'])
        insta_account['Biography'] = str(record['Biography'])
        insta_account['Followers'] = int(record['Followers'])
        insta_account['Following'] = int(record['Following'])
        insta_account['Engagementrate'] = float(record['EngagementRate'])
        insta_account['Totalengagement'] = float(record['TotalEngagement'])
        insta_account['Engagementperpost'] = float(record['EngagementPerPost'])
        insta_account['Posts'] = int(record['Posts'])

        yield insta_account

```

# \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

# Main Program

if **name** == " **main**":  
es = elasticsearch.Elasticsearch(timeout=30)  
data\_loader = Instagram()  
es.indices.delete(index="instagram",ignore=404)  
deque(helpers.parallel\_bulk(es,data\_loader.create\_index(),index="instagram",chunk\_size=300,thread\_count=2,queue\_size=1), maxlen=0)  
es.indices.refresh()

# \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

/\>

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [February 22, 2022, 10:55pm UTC](https://discuss.elastic.co/t/error-during-bulk-import-while-inserting-around-200k-documents-from-sql-server/297942/2 "2022-02-22T22:55:16Z")

</div>

Welcome to our community! 😃

Please format your code/logs/config using the `</>` button, or markdown style back ticks. It helps to make things easy to read which helps us help you 🙂

---

<div class="post-metadata">

**Author:** ![girishraghu](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@girishraghu](https://discuss.elastic.co/u/girishraghu)\
**Post date:** [February 23, 2022, 8:55am UTC](https://discuss.elastic.co/t/error-during-bulk-import-while-inserting-around-200k-documents-from-sql-server/297942/3 "2022-02-23T08:55:21Z")

</div>

Few more things -  
I am trying to insert around 200K documents. There are two big fields of length NVARCHAR(2048) and NVARCHAR(4000) respectively.

---

<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:** [March 23, 2022, 8:55am UTC](https://discuss.elastic.co/t/error-during-bulk-import-while-inserting-around-200k-documents-from-sql-server/297942/4 "2022-03-23T08:55:34Z")

</div>

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