New

Extend Real-Time Ingestion with Kafka Connect

Notebook


SingleStore Notebooks

Extend Real-Time Ingestion with Kafka Connect

Note

You need a Standard or Premium cluster, a database user with the SUPER permission (to switch on connector pipelines, an experimental feature), and an AWS account that can create an Amazon Kinesis data stream. The stream costs a few cents an hour and is deleted in the clean-up step.

The goal of this notebook is to demonstrate how Kafka Connect pipelines let SingleStore read directly from streaming sources such as Amazon Kinesis, with no Kafka cluster, Connect workers or other intermediate infrastructure in between.

The story: AdVenture Inc. (yes, the ad startup from our AI and search notebook) just bought PixelPop Games, maker of the hit mobile game Pigeon Panic. Every ad shown, tapped or turned into an in-game purchase flows into Amazon Kinesis. Morgan, the CMO, wants those events next to the ad campaigns by Friday. The platform team wants six weeks to stand up Kafka.

ChapterWhat happensYou use
1The acquisition: game events start flowing into KinesisAmazon Kinesis, the Digital Marketing dataset
2No middlemen: Kinesis straight into SingleStoreNEW Kafka Connect pipeline (LOAD DATA KAFKACONNECT)
3The live scoreboard: which ads make moneyBackground pipeline + SQL on fresh data
4Oops, replay itNEW PIPELINES_SOURCE_OFFSETS, NEW ALTER PIPELINE ... SET OFFSETS
5Same trick, other cloudsGoogle Cloud Pub/Sub connector pipeline

Prerequisites

1. Generate the Digital Marketing dataset. The dataset can be generated using the website here: digital-marketing.labs.singlestore.com

  1. Make sure your cluster is running.

  2. Open the website and fill in the connection form: your cluster host with https:// in front (find it under Connect in the portal), username admin, your cluster password, and database name martech. Click Connect.

  3. Click Setup Database and keep the Simulator switch on.

  4. Under Locations, tick a few cities. Each city adds ad campaigns.

  5. Wait until Ad Campaigns shows a number above 0. Ad notifications start flowing a little later.

If you skip this, the notebook makes up advertisers and ad events from the dataset's vendor list instead.

2. Get AWS access keys. Create an IAM user (or use an existing one) with this policy, and create an access key for it:

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": ["kinesis:CreateStream", "kinesis:DeleteStream", "kinesis:DescribeStream",
               "kinesis:DescribeStreamSummary", "kinesis:ListShards", "kinesis:PutRecords",
               "kinesis:GetRecords", "kinesis:GetShardIterator"],
    "Resource": "arn:aws:kinesis:*:*:stream/pixelpop-*"
  }]
}

The policy only covers streams whose names start with pixelpop-. Use a dedicated user: the pipeline stores the keys in its configuration.

Chapter 0: Setup

Start with the database drop-down at the top of the notebook empty. The setup cell creates a database called adventure_streams.

In [1]:

1%pip install singlestoredb pandas boto3 --quiet

In [2]:

1import base64, getpass, json, os, random, re, time, uuid2import pandas as pd3import singlestoredb as s24
5pd.set_option('display.max_colwidth', 120)6random.seed(7)7
8DB_NAME = 'adventure_streams'9DEMO_DB = 'martech'          # created by the Digital Marketing demo website10PIPELINE = 'pixelpop_events'  # also the name of the table the pipeline creates11
12USE_DB = None13
14def connect():15    global conn16    conn = s2.connect(database=USE_DB) if USE_DB else s2.connect()17    conn.autocommit(True)18    if USE_DB:19        with conn.cursor() as cur:20            cur.execute(f'USE {USE_DB}')21
22def _query(query, params=None):23    with conn.cursor() as cur:24        cur.execute(query, params)25        if cur.description:26            cols = [d[0] for d in cur.description]27            return pd.DataFrame(cur.fetchall(), columns=cols)28    return None29
30def run_sql(query, params=None):31    # Run a query and return a DataFrame (or None). Reconnects once if an idle connection was dropped.32    try:33        return _query(query, params)34    except Exception as e:35        if getattr(e, 'errno', None) in (2006, 2013) or 'Lost connection' in str(e) or 'gone away' in str(e):36            print('(Connection was dropped, reconnecting...)')37            connect()38            return _query(query, params)39        raise40
41def try_sql(query, params=None, label='This step'):42    # Like run_sql(), but explains and continues if the cluster doesn't support something.43    try:44        return run_sql(query, params)45    except Exception as e:46        print(f'{label} was skipped: {e}')47        return None48
49connect()50run_sql(f'CREATE DATABASE IF NOT EXISTS {DB_NAME}')51USE_DB = DB_NAME52connect()53print('Connected to database:', run_sql('SELECT DATABASE() AS db').iloc[0, 0])54print('SingleStore version:  ', run_sql('SELECT @@memsql_version AS v').iloc[0, 0])

Action Required

Select adventure_streams from the database drop-down at the top of this notebook (refresh the list if it isn't there yet).

NEW  Kafka Connect pipelines (experimental)
Connector pipelines run Kafka Connect source connectors inside SingleStore. They're switched on with an experimental-features setting. The next cell adds it to whatever is already set, instead of overwriting it. This needs the SUPER permission and applies to the whole cluster.

In [3]:

1FLAG = 'connector_pipelines_enabled=true'2current = run_sql("SHOW VARIABLES LIKE 'experimental_features_config'")3value = current.iloc[0, 1] if current is not None and len(current) else ''4if FLAG in (value or ''):5    print('Connector pipelines are already enabled.')6else:7    new_value = f'{value},{FLAG}' if value else FLAG8    run_sql('SET GLOBAL experimental_features_config = %s', (new_value,))9    print('Connector pipelines enabled:', run_sql("SHOW VARIABLES LIKE 'experimental_features_config'").iloc[0, 1])

Chapter 1: The acquisition

#war-room Mon 9:00 AM  Morgan (CMO): We bought PixelPop Games! 5 million players see our ads inside Pigeon Panic. Their events live in something called Kinesis. I want to know which ads make money. By Friday.

#war-room Mon 9:04 AM  Priya (Platform Lead): Kinesis to our database? We'd need a Kafka cluster, Kafka Connect workers and a schema registry. Six weeks, minimum.

First, the ad campaigns from the Digital Marketing demo, plus recent ad notifications to replay as in-game ad impressions:

In [4]:

1has_demo = run_sql('''2    SELECT COUNT(*) AS n FROM information_schema.TABLES3    WHERE TABLE_SCHEMA = %s AND TABLE_NAME IN ('offers', 'notifications', 'cities')4''', (DEMO_DB,)).iloc[0, 0] == 35
6if (has_demo7        and run_sql(f'SELECT COUNT(*) FROM {DEMO_DB}.offers').iloc[0, 0] > 08        and run_sql(f'SELECT COUNT(*) FROM {DEMO_DB}.notifications').iloc[0, 0] > 0):9    offers = run_sql(f'SELECT offer_id, customer AS advertiser, notification_content AS ad_text FROM {DEMO_DB}.offers')10    shown = run_sql(f'''11        SELECT n.subscriber_id AS player_id, n.offer_id, n.cost_cents, c.city_name AS city12        FROM {DEMO_DB}.notifications n JOIN {DEMO_DB}.cities c ON c.city_id = n.city_id13        ORDER BY n.ts DESC LIMIT 2000014    ''')15    print(f'Using the demo database: {len(offers)} ad campaigns, {len(shown)} recent ad notifications.')16else:17    print('No demo data found, so making up campaigns from the dataset vendor list.')18    vendors = pd.read_json('https://raw.githubusercontent.com/singlestore-labs/demo-realtime-digital-marketing/main/data/vendors.json')19    vendors = vendors.drop_duplicates('vendor').head(300)20    offers = pd.DataFrame({21        'offer_id': range(1, len(vendors) + 1),22        'advertiser': vendors['vendor'].values,23        'ad_text': [f'{random.randint(10, 50)}% off at {v}' for v in vendors['vendor']],24    })25    shown = pd.DataFrame({26        'player_id': [random.randint(1, 100000) for _ in range(20000)],27        'offer_id': [random.choice(offers['offer_id'].tolist()) for _ in range(20000)],28        'cost_cents': [random.randint(2, 15) for _ in range(20000)],29        'city': 'New York',30    })31
32# Keep a copy of the campaigns in our own database for the scoreboard joins33run_sql('DROP TABLE IF EXISTS campaigns')34run_sql('CREATE TABLE campaigns (offer_id BIGINT PRIMARY KEY, advertiser VARCHAR(100), ad_text TEXT)')35with conn.cursor() as cur:36    cur.executemany('INSERT INTO campaigns VALUES (%s, %s, %s)', offers.values.tolist())37offers.head()

Now connect to AWS. Your keys are typed in, not saved in the notebook:

In [5]:

1import boto32
3AWS_REGION = os.environ.get('AWS_REGION') or input('AWS region [us-east-1]: ').strip() or 'us-east-1'4KINESIS_REGION = AWS_REGION.upper().replace('-', '_')5AWS_KEY_ID = os.environ.get('AWS_ACCESS_KEY_ID') or getpass.getpass('AWS access key ID: ')6AWS_SECRET = os.environ.get('AWS_SECRET_ACCESS_KEY') or getpass.getpass('AWS secret access key: ')7
8kinesis = boto3.client('kinesis', region_name=AWS_REGION,9                       aws_access_key_id=AWS_KEY_ID, aws_secret_access_key=AWS_SECRET)10
11STREAM = f'pixelpop-events-{uuid.uuid4().hex[:6]}'12SHARDS = 213kinesis.create_stream(StreamName=STREAM, ShardCount=SHARDS)14print(f'Creating Kinesis stream {STREAM} with {SHARDS} shards...')15kinesis.get_waiter('stream_exists').wait(StreamName=STREAM)16print('Stream is active.')

Each ad notification becomes an in-game impression. About 1 in 8 players tap the ad (click), and about a third of those buy something (purchase). The events are JSON, just as a mobile game would send them:

In [6]:

1campaign_ids = offers['offer_id'].tolist()2
3def game_events(n_impressions, from_real=True):4    events = []5    sample = shown.sample(n_impressions, replace=len(shown) < n_impressions) if from_real else None6    for i in range(n_impressions):7        row = sample.iloc[i] if from_real else None8        base = {9            'game': 'Pigeon Panic',10            'player_id': int(row['player_id']) if from_real else random.randint(1, 100000),11            'offer_id': int(row['offer_id']) if from_real else random.choice(campaign_ids),12            'city': str(row['city']) if from_real else 'New York',13            'level': random.randint(1, 60),14        }15        cost = int(row['cost_cents']) if from_real else random.randint(2, 15)16        now = time.time() * 100017        events.append({**base, 'event_id': uuid.uuid4().hex, 'event': 'impression', 'cost_cents': cost, 'ts_ms': now})18        if random.random() < 0.12:19            events.append({**base, 'event_id': uuid.uuid4().hex, 'event': 'click', 'ts_ms': now + 800})20            if random.random() < 0.33:21                events.append({**base, 'event_id': uuid.uuid4().hex, 'event': 'purchase',22                               'amount_cents': random.choice([99, 199, 499, 999, 1999]), 'ts_ms': now + 5000})23    return events24
25def send(events):26    for i in range(0, len(events), 500):     # PutRecords takes up to 500 records per call27        batch = events[i:i + 500]28        kinesis.put_records(StreamName=STREAM, Records=[29            {'Data': json.dumps(e).encode(), 'PartitionKey': str(e['player_id'])} for e in batch])30    return len(events)31
32print(f'Sent {send(game_events(3000))} events to Kinesis.')

Chapter 2: No middlemen

#war-room Mon 10:15 AM  Morgan (CMO): Priya says six weeks. You have until lunch.

NEW  Kafka Connect pipeline for Amazon Kinesis
A Kafka Connect source connector runs inside the pipeline, so SingleStore reads Kinesis directly. No Kafka brokers, no Connect workers. CREATE INFERRED PIPELINE also creates the destination table for you.

In [7]:

1config = {2    'connector.class': 'com.github.jcustenborder.kafka.connect.kinesis.KinesisSourceConnector',3    'kafka.topic': 'pixelpop',          # a label only: no Kafka topic is needed4    'kinesis.stream': STREAM,5    'kinesis.region': KINESIS_REGION,6    'kinesis.position': 'TRIM_HORIZON', # start from the oldest record in the stream7    'tasks.max': SHARDS,                # one task per shard8}9credentials = {10    'aws.access.key.id': AWS_KEY_ID,11    'aws.secret.key.id': AWS_SECRET,12}13
14try_sql(f'DROP PIPELINE IF EXISTS {PIPELINE}', label='Removing an old pipeline')15run_sql(f'DROP TABLE IF EXISTS {PIPELINE}')16run_sql(f'''17    CREATE INFERRED PIPELINE {PIPELINE}18    AS LOAD DATA KAFKACONNECT 'kafka-connector'19    CONFIG %s20    CREDENTIALS %s21    FORMAT AVRO22''', (json.dumps(config), json.dumps(credentials)))23print(f'Pipeline {PIPELINE} created. That was the whole integration.')

Evaluator's corner: what you didn't have to build. Compare this with the usual route from Kinesis to a database:

Usual setupWith a Kafka Connect pipeline
Kafka cluster (brokers, storage, upgrades)Not needed
Kafka Connect worker clusterNot needed: the connector runs in the pipeline
A sink connector and its configurationNot needed: SingleStore is the destination
Monitoring for each hopOne place: SHOW PIPELINES and information_schema

Run the pipeline once in the foreground. The connector needs a few seconds to start, so the cell checks a few times:

In [8]:

1for attempt in range(6):2    try_sql(f'START PIPELINE {PIPELINE} FOREGROUND', label='Foreground run')3    loaded = run_sql(f'SELECT COUNT(*) FROM {PIPELINE}').iloc[0, 0]4    if loaded:5        break6    time.sleep(5)7print(f'{loaded} events loaded from Kinesis.')8run_sql(f'SHOW CREATE TABLE {PIPELINE}').iloc[0, 1]

Each row holds one Kinesis record in the record column. Peek at one:

In [9]:

1sample = json.loads(run_sql(f'SELECT record FROM {PIPELINE} LIMIT 1').iloc[0, 0])2print(json.dumps(sample, indent=2)[:1200])

The game's JSON sits inside the Kinesis record's data field. The next cell finds it, works out how it's encoded, and creates a game_events view with proper columns, so every later query reads like a normal table:

In [10]:

1data_key = next((k for k in sample if k.lower() == 'data'), None)2if data_key is None:3    raise KeyError(f'No data field found in the Kinesis record. Fields: {list(sample)}')4value = sample[data_key]5
6if isinstance(value, dict):7    payload = f"JSON_EXTRACT_JSON(record, '{data_key}')"8else:9    try:10        json.loads(value)11        payload = f"JSON_EXTRACT_STRING(record, '{data_key}')"12    except (ValueError, TypeError):13        payload = f"FROM_BASE64(JSON_EXTRACT_STRING(record, '{data_key}'))"14print('Game event JSON is read with:', payload)15
16run_sql('DROP VIEW IF EXISTS game_events')17run_sql(f'''18    CREATE VIEW game_events AS19    SELECT JSON_EXTRACT_STRING({payload}, 'event_id')                    AS event_id,20           JSON_EXTRACT_STRING({payload}, 'event')                       AS event,21           FROM_UNIXTIME(JSON_EXTRACT_DOUBLE({payload}, 'ts_ms') / 1000) AS ts,22           JSON_EXTRACT_BIGINT({payload}, 'player_id')                   AS player_id,23           JSON_EXTRACT_BIGINT({payload}, 'offer_id')                    AS offer_id,24           JSON_EXTRACT_STRING({payload}, 'city')                        AS city,25           JSON_EXTRACT_BIGINT({payload}, 'level')                       AS level,26           JSON_EXTRACT_BIGINT({payload}, 'cost_cents')                  AS cost_cents,27           JSON_EXTRACT_BIGINT({payload}, 'amount_cents')                AS amount_cents28    FROM {PIPELINE}29''')30run_sql('SELECT event, COUNT(*) AS events FROM game_events GROUP BY event ORDER BY events DESC')

Chapter 3: The live scoreboard

#war-room Mon 11:58 AM  Morgan (CMO): Lunch is in two minutes. Which ads actually make money? And I want to watch it update LIVE.

Start the pipeline in the background so it keeps pulling from Kinesis, then keep the game busy for a minute. Watch the totals climb as events land:

In [11]:

1run_sql(f'START PIPELINE {PIPELINE}')2
3for tick in range(6):4    send(game_events(500))5    time.sleep(10)6    counts = run_sql('''7        SELECT IFNULL(SUM(event = 'impression'), 0) AS impressions, IFNULL(SUM(event = 'click'), 0) AS clicks,8               IFNULL(SUM(event = 'purchase'), 0) AS purchases9        FROM game_events10    ''').iloc[0]11    print(f'+{(tick + 1) * 10}s  impressions {int(counts.impressions):>6}  '12          f'clicks {int(counts.clicks):>5}  purchases {int(counts.purchases):>4}')

Now the question Morgan asked: return on ad spend (ROAS) per advertiser, computed from Kinesis events joined with the ad campaigns, in one query on live data:

In [12]:

1run_sql('''2    SELECT c.advertiser,3           ANY_VALUE(c.ad_text)                                   AS ad,4           SUM(e.event = 'impression')                            AS impressions,5           SUM(e.event = 'click')                                 AS clicks,6           ROUND(100 * SUM(e.event = 'click') / NULLIF(SUM(e.event = 'impression'), 0), 1) AS ctr_pct,7           SUM(IFNULL(e.cost_cents, 0)) / 100                     AS spend_usd,8           SUM(IFNULL(e.amount_cents, 0)) / 100                   AS revenue_usd,9           ROUND(SUM(IFNULL(e.amount_cents, 0)) / NULLIF(SUM(IFNULL(e.cost_cents, 0)), 0), 1) AS roas10    FROM game_events e JOIN campaigns c ON c.offer_id = e.offer_id11    GROUP BY c.advertiser12    HAVING impressions >= 513    ORDER BY roas DESC14    LIMIT 1015''')

And where players are when they buy, by city and game level:

In [13]:

1run_sql('''2    SELECT city,3           CASE WHEN level <= 20 THEN 'levels 1-20' WHEN level <= 40 THEN 'levels 21-40' ELSE 'levels 41-60' END AS stage,4           COUNT(*) AS purchases,5           SUM(amount_cents) / 100 AS revenue_usd6    FROM game_events7    WHERE event = 'purchase'8    GROUP BY city, stage9    ORDER BY revenue_usd DESC10    LIMIT 1011''')

#war-room Mon 12:01 PM  Morgan (CMO): Six weeks?! It's 12:01. I'm buying everyone lunch. Except Priya.

Chapter 4: Oops, replay it

#war-room Tue 4:30 PM  Morgan (CMO): Someone on my team ran TRUNCATE on the events. Tell me the data isn't gone.

NEW  PIPELINES_SOURCE_OFFSETS
Shows how far a Kafka Connect pipeline has read in its source. For Kinesis, that's a position per shard.

In [14]:

1offsets = try_sql(f'''2    SELECT * FROM information_schema.PIPELINES_SOURCE_OFFSETS3    WHERE DATABASE_NAME = DATABASE() AND PIPELINE_NAME = '{PIPELINE}'4''', label='PIPELINES_SOURCE_OFFSETS')5offsets

Kinesis keeps records for 24 hours by default, so the events are still in the stream. Simulate the accident, rewind the pipeline to the start of the stream, and load everything again:

NEW  ALTER PIPELINE ... SET OFFSETS for Kafka Connect pipelines
Moves a Kafka Connect pipeline's read position, for example back to the earliest record, to replay data.

In [15]:

1before = run_sql(f'SELECT COUNT(*) FROM {PIPELINE}').iloc[0, 0]2run_sql(f'STOP PIPELINE {PIPELINE}')3run_sql(f'TRUNCATE TABLE {PIPELINE}')4print(f'Oops: {before} events deleted. Rows left: {run_sql(f"SELECT COUNT(*) FROM {PIPELINE}").iloc[0, 0]}')5
6try_sql(f'ALTER PIPELINE {PIPELINE} SET OFFSETS EARLIEST', label='Rewinding the pipeline')7for attempt in range(6):8    try_sql(f'START PIPELINE {PIPELINE} FOREGROUND', label='Replay run')9    after = run_sql(f'SELECT COUNT(*) FROM {PIPELINE}').iloc[0, 0]10    if after >= before:11        break12    time.sleep(5)13print(f'Replayed from Kinesis: {after} of {before} events back.')

#war-room Tue 4:36 PM  Morgan (CMO): I take back everything I said about databases.

Chapter 5: Same trick, other clouds

Kinesis is one connector. The same framework reads Google Cloud Pub/Sub with a connector that comes prepackaged, using the same pattern. If PixelPop's next acquisition runs on Google Cloud, the pipeline looks like this (shown for reference, not run here):

CREATE INFERRED PIPELINE pubsub_pipeline
AS LOAD DATA KAFKACONNECT 'CloudPubSubSourceConnector'
CONFIG '{
  "kafka.topic": "pubsub-topic",
  "cps.project": "my-gcp-project",
  "cps.subscription": "singlestore-events-sub",
  "tasks.max": "1"
}'
CREDENTIALS '{"gcp.credentials.json": "<service-account-key-json>"}'
FORMAT JSON;

Same CREATE INFERRED PIPELINE, same START PIPELINE, same offsets view. Only the connector and its settings change.

What you built

  • A NEW Kafka Connect pipeline that reads Amazon Kinesis directly, with no Kafka or Connect cluster in between

  • A table created for you by CREATE INFERRED PIPELINE, and a view that turns raw records into columns

  • A live return-on-ad-spend scoreboard joining streaming game events with ad campaigns

  • Replay after an accident with NEW PIPELINES_SOURCE_OFFSETS and NEW ALTER PIPELINE ... SET OFFSETS

Clean up

Stops and drops the pipeline (which removes the stored AWS keys from SingleStore), drops the adventure_streams database and deletes the Kinesis stream. The demo's martech database is left alone.

In [16]:

1try_sql(f'STOP PIPELINE {PIPELINE}', label='Stop pipeline')2try_sql(f'DROP PIPELINE IF EXISTS {PIPELINE}', label='Drop pipeline')3try_sql(f'DROP DATABASE IF EXISTS {DB_NAME}', label='Drop database')4try:5    kinesis.delete_stream(StreamName=STREAM, EnforceConsumerDeletion=True)6    print(f'Deleting Kinesis stream {STREAM}.')7except Exception as e:8    print('Could not delete the stream, delete it in the AWS console:', e)9print('All clean. Priya will never know.')

Details


About this Template

Use Kafka Connect pipelines and Kinesis to connect directly to additional streaming sources without intermediate infrastructure.

This Notebook can be run in Standard and Enterprise deployments.

Tags

See Notebook in action

Launch this notebook in SingleStore and start executing queries instantly.

License

This Notebook has been released under the Apache 2.0 open source license.