DEV Community: vivek atwal

Path to Cypher Query tuning

vivek atwal — Thu, 27 Oct 2022 11:08:04 +0000

What are the things to learn to be able to tune cypher queries

Preparing for Query Tuning
How queries work in Neo4j here
Controlling Row Cardinality
Neo4j Behind the Scenes
Optimizing Property Access
Node Degree Shortcuts
Monitoring Running Queries

you should be able to:

Define the terms row and DB hit in the context of Cypher querying cypher-cardinality
Use EXPLAIN and PROFILE to identify weaknesses in a query plan
Use Cypher tools to minimize the number of rows processed in a query
Use best practices for minimizing property access
Use monitoring tools to identify the underlying causes of a long-running query 1 2

Resource to get learn above topics

logging in cloudwatch

vivek atwal — Mon, 04 Jul 2022 10:13:29 +0000

Settingup cloudwatch client in server

sudo aws configure
sudo yum install amazon-cloudwatch-agent

verify installation using below commands

cat ~/.aws/credentials
cat ~/.aws/config

To check cloudwatch status using below commands or here

sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -m ec2 -a status
sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -m ec2 -a stop
sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -m ec2 -a start

Setting up python environment


#pip install python-json-logger
#pip install boto3
#pip install watchtower

import boto3
from pythonjsonlogger import jsonlogger
import watchtower

credentials = boto3.Session().get_credentials()
access_key = credentials.access_key
secret_key = credentials.secret_key
region = ""

cloudwatch_client = boto3.client("logs", 
region_name=region,
aws_access_key_id=access_key,
aws_secret_access_key=secret_key
)



def get_logger(log_group, logger_name):    
    logger = logging.getLogger(logger_name)
    logger.setLevel(logging.DEBUG)

    formatter = jsonlogger.JsonFormatter(fmt='%(asctime)s :: %(lineno)s :: %(levelname)-8s :: %(name)s ::  %(message)s')

    handler = watchtower.CloudWatchLogHandler(log_group_name=log_group, log_stream_name=logger_name, boto3_client=cloudwatch_client)

    handler.setFormatter(formatter)
    logger.addHandler(handler)
    return logger


log_group = "app_name"
login_logger = get_logger(log_group, "login")

Reference:

Enabling virtualenv on jupyter notebook

vivek atwal — Thu, 30 Jun 2022 09:26:32 +0000

Introduction

Jupyter notebook make life easier for

Begineer to learn and understand step by step
Experts to explore data,visualiations.
Mentors to create course
Speakers for demonstration

While exploring same libraries have different versions and you need to test those version separately. They can be installed on separate virtualenvs.

Now in order to make this environment quickly accessible, we need to add this env to jupyter notebook as kernels.

Below are the steps:

Jupyter

Installation

sudo apt install jupyter-core

Activate the env and the install ipykernel

pip install ipykernel

Inorder to differentiate different environemnt, you have to name them, Name them with along with packgae version

ipython kernel install --user --name=envname_pkg_v1

To view all the environment

jupyter kernelspec list

Remove environment from jupyter

jupyter kernelspec uninstall envname

Aws Elasticsearch Backup

vivek atwal — Tue, 28 Jun 2022 20:37:58 +0000

Understanding basics

Elasticsearch backup works differently when compared to databases like mysql and mongodb where you can specify the backup path in the runtime,

while in case of elasticsearch, it expects user to specify the backup directory(repository) in advance and Specifying the backup directory in advance is called registering

so first step is to register repository and that is done using _snapshot api.

Syntax:

_snapshot/<repository_name>

Use Cases

Backup from one server and restore on same server
Bacukup from one server and restore to another server.

You will have to register repository from all the server where you want to restore.

References:

_stats in elasticsearch

vivek atwal — Mon, 03 Jan 2022 20:02:42 +0000

Interpretation of right data give you a lot of insights, and how to fetch those data plays an important role, Here we are going to see how to use _stats API to retrieve stats data.

GET my-index/_stats/

This API presents you with lots of data and interpreting the data and creating a meaning out of it, comes with right learning.

We are mainly going to focus on getting specific data from _stats.

Objects from _stats output can be divided in 3 groups:

Indexing includes ==> indexing, merge, refresh, flush, fielddata, segments, translog
Searching includes ==> get, search, query_cache, request_cache
others ==> docs, store, warmer, completion, recovery

Instead of blindly typing <my-index>/_stats it is very important to access specific data and limited data to focus on interpretation.

To know about indexing, try GET my-index/_stats/indexing

How _refresh work in ES in practice

vivek atwal — Mon, 03 Jan 2022 17:43:42 +0000

Learning By doing

Many Processes in elasticsearch are automated and help a new developer to start the journey faster and easily. But later instead of digging deeper, we rely on default setting, this restricts us from exploring and getting a deeper understanding and also limits our visualisation on the flow of working , it also make us poor in troubleshooting.

Create a index with 2 shard and set refresh_interval to 1 minute. We are setting the environment with this setting

PUT my-index
{
  "settings": {
    "index": {
      "number_of_shards": "2",
      "refresh_interval": "1m"
    }
  }
}

Execute to see no documents exists

GET my-index/_search

run _stats

GET my-index/_stats/refresh,flush

Before moving ahead its important to understand the structure of stats output, Look at primaries.refresh.totalvalue and primaries.flush.total, these values are of use to us.

Now lets insert a document

POST my-index/_doc/1
{
  "description": "inspecting index stats"
}

and lets query for a document GET my-index/_search you will not find any document, the documents are only visible after refresh operation takes place. (default refresh interval is 30s, we have to set it to 1 min for experiment purpose).

lets refresh the index manually, using below command

POST my-index/_refresh

now check the refresh count using GET my-index/_stats/refresh,flush and document using GET my-index/_search .

Lets Repeat same for deletion of document.

DELETE my-index/_doc/1
Check the refresh count using GET my-index/_stats/refresh,flush
Perform manual refresh for operation to reflect POST my-index/_refresh
After refresh , go to step 2.

What we saw in this article was,

changes are reflected after _refresh takes place.
How to check total number of refresh operation using _stats API.

These same steps can be performed for _flush and _forcemerge

Resume Parser

vivek atwal — Thu, 09 Sep 2021 20:01:11 +0000

Parsing Resume is not an easy task. This tasks comes with lot of challenges such as

resumes for different doamins(IT, commerce, etc) have differnet parsing challenges
dealing with different fileformats. (docx,pdf,images)
dealing with resume formats. (structure of resumes)
Identifying sections within resumes (Education, Work Experience, personal details, etc)
Develping an ontology for categorization of domain,skills, designation, etc

Hybrid approach

Rule Based Approach
Statistical Apporach
Machine Learning Based Approach

Rules Based Approach
- Write rules to parse resume and detect diferent sections of resumes using headings.
- Then write separate rules for each section
  - Work experience: parse comapny name, company location, duration(from-to-end Date)
  - Education: Parse Instituion name, year, etc
Statistical Based Approach
- Use statistics to identify the common skills in a particular domain, a very basic way is to count the number of times that skill is mentioned.
- This method also helps to identify if new skill has arised in a particular industry. As more and more candidate start mentioning it the parser will increment the count of that skill in our database. And a threshold will help to qualify the skill.
Machine Learning Based Approach
- On having enough data from above two approach, train model to classify the section
- Trail model to detect NER (location, dates,etc)

You will always feel your parser lack in perfection, so the correct approach would be to set the threshold around your parser and not getting overwhelmed by all the problems at the same time. and you will also experience the chicken-and-egg problem at start.

Paid tools

I will Keep updating more on this, Please let me know if you are looking for depth on any specific thing for resume parser.

DynamoDB Expressions

vivek atwal — Thu, 09 Sep 2021 19:28:00 +0000

As almost every developer has worked with Relational (Mysql,Postgres,etc) and No-SQL (MongoDB) databases, and over the time has developed the intuition for writing and execution of CRUD operations queries for these databases. DynamoDB breaks this intuition in many ways.

Lately, I have been working on a project which has DynamoDB as its Database. And like most of the developers, I searched for insert, update and delete queries and started writing the DynamoDB queries with the intuition of Mysql, Postgres, MongoDB.

I was able to write basic queries but slowly I started to realise that I am struggling to understand the execution of queries. And then I found the need to go back to Basics of query writing in DynamoDB and that was Expressions.

Before Moving to Expression Let understand the Jargons in Dynamodb

Item: Refers to row, record, Document in other databases.

Attribute: Refer to column in Mysql, Postgres and Field in MongoDB and Elasticsearch.

Expressions are an integral part of using DynamoDB

Expressions are rules for simple and complex CRUD operations.
You have to follow strict syntax to write expressions
This strict nature of expression enables DynamoDB for faster execution of queries even on millions of records/Items.
The size of the table doesn't matter latency remains constant i.e <10ms for small(1 GB) or big (100 GB) tables

Types Of Expression	Read	Insert	Update
Projection expressions	1
Key condition expressions	1
Condition expressions		1	1
Update expressions			1
Filter expressions

These Tables show the need of Expression for different Database operations.

Condition expressions

DynamoDB write operations are unconditional, Each operation overwrites an existing item with same primary key.

Many time need arises that you only want to insert new Item(row/documents) if it does not already exist in the table. Which means you need some condition to be satisfied, before write operation takes place. ConditionExpression enables you to write your such conditions.

Sequence of query Execution

DynamoDB retrieve the Item for given primary key. Atmost only 1 Item is retrieved as it is mandatory to specify the primary key.
Then verifies that the retrieved Item satisfies the User condition (Specified in ConditionExpression) or not.
If User condition is satisfied then write operation is executed or else the execution fails.

Condition expressions always operate on an individual item ~~not multiple items~~ when certain conditions are true.

On first run, this Item is inserted successfully. If you try inserting the same Item again, you'll get an error:

An error occurred (ConditionalCheckFailedException) when calling the PutItem operation: The conditional request failed

Alex has explained ConditionExpression in detail, For example you can also refer AWS Documentation, WorkingWithItems.ConditionalUpdate, ConditionExpresion PutItem and UpdateItem example

Update expression

Update expressions are used to update a particular attribute in an existing Item.
This is same as Update set column_name='value'in mysql.
DynamoDB Syntax

UpdateExpression: 'SET attributeToEdit = :newValue REMOVE attributeToDelete'

Projection expressions

Project expressions are used to specify a subset of attributes you want to receive when reading Items.
We used these in our GetItem calls.
GetItem calls are same as select queries in mysql.
We specify column name in select queries such as Select column1, column2, column3 from tablename in the same way we use ProjectionExpressions to specify attributes that we want to retireve in query.

Key condition expressions

Key condition expressions are used when querying a table with a composite primary key to limit the items selected.

Filter expressions

Filter expressions allow you to filter the results of queries and scans to allow for more efficient responses.

DynamoDB limitations

SQLite Installation

vivek atwal — Sat, 03 Jul 2021 10:01:03 +0000

I have been working on a project, and there was a need for serverless mini database. I decided to go with sqlite, it has its own advantages and limitations

Challenges

Many times you will run into error those are SQLite version/release specific, they could be

Features your are looking is available only in new release or specific release
Your application use old release.
You may want to try some deprecated feature for some experiment.

Example

I was struggling with upsert query

INSERT INTO upsert_table (
                             folder,
                             filename,
                             count
                         )
                         VALUES (
                             '2021-01',
                             'abc.json',
                             1
                         )
                         ON CONFLICT (
                             filename
                         )
                         DO UPDATE SET count = count + 1 WHERE filename = 'abc.json';

On local system sqlite version 3.28.0 worked fine and on server I ran into the error with sqlite version 3.22.0 Error: near "ON CONFLICT": syntax error

After some googling i found ON CONFLICT is supported on versions > 3.23.0

The Take away is you may come across many challenges that are release specific, and hence it becomes important to learn to download, compile and build binaries from sources.

Download

Once you have identified the need of specific release
you can go ahead and download from sqlite releases
scroll to your version and click on hash-id named as check-in
You will be re-directed to page that contains details of that specific release
Instead of downloading by clicking on it we will copy the link of the tar.gz and download using wget, and same command can be used in server as well.

  wget https://www.sqlite.org/src/tarball/884b4b7e/SQLite-884b4b7e.tar.gz

Installation

Use below commands to build the binary

cd                                  ;#  Takes you to Home directory
tar xzf SQLite-884b4b7e.tar.gz      ;#  Unpack the source tree into "sqlite"
mkdir bld                           ;#  Build will occur in a sibling directory
cd bld                              ;#  Change to the build directory
../SQLite-884b4b7e/configure        ;#  Run the configure script
make                                ;#  Run the makefile.
make sqlite3.c                      ;#  Build the "amalgamation" source file
make test                           ;#  Run some tests (requires Tcl)

You can also find this steps in README.md in downloaded folder SQLite-884b4b7e/

After Installation append binary path to Linux $PATH variable to access sqlite from anywhere in your system, If you don't run below command you will have to run sqlite from folder where its binary is stored.

export PATH=$HOME/bld:$PATH

Note: If you find below error during building binaries

exec:  tclsh:  not found

then install Tcl

sudo apt-get install --reinstall tcl

Upgrading python's SQLite

Now you have upgraded sqlite version on your OS. But you won't be able to access the new version from python, Because Python can't use the sqlite3 binary directly. It always uses a module which is linked against the sqlite3 shared library.

So when you print sqlite version from python , it will give you old version

import sqlite3
sqlite3.sqlite_version #sqlite_version - sqlite version
sqlite3.version # version - pysqlite version

To make python use new sqlite version, we have to update the .so file in linux.
Go to directory where you had build the sqlite from source, You will find libsqlite3.so.0.8.6 in lib folder.
Move libsqlite3.so.0.8.6 to /usr/lib/x86_64-linux-gnu/

cd $HOME/bld/.libs
sudo mv libsqlite3.so.0.8.6 /usr/lib/x86_64-linux-gnu/

Now you are ready to use new sqlite version from python.

Why should you consider using SQLite

SQLite is a lightweight, small and self-contained RDBMS in a C library. Popular databases like MySql, PostgreSQL, etc. works in the client-server model and they have a dedicated process running and controlling all the aspects of database operation.

But SQLite has no process running and has no client-server model. SQLite DB is simply an file with .sqlite3/.sqlite/.db extension. Every programming language has a library to support SQLite.

You can find SQLite being used in

Web browsers(Chrome, Safari, Firefox).
MP3 players, set-top boxes, and electronic gadgets.
Internet of Things (IoT).
Android, Mac, Windows, iOS, and iPhone devices.

There are lot more areas where SQLite is used. Every smartphone in the world has hundreds of SQLite database files and there are over one trillion databases in active use. That’s quite huge in numbers.

Undoing Changes in git

vivek atwal — Wed, 30 Jun 2021 18:46:26 +0000

Undoing a Commit

Similar to unstaging, we occasionally may want to undo an entire commit. To clarify, when we say "undo a commit", we mean remove that commit from our history, and revert to the point at which the files were staged (with the changes captured in the commit).
The command used to undo a commit is:

git reset --soft HEAD^

To break this down, HEAD refers to the current branch, and HEAD^ means one commit back, aka the "parent commit". The --soft here specifies that we should reset the branch (to point at that parent commit), but otherwise leave the files in the working directory and the index untouched.

This has the effect of undoing the commit, taking us back to just before we made it. Note that it leaves our changes staged in the index.

While useful, this is a complex command to remember, so once again we can create an alias, mapping this operation to the intuitive "uncommit" alias name.

git config --global alias.uncommit 'reset --soft HEAD^'

Unstaging Files

Occasionally we'll find that after making a round of changes, we realize that some of the changes are entirely unrelated to others.
Perhaps we've run git add ., but we then realize that we only want to commit two of the three files that are now staged.
Once again, we can use a simple Git command to undo this.

git reset filename

Checkout to Undo Changes

To remove a particular file changes from working directory

git checkout filename

To remove all the changes from working directory

git checkout .

NOTE This is one of the few dangerous operations in Git. If you run checkout without staging or committing your changes, Git will destroy your work and you will not be able to get it back. Be sure to use caution with git checkout .!