Streaming at the Scale of Social Media

This is the boring server rendered SEO version of this article. If you're a robot, please keep reading. The interactive version of this is at /greenhouse.

Social media is a non-stop deluge of information. Just check out the live tab to disorient yourself with the hive mind of the information age so you can sidestep a listless & long lingering sense of FOMO. (oleander™ is not responsible for the content of social media) At the same time all this data needs to be processed all the time for moderation & big data AI social media analytics. (I considered adding a couple more buzzwords for good measure, but AI told me no, and I'm contractually obliged to obey to gain incremental favor with our future overlords) I also have a devious ulterior motive to stay relevant with the Gen Zees (not sure this is a thing) at real human life touch grass concerts, so I felt compelled to overengineer a monitoring system to track their colloquialisms to stay relevant at my tender age.

I've attached a Streamkap pipeline to an oleander destination to warehouse everything that's public on the Bluesky social network. Streamkap allows us to write large volumes of data without having to worry about backpressure and provides users with the ability to connect a variety of sinks, including oleander managed Iceberg. Since I'm warehousing into oleander, I only pay object storage costs to do this. The best estimate I have after working with this data is that it will be about ~1TB a year and compound at $27 annually at current deluge & pricing levels as of August in the year of the AI psychosis CEO.

Blueskythe firehose→Streamkapthe connector→oleanderthe warehouse

Landing in Storage

Streamkap has some great quality of life features out of the box that make our lives easier on the storage side, like autocompaction to make the parquet data files that ultimately land in oleander of a certain size so that I don't end up with the much dreaded small files problem that lakehouses often suffer from. While we offer scheduled (only British enunciation on this word please) Iceberg maintenance as part of oleander, we don't even need it for this since the raw data coming in is green by default and segmented automagically, thanks Streamkap!

Processing the Sentiment

Our secondary processing is bucket based and rolled up in the process described below. For each "bucket" we do some semantic processing all within SQL, using text splitting, a dictionary of positive and negative words, and lookback expressions. There are a couple of these queries we run to drive our dashboard, but let's look at one in detail. Below are some WITH clauses followed by the final aggregations together.

1. This section contains a dictionary of english language words and scores about how positive or negative each detection should weight our score.

lex AS (
  SELECT map(
    'amazing', 4,
    'great', 3,
    'good', 2,
    'happy', 3,
    'bad', -3,
    'sad', -2,
    'terrible', -4,
    'hate', -4
    -- yes, there are many more
  ) AS sentiment
)

2. This gets posts from the last 15 minutes from the main posts table that are in english and does some character stripping.

posts AS (
  SELECT
    (ingested_at DIV 900000) * 900000 AS bucket,
    -- 900000 is fifteen minutes
    split(
      regexp_replace(lower(text), '[^a-z0-9]+', ' '),
      ' '
    ) AS words
  FROM oleander.bluesky.bluesky_posts
  WHERE lower(langs) LIKE 'en%'
    AND operation LIKE 'c%'
)

3. This section contains some somewhat insane AI gen magic (I wasn't aware of this) with the miraculous spark function posexplode that extracts the position of words and builds a table with the word preceding it.

tokens AS (
  SELECT
    bucket,
    word,
    CASE WHEN pos > 0 THEN words[pos - 1] END AS previous_word
  FROM posts
  LATERAL VIEW posexplode(words) t AS pos, word
)

Let's look at a simple sentence:

It is not cool that I have traded my life and vitality for keystrokes.

This allows us to apply a negation to the positive sentiment and flip over to become negative. One obvious issue with this lookback is something like "not really good" as the negation precedes a dreaded adverb, but since Stephen King says "Adverbs pave the road to hell.", I don't really care about this and it's the exception rather than the rule.

pos   word       previous_word
---   --------   -------------
2     not        is
3     cool       not
4     that       cool

4. This is the final table response that governs what's actually committed to the warehouse which applies the negation, joins in the dictionary. We sum up the aggregation of all the posts for this period to distill a sentiment score for a particular time.

SELECT
  bucket,
  CASE
    WHEN previous_word IN ('not', 'no', 'never', 'cant', 'dont', 'isnt')
      THEN concat('not ', word)
    ELSE word
  END AS term,
  count(*) AS mentions,
  sum(
    CASE
      WHEN previous_word IN ('not', 'no', 'never', 'cant', 'dont', 'isnt')
        THEN -lex.sentiment[word]
      ELSE lex.sentiment[word]
    END
  ) AS sentiment_score

FROM tokens
CROSS JOIN lex
WHERE lex.sentiment[word] IS NOT NULL
GROUP BY 1, 2
ORDER BY bucket, sentiment_score DESC;

15 Minute Rollups

I cannot afford to do trend analysis for all of history every time one of you dear curious readers asks: "What was trending from 8:15 to 8:30 during No-sky July 26th in the year of the AI psychosis CEO" so I run a Spark task (I could have used one of our other engines of course, but like Spark well enough for this) every 15 minutes to merge the latest result in with all the previous results for our rollups. This results in low latency responses out of the warehouse since all the processing gets done ahead of time and appended at regular intervals. What I actually read out of the tables are derived results and not all the text processing, rollups and text tokenization that is needed to get the results.

For the Comments Section

If there was a comments section, which there is not because at oleander we are a theocracy that rules with a patinated iron scepter that ends with a series of interwoven faded pink petals coated with a dab of invisible poison, I would ask you to leave your thoughts below. And since there is no comment section, I would just ask you to try for yourself and see what you can build with Streamkap & oleander! For now, feel free to play around with the interactive playground.