The cryptocurrency market operates 24/7 at breakneck speed. For crypto analytics companies, staying ahead requires instantly processing and analyzing a torrent of real-time data to deliver valuable insights to users and make informed internal decisions. Monitoring market shifts, gauging community sentiment, and ensuring platform reliability demand immediate awareness, driven by data from social media, news feeds, audio streams, and internal logs.
Traditional batch processing systems simply can't keep up. The inherent delays mean insights are often outdated by the time they're available, rendering them ineffective in this volatile environment. Furthermore, the sheer diversity of data sources – from structured application logs to unstructured audio transcripts and high-velocity social media feeds – presents significant technical hurdles in ingestion, transformation, and analysis.
This is where RisingWave, a distributed SQL streaming database, comes in. RisingWave is designed specifically for real-time data processing. It allows companies to ingest data streams directly, perform complex transformations using familiar SQL, and maintain fresh, queryable results through materialized views (MVs). These MVs act as continuously updated tables, providing low-latency access to insights that power dashboards, analytics tools, trading algorithms, and even machine learning models like LLMs.
This blog post showcases how a crypto analytics company can leverage RisingWave to tackle these challenges head-on. We'll explore five key use cases, demonstrating how RisingWave ingests and transforms data from critical sources – real-time web content, audio streams (like podcasts and X/Twitter Spaces), X/Twitter engagement, application logs, Medium, You Tube and more – into actionable, real-time insights.
Use Case 1: Real-time web data processing and analysis
Business Need: To capture market-moving news and analysis the moment it's published, the company needs to ingest and structure content from various web sources in real time.
First, we define a table to ingest raw web data streamed via Amazon Kinesis. This table captures essential metadata and content for each piece of web data.
-- Ingest raw web data events from Kinesis
CREATE TABLE web_data_realtime_raw (
data_source VARCHAR,
content VARCHAR,
title VARCHAR,
authors VARCHAR,
quick_take VARCHAR,
publish_time VARCHAR,
url VARCHAR,
logo_url VARCHAR,
source_display_name VARCHAR,
dw_creation_date TIMESTAMPTZ AS PROCTIME () -- Marks processing time
) APPEND ONLY WITH (
connector = 'kinesis',
stream = 'web-data-stream',
aws.region = 'us-west-2',
scan.startup.mode = 'earliest',
-- aws.credentials.access_key_id = '<YOUR_ACCESS_KEY_ID>', -- Placeholder: Use secure credential management
aws.credentials.role.arn = 'arn:aws:iam::647756455373:role/RisingWaveReadOnlyRole' -- Example Role ARN
) FORMAT PLAIN ENCODE JSON;
Value Proposition: This table acts as the entry point for all incoming web content, making it immediately available within RisingWave.
Next, we create a materialized view to structure and enhance this raw data.
-- Structure and enhance raw web data into a queryable MV
CREATE MATERIALIZED VIEW web_realtime_data AS
SELECT
data_source || '_' || title AS doc_id, -- Create a unique document ID
authors AS author_id,
publish_time AS created_at,
0 AS engagement_count, -- Initialize engagement counter (can be joined later)
title || ' ' || content AS text, -- Combine title and content for NLP
url,
data_source,
source_display_name,
content AS web_content,
title AS web_title,
authors AS web_authors,
quick_take AS web_quick_take,
publish_time AS web_publish_time,
url AS web_url,
logo_url AS web_logo_url,
dw_creation_date
FROM web_data_realtime_raw;
Value Proposition: The web_realtime_data MV provides a clean, structured, real-time view of web content. It standardizes fields, creates a unique ID for easy reference, and combines text fields, making the data readily usable for downstream applications like dashboards showing latest news or NLP pipelines extracting key entities.
Use Case 2: Analyzing Real-Time Audio Streams (Podcasts, X/Twitter Spaces)
Business Need: Audio platforms like podcasts and X/Twitter Spaces are rich sources of crypto insights, alpha, and community sentiment. Processing this unstructured data in real-time is crucial but challenging.
This table ingests metadata and transcripts from various audio sources via Kinesis.
-- Ingest raw audio metadata and transcripts from Kinesis
CREATE TABLE audio_data_raw (
content_type VARCHAR, -- e.g., 'youtube', 'podcast', 'twitter_space'
transcript VARCHAR,
engagement_count INT,
-- Podcast specific fields...
podcast_audio_url VARCHAR,
podcast_transcript_url VARCHAR,
-- Twitter Space specific fields...
twitter_space_id VARCHAR,
twitter_space_title VARCHAR,
twitter_space_creator_user_name VARCHAR,
-- YouTube specific fields...
youtube_episode_id VARCHAR,
youtube_episode_title VARCHAR,
youtube_channel_name VARCHAR,
youtube_episode_view_count INT,
-- ... other fields for different audio types
-- Add fields from original example as needed, keeping it illustrative
youtube_episode_tags VARCHAR[]
) WITH (
connector = 'kinesis',
stream = 'audio-data-sourcing-stream',
aws.region = 'us-west-2',
scan.startup.mode = 'earliest',
-- aws.credentials.access_key_id = '<YOUR_ACCESS_KEY_ID>', -- Placeholder: Use secure credential management
aws.credentials.role.arn = 'arn:aws:iam::647756455373:role/RisingWaveReadOnlyRole' -- Example Role ARN
) FORMAT PLAIN ENCODE JSON;
Value Proposition: This centralizes diverse audio stream metadata, making transcripts and key details accessible for real-time processing.
As an example, we create an MV specifically focused on extracting and structuring YouTube data from the raw stream. Similar MVs could be created for Podcasts or Twitter Spaces.
-- Create an MV focused on structured YouTube data, deduplicating by episode ID
CREATE MATERIALIZED VIEW youtube_data_mv AS
SELECT DISTINCT ON (youtube_episode_id) -- Ensure one record per video
content_type AS data_source,
transcript AS text,
engagement_count,
content_type,
-- YouTube specific fields from audio_data_raw
youtube_audio_url,
youtube_transcript_url,
youtube_metadata_url,
youtube_channel_name,
youtube_channel_id,
youtube_channel_url,
youtube_playlist_id,
youtube_playlist_name,
youtube_playlist_title,
youtube_playlist_uploader,
youtube_playlist_uploader_id,
youtube_episode_playlist_index,
youtube_episode_speaker,
youtube_episode_id,
youtube_episode_uploader_name,
youtube_episode_uploader_id,
youtube_episode_uploader_url,
youtube_episode_created_at,
youtube_episode_title,
youtube_episode_full_title,
youtube_episode_url,
youtube_episode_content_snippet,
youtube_episode_audio_type,
youtube_episode_audio_length,
youtube_episode_view_count,
youtube_episode_tags,
youtube_hardcode_episode_title,
youtube_hardcode_episode_full_title,
youtube_hardcode_playlist_image_url,
youtube_hardcode_playlist_name,
youtube_hardcode_playlist_host_name
FROM audio_data_raw
WHERE content_type = 'youtube';
Value Proposition: The youtube_data_mv provides a clean, deduplicated, real-time dataset specifically for YouTube content. Analysts can query this MV directly to find trending topics in YouTube crypto discussions, analyze transcripts for sentiment, or track channel activity without sifting through raw, mixed-source data.
Use Case 3: Tracking Real-Time X/Twitter Engagement
Business Need: Understanding which crypto projects or topics are generating buzz requires tracking not just what is being said on platforms like X (formerly Twitter), but also the engagement (likes, retweets, replies) around those conversations in real time.
This table ingests detailed tweet data, including engagement metrics, from a Kinesis stream.
-- Ingest raw tweet data including engagement metrics from Kinesis
CREATE TABLE twitter_tweet_engagement_data_raw (
text VARCHAR,
id VARCHAR, -- Tweet ID
edit_history_tweet_ids VARCHAR[],
lang VARCHAR,
attachments JSONB,
author_id VARCHAR,
conversation_id VARCHAR,
created_at VARCHAR,
crawled_at VARCHAR, -- When the data was collected
entities JSONB,
in_reply_to_user_id VARCHAR,
public_metrics JSONB, -- Contains like_count, retweet_count, etc.
referenced_tweets JSONB,
reply_settings VARCHAR,
source VARCHAR,
author_detail JSONB,
media_detail JSONB,
referenced_detail JSONB,
is_original_tweet BOOLEAN,
kaito_tweet_type VARCHAR, -- Custom classification
crypto_ticker VARCHAR, -- Identified crypto ticker
is_thread BOOLEAN,
tweet_count_within_thread INT,
tweet_level_within_thread INT
) APPEND ONLY WITH (
connector = 'kinesis',
stream = 'twitter-engagement-stream',
aws.region = 'us-west-2',
scan.startup.mode = 'earliest',
-- aws.credentials.access_key_id = '<YOUR_ACCESS_KEY_ID>', -- Placeholder: Use secure credential management
aws.credentials.role.arn = 'arn:aws:iam::647756455373:role/RisingWaveReadOnlyRole' -- Example Role ARN
) FORMAT PLAIN ENCODE JSON;
Value Proposition: This table captures the rich context and metrics associated with each tweet as it happens.
To focus on the most impactful version of a tweet (since engagement metrics can update), we create an MV that deduplicates tweets based on engagement, keeping the record with the highest like count.
-- Create an MV showing the latest state of tweet engagement, deduplicated by tweet ID
CREATE MATERIALIZED VIEW latest_tweet_engagement AS
SELECT *
EXCEPT (rank, like_count) -- Exclude helper columns
FROM (
SELECT *,
row_number() OVER (PARTITION BY id ORDER BY like_count DESC, crawled_at DESC) AS rank -- Rank by likes, then freshness
FROM (
SELECT *,
CAST((public_metrics ->> 'like_count') AS BIGINT) AS like_count -- Extract like count
FROM twitter_tweet_engagement_data_raw
)
)
WHERE rank = 1; -- Keep only the top-ranked record per tweet ID
Value Proposition: The latest_tweet_engagement MV provides a continuously updated view of tweets, ensuring that analyses are based on the most recent engagement figures. This allows the company to quickly identify highly resonant content and track trending discussions without noise from intermediate updates.
Use Case 4: Monitoring Real-Time Application Logs
Business Need: For exchanges, wallets, or dApps, platform stability is paramount. Real-time monitoring of application logs is essential for quickly identifying and resolving errors that could impact users or operations.
This table ingests log entries from application servers via Kinesis.
-- Ingest raw application log data from Kinesis
CREATE TABLE web_logs (
id VARCHAR, -- Log entry ID
timestamp TIMESTAMP, -- Event timestamp
message VARCHAR, -- Log message content
dw_creation_date TIMESTAMPTZ AS PROCTIME () -- Processing time
) APPEND ONLY WITH (
connector = 'kinesis',
stream = 'web-logs-stream',
aws.region = 'us-west-2',
scan.startup.mode = 'earliest',
-- aws.credentials.access_key_id = '<YOUR_ACCESS_KEY_ID>', -- Placeholder: Use secure credential management
aws.credentials.role.arn = 'arn:aws:iam::647756455373:role/RisingWaveReadOnlyRole' -- Example Role ARN
) FORMAT PLAIN ENCODE JSON;
Value Proposition: Makes application logs immediately available for stream processing within RisingWave.
To get a high-level view of application health, we create an MV that aggregates error logs in real time.
-- Create an MV summarizing error occurrences from web logs
CREATE MATERIALIZED VIEW error_logs_summary AS
SELECT
message, -- Group by the specific error message
COUNT(*) AS error_count,
MAX(timestamp) AS last_occurrence
FROM web_logs
WHERE message LIKE '%ERROR%' -- Filter for error messages
GROUP BY message;
Value Proposition: The error_logs_summary MV provides an instant dashboard of application errors. Operations teams can see which errors are occurring, how frequently (error_count), and when the last one happened (last_occurrence), enabling rapid response and improved platform reliability.
Use Case 5: Deriving Real-Time X/Twitter Sentiment Insights
Business Need: Directly linking detected sentiment shifts (e.g., sudden positive or negative buzz) on X/Twitter to specific crypto assets can provide valuable signals for trading strategies or market understanding.
This table ingests pre-processed sentiment events, likely generated by an upstream ML model analyzing tweets, identifying a ticker and a sentiment event type.
-- Ingest pre-processed sentiment events related to crypto tickers from Kinesis
CREATE TABLE twitter_sentiment_events_source ( -- Renamed from _test
id VARCHAR PRIMARY KEY, -- Event ID
ticker VARCHAR, -- Crypto asset ticker (e.g., BTC, ETH)
datetime VARCHAR, -- Timestamp of the event
event VARCHAR -- Type of sentiment event (e.g., 'POSITIVE_SPIKE', 'NEGATIVE_TREND')
) WITH (
connector = 'kinesis',
stream = 'sentiment-events-stream',
aws.region = 'us-west-2',
scan.startup.mode = 'latest', -- Often interested in most recent sentiment signals
-- aws.credentials.access_key_id = '<YOUR_ACCESS_KEY_ID>', -- Placeholder: Use secure credential management
aws.credentials.role.arn = 'arn:aws:iam::647756455373:role/RisingWaveReadOnlyRole' -- Example Role ARN
) FORMAT PLAIN ENCODE JSON;
Value Proposition: Provides a stream of structured sentiment signals ready for aggregation.
To track overall sentiment trends per crypto asset, we create an MV that aggregates these events.
-- Create an MV aggregating sentiment events by ticker and event type
CREATE MATERIALIZED VIEW sentiment_trends AS
SELECT
ticker,
event,
COUNT(*) AS event_count, -- How many times this event type occurred for the ticker
MAX(datetime) AS last_event_time -- When the latest event of this type occurred
FROM twitter_sentiment_events_source
GROUP BY ticker, event;
Value Proposition: The sentiment_trends MV transforms raw sentiment events into an actionable summary. Analysts or trading algorithms can query this view to see the real-time count and recency of specific sentiment patterns (like 'POSITIVE_SPIKE') for each crypto asset, directly informing market analysis and decision-making.
Conclusion
As demonstrated through these five use cases, RisingWave empowers crypto analytics companies to effectively harness diverse, high-velocity data streams. By leveraging its SQL interface for real-time ingestion, transformation, and analysis via Materialized Views, companies can:
Simplify Complex Pipelines: Consolidate processing logic within RisingWave, reducing the need for separate batch and streaming systems.
Gain Real-Time Insights: Analyze web content, audio transcripts, social engagement, logs, and sentiment events as they happen.
Enhance Agility: React swiftly to market trends, emerging narratives, and operational issues.
Improve Decision-Making: Equip analysts, traders, and operations teams with up-to-the-second data.
In the fast-paced and data-intensive crypto landscape, the ability to process and act on real-time information is not just an advantage – it's a necessity. RisingWave provides the tools to build robust, efficient, and responsive analytics platforms, giving crypto companies the competitive edge needed to thrive.
Ready to learn more?
Check out the RisingWave documentation.
Explore more use cases.
Try RisingWave Cloud with a free trial.
