PG_SIZE_PRETTY

Giriş

Tablonun büyüklüğünü okunaklı halde gösterir

Örnek

Şöyle yaparız

SELECT pg_size_pretty(pg_table_size('table1')) table_size;

 table_size 
------------
 7112 kB
(1 row)

pgoutput Debezium Plugin

Giriş

Açıklaması şöyle

The pgoutput Debezium plugin is a Kafka Connect connector that can be used to capture changes from a PostgreSQL database and stream them to Kafka. The plugin uses the pgoutput replication stream to capture changes, which is a high-performance way to capture changes from PostgreSQL.

Varsayılan çıktı avro formatında

Örnek

Şöyle yaparız

curl -H 'Content-Type: application/json' debezium:8083/connectors --data '
{
  "name": "shipments-connector",  
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector", 
    "plugin.name": "pgoutput",
    "database.hostname": "postgres", 
    "database.port": "5432", 
    "database.user": "postgresuser", 
    "database.password": "postgrespw", 
    "database.dbname" : "shipment_db", 
    "database.server.name": "postgres", 
    "table.include.list": "public.shipments" 
  }
}'

TO_TSVECTOR metodu - Text to tsvector Type - Full Text Search İçindir

to_tsvector Nedir? - Text to tsvector Type

Açıklaması şöyle

We can use the to_tsvector to convert any arbitrary text to tsvector similar to how we typecast other data types.

Açıklaması şöyle

The `to_tsvector` function tokenizes the text, removes stop words, applies stemming (reducing words to their root form), and assigns weights to the tokens based on their importance. The resulting `ts_vector` object is a sorted list of lexemes with their respective positions and weights. For example, the text “The quick brown fox” in English might be represented as a `ts_vector` like this: `’brown’:3 ‘fox’:4 ‘quick’:2`. 

Örnek

Şöyle yaparız ve çıktı olarak şunu alırız

SELECT to_tsvector('the cat got scared by a cucumber');

'car':2 'cucumb':7 'got':3 'scare':4

tsvector Veri Yapısı Nedir? - Metni Lexeme'lere (Kelimelere) Böler - Full Text Search İçindir

Giriş

Açıklaması şöyle. Bir metni kelimeler olarak saklar. Yani tsvector metni Lexeme'lere böler.

The `ts_vector` data type in PostgreSQL represents a document as a sequence of lexemes (words) along with their positions and weights. It is created using the `to_tsvector` function, which takes a configuration name (specifying the language and text processing rules) and a text value as input.

Açıklaması şöyle. tsvector kelimesi text search vector anlamına gelir.

tsvector is a particular data type that stores text structure in the document format. tsvector stands for text search vector. We can use the to_tsvector to convert any arbitrary text to tsvector similar to how we typecast other data types.

Document format için açıklama şöyle

A document is the unit of searching in a full text search system; for example, a magazine article or email message. The text search engine must be able to parse documents and store associations of lexemes (key words) with their parent document. Later, these associations are used to search for documents that contain query words.

lexeme vs stem

Açıklaması şöyle. Sanırım lexeme bir seti temsil ediyor. Bu setin temel haline ise lemma deniliyor.

A "lexeme" is a theoretical thing, a unit in the mental lexicon. You can think of it as being an entire dictionary entry, but in our mental knowledge bank of what words mean rather than a physical book.

A "stem" is a practical thing: it's the part of a word that you stick affixes onto. The stem of play, playing, plays, played, etc is play-. In English the stem usually looks like an actual word, but it doesn't have to be: in Latin, the root of the Latin words amīcus, amīcī, amīcum, amīcō, etc is amīc-, which isn't a valid Latin word on its own. So you'll sometimes find the word "lemma" used to mean "the stem, with some default affix attached to make it a real word" (in Latin, that would be amīcus).

The concept of a lexeme is pretty standard across languages. No matter what language you speak, you have some sort of mental understanding of what words mean. But the concept of a stem is very useful in some languages and nigh useless in others. It all depends how much the language uses affixes.

Örnek

Şöyle yaparız ve çıktı olarak şunu alırız

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;

'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'

Debezium Connector transforms - route

Giriş

Değişen tablo ismine göre çıktının yönlendirileceği topic ismini belirtir

Örnek

Şöyle yaparız

{
  "name": "pg_user_data-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",
    "database.hostname": "postgres",
    "database.port": "5432",
    "database.user": "postgres",
    "database.password": "postgres",
    "database.server.id": "184055",
    "database.server.name": "dbserver2",
    "database.include": "user_data",
    "database.dbname": "user_data",
    "database.history.kafka.bootstrap.servers": "kafka:9092",
    "database.history.kafka.topic": "schema-changes.user_data",
    "transforms": "route",
    "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",
    "transforms.route.regex": "([^.]+)\\.([^.]+)\\.([^.]+)",
    "transforms.route.replacement": "$3"
  }
}

public.customers.orders tablosunu orders isimli topic'e yazar

Örnek

Şöyle yaparız

"config": {
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "database.hostname": "localhost",
  "database.port": "5432",
  "database.user": "sample_user",
  "database.password": "sample_pass",
  "database.dbname": "sample_db",
  "database.server.name": "sample_servername",
  "table.include.list": "sample_schema.sample_table",
  "topic.prefix": "sample.topic.prefix",
  "plugin.name": "pgoutput",
  "slot.name": "debezium_slot",
  "schema.include.list": "sample_schema",
  "transforms": "unwrap,reroute_topic",
  
  "transforms.reroute_topic.type": "io.debezium.transforms.ByLogicalTableRouter",
  "transforms.reroute_topic.key.enforce.uniqueness": "false",
  "transforms.reroute_topic.topic.regex": "sample_reroute_source_topic",
  "transforms.reroute_topic.topic.replacement": "sample_reroute_target_topic",
  
  "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
  "transforms.unwrap.drop.tombstones": "false",
  
  "key.converter": "org.apache.kafka.connect.json.JsonConverter",
  "key.converter.schemas.enable": "false",
  "value.converter": "org.apache.kafka.connect.json.JsonConverter",
  "value.converter.schemas.enable": "false",
  "snapshot.mode": "initial",
  "decimal.format": "NUMERIC",
  "json.output.decimal.format": "NUMERIC",
  "decimal.handling.mode": "string"
}

Burada sample_schema.sample_table tablosu takip ediliyor ve değişiklikler sample_reroute_target_topic isimli topic'e yazılıyor

JSON ->> Operator - Nesnenin Alanına Erişir WHERE Cümlesinde Kullanılabilir

Giriş

Açıklaması şöyle. Bu operatör hem JSON hem de JSONB için kullanılabilir

When we use ->> operator of JSONB, PostgreSQL can use B-tree or Hash index for processing the operations. ->> operator returns the value of the specified attribute in text format. 

Kullanım

İki kullanımı var

1. Nesnenin bir alanının değerine text olarak erişebiliriz. Açıklaması şöyle. 

Postgres supports JSON type from v9.2. It has added many operators and functions for data manipulation of JSON. The operator -> returns JSON object field by key. The operator ->> returns JSON object field by text.

2. WHERE cümlesinde kullanılabilir

Örnek

Elimizde şöyle bir tablo olsun ve tabloya veri girelim

CREATE TABLE sensor_data (
    id SERIAL NOT NULL PRIMARY KEY,
    data JSON NOT NULL
);

INSERT INTO sensor_data (data)
  VALUES('{ "ip": "J10.3.2.4", "payload": {"temp": "33.5","brightness": "73"}}');

Veriyi sorgulamak için şöyle yaparız. Çıktı olarak ip değerlerini text olarak alırız.

SELECT data->> 'ip' AS ip FROM sensor_data;

Örnek - = Koşulu

Şöyle yaparız

SELECT orders FROM lunchorders WHERE orders ->> 'order_date' = '2020-12-11';

Örnek - >  Koşulu

Şöyle yaparız.

SELECT * FROM lunchorders WHERE  (orders -> 'order_details' ->> 'cost')::numeric > 4.50;

Örnek - LIKE

Şöyle yaparız

SELECT * FROM user_details WHERE details ->> 'alternateContacts' like '%777%';

JPA

Elimizde şöyle bir tablo olsun

CREATE TABLE employee (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  data JSONB
);

Entity şöyle olsun. Burada JSONB verisini Java'da String olarak saklıyoruz

@Entity
@Getter
@Setter
@Table(name = "employee")
public class Employee {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String name;

  @Column(columnDefinition = "jsonb")
  private String data;
}

SpringBoot projesinde @Query yani SQL ile şöyle yaparız. JSON ->> Operator kullanılıyor

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

  @Query(value = "SELECT * FROM employee WHERE data->>'department' = ?1", 
         nativeQuery = true)
  List<Employee> findByDepartment(String department);
}

Specification ile şöyle yaparız. Burada hasDepartment() bir static factory ve bir tane Specification döndürülüyor. Specification Root ve CriteriaQuery nesnelerini kullanarak Type Safe Query imkanı tanıyor. Burada JSON ->> Operator yerine aynı şey olan jsonb_extract_path_text() kullanılıyor.

@Repository
public interface EmployeeSpecificationRepository extends JpaRepository<Employee, Long>, 
  JpaSpecificationExecutor<Employee> {
}

public class EmployeeSpecification {

  public static Specification<Employee> hasDepartment(String department) {
    return new Specification<Employee>() {
      @Override
      public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query,
        CriteriaBuilder cb) {
        return cb.equal(
          cb.function("jsonb_extract_path_text", String.class, root.get("data"), 
            cb.literal("department")),
          department
        );
      }
    };
  }
}

Her iki yöntemi de kullanan Controller için şöyle yaparız

@RestController
public class EmployeeController {

  @Autowired
  private EmployeeRepository employeeRepository;

  @Autowired
  private EmployeeSpecificationRepository employeeSpecificationRepository;

  @GetMapping("/employees/{department}")
  public List<Employee> getByDepartment(@PathVariable String department) {
    return employeeRepository.findByDepartment(department);
  }

  @GetMapping("/employees/Specification/{department}")
  public List<Employee> getBySpecificationDepartment(@PathVariable String department) {
    Specification<Employee> spec = EmployeeSpecification.hasDepartment(department);
    return employeeSpecificationRepository.findAll(spec);
  }
}

PostgreSQL'i Message Queue Olarak Kullanmak

PGMQ 

- PGMQ uzantısı kullanılabilir. Burada bazı açıklamalar var

Custom Çözüm

- Burada bir örnek var