Category: Blog

Traditionally migrating data changes between applications with was implemented real-time or near real-time using APIs developed on source or target with a push or pull mechanism, incremental data transfers using database logs, batch processes with custom scripts etc., These solutions had drawbacks like

• Source and target system code changes catering to specific requirement
• Near real-time leading to data loss
• Performance issues when the data change frequency and/or the volume is high
• Push or pull mechanism leading to high availability requirement
• adding multiple target applications would need a larger turnaround time
• Database specific real-time migration was confined to vendor specific implementation
• Scalability of the solution was time and cost intensive operation

Change data capture (CDC) refers to the process of identifying and capturing changes made to data in a database and then delivering those changes in real-time to a downstream process or system. Moving data from one application database into another database with a minimal impact on the performance of the applications is the main motto behind this design pattern. It is a perfect solution for modern cloud architectures since it is a highly efficient way to move data across a wide area network. And, since it’s moving data in real-time, it also supports real-time analytics and data science.

In most of the scenarios CDC is used to capture changes to data and take an action based on that change. The change to data is usually one of insert, update or delete. The corresponding action usually is supposed to occur in the target system in response to the change that was made in the source system. Some use cases include:

• Moving data changes from OLTP to OLAP in real time
• Consolidating audit logs
• Tracking data changes of specific objects to be fed into target SQL or NoSQL databases

Overview:

In the following example we would be using CDC between source and target PostgreSQL instances using Debezium connector on Apache Kafka with a Confluent schema registry to migrate the schema changes onto the target database. We would be using docker containers to setup the environment.

Now, let us setup the docker containers to perform CDC operations. In this article we would be focusing only on insert and update operations.

Docker Containers:

1. In a Windows or Linux machine, install Docker and create a docker-compose.yml file with the following configuration.

2. Run the docker-compose.yml file by navigating to the directory in which the file was created using command prompt or a terminal and run the below command.

Debezium plugin configuration:

1. Once the docker containers are created, we need to copy Debezium kafka connect jar files into the plugins folder using the below command.

2. Restart the kafka-connect container after the copy command is executed.

Database configuration:

1. Connect to the postgres-source and postgres-target databases using psql or pgAdmin tool.
2. Create a database named testdb in both the servers.
3. Create a sample table in both the databases.

Ex: create table test(uuid serial primary key, name text);

4. In postgres-source database execute the below command to change WAL_LEVEL to logical.

Alter system set wal_level=’logical’;

5. Restart the postgres-source docker container using the docker stop and start commands.

Source Connector:

Using any of the REST client tools, like Postman, send a POST request to the following endpoint with the below mentioned body to create the source connector.

Endpoint: http://localhost:8083/connectors

In case of multiple tables, include the comma separated tables list in table.include.list property.

Sink Connector:

Using any of the REST client tools, like Postman, send a POST request to the following endpoint with the below mentioned body to create the sink connector.

Endpoint: http://localhost:8083/connectors

Note: 172.18.0.5 is the local IP address of postgres-target database which can be obtained using docker inspect command. Replace the IP address with the one from target container.

Testing the connectors:

Once the connectors are created, insert or update the records in the table(s) of the source database. Check the records in the target database.

In order to debug, check the logs of kafka-connect container using docker logs command. Ex: docker logs –f 4a

To delete the created connectors run the following API with DELETE method chosen as the request method.

http://localhost:8083/connectors/test-sink-schema-connector

While trends in the power and utilities business models have been exempt from massive overhauls for a while, the influence of technological innovations is slowly starting to change this factor. Due to this, digitization’s visionary approach to technology lays the groundwork for new capabilities that are expected to accelerate exponential growth in pressure-pumping market value for decades to come.

What is digital transformation in the power sector?

Rapid transition to clean power is helping businesses reduce their carbon footprints and many are also racing to catch up with the rising sophistication of digital technologies. Companies must ensure that they have the right technology in place to support a digital transformation strategy and leverage it across their entire operations. The power industry is leading the way in this direction, with renewable power at the center of many providers’ strategies. 

This translates into a newer, more diverse set of assets to manage and integrate with the rapidly ageing set of existing assets worth about $1.2 trillion. In addition to online tools that automate business processes, programmable automation (PA) and cognitive computing are quickly entering the mainstream as firms realise these technologies can help them reduce costs and improve performance. But moving from traditional asset management strategies to develop comprehensive digital strategies with robust data governance and cybersecurity at its core will mean going beyond software alone: 

The need of the moment: Companies need more robust analytics capabilities that can support key business processes such as fuel planning, risk management, maintenance scheduling, and asset monitoring as well as infrastructure planning for new projects

Digital paradigm shifts in power transformation:

The power and utilities industry today faces a host of challenges that are making the business more difficult to succeed in. On the one hand, high oil prices, a growing population, and widespread use of electricity are making it imperative that businesses adapt to reflect market demands. Meanwhile, industry professionals lack the agility and resilience to effectively deal with these new demands.

At this point in time, what’s more important is how your business will be better able to adapt to changing conditions and capitalise on opportunities as they emerge.

Let us look at some digital paradigm shifts that are game changers in the vertical:

1. Industry 4.0:

Industry 4.0 has revolutionised the way manufacturing operations are run. Industry 4.0 is the transition from a manufacturing operation to an intelligent operation. Data-driven, automated, and ever-improving technology will make factories leaner, more efficient, and profitable.

Today, there is more opportunity than ever before in this industry as digital twins and IoT devices bring us ever closer to fully autonomous systems fuelled by massive amounts of data. This gives more power to utility companies when it comes to gaining a competitive advantage over their peers. However, cyber security becomes even more important through such innovations.

2. Compliance:

The industry is facing an uphill battle in order to meet new emission regulations and sustainability targets. Oil and gas companies are being tasked with implementing smarter, more efficient operations. With the heat on for future-ready leaders, getting creative with available technology solutions will be key in our efforts to meet air quality goals.

3. Changing industry needs:

The shift toward alternative power sources has not only affected the business but has also opened new opportunities for companies in the industry. It has shown the customers what is needed and how their lives will be improved. The shift towards clean power has created new revenue opportunities for utility companies. Customer accessibility and sustainability have driven the development of IoT devices, which allow power companies to connect with customers and influence behavior. In addition, IoT devices are helping reduce the environmental impact that extraction creates.

4. Digital asset management:

Digital innovation is playing a key role in the transformation of asset management in the generation segment. As the industry moves toward digitization in the sector, digital asset management can play a key role in the transformation of asset management, helping reduce O&M costs, boost reliability and profitability, and lowering greenhouse gas (GHG) emissions. 

5. Data management:

We are now entering the era of smart grids. Companies are facing the challenge of collecting, analysing, and acting on all their valuable data. Connected devices help them to collect and analyse data from multiple sources and make it accessible to other departments of an enterprise. Infrastructure set in place that communicates with data centres provides information on electricity consumption; sensors enable real-time monitoring of facilities and assets on the premises; IoT-based solutions offer comprehensive insight into the performance of plants, equipment, and networks as well as usage patterns at every stage of the supply chain.

Wrapping up…

The power industry is open to a great number of digital transformations, the most important of which is the fact that it allows companies to open new horizons of digital opportunities and reach a new level of global economic growth, and effective operation. 

With the transition to digitization underway, IT architecture is playing an increasingly important role in ensuring that generation and consumption interact smoothly. Not only does the utility sector need to find new ways to manage their business, but they also have a duty to inform their consumers about new technologies and service offerings so they can make informed choices.