<jupyter_start><jupyter_text>**Note:** Materialize is Postgres wire-compatible (🐘), and so can connect to downstream tools like Hex using the existing Postgres wiring, instead of requiring a dedicated source.<jupyter_code>import datetime
import pytz

date_now = datetime.datetime.now()
timezone = pytz.timezone("Europe/Berlin")
d_aware = timezone.localize(date_now)
d_aware.tzinfo

date_arrival = datetime.datetime(2022, 3, 22, 15, 21, 00)
timezone = pytz.timezone("US/Central")
da_aware = timezone.localize(date_arrival)
da_aware.tzinfo

diff = d_aware-da_aware
diff_trunc = str(diff).split(".")[0]<jupyter_output><empty_output><jupyter_text>I should have landed in Austin {{diff_trunc}} hours ago. But the universe had bigger plans, so I'm stuck at home trying to ignore the FOMO, while 99.9% of my Twitter timeline (and probably yours, if you're reading this!) is out and about at [**Data Council**](https://www.datacouncil.ai/).To try and live vicarioulsy through everyone there, I sat down and built a streaming demo using ✨[**Redpanda**](https://redpanda.com/), [**Materialize**](https://materialize.com/) and [**Hex**](https://hex.tech/)✨ to keep up with the buzz around the conference as folks post about it on Twitter. This means that, if there is no buzz and no one tweets, the demo will be an absolute failure (@pedram_navid, I'm counting on you).If you want a sneak peek into what's running behind the scenes, check out [this GitHub repo](https://github.com/morsapaes/hex-data-council). Are you also not there?If you're also **not** at the conference, you can at least make this demo do something cool! Enter your **Twitter handle** below and **click** the red button.<jupyter_code>twitter_handle = _hex_json.loads("\"@random\"")
not_there = _hex_json.loads("false")
twitter_handle_clean=twitter_handle.replace('@','')
# import jinja2
# raw_query = """
#     {% if not_there %}
#     
#     INSERT INTO users_not_there
#     VALUES ({{twitter_handle_clean}});
#     
#     {% endif %}
# """
# sql_query = jinja2.Template(raw_query).render(vars())<jupyter_output><empty_output><jupyter_text>Nothing to see yet, but we'll make good use of this in a bit! Staying in the (k)now Okay, so we have data streaming in from Twitter in real-time: what now? Let's start by keeping an updated list of tweets being posted about the conference as they slip off fingers far, far away.**What is picked up?**This is not an exact science, so for your tweets to show up you'll need to either mention `@DataCouncilAI` or include the words `data council`. Note: only _real_ tweets, quoted retweets and replies are picked up!Just **try tweeting** something! It should appear 👇 in a heartbeat (well, you'll need to re-run the notebook 1):<jupyter_code># import jinja2
# raw_query = """
#     SELECT tweet AS "Tweet",
#            tweet_type AS "Tweet type",
#            username AS "Username",
#            created_at AS "Created at"
#     FROM twitter_tweets_enriched
#     WHERE username NOT IN (SELECT username FROM users_not_there)
#     --Hashtag classic data processing :sweatsmile:
#     --It'd be saner to filter out unrelated tweets and replies based on id and in_reply_to
#     AND username NOT IN ('Artha__Global','SaudiNewsFR','terronk','GeorgeCipparro2','3Sentinel4','zeelurker1','BeholdRetreats','zachcoelius','Bkearns123','justinbvert','overtimeLaw')
#     ORDER BY created_at DESC;
# """
# sql_query = jinja2.Template(raw_query).render(vars())<jupyter_output><empty_output><jupyter_text>> 1 _Re-running a notebook doesn't incurr any significant "cost" in Materialize; we're just reading data out of the self-updated materialized views. And, although the results are updated with sub-second latency in the database, it is currently not possible to schedule runs anywhere near that frequency. We're in touch with the Hex team to change that!_ 🧑‍🚀✨Remember the red button up there, and how we're not at Data Council? This allows us to dynamically route tweets from folks that mark themselves as "not there" to create a commiseration feed of sorts (thanks for the [inspiration](https://twitter.com/jillzzy/status/1506021651288272899?s=20&t=x-edWkssrAP2u71XWR4HkA), @jillzzy!).<jupyter_code># import jinja2
# raw_query = """
#     SELECT * 
#     FROM twitter_tweets_enriched
#     WHERE username IN (SELECT username FROM users_not_there)
#     ORDER BY created_at DESC;
# """
# sql_query = jinja2.Template(raw_query).render(vars())<jupyter_output><empty_output><jupyter_text>Keeping scoreHow do we know who's ahead of the Twitter game at the conference? Or how much Twitter activity there is in the first place? Materialize is pretty good at keeping track of events over time and maintaining query results **incrementally updated** in (you've guessed it) [materialized views](https://materialize.com/why-use-a-materialized-view/). This means that it can handle heavy-duty computations, like running aggregations, with minimal effort as new events stream in, ditching the need for scheduled refreshes or full rescans of the source data each time.Let's get an idea of the bigger picture as time moves forward:<jupyter_code># import jinja2
# raw_query = """
#     SELECT SUM(total_tweets) AS cnt_tweets
#     FROM agg_tweets;
# """
# sql_query = jinja2.Template(raw_query).render(vars())
# import jinja2
# raw_query = """
#     SELECT COUNT(username) AS cnt_users
#     FROM agg_tweets;
# """
# sql_query = jinja2.Template(raw_query).render(vars())
cnt_tweets=total_tweets.iat[0,0]
cnt_users=total_users.iat[0,0]
# import jinja2
# raw_query = """
#     SELECT COUNT(DISTINCT username) 
#     FROM users_not_there;
# """
# sql_query = jinja2.Template(raw_query).render(vars())
cnt_users_nt=total_users_nt.iat[0,0]
cnt_tweets
cnt_users
cnt_users_nt
# import jinja2
# raw_query = """
#     SELECT username, 
#            total_tweets 
#     FROM agg_tweets 
#     ORDER BY total_tweets DESC;
# """
# sql_query = jinja2.Template(raw_query).render(vars())
# import jinja2
# raw_query = """
#     SELECT time_bucket AS "Time bucket",
#            total_tweets AS "Total tweets" 
#     FROM tweets_hourly;
# """
# sql_query = jinja2.Template(raw_query).render(vars())
import altair
chart_query_result_6 = altair.Chart.from_json("""
{
    "width": 500,
    "height": 500,
    "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
    "layer": [
        {
            "data": {
                "name": "layer00"
            },
            "mark": {
                "type": "bar",
                "clip": true,
                "tooltip": true
            },
            "encoding": {
                "x": {
                    "field": "Time bucket",
                    "type": "temporal"
                },
                "y": {
                    "field": "Total tweets",
                    "type": "quantitative"
                },
                "color": {
                    "value": "#C84654"
                }
            }
        }
    ],
    "resolve": {
        "scale": {}
    },
    "datasets": {
        "layer00": [
            {
                "name": "dummy",
                "value": 0
            }
        ]
    },
    "config": {
        "legend": {
            "disable": false
        }
    }
}
""")
chart_query_result_6.datasets.layer00 = query_result_6.to_json(orient='records')
chart_query_result_6.display(actions=False)<jupyter_output><empty_output><jupyter_text>Missing outTo finish off this self-flagellation experiment, let's turn to what happens _beyond_ the conference program itself. What are folks planning to do after @JLDLaughlin's talk on [dbt+Materialize](https://www.datacouncil.ai/talks/materializedbt-streaming-for-the-modern-data-stack?hsLang=en)? What vendors are using BBQ as bait?It's admitedly naive to do text processing in SQL, but we can roll with a [regex](https://www.tiktok.com/@nimay.ndolo/video/7042702152774552838) pattern `beer|drink|food|bbq|bats` to capture some of the gatherings we're missing out on. Same as before, whenever a new tweet comes around and it matches the given pattern, it'll end up 👇:<jupyter_code># import jinja2
# raw_query = """
#     SELECT *
#     FROM twitter_tweets_enriched
#     WHERE regexp_match(tweet,'beer|drink|food|bbq|bats','i') IS NOT NULL
#     ORDER BY created_at DESC;
# """
# sql_query = jinja2.Template(raw_query).render(vars())<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11375598086124401, 'fraction_numerical': 0.013995215311004785, 'mean_word_length': 3.6372712146422628, 'pattern_counts': {'":': 28, '<': 25, '<?xml version=': 0, '>': 26, 'https://': 10, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '167491', 'license': 'Apache-2.0', 'n_tokens_mistral': 2769, 'n_tokens_neox': 2568, 'n_words': 810, 'path': 'hex-data-council.ipynb', 'repo_name': 'morsapaes/hex-data-council', 'size': '8360'}