In this blog post, you’ll be able to build a Streamlit application that empowers users to ask questions in natural language, gain instant insights and interact with their data in less than two minutes, using Snowflake Cortex Analyst ❄️

Introduction:

In today’s data-driven world, companies are investing heavily in LLM-powered conversational self-service analytics tools to empower business users with real-time insights. Traditionally, business users have relied on BI dashboards to answer their data questions, leaving them dependent on data analysts and engineers, which can take long days.

Building such tools is complex, and many self-service analytics initiatives fail due to poor quality and inaccurate insights, which may lead to significant business consequences. Trust and accuracy are the key metrics that make business users rely on these tools.

What is Cortex Analyst ?

With the promise of delivering accurate self-service analytics to businesses, Snowflake has released Cortex Analyst (currently in Public Preview). This fully managed LLM-powered feature helps organizations create applications capable of reliably answering business questions based on the structured data stored in Snowflake, with no SQL knowledge required, achieving over 90%+ accuracy in real-world use cases better than other Text-to-SQL solutions.

Powered by state-of-the-art large language models such as Meta, Llama3 and Mistral, Cortex Analyst is delivered as a REST API, making it easy to integrate to existing workflows, business tools and applications like Streamlit and custom chat interfaces, while maintaining data security and governance. Learn more about Cortex Analyst accuracy

Cortex Analyst semantic model

Similar to how a data analyst writes a SQL query after understanding business logic and the meanings of tables and columns, Cortex Analyst uses a semantic model to capture the business meaning and provide a richer description of the data’s semantic information. This ensures high-precision SQL generation and accurate results. The semantic model is a YAML file containing metadata such as table names, column names, value types, descriptions, verified queries, filters, and more.

Below is a sample semantic model YAML file:

name: Sales Data
tables:
- name: sales_data
  base_table:
    database: sales
    schema: public
    table: sd_data

  dimensions:
    - name: state
      description: The state where the sale took place.
      expr: d_state
      data_type: TEXT
        unique: false
        sample_values:
          - "CA"
          - "IL"

    # Time dimension columns in the logical table.
    time_dimensions:
      - name: sale_timestamp
        synonyms:
          - "time_of_sale"
          - "transaction_time"
        description: The time when the sale occurred. In UTC.
        expr: dt
        data_type: TIMESTAMP
        unique: false

    # Measure columns in the logical table.
    measures:
      - name: profit
        synonyms:
          - "earnings"
          - "net income"
        description: The profit generated from a sale.
        expr: amt - cst
        data_type: NUMBER
        default_aggregation: sum

verified_queries:
  - name: "California profit"
    question: "What was the profit from California last month?"
    verified_at: 1714497970
    verified_by: Jane Doe
    sql: "
SELECT sum(profit)
FROM __sales_data
WHERE state = 'CA'
    AND sale_timestamp >= DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE))
    AND sale_timestamp < DATE_TRUNC('month', CURRENT_DATE)
"

Create a Streamlit app to Chat with Any Data Accurately in Minutes

Creating a semantic model requires time to fill in the necessary metadata. To accelerate the delivery of a self-service analytics application, a Streamlit application can automate the generation of the semantic model, uploads it to a stage, and enables you to start conversing with your data in natural language within minutes. This app is inspired from Github repository

This application uses metadata information (retrieved using the DESCRIBE TABLE command) about the selected tables to fill in required information in the semantic model, such as column names and column types. Table and column descriptions are generated by Cortex LLM’s COMPLETE function using the Llama3–70b model, as shown in the architecture below:

To use this application:

1. Download the streamlit_app.py file from the GitHub repository.
2. Login to Your Snowflake Account: Head over to “Projects.”
3. Select “Streamlit”: You will see a list of applications.
4. Create a New Streamlit App: Click on “+ Streamlit App”. Provide a title, location, and warehouse for your application. Click on “Create” and paste the Python code from streamlit_app.py.
5. Run the Application.
6. Getting Started: Once the Streamlit app is running, navigate to “Getting Started” page. Provide a name and description for your semantic model, then click on “Save Semantic Model Info”
7. Click on “Table Definition”: Select the databases, schemas, and tables you want to query in natural language by clicking on “Add table to YAML”. You also need to select the stage where to save the semantic model YAML file that will be used to request the Cortex Analyst REST API. The application auto-populates the semantic model YAML file with table descriptions, column metadata, and descriptions generated using the Llama3 model.
8. Upload to Stage: Once you’ve added all tables, click on “Upload to Stage.” You will find a new YAML file in the stage you’ve previously selected.
9. Chat with Your Data: You’re all set! Go to the “Chat with your data” page and start asking questions about the tables you’ve selected.
10. Reset the App: You can reset the app to its initial state, clearing all input and saved data to start fresh.

Below is a demo showing all the steps above:

This application can be further improved by adding verified queries, synonyms, and sample values to enhance the accuracy of the results.

Hope you find this useful !

Conclusion

AI for BI has traditionally been challenging. Snowflake Cortex Analyst represents a significant step forward in data analytics. With it, you can effortlessly build production-ready text-to-SQL services that deliver highly accurate results, enabling organizations to make data-driven decisions more efficiently and transforming the way they interact with their data.

Some useful links:

• Cortex Analyst documentation
• Semantic Model spec
• Learn more about Cortex Analyst architecture and its key components.
• Cortex LLM functions

Self-serve Analytics application in minutes with Snowflake Cortex Analyst ❄️ was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.

Introduction

Greenplum 7, the upcoming release of the Greenplum data warehouse based on PostgreSQL v12, marking a huge milestone for the Greenplum community. This release will support many new extensions for AI/ML, such as pgvector and postgresML.

The latest beta5 release has introduced support for the popular #pgvector extension, enabling large-scale vector similarity search.

More details here: https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/relnotes-release-notes.html

This advancement seamlessly complements Greenplum’s existing capabilities by facilitating support for unstructured data within the data warehouse through vector embeddings. It extends to data federation, enabling seamless querying (both read and write operations) across various databases and datalakes, streaming capabilities to ingest real-time data from Kafka and RabbitMQ, in addition to geospatial analytics through PostGIS and text analytics through Full-text search and GPText (built upon Apache SolR).

This expansive feature set is further enhanced by a wide array of in-database machine learning capabilities, which include support for Python, R, MADlib extension, and Containers. These capabilities enable the training, fine-tuning, and deployment of models within the Greenplum environment.

In this blog post, we will delve into the revolutionary impact of pgvector on Greenplum, exploring the new horizons it opens.

Furthermore, we will demonstrate why VMware Greenplum stands as a great choice for companies in search of a Modern Data Warehouse/Platform equipped with Vector Search capabilities tailored for analytical excellence.

pgvector — a PostgreSQL vector search extension

pgvector is an open-source vector database extension for PostgreSQL. It enables you to store vector embeddings and perform vector similarity search in PostgreSQL. It is particularly useful for applications involving natural language processing, such as those built on top of OpenAI’s GPT models.

pgvector extension brings great new capabilities to PostgreSQL, to become a Vector Database, it became a great choice for developers seeking to develop new AI-applications empowered with vector similarity search while keeping ACID compliance of an object-relational database like PostgreSQL.

The versatility and efficiency of vector databases make them valuable for a wide range of applications across various industries. Some of the prominent ones are:

• Natural language processing (NLP): Vector databases facilitate semantic search and text analysis through efficient handling of word embeddings or document vectors. They enable tasks like document classification, sentiment analysis, and keyword extraction, helping organizations make sense of massive volumes of textual data from sources like social media, forums, customer interactions — and currently most importantly — LLM prompt inputs.
• Image search and recognition: reverse image search, object detection, and facial recognition…
• Recommendation systems: for modern e-commerce and content platforms, analyze user preferences and content features to generate personalized and highly relevant recommendations to users in real-time.

VMware Greenplum + pgvector: a Massive Vector Database for Analytics

Thanks to the vector capabilities introduced by pgvector, Greenplum takes a giant leap forward, positioning itself as a unique data warehouse and a great vector database for analytical purposes.

However, you may wonder, “Does Greenplum truly fulfill the criteria of a robust and proven vector database?”

Revisiting the fundamentals, vector databases possess the ability to store and retrieve vectors as multidimensional points.

They amplify their capabilities by enabling efficient and rapid lookup of nearest neighbors within the N-dimensional space. Typically driven by k-nearest neighbor (k-NN) indexes and bolstered by algorithms such as Hierarchical Navigable Small World (HNSW) and Inverted File Index (IVF), vector databases offer additional functionalities like data management, fault tolerance, access control, and an adept query engine.

From the description above, we can extract the requirements for an advanced vector database, which ideally encompasses:

1. Vector-Store.

2. Vector Similarity Search with Diverse Distance Metrics.

3. Indexing.

4. Speed.

5. Reliability (Fault Tolerance and High Availability).

6. Distributed Architecture.

7. Scalability.

With this framework in mind, let’s delve into how Greenplum fulfills each of these requisites:

1. Vector-Store (Storing vectors and embeddings):

Before storing vectors inside Greenplum, you should enable the pgvector extension by running the following CREATE EXTENSION statement:

CREATE EXTENSION vector;

To create a table for storing vectors, use the following SQL command, adjusting the dimensions as needed.

CREATE TABLE vectors_table (
      id BIGSERIAL PRIMARY KEY,
      embedding VECTOR(3)
);

The command generates a table named vectors_table with an embedding column capable of storing vectors with 3 dimensions.

You can store vectors in your table, execute an INSERT statement similar to the following to store embeddings:

INSERT INTO vectors_table (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

This command inserts two new rows into the vectors_table table with the provided embeddings.

2. Vector Similarity Search with Diverse Distance Metrics:

To retrieve vectors and calculate similarity, use SELECT statements and the built-in vector operators.

For instance, you can find the most similar item to a given embedding using the following query:

SELECT * FROM vectors_table ORDER BY embedding <-> '[3,1,2]' LIMIT 1;

This query computes the Euclidean distance (L2 distance) between the given vector and the vectors stored in the vectors_table table, sorts the results by the calculated distance, and returns the top 5 most similar vectors.

• pgvector also supports inner product (<#>) and cosine distance (<=>).

3. Indexing:

Using an index on the vector column can improve query performance with a minor cost in recall.

You can add an index for each distance function you want to use. For example, the following query adds an index to the embedding column for the L2 distance function:

CREATE INDEX ON vectors_table USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

pgvector currently supports Inverted File Flat (ivfflat) indexing algorithm for approximate nearest neighbor search.

4. Speed:

The integration of pgvector fortifies Greenplum’s capacity for vector semantic searches.

Rigorous performance testing conducted within our Greenplum lab cluster, involving three segment hosts on GCP compute engines of c2-standard-16c, yielded remarkable results.

Our cluster demonstrated less than 250 milliseconds for a Cosine search in Greenplum across a Vector Table containing 500 million rows, each vectors with 512 dimensions !

Stay Tuned ! an upcoming blog post will provide further insights on Greenplum’s vector search performance.

5. Reliability (Fault Tolerance and High Availability):

Greenplum data warehouse is a reliable distributed system, it has automated Fault Detection and Self-Healing.

• Failover and Load Balancing: Mirroring enables automatic failover mechanisms, where if a primary segment becomes inaccessible, traffic can be redirected to the mirrored segment. This load balancing ensures that queries and workloads continue to be processed even during failures.
• Improved Data Integrity: Mirroring enhances data integrity by providing a second copy of the data that can be used for validation and comparison. This helps identify and rectify discrepancies or errors that may occur in the primary copy. Even if one copy of the data is compromised, the mirrored copy remains intact.

6. Distributed Architecture:

Greenplum’s Massively Parallel Processing (MPP) architecture divides data and tasks across multiple nodes, enabling parallel processing. This design enhances query performance and fault tolerance, as tasks can be distributed and executed simultaneously.

7. Scalability:

Greenplum’s elastic scalability allows for easy expansion by adding more nodes, accommodating growing data and user demands without compromising performance.

Conclusion:

Through these comprehensive demonstrations, we not only validated the technological capabilities of VMware Greenplum and the pgvector extension but also demonstrated why it stands as great choice for companies willing to augment their data platforms for new AI/ML workloads.

As a modern data warehouse and a massive vector database, Greenplum offers an unmatched suite of features encompassing vector storage, lightning-fast query performance, fault tolerance, high availability, scalability, and more.

In the ever-evolving landscape of data analytics, Greenplum data platform does not only meet but exceeds the demands of modern businesses seeking advanced analytical capabilities. Its integration with the pgvector extension propels it to the forefront of the industry, equipping organizations with the tools they need to extract meaningful insights from vast amounts of vector data.

Why VMware Greenplum as a Massive Vector Database for your Analytical needs ? was originally published in 𝐀𝐈 𝐦𝐨𝐧𝐤𝐬.𝐢𝐨 on Medium, where people are continuing the conversation by highlighting and responding to this story.

In this post, we will explore how Greenplum, an innovative and high-performance data warehouse for big data analytics and machine learning, can unleash the power of big data in real time. Additionally, we will delve into its advanced streaming capabilities that enable real-time data ingestion, processing, and analytics.

We will walk through the technical configuration and step-by-step instructions to set up this data pipeline.

Understanding Change Data Capture (CDC)

Change Data Capture is a technique used to capture and record changes made to a database, allowing applications to respond quickly to those changes. It tracks insert, update, and delete operations on database tables and transforms them into events. Downstream systems can then ingest this event stream for various purposes, including real-time analytics, data warehousing, and data integration.

Introducing RabbitMQ (Streaming Events through RabbitMQ)

RabbitMQ is a widely adopted message broker that facilitates the reliable exchange of messages between applications.

Last year, streams were introduced, and today, RabbitMQ can be used to support many use cases, whether as a message broker, for message streams, or doing both in unison. It supports multiple messaging protocols and communication patterns, making it an ideal choice for streaming data from various sources to a destination like Greenplum.

Using the RabbitMQ streaming layer, we can decouple the data source from the data warehouse, ensuring better scalability, reliability, and fault tolerance.

Leveraging Debezium for Change Data Capture

Debezium is an open-source platform that specialises in CDC for databases. It supports various database management systems, including DB2, MySQL, PostgreSQL, MongoDB, Oracle, and SQL Server.

Debezium captures row-level changes from database transaction logs and transforms them into a CDC event stream. It also ensures data integrity and consistency during the capture process.

Greenplum Streaming Capabilities

Greenplum, a PostgreSQL-based massively parallel processing (MPP) data warehouse, offers a powerful streaming capability that enables real-time data ingestion from external sources.

With Greenplum Streaming Server (GPSS), organisations can efficiently process data streams and integrate them directly into the Greenplum database.

In our real-time CDC setup, GPSS plays a vital role in ingesting the data from RabbitMQ, as captured by Debezium, and populating CDC events to tables in Greenplum to INSERT, UPDATE or DELETE records.

As an integral part of Greenplum’s ecosystem, GPSS delivers unmatched streaming capabilities that streamline data pipelines and supercharge analytical insights. It can handle massive volumes of streaming data with exceptional scalability (proven with 10 million events per second with more than 500 Billion rows per day in a Multi-Trillion Greenplum Database and Running Analytics and ML on top of this Database).

Real-time CDC from Any Database to Greenplum data warehouse

As a source database, we will use PostgreSQL and enable real-time CDC to Greenplum; the following components are used in the configuration:

• PostgreSQL 15 database
• Debezium Server 2.4
• RabbitMQ 3.12.2
• Greenplum 6.24 ( + Greenplum Streaming Server 1.10.1)

To facilitate the execution of this demo, all components will be deployed using docker-compose:

version: "3.9"
services:
  gpdb:
    image: docker.io/ahmedrachid/gpdb_demo:6.21
    depends_on:
      - rabbitmq
    privileged: true
    entrypoint: /usr/lib/systemd/systemd
    ports:
      - 5433:5432
    volumes:
      - ${PWD}/greenplum-db-6.24.0-rhel8-x86_64.rpm:/home/gpadmin/greenplum-db-6.24.0-rhel8-x86_64.rpm
      - ${PWD}/gpss-gpdb6-1.10.1-rhel8-x86_64.gppkg:/home/gpadmin/gpss-gpdb6-1.10.1-rhel8-x86_64.gppkg
      - ${PWD}/script_gpdb.sh:/home/gpadmin/script_gpdb.sh
  rabbitmq:
    image: rabbitmq:3-management-alpine
    container_name: rabbitmq
    ports:
      - 5672:5672
      - 15672:15672
      - 5552:5552
    environment:
      RABBITMQ_DEFAULT_PASS: root
      RABBITMQ_DEFAULT_USER: root
      RABBITMQ_DEFAULT_VHOST: vhost
  postgres:
    image: quay.io/debezium/example-postgres:2.1
    container_name: postgres
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
  debezium-server:
    image: quay.io/debezium/server:2.4
    container_name: debezium-server
    ports:
      - 8080:8080
    volumes:
      - ./conf:/debezium/conf
    depends_on:
      - rabbitmq
      - gpdb
      - postgres

The main configuration file for Debezium is conf/application.properties:

debezium.sink.type=rabbitmq
debezium.sink.rabbitmq.connection.host=rabbitmq
debezium.sink.rabbitmq.connection.port=5672
debezium.sink.rabbitmq.connection.username=root
debezium.sink.rabbitmq.connection.password=root
debezium.sink.rabbitmq.connection.virtual.host=vhost
debezium.sink.rabbitmq.connection.port=5672
debezium.source.connector.class=io.debezium.connector.postgresql.PostgresConnector
debezium.source.offset.storage.file.filename=data/offsets.dat
debezium.source.offset.flush.interval.ms=0
debezium.source.database.hostname=postgres
debezium.source.database.port=5432
debezium.source.database.user=postgres
debezium.source.database.password=postgres
debezium.source.database.dbname=postgres
debezium.source.topic.prefix=tutorial
debezium.source.table.include.list=inventory.customers
debezium.source.plugin.name=pgoutput
debezium.source.tombstones.on.delete=false
debezium.sink.rabbitmq.routingKey=inventory_customers

As you can see, we’re using Debezium to stream CDC events from a PostgreSQL table called inventory.customers to our RabbitMQ cluster.

Deploy PostgreSQL database:

Run the following Docker command to install & deploy a preconfigured PostgreSQL container:

docker compose up postgres -d

You can now connect to the database and explore the table we’re using to stream CDC events:

$ docker-compose exec postgres env PGOPTIONS="--search_path=inventory" bash -c 'psql -U $POSTGRES_USER postgres -c "SELECT * FROM customers;"'
  id  | first_name | last_name |         email
------+------------+-----------+-----------------------
 1001 | Sally      | Thomas    | sally.thomas@acme.com
 1002 | George     | Bailey    | gbailey@foobar.com
 1003 | Edward     | Walker    | ed@walker.com
 1004 | Anne       | Kretchmar | annek@noanswer.org
(4 rows)

Setup & Configure RabbitMQ:

To deploy our RabbitMQ cluster, we’ll use the same docker compose command as below:

docker compose up rabbitmq -d

Once you have deployed RabbitMQ using Docker Compose, you can configure it to suit your needs.

RabbitMQ provides a web-based management interface that you can use to configure various aspects of the RabbitMQ server.

To access the management interface, open a web browser and navigate to the IP address or domain name of your RabbitMQ instance, followed by the management port number (by default, 15672). In this example, you can access the management interface by navigating to http://localhost:15672

Once you have accessed the management interface, you can log in with the default credentials (root/root) or with the username and password specified in the Docker Compose file.

From the management interface, you should create the following:

• A new RabbitMQ Exchange called tutorial.inventory.customers
• A new RabbitMQ Stream called inventory.customersand bind it to the tutorial.inventory.customers exchange using the routingKey inventory_customers

Setup & Configure Debezium Server:

At this point, our PostgreSQL and RabbitMQ containers are running, but we haven’t started our Debezium Server yet. To do so, we will run the following:

docker compose up debezium-server -d

Once done, you should see a new open connection to the RabbitMQ cluster:

You should also see new messages in the RabbitMQ Stream ready to be consumed:

Setup Greenplum to ingest real-time CDC events:

To enable real-time CDC from RabbitMQ to Greenplum, we have a Greenplum 6.24 instance, pre-configured with Greenplum Streaming Server 1.10.1:

Firstly, we need to deploy the Greenplum container:

docker compose up gpdb -d

Once done, we should now create a customers table to load CDC events:

$ docker compose exec -ti gpdb bash
$ su - gpadmin
$ psql postgres -c 'CREATE TABLE public.customers (id INT, first_name TEXT, last_name TEXT, email TEXT) DISTRIBUTED BY (id);'

GPSS or Greenplum Streaming Server efficiently consumes and processes data streams streaming from Kafka and RabbitMQ (from a RabbitMQ queue or a RabbitMQ stream) into Greenplum Database.

It provides a flexible, scalable architecture that enables high-throughput data ingestion with minimal latency. GPSS is designed to handle various data formats, including TEXT, CSV, JSON, Avro, and more, making it well-suited for real-time CDC scenarios.

To start loading CDC events from RabbitMQ Stream, we should start our GPSS process using the following:

nohup gpss & 

Then, create our GPSS job with the configuration below:

DATABASE: postgres
USER: gpadmin
HOST: localhost
PORT: 5432
VERSION: 2
RABBITMQ:
  INPUT:
    SOURCE:
      SERVER: root:root@rabbitmq:5552
      STREAM: inventory.customers
      VIRTUALHOST: vhost
    DATA:
      COLUMNS:
        - NAME: j
          TYPE: json
      FORMAT: json
    ERROR_LIMIT: 25
  OUTPUT:
      TABLE: customers
      MODE: MERGE
      MATCH_COLUMNS:
        - id
      DELETE_CONDITION: ((j->>'payload')::json->>'op')='d'
      MAPPING:
           - NAME: id
             EXPRESSION: CASE WHEN ((j->>'payload')::json->>'op')='d' THEN (((j->>'payload')::json->>'before')::json->>'id')::int ELSE (((j->>'payload')::json->>'after')::json->>'id')::int END
           - NAME: first_name
             EXPRESSION: CASE WHEN ((j->>'payload')::json->>'op')='d' THEN (((j->>'payload')::json->>'before')::json->>'first_name')::text ELSE (((j->>'payload')::json->>'after')::json->>'first_name')::text END
           - NAME: last_name
             EXPRESSION: CASE WHEN ((j->>'payload')::json->>'op')='d' THEN (((j->>'payload')::json->>'before')::json->>'last_name')::text ELSE (((j->>'payload')::json->>'after')::json->>'last_name')::text END
           - NAME: email
             EXPRESSION: CASE WHEN ((j->>'payload')::json->>'op')='d' THEN (((j->>'payload')::json->>'before')::json->>'email')::text ELSE (((j->>'payload')::json->>'after')::json->>'email')::text END

Submit the GPSS job configuration:

$ gpsscli submit gpss_job.yaml

Start the GPSS job to begin capturing and processing data from RabbitMQ Stream:

$ gpsscli start gpss_job

Once started, you should now see customers job running:

[gpadmin@df7ac82e5633 ~]$ gpsscli list

JobName                             JobID                               GPHost          GPPort  DataBase        Schema          Table                           Topic           Status
gpss_job                            7f40703e01734ad570c31b028fdfc615    localhost       5432    postgres        public          customers                                       JOB_RUNNING

Insert data into the PostgreSQL customers table:

You should now see some data (4 records) coming into the Greenplum table, and you can also generate many CDC events by running INSERT/UPDATE or DELETEs on the source database.

Let’s insert some data dynamically:

INSERT INTO customers (id, first_name, last_name, email)
SELECT i, 
'First_' || i,
'Last_' || i,
'first' || i || '.last' || i || '@example.com' AS email
FROM generate_series(1010, 1000004) AS i;

Real-time CDC data being applied on Greenplum:

• After executing the INSERT INTO command, the CDC events are promptly applied to the Greenplum customers table in real time.

• On the other hand, you can UPDATE a record on the PostgreSQL source database:

As a result of this process, you can observe that the UPDATE event has been successfully applied on the Greenplum side, ensuring data consistency and synchronisation.

Conclusion

Greenplum’s streaming capabilities, RabbitMQ Streams and Debezium together, form a robust and efficient real-time CDC pipeline from any database to Greenplum.

This setup empowers organisations to harness the power of real-time data analytics, making informed decisions based on the latest data insights. By leveraging Greenplum’s streaming capability, businesses can gain a competitive edge in today’s data-driven landscape.

Authors

This blog post was co-written with my colleague: Martin Visser

📝 References :

1. Open-source Greenplum data warehouse: https://greenplum.org/
2. VMware Greenplum data warehouse: https://docs.vmware.com/en/VMware-Tanzu-Greenplum/index.html
3. Greenplum Streaming Server: https://docs.vmware.com/en/VMware-Greenplum-Streaming-Server/index.html
4. RabbitMQ: https://www.rabbitmq.com/
5. Debezium: https://debezium.io/

Thanks for reading! Any comments or suggestions are welcome! Check out other Greenplum articles here.

Streaming CDC events from Any Database to Greenplum Data Warehouse using RabbitMQ and Debezium was originally published in Greenplum Data Clinics on Medium, where people are continuing the conversation by highlighting and responding to this story.

The exponential progress of AI models over the past year, such as ChatGPT, inspired many organisations to enhance user experiences and unlock the full potential of their unstructured data, from texts to images to videos, by unleashing Generative AI and Large Language Models (LLMs).

In this blog post, you’ll learn how to harness the power of vector similarity search of pgvector extension within Greenplum data warehouse and combine it with OpenAI models to extract valuable insights from textual data at Petabytes large scale and take advantage of Greenplum’s incredible MPP architecture.

Introduction:

Companies started looking for technologies and ways to augment their data platforms for AI and use large language models for their Chatbots, Recommendation Systems or Search Engines…

But one specific challenge has been managing and deploying these AI models and storing and querying ML-generated embeddings at scale.

What are embeddings?

Embeddings refer to transforming data or complex objects like texts, images or audio into a list of numbers in a high-dimensional space.

This technique is used in every machine learning (ML) or Deep Learning (DL) algorithm that enables capturing / understanding of the meaning and context of data (semantic relationships) and knowledge of complex relationships and patterns within the data (syntactic relationships).

You can use the resulting vector representations for various applications, such as information retrieval, image classification, natural language processing, etc.

The following diagram visually represents what this looks like for word embeddings in 2D space.

You can notice that semantically similar words are close together in the embedding. For example: “apple” word is closer to “orange” than “dog” or “cat”.

After generating embeddings, companies can perform similarity searches within the vector space and build an AI applications such as product recommendation systems ...

Storing embeddings in Greenplum using pgvector

Greenplum 7 is well equipped and ready to store and query Vector Embeddings at large scale thanks to pgvector extension. This brings vector database capabilities to the Greenplum data warehouse, which enables users to perform fast retrieval and efficient similarity searches.

Using pgvector in Greenplum, you can set up, operate, and scale databases for your ML-enabled applications.

For example, a streaming service can use pgvector to provide a list of film recommendations similar to the one you just watched.

Why Greenplum & pgvector?

Many companies would like to store, query and perform vector semantic searches within their Entreprise Data Warehouse without managing another Vector database.

Fortunately, combining Greenplum and pgvector lets you build fast + scalable applications using embeddings from AI models and get to production sooner.

Build an AI-Assitant for your product documentation in Greenplum using pgvector and OpenAI.

Context:

We have all used a Chatbot like ChatGPT before and found it great for casual, general-purpose question-answers, but you may have also noticed that ChatGPT falls short when deep and domain-specific knowledge is needed. Also, it makes up answers to fill its knowledge gaps and never cites its sources.

But how can we improve this? How can we build a ChatGPT that accurately searches suitable data sources and answers questions?

TThe answer is: that making product documentation searchable and supplying a task-specific prompt to OpenAI can result in more reliable results. In other words, we will ask pgvector to search for a suitable dataset from the Greenplum table when a user asks a question. Then, we will provide it to OpenAI as a reference document to answer the users’ queries.

Embeddings in practice:

In this section, we will see embeddings in practice and learn how to use the open-source pgvector extension for Greenplum, which facilitates the storage of embeddings and enables querying for a vector’s nearest neighbours.

We demonstrate this functionality by building an Intelligent Chatbot using OpenAI and empowering it with Semantic Text Search to have more domain-specific knowledge about VMware Data Solutions, capable of answering deep technical questions about Greenplum, RabbitMQ, Gemfire, VMware SQL and VMware Data Service Manager, as in the following figure:

The main steps are:

1. Install & Enable the pgvector extension.

Following successful installation, you can start the storage of vector embeddings in Greenplum and perform semantic searches by enablingpgvectorrunning:

CREATE EXTENSION vector;

2. Create a Product Documentation table with VECTOR data-type

Next, let’s create a table to store our products documentation and their embeddings:

CREATE TABLE tanzu_documents (
  id bigserial primary key,
  content text,
  embedding vector(1536)
)
DISTRIBUTED BY (id)
;

pgvector introduces a new data type called a vector. We create an embedding column with the vector data type in the code above. The size of the vector defines how many dimensions the vector holds. OpenAI's text-embedding-ada-002 model outputs 1536 dimensions, so that we will use that for our vector size.

Since we’re using OpenAI API in this post, install the openai package on every Greenplum host running:

gpssh -f gphostsfile -e 'pip3 install -y openai'

We also create a text column named content to store the original product documentation text that produced this embedding.

Note: The table above is distributed by the “id” column across Greenplum segments,pgvectorextension works seamlessly and perfectly with Greenplum features. Hence, adding pgvector’s efficiency in managing and searching through massive amounts of data to Greenplum’s MPP capabilities from distribution to partitioning enable Greenplum users to build scalable AI applications.

3. Greenplum PL/Python Function to get OpenAI embeddings

Now, we should generate embeddings for our documents; here, we’ll use the OpenAI text-embedding-ada-002 model API to generate embeddings from the text.

The best way to do it is by creating a Python function inside Greenplum database leveragingPL/Python3u Procedural Language. The following Greenplum Python function returns a vectorembeddings for each input document.

CREATE OR REPLACE FUNCTION get_embeddings(content text)
RETURNS VECTOR
AS
$$

  import openai
  import os
  text = content
  openai.api_key = os.getenv("OPENAI_API_KEY")
  response = openai.Embedding.create(
         model="text-embedding-ada-002",
         input = text.replace("\n"," ")
     )
  
  embedding = response['data'][0]['embedding']
  return embedding

$$ LANGUAGE PLPYTHON3U;

4. Data Loading to Greenplum table

Load original texts into tanzu_documents table, specifically tocontent column, then update embedding column and generate OpenAI embeddings for every content using the previously created get_embeddings Python function :

UPDATE tanzu_documents SET embedding  = get_embeddings(content);

5. First Semantic Search Query

Let’s build our first semantic search query using pgvector’s cosine distance (using <=> operator) and find the most similar text to our question (i.e., the text with min distance): How to install Greenplum?

WITH cte_question_embedding AS 
  (
SELECT get_embeddings('How to create an external table in Greenplum using PXF to read from an Oracle database ?') 
        AS question_embeddings 
) 

SELECT id, content, embedding <=> cte_question_embedding.question_embeddings AS distance 
  FROM tanzu_documents, cte_question_embedding  
    ORDER BY embedding <=> cte_question_embedding.question_embeddings 
 ASC LIMIT 1 ;

pgvector introduces three new operators that can be used to calculate similarity: Euclidean distance (L2 distance) <-> , negative inner product<#>and cosine distance <=>

The SELECT statement should return the following output:

id       | 640
content  | title: Accessing External Data with PXF --- Data managed by your organisation may already reside in external sources such as Hadoop, object stores, and other SQL databases. The Greenplum Platform Extension Framework \(PXF\) provides access to this external data via built-in connectors that map an external data source to a Greenplum Database table definition. PXF is installed with Hadoop and Object Storage connectors. These connectors enable you to read external data stored in text, Avro, JSON, RCFile, Parquet, SequenceFile, and ORC formats. You can use the JDBC connector to access an external SQL database. > **Note** In previous versions of the Greenplum Database, you may have used the `gphdfs` external table protocol to access data stored in Hadoop. Greenplum Database version 6.0.0 removes the `gphdfs` protocol. Use PXF and the `pxf` external table protocol to access Hadoop in Greenplum Database version 6.x. The Greenplum Platform Extension Framework includes a C-language extension and a Java service. After configuring and initialising PXF, you start a single PXF JVM process on each Greenplum Database segment host. This long-running process concurrently serves multiple query requests. For detailed information about the architecture of and using PXF, refer to the [Greenplum Platform Extension Framework \(PXF\)](https://docs.vmware.com/en/VMware-Greenplum-Platform-Extension-Framework/6.6/greenplum-platform-extension-framework/overview_pxf.html) documentation. **Parent topic:** [Working with External Data](../external/g-working-with-file-based-ext-tables.html) **Parent topic:** [Loading and Unloading Data](../load/topics/g-loading-and-unloading-data.html)
distance | 0.12006528354516588

6. Similarity Search SQL Function:

Since we’re going to perform a similarity search over many embeddings. Let’s create a SQL function for that:

CREATE OR REPLACE FUNCTION match_documents (
  query_embedding VECTOR(1536),
  match_threshold FLOAT,
  match_count INT
)

RETURNS TABLE (
  id BIGINT,
  content TEXT,
  similarity FLOAT
)

AS $$

  SELECT
    documents.id,
    documents.content,
    1 - (documents.embedding <=> query_embedding) AS similarity
  FROM tanzu_documents documents
  WHERE 1 - (documents.embedding <=> query_embedding) > match_threshold
  ORDER BY similarity DESC
  LIMIT match_count;

$$ LANGUAGE SQL STABLE;

We will be using the match_documents function and provide the most similar text to the OpenAI model as the following:

SELECT t.id, t.content, t.similarity
  FROM match_documents(
          (select get_embeddings('How to create an external table in Greenplum using PXF to read from an Oracle database ?')) 
           , 0.8
           , 1) t
; 

id         | 640
content    | title: Accessing External Data with PXF --- Data managed by your organisation may already reside in external sources such as Hadoop, object stores, and other SQL databases. The Greenplum Platform Extension Framework \(PXF\) provides access to this external data via built-in connectors that map an external data source to a Greenplum Database table definition. PXF is installed with Hadoop and Object Storage connectors. These connectors enable you to read external data stored in text, Avro, JSON, RCFile, Parquet, SequenceFile, and ORC formats. You can use the JDBC connector to access an external SQL database. > **Note** In previous versions of the Greenplum Database, you may have used the `gphdfs` external table protocol to access data stored in Hadoop. Greenplum Database version 6.0.0 removes the `gphdfs` protocol. Use PXF and the `pxf` external table protocol to access Hadoop in Greenplum Database version 6.x. The Greenplum Platform Extension Framework includes a C-language extension and a Java service. After configuring and initialising PXF, you start a single PXF JVM process on each Greenplum Database segment host. This long-running process concurrently serves multiple query requests. For detailed information about the architecture of and using PXF, refer to the [Greenplum Platform Extension Framework \(PXF\)](https://docs.vmware.com/en/VMware-Greenplum-Platform-Extension-Framework/6.6/greenplum-platform-extension-framework/overview_pxf.html) documentation. **Parent topic:** [Working with External Data](../external/g-working-with-file-based-ext-tables.html) **Parent topic:** [Loading and Unloading Data](../load/topics/g-loading-and-unloading-data.html)
similarity | 0.8775289173395486

7. Vectors Indexing:

Our table may grow over time with embeddings, and we will likely want to perform a semantic search across billions of vectors.

What’s excellent about pgvector is its indexing capabilities to speed up queries and enable faster searching.

Vector indexes perform an exact nearest neighbour search (ANN/KNN); they are important to make ordering faster (ORDER BYclause) because vectors are not grouped by similarity, so finding the closest by sequential scan is a slow operation.

Each distance operator requires a different type of index. A good starting number of lists is rows / 1000 for up to 1M rows and sqrt(rows) for over 1M. Since we order by cosine distance, then we will usevector_cosine_ops index.

-- Create a Vector Index 
CREATE INDEX ON tanzu_documents 
USING ivfflat (embedding vector_cosine_ops)
WITH
  (lists = 300);

-- Analyze table
ANALYZE tanzu_documents;

Read more about pgvector indexing here: https://github.com/pgvector/pgvector#indexing

8. Provide an OpenAI model with the right dataset for a relevant answer

Build a PL/Python function that takes as input both: the user’s input and the most similar text to it then asks the OpenAI model to answer:

CREATE FUNCTION ask_openai(user_input text, document text)
RETURNS TEXT
AS
$$

   import openai
   import os

   openai.api_key = os.getenv("OPENAI_API_KEY")
   search_string = user_input
   docs_text = document

   messages = [{"role": "system",
                "content": "You concisely answer questions based on text provided to you."}]

   prompt = """Answer the user's prompt or question:

   {search_string}

   by summarising the following text:

   {docs_text}

   Keep your answer direct and concise. Provide code snippets where applicable.
   The question is about a Greenplum / PostgreSQL database. You can enrich the answer with other
   Greenplum or PostgreSQL-relevant details if applicable.""".format(search_string=search_string, docs_text=docs_text)

   messages.append({"role": "user", "content": prompt})

   response = openai.ChatCompletion.create(model="gpt-3.5-turbo", messages=messages)
   return response.choices[0]["message"]["content"]

$$ LANGUAGE PLPYTHON3U;

9. Building a smarter search function

As we mentioned before, ChatGPT doesn’t just return existing documents. It’s able to assimilate various information into a single, cohesive answer. To do this, we need to provide GPT with some relevant documents and a prompt that it can use to formulate this answer.

As a final step, we should combine previous functions into a single process to serve our Intelligent AI-Assitant application.

Our previous functions and Embeddings can help solve this by splitting prompts into a two-phased process:

1. Query our embedding database for the most relevant documents related to the question.
2. Inject these documents as context for the OpenAI model to reference in its answer.

CREATE OR REPLACE FUNCTION intelligent_ai_assistant(
  user_input TEXT
)

RETURNS TABLE (
  content TEXT
)
LANGUAGE SQL STABLE
AS $$

  SELECT
    ask_openai(user_input, 
              (SELECT t.content 
                FROM match_documents(
                      (SELECT get_embeddings(user_input)) , 
                        0.8, 
                        1) t
                )
    );

$$;

The SQL function above takes user input, converts it to embeddings, performs a semantic text search using pgvectoron tanzu_documents table to find the most relevant documentation, and lastly feeds it as a reference text to OpenAI API call, which returns the final answer.

10. Build our own Chatbot 🤖 empowered with Semantic Text Search capability using OpenAI and Streamlit 🎈

Finally, we’ve developed a Streamlit 🎈 Chatbot 🤖 that understands our documents and uses the Greenplum data warehouse with pgvector semantic text search.

This Chatbot Streamlit application is available here: https://greenplum-pgvector-chatbot.streamlit.app/

You can find the source code here: https://github.com/ahmedrachid/streamlit-chatbot-greenplum

🚀 Conclusion

To conclude, companies wishing to build scalable AI applications can unleash Greenplum performance and massively parallel processing capabilities, combine it with the pgvectorto process, and perform fast retrieval, similarity and semantic search over massive amounts of vector embeddings and unstructured data.

📝 References :

1. Open-source Greenplum data warehouse: https://greenplum.org/
2. VMware Greenplum data warehouse: https://docs.vmware.com/en/VMware-Tanzu-Greenplum/index.html
3. pgvector extension - Open-source vector similarity search for Postgres: https://github.com/pgvector/pgvector

Thanks for reading! Any comments or suggestions are welcome! Check out other Greenplum articles here.

Building large scale AI-powered search in Greenplum using pgvector and OpenAI was originally published in Greenplum Data Clinics on Medium, where people are continuing the conversation by highlighting and responding to this story.

This blog constitutes the third part of the “Greenplum for End-to-End Data Science & ML” series. In this article, we demonstrate combining Greenplum Data Warehouse MPP capabilities with Python’s rich ecosystem to make the end-to-end Machine Learning / NLP model development experience significantly faster.

The figure below illustrates a much-needed paradigm shift when training and inferencing ML models on real-life, large datasets.

• In-client machine learning requires data movement from the data source to the computing environment, but this approach becomes prohibitive when the amount of data used is large (e.g. 10s of GBs ~ PBs).
• In-database machine learning pushes the computing down to the data source, eliminating data movement and significantly increasing scalability and performance.

Setup and connectivity

1. Install Packages

pip install ipython-sql pandas numpy sqlalchemy plotly-express sql_magic pgspecial

2. Import Packages

import pandas as pd
import numpy as np
import os
import sys
import plotly_express as px
# For DB Connection
from sqlalchemy import create_engine
import psycopg2
import pandas.io.sql as psql
import sql_magic

3. Database Connectivity

There are several ways to establish a connection to Greenplum. With a Jupyter notebook, one can use SQL magic. This allows running SQL queries in a Jupyter cell. Link to documentation: https://pypi.org/project/ipython-sql/ Installation on the client:

pip install ipython-sql

For use at the notebook level, it suffices to establish the connection:

%load_ext _ sql
%sql postgresql://<user>:<password>@<IP_address>:<port>/<database_name>

Then run the SQL commands in a cell like this:

%%sql 
SELECT version ();

You can also use the psycopg2 connector or pyodbc (there are also other options via JDBC, for instance)

Dataset Overview

The dataset consists of 1967 stock market financial news written in English and categorised by sentiment.

Data Fields are:

• docid: the unique identifier of financial news
• original_news: raw text of financial news
• label: a label corresponding to the class as a string: positive or negative

%%sql
SELECT * FROM ds_demo.sentiment_news
LIMIT 5;

%sql SELECT count(*) FROM ds_demo.sentiment_news;

NLP — Sentiment Analysis with PL/Python

Sentiment analysis aims to determine the sentiment strength from a textual source for sound decision-making; it’s widely used in financial companies to predict whether the news may positively or negatively influence the future stock price.

First, we’ll explore and visualise our dataset, combining SQL with Pandas & Plotly Python packages.

1. Sentiment classes distribution of our dataset:

%%read_sql df_count_sentiment
SELECT label, count(*) AS number_of_news
FROM ds_demo.sentiment_news
GROUP BY 1
ORDER BY 2 ASC

color_discrete_map = {'negative': 'rgb(255,0,0)', 
                      'positive': 'rgb(0,255,0)' }

px.pie(df_count_sentiment,
        values = 'number_of_news', 
        names = 'label', 
        color = 'label', 
        color_discrete_map=color_discrete_map)

The chart shows that our dataset is imbalanced; we have twice as positive news than negative news. This can impact our model performance and might result in underfitting in real-life data science projects.

2. Number of Characters

%%read_sql df_max_length
SELECT max(length(original_news))
FROM ds_demo.sentiment_news;

The most extended news of our dataset has less than 300 characters, which means that the sentiment can be captured from a short and synthetic text.

3. Number of characters by sentiment class:

%%read_sql df_len_news
SELECT original_news, 
       label, 
       length(original_news) * 1.0 / max(length(original_news)) 
       OVER (partition by NULL) AS ratio_chars
FROM ds_demo.sentiment_news

px.box(df_len_news, 
       y="ratio_chars", 
       x ='label',
       color='label', 
       title = 'Number of characters distribution by sentiment class',
       color_discrete_map=color_discrete_map)

4. Number of words by sentiment class:

%%read_sql df_len_words
SELECT original_news, label, 
       ARRAY_LENGTH(STRING_TO_ARRAY(original_news, ' '), 1) 
       AS number_of_words
FROM ds_demo.sentiment_news

px.box(df_len_words, 
       y="number_of_words", 
       x ='label', 
       color='label', 
       title = 'Number of words distribution by sentiment class', 
       color_discrete_map=color_discrete_map)

Positive news tends to have slightly more words and characters than negative news, but overall we can’t base our analysis on length.

Data Preprocessing — Text Cleaning

Text cleaning here refers to removing or transforming specific parts of the text so that the text becomes more easily understandable for NLP models learning the text. This often enables NLP models to perform better by reducing noise in text data.

To do that, we create a PL/Python and apply simple preprocessing actions:

• Firstly, change texts to lowercase.
• Then, delete any special characters and URLs.
• Finally, delete any multiple spaces.

%%sql 
DROP FUNCTION IF EXISTS text_prepare(text);
CREATE OR REPLACE FUNCTION text_prepare( content text)
RETURNS text
AS $$
    import re     
    text = content
    replace_by_space_re = re.compile('[/(){}\[\]\|@,;]')
    bad_symbols_re = re.compile('[^0-9a-z #+_]')
    links_re = re.compile('(www|http)\S+')

    text = text.lower()  # lowercase text
    text = re.sub(replace_by_space_re," ",text) 
    text = re.sub(bad_symbols_re, "",text) 
    text = re.sub(links_re, "",text) 
    text = re.sub(' +', ' ', text)
    return text.strip()
$$ LANGUAGE plpython3u
;

We store processed news in a new column called cleaned_text, which will be used for model training.

%%sql 
ALTER TABLE ds_demo.sentiment_news ADD COLUMN cleaned_text text;
UPDATE ds_demo.sentiment_news 
 SET cleaned_text = text_prepare(original_news::text);

A quick overview of cleansed news:

%sql SELECT * FROM ds_demo.sentiment_news LIMIT 2;

Train a Sentiment Analysis model with PL/Python

Training a Natural Language Processing model to automatically predict the Sentiment of financial news can help traders and investors to build their trading/quantitative strategies.

Sentiment analysis is a text classification task. In this section, we use the Bag of Words technique to convert texts into numerical representation such that the same can be used to train a Logistic Regression model.

Model training — Term Frequency Logistic Regression

Let’s write PL/Python function that can be called like any other SQL function. The integration is effortless, as Python has endless libraries for Machine Learning.

Moreover, besides giving full support to Python, PL/Python also provides convenient functions to run any parametrised query. So, executing Machine Learning algorithms can be a question of a couple of lines.

Let’s take a look:

%%sql 
DROP FUNCTION IF EXISTS train_sentiment_news(text[],text[]);
DROP TYPE IF EXISTS news_type;
-- Create a new type for our PL/Python function output
CREATE type news_type AS (content text, label text, prediction text);
-- PL/Python function using Python3.9
CREATE FUNCTION train_sentiment_news( cleaned_text text[], label text[])
RETURNS SETOF news_type
AS $$
    import pandas as pd
    from sklearn.feature_extraction.text import CountVectorizer
    from sklearn.model_selection import train_test_split
    from sklearn.linear_model import LogisticRegression

    X = cleaned_text
    y = label
    df= pd.DataFrame()
    df['content'] = X
    df['label'] = y
    X_train, X_test, y_train, y_test = train_test_split(df['content'], df['label'], test_size=0.2, random_state=42)

    vectorizer = CountVectorizer(min_df=4, stop_words='english')

    X_train = vectorizer.fit_transform(X_train)
    X_test = vectorizer.transform(X_test)

    # LOGISTIC REGRESSION
    logreg = LogisticRegression()
    # TRAIN 
    logreg.fit(X_train, y_train)
    X = vectorizer.transform(df['content'])
    # PREDICTIONS ON FULL DATASET 
    lr_prediction = logreg.predict(X)
    df['prediction'] = lr_prediction
    
    return [{'content': str(row['content']), 
             'label': str(row['label']), 
             'prediction': str(row['prediction'])} 
            for index, row in df.iterrows()]

$$ LANGUAGE plpython3u;

As you can see, PL/Python is straightforward.

• Firstly, we import the packages we need; we use pandas and scikit-learn libraries.
• We need to load inputs (news and labels) into a dataframe and transform the numeric variables into numeric type using Bag of Words / CountVectorizer
• Then, we call Logistic Regression and train it on the training set (80% of our dataset).
• Finally, we return the prediction on the whole dataset as a list of news_type types.

The final line specifies the extension language: in this case, we are using Python3, and for that reason, the extension is called plpython3u. If you want to execute it in Python2, use the extension language named plpythonu.

Greenplum also provides another language handler, PL/Container, which runs PL/Container in a Docker. The execution could indeed be safer.

Show predictions

We can check predictions made by our model.

The function train_sentiment_news() takes two arrays as inputs, so we need to apply ARRAY_AGG() function to concatenate all records of columns cleaned_text and labels together as two arrays.

Furthermore, since train_sentiment_news() returns a set of new_type, we need to decompose every record of results into different columns using CTEs (Common Table Expression).

%%read_sql df_preds
WITH cte_data_array_agg AS (
        SELECT ARRAY_AGG(cleaned_text) AS contents, 
                ARRAY_AGG(label) AS labels 
        FROM ds_demo.sentiment_news
    ),
    cte_predictions AS (
        SELECT train_sentiment_news(t.contents, t.labels) 
        FROM cte_data_array_agg
    )
SELECT (train_sentiment_news::news_type).* FROM cte_predictions;

Model Evaluation

Now, let’s evaluate our model by calculating: Accuracy, F1-score, Precision, Recall

%%sql 
DROP FUNCTION IF EXISTS metrics_report(text[],text[]);
DROP TYPE IF EXISTS prediction_type;
CREATE type prediction_type AS (accuracy float, f1_score float, precision float, recall float);
CREATE OR REPLACE FUNCTION metrics_report(label text[], prediction text[])
RETURNS prediction_type
AS $$
    from sklearn.metrics import precision_recall_fscore_support as score
    from sklearn.metrics import accuracy_score
    import numpy as np

    y_true = label
    y_pred = prediction

    accuracy = accuracy_score(y_true, y_pred)
    precision, recall, f1_score, support = score(y_true, y_pred)

    precision = float(np.mean(precision))
    f1_score = float(np.mean(f1_score))
    recall = float(np.mean(recall))
    accuracy = float(accuracy)

    return {'accuracy': accuracy,
            'f1_score': f1_score,
            'precision': precision,
            'recall': recall
    }
$$ LANGUAGE plpython3u
;

We apply ARRAY_AGG() to concatenate columns and decompose composite-type results using CTEs.

%%read_sql df_preds
WITH
  cte_data_array_agg AS (
    SELECT ARRAY_AGG(cleaned_text) AS contents, ARRAY_AGG(label) AS labels 
      FROM ds_demo.sentiment_news
  ),
  cte_predictions AS (
      SELECT train_sentiment_news(t.contents, t.labels) 
      FROM cte_data_array_agg
  ),
  cte_label_pred AS (
    select (train_sentiment_news::news_type).* 
      FROM cte_predictions
  ),
  cte_metrics_report AS (
      SELECT metrics_report(array_agg(label), array_agg(prediction)) 
      FROM cte_label_pred
  )

SELECT (metrics_report::prediction_type).* FROM cte_metrics_report

The model has achieved an accuracy of 92.93%, whereas recall and precision are slightly lower than accuracy.

Model Deployment — Storing the model

It doesn’t make much sense to create a model and not do anything with it. So, we will need to store it as binaries using the “bytea” data type.

Store it as binary in a SQL table

%%sql 
DROP FUNCTION IF EXISTS save_nlp_models(text[],text[]);
DROP TYPE IF EXISTS model_type CASCADE;
CREATE TYPE model_type as (model_logreg bytea, model_bow bytea);
CREATE FUNCTION save_nlp_models(cleaned_text text[], label text[])
RETURNS model_type
AS $$

    import pandas as pd
    from sklearn.feature_extraction.text import CountVectorizer
    from sklearn.model_selection import train_test_split
    from sklearn.linear_model import LogisticRegression
    import pickle
    
    X = cleaned_text
    y = label
    df= pd.DataFrame()
    df['content'] = X
    df['label'] = y
    X_train, X_test, y_train, y_test = train_test_split(df['content'], 
                                                        df['label'], 
                                                        test_size=0.2, 
                                                        random_state=42)

    vectorizer = CountVectorizer(min_df=4, stop_words='english')

    X_train = vectorizer.fit_transform(X_train)
    X_test = vectorizer.transform(X_test)

    # LOGISTIC REGRESSION
    logreg = LogisticRegression()
    logreg.fit(X_train, y_train)
    X = vectorizer.transform(df['content'])
    lr_prediction = logreg.predict(X)
    df['prediction'] = lr_prediction
    
    # Save Logistic Regression model
    model_logreg = pickle.dumps(logreg)
    # Save CountVectorizer 
    model_countvectorizer = pickle.dumps(vectorizer)
    
    return {'model_logreg': model_logreg
            ,'model_bow': model_countvectorizer}
$$ LANGUAGE plpython3u
;

To do so, let’s create a table ds_demo.saved_models to save the model first:

%%sql
DROP TABLE IF EXISTS ds_demo.saved_models;
CREATE TABLE ds_demo.saved_models (model_logreg bytea, 
                                   model_bow bytea, 
                                   model_name text);

In this case, our table has just a model_name and two byte array fields (one for Logistic Regression and the other for Bag of Words) that is the actual model serialised. Please note that it is the same data type as the one that our defined Scikit-learn models return.

Once we have the table, we can easily insert a new record with the model:

%%sql
WITH cte_models AS (
        SELECT save_nlp_models(t.contents, t.labels) 
    FROM (
        SELECT ARRAY_AGG(cleaned_text) AS contents, 
               ARRAY_AGG(label) AS labels 
          FROM ds_demo.sentiment_news) t
      ),
    cte_casted_model AS (
        SELECT (save_nlp_models::model_type).* 
        FROM cte_models)
    
INSERT INTO ds_demo.saved_models 
SELECT model_logreg, model_bow, 'nlp_sentiment_analysis_bow_logreg' AS model_name FROM cte_casted_model;

Show binaries

%%sql 
SELECT model_name, model_logreg::text, model_bow::text 
FROM ds_demo.saved_models;

Displaying Model Info

So far, we have been able to create a model and store it. But getting it directly from the database isn’t very useful.

Therefore, we must return to Python to display useful information about our model. This is the function we are going to use:

%%sql
DROP FUNCTION get_model_info(text,text,text);
CREATE OR replace FUNCTION get_model_info(model_table text, model_column text, model_name text) 
RETURNS text
AS $$
    from pandas import DataFrame
    import pickle
    rv = plpy.execute('SELECT %s FROM %s WHERE model_name = %s;' % (plpy.quote_ident(model_column), model_table, "'"+model_name+"'"))
    model = pickle.loads(rv[0][model_column])
    
    return str(model.get_params())
 
$$ LANGUAGE plpython3u;

Let’s start from the beginning: we are passing, again, the table containing the models and the column that holds the binary. The pickle.load() function reads the output.

(Here, you can see how results from a plpython query plpy.execute are loaded into Python).

Once the model is loaded, we return model.get_params(), where our logistic regression parameters are stored.

This is just an example of how to output a specific model feature. You can create similar functions to return other features or even all features.

Let’s take a look at what it returns:

%%sql
select get_model_info('ds_demo.saved_models','model_logreg','nlp_sentiment_analysis_bow_logreg');

Model inference — predict new data-feed

Now that we have a model let’s use it to make predictions! Invoking the model is simple and can be done in SQL using PL/Python.

%%sql 
DROP FUNCTION IF EXISTS predict_sentiment(text, bytea, bytea);
CREATE FUNCTION predict_sentiment(cleaned_text text, model_logreg bytea, model_bow bytea)
RETURNS text
AS $$
    import pickle
    
    logreg = model_logreg
    vectorizer = model_bow
    texts = cleaned_text
    
    # Save Logistic Regression model
    model_logreg_bytes = pickle.loads(logreg)
    # Save CountVectorizer 
    model_countvectorizer = pickle.loads(vectorizer)
    
    return list(model_logreg_bytes.predict(model_countvectorizer.transform([texts])))[0]
$$ LANGUAGE plpython3u
;

Compared to the previous function, we add one input parameter (cleaned_text), passing the input representing a piece of financial news for which we want to get the sentiment.

%%sql
SELECT content, predict_sentiment(text_prepare(t.content), b.model_logreg, b.model_bow)
FROM
   ds_demo.financial_news t,
    ds_demo.saved_models b 
LIMIT 5;

We can now process larger datasets and show positive/negative news distribution.

%%sql
SELECT predict_sentiment, count(*)
FROM (
  SELECT content, predict_sentiment(t.content, b.model_logreg, b.model_bow)
  FROM
    ds_demo.financial_news t,
    ds_demo.saved_models b 
    LIMIT 10000
  ) results
GROUP BY 1;

Conclusion

We have seen in this article that you can train and use NLP & Machine Learning without leaving the Greenplum data warehouse. Greenplum has strong analytical capabilities that make them well suited for data science problems at a massive scale.

Using Greenplum’s in-database Machine Learning capabilities in PL/Python and PL/R enable data scientists to harness the vast ecosystem of machine-learning libraries in Python and R to analyse massive datasets.

If you have enjoyed this post, stay tuned for our next entry in the series on Data Science with GreenplumPython, a Python wrapper for Greenplum and PostgresQL, which further enhances the data scientist’s user experience when working with big data.

Greenplum for Data Science Blog Series Part 3: Scalable in-database Machine Learning & NLP with… was originally published in Greenplum Data Clinics on Medium, where people are continuing the conversation by highlighting and responding to this story.

The Growth, Challenge, and Opportunity of Data

We are in the digital age, and everything we do with our smartphones, tablets, computers, and even household appliances produces an enormous amount and variety of data. While this may sound exciting, collecting data for the sake of having more data is not a meaningful exercise or a productive one. We need to understand what user goals or business outcomes might benefit from this data and invest the time and effort to extract value from the data. And this is where data scientists come into the picture.

How Data Scientists Might Help

Among all fields related to data, data science is a field of study that leverages data and quantitative modelling to build meaningful outcomes for users. The data scientist engages in a wide range of activities in this context, including but not limited to the following:

1. Partnering with designers to build empathy with users to understand their needs and explore how we might use data and algorithms to delight them
2. Prioritise what to build next with product managers.
3. Collaborate with data engineers on identifying and preparing data for analysis.
4. Explore data and build machine models to serve user needs — most commonly coding in Python, R, or SQL.
5. Integrate with software engineers to deploy models as services to be consumed by applications or directly by users.
6. Iteratively repeat (1)-(5) to continuous drive value for users

Focus for this Series: Technical Approaches & Tools to Extract value from Big Data

The activities listed above include aspects related to how data scientists work (e.g., user-centred, lean, agile) and also the tools & technologies needed to be most productive. We focus this technical blog series on the tooling component primarily related to (4) in the list above.

Specifically, we explore scenarios where the data scientist explores data and builds machine-learning models with a massive set of data. In this context, the data scientist may encounter particular technical challenges, including the following:

• Slow data science pipeline with large data volumes
• Hitting the ceilings of memory and computing in traditional Python/R runtimes
• Limited analytical functionality in platforms that enable big data computing

This series of technical blog posts addresses these challenges, focusing on VMware Greenplum as a critical enabling technology.

Why Greenplum?

Exporting data from a database and importing it into a server or desktop environment with commonly used tools for data science (e.g. Python, R) is not an ideal workflow for big data analytics. As described above, data scientists may need help with these tools' memory and scalability limitations and restrictive bottlenecks associated with transferring large amounts of data between different platforms.

This is where leveraging the right tool becomes an important decision for the data scientist. In this post, we focus on Greenplum, a massively parallel processing PostgreSQL engine which provides built-in tools for data scientists for high-scale data exploration and model training. These tools and extensions include:

• Apache MADlib for machine learning
• Procedural language extensions to massively parallelise Python & R
• PostGIS for geospatial analytics and GPText for text search and processing
• Interoperability with dashboarding tools such as Tableau, PowerBI…

In this post, we describe how to start with Greenplum and share examples of how a data scientist might use the abovementioned tools.

Part 1: Setup and connectivity

1. Install packages:

!pip install ipython-sql pandas numpy sqlalchemy plotly-express sql_magic pgspecial

2. Import packages:

import pandas as pd
import numpy as np
import os
import sys
import plotly_express as px
# For DB Connection
from sqlalchemy import create_engine
import psycopg2
import pandas.io.sql as psql
import sql_magic

3. Greenplum Database Connectivity:

There are several ways to establish a connection to Greenplum. With a Jupyter notebook, one can use SQL magic. This allows running SQL queries in a Jupyter cell.

• Installation on the client:

!pip install ipython-sql

For use at the notebook level, it suffices to establish the connection:

% load_ext _ sql
% sql postgresql://<user>:<password>@<IP_address>:<port>/<database_name>

Then run the SQL commands in a cell like this:

%% sql 
SELECT version ();

You can also use the psycopg2 connector or pyodbc (there are also other options via JDBC, for instance)

Part 2: Dataset Overview

In this blog series, a Daily Financial News for stocks dataset from Kaggle with Licence CC0: Public Domain has been selected for use. We will use Greenplum and follow the data science cycle (Data Exploration — Data Preparation — Data Modelling — Model Evaluation — Model Deployment) to train an NLP model to process Sentimental Analysis for news.

The dataset contains Raw headlines from Benzinga.com and its partners. Its size is augmented up to around four million rows, which is quite an extensive dataset for manipulation, and it has seven columns:

• docid — integer: index unique
• title — text: headline of Row
• content — text: equivalent to the title
• url — text: link related to News
• publisher — text: News publisher
• date — date: publish date
• stock — text: stock tickers symbol related to News

To begin with this dataset, we will process an Exploratory Data Analysis with SQL command, including Greenplum Functions, without using other extensions (which will be discovered in the following blogs of the series).

Part 3: Data Preparation

The dataset is saved in the table source_financial_news. Let's take a look at one row of this table:

%%sql
SELECT * FROM source_financial_news LIMIT 1;

How many rows it contains precisely:

%%sql
SELECT count(*) FROM source_financial_news;

Partitioned table for better analytical performance

Since old news won’t be used frequently, let’s create a table ds_demo.financial_news partitioned by datewhere we are interested only in News published after 2010.

Setting the distributed key to the unique index docid allows data to be distributed equitably across different segments.

Moreover, setting storage to column-oriented and appendoptimized to True improves the analytical performance overall.

%%sql 
DROP TABLE IF EXISTS ds_demo.financial_news;
CREATE TABLE ds_demo.financial_news 
(
    LIKE source_financial_news
)
WITH (appendoptimized=true, orientation=column)
DISTRIBUTED BY(docid)
PARTITION BY RANGE (date)
(
    START (date '2010-01-01') INCLUSIVE
    END (date '2024-01-01') EXCLUSIVE
    EVERY (INTERVAL '1 month'), 
    DEFAULT PARTITION old_news
);

-- Insert data from the source table into a newly created partitioned table
INSERT INTO ds_demo.financial_news SELECT * FROM source_financial_news;

Thanks to command \d+, a description of the newly created Analytical table is shown below:

%sql \d+ ds_demo.financial_news

Note: With Greenplum 7, partitions don’t need to follow the same settings, and users can customise them according to their needs.

Check that our table is evenly distributed

Now our new table has been created, let’s check its distribution by calculating the standard deviation of the number of rows per segment.

%%sql
-- Standard deviation to check table distribution
SELECT STDDEV(count) 
FROM (
    SELECT gp_segment_id, COUNT(*)
    FROM ds_demo.financial_news
    GROUP BY 1
) table_distribution ;

The standard deviation score is low (399.5784) compared to the mean 163383.333. Our table is evenly distributed!

Partitions to scan only needed data

With Partitions, there is no need to scan the older partitions at all to answer the query below.

%%sql
-- Show the execution plan "EXPLAIN" and check scanned partitions
EXPLAIN SELECT COUNT(*) 
        FROM ds_demo.financial_news 
          WHERE date >= '2020-01-01'::date

Furthermore, if we add a superior condition sup_date on date, there is no need to scan partitions with date superior to sup_date

Part 4: Exploratory Data Analysis with SQL & MADlib

After preparing our ds_demo.financial_news table, we will use SQL (sqlmagic for interactive results with the Greenplum database) and MADlib, an open-source SQL-based library for scalable in-database analytics.

It provides data-parallel implementations of mathematical, statistical, graph and machine learning methods for structured and unstructured data.

1. Another overview of the first two rows of Table

%%sql 
SELECT * FROM ds_demo.financial_news LIMIT 2 ;

2. Apache MADlib for robust and powerful statistics using SQL

• Make sure that the MADlib extension is enabled!

%sql SELECT madlib.version();

2.1. Statistical description of the table — MADlib

MADlib’s summary() function produces summary statistics for any data table. The function invokes various methods from the MADlib library to provide the data overview.

%%sql
DROP TABLE IF EXISTS ds_demo.financial_news_summary;
SELECT * FROM madlib.summary( 
        'ds_demo.financial_news', 'ds_demo.financial_news_summary'  
);

The output of the function summary() is saved in the table ds_demo.financial_news_summary.

%%sql
SELECT 
    target_column, distinct_values, missing_values, blank_values, 
    min, max, mean, median, variance, most_frequent_values
FROM ds_demo.financial_news_summary;

3. Number of news per date

To see the evolution of the number of news over the months:

1. Firstly, convert the date column into Year-Month format using the to_char SQL function.
2. Then, group by the formatted date and calculate the frequencies using COUNT(*)
3. Order final results based on the date.

%%read_sql df_news_per_date
SELECT 
    to_char(date,'YYYY-MM') as date, 
    COUNT(*) 
FROM ds_demo.financial_news 
    WHERE date IS NOT NULL
GROUP BY 1
ORDER BY 1
DESC;

Let’s now visualise the aggregated results using Plotly:

px.line(df_news_per_date, 
        x = 'date', 
        y = 'count', 
        title='Evolution of number of Stock Market News over time')

4. Histogram of Financial News Length

Visualise the distribution of stock market news length:

%%read_sql df_histogram
WITH drb_stats AS (
    SELECT min(length(title)) AS min,
           max(length(title)) AS max
      FROM ds_demo.financial_news
),
     histogram AS (
   SELECT width_bucket(length(title), min, max, 10) AS bucket,
          int4range(MIN(length(title)), MAX(length(title)), '[]') AS range,
          COUNT(*) AS freq
     FROM ds_demo.financial_news, drb_stats
     GROUP BY bucket
     ORDER BY bucket
)

SELECT bucket, range, freq,
        repeat('■',
               (   freq::FLOAT
                 / MAX(freq) OVER()
                 * 30
               )::INT
        ) AS bar
FROM histogram
ORDER BY bucket ASC
;

fig = px.histogram(y = df_histogram.freq, 
                   x = df_histogram.range.astype(str), 
                   nbins=10, 
                   title = 'News titles length histogram')
fig.update_layout(yaxis_title="Frequencies", xaxis_title = 'Bucket (range)')
fig.show()

5. Number of news per stock

Use MADlib summary results to visualise the most frequent stocks.

%%read_sql df_stock_frequencies
SELECT most_frequent_values, mfv_frequencies 
FROM ds_demo.financial_news_summary 
  WHERE target_column = 'stock';

px.bar(y = df_stock_frequencies.most_frequent_values.values[0], 
       x = df_stock_frequencies.mfv_frequencies.values[0], 
       color = df_stock_frequencies.most_frequent_values.values[0],
       labels={'x':'News frequency', 'y': 'Stock'},text_auto='.2s',
       title="10 Most Frequent Stocks")

6. Number of news per publisher (source)

Let’s find the most dominant publishers in our dataset:

%%read_sql df_publisher_frequencies
SELECT most_frequent_values, mfv_frequencies 
FROM ds_demo.financial_news_summary 
  WHERE target_column = 'publisher';

px.bar(y = df_publisher_frequencies.most_frequent_values.values[0], 
       x = df_publisher_frequencies.mfv_frequencies.values[0], 
       color = df_publisher_frequencies.most_frequent_values.values[0],
       labels={'x':'News frequency', 'y': 'Publisher'},text_auto='.2s',
       title="Top 10 - Frequent News sources")

7. Which website is most present in our dataset?

%%read_sql df_url_domain
SELECT 
        split_part(REGEXP_REPLACE(url, '^(https?://)?(www\.)?', ''), '/', 1) AS source_url,
        count(*) as frequency
FROM (SELECT 
        CASE WHEN url IS NOT NULL THEN url ELSE 'Other' END 
        AS url 
      FROM ds_demo.financial_news
      ) t
GROUP BY 1
ORDER BY 2 ASC;

fig = px.pie(df_url_domain, 
             names = 'source_url', values = 'frequency',  
             width=800, height=600, 
             title='Source websites of Stock Market News')

fig.update_traces(textposition='inside', textinfo='percent+label')
fig.show()

Conclusion

To conclude, Data Analysts & Scientists can unleash Greenplum performance and massively parallel processing capabilities to process and explore large datasets using built-in analytics functions by combining SQL & Apache MADlib and visualising the results using Python.

In a later blog, we will discover how Greenplum enables Advanced Text Analytics & Text Search by processing mass quantities of textual data using its GPText (Greenplum Text) solution.

Thanks for reading! Any comments or suggestions are welcome! Check out other Greenplum articles here.

Greenplum for Data Science Blog Series Part 1: Big Data Analysis with SQL and Python was originally published in Greenplum Data Clinics on Medium, where people are continuing the conversation by highlighting and responding to this story.

dbt or data-build-tool is a transformation tool in the ELT pipeline that lets teams transform data, following software engineering best practices. Through native support for connectivity to many data warehouses, dbt runs SQL queries against a database warehouse platform or query engine to materialise data as tables and views.

It is used to modularise and centralise your data-warehouse analytics code, enabling teams to collaborate on data models, version them, and test and document queries before safely deploying them to production, with monitoring and visibility.

In this blog post, we will see how Greenplum and dbt can operate together to handle reliable and high-performance Big Data workloads and data transformations with simplicity and elasticity thanks to the dbt-greenplum adapter built by the Greenplum community.

What is Greenplum Data Warehouse?

Greenplum is an advanced, fully featured, open-source MPP data warehouse based on PostgreSQL. It provides powerful and rapid analytics on petabyte-scale data volumes. Uniquely geared toward big data analytics.

Greenplum is powered by the world's most advanced cost-based query optimiser delivering high analytical query performance on large data volumes.

For more details, take a look at VMware Greenplum docs

Key Features of Greenplum

1. Cloud-agnostic for flexible deployment in the public cloud, private cloud, or on-premise
2. Analytics from business intelligence to artificial intelligence
3. Handle streaming data and enterprise ETL with ease
4. Maximise uptime and protect data integrity
5. Industry-leading performance
6. Scales to petabytes of data
7. Based on open-source projects like PostgreSQL offers an open ecosystem and integration with popular tools.
8. Massively parallel, highly concurrent architecture
9. Comes with libraries for advanced analytics to process geospatial, text, machine learning, graph, time series, and artificial intelligence
10. Parallel, high throughput data access and federated query processing across heterogeneous data sources from object storage like AWS S3, GCS, and Azure Blob Storage to external databases like PostgreSQL, SQL Server, and Oracle

dbt + Greenplum integration:

Using Greenplum and dbt together enables organisations to focus on data without worrying about performance tuning, resource allocation, testing, change management, documentation…

Since Greenplum is a PostgreSQL-based data warehouse, customers can use the dbt-postgres adapter developed and maintained by dbt Labs. Yet, users can now use the dbt-greenplum adapter: https://docs.getdbt.com/reference/warehouse-setups/greenplum-setup.

It unlocks the benefits of a mighty data warehouse. It is easy to set up, connecting to Greenplum has never been simpler, and it provides better defaults.

Getting started:

Step 1: Setup environment

Installing dbt-greenplum:

1. First, we must create a new Python environment and then install dbt-greenplum using pip3; it is the easiest way to install the adapter.

Installing dbt-greenplum will also install dbt-core and any other dependencies.

$ python3 -m venv dbt-env
$ source dbt-env/bin/activate
$ pip install dbt-greenplum

2. You can now check if everything works by running the dbt — version.

$ dbt --version
Core:
  - installed: 1.2.0
  - latest:    1.4.1 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - postgres:  1.2.0 - Update available!
  - greenplum: 1.2.0 - Up to date!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

3. Initiate the greenplum_demo project using the init command, then move into it:

$ dbt init greenplum_demo
$ cd greenplum_demo

You can now see the dbt's project structured like this:

Connect to Greenplum Configuration:

In this step, we configure the connection between dbt and Greenplum using a profile and a YAML file with all the connection details to the Greenplum data warehouse.

1. Create a file in the ~/.dbt/ directory named profiles.yml.

$ vi ~/.dbt/profile.yml

2. Copy the following and paste it into the new profiles.yml file. Make sure you update the values where noted.

greenplum_demo:
  target: dev
  outputs:
    dev:
      type: greenplum
      host: [hostname]
      user: [username]
      password: [password]
      port: [port]
      database: [database name]
      schema: [dbt schema]

Here's the profile file used for this blog:

3. Run the debug command from your project to confirm that you can successfully connect:

$ dbt debug

Step 2: Perform your first dbt run​

dbt project has some example models in it. We will check that we can run them to confirm everything is in order.

1. Enter the run command to build example models:

$ dbt run

It would be best if you had an output that looks like this:

Step 3: Build your first dbt-greenplum models​:

The dbt-greenplum adapter guides users to an improved experience with less effort to achieve better performance. Users can materialise their tables in the best possible way to optimise their performance by simply choosing the suitable storage model (column or row-oriented, append-optimised tables), distribution, partitioning and data compression strategy.

It's now time to build a dbt-greenplum model with advanced configurations!

1. Open your project in your favourite code editor and create our first dbt models.
2. Create two SQL files, classic_dbt_model.sql and greenplum_dbt_model.sql, in the models' directory to see the benefits of having a dbt-greenplum adapter.

For this blog, we use Greenplum generate_series and random functions to create a large six months CPU Usage table for 1000 devices with 1-minute intervals, ending now, which generates 265 million rows!

SELECT time, device_id, random()*100 as cpu_usage
   FROM generate_series(
     now() - INTERVAL '6 months',
      now(),
      INTERVAL '1 minute'
     ) as time,
   generate_series(1,1000) device_id;

• Create the first file named models/classic_dbt_model.sql, and paste the following:

{{ config(materialized='table') }}

with source_data as (

  SELECT time, device_id, random()*100 as cpu_usage
  FROM generate_series(
    now() - INTERVAL '6 months',
     now(),
     INTERVAL '1 minute'
    ) as time,
  generate_series(1,1000) device_id

)
select *
from source_data

This model will create a randomly distributed Greenplum table without advanced or powerful features.

• On the other hand, create the second file named models/greenplum_dbt_model.sql, and paste the following code:

{% set fields_string %}
    time timestamp NULL,
    device_id int null,
    cpu_usage double precision
{% endset %}

{% set raw_partition %}
   PARTITION BY RANGE (time)
   (
       START ('2022-06-01'::timestamp) INCLUSIVE
       END ('2024-01-01'::timestamp) EXCLUSIVE
       EVERY (INTERVAL '1 day'),
       DEFAULT PARTITION extra
   );
{% endset %}

{{
   config(
       materialized='table',
       distributed_by='device_id',
       appendonly='true',
       orientation='column',
       compresstype='ZLIB',
       compresslevel=3,
       fields_string=fields_string,
       raw_partition=raw_partition,
       default_partition_name='other_data'
   )
}}

with source_data as (

   SELECT time, device_id, random()*100 as cpu_usage
   FROM generate_series(
     now() - INTERVAL '6 months',
      now(),
      INTERVAL '1 minute'
     ) as time,
   generate_series(1,1000) device_id

)
select *
from source_data

This model will create a Greenplum column-oriented, append-optimised table distributed across segments by the device_id column key. It is partitioned on the time column with a daily interval, making it easier to manage and query data. Also, it uses the ZLIB algorithm with a compressed level = 3.

3. Let's now run dbt models using:

$ dbt run

4. To see the generated SQL by dbt, which ran on Greenplum, go to /target/run directory:

• classic_dbt_model.sql:

This model created a 265 million rows table randomly distributed with default Greenplum options.

• greenplum_dbt_model.sql:

While for the second table, our data is distributed based on the device_id column. The table is column-oriented, partitioned daily with a ZLIB compression.

5. When we return to the Greenplum console, we can select from these models:

Query Performance & Results Analysis:

As you can see, tables in Greenplum have powerful optimisation configurations that should improve query performance. To compare query performance between our newly created dbt tables, we will run analytical queries against these two large tables of 265 million records.

• SELECT MAX(cpu_usage) FROM dbt. ***_dbt_model where time >= ‘2023–01–01’::timestamp:

• SELECT COUNT(*) FROM dbt.****_dbt_model WHERE cpu_usage >= 99 AND (time between ‘2023–02–18’::timestamp and ‘2023–02–20’::timestamp):

As expected, analytical queries are much faster on the greenplum_dbt_model table; our query took only 0.163 seconds to query a 265 million rows table thanks to the advanced Greenplum capabilities from distribution, partitioning and column orientation features, which is ten times faster than the classic_dbt_model table that took more than a second.

Conclusion:

The winning combination of Greenplum and dbt dramatically simplifies your overall data architecture by providing a centralised and familiar SQL-based environment for collaborative data transformation.

Built for performance on top of open source technologies with widespread community support — dbt and Greenplum — ensures ongoing innovation and eliminates the risk of any vendor lock-in.

Thanks for reading! Any comments or suggestions are welcome! Check out other Greenplum articles here.

Accelerate Analytics with Greenplum Data-Warehouse & dbt was originally published in Greenplum Data Clinics on Medium, where people are continuing the conversation by highlighting and responding to this story.