Mappings in Elasticsearch

Mapping is the process that defines how a document, which contains fields is stored and indexed in Elasticsearch. Basically, it defines the schema of the document and datatype for fields in the document.

Syntax to check index mappings: GET /index_name/_mappings

There are two types of mappings used in Elasticsearch

Explicit mapping

Dynamic mapping

Explicit mapping

This type of mapping allows us to choose how to define the mapping definition, such as provide an option to name our columns their data types, whether string…


“LIKE” vs “ILIKE” operator in PostgreSQL

PostgreSQL is a case-sensitive language in order to make it a case-insensitive language during pattern matching we make use of the ILIKE operator.

ILIKE operator works the same way as a LIKE operator but makes the language case-insensitive. Both the operators are used for pattern matching in PostgreSQL.

Pattern matching operators

1) LIKE (~~)

Syntax: WHERE value LIKE ‘xyz%’

2) NOT LIKE (!~~)

Syntax: WHERE value NOT LIKE ‘xyz%’

3) ILIKE (~~*)

Syntax: WHERE value ILIKE ‘xyz%’

4) NOT ILIKE (!~~*)

Syntax: WHERE value NOT…


“WHERE” vs “HAVING” clause in PostgreSQL

WHERE vs HAVING

“HAVING” clause: It is used to filter records from the groups based on the specified condition.

“WHERE” clause: It is used to filter records from the table based on the specified condition.

Syntax of “WHERE” and “HAVING” clause

“HAVING” Syntax:

SELECT column1, column2, aggregate_function(expr) FROM
table GROUP BY column1,column2 HAVING condition;

“WHERE” Syntax:(with GROUP BY)

SELECT column1, column2, aggregate_function(expr) FROM
table WHERE condition GROUP BY column1, column2;

“WHERE” Syntax:(without GROUP BY)

SELECT column1, column2, aggregate_function(expr) FROM…


In this article, we will see how to set up PostgreSQL version-13 on your windows machine and what are the new features added in this version.

How to install and setup PostgreSQL 13 on a Windows machine?


In this article, we will walk through how to perform web scraping using Python from scratch.


To understand the working of choose() function and case statements in SQL Server.

Let’s first understand the syntax (how we write choose() function and case statements in SQL Server) then we will check the performance between the two with bulk data.

Consider a table called Emp_Details having columns Id, Name, and DOB(date of birth).

CREATE TABLE Emp_Details

(

Id int,

Name varchar(20),

DOB date

);

Let’s add some data to Emp_Details

INSERT INTO Emp_Details VALUES

(1,’Mark’,’1980–01–11'),(2,’John’,’1981–12–12'),(3,’Amy’,’1979–11–21'),

(4,’Ben’,’1978–05–14'),(5,’Sara’,’1970–03–17'),(6,’David’,’1978–04–05');

Problem Statement:- Write an SQL query to fetch Name, DOB, and MonthName from DOB.

Solution:-

  1. By using CHOOSE() function

SELECT Name, DOB,

CHOOSE(DATEPART(MONTH,DOB),’Jan’,’Feb’,’March’,’April’,’May’,’April’


A CSV (Comma Separated Values) file uses commas to separate different values within the file.

The CSV file is a standard format when importing data to a table or exporting data to our system.

Data from CSV to Databases

In order to add bulk entries from CSV files to databases like Mysql, Postgresql, SQL Server, Oracle, etc… This involves a two-step process:

Step:1 Reading CSV file from the system.

Step:2 Loading CSV data to the database tables.

In this article, we will see bulk data insertion in Mysql, Postgresql, Cassandra, and SQL Server.

  1. SQL Server Database

Let’s consider a table called contacts and having following…


PDF and Word documents are binary files, which makes them much more complex than plaintext files. There are Python modules that make it easy for us to interact with PDFs and Word documents.

First Python module: PyPDF2

Second Python module: Python-Docx

To merge multiple pdf files into one pdf file python makes use of a module called PyPDF2 or Python-Docx.

In this article, we will use module PyPDF2

Step:-1 Create a folder named PyPDF, add a file app.py, open the terminal and execute the following command

# Install PyPDF2 module

pipenv install PyPDF2

Step:-2 Open app.py …


This article is a complete guide for new beginners to start with the PostgreSQL database.

Let’s start with a brief introduction about the database PostgreSQL is an open-source object-relational database management system. Object relation because it functions with the object as a relational component in the database. It uses SQL(Structured query language)for accessing data from the database.

Advantages and Disadvantages

Following are the advantages and disadvantages of PostgreSQL

Advantages:-

  1. Open-source and easy to use
  2. Supports ACID property
  3. Has user-defined data types
  4. Has extension for time-series database(TimescaleDB)
  5. Large support for JSON values
  6. Has extension for spatial database(PostGIS)
  7. Supports multi-version concurrency control(MVCC)model


Twilio

Twilio Messaging is an API to send and receive SMS, MMS, OTT messages globally. It uses intelligent sending features to ensure messages reliably reach end-users wherever they are.
Twilio has SMS-enabled phone numbers available in more than 180 countries.

To send text messages using python code

Step 1:- Create a folder named Pytext and add app.py file.

# install module Twilio
pipenv install Twilio

# import client from Twilio rest API
from Twilio.rest import Client

Step 2:- Create a trial account on Twilio, get Project Info(eg:-account_sid,auth_token), and generate a phone number to snd text msg from Twilio.

account_sid = “…”
auth_token…

Nidhi Gupta

Database Engineer having working experience in SQL and NO-SQL databases. 👨‍💻

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store