SteemSQL database has been updated with a new set of features, including language detection.
It's has been quite long before I was able to release these new features. The main reasons are:
- The amount of data to manipulate is quite huge. SteemSQL database is now several gigabytes. I had to reprocess all existing posts and comments.
- Running service could not be interrupted as many processes regularly connect to SteemSQL to retrieve data.
Anyway, everything is now ready for you. Let's have a look at what's new.
1. New database schema
Comments table has been added
This table is populated with data that were previously stored in
A new record is created in
TxComments each time a post or comment is created or edited. The correlated data have to be updated each time a transaction related to post is generated. This resulted to the field contents in
TxComments being redundant and hard to manage.
The new design is much more simpler for data retrieval.
2. Post indicator
post boolean field has been added to the
Comments table structure.
This field indicate whether the record is a post or a comment. It's more required to the check the
title field content and record filtering is now blazing fast.
3. Language detection
Language detection has been added to the Database Injector process.
Each time a post or comment is created, the body content is analyzed to determine which language(s) it contains.
The result is stored in JSON format in the field
body_language in the table
As a post or comment can contains multiple languages, the result is an array.
Each object contains following values:
- Language code
- Confidence score
- Is reliable - true/false
If the language cannot be determined (ex: post containing pictures only), the array will be left empty
The result is something like this:
A post with several language will have his
body_language field set to something like
confidence value is related to how much text the post contains. The more text analyzed, the better the language analysis, the higher the
confidence value. Confidence is not a ratio and can be higher than 100.
If the post contains words in different languages,
isReliable will be set to
true to identify the most probable language, even if its
confidence value is lower.
If there is only one language and
isReliable is set to
false, this indicate
confidence is too low.
Be aware that language detector works using probabilities and sometimes it is not accurate with very short texts. The same happens when different languages used in the post have similar words.
The language detector can also be tricked when the content of a post contains lot's of "technical noise" like pictures, source code, edit tags, ...
I hope this will help non-English communities to better identify post in their respective language.
If you need help, have any comment or request, please use SteemSQL channel on steemit.chat.
Thanks for reading!
I hope you enjoyed reading this post. As mentioned in my witness thread, I'm committed to help non-English communities to develop.
You like this post, do not forget to upvote or follow me or resteem