Forem: Wayne S

Exploring the Implementation of Graph Data in OceanBase

Wayne S — Tue, 26 Dec 2023 14:00:36 +0000

In the modern landscape of data management and processing, the need for sophisticated data models and methodologies that can handle complex data structures has never been more imperative. The advent of graph data, with its unique ability to represent intricate relationships naturally and intuitively, has opened up a new frontier in data analysis.

While established graph databases like Neo4j offer robust solutions for handling graph data, the idea of integrating graph data into SQL databases offers potential advantages worth exploring. SQL databases are well-regarded for their ability to handle structured data efficiently, offering robustness, reliability, and a familiar query language (SQL). In this article, I will try to explore the potential implementation of graph data and graph queries in OceanBase, an open-source distributed SQL database.

By harnessing the power of graph data within a SQL database, we aim to combine the strengths of both paradigms - the structured data handling of SQL databases with the flexible, relationship-focused nature of graph data.

This article is an initial exploration of the potential benefits, challenges, and implications of incorporating graph data into the OceanBase ecosystem. I’ll dive into the reasons behind this integration, explore potential data modeling strategies, and discuss some sample queries. We'll also critically evaluate the performance implications and the limitations that could arise during this implementation.

My aim is not to provide a definitive solution but to stimulate a conversation and inspire further research on this topic. The final section of the article will offer some suggestions for the OceanBase team, hoping to contribute to the ongoing evolution of the database.

What is graph data and why it is important

Graph data is a form of data that captures entities, known as nodes or vertices, and the relationships between these entities, known as edges or links. This flexible structure enables graph data to model intricate relationships and complex systems with a high degree of fidelity, making it a powerful tool in diverse fields, from social network analysis to supply chain management.

Graph data is characterized by its non-linearity and the absence of a predetermined schema, which distinguishes it from traditional relational databases. The focus is not on the data itself but on the relationships between the data. This relationship-centric approach allows for deep, multi-layered analysis and can reveal insights that may remain hidden in more traditional data structures.

To illustrate, consider a social media platform. In this context, individual users can be represented as nodes, and the connections between them - friendships, messages, interactions - can be represented as edges.

A graph data model would allow us to map out the entire network, providing a detailed picture of how users interact with each other. This could reveal patterns and insights such as the most influential users, communities within the larger network, and potential pathways for information flow.

Why OceanBase users should care about graph data?

The idea to explore the integration of graph data into OceanBase is driven by the increasing prevalence of mixed data models in modern applications. As businesses and organizations tackle more complex problems and seek to extract more value from their data, they often find themselves needing to combine the structured, tabular data model offered by SQL databases with graph data. Since I have developed multiple projects based on OceanBase, it is an ideal platform for me to do such an experiment.

Numerous potential use cases could benefit from this integration. For example, in online finance and recommendation systems, the ability to efficiently trace and analyze connections could prove invaluable. In other words, by trying to implement graph data in OceanBase, we're not only trying to enrich its capabilities but also expand its potential applications.

However, challenges exist. The unique nature of graph data, with its focus on relationships over individual data points, presents new problems in terms of data modeling, query language, and performance optimization. The following sections will dive into these issues, providing a comprehensive analysis of the feasibility and potential benefits of this integration.

Modeling of graph data in OceanBase

To model graph data within OceanBase, we would essentially be creating a relational representation of our graph. The graph is broken down into its fundamental components: vertices (or nodes) and edges (or relationships). This approach is commonly known as the adjacency list model.

The adjacency list model involves two tables: one for vertices and one for edges. The Vertices table stores information about each vertex, while the Edges table stores information about each edge, including the vertices it connects.

Let's look at how these tables can be structured in OceanBase:

Create the vertex table:

CREATE TABLE Vertices (
    VertexID INT PRIMARY KEY,
    VertexProperties JSON
);

In this table, VertexID is the primary key that uniquely identifies each vertex. VertexProperties is a JSON field that stores the properties of each vertex. This structure allows us to represent a wide range of data types and complexities.

The edge table schema:

CREATE TABLE Edges (
    EdgeID INT PRIMARY KEY,
    SourceVertexID INT,
    TargetVertexID INT,
    EdgeProperties JSON,
    FOREIGN KEY (SourceVertexID) REFERENCES Vertices(VertexID),
    FOREIGN KEY (TargetVertexID) REFERENCES Vertices(VertexID)
);

In the Edges table, EdgeID is the primary key that uniquely identifies each edge. SourceVertexID and TargetVertexID identify the vertices that this edge connects, and EdgeProperties is a JSON field that stores the properties of each edge. The FOREIGN KEY constraints ensure that every edge corresponds to valid vertices in the Vertices table.

With the schemata of each table set, let’s try to create some dummy data in the database for later queries.

In the Vertices table, I will try to create 1,000 rows that represent users in an online transaction app.

CREATE FUNCTION random_ip() RETURNS TEXT DETERMINISTIC
BEGIN
  RETURN CONCAT(
    FLOOR(RAND() * 255) + 1, '.',
    FLOOR(RAND() * 255) + 0, '.',
    FLOOR(RAND() * 255) + 0, '.',
    FLOOR(RAND() * 255) + 0
  );
END;

CREATE FUNCTION random_device() RETURNS TEXT DETERMINISTIC
BEGIN
  DECLARE device TEXT;
  SET device = CASE FLOOR(RAND() * 6)
    WHEN 0 THEN 'IPHONE'
    WHEN 1 THEN 'ANDROID'
    WHEN 2 THEN 'PC'
    WHEN 3 THEN 'IPAD'
    WHEN 4 THEN 'TABLET'
    ELSE 'LAPTOP'
  END;
  RETURN device;
END;

CREATE PROCEDURE InsertRandomUsers()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000 DO
    INSERT INTO Vertices (VertexID, VertexProperties)
    VALUES (i+1, CONCAT(
      '{"name": "User', i+1,
      '", "email": "user', i+1,
      '@email.com", "ip":"', random_ip(),
      '", "DEVICE": "', random_device(), '"}'
    ));
    SET i = i + 1;
  END WHILE;
END;

CALL InsertRandomUsers();

The “users” in the table have properties such as IP, device, and email. With this setup, it is possible to explore how to implement anti-money laundry and fraud detection algorithms in an online transaction app. However, the situation in the real world will be much more complicated, thus these nodes will have more properties for analysis.

Now, let’s try to create some dummy data for the Edges table, with each row representing a “transaction” between each “user.” Each transaction will only have one property-the amount of the transaction.

-- Create a temporary table to hold a series of numbers
CREATE TEMPORARY TABLE temp_numbers (
    number INT
);

-- Stored procedure to fill the temporary table
DELIMITER //
CREATE PROCEDURE FillNumbers(IN max_number INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= max_number DO
    INSERT INTO temp_numbers VALUES (i);
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

-- Call the procedure again to fill the temporary table with numbers from 1 to 1000
CALL FillNumbers(10000);

-- Use the temporary table to insert 1000 edges
INSERT INTO Edges (EdgeID, SourceVertexID, TargetVertexID, EdgeProperties)
SELECT
  number,
  FLOOR(1 + RAND() * 1000),
  FLOOR(1 + RAND() * 1000),
  CONCAT('{"amount": ', FLOOR(10 + RAND() * 1000), ', "date": "', DATE(NOW() - INTERVAL FLOOR(1 + RAND() * 365) DAY), '"}')
FROM temp_numbers;

This SQL query will create 10,000 random transactions in the Edges table. The data will also indicate the sender(SourceVertexID) and receiver(TargetVertexID) of the transaction.

Now here is what our database looks like.

Vertices:

Edges:

This example demonstrates a basic way to model graph data in OceanBase. It shows that even without native graph support, it's possible to implement graph-like structures in OceanBase. However, this approach has its limitations, especially when it comes to complex graph operations and performance, which we will discuss in the subsequent sections.

Some sample queries

Building on the adjacency list model we have implemented in OceanBase, we can now perform a variety of graph queries. In this section, I will try to run some sample queries using typical graph algorithms. It's worth noting that OceanBase's support for the recursive Common Table Expressions (CTEs) makes recursive queries possible, which is crucial for many graph algorithms.

Breadth-First Search (BFS)

Breadth-first search is a common algorithm used in graph data to find the shortest path from a starting node to a target node. It's particularly useful for exploring the connections between nodes in a graph. We can use recursive CTEs in OceanBase to implement BFS.

Suppose we want to find all users reachable within 3 steps (or transactions) from a specific user (with VertexID = 1), we can use the following query:


WITH RECURSIVE bfs AS (
    SELECT SourceVertexID, TargetVertexID, 1 AS depth
    FROM Edges
    WHERE SourceVertexID = 1
    UNION ALL
    SELECT Edges.SourceVertexID, Edges.TargetVertexID, bfs.depth + 1
    FROM Edges JOIN bfs ON Edges.SourceVertexID = bfs.TargetVertexID
    WHERE bfs.depth < 3
)
SELECT * FROM bfs;

In this query, the WITH RECURSIVE clause creates a recursive CTE named bfs. The CTE starts with all edges originating from the node with VertexID = 1. It then repeatedly adds edges that connect from the previously found nodes until it reaches a depth of 3.

The BFS query returns a set of all users that can be reached within 3 transactions from the user with VertexID = 1 (or other nodes based on your choice).

This result provides insights into the transaction network of the initial user, revealing users that are closely connected (within 3 transactions) to the initial user. This information can be useful in various applications such as fraud detection, where closely connected users may indicate suspicious activities.

In the query against my dummy database, I retrieved 871 rows of data with 131 ms. This is not too bad considering it contains 1,000 nodes and 10,000 edges.

Detecting Circular Transactions

Circular transactions, where money is transferred around a loop of accounts, can be a signal of fraudulent or money laundering activities. Identifying such loops can be a useful way to detect suspicious activity. We can use a depth-first search (DFS) algorithm to find such loops. Here is an example of how that might be done with a maximum path length of 5:

WITH RECURSIVE dfs AS (
    SELECT SourceVertexID, TargetVertexID, CAST(CONCAT(SourceVertexID, '-', TargetVertexID) AS CHAR(1000)) AS path, 1 AS depth
    FROM Edges
    WHERE SourceVertexID = 1
    UNION ALL
    SELECT dfs.SourceVertexID, Edges.TargetVertexID, CONCAT(dfs.path, '-', Edges.TargetVertexID), dfs.depth + 1
    FROM Edges JOIN dfs ON Edges.SourceVertexID = dfs.TargetVertexID
    WHERE dfs.depth < 5 AND dfs.SourceVertexID != Edges.TargetVertexID AND dfs.path NOT LIKE CONCAT('%-', Edges.TargetVertexID, '-%')
)
SELECT * FROM dfs WHERE SourceVertexID = TargetVertexID;

In this query, the WITH RECURSIVE clause creates a recursive CTE named dfs. The CTE starts with all edges originating from the node with VertexID = 1. It then repeatedly adds edges that connect from the previously found nodes until it reaches a depth of 5 or it finds a circular transaction (where SourceVertexID = TargetVertexID). The path column tracks the path taken by the DFS, which is used to ensure that the same node is not visited twice.

Identifying High-Frequency Transaction Pairs

In an online transaction system, if two users are frequently transacting with each other, it could indicate a strong relationship between them, or it could be a sign of suspicious activity. We can use a simple SQL query to find pairs of users who have transacted with each other more than a certain number of times:

SELECT SourceVertexID, TargetVertexID, COUNT(*) AS transaction_count
FROM Edges
GROUP BY SourceVertexID, TargetVertexID
HAVING transaction_count > 5
ORDER BY transaction_count DESC;

This query groups the transactions by the source and target vertices (i.e., the users involved in the transaction) and counts the number of transactions for each pair. It then filters out pairs with less than 5 transactions and orders the result by the number of transactions.

In this example, the algorithm took 127 milliseconds to complete querying all 10,000 transactions.

Uncovering Unusually Large Transactions

Sometimes, unusually large transactions may be a sign of potential fraud. We can use SQL to identify transactions where the amount is higher than a certain threshold (in this case, the threshold is set to 990 because the dummy data is random between 1 and 1,000. But you can set a more realistic threshold in your case).

Suppose we want to not only find transactions where the amount is higher than a certain threshold but also want to include information about the sender and recipient, such as their device, IP, and transaction date. We also want to order the results by the transaction amount and date. Here's how we can do that:

SELECT
    e.SourceVertexID,
    e.TargetVertexID,
    JSON_EXTRACT(e.EdgeProperties, '$.amount') AS transaction_amount,
    JSON_EXTRACT(e.EdgeProperties, '$.date') AS transaction_date,
    JSON_EXTRACT(s.VertexProperties, '$.DEVICE') AS sender_device,
    JSON_EXTRACT(s.VertexProperties, '$.ip') AS sender_ip,
    JSON_EXTRACT(r.VertexProperties, '$.DEVICE') AS recipient_device,
    JSON_EXTRACT(r.VertexProperties, '$.ip') AS recipient_ip
FROM
    Edges AS e
JOIN
    Vertices AS s ON e.SourceVertexID = s.VertexID
JOIN
    Vertices AS r ON e.TargetVertexID = r.VertexID
WHERE
    JSON_EXTRACT(e.EdgeProperties, '$.amount') > 990
ORDER BY
    transaction_amount DESC,
    transaction_date DESC;

In this query, we join the Edges table with the Vertices table twice - once for the sender (source vertex) and once for the recipient (target vertex). We then extract additional information about the sender and recipient from the VertexProperties field. Finally, we order the results by the transaction amount and date in descending order. This query gives us a more comprehensive view of large transactions, including who is involved and what devices they're using.

The performance of this query is influenced by the complexity of joining two tables and extracting JSON properties. While the query executed swiftly with smaller datasets, it will show a noticeable slowdown as the volume of data increases. Specifically, as the number of transactions (edges) and users (vertices) grew, the time taken to join tables and parse JSON properties increased, leading to longer execution times. Thus, although the query provided valuable insights, its scalability and performance on larger datasets could be a concern.

Performance

Based on the experiments, the performance of implementing graph data in OceanBase was a mixed bag. On the positive side, the adjacency list model was proven possible to implement, and basic graph operations, such as BFS and identifying high-frequency transaction pairs, ran efficiently. With a dataset of 1,000 nodes and 10,000 edges, these queries were executed in less than 200 milliseconds, which is more than satisfactory for a lot of real-world applications.

However, the performance of more complex graph operations, such as DFS, left much to be desired. The execution time of these operations grew exponentially with the depth or complexity of the operation, suggesting that the adjacency list model might not be scalable for large or complex graphs.

In the DFS example, when I set the depth to 5, the query was completed in 428 ms. However, increasing the depth to 6 caused the query time to surge to nearly 4.785 seconds, an approximately 11-fold increase. Further increasing the search depth to 7 resulted in the query taking an overwhelming 67.843 seconds to complete, marking a 14-fold increase from the previous depth. This pattern suggests an exponential growth in query time as the depth increases.

The query time for the DFS algorithm grows exponentially as the depth grows.

This performance degradation can be a significant barrier for real-time or near-real-time applications, where rapid responses are critical. Furthermore, large-scale graphs with thousands or millions of nodes and edges could exacerbate these performance issues, making the queries impractical for use in production databases.

Another performance issue was the overhead of JSON processing. While using JSON fields provided great flexibility in modeling graph data, it also added significant overhead to the database operations. Queries that involved multiple JSON extractions or manipulations were noticeably slower, and this overhead would likely increase with larger and more complex JSON objects.

The inherent limitations of SQL posed significant challenges for performing certain graph operations. For example, graph algorithms that required maintaining state across iterations, such as Dijkstra's algorithm, could not be implemented using SQL alone.

Interestingly, a 2019 study conducted by a team at Alibaba demonstrated the performance of relational databases in implementing graph data against a massive 10-billion-scale graph dataset. The research highlighted the efficiency of PostgreSQL's CTE syntax in implementing graph search requirements such as N-depth search, shortest path search, and controlling and displaying the tier depth.

In the context of a 5 billion point-edge network, N-depth searches and shortest path searches all had response times in “the millisecond range,” according to the Alibaba research. This is particularly notable when conducting a 3-depth search with a limit of 100 records per tier, where the response time was only 2.1 milliseconds, and the Transactions Per Second (TPS) reached 12,000. This impressive performance could serve as a benchmark for the OceanBase team in optimizing their graph data implementation.

Graph data in OceanBase: What’s next?

While OceanBase has made significant strides in providing robust, reliable, and scalable SQL database services, the integration of graph data handling capabilities could further enhance its value proposition. This article has demonstrated that basic graph data handling is indeed possible in OceanBase, but there are areas for improvement.

Here are some suggestions for the OceanBase team based on the discoveries made during this exploration:

Optimize JOIN operations for graph data: Graph queries often involve complex JOIN operations. Optimizing these operations specifically for graph data could significantly improve performance. This could involve creating specialized indexes for graph data or implementing more efficient algorithms for JOIN operations.
Improve JSON parsing performance: JSON parsing can be a significant bottleneck in graph queries. Techniques such as pre-parsing JSON fields, caching parsed results, or implementing more efficient JSON parsing algorithms could help improve performance.
Implement graph-specific indexing: Indexing greatly improves query performance in SQL databases. Creating specialized indexes for graph data, such as indexes on edge properties or composite indexes on (source, target) vertex pairs, could make graph queries run faster.

This exploration into the incorporation of graph data into OceanBase has revealed a promising yet challenging road ahead. The potential to harness the power of graph data within the existing SQL structure offers exciting possibilities for expanding the range of applications and improving data analysis capabilities. However, the performance of complex graph operations and the processing overhead of JSON data present significant hurdles to overcome.

This exploration has only scratched the surface of what's possible, and there's much more to uncover. I hope that this article will stimulate further discussion, contributing to the ongoing evolution of OceanBase and similar databases.

Create a headless CMS using OceanBase and TypeScript: A step-by-step tutorial

Wayne S — Thu, 07 Dec 2023 12:07:41 +0000

If you're planning to start a blog or showcase your products on a website, you have two main options. You could code everything from scratch using HTML, CSS, and JavaScript, creating databases and interfaces to manage your content. This, however, can be challenging if you're not a seasoned programmer. A more efficient alternative is to use a Content Management System (CMS), providing you with the tools to manage your content and design your website effortlessly.

There are numerous CMSs available, each with its strengths. WordPress, the most popular CMS, is known for its user-friendly interface and vast plugin ecosystem. Joomla and Drupal offer more robust platforms for complex websites, though they require some technical expertise. For beginners, Squarespace and Wix are ideal for creating visually attractive websites without needing to code.

However, these CMSs are often tied to the presentation layer, limiting your content's reach to the platform they serve. If you want your content to reach audiences through various channels like mobile apps, smart devices, or voice assistants, traditional CMSs may fall short. The modern web extends beyond websites to include mobile and IoT devices, smartwatches, and in-car entertainment systems.

What is headless CMS?

Enter the world of headless CMS. A headless CMS takes a different approach, separating the content management from the content presentation. Imagine it as a conductor, orchestrating your content delivery to various platforms without being tied to a single instrument.

A headless CMS is a back-end content repository system that facilitates content storage, modification, and delivery via a RESTful API (Application Programming Interface) or GraphQL. It is called 'headless' because it decouples the 'head' (the front-end or the presentation layer) from the 'body' (the back-end or the content repository).

A headless CMS brings several benefits to the table. It gives developers the freedom to use their preferred tech stack for front-end development, fostering unique and tailored user experiences. It supports omnichannel content delivery, a critical factor in today's multi-device world. Yet, it's not without its challenges. The absence of a visual front-end may make it less intuitive for non-technical users.

It also demands a proficient development team to build the client side, and certain features like SEO and preview functionality may require extra effort to implement. Despite these challenges, a headless CMS, with its flexibility and adaptability, stands as a formidable tool in the right hands.

Building a headless CMS

Now that we've navigated the landscape of CMS, it's time to get our hands dirty and dive into the heart of this article: building a headless CMS using TypeScript and OceanBase.

Why this particular tech stack, you might ask?

Let's start with TypeScript. It's a superset of JavaScript that introduces static typing, a feature that JavaScript traditionally lacks. Static typing enhances code reliability, predictability, and maintainability, making TypeScript a powerful part of our stack to build a robust headless CMS.

You might have come across blog posts hailing JavaScript as the "most popular back-end language for web development." But in reality, pure JavaScript is seldom used for back-end development. More often than not, developers lean towards TypeScript, JavaScript’s statically typed sibling. The reason for this preference lies in TypeScript's unique features that enhance code reliability and maintainability, making it a more suitable choice for back-end development.

Next up is OceanBase, my choice of database for most of my projects. As an enterprise-grade, high-performance relational database, OceanBase provides a strong backbone for the headless CMS. Its high scalability and reliability ensure that our content management system can handle vast amounts of data and traffic, making it a reliable choice for large-scale applications.

Finally, I’ve chosen Express.js to build the API server. Express is a flexible Node.js web application framework that provides a robust set of features for web and mobile applications. Its simplicity and flexibility make it an ideal choice for the CMS project, allowing us to build powerful APIs with minimal hassle.

The codebase of the project has been uploaded to this GitHub repo. You can clone and play around with it, or deploy it with GitPod and see it work in action.

Setting up the project

Before we start, ensure that you have the following installed on your machine:

Node.js: This is the JavaScript runtime we'll be using to run our server-side code.
TypeScript: As we discussed earlier, TypeScript is a superset of JavaScript with static type checking.
OceanBase: To use OceanBase in the project, you first need a running OceanBase cluster. You have several options for doing so. You can install OceanBase in your local environment, spin up a virtual machine in the cloud to run it or use OceanBase Cloud in the AWS marketplace to set up your cluster in just a few clicks. After setting up your OceanBase server, don’t forget to obtain the username, password, hostname, and port for later use.

First, let's initialize our project. Open your terminal, navigate to your desired directory, and run the following command to create a new Node.js project:

$ mkdir oceanbase-typescript && cd oceanbase-typescript
$ npm init -y

This will create a new directory for your project and initialize a new Node.js application inside it.

Next, let's install the necessary dependencies. We'll need express for our server, TypeScript for writing our code, and a couple of other packages:

$ npm install express typescript ts-node nodemon

In this project, we are going to use TypeORM as the Object-Relational Mapping (ORM) to connect our project with OceanBase. TypeORM supports a wide variety of databases, including MySQL, Postgresql, and MongoDB. Since TypeORM doesn’t yet have a driver for OceanBase, and OceanBase is compatible with MySQL, we are going to use the MySQL driver for our OceanBase connection.

To use TypeORM and the MySQL driver, we need to install them in our project. Run the following command:

$ npm install typeorm mysql

Now, let's create an src/index.ts file in the root directory of our project. This will be the entry point of our application. Here's a basic setup for our Express server:

import express from 'express';

const app = express();
const port = process.env.PORT || 3000;

app.get('/', (req, res) => {
    res.send('Hello World!');
});

app.listen(port, () => {
    console.log(`Server is running on port ${port}`);
});

To run the server, we'll use ts-node in conjunction with nodemon to ensure our server restarts whenever we make changes. Add a start script to your package.json:

"scripts": {
    "start": "nodemon --watch 'src/**/*.ts' --exec 'ts-node' src/index.ts",
    // ... other scripts
}

Now, you can start your server by running:

npm run start

You should see the console log Server is running on port 3000. Now, make a GET request to the server at http://localhost:3000, you should see "Hello World!" displayed.

That's it! You've now set up a basic TypeScript and express.js project. In the next section, we'll connect to our OceanBase database and start building the CMS functionalities.

Connection to OceanBase

Our CMS will consist of blog posts, tags, and users. Each of these corresponds to an entity in our application. Let's create each of these entities.

Create the following files in the src/entity directory:

User.ts
BlogPost.ts
Tag.ts

Let's start with User.ts. A user in our CMS will have an ID, a name, and a list of blog posts they've authored. Here's how you can define this entity:

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number

    @Column()
    firstName: string

    @Column()
    lastName: string

    @Column()
    age: number

}

Next, we'll define the Tag entity in Tag.ts. A tag will have an ID, a name, and a list of blog posts that have this tag.

import {
    Entity,
    PrimaryGeneratedColumn,
    Column,
    ManyToMany,
} from "typeorm";
import { BlogPost } from "./BlogPost";

@Entity()
export class Tag {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @ManyToMany(() => BlogPost, blogPost => blogPost.tags)
    blogPosts: BlogPost[];
}

Finally, we'll define the BlogPost entity in BlogPost.ts. A blog post will have an ID, a title, a slug (for the URL), the content of the post, a reference to the author, and a list of tags.

import {
    Entity,
    PrimaryGeneratedColumn,
    Column,
    ManyToMany,
    JoinTable
} from "typeorm";
import { Tag } from "./Tag";

@Entity()
export class BlogPost {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    title: string;

    @Column()
    content: string;

    @Column()
    author: string;

    @Column()
    excerpt: string;

    @Column({
        unique: true
    })
    slug: string;

    @Column({
        type: 'date'
    })
    publishDate: Date;

    @Column({
        type: 'enum',
        enum: ['DRAFT', 'PUBLISHED', 'ARCHIVED'],
        default: 'DRAFT'
    })
    status: string;

    @ManyToMany(() => Tag, tag => tag.blogPosts, { 
        cascade: true
    })
    @JoinTable()
    tags: Tag[];
}

Now that we have set up our entities, the next step is to connect our application to the OceanBase database. For this, we will create a DataSource object that will handle the connection.

Let's have a look at the src/data-source.ts file:

import "reflect-metadata"
import { DataSource } from "typeorm"
import { User } from "./entity/User"
import {Tag} from './entity/Tag'
import { BlogPost } from "./entity/BlogPost"

require('dotenv').config()

export const AppDataSource = new DataSource({
    type: "mysql",
    host: process.env.OB_HOST,
    port: Number(process.env.OB_PORT),
    username: process.env.OB_USER,
    password: process.env.OB_PASSWORD,
    database: process.env.OB_DB,
    synchronize: true,
    logging: false,
    entities: [User, Tag, BlogPost],
    migrations: [],
    subscribers: [],
}).initialize()

We first import the necessary modules and entities. Then, we use the dotenv package to load environment variables from a .env file. This is where we'll store our OceanBase connection details.

Next, we create a new DataSource object. This object is configured to connect to a MySQL database, which is compatible with OceanBase. We provide it with the necessary connection details:

host: The host where your OceanBase cluster is running.
port: The port to connect to.
username and password: The credentials to authenticate with the database.
database: The name of the database you want to connect to.

Lastly, we specify the entities that we created earlier. These are the tables that TypeORM will create in our OceanBase database.

Now, wherever we need to interact with the database in our application, we can import this AppDataSource and use it. This way, we ensure that our application is always using the same database connection.

Remember to replace the placeholders in the .env file with your actual OceanBase connection details. The .env file should look something like this:

OB_HOST=your-oceanbase-host
OB_PORT=your-oceanbase-port
OB_USER=your-oceanbase-username
OB_PASSWORD=your-oceanbase-password
OB_DB=your-oceanbase-database

With this setup, our application is now ready to store and retrieve data from OceanBase. In the next section, we'll start building the API endpoints for our CMS.

Setting up CRUD actions for the CMS

The core of our CMS will be the CRUD (Create, Read, Update, Delete) actions. These actions will allow us to manage our content, including creating blog posts, adding tags, and retrieving posts. In this section, we will create several modules to handle these actions.

Add a tag

The first module we will create is addTag.ts, which will allow us to add new tags to our CMS. This module imports the AppDataSource and Tag entity and exports an asynchronous function addTag that takes a tag name as a parameter.

import { AppDataSource } from "../data-source";
import { Tag } from "../entity/Tag";

export async function addTag(name: string) {
    const dataSource = await AppDataSource;
    const tagRepository = dataSource.manager.getRepository(Tag);

    const newTag = new Tag();
    newTag.name = name;

    const result = await tagRepository.save(newTag);

    return result;
}

This function first waits for the AppDataSource to initialize, then fetches the repository for the Tag entity. It creates a new Tag instance, assigns the provided name to it, and saves this new tag to the repository. The function then returns the result of this operation.

Create a post

Next, we'll create createPost.ts, which will allow us to create blog posts. This module is similar to addTag.ts, but it also handles the creation of tags associated with the post.

import { AppDataSource } from "../data-source";
import { BlogPost } from "../entity/BlogPost";
import { Tag } from "../entity/Tag";

export async function createPost(title: string, content: string, author: string, slug: string, publishDate: Date, status: string, tagNames: string[]) {
    const dataSource = await AppDataSource;
    const postRepository = dataSource.manager.getRepository(BlogPost);
    const tagRepository = dataSource.manager.getRepository(Tag);

    const tags = [];
    for (const tagName of tagNames) {
        let tag = await tagRepository.findOne({ where: { name: tagName } });
        if (!tag) {
            tag = new Tag();
            tag.name = tagName;
            tag = await tagRepository.save(tag);
        }
        tags.push(tag);
    }

    const newPost = new BlogPost();
    newPost.title = title;
    newPost.content = content;
    newPost.author = author;
    newPost.slug = slug;
    newPost.publishDate = publishDate;
    newPost.status = status;
    newPost.tags = tags;

    return await postRepository.save(newPost);
}

This function takes several parameters: title, content, author, slug, publishDate, status, and tagNames. It initializes the AppDataSource and fetches the repositories for BlogPost and Tag. It then creates a new Tag for each tag name provided, if it doesn't already exist, and saves it to the Tag repository.

A new BlogPost is then created with the provided parameters and the array of Tag instances. The post is saved to the BlogPost repository and the function returns the result of this operation.

Get all posts

The getAllPosts.ts module allows us to retrieve all blog posts from our CMS. It exports an asynchronous function getAllPosts that fetches and returns all posts from the BlogPost repository.

import { AppDataSource } from "../data-source";
import { BlogPost } from "../entity/BlogPost";

export async function getAllPosts() {
    const dataSource = await AppDataSource;
    return await dataSource.manager.getRepository(BlogPost).find();
}

Similarly, the getAllTags.ts module exports an asynchronous function getAllTags that fetches and returns all tags from the Tag repository.

import { AppDataSource } from "../data-source";
import { Tag } from "../entity/Tag";

export async function getAllTags() {
    const dataSource = await AppDataSource;
    return await dataSource.manager.getRepository(Tag).find();
}

Get a post by slug

The getPostBySlug.ts module exports an asynchronous function getPostBySlug that fetches and returns a post based on its slug.

import { AppDataSource } from "../data-source";
import { BlogPost } from "../entity/BlogPost";

export async function getPostBySlug(slug: string) {
    const dataSource = await AppDataSource;
    return await dataSource.manager.getRepository(BlogPost).findOne({where: {slug}});
}

Get all posts by tag

The getPostsByTag.ts module exports an asynchronous function getPostsByTag that fetches and returns all posts associated with a specific tag.

import { AppDataSource } from "../data-source";
import { BlogPost } from "../entity/BlogPost";
import { Tag } from "../entity/Tag";

export async function getPostsByTag(tagName: string) {
    const dataSource = await AppDataSource;
    const tagRepository = dataSource.manager.getRepository(Tag);
    const tag = await tagRepository.findOne({ where: { name: tagName } });

    if (!tag) {
        throw new Error(`No tag found with the name ${tagName}`);
    }

    return await dataSource.manager.getRepository(BlogPost).find({ where: { tags: tag } });
}

This function takes a tagName as a parameter. It initializes the AppDataSource and fetches the repository for Tag. It then searches for a tag with the provided name. If no such tag is found, an error is thrown. If the tag is found, the function fetches and returns all BlogPost entities associated with this tag from the BlogPost repository.

With these files, we have set up the basic CRUD operations for our CMS. These operations allow us to create, retrieve, and manage blog posts and tags in our CMS, utilizing the power of TypeScript and OceanBase.

Setting up the API server in Express

The Express.js framework will handle the HTTP requests and responses for our CMS. In the src/index.ts file, we start by importing the necessary modules and functions:

import express, { Request, Response , NextFunction} from 'express';
import { AppDataSource } from './data-source';
import { addTag } from './modules/addTag';
import { createPost } from './modules/createPost';
import { getAllPosts } from './modules/getAllPosts';
import { getAllTags } from './modules/getAllTags';
import { getPostBySlug } from './modules/getPostBySlug';
import { getPostsByTag } from './modules/getPostsByTag';

Next, we create an Express application and set the port to 3000. We also use the express.json() middleware to parse incoming JSON payloads:

const app = express();
const port = 3000;

app.use(express.json());

We then define several routes for our CMS:

GET /posts: This route fetches all blog posts from the CMS and sends them in the HTTP response.

app.get('/posts', async (req: Request, res: Response) => {
    const posts = await getAllPosts();
    res.json(posts);
});

POST /posts: This route creates a new blog post. It expects the post details to be provided in the HTTP request body.

app.post('/posts', async (req: Request, res: Response, next: NextFunction) => {
    try {
        const { title, content, author, slug, publishDate, status, tagNames } = req.body;
        await createPost(title, content, author, slug, publishDate, status, tagNames);
        res.sendStatus(201);
    } catch (error) {
        next(error);
    }
});

POST /tags: This route creates a new tag. It expects the tag name to be provided in the HTTP request body.

app.post('/tags', async (req: Request, res: Response, next: NextFunction) => {
    try {
        const { name } = req.body;
        await addTag(name);
        res.sendStatus(201);
    } catch (error) {
        next(error);
    }
});

GET /tags: This route fetches all tags from the CMS and sends them in the HTTP response.

app.get('/tags', async (req: Request, res: Response, next: NextFunction) => {
    try {
        const tags = await getAllTags();
        res.json(tags);
    } catch (error) {
        next(error);
    }
});

GET /posts/:slug: This route fetches a blog post based on its slug and sends it in the HTTP response.

app.get('/posts/:slug', async (req: Request, res: Response, next: NextFunction) => {
    try {
        const { slug } = req.params;
        const post = await getPostBySlug(slug);
        if (post) {
            res.json(post);
        } else {
            res.sendStatus(404);
        }
    } catch (error) {
        next(error);
    }
});

GET /posts/tag/:tagName: This route fetches all blog posts associated with a specific tag and sends them in the HTTP response.

app.get('/posts/tag/:tagName', async (req: Request, res: Response, next: NextFunction) => {
    try {
        const { tagName } = req.params;
        const posts = await getPostsByTag(tagName);
        res.json(posts);
    } catch (error) {
        next(error);
    }
});

We also add an error handling middleware to catch any errors that might occur during the handling of requests:

app.use((error: Error, req: Request, res: Response, next: NextFunction) => {
    console.error(error);
    res.status(500).json({ error: 'Internal server error' });
});

Finally, we start the Express server and add event listeners to handle graceful shutdowns:

app.listen(port, () => {
    console.log(`Server is running on port ${port}`);
});

process.on('SIGINT', async () => {
    const conn = await AppDataSource;
    await conn.close();
    console.log('Database connection closed');
    process.exit(0);
});

process.on('unhandledRejection', (reason, promise) => {
    console.log('Unhandled Rejection at:', promise, 'reason:', reason);
    process.exit(1);
});

That's it! With these steps, we have set up the API server for our CMS using Express.js, TypeScript, and OceanBase.

Testing the API

Now that we have our API server all set up, it's time to test it out. We will use the VS Code plugin Thunder Client to make HTTP requests to our server and check its responses.

Type the following command to start the server.

npm run start

Let's start by testing the POST /tags endpoint that we created to add new tags to our CMS. Open Postman and create a new request. Set the request type to POST and the URL to http://localhost:3000/tags. In the request body, select raw and JSON and input a new tag name as follows:

{
    "name": "Technology"
}

Click on "Send". If everything is working correctly, you should receive a 201 Created status code.

Next, let's test the POST /posts endpoint. Create a new POST request to http://localhost:3000/posts. In the request body, input a new blog post with title, content, author, slug, publish date, status, and an array of tag names:

{
    "title": "The Future of CMS",
    "content": "This is a blog post about the future of CMS.",
    "author": "John Doe",
    "slug": "the-future-of-cms",
    "publishDate": "2023-12-01",
    "status": "PUBLISHED",
    "tagNames": ["Technology", "CMS"]
}

Click on "Send". You should receive a 201 Created status code.

Now, let's test the GET /posts endpoint. Create a new GET request to http://localhost:3000/posts and click on "Send". You should receive a 200 OK status code and a list of all blog posts in the response body.

Similarly, test the GET /tags endpoint by creating a new GET request to http://localhost:3000/tags. You should receive a 200 OK status code and a list of all tags in the response body.

To test the GET /posts/tag/:tagName endpoint, create a new GET request to http://localhost:3000/posts/tag/Technology. You should receive a 200 OK status code and a list of all blog posts associated with the tag "Technology".

Finally, to test the GET /posts/:slug endpoint, create a new GET request to http://localhost:3000/posts/the-future-of-ai. You should receive a 200 OK status code and the details of the blog post with the slug "the-future-of-ai".

Congratulations! If all your requests returned the expected responses, your API server is working as expected. You've successfully built a headless CMS using TypeScript and OceanBase and equipped it with a robust API server using Express.js.

Conclusion

Building a headless CMS might seem like a daunting task, but as we've seen in this tutorial, with the right tools and a solid structure, it's entirely achievable. Using TypeScript and OceanBase, we've created a flexible and scalable CMS that can serve content on a variety of platforms.

OceanBase, a distributed, high-performance relational database, served as the backbone of our CMS. Its high scalability and compatibility with MySQL made it an excellent choice for projects like this. OceanBase not only handled vast amounts of data efficiently but also provided reliability, ensuring that our CMS can handle large-scale applications.

TypeScript's static typing enhanced our code's reliability, predictability, and maintainability, making it a powerful ally in our quest to build a robust headless CMS. Express.js, a lightweight and flexible Node.js web application framework, enabled us to build a sleek API server with minimal hassle.

This guide served as a demonstration of how you can use OceanBase in a TypeScript project. It's a testament to OceanBase's versatility and robustness, proving that it can be an excellent choice for a wide range of applications, from small projects to large-scale enterprise solutions.

Remember, the journey doesn't end here. There's a plethora of additional features and enhancements you can add to your CMS. From incorporating user authentication to adding a front end to interact with your CMS, the possibilities are endless. With OceanBase as the backbone of your project, you can be confident that your CMS will be able to handle whatever you throw at it.

Again, you can find the source code of the project on this GitHub repo. The repo can also be a starting point for any other TypeScript/OceanBase projects in your mind.

A beginner’s guide to sentiment analysis using OceanBase and spaCy

Wayne S — Wed, 25 Oct 2023 08:50:01 +0000

In the modern commercial world, understanding your customer's sentiments is crucial. Businesses are progressively recognizing the importance of this aspect, as it allows them to gauge customer satisfaction, measure product reception, and understand market trends. This is where the power of Natural Language Processing (NLP) comes in, specifically through sentiment analysis.

Sentiment analysis, or opinion mining, uses NLP, text analysis, and computational linguistics to identify and extract subjective information from source materials. It's a method used to determine the attitudes, opinions, and emotions of a speaker or writer concerning some topic or the overall contextual polarity of a document.

In this article, I'm going to walk through a sentiment analysis project from start to finish, using open-source Amazon product reviews. However, using the same approach, you can easily implement mass sentiment analysis on your own products. We'll explore an approach to sentiment analysis with one of the most popular Python NLP packages: spaCy.

For the core database of my sentiment analysis projects, I'll be using OceanBase. OceanBase is a reliable and high-performance relational database that is well-suited for data scientists working on NLP projects. Its high scalability means it can handle the large amounts of data that are often a part of NLP projects.

Additionally, its capacity for high concurrency allows for multiple processes to be executed simultaneously, a beneficial feature when dealing with the complex computations required in NLP. Lastly, OceanBase's strong consistency ensures the integrity and accuracy of the data, a critical factor in any data analysis.

Setting up the environment

In this project, we’ll use Google Colab to run the sentiment analysis. I have included a Colab notebook that contains all the code and results of this project.

We start by importing the necessary Python libraries, including pandas for data manipulation, numpy for numerical computations, matplotlib and seaborn for data visualization, spaCy for natural language processing, and TextBlob for sentiment analysis.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import spacy
from textblob import TextBlob

plt.style.use('ggplot')

To use OceanBase in the project, you first need a running OceanBase cluster. You have several options for doing so. You can install OceanBase in your local environment, spin up a virtual machine in the cloud to run it, or use OceanBase Cloud in the AWS marketplace to set up your cluster in just a few clicks. After setting up your OceanBase server, don’t forget to obtain the username, password, hostname, and port for later use.

Next, we set up our connection to OceanBase, a distributed relational database that is highly available and scalable. We use SQLAlchemy to create a connection with the database.

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://username:password@hostname:2881/db')

We then create a table in OceanBase to store the reviews. The table includes columns for the product ID, user ID, profile name, helpfulness numerator and denominator, score, time, summary, and text of the review.

Explore the raw data

The raw data we are going to analyze comes from Kaggle, which is an online community of data scientists and machine learning practitioners. The open dataset consists of reviews of fine foods from Amazon. The data span a period of more than 10 years, including around 500,000 reviews up to October 2012. Reviews include product and user information, ratings, and a plain text review.

After loading our data into a data frame, we explore it by checking its shape and the first few rows. We also visualize the distribution of review scores.

df = pd.read_csv('path/to/file.csv')
print(df.shape)

And we see the following results

(568454, 10)

We can see that the original dataset contains nearly half a million reviews. A quick df.head() command will give us a preview of the dataset

Next, we visualize the distribution of review scores. This gives us an overview of the reviews. For instance, if most reviews have a score of 5, we can infer that customers are generally satisfied with the products.

ax = df['Score'].value_counts().sort_index() \
    .plot(kind='bar',
          title='Count of Reviews by Stars',
          figsize=(10, 5))
ax.set_xlabel('Review Stars')
plt.show()

Now we can upload the data frame, which contains all the reviews to the Reviews table in our OceanBase database.

# Upload the data frame to the OceanBase table
df.to_sql('Reviews', con=engine, if_exists='append', index=False)

Setting up sentiment analysis functions

Now, let's move on to the core part of our project - sentiment analysis. We start by loading the English model from spaCy.

# Load the nlp model
nlp = spacy.load('en_core_web_sm')

We define a function get_sentiment to calculate the sentiment polarity and subjectivity of a text. The polarity score ranges from -1 to 1 (where 1 is positive sentiment, -1 is negative sentiment, and 0 is neutral sentiment), and the subjectivity score ranges from 0 to 1 (where 0 is objective and 1 is subjective).

def get_sentiment(text):
    """This function receives a text and returns its sentiment polarity and subjectivity."""
    doc = nlp(text)
    sentiment = TextBlob(text).sentiment
    return sentiment

We test our function on a sample review from our dataset to make sure it's working as expected. Then, we apply this function to all reviews in our dataset and add new columns for polarity, subjectivity, and sentiment.

# Test the function on an example review
example = df['Text'][30]
print(example)
# I have never been a huge coffee fan. However, my mother purchased this little machine and talked me into trying the Latte Macciato. No Coffee Shop has a better one and I like most of the other products, too (as a usually non-coffee drinker!).<br />The little Dolche Guesto Machine is super easy to use and prepares a really good Coffee/Latte/Cappuccino/etc in less than a minute (if water is heated up). I would recommend the Dolce Gusto to anyone. Too good for the price and I'am getting one myself! :)

This gives us the following result:

get_sentiment(example)
# Sentiment(polarity=0.2557692307692308, subjectivity=0.5608974358974359)

This means that the sentiment of this review is positive and has a subjectivity score of around 0.56. Which is pretty accurate by telling from our human instincts.

In sentiment analysis, polarity determines the emotional state expressed in the text. The polarity score ranges from -1 to 1. A score closer to 1 means a higher degree of positivity, and a score closer to -1 means a higher degree of negativity.

Subjectivity, on the other hand, determines if the text is objective or subjective. The subjectivity score ranges from 0 to 1. A score closer to 0 is more objective, and a score closer to 1 is more subjective.

With the sentiment analysis function ready, we can run sentiment analysis against every row in our OceanBase database and store their sentiment and subjectivity in separate columns for future uses.

#### Run sentiment analysis in the reviews table
connection = engine.connect()

# Read the Reviews table into DataFrame
df = pd.read_sql_table('Reviews', connection)

# Apply the sentiment analysis function to the 'Text' column
df['Polarity'], df['Subjectivity'] = zip(*df['Text'].map(get_sentiment))
df['Sentiment'] = df['Polarity'].apply(lambda polarity : 'Positive' if polarity > 0 else 'Negative' if polarity < 0 else 'Neutral')
connection.close()

Now let’s take a look at the data frame

df.head()

We now have 3 new columns that represent the polarity, subjectivity, and sentiment of each review.

To preserve the original raw data, I will store the new data frame in a separate table in OceanBase

# Write the DataFrame back to OceanBase and store it in the 'Sentiment' table
connection = engine.connect()
df.to_sql('Sentiment', con=engine, if_exists='replace', index=False)
connection.close()

Sentiment analysis of the data

In this section, I will try to go through some of the most popular sentiment analysis paradigms of product reviews using data stored in our OceanBase database, then we will try to do some sophisticated analyses to demonstrate the power of NLP and how it can be used in business intelligence.

Sentiment distribution - an overview

First, we read the Sentiment table into a data frame and separate the data based on sentiment. We then visualize the score distribution for each sentiment using histograms. This allows us to see if there is a relationship between the sentiment of a review and the score it gives.

# Count the number of positive, negative and neutral reviews
sentiment_counts = df['Sentiment'].value_counts()
print(sentiment_counts)

# Visualize the sentiment distribution
plt.figure(figsize=(10,5))
sns.barplot(x=sentiment_counts.index, y=sentiment_counts.values, alpha=0.8)
plt.title('Sentiment Distribution')
plt.ylabel('Number of Occurrences', fontsize=12)
plt.xlabel('Sentiment', fontsize=12)
plt.show()

#### We are expected to see the following results:
# Positive    881
# Negative     98
# Neutral      21
# Name: Sentiment, dtype: int64

From this overview, we can tell that most of the reviews are positive.

Understanding Score Distribution by Sentiment

Now let’s try to visualize the score distribution for positive, negative, and neutral reviews. It separates reviews based on their sentiment and plots a histogram for each sentiment to show the distribution of scores.

This analysis can provide valuable insights for businesses. For example, if a business notices that the majority of positive reviews are scoring a 4 instead of a 5, they might investigate what is preventing customers from giving a perfect score.

# Separate the data based on sentiment
positive_reviews = df[df['Sentiment'] == 'Positive']
negative_reviews = df[df['Sentiment'] == 'Negative']
neutral_reviews = df[df['Sentiment'] == 'Neutral'] # Assuming you have neutral reviews

# Plotting
fig, axs = plt.subplots(3, figsize=(10, 15))

# Positive reviews
axs[0].hist(positive_reviews['Score'], bins=5, color='green', edgecolor='black')
axs[0].set_title('Score Distribution for Positive Reviews')
axs[0].set_xlabel('Score')
axs[0].set_ylabel('Frequency')

# Negative reviews
axs[1].hist(negative_reviews['Score'], bins=5, color='red', edgecolor='black')
axs[1].set_title('Score Distribution for Negative Reviews')
axs[1].set_xlabel('Score')
axs[1].set_ylabel('Frequency')

# Neutral reviews
axs[2].hist(neutral_reviews['Score'], bins=5, color='blue', edgecolor='black')
axs[2].set_title('Score Distribution for Neutral Reviews')
axs[2].set_xlabel('Score')
axs[2].set_ylabel('Frequency')

plt.tight_layout()
plt.show()

Boxplot of polarity by sentiment

We use a boxplot to visualize the distribution of polarity scores for each sentiment. This helps us understand the spread and skewness of the polarity scores. If the boxplot for a sentiment is skewed, it means that the reviews of that sentiment tend to be more positive or negative.

# Boxplot of Polarity by Sentiment
plt.figure(figsize=(10,5))
sns.boxplot(x='Sentiment', y='Polarity', data=df)
plt.title('Boxplot of Polarity by Sentiment')
plt.show()

Time Series Analysis

We analyze how sentiments and scores have evolved over time by converting the 'Time' column into a readable format and plotting a stacked bar plot of sentiment over time. This can help us identify trends and patterns in the sentiment of reviews over time.

# Time Series Analysis
# Convert 'Time' to datetime and extract the year
df['Time'] = pd.to_datetime(df['Time'], unit='s')
df['Year'] = df['Time'].dt.year

plt.figure(figsize=(10,5))
df.groupby(['Year', 'Sentiment']).size().unstack().plot(kind='bar', stacked=True)
plt.title('Sentiment Over Time')
plt.show()

Correlation Matrix

We can use a correlation matrix to understand the relationships between different numerical variables. This can help us identify if certain variables are associated with one another.

# Correlation Matrix
plt.figure(figsize=(10,5))
sns.heatmap(df[['HelpfulnessNumerator', 'HelpfulnessDenominator', 'Score', 'Polarity', 'Subjectivity']].corr(), annot=True, cmap='coolwarm')
plt.title('Correlation Matrix')
plt.show()

Word cloud of positive and negative reviews

Now, we can generate a word cloud for each sentiment to give us a visual representation of the most common words in positive, and negative reviews. This can help us understand the common themes in each sentiment.

from wordcloud import WordCloud

# Combine all reviews for the desired sentiment, remove 'br' (line breaks) from the text
positive_text = ' '.join(df[df['Sentiment']=='Positive']['Text'].tolist()).replace('br', '')
negative_text = ' '.join(df[df['Sentiment']=='Negative']['Text'].tolist()).replace('br', '')

# Generate word clouds
wordcloud_positive = WordCloud(background_color='white').generate(positive_text)
wordcloud_negative = WordCloud(background_color='white').generate(negative_text)

# Plot word clouds
plt.figure(figsize=(10, 5))

plt.subplot(1, 2, 1)
plt.imshow(wordcloud_positive, interpolation='bilinear')
plt.axis('off')
plt.title('Word Cloud of Positive Sentiment')

plt.subplot(1, 2, 2)
plt.imshow(wordcloud_negative, interpolation='bilinear')
plt.axis('off')
plt.title('Word Cloud of Negative Sentiment')

plt.show()

Like the opening of Leo Tolstoy's novel Anna Karenina: “Happy families are all alike; every unhappy family is unhappy in its own way,” there isn’t much to analyze in positive reviews, but in negative reviews, we can easily find out people were complaining about the taste and packing of the products. Remember, this dataset focuses on food reviews on Amazon.

Sentiment by Day of Week

Sometimes, people give reviews based on their mood. And people usually have better moods on weekends than on Mondays. We can analyze if there is any pattern in sentiment based on the day of the week the review was posted. This can help businesses understand if there are any temporal trends in customer sentiment.

# Sentiment by Day of Week

df['DayOfWeek'] = df['Time'].dt.dayofweek

df.groupby(['DayOfWeek', 'Sentiment']).size().unstack().plot(kind='bar', stacked=True)
plt.title('Sentiment by Day of Week')
plt.xlabel('Day of Week (0=Monday, 6=Sunday)')
plt.ylabel('Number of Reviews')
plt.show()

Well, it turned out the result somehow doesn’t reflect our hypothesis. But you can try it on your own dataset, and perhaps you will find something interesting.

Length of Review vs. Helpfulness

Now let’s try to see if there's a correlation between the length of the review and its helpfulness. This can provide insights into how much detail customers find useful in reviews.

# Calculate review length and filter the DataFrame
df['ReviewLength'] = df['Text'].apply(len)
df_filtered = df[(df['ReviewLength'] > 1) & (df['ReviewLength'] <= 2000)]

# Length of Review vs. Helpfulness
plt.figure(figsize=(10,5))
sns.scatterplot(x='ReviewLength', y='HelpfulnessNumerator', hue='Sentiment', data=df_filtered)
plt.title('Length of Review vs. Helpfulness')
plt.xlabel('Review Length')
plt.ylabel('Helpfulness')
plt.show()

It turned out that lengthy reviews are not necessarily more helpful according to other users’ feedback. But some extremely long reviews, like this one with around 1,600 characters, did receive the highest score of helpfulness.

Correlation between Subjectivity and Score

We can also visualize the correlation between the subjectivity of a review and the score it gives. This can help us understand if more subjective or objective reviews tend to give higher or lower scores.

# Correlation between Subjectivity and Score
plt.figure(figsize=(10,5))
sns.scatterplot(x='Subjectivity', y='Score', hue='Sentiment', data=df)
plt.title('Correlation between Subjectivity and Score')
plt.xlabel('Subjectivity')
plt.ylabel('Score')
plt.show()

Find out the most beloved products

Identifying the most loved products is an essential aspect of running a successful e-commerce business. It provides sellers with valuable insights into customer preferences and product performance, which can be used to drive strategic decisions and business growth.

Understanding which products are most loved can help e-commerce sellers allocate their resources more effectively. This includes decisions about inventory management, marketing budget allocation, and even product development.

Here’s how you can do it with our Sentiment dataset stored in OceanBase:

from IPython.display import display

# Create a new DataFrame to count the number of reviews and average scores
grouped = df.groupby('ProductId').agg({'Score': ['count', 'mean']})

# Create a new DataFrame to count the number of positive reviews
positive_reviews = df[df['Sentiment'] == 'Positive'].groupby('ProductId').count()

# Add the positive reviews count to the grouped DataFrame
grouped['PositiveReviewsCount'] = positive_reviews['Sentiment']

# Sort the values by count and mean score both in descending order to get most reviewed and highest scored products.
grouped.sort_values(by=[('Score', 'count'), ('Score', 'mean'), 'PositiveReviewsCount'], ascending=[False, False, False], inplace=True)

# Take the top 10
top_10 = grouped.head(10)

# Use Styler to add some background gradient
styled = top_10.style.background_gradient()

# Display the styled DataFrame
display(styled)

And we get this chart which gives us the top 10 “most beloved” products depending on their number of positive reviews and overall score.

The performance of a product is not just about sales numbers; customer reviews and sentiments are a significant part of this equation. A product might be a best-seller, but if the majority of reviews are negative, it might indicate a problem with the product that needs to be addressed. In this data set, we can easily find that product “B000HDMUQ2" is popular but received a mediocre review.

Conclusion

Sentiment analysis is a powerful tool that can provide profound insights into customer preferences and behaviors. By analyzing these Amazon reviews, we can understand not only the polarity of sentiments but also the subjectivity of the reviews. This analysis can shed light on the emotional state and personal biases of the reviewers.

While this particular sentiment analysis project focused on Amazon reviews as an example, the methodologies and tools employed here can be applied to much more complex and diverse datasets. For instance, you could analyze social media posts to understand public sentiment about a particular topic or event, customer support transcripts to discover common issues and pain points, or even news articles to identify trends and biases in media coverage.

And of course, having a robust, scalable, and reliable database is essential for any data scientist working on large-scale projects. The ability to handle vast amounts of data efficiently and conduct complex computations quickly is crucial for timely and accurate analysis. I think this is where OceanBase shines.

Sentiment analysis using NLP and OceanBase can provide data scientists with invaluable insights that can guide their strategies, improve their services, and ultimately increase customer satisfaction.

By harnessing these tools, data scientists can gain a deeper understanding of the sentiments in unstructured text data, enabling them to obtain insights that will lead to better decisions and improved products and services.

I've included all the code and results from this sentiment analysis project in a Google Colab notebook. It's a comprehensive resource that walks you through each step of the analysis, from setting up the environment to visualizing the results.

I encourage you to copy the notebook and experiment with it. Feel free to modify the code, try different parameters, or even apply the methodology to your own datasets. It's a great way to learn more about sentiment analysis, natural language processing, and the capabilities of OceanBase. You're welcome to use it as a starting point for your own data analysis projects.

Using OceanBase to build a real-time user analytics pipeline

Wayne S — Wed, 06 Sep 2023 07:24:13 +0000

In the digital era we live in, data is everywhere - it's collected from sources like web and mobile applications, IoT devices, social media interactions, and CRM systems. As a SaaS product owner myself, I've seen how this vast array of data sources can become overwhelming when it comes to user behavior analysis and visualization. The challenge is not just in collecting the data, but also in managing and making sense of it.

A solution I've found effective is setting up a centralized data processing pipeline. It involves collecting user data from various sources, storing it in a single database, and then analyzing and visualizing this data in real time to provide insights into the product and how users are using the product. It's like having a one-stop shop for all your data needs, which can be extremely useful for decision-making processes.

If you've been following my journey, you'll know that I've been exploring OceanBase and integrating it into various projects. But my choice wasn't just about familiarity. OceanBase is genuinely impressive when it comes to handling real-time analysis and large volumes of data. It's not just about the fact that it can cope with large volumes of data, but it does so efficiently, without compromising on performance.

In the following sections, I'll walk you through how I used OceanBase, along with Jitsu and Metabase, to build a user events data analytics pipeline. It's an interesting journey that I hope will inspire you to explore these tools and the possibilities they offer.

Tools used in this project

In this project, I utilized three key tools: OceanBase, Jitsu, and Metabase. Each of these tools plays a critical role in the user analytics pipeline for data collection, storage, and analysis.

OceanBase is the backbone of this project, serving as the core database for storing user events. It's a distributed SQL database known for its scalability and efficiency in handling large volumes of data, making it an ideal choice for the database in a real-time analytics pipeline.

Jitsu, an open-source data collection platform, is used to capture user events across various platforms, including web and mobile apps. It collects data and sends it directly to OceanBase. On the other end, Metabase, an open-source business intelligence tool, is used to visualize and analyze the data stored in OceanBase. It provides a user-friendly interface for exploring data and generating insights. Together, these tools create a comprehensive pipeline for real-time user analytics.

Here is the diagram for the project.

For this project, I used Gitpod to streamline the setup of the development environment, which included OceanBase, Jitsu, Metabase, and a SvelteKit demo app. The demo app mimics a real-world web application, allowing me to generate user interactions and send data to the pipeline, demonstrating how the system processes real-time data.

The entire codebase for this project is available on GitHub, allowing anyone interested in getting their hands dirty. You can directly open this project in Gitpod and experiment with the pipeline yourself. This hands-on approach provides a practical understanding of how each component interacts within the pipeline, and the opportunity to explore the capabilities of these tools in a real-world context.

Setting up the GitPod environment

As mentioned earlier, we will be using GitPod to set up the development environment for the project. For those who might not be familiar, GitPod allows you to define your development environment's configuration using a .gitpod.yml file. This file is written in YAML and is located at the root of your project's repository. It specifies everything GitPod needs to prepare your development environment.

Our .gitpod.yml file includes the setup for OceanBase, Jitsu, Metabase, and a network that connects these services. Let's break down each service setup:

Initialize the image

Before we can use the services used in the pipeline, we first need to set up an image for our GitPod environment.

image:
  file: .gitpod.Dockerfile

The .gitpod.Dockerfile is a configuration file that Gitpod uses to build a custom Docker image for your workspace. It installs the Docker engine, Docker CLI, and the containerd service.

FROM gitpod/workspace-full
USER gitpod
RUN sudo apt-get update && sudo apt-get install -y \
    apt-transport-https \
    ca-certificates \
    curl \
    gnupg-agent \
    software-properties-common
# Install Docker
RUN curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
RUN sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
RUN sudo apt-get update
RUN sudo apt-get install -y docker-ce docker-ce-cli containerd.io
# Allow the gitpod user to execute docker commands
RUN sudo usermod -aG docker gitpod

Metabase setup

Within the tasks section of our .gitpod.yml file, we set up Metabase using Docker. We instruct Docker to run a Metabase instance, binding it to port 4000 and attaching it to our custom network, pipeline_network.

tasks:
  - init: docker network create pipeline_network
  - init: echo 'Setting up Metabase...'
    command: docker run -d -p 4000:3000 --name metabase --network=pipeline_network metabase/metabase

Jitsu setup

Next, we set up Jitsu by running a bash script, jitsu-setup.sh, during the initialization phase. Once the setup script completes, Docker Compose is instructed to start the Jitsu service using the specifications in jitsu-docker-compose.yml.

- init: bash jitsu-setup.sh
    command: docker compose -f jitsu-docker-compose.yml up

OceanBase setup

For OceanBase, we first pull the OceanBase image from Docker Hub. Once the image is available locally, we start a new OceanBase container, attach it to our network, and expose port 2881. A bash script, oceanbase-gitpod-init.sh, is then run to complete the setup.

- name: OceanBase
    init: docker pull oceanbase/oceanbase-ce
    command: |
      echo "alias sh='/bin/bash'" >> ~/.profile
      source ~/.profile
      docker run --network=pipeline_network -p 2881:2881 --name obstandalone -e MINI_MODE=1 -d oceanbase/oceanbase-ce
      bash oceanbase-gitpod-init.sh

Ports Configuration

Finally, we define the ports that the services expose. The Jitsu web server is publicly visible on port 3216. The Metabase server and OceanBase database, however, are exposed on ports 4000 and 2881, respectively, but are only visible within the private network.

ports:
    - name: Jitsu Server
      description: The Jitsu web server
      port: 3216
      visibility: public

    - name: Metabase Server
      port: 4000
      visibility: private

    - name: OceanBase Database
      port: 2881
      visibility: private

This configuration enables us to have a ready-to-code environment right when we open the project in GitPod, saving us the hassle of manual setup and configuration.

Running the GitPod environment

Once you have your project codebase hosted on GitHub and your Gitpod configuration files (/.gitpod.yml and /.gitpod.Dockerfile) set up in the root directory of your repository, you're ready to launch your Gitpod environment.

To open your project in GitPod, you can use a special URL format that GitPod recognizes. Simply prepend https://gitpod.io/# to the URL of your GitHub repository. For example, if your GitHub repository is at https://github.com/username/repo, you would open it in Gitpod by navigating to https://gitpod.io/#<https://github.com/username/repo in your browser.

Upon accessing this URL, Gitpod will start a new workspace and prepare it according to your .gitpod.yml and .gitpod.Dockerfile files. This includes setting up OceanBase, Jitsu, and Metabase, and launching the SvelteKit demo app.

Once the workspace is ready, you will see a coding environment in your browser with a terminal running. From here, you can start interacting with your project as if it were running locally on your machine.

Once you're inside the Gitpod workspace, you'll notice that all the services as per your configuration are up and running. You can confirm this by checking the terminal output and observing the processes in action.

These services, now running smoothly in your Gitpod workspace, are ready to handle real-time data processing and visualization, allowing you to dive right into the project.

Setting up Jitsu and Metabase

Once your Gitpod environment is up and running, the next step is to configure Jitsu and Metabase to connect with OceanBase.

Jitsu Setup

In Jitsu, the first thing you need to do is to add a new data destination. Since OceanBase is compatible with MySQL, you can select the MySQL database type from the list. As Jitsu and OceanBase are running in the same network within your Gitpod workspace, you can use localhost or 127.0.0.1 as the hostname. The port to connect to OceanBase is 2881.

In Jitsu, you can also add a website that sends the data. Remember to store the write keys for both frontend and backend.

Metabase Setup

Setting up Metabase is a similar process. Metabase runs on port 4000 in the Gitpod workspace. You need to connect OceanBase as the data source in Metabase. Like with Jitsu, you can use localhost or 127.0.0.1 as the hostname to connect to OceanBase.

By setting up Jitsu and Metabase in this way, you create a pipeline where user events are captured by Jitsu, stored in OceanBase, and visualized and analyzed in Metabase. This setup forms the basis of our real-time user analytics pipeline.

The next step will be using Jitsu’s frontend SDK to capture events.

Capturing events in a demo app

The next critical step in our user analytics pipeline is capturing user events. For this, we'll use Jitsu’s frontend SDK in combination with a SvelteKit demo app.

Setting Up the demo app

To start, we'll create an app folder in the root directory of our project. This folder will hold our SvelteKit demo app. We'll clone an open-source SvelteKit app into this folder using the following command:

git clone https://github.com/tsukhu/sveltekit-demo.git .

Once the app is cloned, we need to install its dependencies. In the terminal, navigate to the app directory and run npm install. This command will install all the necessary packages for the SvelteKit app.

With all the packages installed, we can now start our development server. Run npm run dev in the terminal. This command will start the SvelteKit app, and you can access it directly in your Gitpod workspace.

This demo SvelteKit app contains a user authentication module and several demo functionalities, including logging in with Google, a Todo list app, and a counter app. It can be used to mimic what’s happening in a real-world app: Users register or log in to an app, and use the functionalities of the app.

With the SvelteKit demo app up and running, we can now integrate Jitsu’s frontend SDK to capture user events. This setup enables us to mimic real-world user interactions and test our user analytics pipeline.

Using the Jitsu SDK in the demo app

The Jitsu SDK, available as the @jitsu/js NPM package, enables you to track events directly from your JavaScript code. It's isomorphic, meaning it can be used in both browser and Node.js environments.

To install the Jitsu SDK, you'll need to run the following command in your terminal:

npm install @jitsu/js

This command adds the @jitsu/js package to your project, making the Jitsu analytics functions available for use.

Now we can go back to our SvelteKit code to add the event tracking code. First, under the /src/action folders folder, we can add a jitsu.ts file with the following code.

import { jitsuAnalytics } from "@jitsu/js";
export async function track(userEmail:string, event: string) {
    const analytics = jitsuAnalytics({
        host: "https://3216-wayneshn-oceanbasepipel-yr7spzlvu3y.ws-eu104.gitpod.io",
        // Browser Write Key configured on Jitsu Site entity.
        // If no Browser Write Key is added for Site entity, Site ID value can be used a Write Key.
        writeKey: "YOUR_JITSU_WRITE_KEY_FRONTEND",
    });
    await analytics.track(event, {userEmail: userEmail})
}

This code essentially imports the Jitsu library and exports a function that can send the user email and the event type to the Jitsu server we just set up.

Remember to replace "YOUR_JITSU_WRITE_KEY_FRONTEND" with your actual Jitsu write key.

With this setup, you can now use the track function throughout your SvelteKit app to send user events to your Jitsu server.

Use the track function

Once you have the track function set up, you can use it to capture various user events throughout your application. For instance, let's consider tracking user sign-in events.

In your src/Auth.ts file, you can integrate the track function as follows:

import { env } from '$env/dynamic/public';
import { browser } from '$app/environment';
import { getApps, getApp, initializeApp } from '@firebase/app';
import { getAuth, GoogleAuthProvider, signInWithPopup } from '@firebase/auth';
import type { FirebaseOptions } from '@firebase/app';
import type { Auth, UserCredential } from '@firebase/auth';
import { track } from './actions/jitsu';

const config: FirebaseOptions = {
    apiKey: env.PUBLIC_FIREBASE_API_KEY as string,
    authDomain: env.PUBLIC_FIREBASE_AUTH_DOMAIN as string,
    projectId: env.PUBLIC_FIREBASE_PROJECT_ID as string,
    storageBucket: env.PUBLIC_FIREBASE_STORAGE_BUCKET as string,
    messagingSenderId: env.PUBLIC_FIREBASE_MESSAGING_SENDER_ID as string,
    appId: env.PUBLIC_FIREBASE_APP_ID as string
};

if (browser && !getApps().length) {
    initializeApp(config);
}

export const getAppAuth = (): Auth => {
    return getAuth(getApp());
};
export const googleProvider = new GoogleAuthProvider();

export const signIn = (): Promise<UserCredential | void> => {
    if (browser) {
        const auth = signInWithPopup(getAppAuth(), googleProvider);
        auth.then(data=> {
            track(data.user.email, "SignIn")
        })
        return auth
    } else {
        return new Promise((r) => r());
    }
};

In this code snippet, we have a signIn function that handles user sign-in via Google using Firebase. After a successful sign-in, the track function is called with the user's email and the event type "SignIn" as arguments. This sends the sign-in event to the Jitsu server for tracking.

This way, you can track any user event by simply calling the track function with the appropriate arguments at the point in your code where the event occurs. By integrating this tracking throughout your application, you can capture a wide range of user interactions and gain valuable insights into user behavior.

The same approach of using the track function can be applied to other user events within your application. For instance, we have a Todo list functionality in the demo app, so we can track events related to it.

In the script section of your Svelte component for the Todo list, located in /src/components/Todo.svelte , you can insert the track function as follows:

    import TodoList from './TodoList.svelte';
    import type { TodoType } from './TodoList.svelte';
    import { track } from '$src/actions/jitsu';
    import authStore from '$src/stores/authStore';

    let lastId = 0;
    const createTodo = (text: string, done = false): TodoType => ({ id: ++lastId, text, done });

    let todoText = '';
    let todos: TodoType[] = [createTodo('learn SvelteKit', true)];

    $: upcompletedCount = todos.filter((t) => !t.done).length;
    $: status = `${upcompletedCount} of ${todos.length} remaining`;

    function addTodo() {
        todos = todos.concat(createTodo(todoText));
        todoText = '';
        track($authStore.user?.email, 'AddTodo')
    }

    const archiveCompleted = () => {
        todos = todos.filter((t) => !t.done)
        track($authStore.user?.email, 'CompleteTodo')
    };

    const deleteTodo = (todoId: number) => {
        todos = todos.filter((t) => t.id !== todoId)
        track($authStore.user?.email, 'DeleteTodo')
    };

    function toggleDone(id: number) {
        todos = todos.map((t) => (t.id === id ? { ...t, done: !t.done } : t));
    }

This code first imports the tract function, and then when a user performs the addTodo function, it tracks the user's email and sends an “AddTodo” event to Jitsu.

Since the demo app also has functionalities like counter, loan calculation, and weather query, we can add the tracking code to the corresponding functionalities.

Data visualization in Metabase

After setting up the tracking code, user interactions with the app are captured by Jitsu. You can see all these events being recorded in real-time.

The data is also stored in our OceanBase database.

In order to make the data more meaningful and to simulate a realistic scenario, I implemented a script to mimic user activity in the app, which basically automates user interactions with the app, such as signing in, adding and completing tasks, and deleting tasks.

This resulted in generating thousands of rows of data. Now, our OceanBase database consists of around 6000 rows of data. With the script running, new “fake” user events data also keep coming in.

With user events data stored in the OceanBase database and Metabase connected to our OceanBase instance, we can now leverage Metabase's capabilities to visualize and analyze the data.

Metabase provides a user interface to create various types of charts, tables, and dashboards. You can write SQL queries directly to fetch data from your OceanBase database or use the built-in query builder for a more visual approach.

For example, if we want to know how many new registered users we have every week in the past 6 months, we can use the following SQL statement.

SELECT 
    DATE_ADD(DATE(timestamp), INTERVAL(1-DAYOFWEEK(timestamp)) DAY) AS Week_Start,
    COUNT(DISTINCT user_email) AS New_Users 
FROM 
    events
WHERE 
    timestamp >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
    AND event = 'Register'
GROUP BY 
    Week_Start
ORDER BY 
    Week_Start DESC;

This statement counts the “Register” event in each given week and returns a table of new users per week. We can also visualize the table in Metabase:

Monthly Active Users (MAUs) is a key business metric for many online services. It represents the number of unique users who interact with a service within a month. To count the change in MAUs per week, you can use the following SQL statement:

SELECT 
    DATE_ADD(DATE(timestamp), INTERVAL(1-DAYOFWEEK(timestamp)) DAY) AS Week_Start,
    COUNT(DISTINCT user_email) AS Weekly_Active_Users 
FROM 
    events
WHERE 
    timestamp >= DATE_SUB(NOW(), INTERVAL 9 MONTH)
    AND event IN ('DeleteTodo', 'AddTodo', 'DeleteTodo', 'ReSetCounter', 'MinusCounter', 'CompleteTodo', 'SignIn', 'Register', 'Subscribe', 'ViewPricing')
GROUP BY 
    Week_Start
HAVING 
    COUNT(DISTINCT user_email) >= 2
ORDER BY 
    Week_Start DESC;

In this query, timestamp is the timestamp when an event occurs, user_email is the identifier of the user who triggered the event, and events is the table that stores event data. This SQL statement groups the events by the week of their occurrence, counts the number of distinct users for each week, and only includes the data for the past 9 months.

The event IN ('DeleteTodo', 'AddTodo', 'DeleteTodo', 'ReSetCounter', 'MinusCounter', 'CompleteTodo', 'SignIn', 'Register', 'Subscribe', 'ViewPricing') condition ensures that only the specified types of events are considered — Only when a user conducted events in this list, they will be considered an “active user.”

Similarly, we can also write SQL code to visualize the user growth and total events per day.

What’s cool about Metabase is that it allows you to build a dashboard to include all key business data in one page and make it refresh every minute.

Here is a dashboard I built with Metabase that extracts all the key information about the demo app and displays them in one page, with data being updated every minute.

Conclusion

In this blog post, we've walked through the process of building a real-time user analytics pipeline centered around OceanBase. We've seen how OceanBase, in conjunction with Jitsu and Metabase, can provide a powerful solution for handling, analyzing, and visualizing user event data from different sources.

The pipeline we've built collects user data from various sources, stores it in a database (OceanBase), and then analyzes and visualizes this data in real time. This provides valuable insights into the product and how users interact with the product.

One of the key strengths of OceanBase that we can leverage in this kind of system is its scalability and efficiency in handling large volumes of data, which makes it an ideal choice for the database in a real-time analytics pipeline.

Moreover, the entire codebase for this project is available on GitHub. I encourage you to clone this project and open it in Gitpod to experiment with the pipeline yourself. Please note that running OceanBase requires a generous amount of memory, so it's advised to use the Large GitPod instance with 16Gb of RAM.

In conclusion, this project demonstrates that OceanBase can serve as a powerful database in the creation of user analytics pipelines. Whether you're a data scientist seeking to understand user behavior or a marketing team looking to optimize user engagement, I believe this setup offers a compelling way to make sense of your data and extract meaningful insights.

Create a Langchain alternative from scratch using OceanBase

Wayne S — Fri, 11 Aug 2023 10:41:12 +0000

Recently, I've been exploring the world of OceanBase, a distributed relational database management system. As I navigated through its extensive documentation, I realized the magnitude of the task at hand - the sheer volume of content made it challenging to find precise information quickly and efficiently.

This experience sparked an idea. What if there was a way to simplify this process? What if we could leverage the power of AI to navigate through this vast sea of information? And thus, the concept of a document chatbot for OceanBase was born.

But I wanted to take this idea a step further. To make the project more intriguing and to deepen my understanding of OceanBase, I decided to use OceanBase itself as the vector database for AI training. This approach would not only provide a practical solution to the information retrieval challenge but also offer a unique opportunity to explore the capabilities of OceanBase from a different perspective.

In this blog post, I will share the journey of bringing this idea to life. From integrating AI with OceanBase to training the model and creating a chatbot, we will explore the challenges, the solutions, and the insights gained along the way. Whether you're an AI enthusiast, a database professional, or simply interested in the intersection of these two fields, I invite you to join me on this exciting exploration.

TL:DR:

The project integrates AI with OceanBase to create a document chatbot capable of answering user queries based on OceanBase's documentation, and any other GitHub-hosted documentation.
User questions and documentation articles are transformed into vector representations for comparison and answer generation.
Due to OceanBase's lack of inherent support for vector data type, vectors are stored and retrieved as JSON.
Dive into a step-by-step guide on setting up the project environment, training the model, and setting up the server.
The project highlights the need for OceanBase to incorporate support for the vector data type to fully leverage the potential of AI and machine learning.

Some reflections on the project

Embarking on this project to integrate OceanBase with AI was a challenging yet rewarding endeavor. It was an opportunity to dive into the intricacies of AI and databases, which provided a rich learning experience.

Generally, a document chatbot that we want to make uses AI to answer user queries with information from a document database. It transforms user questions into vector representations and compares them with preprocessed document vectors. The most relevant document is identified based on vector similarity. The chatbot then uses this document to generate a contextually appropriate answer, providing precise and relevant responses.

Before a chatbot can answer questions based on given documentation, you need to “train” it with relevant knowledge. The training process, at its essence, is to transform articles in the documentation into embeddings - numerical representations (vectors) of text that capture the semantic meanings of words. The embeddings are then stored in a database, in this project, OceanBase.

You may ask why I don’t use Langchain, which is already an established solution. While Langchain is a powerful platform for language model training and deployment, it wasn't the optimal choice for this project due to its expectations and our specific requirements.

Langchain assumes that the underlying database can handle vector storage and perform similarity searches. However, OceanBase, the database we used, doesn't directly support the vector data type.

Moreover, the project's key objective was to explore OceanBase's potential as a vector database for AI training. Using Langchain, which handles similarity search internally, would have bypassed the opportunity to delve into these aspects of OceanBase.

Since OceanBase doesn’t directly support vector stores, we have to find a workaround. But vectors are essentially an array of numbers. We can store it as a JSON string or a blob in the database. And when we need to compare the records with the question embeddings, we can just turn them back to embeddings in the next step.

Given that OceanBase does not inherently support the vector data type, each embedding needs to be converted into a data type that OceanBase does support, in this case, JSON. Consequently, when comparing the question with the articles, these records need to be reverted back to their original embedding form. This conversion process, unfortunately, results in a slower system that lacks scalability.

To fully leverage the potential of AI and machine learning, I would strongly recommend that the OceanBase team consider incorporating support for the vector data type. This would not only enhance the performance and scalability of the system but also position OceanBase as a forward-thinking database solution ready to embrace the AI era. I created an issue on their GitHub repo. If you are interested in exploring further, welcome to join the discussion there.

The project

The system is built on a Node.js framework, using Express.js for handling HTTP requests. Sequelize, a promise-based Node.js ORM, is used for the task of database schema migrations, seeding, and querying.

The project is structured to handle two main requests: training the model with articles and asking questions. The '/train' endpoint fetches the articles, transforms them into vectors, and stores them in the database. The '/ask' endpoint takes a user's question, finds the most relevant article using the stored vectors, and generates an answer using OpenAI's API.

This project is available on GitHub for everyone to access and utilize. This means you can leverage the power of this AI-driven solution not only for OceanBase but also for any other document sets you might have. Feel free to clone the repository, explore the code, and use it as a base to train your own document chatbot. It's a great way to make your documents more interactive and accessible.

Getting started

To set up the project environment from scratch, follow these steps:

Install Node.js: If not already installed, download and install Node.js from the official Node.js website.

Create a new project: Open your terminal, navigate to your desired directory, and create a new project using the command mkdir oceanbase-vector. Navigate into the new project directory with cd oceanbase-vector.

Initialize the Project: Initialize a new Node.js project by running npm init -y. This command creates a new package.json file with default values.

Install Dependencies: Install the necessary dependencies for the project. Use the following command to install all necessary packages:

npm install dotenv express lodash mysql2 openai sequelize --save

We will be using Sequelize as the ORM to interact with the OceanBase database. The lodash package is used to calculate cosine similarity in the similarity search process. And of course, we need the openai package to interact with OpenAI’s API. We will use express as the server, which will expose the ask and train endpoints.

Next, use npm install sequelize-cli --save-dev to install the development dependencies.

Now, our project setup is complete, and we are ready to start building each module.

Setting up OceanBase

To set up the project, you first need a running OceanBase cluster. This article I wrote has detailed instructions on how to set up OceanBase.

Once you have a running OceanBase instance, the next step is to create a table for storing the articles and their corresponding embeddings. This table, named article_vectors, will have the following columns:

id: This is an integer type column that will serve as the primary key for the table. It is auto-incremented, meaning each new entry will automatically be assigned an ID that is one greater than the ID of the previous entry.
content_vector: This is a JSON type column that will store the vector representation of the article. Each vector will be transformed into a JSON object for storage.
content: This is a text type column that will store the actual content of the article.

To create the article_vectors table, you can use the following SQL command:

CREATE TABLE article_vectors (
    id INT AUTO_INCREMENT,
    content_vector JSON,
    content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
    PRIMARY KEY (id)
);

Now, we have a table ready in the OceanBase database to store the articles and their vector representations. In the next sections, we will populate this table with data from the OceanBase documentation and then use this data to train our AI model.

We also need to define the ArticleVector model in the Sequlize project. First, run sequelize init to initialize the sequelize environment. In the /models folder generated, create a new file called ArticleVector.js.

const { DataTypes, Model } = require('sequelize');

class ArticleVector extends Model {}

module.exports = (sequelize) => {
    ArticleVector.init(
        {
            id: {
                type: DataTypes.INTEGER,
                autoIncrement: true,
                primaryKey: true,
            },
            content_vector: {
                type: DataTypes.JSON,
            },
            content: {
                type: DataTypes.TEXT,
            },
        },
        {
            sequelize,
            modelName: 'ArticleVector',
            tableName: 'article_vectors',
            timestamps: false,
        },
        {
            sequelize,
            modelName: 'ArticleVector',
            tableName: 'content',
            timestamps: false,
        }
    );

    return ArticleVector;
};

The code defines a Sequelize model for the article_vectors table in our OceanBase database. Sequelize is a promise-based Node.js ORM that allows you to interact with your SQL database using high-level API calls.

Training on the docs

The training process is the heart of our document chatbot. It involves fetching the OceanBase documentation from the GitHub repository, transforming the content into vector representations, and storing these vectors in the OceanBase database. This process is handled by two main scripts: trainDocs.js and embedding.js.

Fetching the documentation

The trainDocs.js script fetches the contents of the OceanBase documentation from the GitHub repository. It uses the GitHub API to access the repository and retrieve all the .md files. The script is designed to recursively fetch the contents of directories, ensuring that all documentation files, regardless of their location in the repository, are retrieved.

The script also fetches the content of each documentation file. It sends a request to the file's download URL and stores the returned content for further processing.

async function fetchRepoContents(
    repo,
    path = '',
    branch = 'main',
    limit = 100
) {
    const url = `https://api.github.com/repos/${repo}/contents/${path}?ref=${branch}`;
    const accessToken = process.env.GITHUB_TOKEN;
    const response = await fetch(url, {
        headers: {
            Authorization: `Bearer ${accessToken}`,
            Accept: 'application/vnd.github+json',
            'X-GitHub-Api-Version': '2022-11-28',
        },
    });
    const data = await response.json();

    // If the path is a directory, recursively fetch the contents
    if (Array.isArray(data)) {
        let count = 0;
        const files = [];
        for (const item of data) {
            if (count >= limit) {
                break;
            }
            if (item.type === 'dir') {
                const dirFiles = await fetchRepoContents(
                    repo,
                    item.path,
                    branch,
                    limit - count
                );
                files.push(...dirFiles);
                count += dirFiles.length;
            } else if (item.type === 'file' && item.name.endsWith('.md')) {
                files.push(item);
                count++;
            }
        }
        return files;
    }

    return [];
}

/**
 * @param {RequestInfo | URL} url
 */
async function fetchFileContent(url) {
    const response = await fetch(url);
    const content = await response.text();
    return content;
}

Now we can write the main function handling the embedding. The function will use the GitHub fetch functions we have defined to get all markdown files from a given repository. Notice that I have added a limit parameter to limit the number of files fetched. This is because the GitHub API has limits on how many requests you can make per hour. If the repo contains thousands of files, you may hit the limit.

async function articleEmbedding(repo, path = '', branch, limit) {
    const contents = await fetchRepoContents(repo, path, branch, limit);
    console.log(contents.length);
    contents.forEach(async (item) => {
        const content = await fetchFileContent(item.download_url);
        await storeEmbedding(content);
    });
}

Transforming and storing the vectors

The embedding.js script is responsible for transforming the fetched documentation into vector representations and storing these vectors in the OceanBase database. This is done using two main functions: embedArticle() and storeEmbedding().

To make sequelize and OpenAI work, we have to initialize them.

const { Sequelize } = require('sequelize');
const dotenv = require('dotenv');
dotenv.config();
const env = process.env.NODE_ENV || 'development';
const config = require(__dirname + '/../config/config.json')[env];

const { Configuration, OpenAIApi } = require('openai');

const configuration = new Configuration({
    apiKey: process.env.OPENAI_KEY,
});
const openai = new OpenAIApi(configuration);

The embedArticle() function uses OpenAI's Embedding API to transform the content of an article into a vector. It sends a request to the API with the article content and retrieves the generated vector.

async function embedArticle(article) {
    // Create article embeddings using OpenAI
    try {
        const result = await openai.createEmbedding({
            model: 'text-embedding-ada-002',
            input: article,
        });
        // get the embedding
        const embedding = result.data.data[0].embedding;
        return embedding;
    } catch (error) {
        if (error.response) {
            console.log(error.response.status);
            console.log(error.response.data);
        } else {
            console.log(error.message);
        }
    }
}

The storeEmbedding() function takes the generated vector and stores it in the OceanBase database. It connects to the database using Sequelize, creates a new record in the article_vectors table, and stores the vector and the original article content in the appropriate columns.

let sequelize;
if (config.use_env_variable) {
    sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
    sequelize = new Sequelize(
        config.database,
        config.username,
        config.password,
        config
    );
}
const ArticleVectorModel = require('../models/ArticleVector');
const ArticleVector = ArticleVectorModel(sequelize);
async function storeEmbedding(article) {
    try {
        await sequelize.authenticate();
        console.log('Connection has been established successfully.');
        const articleVector = await ArticleVector.create({
            content_vector: await embedArticle(article),
            content: article,
        });
        console.log('New article vector created:', articleVector.id);
    } catch (err) {
        console.error('Error:', err);
    }
}

To initiate the training process, you just need to call the articleEmbedding() function in trainDocs.js with the appropriate parameters. This function fetches the documentation, transforms it into vectors, and stores these vectors in the database.

This training process ensures that the chatbot has a comprehensive understanding of the OceanBase documentation, allowing it to provide accurate and relevant answers to user queries.

In the next section, we will discuss how to use the trained model to answer user queries.

As you may have noticed, we are using a .env file to store sensitive information in the project. Here is an example .env file you can use:

GITHUB_TOKEN="Get your Github token from https://docs.github.com/en/rest/guides/getting-started-with-the-rest-api?apiVersion=2022-11-28"
OPENAI_KEY ="YOUR_OPENAI_KEY"
DOC_OWNER="OceanBase, you can change this to another product's name so that the chatbot knows which product it is dealing with"
MODEL="gpt-4 and gpt-3.5-turbo are supported"

Asking the docs

Once the training process is complete, the chatbot is ready to answer user queries. This is handled by the /embeddings/askDocs.js file, which uses the stored vectors to find the most relevant documentation for a given question and then generates an answer using OpenAI's API.

The getSimilarDoc() function in askDocs.js is responsible for finding the most relevant documentation for a given question. It starts by transforming the question into a vector using the embedArticle() function from embedding.js.

Next, it retrieves all the stored vectors from the article_vectors table in the OceanBase database. It then calculates the cosine similarity between the question vector and each stored vector. The cosine similarity is a measure of how similar two vectors are, making it perfect for this task.

async function getSimilarDoc(question) {
    let sequelize;
    if (config.use_env_variable) {
        sequelize = new Sequelize(process.env[config.use_env_variable], config);
    } else {
        sequelize = new Sequelize(
            config.database,
            config.username,
            config.password,
            config
        );
    }
    const ArticleVector = ArticleVectorModel(sequelize);
    // get all rows in article_vector table
    const vectors = await ArticleVector.findAll();
    sequelize.close();
    // console.log(vectors[0]);

    // Calculate cosine similarity for each vector
    const embeddedQuestion = await embedArticle(question);

    // Calculate cosine similarity for each vector
    // Calculate cosine similarity for each vector
    const similarities = vectors.map((vector) => {
        const similarity = cosineSimilarity(
            embeddedQuestion,
            vector.content_vector
        );
        return {
            id: vector.id,
            content: vector.content,
            similarity: similarity,
        };
    });
    const mostSimilarDoc = _.orderBy(similarities, ['similarity'], ['desc'])[0];
    // console.log(mostSimilarDoc);
    return mostSimilarDoc;
}

The cosineSimilarity() function in askDocs.js is a crucial part of the document retrieval process. It calculates the cosine similarity between two vectors, which is a measure of how similar they are.

In the context of this project, it is used to determine the similarity between the vector representation of a user's question and the vector representations of the articles in the database. The article with the highest cosine similarity to the question is considered the most relevant.

The function takes two vectors as inputs. It calculates the dot product of the two vectors and the magnitude of each vector. The cosine similarity is then calculated as the dot product divided by the product of the two magnitudes.

function cosineSimilarity(vecA, vecB) {
    const dotProduct = _.sum(_.zipWith(vecA, vecB, (a, b) => a * b));
    const magnitudeA = Math.sqrt(_.sum(_.map(vecA, (a) => Math.pow(a, 2))));
    const magnitudeB = Math.sqrt(_.sum(_.map(vecB, (b) => Math.pow(b, 2))));

    if (magnitudeA === 0 || magnitudeB === 0) {
        return 0;
    }
    return dotProduct / (magnitudeA * magnitudeB);
}

The function returns the documentation with the highest cosine similarity to the question. This is the most relevant documentation for the given question.

With the most relevant documentation identified, the askAI() function in askDocs.js generates an answer to the question. It uses OpenAI's API to generate a contextually appropriate answer based on the identified documentation.

The function sends a request to the API with the question and the relevant documentation. The API returns a generated answer, which the function then returns.

This process ensures that the chatbot provides accurate and relevant answers to user queries, making it a valuable tool for anyone seeking information from the OceanBase documentation.

async function askAI(question) {
    const mostSimilarDoc = await getSimilarDoc(question);
    const context = mostSimilarDoc.content;
    try {
        const result = await openai.createChatCompletion({
            model: process.env.MODEL,
            messages: [
                {
                    role: 'system',
                    content: `You are ${
                        process.env.DOC_OWNER || 'a'
                    } documentation assistant. You will be provided a reference docs article and a question, please answer the question based on the article provided. Please don't make up anything.`,
                },
                {
                    role: 'user',
                    content: `Here is an article related to the question: \n ${context} \n Answer this question: \n ${question}`,
                },
            ],
        });
        // get the answer
        const answer = result.data.choices[0].message.content;
        console.log(answer);
        return {
            answer: answer,
            docId: mostSimilarDoc.id,
        };
    } catch (error) {
        if (error.response) {
            console.log(error.response.status);
            console.log(error.response.data);
        } else {
            console.log(error.message);
        }
    }
}

Setting up the API server

Now that we have set up the training and asking function, we can call them from an API endpoint. The Express.js server is the main entry point of our application. It exposes two endpoints, /train and /ask, which handle the training of the model and answering user queries, respectively. The code described here will be in the index.js file.

The server is initialized using Express.js, a fast, unopinionated, and minimalist web framework for Node.js. It is configured to parse incoming requests with JSON payloads using the express.json() middleware.

const express = require('express');
const app = express();
const db = require('./models');
app.use(express.json());

The server establishes a connection with the OceanBase database using Sequelize. The sync() function is called on the Sequelize instance to ensure that all defined models are synchronized with the database. This includes creating the necessary tables if they don't already exist.

db.sequelize.sync().then((req) => {
    app.listen(3000, () => {
        console.log('Server running at port 3000...');
    });
});

Training Endpoint

The /train endpoint is a POST route that triggers the training process. It expects a request body containing the GitHub repository details (repo, path, branch, and limit). These details are passed to the articleEmbedding() function, which fetches the documentation, transforms it into vectors, and stores these vectors in the database.

app.post('/train', async (req, res, next) => {
    try {
        await articleEmbedding(
            req.body.repo,
            req.body.path,
            req.body.branch,
            req.body.limit
        );
        res.json({
            status: 'Success',
            message: 'Docs trained successfully.',
        });
    } catch (e) {
        next(e); // Pass the error to the error handling middleware
    }
});

If the training process is successful, the endpoint responds with a success message. If an error occurs during the process, it is passed to the error handling middleware.

A POST request to the endpoint will contain a request body like this:

{
  "repo":"oceanbase/oceanbase-doc",
  "path":"en-US",
  "branch": "V4.1.0",
  "limit": 1000
}

You can change the repository to other GitHub-hosted documentation you want. The path is the folder of the targeting content. In this case, I used “en-US” because I only want to train the English documentation of OceanBase. The branch parameter indicates the branch of the repo. In most cases, you can use the main branch or the branch of a certain version. The limit parameter is only used if a repository has too many files so that your GitHub API hits the limit.

This is how it looks like if the training is successful.

And in the OceanBase database, we can see all the records and their embeddings:

Asking Endpoint

The /ask endpoint is also a POST route. It expects a request body containing a user's question. This question is passed to the askAI() function, which finds the most relevant documentation and generates an answer using OpenAI's API.

app.post('/ask', async (req, res, next) => {
    try {
        const answer = await askAI(req.body.question);
        res.json({
            status: 'Success',
            answer: answer.answer,
            docId: answer.docId,
        });
    } catch (e) {
        next(e); // Pass the error to the error handling middleware
    }
});

The endpoint responds with the generated answer and the ID of the most relevant documentation. This is an example of a successful request.

As we can see, when we ask the chatbot what are the software and hardware environments to install OceanBase in a demo environment, the chatbot provided an answer that is stated in this article.

Conclusion

This project represents an experiment in integrating AI with OceanBase to create a document chatbot. While it demonstrates the potential of AI in enhancing the usability of documentation, it's important to note that this is still an experimental setup and not yet ready for production use. The current implementation faces scalability issues due to the need to convert vectors into JSON for storage and back into vectors for comparison.

To fully leverage the potential of AI and machine learning, I would again recommend that the OceanBase team consider incorporating support for the vector data type. This would not only enhance the performance and scalability of the system but also position OceanBase as a forward-thinking database solution as AI becomes the next big thing. I have created an issue in the OceanBase GitHub repo to discuss this proposal, you are welcome to join the conversation.

The code for this project is available on GitHub for everyone to access and utilize. This means you can leverage the power of this AI-driven solution not only for OceanBase but also for any other document sets you might have. Feel free to clone the repository, explore the code, and use it as a base to train your own document chatbot using OceanBase. It's a great way to make your documents more interactive and accessible.

In conclusion, while there is still work to be done, this project represents a promising step toward using OceanBase as a vector database for AI training. With further development and refinement of the database, we can look forward to a time when finding answers in documentation is as simple as asking a question.

Use OceanBase in Node.js: Build a CRM with Sequelize and Express

Wayne S — Thu, 13 Jul 2023 07:21:10 +0000

Welcome to the latest episode in our series of articles designed to help you get started with OceanBase, a next-generation distributed relational database. Building on our previous guides where we connected OceanBase to a Sveltekit app and built an e-commerce app with Flask and OceanBase, we now turn our attention to integrating OceanBase into a Node.js project using the popular Sequelize ORM and the Express server.

Sequelize is a promise-based Node ORM that supports the dialects for PostgreSQL, MySQL, MariaDB, SQLite, and of course, OceanBase. It offers a robust set of features for model querying and manipulation. Express, on the other hand, is a minimal and flexible Node web application framework that provides a powerful set of features for web and mobile applications.

But why use Sequelize and Express with OceanBase, you may ask? Sequelize will help us interact with OceanBase, which is compatible with MySQL, in a more JavaScript-friendly way, abstracting away much of the SQL syntax. Express, being a fast, unopinionated, and minimalist web framework for Node, will allow us to build our application's backend with ease and efficiency.

What we are going to build: A mini-CRM system

In this article, I’m going to build a mini Customer Relationship Management (CRM) system. CRM systems are integral to managing customer data, interactions, and business information. They are crucial for businesses of all sizes to maintain effective customer relationships and to ensure smooth operations.

The mini CRM system will leverage the power of OceanBase, a next-generation distributed relational database that excels in handling massive amounts of data with high availability and strong consistency. OceanBase's scalability and distributed nature make it a perfect fit for businesses of all sizes. As businesses grow and the volume of their data increases, they can effortlessly scale their database with OceanBase, making it an ideal choice for a CRM system and other enterprise resource planning applications.

The mini CRM system will be a simple, yet powerful application that will allow us to perform Create, Read, Update, and Delete (CRUD) operations on contacts in our database. These operations form the backbone of any data-driven application and are essential for managing records in a database.

The CRM system will have the following features:

Create Contacts: We will be able to add new contacts to our database. Each contact will have basic information such as name, email, company, and owner.
Read Contacts: We will be able to view all the contacts stored in our database. This feature will display a list of all the contacts with their respective information. We will also be able to read the record of one specific contact.
Update Contacts: This feature will allow us to modify the details of a specific contact. We can update any information related to a contact, such as changing their email or company.
Delete Contacts: If we no longer need to keep a contact in our database, we can delete it. This feature will remove the contact and all its related information from our database.

By building this mini CRM system, we will learn how to effectively use OceanBase in a Node.js project with Sequelize as the ORM and Express as the server. We will understand how to perform CRUD operations using Sequelize and how to structure our Express application to handle these operations. Most importantly, we will see how OceanBase can effortlessly manage our data, providing us with a reliable and scalable solution for our application's database needs.

Setting up the database

To set up the project, you first need a running OceanBase cluster. You have several options for doing so. You can install OceanBase in your local environment, spin up a virtual machine in the cloud to run it, or use OceanBase Cloud in the AWS marketplace to set up your cluster in just a few clicks.

In this tutorial, I will simply set up a demo OceanBase cluster on an AWS EC2 instance. You can read my previous article on how to install OceanBase in AWS.

After installing OceanBase and spinning up the demo cluster, I’m going to set up a demo user for the development, rather than using the root user, which is considered not secure.

We can create a new user called demo that only has access to the database we are using. Before this, I have also created a database in OceanBase called app, which will be the main database we will interact with in this article.

To create a new user, we will use the following command:

CREATE USER 'demo'@'localhost' IDENTIFIED BY 'password';

Next, we will grant the user permission to access the app database.

GRANT ALL PRIVILEGES ON app.* TO 'demo'@'localhost';

Now we can connect to the OceanBase instance from our project using the demo user credentials.

Host: YOUR_EC2_PUBLIC_IP_ADDRESS
Port: 2881
User: demo
Password: password

Setting up the project

Now, we need to set up our Node.js project. I’ll assume you have Node.js and npm installed. If not, you can download and install them from the official Node.js website. Once you're ready, create a new directory for your project and initialize it with npm:

mkdir oceanbase-sequelize && cd oceanbase-sequelize
npm init -y

Next, install Express, Sequelize, and the mysql2 driver. (Since there is no direct Sequelize support of OceanBase, and OceanBase is compatible with MySQL, I will use the MySQL driver to connect to OceanBase in Sequelize, which works perfectly fine in all my tests.)

npm install express sequelize mysql2

Configuring Sequelize

Sequelize is a powerful ORM tool that provides a high-level abstraction for managing database operations. It supports a wide range of databases, including OceanBase (via MySQL). Configuring Sequelize involves setting up the connection to the database and defining models that represent the tables in the database.

You can use a Command Line Interface (CLI) that simplifies the process of setting up and managing your project. One of the commands provided by the Sequelize CLI is sequelize init, which sets up a basic project structure for you.

Before you can use the Sequelize CLI, you need to install it. You can do this by running:

npm install --save-dev sequelize-cli

Once the Sequelize CLI is installed, you can initialize your project by running:

npx sequelize init

This command will create the following directories and files:

config/: This directory contains a config.json file where you can specify your database configuration for different environments (development, test, and production).
models/: This directory is for your Sequelize models. By default, it includes an index.js file that sets up Sequelize and imports all models.
migrations/: This directory is for your migration scripts.
seeders/: This directory is for your seeder files.

Configuring the Database Connection

To connect to your OceanBase database, you need to update the config/config.json file with your database credentials. Here's my configuration for the credentials I created in the last section:

{
  "development": {
    "username": "demo",
    "password": "password",
    "database": "app",
    "host": "YOUR_EC2_PUBLIC_IP_ADDRESS",
        "port": 2881,
    "dialect": "mysql"
  },
  "test": {
    "username": "your_username",
    "password": "your_password",
    "database": "your_database",
    "host": "localhost",
    "dialect": "mysql"
  },
  "production": {
    "username": "your_username",
    "password": "your_password",
    "database": "your_database",
    "host": "localhost",
    "dialect": "mysql"
  }
}

Now, Sequelize is configured and ready to use in your project. You can start defining your models in the models/ directory and use Sequelize's features to interact with your OceanBase database.

Defining data models

Data models in Sequelize represent tables in your database. They are defined using the define method on your Sequelize instance. This method takes two arguments: the name of the model (which will be used as the table name) and an object that defines the model's attributes.

Let's define a Contact model for the CRM system. This model will have the following attributes: firstName, lastName, email, age, company, and owner.

Here's how we can define this model in the models/Contact.js file:

module.exports = (sequelize, DataTypes) => {
    const Contact = sequelize.define('Contact', {
        firstName: {
            type: DataTypes.STRING,
            allowNull: false,
            validate: {
                notEmpty: true,
            },
        },
        lastName: {
            type: DataTypes.STRING,
            allowNull: false,
            validate: {
                notEmpty: true,
            },
        },
        email: {
            type: DataTypes.STRING,
            allowNull: false,
            validate: {
                notEmpty: true,
            },
        },
        age: {
            type: DataTypes.INTEGER,
            allowNull: true,
            validate: {
                notEmpty: true,
            },
        },
        company: {
            type: DataTypes.STRING,
            allowNull: true,
            validate: {
                notEmpty: true,
            },
        },
        owner: {
            type: DataTypes.STRING,
            allowNull: true,
            validate: {
                notEmpty: true,
            },
        },
    });
    return Contact;
};

In this code, we're defining a Contact model with six attributes. Each attribute is an object that specifies the data type and validation rules.

For example, the firstName, lastName, and email attributes are of type STRING and cannot be null or empty. The age, company, and owner attributes are also of type STRING but can be null, however, they cannot be empty if provided.

The validate property is used to specify validation rules. In this case, we're using the notEmpty rule, which ensures that the value is not an empty string.

The define method returns the model, which we then export so it can be used in other parts of our application. This Contact model now represents a Contact table in our OceanBase database. You can use this model to perform CRUD operations on the Contact table.

Building the Express application

The Express application is the core of our project. It's where we define our routes and handle requests. In our case, we'll be creating routes to perform CRUD (Create, Read, Update, Delete) operations on our Contact model.

Setting Up Express

First, we import Express and create an instance of it. We also import our Contact model and the Sequelize instance from the models directory.

const express = require('express');
const app = express();

// Connect to the database
const db = require('./models');
const { Contact } = require('./models');

We also add a middleware to parse JSON bodies. This is necessary because we'll be receiving JSON data in our POST and PATCH requests.

app.use(express.json());

Creating Routes

Get All Contacts

Our first route is a GET route to /contacts. This route retrieves all contacts from the database using the findAll method on the Contact model.

app.get('/contacts', async (req, res) => {
    const contacts = await Contact.findAll();
    res.json(contacts);
});

Get a Contact by ID

Next, we have a GET route to /contact/:id. This route retrieves a single contact by its ID using the findByPk method.

app.get('/contact/:id', async (req, res) => {
    const contact = await Contact.findByPk(req.params.id);
    if (contact) {
        res.json(contact);
    } else {
        res.status(404).send({
            status: 'failed',
            message: 'Contact not found',
        });
    }
});

Create a New Contact

We also have a POST route to /contact. This route creates a new contact using the create method.

app.post('/contact', async (req, res) => {
    const newContact = await Contact.create(req.body);
    res.json(newContact);
});

When making a POST request to this route, the request body should be a JSON object that represents a new contact. The structure of this object should match the structure of our Contact model.

Here's an example of how you can structure the request body:

{
    "firstName": "John",
    "lastName": "Doe",
    "email": "john.doe@example.com",
    "age": 30,
    "company": "Tech Corp",
    "owner": "Jane Doe"
}

Delete a Contact

Our DELETE route to /contact/:id deletes a contact by its ID using the destroy method.

app.delete('/contact/:id', async (req, res) => {
    const result = await Contact.destroy({
        where: {
            id: req.params.id,
        },
    });
    if (result) {
        res.status(200).send({
            status: 'succeed',
            message: 'Contact deleted',
        });
    } else {
        res.status(404).send({
            status: 'failed',
            message: 'Contact not found',
        });
    }
});

In this route, :id is a route parameter that represents the ID of the contact you want to delete. When you make a DELETE request, you replace :id with the actual ID of the contact.

For example, if you want to delete the contact with an ID of 5, you would send a DELETE request to /contact/5.

The ID of a contact is typically assigned by OceanBase when the contact is created. You can find the ID of a contact by making a GET request to the /contacts route, which returns a list of all contacts. Each contact in the list includes an id property that you can use in the DELETE route.

Update a Contact

Finally, we have a PATCH route to /contact/:id that updates a contact by its ID using the update method.

app.patch('/contact/:id', async (req, res) => {
    const updatedContact = await Contact.update(req.body, {
        where: {
            id: req.params.id,
        },
    });
    if (updatedContact[0]) {
        res.status(200).send({
            status: 'Contact updated',
            data: await Contact.findByPk(req.params.id),
        });
    } else {
        res.status(404).send({
            status: 'failed',
            message: 'Contact not found',
        });
    }
});

When making a PATCH request to this route, the request body should be a JSON object that includes the properties you want to update. The structure of this object should match the structure of our Contact model, but you only need to include the properties you want to change.

Here's an example of how you can structure the request body:

{
    "email": "new.email@example.com",
    "company": "New Tech Corp"
}

In this example, we're only updating the email and company properties. The firstName, lastName, age, and owner properties will remain unchanged.

Starting the server

Finally, we synchronize our Sequelize models with our OceanBase database using db.sequelize.sync() and start our server.

db.sequelize.sync().then((req) => {
    app.listen(3000, () => {
        console.log('Server running at port 3000...');
    });
});

The sync method creates the necessary tables in the database if they don't exist. After the synchronization is complete, we start our server on port 3000.

And that's it! We now have a fully functional Express application that can perform CRUD operations on our Contact model using Sequelize and OceanBase.

Running the app

To run the application, simply navigate to the root directory of your project in your terminal and run the command:

node index.js

You should see the message "Server running at port 3000..." in your terminal, indicating that your application is running and ready to accept requests.

To interact with your application, you can use a tool like Postman, which allows you to send HTTP requests to your server and view the responses. In this article, I will just use the Thunder Client plugin in VS Code to test the requests.

Here are some examples of how you can use Thunder Client to interact with your application:

Creating a new contact

To create a new contact, you can send a POST request to http://127.0.0.1:3000/contact. In the body of the request, you would include a JSON object that represents the new contact.

In this example, we're creating a new contact with the name "John Smith", email "john@example.com", age 33, company "EXAMPLE INC", and owner "Wayne". After sending the request, we receive a response that includes the new contact with an ID assigned by OceanBase.

Getting all contacts

To get all contacts, you would send a GET request to http://127.0.0.1:3000/contacts.

In this example, we're retrieving all contacts from the OceanBase database. The response is an array of contacts.

Getting a contact by ID

To get a contact by ID, you would send a GET request to http://127.0.0.1:3000/contact/:id, replacing :id with the ID of the contact.

In this example, we're retrieving the contact with an ID of 7. The response is the contact with that ID.

Updating a contact

To update a contact, you would send a PATCH request to http://127.0.0.1:3000/contact/:id, replacing :id with the ID of the contact. In the body of the request, you would include a JSON object that includes the properties you want to update.

In this example, we're updating the first name of the contact with an ID of 6. The response is the updated contact.

Deleting a contact

To delete a contact, you would send a DELETE request to http://127.0.0.1:3000/contact/:id, replacing :id with the ID of the contact.

In this example, we're deleting the contact with an ID of 5. The response is a message indicating that the contact was successfully deleted.

Conclusion

In this article, we've walked through the process of integrating OceanBase into a Node project using Sequelize and Express. We've built a mini-CRM system that performs CRUD operations on a Contact model.

We've seen how Sequelize abstracts away much of the SQL syntax, allowing us to interact with our OceanBase database in a JavaScript-friendly way. We've also seen how Express allows us to build our application's backend with ease and efficiency. The mini-CRM system we've built is a simple yet powerful application that leverages the scalability and distributed nature of OceanBase, making it a perfect fit for businesses of all sizes.

If you want to explore the project further, you can clone the project from my GitLab repository and run it on your local machine. Here is the link to the repository: https://gitlab.com/waynering/sequalize-app

Feel free to modify the project and experiment with different features of OceanBase. The possibilities are endless, and there's always something new to learn.

If you need any help installing or using OceanBase, you can reach out to the OceanBase team on Twitter (@OceanBaseDB) or ask a question on StackOverflow with the “OceanBase” tag.

Building a headless e-commerce app using OceanBase and Python

Wayne S — Wed, 26 Apr 2023 15:05:34 +0000

Python has in recent years rapidly become one of the most popular languages for backend development, thanks to its flexibility and rich library support.

In this blog post, I will demonstrate how to use OceanBase in a Python project. By combining OceanBase with popular Python libraries such as FastAPI, SQLAlchemy (ORM), and Uvicorn (server), we will create a REST API that serves as the backend of a headless e-commerce application.

Throughout the tutorial, we will explore the capabilities of OceanBase in a Python project and showcase how to make CRUD operations to items and stores in the database. With the integration of OceanBase in our e-commerce application, we can ensure that our backend is capable of handling massive amounts of data and traffic while maintaining optimal performance.

OceanBase is a high-performance, distributed relational database system designed to provide exceptional scalability and ACID capability. OceanBase, which is open source, is an ideal choice for businesses seeking a robust and reliable distributed database solution for their applications.

What we are going to build: A headless e-commerce API

In this tutorial, we will create a FastAPI application that provides access to items and store resources for a headless e-commerce API, connected to an OceanBase database.

Headless e-commerce is an innovative approach to online sales that involves decoupling the frontend and backend components of an e-commerce application. By separating the presentation layer (the "head") from the underlying business logic and data management (the "body"), headless e-commerce enables businesses to achieve greater flexibility, scalability, and customization in their online stores.

Our OceanBase-powered headless e-commerce app consists of an Item module and a Store module, where all items belong to their corresponding store. Features of this API include:

Item Management
- Create an item: Users can create new items with details such as name, price, description, and the associated store.
- Retrieve items: Fetch all items from the store.
- Update an item: Update an item's details by its ID.
- Delete an item: Delete an item by its ID.
Store Management
- Create a store: Users can create new stores with a name.
- Retrieve stores: Fetch all stores or filter stores by name.
- Update a single store: Update a store by its ID.
- Delete a store: Delete a store by its ID.
Database Integration:
- The application uses SQLAlchemy as the ORM to interact with an OceanBase database. It defines models, schemas, and repositories to manage items and stores in the database.
Documentation:
- The application is self-documented using FastAPI's built-in support for OpenAPI and Swagger UI. Users can interact with the API through the automatically generated Swagger UI.

In this tutorial, we will only build a basic headless API to demonstrate the capabilities of OceanBase in a Python application. However, a complete headless e-commerce API would typically include additional features and integrations to provide a more comprehensive solution. For example, essential e-commerce features like user authentication and authorization, cart management, order management, and shipping management are missing from this demo.

Again, the codebase for this project is uploaded to GitLab. You can read the last section of this post about how to set up your own project using this codebase.

Prerequisites

You'll need Python 3 with virtualenv installed. Virtualenv is a package and virtual environment manager that uses pip with features like version locking and dependency isolation between projects.

To follow this demo project, you also need a running OceanBase cluster. You have several options for doing so. You can install OceanBase in your local environment, spin up a virtual machine in the cloud to run it, or use OceanBase Cloud in the AWS marketplace to set up your cluster in just a few clicks.

In this project, I’m going to use the EC2 approach and the demo server for simplicity. In production, please follow OceanBase’s official guide on deploying in a Kubernetes cluster.

Setup and installation

Start by creating a folder called python-fastapi-oceanbase-api in any directory on your disk for the project:

$ mkdir python-fastapi-oceanbase-api
$ cd python-fastapi-oceanbase-api

Navigate to the project folder and activate the virtual environment:

python3 -m venv venv
source venv/bin/activate

Install the required dependencies using pip:

pip install fastapi uvicorn sqlalchemy

As the initial setup, this command installs FastAPI for building the API, Uvicorn as the ASGI server, and SQLAlchemy as the ORM.

As we dive into the project, we may have to install some additional packages. In the final Gitlab repo, I will also include a requirements.txt file that contains all the necessary packages.

Set up the OceanBase connection

Create a new file called db.py in the project root directory and add the following code to configure the database connection using SQLAlchemy:

import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
load_dotenv()

DATABASE_URL = os.environ.get('DB_URL')
print(DATABASE_URL)
engine = create_engine(DATABASE_URL) # type: ignore
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Here we will use a .env file to store our OceanBase database connection string. Since OceanBase is compatible with MySQL, and SQLAlchemy currently doesn’t support OceanBase, we can use the MySQL protocol instead. Here is what the connection string in the .env file looks like:

DB_URL = mysql+pymysql://user:password@EC2_IP:port/database_name

The db.py file will import the necessary packages to create the SQLAlchemy engine and database session for OceanBase, create the SQLAlchemy engine for OceanBase, and define the SessionLocal class, which represents a database session.

Finally, a Base class was created using declarative_base(), which will be inherited by each of the database models or ORM classes. The get_db() function creates an independent database session for each request.

Designing database models

In this section, we will develop database models for storing and organizing our data. Our application requires two database models: Item and Store, along with their respective repositories. We will use db.py, which we created in the previous section, to develop our SQLAlchemy models. This file includes a class called Base that serves as a declarative base for defining our models.

Create a folder named database and add two files: models.py and repositories.py. We will define all database entities in models.py and their corresponding repositories in repositories.py.

The content of the models.py file should be like this:

from sqlalchemy import Column, ForeignKey, Integer, String, Float
from sqlalchemy.orm import relationship

from db import Base

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True,index=True)
    name = Column(String(80), nullable=False, unique=True,index=True)
    price = Column(Float(precision=2), nullable=False)
    description = Column(String(200))
    store_id = Column(Integer,ForeignKey('stores.id'),nullable=False)
    def __repr__(self):
        return 'ItemModel(name=%s, price=%s,store_id=%s)' % (self.name, self.price,self.store_id)

class Store(Base):
    __tablename__ = "stores"
    id = Column(Integer, primary_key=True,index=True)
    name = Column(String(80), nullable=False, unique=True)
    items = relationship("Item",primaryjoin="Store.id == Item.store_id",cascade="all, delete-orphan")

    def __repr__(self):
        return 'Store(name=%s)' % self.name

The models.py code creates and manages the Item and Store database models. It begins by defining the Item class and mapping it to the 'items' table in our database. The table columns and their data types are specified, with store_id serving as a Foreign key reference for Stores.

A relationship is established using the SQLAlchemy ORM, which virtually contains values from related tables. Finally, additional helper methods are added to display the Store object at runtime.

The repositories.py file includes a collection of reusable functions that facilitate interaction with the data stored in the database.

from sqlalchemy.orm import Session
from . import models, schemas

class ItemRepo:

 async def create(db: Session, item: schemas.ItemCreate):
        db_item = models.Item(name=item.name,price=item.price,description=item.description,store_id=item.store_id)
        db.add(db_item)
        db.commit()
        db.refresh(db_item)
        return db_item

 def fetch_by_id(db: Session,_id):
     return db.query(models.Item).filter(models.Item.id == _id).first()

 def fetch_by_name(db: Session,name):
     return db.query(models.Item).filter(models.Item.name == name).first()

 def fetch_all(db: Session, skip: int = 0, limit: int = 100):
     return db.query(models.Item).offset(skip).limit(limit).all()

 async def delete(db: Session,item_id):
     db_item= db.query(models.Item).filter_by(id=item_id).first()
     db.delete(db_item)
     db.commit()


 async def update(db: Session,item_data):
    updated_item = db.merge(item_data)
    db.commit()
    return updated_item

class StoreRepo:

    async def create(db: Session, store: schemas.StoreCreate):
            db_store = models.Store(name=store.name)
            db.add(db_store)
            db.commit()
            db.refresh(db_store)
            return db_store

    def fetch_by_id(db: Session,_id:int):
        return db.query(models.Store).filter(models.Store.id == _id).first()

    def fetch_by_name(db: Session,name:str):
        return db.query(models.Store).filter(models.Store.name == name).first()

    def fetch_all(db: Session, skip: int = 0, limit: int = 100):
        return db.query(models.Store).offset(skip).limit(limit).all()

    async def delete(db: Session,_id:int):
        db_store= db.query(models.Store).filter_by(id=_id).first()
        db.delete(db_store)
        db.commit()

    async def update(db: Session,store_data):
        db.merge(store_data)
        db.commit()

The repositories.py file defines helper methods that facilitate CRUD operations on both the Item and Store database models. These functions enable efficient interaction with the data stored in the database, streamlining the process of managing and accessing information within the application.

Create schema

We will now create a file named schemas.py within the database folder. This file will house the Pydantic models that correspond to our SQLAlchemy models. Essentially, these Pydantic models will outline the schema or the valid data structure for our application. Pydantic takes care of all data validations automatically.

The Item schema represents an individual item in a store. The schema is based on the ItemBase class, which contains the following attributes:

name (str): The name of the item.
price (float): The price of the item.
description (Optional[str]): An optional description of the item, which can be None if not provided.
store_id (int): The ID of the store where the item is available.
id (int): The unique identifier of the item.

from typing import List, Optional
from pydantic import BaseModel

class ItemBase(BaseModel):
    name: str
    price : float
    description: Optional[str] = None
    store_id: int

class ItemCreate(ItemBase):
    pass

class Item(ItemBase):
    id: int

    class Config:
        orm_mode = True

The Store schema represents a store containing items. The schema is based on the StoreBase class, which contains the following attribute:

name (str): The name of the store.
id (int): The unique identifier of the store.
items (List[Item]): A list of items available in the store, represented by the Item schema. By default, the list is empty.

class StoreBase(BaseModel):
    name: str

class StoreCreate(StoreBase):
    pass

class Store(StoreBase):
    id: int
    items: List[Item] = []

    class Config:
        orm_mode = True

The API

With the OceanBase connection set up, let's build a FastAPI interface to interact with our database. In the root folder, we will create a main.py file.

This file should provide a REST API interface for managing Items and Stores in OceanBase, allowing clients to interact with the resources using standard HTTP methods. FastAPI takes care of generating the appropriate Swagger documentation and handling request/response validation.

The file will do the following:

Import necessary packages and models, and then create the FastAPI app.

from fastapi import Depends, FastAPI, HTTPException
from fastapi.responses import JSONResponse
from database import models
from db import get_db, engine
import database.models as models
import database.schemas as schemas
from database.repositories import ItemRepo, StoreRepo
from sqlalchemy.orm import Session
import uvicorn
from typing import List,Optional
from fastapi.encoders import jsonable_encoder

app = FastAPI(title="Sample FastAPI Application",
    description="Sample FastAPI Application with Swagger and Sqlalchemy",
    version="1.0.0",)

models.Base.metadata.create_all(bind=engine)

@app.exception_handler(Exception)
def validation_exception_handler(request, err):
    base_error_message = f"Failed to execute: {request.method}: {request.url}"
    return JSONResponse(status_code=400, content={"message": f"{base_error_message}. Detail: {err}"})

Define the API endpoints for Items:

Create a new Item: A POST request to /items that accepts an ItemCreate schema and returns the created Item.
Get all Items: A GET request to /items that retrieves a list of all items, with optional filtering by name.
Get a specific Item: A GET request to /items/{item_id} that retrieves an item by its ID.
Delete an Item: A DELETE request to /items/{item_id} that removes the item with the specified ID from the database.
Update an Item: A PUT request to /items/{item_id} that updates the item with the specified ID using the data provided in the Item schema.

@app.post('/items', tags=["Item"],response_model=schemas.Item,status_code=201)
async def create_item(item_request: schemas.ItemCreate, db: Session = Depends(get_db)):
    """
    Create an Item and store it in the database
    """

    db_item = ItemRepo.fetch_by_name(db, name=item_request.name)
    if db_item:
        raise HTTPException(status_code=400, detail="Item already exists!")

    return await ItemRepo.create(db=db, item=item_request)

@app.get('/items', tags=["Item"],response_model=List[schemas.Item])
def get_all_items(name: Optional[str] = None,db: Session = Depends(get_db)):
    """
    Get all the Items stored in database
    """
    if name:
        items =[]
        db_item = ItemRepo.fetch_by_name(db,name)
        items.append(db_item)
        return items
    else:
        return ItemRepo.fetch_all(db)

@app.get('/items/{item_id}', tags=["Item"],response_model=schemas.Item)
def get_item(item_id: int,db: Session = Depends(get_db)):
    """
    Get the Item with the given ID provided by User stored in database
    """
    db_item = ItemRepo.fetch_by_id(db,item_id)
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found with the given ID")
    return db_item

@app.delete('/items/{item_id}', tags=["Item"])
async def delete_item(item_id: int,db: Session = Depends(get_db)):
    """
    Delete the Item with the given ID provided by User stored in database
    """
    db_item = ItemRepo.fetch_by_id(db,item_id)
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found with the given ID")
    await ItemRepo.delete(db,item_id)
    return "Item deleted successfully!"

@app.put('/items/{item_id}', tags=["Item"],response_model=schemas.Item)
async def update_item(item_id: int,item_request: schemas.Item, db: Session = Depends(get_db)):
    """
    Update an Item stored in the database
    """
    db_item = ItemRepo.fetch_by_id(db, item_id)
    if db_item:
        update_item_encoded = jsonable_encoder(item_request)
        db_item.name = update_item_encoded['name']
        db_item.price = update_item_encoded['price']
        db_item.description = update_item_encoded['description']
        db_item.store_id = update_item_encoded['store_id']
        return await ItemRepo.update(db=db, item_data=db_item)
    else:
        raise HTTPException(status_code=400, detail="Item not found with the given ID")

Define the API endpoints for Stores:

Create a new Store: A POST request to /stores that accepts a StoreCreate schema and returns the created Store.
Get all Stores: A GET request to /stores that retrieves a list of all stores, with optional filtering by name.
Get a specific Store: A GET request to /stores/{store_id} that retrieves a store by its ID.
Delete a Store: A DELETE request to /stores/{store_id} that removes the store with the specified ID from the database.

@app.post('/stores', tags=["Store"],response_model=schemas.Store,status_code=201)
async def create_store(store_request: schemas.StoreCreate, db: Session = Depends(get_db)):
    """
    Create a Store and save it in the database
    """
    db_store = StoreRepo.fetch_by_name(db, name=store_request.name)
    print(db_store)
    if db_store:
        raise HTTPException(status_code=400, detail="Store already exists!")

    return await StoreRepo.create(db=db, store=store_request)

@app.get('/stores', tags=["Store"],response_model=List[schemas.Store])
def get_all_stores(name: Optional[str] = None,db: Session = Depends(get_db)):
    """
    Get all the Stores stored in database
    """
    if name:
        stores =[]
        db_store = StoreRepo.fetch_by_name(db,name)
        print(db_store)
        stores.append(db_store)
        return stores
    else:
        return StoreRepo.fetch_all(db)

@app.get('/stores/{store_id}', tags=["Store"],response_model=schemas.Store)
def get_store(store_id: int,db: Session = Depends(get_db)):
    """
    Get the Store with the given ID provided by User stored in database
    """
    db_store = StoreRepo.fetch_by_id(db,store_id)
    if db_store is None:
        raise HTTPException(status_code=404, detail="Store not found with the given ID")
    return db_store

@app.delete('/stores/{store_id}', tags=["Store"])
async def delete_store(store_id: int,db: Session = Depends(get_db)):
    """
    Delete the Item with the given ID provided by User stored in database
    """
    db_store = StoreRepo.fetch_by_id(db,store_id)
    if db_store is None:
        raise HTTPException(status_code=404, detail="Store not found with the given ID")
    await StoreRepo.delete(db,store_id)
    return "Store deleted successfully!"

Run the FastAPI application using Uvicorn with a specified port and reload option.

if __name__ == "__main__":
    uvicorn.run("main:app", port=9000, reload=True)

Running the app

Now we have a functioning CRUD API for our headless e-commerce app, let’s try to run the app by typing the following command in the terminal:

python main.py

And we can access a running dev server on http://127.0.0.1:9000. With this setup, clients can easily interact with the resources using standard HTTP methods.

The Swagger UI provides a user-friendly interface for interacting with the API and exploring its endpoints, request parameters, and responses. You can use it to test API requests and ensure that your API is functioning as expected.

To be able to create an item in our e-commerce app, we first need to create a store. We can POST the /stores endpoint to create one. In this example, I will create a store called GearGenius, which is a store for tech gadgets.

Then we can use the GET method in the /stores endpoint to get all stores in the e-commerce app. Each store has a store ID, which can be used when we add items to the store.

Now we can create an item in a specific store by using the POST method in the /items endpoint. In previous steps, we have created a store with the id of 2, we can use this number in the store_id property.

Then we can check all the items in the database by using the GET method in the /items endpoint.

You can also make DELETE and PUT requests to the /items/{item_id} method to remove or update an item in the store.

Conclusion

Now we have successfully built a RESTful API using FastAPI, SQLAlchemy, and Uvicorn with an OceanBase database. The API provides endpoints for creating, retrieving, updating, and deleting items and stores in the database. We also explored how to use the Swagger UI to interact with the API and test API requests.

I have uploaded the code of this project to this GitLab repository. You can clone the project and run it in our local environment as a starter for your Python OceanBase application.

To set up, duplicate the .env.example file and rename it to .env, and then substitute the DB_URL variable based on your own database setup.

You can install all the necessary packages by running the following command:

pip install -r requirements.txt

Overall, this tutorial gives an overview of how OceanBase is used in a Python project. Moving forward, you can enhance this e-commerce platform by adding additional features and functionalities such as user management and authentication, payment integration, and searching. You can also start creating other API-based apps for OceanBase based on this boilerplate.

Build a Full Stack App with SvelteKit and OceanBase

Wayne S — Mon, 10 Apr 2023 14:02:20 +0000

In the previous post of our series about how to use OceanBase in solving real-world problems, I talked about how to install OceanBase on an AWS EC2 instance. Today, we're going to dive into the world of Node/JavaScript and demonstrate how to implement OceanBase in a full-stack app.

For this project, we'll be building a Todo app that allows users to create, read, update, and delete tasks. The app will utilize SvelteKit for the frontend and server, and OceanBase as the database.

In this article, I'll guide you through the process of setting up OceanBase and SvelteKit and integrating them to build the todo app. We'll cover everything from creating the necessary components and routes to connecting to the database and performing CRUD operations.

By the end of this article, you'll have a fully functional app that utilizes the OceanBase database and the ease of use of SvelteKit. So, let's dive in and get started!

About OceanBase and SvelteKit

SvelteKit is a framework built on top of Svelte (similar to how Next.js is to React). It is designed to build web applications of all sizes, with flexible file system-based routing. Unlike other single-page apps, SvelteKit does not compromise on SEO, progressive enhancement, or the initial load experience – but unlike traditional server-rendered apps, navigation is instantaneous for that app-like feel.

SvelteKit and OceanBase make a dynamic duo. OceanBase is a distributed relational database management system developed by Ant Group. It's designed to handle extremely large amounts of data and provide high availability and scalability.

OceanBase simplifies the process of building and deploying cloud-native applications, reducing deployment time significantly. With OceanBase’s powerful distributed architecture, you can build apps faster and more efficiently, while ensuring reliability and performance across various regions.

What we are going to build: A Todo app

We are going to build a Todo app that allows users to create, read, update, and delete tasks. The app will use SvelteKit for the frontend and the server, and OceanBase as the database. Since OceanBase is compatible with MySQL, we can use MySQL clients to connect the server and the OceanBase database instance.

This Todo app built with SvelteKit and OceanBase serves as a great example of how to use OceanBase in a project. Even though OceanBase is usually used in much more sophisticated use cases, like banking, fraud detection, and insurance, this project provides an overview of how to implement OceanBase in a full-stack app built with SvelteKit, from setting up the necessary components and routes to connecting to the database and performing CRUD operations.

The task data will be stored in an OceanBase database on an EC2 instance in AWS. Here is what the final app looks like. I will also include a GitLab repository at the end of the article so that you can try it out yourself.

The technologies used in this project include:

OceanBase, a distributed relational database
SvelteKit, a full-stack web framework based on Svelte
Tailwind CSS, a utility-first CSS framework for styling the frontend components
MySQL2, a MySQL driver for Node.js

Setting up OceanBase

In this project, I’m going to use the EC2 approach and the demo server for simplicity. In production, please see OceanBase’s official guide on deploying in a Kubernetes cluster.

After running the demo server, I created a demo user with the password “demo” that only has access to the todos database that I created for this project.

Here's an example schema for the tasks table:

CREATE TABLE tasks (
  id INT NOT NULL AUTO_INCREMENT,
  text VARCHAR(255) NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT false,
  PRIMARY KEY (id)
);

This creates a tasks table with three columns: id (an auto-incrementing integer), text (a string of up to 255 characters, which is the name of the todo item), and completed (a boolean value that defaults to false). The primary key for the table is the id column.

Setting up SvelteKit

To get started with SvelteKit, you'll need to have Node.js installed on your machine. You can download the latest version of Node.js from the official website.

Once you have Node.js installed, you can create a new SvelteKit project using the following command:

npx degit sveltejs/kit oceanbase-app
cd oceanbase-app
npm install

This will create a new SvelteKit project in the oceanbase-app directory and install all the necessary dependencies.

Now, run the following command to start the development server.

npm run dev

This will start the server and allow you to view your app in the browser at http://localhost:3000.

The user interface

Svelte is a component-based framework, meaning that we need to create a few Svelte components in the src folder. In the components folder, we need to create two Svelte components: Todo.svelte, TodoForm.svelte. The Todo component will display a list of tasks, and the TodoForm component will allow users to create and update tasks.

Here is the Todo.svelte component. It's responsible for rendering a single todo item on the screen, including a checkbox to mark the task as completed, the task text, and a "Delete" button to remove the task from the list.

The component receives a todo object as a prop, which contains information about the task, such as its ID, text, and completion status. The toggleTodoCompleted function is called when the user clicks on the checkbox, and the deleteTodo function is called when the user clicks on the "Delete" button. Both functions are imported from the todoStore.js file, which handles the CRUD operations for the todo items in the OceanBase database.

<script>
    export let todo;
    import {deleteTodo, toggleTodoCompleted} from '../stores/todoStore.js';
</script>

<li class="bg-white flex items-center shadow-sm border border-gray-200 rounded-lg my-2 py-2 px-4">
    <input
        name="completed"
        type="checkbox"
        checked={todo.completed}
        on:change={() => toggleTodoCompleted(todo.id, todo.completed)}
        class="mr-2 form-checkbox h-5 w-5"
    />
    <span
        class={`flex-1 text-gray-800  ${
            todo.completed ? 'line-through' : ''
        }`}
    >
        {todo.text}
    </span>
    <button
        type="button"
        class="text-sm bg-red-500 hover:bg-red-600 text-white py-1 px-2 rounded focus:outline-none focus:shadow-outline"
        on:click={() => deleteTodo(todo.id)}
    >
        Delete
    </button>
</li>

Here is the TodoForm.svelte component. It imports an addTodo function from the todoStore.js file, which handles the CRUD operations for the todo items in the OceanBase database. The form includes a label and input field for the user to enter the task text, and a submit button to add the task to the app.

<script>
    import { addTodo } from '../stores/todoStore.js';
    let todo = '';

    const handleSubmit = () => {
        addTodo(todo);
        todo = '';
    };
</script>

<form class="form my-6" on:submit|preventDefault={handleSubmit}>
    <div class="flex flex-col text-sm mb-2">
        <label for="todo" class="font-bold mb-2 text-gray-800 "> Todo </label>
        <input
            type="text"
            name="todo"
            bind:value={todo}
            placeholder="ex. Learn about authentication in Next.js"
            class="appearance-none shadow-sm border border-gray-200 p-2 focus:outline-none focus:border-gray-500 rounded-lg "
        />
    </div>
    <button
        type="submit"
        class=" w-full shadow-sm rounded bg-green-500 hover:bg-green-600 text-white py-2 px-4"
    >
        Submit
    </button>
</form>

Then we need to display all todos on the page. We will use the default index page to do so. In the /src/route/index.svelte page, we can iterate all todos and display them.

<script>
    import Todo from '../components/Todo.svelte';
    import TodoForm from '../components/TodoForm.svelte';
    import { todos } from '../stores/todoStore.js';
</script>

<main>
    <h1 class="text-2xl font-bold text-center text-gray-800 md:text-3xl">
        Todo App
    </h1>
    <TodoForm />
    {#each $todos as todo (todo.id)}
        <Todo {todo} />
    {/each}
</main>

In the src/stores/todoStore.js file, we have the functions for adding, deleting, and updating tasks. These functions connect to the OceanBase database and perform the necessary operations to create, read, update and delete tasks.

The loadTodos function loads the tasks from the database and updates the todos store with the data. The addTodo, deleteTodo, and toggleTodoCompleted functions connect to the OceanBase database and perform the necessary operations to create, delete and update tasks. By using these functions, we can perform CRUD operations on tasks through the UI.

Note that for security reasons, these function talks to the /api/todos API route, not directly with the database. In this case, database queries run safely on the server side. I will dive into the API routes in the next section of this article.

import { writable } from 'svelte/store';

export const todos = writable([]);

export const loadTodos = async () => {
    try {
        const response = await fetch('/api/todos');
        const data = await response.json();
        todos.set(data);
    } catch (error) {
        console.error('Error fetching data:', error);
    }
};
loadTodos();

export const addTodo = async (text) => {
    try {
        const response = await fetch('/api/todos', {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json',
            },
            body: JSON.stringify({ text: text, completed: false }),
        });

        const data = await response.json();
        todos.update((currentTodos) => [...currentTodos, data]);
    } catch (error) {
        console.error('Error adding todo:', error);
    }
};

export const deleteTodo = async (id) => {
    try {
        await fetch(`/api/todos/${id}`, {
            method: 'DELETE',
        });

        todos.update((currentTodos) =>
            currentTodos.filter((todo) => todo.id !== id)
        );
    } catch (error) {
        console.error('Error deleting todo:', error);
    }
};

export const toggleTodoCompleted = async (id, currentState) => {
    try {
        const response = await fetch(`/api/todos/${id}`, {
            method: 'PUT',
            headers: {
                'Content-Type': 'application/json',
            },
            body: JSON.stringify({ completed: !currentState }),
        });

        const data = await response.json();
        todos.update((currentTodos) =>
            currentTodos.map((todo) => (todo.id === id ? data : todo))
        );
    } catch (error) {
        console.error('Error toggling todo completed status:', error);
    }
};

Connecting SvelteKit and OceanBase

Now let’s talk about the server-side code. The server side consists of two parts: a database module that sets up a connection between the server and our running OceanBase instance, and an API route that performs CRUD actions in the OceanBase database from the server.

In the lib/db/oceanbase.js file, we can use the MySQL2 client to set up a connection with OceanBase and then export the connection for our API routes to consume. It is always a good practice to store your database credentials in a .env file and use the dotenv package to access those values.

import mysql from 'mysql2/promise';
import * as dotenv from 'dotenv';
dotenv.config();
export const oceanbaseConnection = await mysql.createConnection({
    host: process.env.HOST,
    port: 2881,
    user: process.env.USERNAME,
    database: 'todos',
});

In the src/routes/api folder, we have an index.js and a [id].js file. These files contain the API endpoints that our app will utilize to interact with the OceanBase database. The index.js file handles the CRUD operations for all todos, while the [id].js file handles CRUD operations for a single todo by ID.

In the index.js file, we first need to import the oceanbaseConnection object from our database module and write the GET and POST request functions. The two functions will allow the frontend to get all todos from the database and insert a new todo item into the database.

import { oceanbaseConnection } from '../../../lib/db/oceanbase';

For the GET request, we need to make a SELECT everything SQL query to the database’s tasks table.

export async function get() {
    let results = await oceanbaseConnection
        .query('SELECT * FROM tasks')
        .then(function ([rows, fields]) {
            console.log(rows);
            return rows;
        });

    return {
        body: results,
    };
}

For the POST method, we need to get the text and completed properties from the request and corporate them in an INSERT SQL statement that inserts a row into the tasks table.

export async function post(request) {
    console.log(request);
    const { text, completed } = request.body;
    let newTask = await oceanbaseConnection
        .query('INSERT INTO tasks (text, completed) VALUES (?, ?)', [
            text,
            completed,
        ])
        .then(function ([result]) {
            return { id: result.insertId, text: text, completed: completed };
        });
    return {
        body: newTask,
    };
}

After adding a few todo items from the UI, we can see the following rows in our OceanBase database:

Similarly in the [id].js file, we need to set up a DELETE and a PUT function so that the frontend can perform a delete action, or toggle the completed status of a todo.

First, let's also import the oceanbaseConnection object from the database module.

import { oceanbaseConnection } from '../../../lib/db/oceanbase';

In the [id].js file, a DELETE method is implemented to delete an item from the tasks table. This is achieved by using the DELETE FROM SQL statement using the id parameter from the request.

export async function del(req) {
    const { id } = req.params;
    try {
        await oceanbaseConnection.query('DELETE FROM tasks WHERE id = ?', [id]);
        return {
            status: 200,
            body: { message: `Todo with id ${id} deleted successfully.` },
        };
    } catch (error) {
        console.error('Error deleting todo:', error);
        return {
            status: 500,
            body: { message: 'Error deleting todo. Please try again later.' },
        };
    }
}

A PUT method is implemented to update the completed status of a todo item in the tasks table. We will use the UPDATE statement to update the item in the database.


export async function put(req) {
    const { id } = req.params;
    const { completed } = req.body;

    try {
        await oceanbaseConnection.query(
            'UPDATE tasks SET completed = ? WHERE id = ?',
            [completed, id]
        );
        const [updatedTodo] = await oceanbaseConnection.query(
            'SELECT * FROM tasks WHERE id = ?',
            [id]
        );
        return {
            status: 200,
            body: JSON.stringify(updatedTodo),
        };
    } catch (error) {
        console.error('Error toggling todo completed status:', error);
        return {
            status: 500,
            body: {
                message:
                    'Error toggling todo completed status. Please try again later.',
            },
        };
    }
}

Conclusion

I have uploaded a project to GitLab that you can clone and use to start your own project using SvelteKit and OceanBase. You can find the project at https://gitlab.com/waynering/oceanbase-sveltekit. Simply clone the repository and follow the instructions in the README file to set up the project in your local environment.

To run the application, you need to modify the .env.example file using your own database credentials and rename the file to a .env file.

In conclusion, building a full-stack app with SvelteKit and OceanBase can be a great way to learn how to use OceanBase in a project. By setting up the necessary components and routes, connecting to the database, and performing CRUD operations, you can gain a better understanding of how to use OceanBase in a practical setting. With the help of this Todo app, you can explore the features of OceanBase and see how it works in conjunction with your Node applications.

If you need any help installing or using OceanBase, you can reach out to the OceanBase team on Twitter (@OceanBaseDB) or ask a question on StackOverflow with the "OceanBase" tag.

How to install OceanBase on an AWS EC2 instance [Step-by-step guide]

Wayne S — Fri, 07 Apr 2023 06:13:40 +0000

In the world of big data, distributed databases play an important role. As data generated and processed by companies grow exponentially, there is a growing need for scalable and reliable database management solutions. An increasing number of companies are already shifting to distributed databases. OceanBase, a distributed database that offers capabilities in both transactions and analytics, as well as compatibility with MySQL, is rapidly gaining traction among data-driven teams.

Developed by Ant Group, OceanBase has been the driving force behind Alibaba’s Singles Day shopping event for nine consecutive years, managing billions of dollars in transactions annually.

As an enterprise-grade DBMS, OceanBase also provides a community edition that allows developers to experience its full potential without a complicated setup.

In this article, I will walk you through how to install OceanBase on an AWS EC2 instance. This is the first in a series of articles where I demonstrate how to integrate OceanBase into your applications.

Prerequisites

Before we jump into the installation process of OceanBase, we need to make sure that the EC2 instance we spin up in AWS meets the hardware requirements of OceanBase as stated in the documentation.

According to the documentation, a server with at least four cores and 10 GiB of RAM (preferably 16 GiB) is required to install OceanBase. The recommended operating systems are Red Hat Enterprise Linux Server, CentOS Linux, and Anolis OS.

In this walkthrough, I will use the CentOS 8 operating system and the a1.x2large EC2 instance type, which comes with 8 vCPUs and 16 GiB of RAM. Since OceanBase recommends having a disk space of “four times the memory size or more,” I’ll mount a 100-GiB SSD storage volume to the instance.

Once we spun up our EC2 instance and connected to it, we can get started and install OceanBase.

Install OceanBase

The first step is to update the yum package. Run the following bash command

sudo yum update

The easiest way to install OceanBase on a server is to use the official all-in-one installation package, which will install OBD, OceanBase DBMS, OBProxy, obagent, Grafana, and Prometheus on your machine. You can use the all-in-one installation by running the following code:

bash -c "$(curl -s <https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/oceanbase-all-in-one/installer.sh>)"

This process may take up to 5 minutes. Once everything is installed, you will see an “Install Finished” screen like this.

Alternatively, you can use Docker to quickly start an OceanBase server with one line of code:

docker run -p 2881:2881 --name obstandalone -d oceanbase/oceanbase-ce

For simplicity, I will be using the demo instance we just installed using the all-in-one package in the remainder of this article.

The next step is to activate the setup environment by typing the following command:

source ~/.oceanbase-all-in-one/bin/env.sh

Then we can run the obd demo command to start demo instances of the services we just installed from the all-in-one package.

obd demo

Please note that the obd demo command is only recommended to be used for demo purposes and should not be used for production.

Heads up: update your ulimits configuration

After running this command you may encounter an error saying “open files must not be less than 20000 (Current value: 1024)” with the error code OBD-1007. According to the OceanBase documentation, this error message means our ulimits configuration hasn’t met the official requirements.

To change the ulimits configuration, we need to edit the /etc/security/limits.conf file. ulimit is a built-in Linux configuration that allows viewing or limiting system resource amounts that individual users consume. For demo purposes, we will increase the limits to a large number or unlimited. But in production, it is recommended that you follow the best practices of setting ulimits.

* soft nofile 655360
* hard nofile 655360
* soft nproc 655360
* hard nproc 655360
* soft core unlimited
* hard core unlimited
* soft stack unlimited
* hard stack unlimited

To make the configuration work, you need to log out of your session and log in again. After that, you can check if the configuration is correct by running the following command:

ulimit -a

If the reconfiguration is successful, you may see the following screen.

Now let’s run the obd demo command again. And bingo, we got a running OceanBase server. The demo command will also provide credentials for each service.

+---------------------------------------------+
|                   observer                  |
+-----------+---------+------+-------+--------+
| ip        | version | port | zone  | status |
+-----------+---------+------+-------+--------+
| 127.0.0.1 | 4.0.0.0 | 2881 | zone1 | ACTIVE |
+-----------+---------+------+-------+--------+
obclient -h127.0.0.1 -P2881 -uroot -Doceanbase -A
+--------------------------------------------------+
|                     obagent                      |
+--------------+-------------+------------+--------+
| ip           | server_port | pprof_port | status |
+--------------+-------------+------------+--------+
| 172.*.*.42 | 8088        | 8089       | active |
+--------------+-------------+------------+--------+
+-----------------------------------------------------+
|                      prometheus                     |
+--------------------------+------+----------+--------+
| url                      | user | password | status |
+--------------------------+------+----------+--------+
| <http://172>.*.*.42:9090 |      |          | active |
+--------------------------+------+----------+--------+
+-------------------------------------------------------------------+
|                              grafana                              |
+--------------------------------------+-------+-----------+--------+
| url                                  | user  | password  | status |
+--------------------------------------+-------+-----------+--------+
| <http://172>.*.*.42:3000/d/oceanbase | admin | oceanbase | active |
+--------------------------------------+-------+-----------+--------+
+---------------------------------------------+
|                   obproxy                   |
+-----------+------+-----------------+--------+
| ip        | port | prometheus_port | status |
+-----------+------+-----------------+--------+
| 127.0.0.1 | 2883 | 2884            | active |
+-----------+------+-----------------+--------+
obclient -h127.0.0.1 -P2883 -uroot -Doceanbase -A
demo running

Connect to your OceanBase client

Now let’s try to connect to our OceanBase instance using OBClient, OceanBase’s official CLI based on the MariaDB CLI, which can be used to connect to OceanBase Server/Proxy.

Since OBClient is already installed by the all-in-one package, you can simply run the following code to connect to it:

obclient -h127.0.0.1 -P2881 -uroot -Doceanbase -A

If you already have experience using the MariaDB CLI or the MySQL CLI, OBClient shouldn’t look alien to you.

In this walkthrough, I will try to create a database about ocean life using OceanBase (pun intended). To get started, I will create a database called MarineLife in our demo OceanBase instance by running the following code:

CREATE DATABASE MarineLife DEFAULT CHARACTER SET UTF8 READ WRITE;

Now run SHOW DATABASES and we will see the newly created database.

Now that we have created a database in OceanBase, we can try inserting some data into the database using SQL.

Running SQL in OceanBase

First, let’s select the newly created MarineLife database in OBClient.

USE MarineLife;

To get started, I want to create a table in the MarineLife database called Species, which has properties like species_name, common_name, habitant, and conservation_status. To do that, we need to create a table and its scheme:

CREATE TABLE Species (
id INT AUTO_INCREMENT PRIMARY KEY,
species_name VARCHAR(255) NOT NULL,
common_name VARCHAR(255),
habitat VARCHAR(255),
conservation_status ENUM('Least Concern', 'Near Threatened', 'Vulnerable', 'Endangered', 'Critically Endangered', 'Extinct in the Wild', 'Extinct')
);

Now let’s populate the table with some dummy data:

INSERT INTO Species (species_name, common_name, habitat, conservation_status)
VALUES ('Carcharodon carcharias', 'Great White Shark', 'Coastal and offshore waters', 'Vulnerable'),
('Delphinus delphis', 'Short-beaked Common Dolphin', 'Open seas', 'Least Concern'),
('Chelonia mydas', 'Green Sea Turtle', 'Tropical and subtropical oceans', 'Endangered'),
('Eubalaena glacialis', 'North Atlantic Right Whale', 'North Atlantic Ocean', 'Critically Endangered');

Now we can check the database table:

SELECT * FROM Species;

And we will see the data we just inserted into the table.

Connect OceanBase from TablePlus

You can use TablePlus to manage your database from your local machine. Before you do that, you need to change your AWS EC2 security group to allow inbound access to the 2881 port, which is the default OceanBase port.

To do that, select the security group used by your EC2 instance and edit the Inbound rules to allow access to the 2881 port.

Please be aware that the above setting is only for demo purposes. In production, you may want to restrict the allowed source to a limited IP range.

In most cases, you don’t want to remotely access your database using the root user. We can create a new user called demo that only has access to the MarineLife database.

To create a new user, we will use the following command:

CREATE USER 'demo'@'localhost' IDENTIFIED BY 'password';

Next, we will grant the user permission to access the MarineLife database:

GRANT ALL PRIVILEGES ON MarineLife.* TO 'demo'@'localhost';

Now we can connect to the OceanBase instance from TablePlus using the demo user credentials. Once connected, we can view and manage the MarineLife database and its tables from a user-friendly graphical interface.

Host: YOUR_EC2_PUBLIC_IP_ADDRESS
Port: 2881
User: demo
Password: password

And I have added a few more rows to the database.

Conclusion

In this article, we have learned how to install OceanBase on an AWS EC2 instance and connect to it using OBClient and TablePlus. We have also created a database with a table and inserted data into it.

OceanBase offers a scalable, dependable, and efficient distributed database management system that is compatible with MySQL, making it an ideal choice for processing vast amounts of data.

In the next article of this series, we will explore more advanced features of OceanBase and how to integrate them into your applications in various languages.

Forem: Wayne S

Exploring the Implementation of Graph Data in OceanBase

What is graph data and why it is important

Why OceanBase users should care about graph data?

Modeling of graph data in OceanBase

Some sample queries

Breadth-First Search (BFS)

Detecting Circular Transactions

Identifying High-Frequency Transaction Pairs

Uncovering Unusually Large Transactions

Performance

Graph data in OceanBase: What’s next?

Create a headless CMS using OceanBase and TypeScript: A step-by-step tutorial

What is headless CMS?

Building a headless CMS

Setting up the project

Connection to OceanBase

Setting up CRUD actions for the CMS

Add a tag

Create a post

Get all posts

Get a post by slug

Get all posts by tag

Setting up the API server in Express

Testing the API

Conclusion

A beginner’s guide to sentiment analysis using OceanBase and spaCy

Setting up the environment

Explore the raw data

Setting up s**entiment analysis functions**

Sentiment analysis of the data

Sentiment distribution - an overview

Understanding Score Distribution by Sentiment

Boxplot of polarity by sentiment

Time Series Analysis

Correlation Matrix

Word cloud of positive and negative reviews

Sentiment by Day of Week

Length of Review vs. Helpfulness

Correlation between Subjectivity and Score

Find out the most beloved products

Conclusion

Using OceanBase to build a real-time user analytics pipeline

Tools used in this project

Setting up the GitPod environment

Initialize the image

Metabase setup

Jitsu setup

OceanBase setup

Ports Configuration

Running the GitPod environment

Setting up Jitsu and Metabase

Jitsu Setup

Metabase Setup

Capturing events in a demo app

Setting Up the demo app

Using the Jitsu SDK in the demo app

Use the track function

Data visualization in Metabase

Conclusion

Create a Langchain alternative from scratch using OceanBase

TL:DR:

Some reflections on the project

The project

Getting started

Setting up OceanBase

Training on the docs

Fetching the documentation

Transforming and storing the vectors

Asking the docs

Setting up the API server

Training Endpoint

Conclusion

Use OceanBase in Node.js: Build a CRM with Sequelize and Express

What we are going to build: A mini-CRM system

Setting up the database

Setting up the project

Configuring Sequelize

Configuring the Database Connection

Defining data models

Setting up sentiment analysis functions