Using PostgreSQL pgoutput Plugin for Change Data Capture With Debezium

Change Data Capture Architecture Using Debezium, Postgres, and Kafka

was a tutorial on how to use Debezium for change data capture from Azure PostgreSQL and send them to Azure Event Hubs for Kafka - it used the wal2json output plugin.

What About the pgoutput Plugin?

This blog will provide a quick walk through of how to pgoutput plugin. I will not be repeating a lot of details and use containerized versions (using Docker Compose) of Kafka connect, Kafka (and Zookeeper) to keep things simple. So, the only thing you need is Azure PostgreSQL, which you can setup using a variety of options including, the Azure Portal, Azure CLI, Azure PowerShell, ARM template.

The resources are available on GitHub - https://github.com/abhirockzz/debezium-postgres-pgoutput

Using the Right publication.autocreate.mode

With the pgoutput plugin, it’s important that you use the appropriate value for publication.autocreate.mode. If you’re using all_tables (which is the default), you need to ensure that the publication is created up-front for the specific table(s) you want to configure for change data capture. If the publication is not found, the connector will try to create one using _CREATE PUBLICATION <publication_name> FOR ALL TABLES;_ which will fail due to lack of permissions.

The other two options work as expected:

• disabled: you need to ensure that the publication is created up-front. The connector will not attempt to create the publication if it isn’t found to exist upon startup - it will throw an exception and stop.
• filtered: you can (optionally) choose to create the publication up-front. If the publication is not found, the connector will create a new publication for all those tables matching the current filter configuration.

This has been highlighted in the docs https://debezium.io/documentation/reference/1.3/connectors/postgresql.html#postgresql-on-azure

Let’s Try the Different Scenarios

Before that:

Java

1

git clone https://github.com/abhirockzz/debezium-postgres-pgoutput && cd debezium-postgres-pgoutput

Start Kafka, Zookeeper and Kafka Connect containers:

Java

1

export DEBEZIUM_VERSION=1.2

2

docker-compose up

It might take a while to pull the containers for the first time

Once all the containers are up and running, connect to Azure PostgreSQL, create a table and insert some data:

Java

1

psql -h <DBNAME>.postgres.database.azure.com -p 5432 -U <DBUSER>@<DBNAME> -W -d postgres --set=sslmode=require

2

3

psql -h abhishgu-pg.postgres.database.azure.com -p 5432 -U abhishgu@abhishgu-pg -W -d postgres --set=sslmode=require

4

5

CREATE TABLE inventory (id SERIAL, item VARCHAR(30), qty INT, PRIMARY KEY(id));

When publication.autocreate.mode is set to filtered

This works well with Azure PostgreSQL - it does not require super user permissions because the connector creates the publication for a specific table(s) based on the filter/*list values

Update the connector config file (pg-source-connector.json) with details of your Azure PostgreSQL instance and then create the connector

To create the connector:

Java

1

curl -X POST -H "Content-Type: application/json" --data @pg-source-connector.json http://localhost:8083/connectors

Notice the logs (in the docker compose terminal):

Java

1

Creating new publication 'mytestpub' for plugin 'PGOUTPUT'   [io.debezium.connector.postgresql.connection.PostgresReplicationConnection]

Once the connector starts, check the publications in PostgreSQL:

Java

1

  pubname  | schemaname | tablename 

2

-----------+------------+-----------

3

 mytestpub | public     | inventory

Does it work?

Insert a couple of records in the inventory table

Java

1

psql -h <DBNAME>.postgres.database.azure.com -p 5432 -U <DBUSER>@<DBNAME> -W -d postgres --set=sslmode=require

2

3

INSERT INTO inventory (item, qty) VALUES ('apples', '100');

4

INSERT INTO inventory (item, qty) VALUES ('oranges', '42');

5

6

select * from inventory;

The connector should push the change events from PostgreSQL WAL (write ahead log) to Kafka. Check the messages in the corresponding Kafka topic:

Java

1

//exec into the kafka docker container

2

docker exec -it debezium-postgres-pgoutput_kafka_1 bash

3

4

cd bin && ./kafka-console-consumer.sh --topic myserver.public.inventory --bootstrap-server kafka:9092 --from-beginning

You should see a couple of change log event payloads (corresponding to the two INSERTs)

yes they are verbose since the schema is included in the payload

#tutorial #sql #azure #postgresql #kafka #databases #debezium

What is GEEK

Buddha Community

Your Data Architecture: Simple Best Practices for Your Data Strategy

If you accumulate data on which you base your decision-making as an organization, you should probably think about your data architecture and possible best practices.

If you accumulate data on which you base your decision-making as an organization, you most probably need to think about your data architecture and consider possible best practices. Gaining a competitive edge, remaining customer-centric to the greatest extent possible, and streamlining processes to get on-the-button outcomes can all be traced back to an organization’s capacity to build a future-ready data architecture.

In what follows, we offer a short overview of the overarching capabilities of data architecture. These include user-centricity, elasticity, robustness, and the capacity to ensure the seamless flow of data at all times. Added to these are automation enablement, plus security and data governance considerations. These points from our checklist for what we perceive to be an anticipatory analytics ecosystem.

#big data #data science #big data analytics #data analysis #data architecture #data transformation #data platform #data strategy #cloud data platform #data acquisition

Getting Started With Data Lakes

Frameworks for Efficient Enterprise Analytics

The opportunities big data offers also come with very real challenges that many organizations are facing today. Often, it’s finding the most cost-effective, scalable way to store and process boundless volumes of data in multiple formats that come from a growing number of sources. Then organizations need the analytical capabilities and flexibility to turn this data into insights that can meet their specific business objectives.

This Refcard dives into how a data lake helps tackle these challenges at both ends — from its enhanced architecture that’s designed for efficient data ingestion, storage, and management to its advanced analytics functionality and performance flexibility. You’ll also explore key benefits and common use cases.

Introduction

As technology continues to evolve with new data sources, such as IoT sensors and social media churning out large volumes of data, there has never been a better time to discuss the possibilities and challenges of managing such data for varying analytical insights. In this Refcard, we dig deep into how data lakes solve the problem of storing and processing enormous amounts of data. While doing so, we also explore the benefits of data lakes, their use cases, and how they differ from data warehouses (DWHs).

---

This is a preview of the Getting Started With Data Lakes Refcard. To read the entire Refcard, please download the PDF from the link above.

#big data #data analytics #data analysis #business analytics #data warehouse #data storage #data lake #data lake architecture #data lake governance #data lake management

Using PostgreSQL pgoutput Plugin for Change Data Capture With Debezium

Change Data Capture Architecture Using Debezium, Postgres, and Kafka

was a tutorial on how to use Debezium for change data capture from Azure PostgreSQL and send them to Azure Event Hubs for Kafka - it used the wal2json output plugin.

What About the pgoutput Plugin?

This blog will provide a quick walk through of how to pgoutput plugin. I will not be repeating a lot of details and use containerized versions (using Docker Compose) of Kafka connect, Kafka (and Zookeeper) to keep things simple. So, the only thing you need is Azure PostgreSQL, which you can setup using a variety of options including, the Azure Portal, Azure CLI, Azure PowerShell, ARM template.

The resources are available on GitHub - https://github.com/abhirockzz/debezium-postgres-pgoutput

Using the Right publication.autocreate.mode

With the pgoutput plugin, it’s important that you use the appropriate value for publication.autocreate.mode. If you’re using all_tables (which is the default), you need to ensure that the publication is created up-front for the specific table(s) you want to configure for change data capture. If the publication is not found, the connector will try to create one using _CREATE PUBLICATION <publication_name> FOR ALL TABLES;_ which will fail due to lack of permissions.

The other two options work as expected:

• disabled: you need to ensure that the publication is created up-front. The connector will not attempt to create the publication if it isn’t found to exist upon startup - it will throw an exception and stop.
• filtered: you can (optionally) choose to create the publication up-front. If the publication is not found, the connector will create a new publication for all those tables matching the current filter configuration.

This has been highlighted in the docs https://debezium.io/documentation/reference/1.3/connectors/postgresql.html#postgresql-on-azure

Let’s Try the Different Scenarios

Before that:

Java

1

git clone https://github.com/abhirockzz/debezium-postgres-pgoutput && cd debezium-postgres-pgoutput

Start Kafka, Zookeeper and Kafka Connect containers:

Java

1

export DEBEZIUM_VERSION=1.2

2

docker-compose up

It might take a while to pull the containers for the first time

Once all the containers are up and running, connect to Azure PostgreSQL, create a table and insert some data:

Java

1

psql -h <DBNAME>.postgres.database.azure.com -p 5432 -U <DBUSER>@<DBNAME> -W -d postgres --set=sslmode=require

2

3

psql -h abhishgu-pg.postgres.database.azure.com -p 5432 -U abhishgu@abhishgu-pg -W -d postgres --set=sslmode=require

4

5

CREATE TABLE inventory (id SERIAL, item VARCHAR(30), qty INT, PRIMARY KEY(id));

When publication.autocreate.mode is set to filtered

This works well with Azure PostgreSQL - it does not require super user permissions because the connector creates the publication for a specific table(s) based on the filter/*list values

Update the connector config file (pg-source-connector.json) with details of your Azure PostgreSQL instance and then create the connector

To create the connector:

Java

1

curl -X POST -H "Content-Type: application/json" --data @pg-source-connector.json http://localhost:8083/connectors

Notice the logs (in the docker compose terminal):

Java

1

Creating new publication 'mytestpub' for plugin 'PGOUTPUT'   [io.debezium.connector.postgresql.connection.PostgresReplicationConnection]

Once the connector starts, check the publications in PostgreSQL:

Java

1

  pubname  | schemaname | tablename 

2

-----------+------------+-----------

3

 mytestpub | public     | inventory

Does it work?

Insert a couple of records in the inventory table

Java

1

psql -h <DBNAME>.postgres.database.azure.com -p 5432 -U <DBUSER>@<DBNAME> -W -d postgres --set=sslmode=require

2

3

INSERT INTO inventory (item, qty) VALUES ('apples', '100');

4

INSERT INTO inventory (item, qty) VALUES ('oranges', '42');

5

6

select * from inventory;

The connector should push the change events from PostgreSQL WAL (write ahead log) to Kafka. Check the messages in the corresponding Kafka topic:

Java

1

//exec into the kafka docker container

2

docker exec -it debezium-postgres-pgoutput_kafka_1 bash

3

4

cd bin && ./kafka-console-consumer.sh --topic myserver.public.inventory --bootstrap-server kafka:9092 --from-beginning

You should see a couple of change log event payloads (corresponding to the two INSERTs)

yes they are verbose since the schema is included in the payload

#tutorial #sql #azure #postgresql #kafka #databases #debezium

Data Lake and Data Mesh Use Cases

As data mesh advocates come to suggest that the data mesh should replace the monolithic, centralized data lake, I wanted to check in with Dipti Borkar, co-founder and Chief Product Officer at Ahana. Dipti has been a tremendous resource for me over the years as she has held leadership positions at Couchbase, Kinetica, and Alluxio.

Definitions

• A data lake is a concept consisting of a collection of storage instances of various data assets. These assets are stored in a near-exact, or even exact, copy of the resource format and in addition to the originating data stores.
• A data mesh is a type of data platform architecture that embraces the ubiquity of data in the enterprise by leveraging a domain-oriented, self-serve design. Mesh is an abstraction layer that sits atop data sources and provides access.

According to Dipti, while data lakes and data mesh both have use cases they work well for, data mesh can’t replace the data lake unless all data sources are created equal — and for many, that’s not the case.

Data Sources

All data sources are not equal. There are different dimensions of data:

• Amount of data being stored
• Importance of the data
• Type of data
• Type of analysis to be supported
• Longevity of the data being stored
• Cost of managing and processing the data

Each data source has its purpose. Some are built for fast access for small amounts of data, some are meant for real transactions, some are meant for data that applications need, and some are meant for getting insights on large amounts of data.

AWS S3

Things changed when AWS commoditized the storage layer with the AWS S3 object-store 15 years ago. Given the ubiquity and affordability of S3 and other cloud storage, companies are moving most of this data to cloud object stores and building data lakes, where it can be analyzed in many different ways.

Because of the low cost, enterprises can store all of their data — enterprise, third-party, IoT, and streaming — into an S3 data lake. However, the data cannot be processed there. You need engines on top like Hive, Presto, and Spark to process it. Hadoop tried to do this with limited success. Presto and Spark have solved the SQL in S3 query problem.

#big data #big data analytics #data lake #data lake and data mesh #data lake #data mesh

How Has COVID-19 Impacted Data Science?

The COVID-19 pandemic disrupted supply chains and brought economies around the world to a standstill. In turn, businesses need access to accurate, timely data more than ever before. As a result, the demand for data analytics is skyrocketing as businesses try to navigate an uncertain future. However, the sudden surge in demand comes with its own set of challenges.

Here is how the COVID-19 pandemic is affecting the data industry and how enterprises can prepare for the data challenges to come in 2021 and beyond.

#big data #data #data analysis #data security #data integration #etl #data warehouse #data breach #elt