Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Using Elasticsearch as the Primary Data Store in an ETL Pipeline

vlkan.com

Post reply on HN
Print view View original

21–30 of 44 posts

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#21

Post by kn7 »

bobbyi_settv wrote: post #17
kn7 wrote: post #14

Earlier quoted context omitted.

That still doesn't guarantee that a consecutive read is gonna get the last state.

Yes it does. I'm not sure what you think the refresh operation is if not that. I've run countless integration tests with ES and never seen something fail due to refresh not working as advertised. If you have, what version of ES was it? Can you give some sample code that sporadically exhibits the problem?

We generally use index refresh in ITs (running ES in Docker) and it fails occasionally, which I believe the case described here: "The (near) real-time capabilities depend on the index engine used." https://www.elastic.co/guide/en/elasticsearch/reference/curr...

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#22

Post by atombender »

kn7 wrote: post #15
manigandham wrote: post #10

Earlier quoted context omitted.

Elasticsearch has a "refresh" query flag to force immediate consistency: https://www.elastic.co/guide/en/elasticsearch/reference/curr...

That still doesn't guarantee that a consecutive read is gonna get the last state. Welcome to the wonderful world of "eventual consistency".

The "refresh=wait_for" [1] index setting does guarantee that a subsequent read will get the data. It causes all shards to refresh:

    Refresh the relevant primary and replica shards
    (not the whole index) immediately after the operation
    occurs, so that the updated document appears in search
    results immediately
There's also the "wait_for_active_shards=" setting, which merely asks to wait until n shards have written the changes.

[1] https://www.elastic.co/guide/en/elasticsearch/reference/curr...

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#23

Post by IggleSniggle »

manigandham wrote: post #19
kn7 wrote: post #15

Earlier quoted context omitted.

That still doesn't guarantee that a consecutive read is gonna get the last state. Welcome to the wonderful world of "eventual consistency".

How so? That's the entire point of the feature. What else is there to update?

It guarantees that all replications will report state in sync with each other on search, not that the last reported state is the actual current state of the index.

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#24

Post by manigandham »

IggleSniggle wrote: post #23
manigandham wrote: post #19

Earlier quoted context omitted.

How so? That's the entire point of the feature. What else is there to update?

It guarantees that all replications will report state in sync with each other on search, not that the last reported state is the actual current state of the index.

As the numerous comments here and the documentation states, the refresh flag on your insert/update will ensure that changed data in that request is consistent for queries after.

Where did you get the behavior you described? Are you sure you're not confusing this for the separate refresh command itself? That is not attached to any particular insert/update.

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#25

Post by manigandham »

kn7 wrote: post #21
bobbyi_settv wrote: post #17

Earlier quoted context omitted.

Yes it does. I'm not sure what you think the refresh operation is if not that. I've run countless integration tests with ES and never seen something fail due to refresh not working as advertised. If you have, what version of ES was it? Can you give some sample code that sporadically exhibits the problem?

We generally use index refresh in ITs (running ES in Docker) and it fails occasionally, which I believe the case described here: "The (near) real-time capabilities depend on the index engine used." https://www.elastic.co/guide/en/elasticsearch/reference/curr...

That seems to be the issue then. The refresh flag should be passed in your insert/update/delete operations.

The refresh command can also be called (which is what you're doing) but this is a different operation and just triggers the index build with no guarantees that it finishes or is consistent with any particular data mutation.

Did you read the previously posted documentation for the refresh flag?

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#26

Post by billoneil »

> The reason PostgreSQL results were omitted is no matter what kind of optimization we throw at it, the benchmark always took more than 2 hours, regardless of partitioning, whereas MongoDB and Elasticsearch took a couple of minutes.

This seems like something was done incorrectly the comparison shouldn't be that drastic.

> Just one PostgreSQL 9.6.10 instance (shared_buffers = 128MB)

This looks way too low. The postgresql docs say a good starting point for shared_buffers is 25% of the server's memory. In this case that would be 32GB.

https://www.postgresql.org/docs/9.1/runtime-config-resource....

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#27

Post by scarface74 »

JohnBooty wrote: post #4

I'm certain I'm missing something very obvious about Elasticsearch and other NoSQL data stores. But in my brief experience with an Elasticsearch-backed web application I found it difficult to write integration tests for the portions of code that dealt with ES. Thanks to ES's "eventually consistent" nature, tests would fail intermittently because we'd be e.g. querying some data that was written to ES but hadn't been f…

Well, with C# and Linq with Mongo (you did day “other NoSQL” data stores) you can mock out the provider and test your Linq queries by substituting the IMongoQueryable with a List. ElasticLinq is a thing but I’ve heard mixed things about it.

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#28

Post by skullborg »

billoneil wrote: post #26

> The reason PostgreSQL results were omitted is no matter what kind of optimization we throw at it, the benchmark always took more than 2 hours, regardless of partitioning, whereas MongoDB and Elasticsearch took a couple of minutes. This seems like something was done incorrectly the comparison shouldn't be that drastic. > Just one PostgreSQL 9.6.10 instance (shared_buffers = 128MB) This looks way too low. The postgre…

They set out on a 4 year journey to improve their ETL, but didn't take 1 second to change a conservative global config default. Can barely take the rest of the article seriously after a blunder like that

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#29

Post by debarshri »

kn7 wrote: post #13
barbecue_sauce wrote: post #12

Earlier quoted context omitted.

I am not Debarsh, and I am not a data engineer, but isn't the purpose of ETL for transforming data into a more accessible/palatable form for BI?

Bol has plenty of other ETL pipelines for BI. What I meant is the data cooked for search is not (much) of interest to BI, yet. Though we do have other means to feed BI for search-relevant content.

To all fairness, you are right about oracle stuff ingrained in bol.com, however, I am not sure if I should go in detail, but the whole thing used to be like - Maintain event states with "versions" table and then run hadoop, spark jobs on them, and snapshot the latest computed state to oracle so that they could run BI on it.

But I understand now what you actually mean. I wouldn't call it ETL, as ETL is more about prepping the data for BI and not cooking data for search.

yea, I remember they used to have redwood for scheduling PL/SQL queries but I think majority of ETL jobs for BI were in hadoop/spark/flink.

Having said all these, I think it is quite some neat and cool engineering work, I hope you guys are successful implementing the solution.

Re: Using Elasticsearch as the Primary Data Store in an ETL Pipeline

#30

Post by [deleted] »

barbecue_sauce wrote: post #12
kn7 wrote: post #9

Earlier quoted context omitted.

Hey Debarsh! First, thanks for taking time to read such a lengthy post. If I am not mistaken the majority of the PL/SQL glue is owned by Gert, though you might recall better. Quite some VCS history was lost while migrating from SVN to Git. ;-) The reason we are "replicating" the entire data is to 1) determine the affected products and 2) re-execute the relevant configurations (facets, synonyms, etc.) while making ret…

I am not Debarsh, and I am not a data engineer, but isn't the purpose of ETL for transforming data into a more accessible/palatable form for BI?

[deleted]
Post reply on HN