Forem: Matheus Farias de Oliveira Matsumoto

Como fazer o upload de imagens Docker para a Huawei Cloud usando o GitHub Actions

Matheus Farias de Oliveira Matsumoto — Tue, 30 Dec 2025 20:26:00 +0000

O GitHub Actions é uma ferramenta nativa do GitHub que permite a orquestração de workflows diretamente na própria plataforma. Esses workflows são definidos por arquivos YAML, que descrevem de forma declarativa o passo a passo de uma automação. Eles podem ser acionados a partir de eventos no repositório, como a abertura de um pull request, um commit ou a criação de um release.

Em pipelines de CI/CD, o GitHub Actions ajuda a reduzir processos manuais e a garantir a execução consistente das mesmas etapas em todos os ambientes. No contexto de provisionamento de recursos em nuvem, é possível utilizá-lo para criar um container registry por meio do Terraform, estabelecer a conexão com esse registry e enviar as imagens de contêiner para ele - tudo de forma automatizada por meio de workflows.

Neste blog, iremos demonstrar como criar um workflow capaz de provisionar um container registry na Huawei Cloud - o SWR (SoftWare Repository for Contianer) - e enviar imagens de contêiner para esse registry de forma automatizada.

1. Criando um OBS Bucket para armazenar o arquivo tfstate

Primeiramente, precisamos configurar nosso Terraform para armazenar o arquivo tfstate para um bucket OBS. O tfstate é um arquivo do Terraform que mapeia os recursos de infraestrutura com as configurações declaradas, permitindo que o Terraform saiba o que criar, atualizar ou destruir, armazenando o "estado" atual do seu ambiente.

Por padrão, é um arquivo JSON local (terraform.tfstate), mas para trabalho em equipe e segurança, deve-se usar armazenamento remoto (backends), como o OBS.

Para acessar a página do OBS na Huawei Cloud:

Clique em Service List (botão vermelho) localizado no canto superior esquerdo da console da Huawei Cloud;
Procure por "OBS";
Clique em Object Storage Service.

Na página do serviço OBS, clique no botão Create Bucket localizado no canto superior direto.

Forneça um nome para o bucket, selecione a opção Standard e a política do bucket como private. Por fim, clique em Create Now no canto inferior direito da tela.

2. Preparando o repositório e as credenciais no GitHub

O segundo passo é preparar o repositório no GitHub. No seu repositório, clique em Settings > Secrets and variables > Actions para acessar a página para configuração de Secrets e Variables.

No GitHub, devemos configurar os seguintes Secrets, que serão utilizados pelo workflow para autenticação na Huawei Cloud:

HWC_ACCESS_KEY
HWC_SECRET_KEY

Além disso, utilizamos Repository Variables para armazenar valores reutilizáveis e não sensíveis, como:

HWC_OBS_BUCKET_NAME (bucket usado como backend remoto do Terraform)

3. Configurando o terraform para cirar o SWR

Com o repositório configurado, o próximo passo é provisionar o Container Registry da Huawei Cloud (SWR). A configuração é dividida em dois níveis:

Configuração principal do Terraform e do provider;
Módulo responsável pela criação do registry.

Configuração principal do Terraform (`main.tf`)

terraform {
  required_providers {
    huaweicloud = {
      source  = "huaweicloud/huaweicloud"
      version = "1.82.2"
    }
  }

Aqui especificamos o provider da Huawei Cloud e fixamos a versão para garantir consistência entre ambientes e execuções do pipeline.

backend "s3" {
  key    = "terraform.tfstate"
  region = "sa-brazil-1"

  endpoints = {
    s3 = "https://obs.sa-brazil-1.myhuaweicloud.com"
  }

  skip_region_validation      = true
  skip_credentials_validation = true
  skip_metadata_api_check     = true
  skip_requesting_account_id  = true
  skip_s3_checksum            = true
}

Nesse projeto, o state do Terraform é armazenado em um bucket OBS da Huawei Cloud, utilizando o backend S3-compatible.

provider "huaweicloud" {
  region     = var.region
  access_key = var.access_key
  secret_key = var.secret_key
}

O provider utiliza variáveis para região e credenciais. Essas variáveis são injetadas automaticamente pelo GitHub Actions por meio de variáveis de ambiente (TF_VAR_*). Assim, nenhuma credencial sensível fica exposta no código.

module "registry" {
  source = "./modules/registry"
  organization_name = var.organization_name
  repo_name      = var.repo_name
}

Aqui chamamos o módulo responsável por criar o SWR. Esse módulo encapsula toda a lógica relacionada ao registry, tornando o código principal mais limpo e fácil de manter. Note que é necessário criar também arquivos variables.tf onde serão armazenadas as variáveis.

Módulo do Container Registry (`modules/registry/main.tf`)

O módulo define a criação do SWR na Huawei Cloud.

terraform {
  required_providers {
    huaweicloud = {
      source  = "huaweicloud/huaweicloud"
      version = "1.82.2"
    }
  }
}

resource "huaweicloud_swr_organization" "org" {
  name = var.organization_name
}

resource "huaweicloud_swr_repository" "repo" {
  organization = huaweicloud_swr_organization.org.id
  name         = var.awesome_repo
}

4. Criando o Dockerfile (`docker/Dockerfile`)

Precisamos agora criar o arquivo Dockerfile que irá definir nossa imagem do container. Nossa imagem utilizará o NGINX mas com uma página própria nossa.

Crie uma pasta docker e, dentro dela, adicione o arquivo Dockerfile:

FROM nginx:alpine

COPY ./docker/index.html /usr/share/nginx/html/index.html

Na mesma pasta, crie o arquivo index.html com alguma mensagem no corpo do arquivo.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Docker Test</title>
</head>
<body>
    Hello from Huawei Cloud Brasil Team!
</body>
</html>

5. GitHub Actions

Agora, vamos configurar nossos workflows para aplicar o terraform e fazer o push da imagem para o registry. Teremos um workflow para a criação do registry e um outro workflow para enviar a imagem para o SWR.

Provisionando o SWR com Terraform

O primeiro workflow é responsável por criar a organização e o repositório no Software Repository for Container (SWR). Crie o arquivo .github/workflows/terraform-registry.yaml com o seguinte conteúdo:

name: Terraform – Create SWR

on:
  push:
    branches: [ "main" ]
  workflow_dispatch:
    inputs:
      approve:
        description: "Approve Terraform Apply?"
        required: true
        default: "skip"
        type: choice
        options:
          - skip
          - apply

env:
  # Variáveis utilizadas pelo provider da Huawei Cloud
  TF_VAR_access_key: ${{ secrets.HWC_ACCESS_KEY }}
  TF_VAR_secret_key: ${{ secrets.HWC_SECRET_KEY }}

  # Variáveis necessárias para o backend remoto no OBS (S3-compatible)
  AWS_ACCESS_KEY_ID: ${{ secrets.HWC_ACCESS_KEY }}
  AWS_SECRET_ACCESS_KEY: ${{ secrets.HWC_SECRET_KEY }}
  AWS_RESPONSE_CHECKSUM_VALIDATION: when_required
  AWS_REQUEST_CHECKSUM_CALCULATION: when_required

jobs:
  registry:
    name: Terraform – Registry
    runs-on: ubuntu-latest

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Setup Terraform
        uses: hashicorp/setup-terraform@v3
        with:
          terraform_version: 1.6.6

      - name: Terraform Init
        run: terraform init -backend-config="bucket=${{ vars.HWC_OBS_BUCKET_NAME }}"

      - name: Apply SWR module
        run: terraform apply -auto-approve -target=module.registry

Esse workflow irá:

Fazer o checkout do repositório;
Instalar a versão definida do Terraform;
Inicializar o backend remoto utilizando um bucket OBS;
Aplicar somente o módulo responsável pelo registry, utilizando o parâmetro -target.

O uso do -target=module.registry garante que apenas os recursos relacionados ao SWR sejam criados, evitando a aplicação de outros recursos que possam existir futuramente no projeto.

Adquirindo a senha de login do Docker no SWR

Após a execução bem-sucedida do primeiro workflow, o Container Registry já estará disponível na Huawei Cloud. O próximo passo é gerar a senha de autenticação do Docker, que será utilizada para enviar imagens ao registry.

Primeiro, acesse a página do SWR na console da Huawei.

Em seguida, na página de Dashboard, clique no botão Generate Login Command, depois, para um login temporário, em Temporary Login Command. Copie a senha após o parâmetro -p do comando gerado.

No Secrets do GitHub, adicione mais uma secret com o nome DOCKER_LOGIN_PASS e atribua a ela o valor da senha.

Também crie duas variáveis em Variables para o nome da organização e do repositório onde será armazenada a imagem: ORGANIZATION_NAME e REPO_NAME.

Build e Push da imagem Docker

Com o registry criado e a senha de login configurada no GitHub, podemos automatizar o build e o push da imagem Docker. Crie o arquivo .github/workflows/docker-build-push.yaml com o seguinte conteúdo:

name: Build & Push Image to SWR

on:
  workflow_dispatch:

jobs:
  build:
    name: Build & Push Image
    runs-on: ubuntu-latest

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Login to SWR
        run: |
          docker login \
            -u sa-brazil-1@${{ secrets.HWC_ACCESS_KEY }} \
            -p ${{ secrets.DOCKER_LOGIN_PASS }} \
            swr.sa-brazil-1.myhuaweicloud.com

      - name: Build Docker image
        run: |
          docker build \
            -f docker/Dockerfile \
            -t ${{ vars.SWR_URL }}/${{ vars.ORGANIZATION_NAME }}/${{ vars.REPO_NAME }}:latest .

      - name: Push Docker image
        run: |
          docker push \
            ${{ vars.SWR_URL }}/${{ vars.ORGANIZATION_NAME }}/${{ vars.REPO_NAME }}:latest

Para executar:

Vá até Actions no GitHub;
Selecione o workflow Build & Push Image to SWR;
Clique em Run workflow.

Uma vez executado com sucesso, a imagem será enviada para o SWR, podendo ser utilizada por workloads no CCE e no CCI da Huawei Cloud.

6. Conclusão

Com essa abordagem, conseguimos separar claramente as responsabilidades:

Terraform é utilizado para provisionar a infraestrutura de forma declarativa;
GitHub Actions automatiza tanto o provisionamento quanto o ciclo de build e entrega da imagem;
O SWR passa a ser um registry centralizado e reutilizável para múltiplas pipelines.

Essa separação em dois workflows reflete um cenário real de produção, facilita a manutenção do pipeline e reduz acoplamentos desnecessários entre infraestrutura e entrega de aplicações.

Os repositórios do GitHub contendo pipelines semelhantes e mais exemplos de terraform podem ser encontrados em:

Two Step Terraform HWC: https://github.com/MatheusFarias03/TwoStepTerraformHWC
Huawei Cloud Terraform with GitHub Actions: https://github.com/gutierrezps/huaweicloud-terraform-github-actions

Efficient Representation of Relationships with Graph Databases

Matheus Farias de Oliveira Matsumoto — Tue, 19 Sep 2023 18:48:10 +0000

Data has become the lifeblood of businesses and organizations all around the world in the digital era. It is not enough to gather massive volumes of data; it is also necessary to get valuable insights from it. Using graph databases for the effective depiction of relationships is one of the most potent approaches to access these insights.

The Power of Relationships

Data is almost never isolated. In most situations, information is linked together by relationships. Consider a social network in which individuals are connected as friends, or a transportation network in which cities are linked by highways. Understanding and analyzing these interconnections is critical for gaining important insights and making reasonable choices.

For many years, traditional relational databases have served us well, but they are not always the greatest tool for working with complicated relationship data. Graph databases thrive in this context. They are designed specifically for modeling, storing, and querying relationships.

Graph Databases: The Basics

At the core of graph databases are two fundamental building blocks:

Nodes: nodes symbolize entities or data points.
Edges: connect nodes and represent the relationship between them.

Efficient Querying

Graph databases are designed to make it easy to examine relationships. Unlike relational databases, which rely on complex JOIN operations to traverse the graph, graph databases are able to track edges directly. This results in very fast searches, particularly for tasks such as determining the shortest path between nodes or discovering patterns within a network.

While graph databases excel at representing and querying relationships, many organizations rely heavily on traditional relational databases for their structured data needs. So, what if there was a way to seamlessly combine the strengths of both worlds? Enter Apache AGE (A Graph Extension).

AGE: Bridging the Gap Between Relational and Graph Databases

AGE stands for A Graph Extension, and it's designed to bring the power of graph databases to PostgreSQL, one of the most popular relational database management systems (RDBMS) out there. But what exactly does this mean, and why is it significant?

Unified Storage: With AGE, users can work with both relational and graph data models in the same database. This means you can make use of ANSI SQL for structured data and openCypher, a popular graph query language, for graph data within the same database.
Robustness of PostgreSQL: Built on top of PostgreSQL, AGE inherits its robustness and feature set. This includes support for ACID (Atomicity, Consistency, Isolation, Durability) transactions, multi-version concurrency control (MVCC), stored procedures, triggers, constraints, and more. It's a battle-tested database with a long track record of reliability.
Smooth Transition: For users with a background in relational databases, adopting AGE is a breeze. There's no need for complex data migrations or extensive retooling. You can seamlessly integrate graph data analytics into your existing PostgreSQL environment.

In a nutshell, Apache AGE is the future of data management—a future in which there is no need to choose between a relational database and a graph database. You can have both, and they can be combined into a single package.

Links

AGE Website
AGE GitHub
AGE YouTube

Join Our Webinar: Plug-in Graph Analytics on PostgreSQL with Apache AGE

Matheus Farias de Oliveira Matsumoto — Fri, 08 Sep 2023 21:24:09 +0000

Are you ready to unlock the potential of PostgreSQL and graph technology?

Have you ever wondered how PostgreSQL and Graph technologies might be used to create enterprise data management solutions?

Join Ibrar Ahmed, an accomplished PostgreSQL consultant, and Eya Badal Abdisho, Chair of the Apache AGE PMC, for a webinar on maximizing the open source potential of PostgreSQL and graph technology databases!

In the webinar we will:

Discover the reasons behind PostgreSQL's rise to the top and why companies are choosing it over other database solutions;
Explore the benefits of using a graph database as an extension of PostgreSQL and how it can enhance your data management capabilities;
Learn about the various PostgreSQL extensions available and how to effectively integrate them into your data ecosystem;
Get practical tips and strategies for seamlessly integrating PostgreSQL and its extensions into your organization's data infrastructure.

Link: https://us06web.zoom.us/webinar/register/WN_PohMAFj0RJuJPAw8bTq1MA#/registration

Date: Sep 14, 2023 - 5:00 PM GMT

Importing CSV to AGE

Matheus Farias de Oliveira Matsumoto — Sun, 25 Jun 2023 17:22:03 +0000

In this world full of information, relational databases have been the go-to solution for storing and querying structured data. However, as the need for more complex relationships and connectedness arises, graph databases have gained popularity. PostgreSQL, a powerful relational database, offers an extension called AGE, which combines the benefits of relational and graph databases. In this blog post, we will explore how to import CSV files into AGE with Python. Keep in mind that while the script is running, you will need also to have postgres running and a graph created in the database.

Initial CSV File

First, let's suppose that your current csv file is not yet formatted for AGE. For example, we have a simple csv containing the information of online purchases. This file stores the name of the product, it's price, description, the id of the store that sold the product, the id of the user that purchased the product, and the id of the product. Name the file ProductsData.csv.

product_name,price,description,store_id,user_id,product_id
iPhone 12,999,"Apple iPhone 12 - 64GB, Space Gray",1234,1001,123456
Samsung Galaxy S21,899,"Samsung Galaxy S21 - 128GB, Phantom Black",5678,1002,789012
AirPods Pro,249,"Apple AirPods Pro with Active Noise Cancellation",1234,1003,345678
Sony PlayStation 5,499,"Sony PlayStation 5 Gaming Console, 1TB",9012,1004,901234

Reading the CSV File

Now, create a python file named AGECSV.py where all of our work will be done. Create a main() function where we will initialy call the read_csv() function and it will be where the main execution of the program occurs.

def main():

    # Specify the path to your CSV file.
    csv_file = 'ProductsData.csv'
    read_csv(csv_file)

main()

Above the main() function we will define the read_csv() function.

import csv

def read_csv(csv_file):

    # Read the CSV file.
    with open(csv_file, 'r') as file:
        reader = csv.reader(file)

        # Get the header row and go to the next row.
        header = next(reader)
        print(header)

        # Print each row.
        for row in reader:
            print(row)

This function will open the csv_file and create an csv.reader object, which then it will allow us to iterate over the rows of the CSV file. Running this script you'll see the following output:

['product_name', 'price', 'description', 'store_id', 'user_id', 'product_id']
['iPhone 12', '999', 'Apple iPhone 12 - 64GB, Space Gray', '1234', '1001', '123456']
['Samsung Galaxy S21', '899', 'Samsung Galaxy S21 - 128GB, Phantom Black', '5678', '1002', '789012']
['AirPods Pro', '249', 'Apple AirPods Pro with Active Noise Cancellation', '1234', '1003', '345678']
['Sony PlayStation 5', '499', 'Sony PlayStation 5 Gaming Console, 1TB', '9012', '1004', '901234']

In order to load the CSV file to AGE, it must be formatted in the following way:

# Nodes

id,property1,property2,...,propertyN
123,content1,content2,...,contentN
124,content1,content2,...,contentN

# Edges

start_id,start_vertex_type,end_id,end_vertex_type, (properties goes here, just like above)
123,LabelStart,124,LabelEnd

Creating the CSV File

We will create a new function to generate the CSV file, but first, lets refactor the read_csv() function. Now, it will return the read rows from the file.

def read_csv(csv_file):

    with open(csv_file, 'r') as file:
        reader = csv.reader(file)
        rows = list(reader)

    return rows

Bellow it's how we define the create_csv() function.

def create_csv(csv_file):

    # Define the new header and property order.
    new_header = ['id', 'product_name', 'description', 'price', 'store_id', 'user_id']
    property_order = [5, 0, 2, 1, 3, 4]  # Reorder the properties accordingly.

    # Read the original CSV file.
    rows = read_csv(csv_file)

    # Create a new CSV file with the desired format.
    new_csv_file = 'products.csv'
    with open(new_csv_file, 'w', newline='') as file:
        writer = csv.writer(file)

        # Write the new header.
        writer.writerow(new_header)

        # Write each row with reordered properties.
        for row in rows[1:]:
            new_row = [row[i] for i in property_order]
            writer.writerow(new_row)

    print(f"New CSV file '{new_csv_file}' has been created with the desired format.")

This function reorders the properties based on the property_order list and writes the rows into the new file using the writer.writerow() method. Run the function and you'll have the new csv file in hands.

Loading the data to AGE

To send the new CSV file to AGE, we can open postgres and send the data manually there or continue with our python script an send tha data automatically. We are going to do the latter.

First, import these two other libraries: psycopg2 and age. After this, update the main() function as shown bellow.

def main():

    csv_file = 'ProductsData.csv'
    create_csv(csv_file)

    new_csv_file = 'products.csv'
    GRAPH_NAME = 'csv_test_graph'
    NODE_LABEL = 'Products'

    conn = psycopg2.connect(host="localhost", port="5432", dbname="dbname", user="username", password="password")
    age.setUpAge(conn, GRAPH_NAME)

    path_to_csv = '/path/to/csv/' + new_csv_file
    load_csv_nodes(path_to_csv, GRAPH_NAME, conn, NODE_LABEL)

main()

The updated main() function now will connect to the database and to AGE's graph. The load_csv_nodes() function will have the following code:

def load_csv_nodes(csv_file, graph_name, conn, node_label):

    with conn.cursor() as cursor:
        try :
            cursor.execute("""LOAD 'age';""")
            cursor.execute("""SET search_path = ag_catalog, "$user", public;""")
            cursor.execute("""SELECT create_vlabel(%s, %s);""", (graph_name, node_label,) )
            cursor.execute("""SELECT load_labels_from_file(%s, %s, %s)""", (graph_name, node_label, csv_file,) )
            conn.commit()
            print(f"CSV file loaded to AGE successfully!")

        except Exception as ex:
            print(type(ex), ex)
            conn.rollback()

Run the script and you'll see the following output:

New CSV file 'products.csv' has been created with the desired format.
CSV file loaded to AGE successfully!

And voila! AGE now contains the nodes from the csv file. I hope this tutorial has been informative and helpful in guiding you through the process of importing CSV files into AGE with Python.

If you have any feedback or suggestions regarding this tutorial, I encourage you to leave a comment below. Your input is valuable, and I appreciate any insights or corrections you may have. Thank you for reading, and I hope you gained valuable knowledge from this tutorial.

Analyzing LEGO Sets Using a Graph Database

Matheus Farias de Oliveira Matsumoto — Tue, 20 Jun 2023 15:31:18 +0000

Have you ever wondered which pieces you need to complete a LEGO set you're working on? As LEGO enthusiasts, we understand the excitement of building and collecting these iconic toy sets. In this blog post, we'll explore how a graph database can help us analyze LEGO sets and determine the missing pieces needed to complete them. We'll walk through the process step by step using an example LEGO set and queries in SQL, with the use of Apache AGE, a graph extension for PostgreSQL.

Understanding Graph Databases

Before we dive into analyzing LEGO sets, let's briefly explore graph databases and their benefits. Unlike traditional relational databases, graph databases excel at representing complex relationships between data points. In the context of LEGO sets, where pieces have various connections, dependencies, and quantities, a graph database provides a natural and efficient way to model these relationships.

Pieces, Products, and Users can be represented by vertices on our graph. A Product is made by various Pieces and the Users own some of these pieces. Therefore, we can simplify these relations as follows:

(Product)-[REQUIRES {qty: int}]->(Piece {number: int})<-[OWN {qty: int}]-(User)

Creating the LEGO Graph

To get started, we need to set up our graph database to represent LEGO sets. Let's use the provided query to create our LEGO graph:

SELECT * FROM ag_catalog.create_graph('LegoGraph');

This query creates a graph named 'LegoGraph' that will serve as the foundation for our LEGO set analysis.

Defining the LEGO Set

Now that we have our LEGO graph, let's define the specific set we want to analyze. We can create the LEGO set using the following query:

SELECT * FROM cypher('LegoGraph', $$
CREATE (v:Product {theme: 'Lego City', name: 'Police Patrol Car', number: 60239})
RETURN v.name, v.theme, v.number
$$) AS (name agtype, theme agtype, number agtype);

In this query, we create a Product vertex representing the LEGO City Police Patrol Car set and assign it relevant properties such as theme, name, and number.

Specifying Required Pieces

Every LEGO set consists of various pieces, each with a specific quantity. We need to specify the required pieces for our LEGO set and their quantities. Let's add the required pieces for the Police Patrol Car set using the provided query:

SELECT * FROM cypher('LegoGraph', $$ MATCH (v:Product {number: 60239}) CREATE (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 4504369}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6213880}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6213881}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4179874}), 
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4179875}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 4547489}), (v)-[:REQUIRES {qty: 6}]->(:Piece {number: 302301}), 
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6168612}), (v)-[:REQUIRES {qty: 4}]->(:Piece {number: 4259940}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 243101}),
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6023806}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 4515359}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6031947}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 4560929}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4646574}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 416201}),
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6259271}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4159739}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4569056}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 302121}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 242023}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 301023}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 366623}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6188643}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6016165}),
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6112622}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 243224}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 9553}), 
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6172536}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 241226}), (v)-[:REQUIRES {qty: 4}]->(:Piece {number: 6029208}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6199908}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4285883})
$$) AS (a agtype);

This query creates the relationships between the Police Patrol Car set and the individual pieces it requires. Each relationship has a REQUIRES type and specifies the quantity of the required piece.

Creating a User

Now, let's create a user who owns some LEGO pieces. For our example, we'll create a user named Bob. Execute the following query to create the user:

SELECT * FROM cypher('LegoGraph', $$                                                                                               
CREATE (u:User {name: 'Bob'}) RETURN u.name                                                                                                              
$$) AS (name agtype);

Adding Owned Pieces

To determine the missing pieces for the user, we first need to add the pieces they already own. Let's assume Bob owns a few pieces from the Police Patrol Car set. Execute the following queries to add the owned pieces:

SELECT * FROM cypher('LegoGraph', $$
MATCH (u:User {name: 'Bob'}), (p:Piece {number: 4502089}) 
CREATE (u)-[:OWNS {qty: 4}]->(p)
$$) AS (a agtype);

SELECT * FROM cypher('LegoGraph', $$
MATCH (u:User {name: 'Bob'}), (p:Piece {number: 242023}) 
CREATE (u)-[:OWNS {qty: 2}]->(p)
$$) AS (a agtype);

SELECT * FROM cypher('LegoGraph', $$
MATCH (u:User {name: 'Bob'}), (p:Piece {number: 302301}) 
CREATE (u)-[:OWNS {qty: 2}]->(p)
$$) AS (a agtype);

In these queries, we match the user 'Bob' and the specific pieces he owns, and create OWNS relationships between them.

Analyzing Missing Pieces

Finally, let's determine the pieces that Bob is missing to complete the Police Patrol Car set. Execute the following query:

SELECT *
FROM cypher('LegoGraph', $$
    MATCH (v:Product {name: 'Police Patrol Car'})-[r:REQUIRES]->(p:Piece)
    OPTIONAL MATCH (p)<-[o:OWNS]-(u:User {name: 'Bob'})
    RETURN p.number,
        CASE WHEN o.qty IS NOT NULL THEN
            r.qty - o.qty
        ELSE
            r.qty
        END AS qty_missing
$$) AS (piece agtype, qty_missing agtype);

This query matches the Police Patrol Car set and its required pieces. It also performs an optional match with the owned pieces of Bob. By subtracting the owned quantity from the required quantity, we calculate the missing quantity for each piece. The result includes the piece number and the quantity missing.

  piece  | qty_missing
---------+-------------
 242023  | 0
 302301  | 4
 4504369 | 2
 6213880 | 2
 6213881 | 2
 4179874 | 1
 4179875 | 1
 4547489 | 2
 6168612 | 2
 4259940 | 4
 243101  | 1
 6023806 | 1
 4515359 | 2
 6031947 | 1
 4560929 | 2
 4646574 | 1
 416201  | 2
 6259271 | 1
 4159739 | 1
 4569056 | 1
 302121  | 2
 301023  | 2
 366623  | 2
 6188643 | 2
 6016165 | 1
 6112622 | 1
 243224  | 1
 9553    | 1
 6172536 | 1
 241226  | 1
 6029208 | 4
 6199908 | 2
 4285883 | 1
(33 rows)

Conclusion

By leveraging a graph database, we can efficiently analyze LEGO sets and determine the missing pieces needed to complete them. In this blog post, we explored the process step by step, from creating the LEGO graph to analyzing missing pieces for a specific user. Graph databases offer a flexible and intuitive way to represent complex relationships, making them a powerful tool for LEGO enthusiasts and collectors.

Also, if you want to learn more about Apache AGE, checkout their website and GitHub repository.

Happy building!

One Graph Database to Rule Them All

Matheus Farias de Oliveira Matsumoto — Tue, 30 May 2023 21:26:34 +0000

The Secret of the Spheres

At his ancient tower, the data mage ponders over it's mighty database, wondering how one could make this majestic library of information more powerful. How could he perform greater spells over it... A graph database perhaps? If there was only one graph database to rule them all, one database to find all vertices and edges, one database to bring all knowledge, and in the light guide him.

He delves inside the Mages Guild library, looking for some kind of sacred texts to help him in his task. A great book catches his eyes. It's cover filled with small spheres made of glass (which seems to contain constellations inside them) connected by strings of gold. The title reads "Apa Rindë Istima Age".

Bringing the book back to his tower, the data mage starts reading it's pages. As he reads each sentence, the words evaporate into small blue spheres, hovering in the air. Gold connections twist and bind each of these blue orbs together.

A blue smoke comes out from his mouth, turning into a royal python snake. Flying gently, she swirls over his head and proceeds to swallow the spheres one by one. After she has eaten them all, she begins to burn, her smoke entering the mage's ears. As the mage listens attentively to the whispered wisdom carried by the smoke, his eyes closed in deep concentration, he finally opens them and utters with a sense of enlightenment... "The secrets of the spheres reveal the path to ultimate knowledge."

Revealing the Knowledge

Here we are going to reveal what the mage heard whilst the magical snake dust entered his ears. One thing that he heard for sure was that, for him to unlock his database's maximum potential,it was necessary to use Apache AGE, a graph extension for PostgreSQL.

To use it, there are some requirements you must type in your Crystal Ball ... the Terminal I mean:

sudo apt-get update
sudo apt-get install python3-dev libpq-dev
pip install --no-binary :all: psycopg2
pip install antlr4-python3-runtime==4.11.1
pip install apache-age-python

After that, create a python script beginning with the following lines:

import psycopg2
import age

GRAPH_NAME = 'testing_py'
conn = psycopg2.connect(host="localhost", port="5432", dbname="database", user="username", password="password")

age.setUpAge(conn, GRAPH_NAME)

It will import two modules: psycopg2 and age. psycopg2 is a PostgreSQL adapter for Python, allowing Python programs to interact with a PostgreSQL database. age.setUpAge(conn, GRAPH_NAME) will create the graph namespace, where the vertices and edges will be stored.

AGE uses openCypher in it's queries, so every time that you want to make a query for the graph, you must follow the pattern:

SELECT * FROM cypher('graph_name', $$ 
/* Cypher Query Here */ 
$$) AS (result1 agtype, result2 agtype);

Now, lets make a function to create a node in the graph. It will have two arguments node_label and node_prop. node_label will be the label of the node and node_prop the properties, which must be written in a JSON like format.

def create_node(node_label, node_prop):

    with conn.cursor() as cursor:

        try:
            CREATE_NODE_CMD = f"""SELECT * FROM cypher('{GRAPH_NAME}', $$ CREATE (n:{node_label} {node_prop}) $$) as (v agtype); """
            cursor.execute(CREATE_NODE_CMD)
            conn.commit()

        except Exception as ex:
            print(type(ex), ex)
            conn.rollback()

# Example on how to use it:
create_node("Wizard", "{name: 'Gadwick, the Wizened'}")

To create nodes with an edge connecting them, we can create the function bellow:

# Creates two new nodes with an edge connecting them.
def create_connected_nodes(node_one_label, node_one_prop, node_two_label, node_two_prop, edge_label, edge_prop):
    with conn.cursor() as cursor:
        try:
            CREATE_CONNECTED_NODES_CMD = f"""SELECT * FROM cypher('{GRAPH_NAME}', $$ 
                                            CREATE (n:{node_one_label} {node_one_prop})-[:{edge_label} {edge_prop}]->(m:{node_two_label} {node_two_prop}) 
                                            $$) as (a agtype);"""
            cursor.execute(CREATE_CONNECTED_NODES_CMD)
            conn.commit()

        except Exception as ex:
            print(type(ex), ex)
            conn.rollback

create_connected_nodes("Human", "{name: 'Beren'}", "Elf", "{name: Luthien}", "LOVES", "")

Function for finding two specified nodes and connecting them with an edge:

# Finds two specified nodes and connects them.
def connect_two_existing_nodes(node_one_label, node_one_prop, prop_one_val, node_two_label, node_two_prop, prop_two_val, edge_label, edge_prop):
    with conn.cursor() as cursor:
        try:
            CREATE_CONNECTED_NODES_CMD = f"""SELECT * FROM cypher('{GRAPH_NAME}', $$
                                            MATCH (a:{node_one_label}), (b:{node_two_label})
                                            WHERE a.{node_one_prop} = {prop_one_val} AND b.{node_two_prop} = {prop_two_val}
                                            CREATE (a)-[:{edge_label} {edge_prop}]->(b)
                                            $$) as (a agtype);"""
            cursor.execute(CREATE_CONNECTED_NODES_CMD)
            conn.commit()

        except Exception as ex:
            print(type(ex), ex)
            conn.rollback

# Example
connect_two_existing_nodes("Elf", "name", "Luthien", "Human", "name", "Beren", "LOVES", "")

To retrieve all created nodes with the desired properties (the properties must be a list):

def select_all_nodes(properties):
    with conn.cursor() as cursor:
        try:
            FIND_ALL_NODES = f"""SELECT * FROM cypher('{GRAPH_NAME}', $$ 
                                MATCH (n) 
                                RETURN n $$) as (node agtype);"""
            cursor.execute(FIND_ALL_NODES)
            for row in cursor:
                node = row[0]
                print(node.id, node.label)
                for property in properties:
                    print(node[property])
                print("-->", node)

        except Exception as ex:
            print(type(ex), ex)
            conn.rollback

# Example on how to use it.
select_all_nodes(["name", "title"])

Now, you can initialize your Postgres database and run these scripts. After you've done so, the database will be updated with the new nodes and edges.

Continuing The Mage's Story

Empowered by the newfound insights from the sacred texts, the data mage's mind brims with creative energy. With a determined focus, he begins crafting intricate scripts that will shape and mold graph databases to his will. Each line of code serves as a spell, an enchantment that brings the structure of knowledge to life.

Carefully, the mage designs a script for creating graph databases, envisioning a realm where vertices and edges intertwine to form a web of interconnected information. He considers the attributes and properties that will define each vertex, giving them purpose and meaning within the vast network. The script takes shape, imbued with the mage's profound understanding of the intricate relationships between data points.

Unbeknownst to the mage, his rival, Neo Jumbo Jimmy Jellyboo Jeebus, watches from the shadows, his eyes filled with envy and malice. Jeebus recognizes the potential of the mage's creations. Sensing an opportunity to gain an upper hand, Jeebus plots to steal the scripts and harness their power for his own nefarious purposes. The data mage now must be cautious...

Erdős–Rényi Graph Model With Apache AGE

Matheus Farias de Oliveira Matsumoto — Mon, 22 May 2023 19:50:43 +0000

Introduction

In the mathematical field of graph theory, the Erdős–Rényi model refers to one of two closely related models for generating random graphs or the evolution of a random network. These models are named after Hungarian mathematicians Paul Erdős and Alfréd Rényi, who introduced one of the models in 1959.

I've been working on a feature for Apache AGE where it is possible to generate an Erdős–Rényi graph. AGE is an opensource extension for PostgreSQL that enables users to leverage a graph database on top of existing relational databases. Here's the Pull Request for it.

Background

There are two closely related variants of the Erdős–Rényi random graph model, but the one that I made for AGE was the G(n,p) model.

In the G(n,M) model, a graph is chosen uniformly at random from the collection of all graphs which have n nodes and M edges. The nodes are considered to be labeled, meaning that graphs obtained from each other by permuting the vertices are considered to be distinct. For example, in the G(3,2) model, there are three two-edge graphs on three labeled vertices (one for each choice of the middle vertex in a two-edge path), and each of these three graphs is included with probability 1/3.

In the G(n,p) model, a graph is constructed by connecting labeled nodes randomly. Each edge is included in the graph with probability p, independently from every other edge. Equivalently, the probability for generating each graph that has n nodes and M edges is:

p^M x ( 1 − p )^{C(n 2) − M}.

The parameter p in this model can be thought of as a weighting function; as p increases from 0 to 1, the model becomes more and more likely to include graphs with more edges and less likely to include graphs with fewer edges.

Implementation

Here is how it was done to create the vertices in AGE (I skipped some declarations and other parts, but in essence it is like the code below).

    /* Create the vertices and add them to vertex_array */
    for (int i = 0; i < no_vertices; i++)
    {
        vid = nextval_internal(vertex_seq_id, true);
        object_graph_id = make_graphid(vertex_label_id, vid);
        props = create_empty_agtype();

        /* Insert the vertex in the graph and in the list. */
        insert_vertex_simple(graph_id, vertex_label_str, object_graph_id, props);
        vertex_array[i] = object_graph_id;
    }

Then the edges. It uses a nested loop to iterate over unique pairs of vertices. The outer loop iterates from 0 to the number of vertices, and the inner loop starts from the current value of the outer loop variable to the number of vertices.

/* For each unique pair (i, j), generate a random number. If it's probability is P or grater, create
       an edge between i and j. 
     */
    for (int i = 0; i < no_vertices; i++)
    {
        /* It starts with (int j = i) because it's a combination of C(n, 2) total edges. */
        for (int j = i; j < no_vertices; j++)
        {
            /* Generate a random float number between 0 and 1. */
            random_prob = (float) ((float)rand() / (float)RAND_MAX);

            if (random_prob <= set_prob && i != j)
            {
                eid = nextval_internal(edge_seq_id, true);
                object_graph_id = make_graphid(edge_label_id, eid);

                props = create_empty_agtype();

                insert_edge_simple(graph_id, edge_label_str,
                                object_graph_id, vertex_array[i],
                                vertex_array[j], props);

                if (bidirectional == true)
                {
                    eid = nextval_internal(edge_seq_id, true);
                    object_graph_id = make_graphid(edge_label_id, eid);

                    props = create_empty_agtype();

                    insert_edge_simple(graph_id, edge_label_str,
                                    object_graph_id, vertex_array[j],
                                    vertex_array[i], props);
                }
            }
        }
    }

Example

Here is an example of a random generated graph from the Erdos-Renyi function with AGE Viewer:

SELECT ag_catalog.age_create_erdos_renyi_graph('test', 5, '0.4', NULL, NULL, NULL);

NOTICE:  graph "test" has been created
 age_create_erdos_renyi_graph 
------------------------------

(1 row)

The total possible number of edges is C(5,2):

5!/(2! x 3!) =
= (5 x 4 x 3!)/(2! x 3!) =
= (5 x 4)/2! =
= 20/2 =
= 10

But since the set probability was 0.4, each time it tries to create an edge, first a random probability is generated and if this probability is less than 0.4, it creates the edge. That's why the number of edges is reduced.

Conclusion

With this feature developed for Apache AGE, users will easily generate Erdos-Renyi graphs within the PostgreSQL environment. Incorporating this model into AGE expands its capabilities and empowers users to analyze and explore networks efficiently.

If you want to learn more about Apache AGE, checkout my other posts, the Apache AGE website, and it's GitHub repository:

Apache AGE Website
Apache AGE GitHub Repo

Barabasi-Albert Graph for Apache AGE

Matheus Farias de Oliveira Matsumoto — Tue, 16 May 2023 19:09:39 +0000

Introduction

The Barabási–Albert (BA) model is an algorithm for generating random scale-free networks using a preferential attachment mechanism. Several natural and human-made systems, including the Internet, the World Wide Web, citation networks, and some social networks are thought to be approximately scale-free and certainly contain few nodes (called hubs) with unusually high degree as compared to the other nodes of the network. The BA model tries to explain the existence of such nodes in real networks. The algorithm is named for its inventors Albert-László Barabási and Réka Albert.

I've been working on a feature for Apache AGE where it is possible to generate the Barabasi-Albert graph. AGE is an opensource extension for PostgreSQL that enables users to leverage a graph database on top of existing relational databases. Here's the Pull Request for it.

Background

The BA model starts with a small number of nodes and then iteratively adds new nodes to the graph. Each new node is connected to m existing nodes, where m is a fixed parameter of the model. The probability that a new node is connected to an existing node is proportional to the degree of the existing node, meaning that nodes with higher degrees are more likely to receive new connections.

Barabasi-Albert Graph Generation Function

The function is declared at age--1.2.0.sql:

CREATE FUNCTION ag_catalog.age_create_barabasi_albert_graph(graph_name name, 
                                                            num_vertices int,
                                                            num_edges int,
                                                            node_label name = NULL,
                                                            edge_label name = NULL,
                                                            bidirectional bool = true)
RETURNS void
LANGUAGE c
CALLED ON NULL INPUT
PARALLEL SAFE
AS 'MODULE_PATHNAME';

This function will receive six arguments: the name of the graph, the total number of vertices, the number of edges each vertex will connect to, the label for the nodes, the label for the edges and if these connections shall be bidirectional or not (the default is true). AGE's standard node and vertex label are _ag_label_vertex and _ag_label_edge, the label's name arguments are set to NULL, so that, if no argument is passed, it will create vertices and edges of the standard label.

To use it, you can simply call:

SELECT age_create_barabasi_albert_graph('BarabasiAlbertGraph', 100, 1, NULL, NULL, true);

NOTICE:  graph "BarabasiAlbertGraph" has been created
 age_create_barabasi_albert_graph 
----------------------------------

(1 row)

And with AGE Viewer, we can visualize it like so:

Conclusion

With this feature developed for Apache AGE, users will easily generate Barabasi-Albert graphs within the PostgreSQL environment. Incorporating the Barabasi-Albert model into AGE expands its capabilities and empowers users to analyze and explore scale-free networks efficiently.

If you want to learn more about Apache AGE, checkout my other posts, the Apache AGE website, and it's GitHub repository:

Apache AGE Website
Apache AGE GitHub Repo

Recommender Systems with Graph Databases like Apache AGE

Matheus Farias de Oliveira Matsumoto — Fri, 12 May 2023 21:05:06 +0000

Recommender systems are a type of artificial intelligence that helps users discover new items that they may not have found on their own. These systems analyze user data, such as past purchases or browsing history, and provide personalized recommendations based on their interests and behavior.

While there are various approaches to building recommender systems, one approach that has gained popularity in recent years is using graph databases like Apache AGE.

The Role of Graph Databases in Recommender Systems

Graph databases are particularly well-suited for building recommendation engines because they can store and analyze complex relationships between items and users. In a graph-based recommender system, each item and user is represented as a node in a graph, and the relationships between them are modeled as edges.

This allows for more sophisticated recommendation algorithms, such as collaborative filtering and content-based filtering, that take into account not only the user's behavior, but also the relationships between items and users. Graph databases can also handle large-scale graph data with high performance, making them ideal for applications that require complex querying and analysis of graph data.

Apache AGE: A Graph Database for Recommender Systems

Apache AGE is an open-source graph database that is built on top of PostgreSQL. It supports the Cypher query language, which is commonly used in graph databases, and can handle large-scale graph data with high performance.

One of the key features of Apache AGE is its ability to scale horizontally across multiple nodes in a cluster, which allows it to handle large, complex graphs with ease. It also supports parallel query processing, which can further improve performance.

Apache AGE can be used as a graph database for building a recommender system that requires complex querying and analysis of graph data. In a graph-based recommender system, each item and user can be represented as a node in a graph, and the relationships between them can be modeled as edges. Apache AGE can then be used to store and query this graph data efficiently, allowing for faster and more accurate recommendations.

Recommendation analysis that are commonly used

There are several different kinds of recommendation analysis that are commonly used in recommender systems:

Collaborative filtering: This approach recommends items based on the preferences of similar users. It analyzes the user's behavior and finds other users who have similar preferences, then recommends items that those similar users have liked.
Content-based filtering: This approach recommends items based on the attributes of the items themselves. It analyzes the features of the items that a user has liked in the past, such as genre or author, and recommends items with similar attributes.
Hybrid recommendation: This approach combines collaborative and content-based filtering to provide more accurate recommendations. It uses both the user's behavior and the features of the items to make recommendations.
Knowledge-based recommendation: This approach recommends items based on the explicit knowledge of the user's preferences. It asks the user for information about their preferences and uses that information to make recommendations.
Demographic-based recommendation: This approach recommends items based on demographic information about the user, such as age, gender, or location. It assumes that users with similar demographic profiles will have similar preferences.

Each of these approaches has its own strengths and weaknesses, and the most appropriate approach will depend on the specific context and data available.

Conclusion

Recommender systems are an important tool for businesses and organizations to improve customer engagement and satisfaction. Graph databases like Apache AGE provide a powerful platform for building sophisticated and efficient recommendation engines that take into account the complex relationships between items and users. With its scalability and compatibility with the Cypher query language, Apache AGE is an excellent choice for building recommender systems that require complex graph querying and analysis.

For more information about Apache AGE, visit the websites below:

Apache AGE Website
Apache AGE on GitHub

And also, give it a like and share this post with your friends! Thank you for reading!

Introduction to Graph Databases

Matheus Farias de Oliveira Matsumoto — Mon, 08 May 2023 21:02:55 +0000

Graph databases are a relatively new type of database that have gained popularity in recent years. They are designed to store and manage highly connected data and have several advantages over traditional relational databases.

One of these graph databases is Apache AGE. It is an open-source graph database that is an extension of PostgreSQL. It allows users to store and manage highly connected data in the form of nodes and edges. It provides users with the ability to use SQL and openCypher together in the same query. At the end of some sections, there will be a link to an Apache AGE tutorial.

What are Graph Databases?

A graph database is a type of database that stores data in the form of nodes and edges, similar to a graph. Nodes represent entities, while edges represent the relationships between entities. For example, in a social network, nodes could represent users, and edges could represent their relationships (friendship, following, etc.).

Advantages of Graph Databases

Faster query performance

Graph databases are optimized for querying highly connected data. Because they store data in a graph-like structure, they can quickly traverse relationships between nodes and find the data you’re looking for. This makes them much faster than traditional relational databases for certain types of queries.

More flexible data modeling

Graph databases allow for more flexible and dynamic data modeling than traditional relational databases. This makes it easier to add new data and relationships as your application evolves over time. It also allows for more complex queries that would be difficult to express in a relational database.

Better scalability

Because graph databases are optimized for highly connected data, they can scale better than relational databases for certain use cases.

More natural representation of data

Graph databases provide a more natural representation of data than relational databases. Because they store data in a graph-like structure, it’s easier to understand the relationships between entities and how they relate to each other. This can make it easier to develop and maintain applications over time.

Use Cases for Graph Databases

Graph databases are particularly well-suited for use cases where data is highly connected. Here are a few examples:

Social networks

Social networks are a classic use case for graph databases. They require storing and querying highly connected data, such as user profiles, friend relationships, and activity streams.

Creating a Graph Database for a Social Network with Apache AGE

Recommendation engines

Recommendation engines require analyzing the relationships between users and items to make personalized recommendations. Graph databases can efficiently store and query this type of

Recommender System with Apache AGE

Fraud detection

Fraud detection systems require analyzing complex patterns of behavior to detect fraudulent activity. Graph databases can efficiently store and query this type of data, making it easier to detect fraud in real-time.

Uncovering Credit Card Thieves: A Graph-Based Approach with Apache AGE

Conclusion

Graph databases are a powerful tool for storing and managing highly connected data. They provide faster query performance, more flexible data modeling, better scalability, and a more natural representation of data than traditional relational databases. If you’re working with highly connected data, a Apache AGE could be a great choice for your next project.

If you want to contribute for AGE's development, checkout AGE's GitHub Repo

And also, thanks for reading! :)

Recommender System with Apache AGE

Matheus Farias de Oliveira Matsumoto — Thu, 20 Apr 2023 18:46:05 +0000

From watching How Recommender Systems Work (Netflix/Amazon) by Art of the Problem on Youtube, I got inspired by it and wanted to create a blog post about this topic. So, here we'll be working on how we can create a recommendation system with a graph database. For this, we will use Apache AGE, which is an open source extension for PostgreSQL that allows us to create nodes and edges.

Creating the Graph

Given the observations of users past activities, we need to predict what other things the user might also like. We can represent user preferences graphically as connections between People and the things that they have a rating or opinion on, such as Movies. The approach that we are going to use is called Content Filtering, which uses information we know about people and things as connective tissue for recommendations.

-- Creating the graph.
SELECT create_graph('RecommenderSystem');


-- Adding user.
SELECT * FROM cypher('RecommenderSystem', $$
    CREATE (:Person {name: 'Abigail'})
$$) AS (a agtype);


-- Adding movies.
SELECT * FROM cypher('RecommenderSystem', $$
    CREATE (:Movie {title: 'The Matrix'}),
           (:Movie {title: 'Shrek'}),
           (:Movie {title: 'The Blair Witch Project'}),
           (:Movie {title: 'Jurassic Park'}),
           (:Movie {title: 'Thor: Love and Thunder'})
$$) AS (a agtype);


-- Adding categories.
SELECT * FROM cypher('RecommenderSystem', $$
    CREATE (:Category {name: 'Action'}),
           (:Category {name: 'Comedy'}),
           (:Category {name: 'Horror'})
$$) AS (a agtype);

We can represent the strength of the connections with a property called rating on the edges between the users and categories, and movies and categoires. This rating will vary between 0 and 4, where 0 means that the user hated the movie and 4 means that the user loved the movie. This also works for the categories and movies, where 0 is the less likely to have and 4 is the most likely to have.

Let's say that Abigail has a rating of 3 for Comedy, 1 for Action, and 0 for Horror.

-- User preferences.
SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (a:Person {name: 'Abigail'}), (A:Category), (C:Category), (H:Category)
    WHERE A.name = 'Action' AND C.name = 'Comedy' AND H.name = 'Horror' 
    CREATE (a)-[:RATING {rating: 3}]->(C),
           (a)-[:RATING {rating: 1}]->(A),
           (a)-[:RATING {rating: 0}]->(H)
$$) AS (a agtype);

Each movie is also mapped to each category the same way. For example, Matrix has no comedy, lots of action and no horror.

-- The Matrix and it's relationship with Categories.
SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (matrix:Movie {title: 'The Matrix'}), (A:Category), (C:Category), (H:Category)
    WHERE A.name = 'Action' AND C.name = 'Comedy' AND H.name = 'Horror' 
    CREATE (matrix)-[:RATING {rating: 0}]->(C),
           (matrix)-[:RATING {rating: 4}]->(A),
           (matrix)-[:RATING {rating: 0}]->(H)
$$) AS (a agtype);

-- Shrek and it's relationship with Categories.
SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (shrek:Movie {title: 'Shrek'}), (A:Category), (C:Category), (H:Category)
    WHERE A.name = 'Action' AND C.name = 'Comedy' AND H.name = 'Horror' 
    CREATE (shrek)-[:RATING {rating: 4}]->(C),
           (shrek)-[:RATING {rating: 2}]->(A),
           (shrek)-[:RATING {rating: 0}]->(H)
$$) AS (a agtype);

-- The Blair Witch Project and it's relationship with Categories.
SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (witch:Movie {title: 'The Blair Witch Project'}), (A:Category), (C:Category), (H:Category)
    WHERE A.name = 'Action' AND C.name = 'Comedy' AND H.name = 'Horror' 
    CREATE (witch)-[:RATING {rating: 0}]->(C),
           (witch)-[:RATING {rating: 0}]->(A),
           (witch)-[:RATING {rating: 4}]->(H)
$$) AS (a agtype);

-- Jurassic Park and it's relationship with Categories.
SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (jurassic:Movie {title: 'Jurassic Park'}), (A:Category), (C:Category), (H:Category)
    WHERE A.name = 'Action' AND C.name = 'Comedy' AND H.name = 'Horror' 
    CREATE (jurassic)-[:RATING {rating: 1}]->(C),
           (jurassic)-[:RATING {rating: 3}]->(A),
           (jurassic)-[:RATING {rating: 0}]->(H)
$$) AS (a agtype);

-- Thor: Love and Thunder and it's relationship with Categories.
SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (thor:Movie {title: 'Thor: Love and Thunder'}), (A:Category), (C:Category), (H:Category)
    WHERE A.name = 'Action' AND C.name = 'Comedy' AND H.name = 'Horror' 
    CREATE (thor)-[:RATING {rating: 4}]->(C),
           (thor)-[:RATING {rating: 2}]->(A),
           (thor)-[:RATING {rating: 0}]->(H)
$$) AS (a agtype);

Content Filtering Method

To determine wheter someone will like a movie, we need to multiply the factors together and dived it by the number of categories times 4.

-- The Matrix estimated rating for the user.
SELECT e1/(ct*4) AS factor FROM cypher('RecommenderSystem', $$
MATCH (u:Person)-[e1:RATING]->(v:Category)<-[e2:RATING]-(w:Movie{title: 'The Matrix'}), (c:Category) WITH e1, e2, COUNT(*) AS ct
RETURN SUM(e1.rating * e2.rating)::float, ct
$$) AS (e1 float, ct agtype);

      factor       
-------------------
 0.333333333333333
(1 row)

We could represent the strength of a connection between Abigail and The Matrix as: [(3 x 0) + (1 x 4) + (0 x 0)] / 12 = 0.3 . Our estimation is that she will not like the movie so much. Now we need to gather the data for every other movie so we can show the ones that best suit her interests.

SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (a: Person {name: 'Abigail'})-[r1: RATING]->(c: Category)<-[r2: RATING]-(m:Movie)
    WITH a.name AS person, m.title AS movie, 
         SUM(r1.rating * r2.rating)/(count(c) * 4)::float AS rate
    RETURN person, movie, rate AS expected_rating
$$) AS (person agtype, movie agtype, expected_rating float);


  person   |           movie           |  expected_rating  
-----------+---------------------------+-------------------
 "Abigail" | "Thor: Love and Thunder"  |  1.16666666666667
 "Abigail" | "The Matrix"              | 0.333333333333333
 "Abigail" | "Shrek"                   |  1.16666666666667
 "Abigail" | "Jurassic Park"           |               0.5
 "Abigail" | "The Blair Witch Project" |                 0
(5 rows)

Although not much her cup of tea, Shrek and Thor will be the movies from our list that Abigail will prefer watching according to our graph analysis.

Conclusion

We have shown how to create a recommendation system with a graph database using Apache AGE. This approach can be extended to accommodate more complex scenarios, such as incorporating user demographics, search history, or social network connections. Graph databases are well suited for recommendation systems because they can easily represent the relationships between users and items as well as the attributes of those entities. Moreover, the use of SQL and the Cypher query language makes it easier to work with large datasets and perform complex queries. Overall, we hope that this post provides a starting point for those interested in building a recommendation system with a graph database.

If you want to learn more about Apache AGE, checkout the links below:

Hero Builds and Counter Picks in Dota 2 With Apache AGE

Matheus Farias de Oliveira Matsumoto — Wed, 12 Apr 2023 01:54:50 +0000

Apache AGE

Apache AGE is an extension for PostgreSQL that enables users to leverage a graph database on top of the existing relational databases. AGE is an acronym for A Graph Extension and is inspired by Bitnine's AgensGraph, a multi-model database fork of PostgreSQL. It's basic principle is to create a single storage that handles both the relational and graph data model so that the users can use standard SQL along with openCypher, one of the most popular graph query languages today.

What is Dota 2?

Dota 2 is a multiplayer online battle arena (MOBA) game developed and published by Valve Corporation. It is a sequel to the original Defense of the Ancients (DotA) mod for Warcraft III: Reign of Chaos.

In Dota 2, two teams of five players each battle against each other to destroy the enemy team's Ancient, a large structure located at each team's base. Players control powerful hero units with unique abilities and characteristics, and gain experience and gold as they defeat enemy units and structures. This allows them to level up, gain new abilities, and purchase items that enhance their hero's strengths.

The game is played in real-time, and players must work together to coordinate their hero's abilities and strategies to outmaneuver and defeat the opposing team. Dota 2 is known for its high level of strategic complexity and depth, as well as its active competitive scene with numerous professional players and teams competing in tournaments worldwide.

Today we'll learn how to create these builds and analyze which hero is a good match against another with Apache AGE, an opensource graph database extension for PostgreSQL.

Builds

A build in Dota 2 is a particular combination of items, abilities, and talents that a player selects for their hero. Building the proper equipment, abilities, and talents is essential to a player's success in the game because it has a significant impact on their hero's power and skills.

Here's an example of a build for Naga Siren

There are different types of builds in Dota 2, such as core builds, situational builds, and experimental builds. Core builds refer to the standard set of items and skills that most players use for a specific hero, while situational builds are chosen depending on the specific situation and match-up. Experimental builds, on the other hand, are unique and unconventional builds that players may try out to see if they work.

A good Dota 2 build should take into account the player's role, the enemy team composition, and the current state of the game. It should also be adaptable and flexible enough to adjust to changing circumstances in the game. To create a good build, players need to have a good understanding of their hero's strengths and weaknesses, as well as the items and skills available in the game.

Counter Picks

Counter picks refer to selecting a hero that is particularly effective against one or more heroes on the opposing team. A counter pick is chosen with the intention of gaining a strategic advantage over the opposing team, by selecting a hero that can exploit the weaknesses or vulnerabilities of the enemy heroes.

Counter picking is an important aspect of Dota 2 strategy, as it can greatly affect the outcome of a match. For example, if the opposing team has picked a hero with strong magical abilities, a player may choose to counter pick with a hero that has magic resistance or immunity. Alternatively, if the opposing team has picked a hero that relies on mobility and speed, a player may choose to counter pick with a hero that has disables or slows.

Slithice, the Naga Siren

Naga Siren is a versatile hero in Dota 2, known for her ability to control the battlefield with her illusions, disable enemies with her net ability, and reset team fights with her ultimate ability, Song of the Siren. Her primary attribute is agility, which means she excels at dealing physical damage and has a high attack speed. She is typically played in the carry role, but can also be played as a support or utility hero.

She is a strong hero that requires good game sense and strategic thinking to play effectively. She is often picked in professional matches and can be a formidable force in the hands of a skilled player. So, let's create our graph and add her to it.

-- Create the graph.
SELECT create_graph('dota');
NOTICE:  graph "dota" has been created
 create_graph 
--------------

(1 row)

-- Add Naga Siren to our graph
SELECT * FROM cypher('dota', $$
CREATE (a:Hero {name: 'Naga Siren', abilities: ['Mirror Image', 'Ensnare', 'Rip Tide', 'Reel In', 'Song of the Siren']}
RETURN a 
$$) AS (Hero agtype);

                                                                                     hero                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Hero", "properties": {"name": "Naga Siren", "abilities": ["Mirror Image", "Ensnare", "Rip Tide", "Reel In", "Song of the Siren"]}}::vertex
(1 row)

Items and Abilities

Naga Siren is renown to be one of the most boring carries to play against. She will never put herself in harm's way, instead pushing one or more lanes at once thanks to Mirror Image while she will be farming the jungle. Because the illusions keep the lane pushed to provide vision and map control, enemies will have trouble ganking her without using Smoke of Deceit. Furthermore, Song of the Siren either allows her to run away or wait for her team to react if enemies have not bursted her down quickly enough.

Now about the items you should consider purchasing. As a carry Naga Siren, you should almost always go for Radiance as your first major item. However, do not forget to buy enough cheap and cost-efficient items to fill your slots like Wraith Band or Infused Raindrop. Then, go for Boots of Travel, Manta Style, and Octarine Core to constantly produce illusions and Rip Tide, pushing out all the lanes and razing jungle camps. Finally, consider choosing between Butterfly, Heart of Tarrasque, Diffusal Blade, or Eye of Skadi to increase survivability and damage output of Naga Siren and illusions, applying constant pressure on all lanes to limit opportunities for enemies to make plays as they will have to defend nonstop.

As for your ability build, you can opt for an early value point in Ensnare if you want some disable to produce kills with teammates. Otherwise, focus entirely on Rip Tide and Mirror Image so you can use them for farming lanes and jungle camps.

SELECT * FROM cypher('dota', $$                                                              
MATCH (a {name: 'Naga Siren'})                                                                      
CREATE (a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Radiance'}),                                         
(a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Wraith Band'}),                                             
(a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Infused Raindrops'}),                                       
(a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Boots of Travel'}),                                         
(a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Manta Style'}),                                             
(a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Octarine Core'}),                                           
(a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Diffusal Blade'}),                                          
(a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Eye of Skadi'}),                                            
(a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Butterfly'}),                                               
(a)-[:RECOMMENDED_ITEM]->(:Item {name: 'Heart of Tarrasque'),
(a)-[:RECOMMENDED_CARRY_BUILD]->(:Build {type: 'Carry', Q: [1, 3, 5, 7], W: [11, 13, 14, 16], E: [2, 4, 6, 8], R: [9, 12, 18]})                                                                       
$$) AS (a agtype);

Counters

Depending on which heroes the enemy team has chosen, Naga Siren will be a good option or a bad option. Let's analyze three cases for each situation.

Good Against:

Arc Warden

Song of the Siren stops Arc Warden from acting and allows Naga Siren's team to close in on him, past his Magnetic Field.
Mirror Image dispels Flux. The illusions created from it also disable the slow from Flux if Arc Warden casts it after Mirror Image.
Arc Warden's playstyle heavily favors split-pushing, which Naga Siren can compete against with her illusions.

Crystal Maiden

Mirror Image can dispel Frostbite.
Song of the Siren can stop Freezing Field regardless of any kind of invisibility as well as allow Naga Siren's team to quickly close the gap on Crystal Maiden.
Ensnare allows Naga Siren and her team to quickly catch up with Crystal Maiden and kill her.

Sniper

Ensnare with her attacks allows Naga Siren to gank Sniper easily.
Rip Tide removes Sniper's very little armor.

Bad Against:

Axe

Axe will destroy her illusions by proccing Counter Helix more often due to him being hit by more attacks.

Gyrocopter

Flak Cannon and Call Down can easily clear Naga Siren's illusions.

Leshrac

All of Leshrac's abilities, especially Pulse Nova, can easily deal with Naga Siren's illusions.

SELECT * FROM cypher('dota', $$
MATCH (a {name: 'Naga Siren'})
CREATE (a)-[:GOOD_AGAINST]->(:Hero {name: 'Arc Warden'}),
(a)-[:GOOD_AGAINST]->(:Hero {name: 'Crystal Maiden'}),
(a)-[:GOOD_AGAINST]->(:Hero {name: 'Sniper'}),
(a)-[:BAD_AGAINST]->(:Hero {name: 'Axe'}),
(a)-[:BAD_AGAINST]->(:Hero {name: 'Gyrocopter'}),
(a)-[:BAD_AGAINST]->(:Hero {name: 'Leshrac'})
$$) AS (a agtype);

Here is how our graph currently looks in AGE Viewer:

Retrieving the Data

Now that we have some information about Naga Siren, her recommended build for a carry type gameplay and the heroes she is good and bad against, we can analyze if she appears as a good option when an opponent chooses a hero. As an example, let's suppose the opponent chose Sniper and then in another game the opponent chose Axe. We can visualize all the good counter picks with the following query:

-- Good options.
SELECT * FROM cypher('dota', $$                                                              
MATCH (a)-[:GOOD_AGAINST]->(b {name: 'Sniper'})                                                     
RETURN a.name                                                                                       
$$) AS (counter_pick agtype);

 counter_pick 
--------------
 "Naga Siren"
(1 row)

-- Bad options.
SELECT * FROM cypher('dota', $$                                                              
MATCH (a)-[:BAD_AGAINST]->(b {name: 'Axe'})                                                     
RETURN a.name                                                                                       
$$) AS (bad_pick agtype);

   bad_pick
--------------
 "Naga Siren"
(1 row)

If we had more heroes, then it would be more interesting to see all the available options for a match against Sniper and which heroes not to pick agains Axe. Also, it would be best to add each role of our heroes, like Carry, Nuker, Initiator, and so on...

Conclusion

We demonstrated how to create a graph for heroes and explored their abilities and items. We also explained how hero builds and counter picks work in Dota 2, and why they are important for players to master.

Overall, Apache AGE is an excellent tool for Dota 2 players who want to take their game to the next level. It enables them to create complex graphs that capture the nuances of hero builds and counter picks, and provides them with valuable insights that can help them make better strategic decisions in the game.

For more information on Apache AGE, checkout their website and GitHub repo.

Forem: Matheus Farias de Oliveira Matsumoto

Como fazer o upload de imagens Docker para a Huawei Cloud usando o GitHub Actions

1. Criando um OBS Bucket para armazenar o arquivo tfstate

2. Preparando o repositório e as credenciais no GitHub

3. Configurando o terraform para cirar o SWR

Configuração principal do Terraform (main.tf)

Módulo do Container Registry (modules/registry/main.tf)

4. Criando o Dockerfile (docker/Dockerfile)

5. GitHub Actions

Provisionando o SWR com Terraform

Adquirindo a senha de login do Docker no SWR

Build e Push da imagem Docker

6. Conclusão

Efficient Representation of Relationships with Graph Databases

The Power of Relationships

Graph Databases: The Basics

Efficient Querying

AGE: Bridging the Gap Between Relational and Graph Databases

Links

Join Our Webinar: Plug-in Graph Analytics on PostgreSQL with Apache AGE

Are you ready to unlock the potential of PostgreSQL and graph technology?

Importing CSV to AGE

Initial CSV File

Reading the CSV File

Creating the CSV File

Loading the data to AGE

Analyzing LEGO Sets Using a Graph Database

Understanding Graph Databases

Creating the LEGO Graph

Defining the LEGO Set

Specifying Required Pieces

Creating a User

Adding Owned Pieces

Analyzing Missing Pieces

Conclusion

One Graph Database to Rule Them All

The Secret of the Spheres

Revealing the Knowledge

Continuing The Mage's Story

Erdős–Rényi Graph Model With Apache AGE

Introduction

Background

Implementation

Example

Conclusion

Barabasi-Albert Graph for Apache AGE

Introduction

Background

Barabasi-Albert Graph Generation Function

Conclusion

Recommender Systems with Graph Databases like Apache AGE

The Role of Graph Databases in Recommender Systems

Apache AGE: A Graph Database for Recommender Systems

Recommendation analysis that are commonly used

Conclusion

Introduction to Graph Databases

What are Graph Databases?

Advantages of Graph Databases

Faster query performance

More flexible data modeling

Better scalability

More natural representation of data

Use Cases for Graph Databases

Social networks

Recommendation engines

Fraud detection

Conclusion

Recommender System with Apache AGE

Creating the Graph

Content Filtering Method

Conclusion

Hero Builds and Counter Picks in Dota 2 With Apache AGE

Apache AGE

What is Dota 2?

Builds

Counter Picks

Slithice, the Naga Siren

Items and Abilities

Counters

Retrieving the Data

Configuração principal do Terraform (`main.tf`)

Módulo do Container Registry (`modules/registry/main.tf`)

4. Criando o Dockerfile (`docker/Dockerfile`)