Forem: LehaUchicha

Fast apache kafka local deployment

LehaUchicha — Sun, 06 Feb 2022 10:49:05 +0000

Hey, dev.to!

If you are using apache kafka, you know that local deployment required a lot of things. You need to download zookeeper, apache kafka, edit configs and then run them. And, of cause, it takes time. The detailed description you can find in this article https://dev.to/lehauchicha/spring-boot-application-with-apache-kafka-299n. But is it possible to speed up the local deployment process of apache kafka ?

The answer is yes!
In this article we will deploy apache kafka locally using docker-compose. All you need is download and install docker from official site: https://www.docker.com/get-started

Lets create docker-compose.yml file:

version: '2'
services:
  zookeeper:
    image: confluentinc/cp-zookeeper:latest
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000
    ports:
      - 22181:2181

  kafka:
    image: confluentinc/cp-kafka:latest
    depends_on:
      - zookeeper
    ports:
      - 29092:29092
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092,PLAINTEXT_HOST://localhost:29092
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_INTER_BROKER_LISTENER_NAME: PLAINTEXT
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1

As you can see here we specify images for zookeeper and kafka with ports. That's it!

Lets run kafka, using docker-compose:

docker-compose up -d

you will see such result:

Now kafka ready for usage in your applications!

One more thing, for convenient configuring kafka topics, I would like to suggest UI Tool for Kafka - Offset Explorer, which you can find by link https://kafkatool.com/download.html.

For connection to deployed kafka you need install tool and add connection parameters here on properties tab:

and here on advanced tab:

That's it. Thank you for reading!

Spring Boot application with Apache Kafka

LehaUchicha — Mon, 15 Nov 2021 16:07:58 +0000

In this article we will:

Run Zookeeper and kafka locally
Write Spring boot application Kafka-Server. It's order service, which will produce and consume messages using orders topic. Also has entrypoint for order creation which will send message to the kafka topic

All examples available on github: https://github.com/LehaUchicha/Kafka-Example.git

Configure Zookeeper and kafka

Download Zookeeper and Kafka

Before writing our application we need to run zookeeper and kafka locally.
For that go to official site and download the latest version

Zookeeper

https://zookeeper.apache.org/releases.html

Kafka

https://kafka.apache.org/downloads

ps: On the moment of article writing the latest version was 3.0.0, but
this version not worked to me, so I download previous version - 2.8.1, which worked fine

Run Zookeeper

Go to the downloaded and extracted zookeeper folder. You will see such folder structure:

bin - folder, which contains utilities for zookeeper management
conf - folder, which contains configuration files for zookeeper. In this folder you can find zoo_sample.cfg - example of zookeeper configuration.
logs - folder, where zookeeper can write logs

First lets go to the conf folder, make a copy of zoo_sample.cfg and rename it to zoo.cfg.
In the zoo.cfg you need to override such value to dataDir=../logs or to any other directory which you like.
From the config file you can find othe important settings, like clientPort=2181, which said for us that zookeper by default run on port 2181.
You can override properties as you wish, but lets left them as is.

That's it! Now we need to run zookeeper server. For that go to the bin folder and find zkServer.cmd or
zkServer.sh. If you are the Windows user - use zkServer.cmd, otherwise zkServer.sh.

zkServer.sh start

zkServer.cmd

Result will be:

or logs without errors, like here in case if you use zkServer.sh start:

Great! Now we need to run kafka.

Run Kafka

Go to the downloaded and extracted apache kafka folder. You will see such folder structure:

bin - folder, which contains utilities for kafka management
config - folder, which contains configuration files for kafka. In this folder you can find server.properties which should be modified
logs - folder, where kafka can write logs

Go to the config folder and override log.dirs= property in the file server.properties to the any folder where you want to store logs.
I will use logs folder in the kafka root, so in my case it looks like this: log.dirs=../logs

Now time to run apache kafka. For that go to the bin folder.
For NOT Windows user you need to find kafka-server-start and execute script with the path to the server.properties file as an argument

./kafka-server-start.sh ../config/server.properties

For Windows users inside bin folder you can find windows folder which contains the same commands but for windows)
Go to the windows folder and run the same script. Don't forget to specify path to server.properties file as argument

kafka-server-start.bat ../../config/server.properties

If you don't see any exceptions in console, it means, that kafka is working!
Now befor start to writing Spring Boot application we need to create orders topic which our applications will use.
For that we need to execute command:

kafka-topics.bat --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic orders

kafka-topics.sh --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic orders

This command will create topic orders

Write Kafka Server Spring Boot application

You can download configured project from start.spring.io by link

or download ready project from github: https://github.com/LehaUchicha/Kafka-Example.git

Here in dependency you need to specify Spring for Apache Kafka for using kafka in our application.

pom.xml should contain:

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.kafka</groupId>
            <artifactId>spring-kafka</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
</dependencies>

go to the KafkaServerApplication.java file and add @EnableKafka annotation

@EnableKafka
@SpringBootApplication
public class KafkaServerApplication {

    public static void main(String[] args) {
        SpringApplication.run(KafkaServerApplication.class, args);
    }

}

go to the application.properties file and specify such properties

server.port=9955

kafka.server=localhost:9092
kafka.group.id=group1
kafka.producer.id=producer1

Now we need to create configuration for consumer and producer.

Consumer configuration

@Configuration
public class KafkaConsumerConfiguration {

      @Value("${kafka.server}")
      private String kafkaServer;

      @Value("${kafka.group.id}")
      private String kafkaGroupId;

      @Bean
      public KafkaListenerContainerFactory<?> batchFactory() {
            ConcurrentKafkaListenerContainerFactory<Long, OrderDto> factory =
                    new ConcurrentKafkaListenerContainerFactory<>();
            factory.setConsumerFactory(consumerFactory());
            factory.setBatchListener(true);
            factory.setMessageConverter(new BatchMessagingMessageConverter(converter()));
            return factory;
      }

      @Bean
      public KafkaListenerContainerFactory<?> singleFactory() {
            ConcurrentKafkaListenerContainerFactory<Long, OrderDto> factory =
                    new ConcurrentKafkaListenerContainerFactory<>();
            factory.setConsumerFactory(consumerFactory());
            factory.setBatchListener(false);
            factory.setMessageConverter(new StringJsonMessageConverter());
            return factory;
      }

      @Bean
      public ConsumerFactory<Long, OrderDto> consumerFactory() {
            return new DefaultKafkaConsumerFactory<>(consumerConfigs());
      }

      @Bean
      public KafkaListenerContainerFactory<?> kafkaListenerContainerFactory() {
            return new ConcurrentKafkaListenerContainerFactory<>();
      }

      @Bean
      public Map<String, Object> consumerConfigs() {
            Map<String, Object> props = new HashMap<>();
            props.put(ConsumerConfig.BOOTSTRAP_SERVERS_CONFIG, kafkaServer);
            props.put(ConsumerConfig.KEY_DESERIALIZER_CLASS_CONFIG, LongDeserializer.class);
            props.put(ConsumerConfig.VALUE_DESERIALIZER_CLASS_CONFIG, StringDeserializer.class);
            props.put(ConsumerConfig.GROUP_ID_CONFIG, kafkaGroupId);
            props.put(ConsumerConfig.ENABLE_AUTO_COMMIT_CONFIG, true);
            return props;
      }

      @Bean
      public StringJsonMessageConverter converter() {
            return new StringJsonMessageConverter();
      }
}

Producer configuration:

@Configuration
public class KafkaProducerConfiguration {

      @Value("${kafka.server}")
      private String kafkaServer;

      @Value("${kafka.producer.id}")
      private String kafkaProducerId;

      @Bean
      public Map<String, Object> producerConfigs() {
            Map<String, Object> props = new HashMap<>();
            props.put(ProducerConfig.BOOTSTRAP_SERVERS_CONFIG, kafkaServer);
            props.put(ProducerConfig.KEY_SERIALIZER_CLASS_CONFIG, LongSerializer.class);
            props.put(ProducerConfig.VALUE_SERIALIZER_CLASS_CONFIG, JsonSerializer.class);
            props.put(ProducerConfig.CLIENT_ID_CONFIG, kafkaProducerId);
            return props;
      }

      @Bean
      public ProducerFactory<Long, OrderDto> producerOrderFactory() {
            return new DefaultKafkaProducerFactory<>(producerConfigs());
      }

      @Bean
      public KafkaTemplate<Long, OrderDto> kafkaTemplate() {
            KafkaTemplate<Long, OrderDto> template = new KafkaTemplate<>(producerOrderFactory());
            template.setMessageConverter(new StringJsonMessageConverter());
            return template;
      }
}

Note: needs to use org.springframework.kafka.support.serializer.JsonSerializer;, not from Jackson library

OrderService:

@Slf4j
@Service
@AllArgsConstructor
public class OrderServiceImpl implements OrderService {

    private final KafkaTemplate<Long, OrderDto> kafkaOrderTemplate;

    private final ObjectMapper objectMapper;

    @Override
    public void send(OrderDto dto) {
        kafkaOrderTemplate.send("orders", dto);
    }

    @Override
    @KafkaListener(id = "OrderId", topics = {"orders"}, containerFactory = "singleFactory")
    public void consume(OrderDto dto) {
        log.info("-> consumed {}", writeValueAsString(dto));
    }

    private String writeValueAsString(OrderDto dto) {
        try {
            return objectMapper.writeValueAsString(dto);
        } catch (JsonProcessingException e) {
            log.error("Error happens during json processing", e);
            throw new RuntimeException("Error happens during json processing: " + dto.toString());
        }
    }
}

and OrderController:

@RestController
@AllArgsConstructor
public class OrderController {

    private final OrderServiceImpl orderService;

    @PostMapping("/order")
    public void create() {
        OrderDto order = OrderDto.builder()
                .name("New Order #: " + System.nanoTime())
                .description("Standard description")
                .build();
        orderService.send(order);
    }
}

Now when you will run application - entrypoint will be available on the port 9955.
Make POST request using endpoint

localhost:9955/order

Message will be created and sended to the topic orders. After that our application will immediately read this
message through the method with annotation @KafkaListener:

    @KafkaListener(id = "OrderId", topics = {"orders"}, containerFactory = "singleFactory")
    public void consume(OrderDto dto) {
        log.info("-> consumed {}", writeValueAsString(dto));
    }

and in console you will see such result:
console output

That's it! The code available on github: https://github.com/LehaUchicha/Kafka-Example.git

As a bonus, which not explained in the article - you can find the second Spring boot application Kafka-Producer, which will push a message every 5 seconds after running to the orders topic.
And Kafka-Server application will read this message, which you can find in console. you need just run application.

Conclusion

In this article we successfully configured apache kafka and understand how to work with kafka wia Spring Boot.
Also figure out how services can communicate with each other wia apache kafka.

Thank you for reading. If you like an article, you can support me

by buying me a coffee

Have a good day!

Google Cloud Platform: Deploy simple Java Spring boot application

LehaUchicha — Wed, 08 Sep 2021 16:24:27 +0000

Hey, dev.to!

In this article I'll show you an example how to deploy simple java application in Google Cloud Platform (GCP).

What exactly we will do:

Create a simple java application with Spring Boot
Create a docker image for application
Deploy the application in Google Cloud Platform
Create pipeline using Google Cloud Build for building CI/CD
Connect our application to the database using Google Cloud Sql + Google Cloud SQL Auth Proxy and add liquibase for managing database changes.
Use k8s secrets and Google Cloud Secret Manager

Overview

The goal of article is show how to work with GCP, that is why we will create a simple java application with single entity Post and a couple of entrypoints, which will allow CRUD operations only.

We will develop CI/CD flow as on diagram below:

Prerequisites

We need to create a google cloud account.

For that:

Create temporary Gmail account
Navigate to the https://cloud.google.com/ and press Get started for free
Fill up your credit card and personal details
Start my free trial

Application

Clone application from github. Project already contains Dockerfile, cloudbuild.yaml and deployment.yaml file. Needs only update gcp project number [your_project_id].

git clone https://github.com/LehaUchicha/gcp-project.git

Manual Build Docker Image

Create a Cloud Source Repository with our gcp-project.
Add a Dockerfile file into your repository to build your app.
Test the created Docker image.
Push the Docker image into the Artifact Repository.

Go to the GCP and open GCP console:

Create the repository

  gcloud source repos create gcp-project

Clone the repos

  gcloud source repos clone gcp-project

Create an image for project
```
  docker build -t gcp-project:0.0.1 .
```

Run docker iamge

  docker run -p 8080:8080 gcp-project:0.0.1 &

Test docker container
```
  curl localhost:8080/posts
```

Create Docker repository

gcloud artifacts repositories create gcp-project-docker-repo --repository-format=docker \
--location=us-central1 --description="Docker repository for gcp project"

Verify that repository was created

gcloud artifacts repositories list

Configure authentication to docker

  gcloud auth configure-docker us-central1-docker.pkg.dev

Tag the image

Get the project name

  gcloud config get-value project

Then execute command below:

  docker tag gcp-project:0.0.1 us-central1-docker.pkg.dev/gcp-project-[your_project_id]/gcp-project-docker-repo/gcp-project:0.0.1

Where

PROJECT is your Google Cloud project ID. If your project ID contains a colon (:), see Domain-scoped projects.
us-central1 is the repository location.
docker.pkg.dev is the hostname for Docker repositories.
quickstart-image is the image name you want to use in the repository. The image name can be different than the local image name.
tag1 is a tag you're adding to the Docker image. If you didn't specify a tag, Docker will apply the default tag latest.

Push the docker image to repo

docker push us-central1-docker.pkg.dev/gcp-project-[your_project_id]/gcp-project-docker-repo/gcp-project:0.0.1

  ```
  gcloud builds submit --tag us-central1-docker.pkg.dev/PROJECT/gcp-project-docker-repo/gcp-project:0.0.1
  ```

Automate Build Docker Image:

Create a pipeline in Cloud Build to build a Docker image when the source code changes.

create a build pipeline cloudbuild.yaml

steps:
 - name: 'gcr.io/cloud-builders/docker'
   args: [ 'build', '-t', 'us-central1-docker.pkg.dev/gcp-project-[your_project_id]/gcp-project-docker-repo/gcp-project:0.0.1', '.' ]
 - name: 'gcr.io/cloud-builders/docker'
   args: [ 'push', 'us-central1-docker.pkg.dev/gcp-project-[your_project_id]/gcp-project-docker-repo/gcp-project:0.0.1' ]
images: ['us-central1-docker.pkg.dev/gcp-project-[your_project_id]/gcp-project-docker-repo/gcp-project:0.0.1']

Run the pipeline using command

  gcloud builds submit --config cloudbuild.yaml

Create the trigger

gcloud beta builds triggers create cloud-source-repositories --repo=gcp-project --branch-pattern=master --build-config=cloudbuild.yaml

Where:

REPO_NAME is the name of your repository.
BRANCH_PATTERN is the branch name in your repository to invoke the build on.
TAG_PATTERN is the tag name in your repository to invoke the build on.
BUILD_CONFIG_FILE is the path to your build configuration file.
SERVICE_ACCOUNT is the email associated with your service account. If you don't include this flag, the default Cloud Build service account is used.

Manual Docker Image Deployment:

Use the image to create and expose a deployment in k8s.
Update the image and push a change to the deployment.

Work with cluster:

Create the cluster

gcloud container clusters create nginx-1-cluster --zone=us-central1-a

Authenticate to the cluster

gcloud container clusters get-credentials nginx-1-cluster --zone=us-central1-a

Create deployment.yaml

apiVersion: apps/v1
kind: Deployment
metadata:
annotations:
deployment.kubernetes.io/revision: "1"
generation: 1
labels:
app: gcp-project
name: nginx-1
namespace: default
spec:
progressDeadlineSeconds: 600
replicas: 2
revisionHistoryLimit: 10
selector:
matchLabels:
  app: gcp-project
strategy:
rollingUpdate:
  maxSurge: 25%
  maxUnavailable: 25%
type: RollingUpdate
template:
metadata:
  creationTimestamp: null
  labels:
    app: gcp-project
spec:
  serviceAccountName: sql-service-account
  containers:
  - name: gcp-project-sha256-1
    image: INIT_IMAGE_NAME
    imagePullPolicy: Always
    env:
    - name: DB_USER
      valueFrom:
        secretKeyRef:
          name: postgres-db-secret
          key: database
    - name: DB_PASS
      valueFrom:
        secretKeyRef:
          name: postgres-db-secret
          key: password
    - name: DB_NAME
      valueFrom:
        secretKeyRef:
          name: postgres-db-secret
          key: database
    resources: {}
    terminationMessagePath: /dev/termination-log
    terminationMessagePolicy: File

Apply deployment using command
```
kubectl apply -f deployment.yaml
```

Automate Docker Image Deployment:

Create a pipeline in Cloud Build to deploy a new version of your image when the source code changes.

Modify cloudbuild.yaml file. Needs to add steps, where new image version should be set in the deployment.yaml

- id: Update Image Tag
name: ubuntu
args: [ 'bash','-c','sed -i "s|INIT_IMAGE_NAME|us-central1-docker.pkg.dev/gcp-project-322518/gcp-project-docker-repo/gcp-project:$SHORT_SHA|" k8s/deployment.yaml' ]

Modify cloudbuild.yaml file. Needs to apply updated deployment.yaml

- id: Updating Deployment
name: gcr.io/cloud-builders/kubectl
args: ['apply','-f','k8s/deployment.yaml']
env:
- CLOUDSDK_COMPUTE_ZONE=us-central1-a
- CLOUDSDK_CONTAINER_CLUSTER=nginx-1-cluster

Connect to Database using Cloud SQL Auth Proxy.

Create sql instance with private IP. (or both: private and public)
For getting access for application to Cloud Sql, needs to configure SQL Auth Proxy.
You can find detailed instruction here: https://cloud.google.com/sql/docs/mysql/connect-kubernetes-engine.

Create secrets

kubectl create secret generic postgres-db-secret> \
--from-literal=username=db_username \
--from-literal=password=db_pass \
--from-literal=database=db_database

Enable Workload Identity on your cluster

gcloud container clusters update nginx-1-cluster \
--workload-pool=gcp-project-322518.svc.id.goog

Create service-account.yaml

apiVersion: v1
kind: ServiceAccount
metadata:
  name: <YOUR-KSA-NAME> # TODO(developer): replace these values

Apply file

kubectl apply -f service-account.yaml

Enable the IAM binding between your YOUR-GSA-NAME and YOUR-KSA-NAME

gcloud iam service-accounts add-iam-policy-binding \
--role="roles/iam.workloadIdentityUser" \
--member="serviceAccount:gcp-project-[your_project_id].svc.id.goog[default/sql-service-account]" \
postgres-service-account@gcp-project-[your_project_id].iam.gserviceaccount.com

Add an annotation to YOUR-KSA-NAME to complete the binding

kubectl annotate serviceaccount \
sql-service-account \
iam.gke.io/gcp-service-account=postgres-service-account@gcp-project-[your_project_id].iam.gserviceaccount.com

Manual SQL migration scripts

Add liquibase library to pom.xml

<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
</dependency>

Create migration scripts. File db.changelog-0.0.0.yaml

databaseChangeLog:
- changeSet:
  id: create-table-posts
  author: aleksei
  preConditions:
    - onFail: MARK_RAN
      not:
        tableExists:
          tableName: posts
  changes:
    - createTable:
        columns:
          - column:
              autoIncrement: true
              constraints:
                nullable: false
                primaryKey: true
                primaryKeyName: posts_pkey
              name: id
              type: BIGINT
          - column:
              constraints:
                nullable: false
              name: title
              type: VARCHAR(250)
          - column:
              name: description
              type: VARCHAR(250)
          - column:
              constraints:
                nullable: false
              name: full_text
              type: VARCHAR(250)
          - column:
              constraints:
                nullable: false
              name: author
              type: VARCHAR(250)
        tableName: posts

and file db.changelog-master.yaml

  ```yaml

databaseChangeLog:

include: file: db/changelog/db.changelog-0.0.0.yaml ```

Run script using command:
```
  mvn liquibase:update
```

Automate SQL migration scripts

Modify cloudbuild.yaml file. add such fragments:

#Step 3. Install Cloud SQL proxy
- id: Install Cloud Sql Auth Proxy
  name: maven:3-jdk-11
  entrypoint: sh
  args:
    - "-c"
    - "wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy"

#Step 4. Run migration scripts using liquibase
- id: Run migration scripts
  name: maven:3-jdk-11
  entrypoint: sh
  args:
    - "-c"
    - "(./cloud_sql_proxy -instances=gcp-project-[your_project_id]:us-central1:postgre-instance=tcp:localhost:5432 & sleep 2) && mvn liquibase:update -Dliquibase.url=jdbc:postgresql://$$DB_HOST/$$DB_NAME -Dliquibase.username=$$DB_USER -Dliquibase.password=$$DB_PASS"
  timeout: "150s"
  secretEnv: ['DB_PASS', 'DB_USER', 'DB_HOST', 'DB_NAME']
  waitFor: ["Install Cloud Sql Auth Proxy"]

Step 3 required for installing Cloud SQL Auth proxy, otherwise, it will be impossible to connect to Postgres sql.

Step 4. will run Cloud SQL Auth proxy then will run liquibase command for migration.

Here such secrets as DB_PASS', 'DB_USER', 'DB_HOST', 'DB_NAME' comes from Secret management.
Fod fetch secrets from secrets manager needs to modify cloudbuild.yaml

availableSecrets:
  secretManager:
    - versionName: projects/[project_id]/secrets/POSTGRES_PASSWORD/versions/2
      env: 'DB_PASS'
    - versionName: projects/[project_id]/secrets/POSTGRES_USERNAME/versions/1
      env: 'DB_USER'
    - versionName: projects/[project_id]/secrets/POSTGRES_HOST/versions/2
      env: 'DB_HOST'
    - versionName: projects/[project_id]/secrets/POSTGRES_DATABASE/versions/1
      env: 'DB_NAME'

Expose your service over Cloud Load Balancer with external static IP address

Create service.yaml

apiVersion: v1
kind: Service
metadata:
annotations:
cloud.google.com/neg: '{"ingress":true}'
finalizers:
- service.kubernetes.io/load-balancer-cleanup
labels:
app: gcp-project
spec:
clusterIP: 10.64.3.149
clusterIPs:
- 10.64.3.149
externalTrafficPolicy: Cluster
ports:
- nodePort: 32412
port: 80
protocol: TCP
targetPort: 8080
selector:
app: gcp-project
sessionAffinity: None
type: LoadBalancer
status:
loadBalancer:
ingress:
- ip: 34.123.209.167

Execute command:
```
  kubectl apply -f service.yaml
```

You are still here ? Wow! Thank you for reading!
If it will be usefull to make this article more detailed, please, let me know in the comments.
If you like an article, you can support me by buying me a coffee

RDBMS: Basics

LehaUchicha — Tue, 24 Aug 2021 07:09:07 +0000

Table of Content:

OLTP && OLAP
CAP theorem
ACID
Normalization
Indexes
Triggers
Stored procedures
Views
Scaling
- Vertical
- Horizontal
  - Partitioning
  - Replication
  - Sharding
Joins
Optimistic/Pessimistic locks
Nosql Types
New Sql

OLTP and OLAP

Basically all the databases divided into OLTP and OLAP types.
OLTP - Online transaction processing.
OLAP - Online analytical processing

RDMBS supported both types of processing.
For example, when you try to INSERT, UPDATE, DELETE it's OLTP.
But when You try to select with aggregation operation, like GROUP BY, COUNT, SUM. This is
OLAP.
If application works perfect with a thousand of OLTP queries, it can be impossible to work with
a thousand of OLAP operations. For example: Your application can have a feature, which allows finding goods which
users with similar preferences with you are buying.

For solving such problems NoSQL was invented.

Cap theorem

In normal operations, your data store provides all three functions.
But the CAP theorem maintains that when a distributed database experiences a network failure,
you can provide either consistency or availability.

ACID

ACID is a set of guiding principles that ensure database transactions are processed reliably.

A - Atomicity. Commits finish an entire operation successfully or roll back to it's prior state
C - Consistency. Any change maintains data integrity or is cancelled completely
I - Isolation. Any read or write will not be impacted by other reads or writes of separate transaction
D - Durability. Successful commits will survive permanently. ***

Normalization

Normalization is the process of organizing the data in the database.
Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies.
Normalization divides the larger table into the smaller table and links them using relationship.
The normal form is used to reduce redundancy from the database table.

Possible normal forms:

Initial data
1NF
2NF
3NF
EKNF
4NF
ETNF
5NF
DKNF
6NF

Indexes

Indexing is an object, which is used to optimize the performance of a search query.

Main types of indexes are:

Clustered	Non-clustered
* Physically change structure of data storing to B-tree	* Don't change structure of data physically
* Can be created the single per tab;e	* May be a lot per table
* Performance is higher the non-clustered index	Performance is less then clustered index

Other index types:

Bitmap index
Dense index
Sparse index
Reverse index
Primary index
Secondary index ***

Triggers

Trigger is a special type of stored procedure, which user don't execute directly, but which executed by INSERT,
UPDATE or DELETE operations. Allow achieving data consistency and implements complex business logic.

Use case: You have 2 data storages, one of them is hot storage which use actual data and another storage is
cold storage, which store archived data. When user try to delete data from hot storage, in trigger you can implement
moving data to cold storage and after that delete from hot storage.

Stored procedures

Stored procedures can contain some part of business logic.
Pros:
* Can have high performance
* Can be secured
Cons:
* Maintainability and Supportability are decreasing, due to business logic present as in application
as in database. So debug and fixing can take much more time

Views

Views - is a logical structure of database, which allow executing query like to a simple table,
but can aggregate data from different tables.

View types:

Views - logical structure. CAn be pretty slow, due to doesn't store data and executing after each user's query. Data always actual.
Materialized views - the result is stored on disk. It takes some time for building materialized view, but performance is high. Data can be not actual, due to you need to rebuild view after adding, but it is expensive operation.

The ways of updating materialized views:

Manual
By schedule
In case if one of the base tables has been updated.

Scaling

Vertical

Vertical scaling allow increasing computing capabilities by upgrading current hardware.
In most cases add second machine is cheaper than upgrade the exists one with equal characteristics.

Horizontal

Partitioning

A partition is a division of a logical database or its constituent elements into distinct independent
parts. Database partitioning is normally done for manageability, performance or availability reasons,
or for load balancing. It is popular in distributed database management systems, where each partition
may be spread over multiple nodes, with users at the node performing local transactions on the
partition. This increases performance for sites that have regular transactions involving certain
views of data, whilst maintaining availability and security.

Example: On info sites it makes sense to partition records by publish date, due to recent news
more interesting for users, then old ones.

Partitioning criteria:

Range partitioning
List partitioning
Composite partitioning
Round-robin partitioning
Hash partitioning

Replication

The central database is called the publication database because it provides the data for users at
other sites. The data in the publication database is copied (replicated) to subscription databases at
other locations. All users whether connected to the publisher server (the Publisher) or to a server
at one of the remote sites (a Subscriber) see the same data and work on the same records.
A Subscriber can be a Subscriber such as a file server or a disconnected Subscriber such as a laptop.

Basically, Publisher instance used for write operations and Subscriber instances for reading. So, in standard,
configuration there is one publisher and a couple of subscribers.
When you have a heavy update operation and publisher server work with this operation, your application
works without problems and any slow downs, because you have subscriber instances which allow read data
for users.

Sharding

is a horizontal partition of data in a database or search engine. Each shard is held on a separate
database server instance, to spread load. Some data within a database remains present in all shards, but some appears only in a single shard.
Each shard (or server) acts as the single source for this subset of data.

Example: in social network USer ID can be the good candidate for sharding key, due to all the data regarding
user will be stored on the same server

Joins

Optimistic/Pessimistic locks

We use locks to protect data integrity and atomicity in concurrent applications where a record
could get read/write requests.

Optimistic Locking is when you check if the record was updated by someone else before you commit the transaction.
Pessimistic locking is when you take an exclusive lock so that no one else can start modifying the record.

Nosql Types

Here are the four main types of NoSQL databases:

Document databases. In example: MongoDB
Key-value stores. In example: Redis
Column-oriented databases. In example: ClickHouse, MariaDB, Apache Hbase
Graph databases. In example: Neo4j

New Sql

NewSQL is a new approach to relational databases that wants to combine transactional
ACID (atomicity, consistency, isolation, durability) guarantees of good ol’ RDBMSs and
the horizontal scalability of NoSQL. It sounds like a perfect solution, the best of both worlds.
Examples: VoltDB, MemSQL, Tarantool, Cosmos DB, Cloud Spanner.

If you like an article, you can support me

by buying me a coffee

MongoDB: Basics

LehaUchicha — Sat, 24 Jul 2021 23:34:54 +0000

In this article you can find some useful commands for MQL (Mongo Query Language)*

Content:

Importing, Exporting
Database and collection operations
Creating and manipulating document
Advanced CRUD operation
- Comparison operators
- Logic operators
- Expressive query operators
- Array operators and projection
- Query Arrays and sub documents
Aggregation operations
- Cursor methods
- Aggregation framework
Indexing

Import/export operations

For Bson import/export use:

mongorestore
mongodump

Example:

mongorestore --uri "mongodb+srv://<your username>:<your password>@<your cluster>.mongodb.net/sample_supplies"  --drop dump

mongodump --uri "mongodb+srv://<your username>:<your password>@<your cluster>.mongodb.net/sample_supplies"`

For Json import/export use:

mongoimport
mongoexport

Example:

mongoimport --uri="mongodb+srv://<your username>:<your password>@<your cluster>.mongodb.net/sample_supplies" --drop sales.json

mongoexport --uri="mongodb+srv://<your username>:<your password>@<your cluster>.mongodb.net/sample_supplies" --collection=sales --out=sales.json

Database and collection operations

Show the list of databases

show dbs

Switch to use specified database

use my_database

Show the list of collections

show collections

Create a new collection with name 'users'

db.createCollection("users")

Drop 'users' collection

db.users.drop()

Creating and manipulating document

You can try examples on collection 'users' with such structure:

{
    "name": "Alex",
    "age": 10,
    "posts": [{
        "name": "Post1"
    }, {
        "name": "Post2"
    }]
}

Find all documents with field "name" equal to "Alex"

db.users.find({"name": "Alex"})

Get a random document from the collection

db.users.findOne()

Insert document into users collection

db.users.insert({
      "_id" : ObjectId("56d61033a378eccde8a8354f"),  
      "name" : "Peter",  
      "birth_date" : "Feb 20 1995"  
  })

update document.

db.users.updateOne({<query to locate>]}, {<update>})

For the first user which has a name "Alex" will set age = 20

db.users.updateOne({"name": "Alex"}, {"age": 20})

Upsert - hybrid operation of update and insert

db.users.updateOne({<query to locate>]}, {<update>}, {"upsert": true})

For user with name "Alex" will update age, but if such user doesn't exists, will insert new document with name = Alex and age = 20

db.users.updateOne({"name": "Alex"}, {"age": 20}, {"upsert": true})

Update all documents in the 'users' collection where the 'name' field is equal to "Alex" by adding 1 to the current value of the "age" field.

db.users.updateMany({ "name": "Alex" }, { "$inc": { "age": 1 } })

Update one document in the 'users' collection where the "name" is Alex and the "age" field is 20 , by adding a document element to the "posts" array.

db.users.updateOne({ "name": "Alex", "age": 20 },
                    { "$push": { "posts": { "name": "Post3"}
                                }
                     })

Delete all the documents that have "age" field equal to 10

db.users.deleteMany({ "age": 20 })

Delete one document that has "age" field equal to 20.

db.users.deleteOne({ "age": 20 })

Advanced CRUD operators

For executing queries you can use such collection structure

[
    { 
      "name": 'Alex',
      "age": 20,
      "posts": [ { "name": 'Post1' }, { "name": 'Post2' }, { "name": 'Post3' } ] },
    { 
      "name": 'Peter',
      "age": 30,
      "posts": [ { "name": 'Post1' }, { "name": 'Post2' }, { "name": 'Post3' } ] },
    { 
      "name": 'Andrew',
      "age": 18,
      "posts": [ { "name": 'Post1' }, { "name": 'Post2' }, { "name": 'Post3' } ] }
]

Comparison operators

Here the table of comparison operators:

Operator	Description
$eq	Matches if value is equal to the given value
$ne	Matches if value is not equal to the given value
$lt	Matches if values are less than the given value.
$gte	Matches if values are greater or equal to the given value.
$lte	Matches if values are less or equal to the given value.
$in	Matches any of the values in an array.

Find all documents where the "age" less than or equal to 20:

db.users.find({ "age": { "$lte" : 20 }}).pretty()

will return:

{ "_id": ObjectId("611a79b8f30c577098be1de6"),
  "name": 'Andrew',
  "age": 18,
  "posts": [ { "name": 'Post1' }, { "name": 'Post2' }, { "name": 'Post3' } ] }
{ "_id": ObjectId("611a7051f30c577098be1de1"),
  "name": 'Alex',
  "age": 20,
  "posts": [ { "name": 'Post1' }, { "name": 'Post2' }, { "name": 'Post3' } ] }

Find all documents where the "age" less than or equal to 20 but "name" not equal to 'Alex':

db.users.find({ "age": { "$lte" : 20 },
                "name": { "$ne": "Alex" } }).pretty()

will return the single document:

{ "_id": ObjectId("611a79b8f30c577098be1de6"),
  "name": 'Andrew',
  "age": 18,
  "posts": [ { "name": 'Post1' }, { "name": 'Post2' }, { "name": 'Post3' } ] }

Logic operators

Operator	Description
$and	Joins query clauses with a logical AND returns all documents that match the conditions of both clauses.
$not	Inverts the effect of a query expression and returns documents that do not match the query expression.
$nor	Joins query clauses with a logical NOR returns all documents that fail to match both clauses.
$or	Joins query clauses with a logical OR returns all documents that match the conditions of either clause.

Example:

db.users.find({ "$and": [ { "$or" :[ { "age": 20 },
                                    { "name": "Peter" }
                                  ] },
                          { "$or" :[ { "age": 30 },
                                     { "name": "Andrew" } ] }
                         ]}).pretty()

will return

{ "_id": ObjectId("611a79acf30c577098be1de4"),
  "name": 'Peter',
  "age": 30,
  "posts": [ { "name": 'Post1' }, { "name": 'Post2' }, { "name": 'Post3' } ] }

Expressive query operators

For examples use such collection structure:

{ _id: ObjectId("611a79acf30c577098be1de4"),
  name: 'Peter',
  age: 30,
  posts: [ { name: 'Post1' }, { name: 'Post2' } ],
  postsCount: 5,
  videoCount: 5 }
{ _id: ObjectId("611a79b8f30c577098be1de6"),
  name: 'Andrew',
  age: 18,
  posts: [ { name: 'Post1' }, { name: 'Post3' } ],
  postsCount: 6,
  videoCount: 3 }
{ _id: ObjectId("611a7051f30c577098be1de1"),
  name: 'Alex',
  age: 20,
  posts: [ { name: 'Post2' }, { name: 'Post3' } ],
  postsCount: 5,
  videoCount: 7 }

$expr - allow the use of aggregation expressions within the query language

Find all documents where the count of videous and possts are equal:

db.users.find({ "$expr": { "$eq": [ "$postsCount", "$videoCount"] }
              }).count()

will return

Array operators and projection

Projection allows displaying not the whole document, but specified fields only.

Find all documents with exactly 2 posts which include all the posts names listed in the query array, and display their name:

db.users.find({ "posts":
        { "$size": 2, "$all": [ {"name": "Post1"}, {"name":"Post2"} ] } },
                            {"name": 1}).pretty()

will return result

{ _id: ObjectId("611a79acf30c577098be1de4"), name: 'Peter' }

Well, that is good, but what if we want to get document without "_id", only user name ?
We can modify projection like this:

db.test.find({ "posts":
        { "$size": 2, "$all": [ {"name": "Post1"}, {"name":"Post2"} ] } },
                            {"name": 1, "_id": 0}).pretty()

and result will be:

{ name: 'Peter' }

Query Arrays and sub documents

For examples use such collection structure

[
    { "_id": ObjectId("611a79acf30c577098be1de4"),
      "name": 'Peter',
      "age": 30,
      "posts": [ { "name": 'Post1' }, { "name": 'Post2' } ] },
    { "_id": ObjectId("611a79b8f30c577098be1de6"),
      "name": 'Andrew',
      "age": 18,
      posts: [ { "name": 'Post1' }, { "name": 'Post3' } ] },
    { "_id": ObjectId("611a7051f30c577098be1de1"),
      "name": 'Alex',
      "age": 20,
      "posts": [ { "name": 'Post2' }, { "name": 'Post3' } ] }
]

Find all documents, which have in an array "posts" document with "name" equal to "Post1"

db.users.find({ "posts.name": "Post1" },
                  { "name": 1 }).pretty()

will return

{ "_id": ObjectId("611a79acf30c577098be1de4"), "name": 'Peter' }
{ "_id": ObjectId("611a79b8f30c577098be1de6"), "name": 'Andrew' }

The same query as previous but with $elemMatch operator

db.users.find({ "posts":
                      { "$elemMatch": { "name": "Post1" } } },
                  { "name": 1 }).pretty()

will return the same result

{ "_id": ObjectId("611a79acf30c577098be1de4"), "name": 'Peter' }
{ "_id": ObjectId("611a79b8f30c577098be1de6"), "name": 'Andrew' }

also, you can use count()

db.users.find({ "posts.name": "Post1" },
                  { "name": 1 }).count()

db.users.find({ "posts":
                      { "$elemMatch": { "name": "Post1" } } },
                  { "name": 1 }).count()

Result:

Aggregation

Cursor methods

Get the number of documents where name equal to "Alex"

db.users.find({ "name": "Alex" }).count()

The same as db.users.find(), but with pretty formatting

db.users.find({"name": "Alex"}).pretty()

Allow sorting documents by the specified field. "name": -1 means descending. Yoy also can specify 1 - ascending.

db.users.find().sort({ "name": -1 })

limit() Command allow you get specified amount of documents instead all of them

db.users.find().limit(1)

Aggregation framework

Aggregation framework is a simplest form, another way then MQL to query data in mongo DB.
For examples use such collection structure.

{ _id: ObjectId("611a79acf30c577098be1de4"),
  name: 'Peter',
  age: 30,
  posts: [ { name: 'Post1' }, { name: 'Post2' } ],
  postsCount: 5,
  videoCount: 5 }
{ _id: ObjectId("611a79b8f30c577098be1de6"),
  name: 'Andrew',
  age: 18,
  posts: [ { name: 'Post1' }, { name: 'Post3' } ],
  postsCount: 6,
  videoCount: 3 }
{ _id: ObjectId("611a7051f30c577098be1de1"),
  name: 'Alex',
  age: 20,
  posts: [ { name: 'Post2' }, { name: 'Post3' } ],
  postsCount: 5,
  videoCount: 7 }

Find all documents that have Wifi as one of the amenities. Only include price and address in the resulting cursor.
MQL example:

db.users.find({ "name": "Alex" },
                    { "name": 1, "age": 1, "postsCount": 1,"_id": 0 }).pretty()

Aggregation framework example:

db.users.aggregate([
                          { "$match": { "name": "Alex" } },
                          { "$project": { "name": 1,
                                          "age": 1,
                                          "postsCount": 1,
                                          "_id": 0 }}]).pretty()

both return the same result:

{ name: 'Alex', age: 20, postsCount: 5 }

Indexes

Allows making queries more efficient. It's better to create indexes for those fields, which often using in the queries, for example:

db.users.find().sort({ "name": -1 })

db.users.find({"name": "Alex"})

Here field "name" is a good candidate for indexing.

Index types:

Single field index

db.users.createIndex({"name": -1})

Compound index - index for multiple fields

db.users.createIndex({"name": 1}, "last_name": 1)

Index types table

type	description
Single index	contains only one field
Compound index	contains multiple fields
Multikey indexes	indexes against array fields
Text index	MongoDB provides text indexes to support text search queries on string content
Wildcard index	Since MongoDB supports dynamic schemas, applications can query against fields whose names cannot be known in advance or are arbitrary.
2dsphere index	supports queries that calculate geometries on an earth-like sphere

If you like an article, you can support me

by buying me a coffee