Extend Real-Time Ingestion with Kafka Connect
Notebook
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.
| Chapter | What happens | You use |
|---|---|---|
| 1 | The acquisition: game events start flowing into Kinesis | Amazon Kinesis, the Digital Marketing dataset |
| 2 | No middlemen: Kinesis straight into SingleStore | NEW Kafka Connect pipeline (LOAD DATA KAFKACONNECT) |
| 3 | The live scoreboard: which ads make money | Background pipeline + SQL on fresh data |
| 4 | Oops, replay it | NEW PIPELINES_SOURCE_OFFSETS, NEW ALTER PIPELINE ... SET OFFSETS |
| 5 | Same trick, other clouds | Google 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
Make sure your cluster is running.
Open the website and fill in the connection form: your cluster host with
https://in front (find it under Connect in the portal), usernameadmin, your cluster password, and database namemartech. Click Connect.Click Setup Database and keep the Simulator switch on.
Under Locations, tick a few cities. Each city adds ad campaigns.
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).
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
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
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 setup | With a Kafka Connect pipeline |
|---|---|
| Kafka cluster (brokers, storage, upgrades) | Not needed |
| Kafka Connect worker cluster | Not needed: the connector runs in the pipeline |
| A sink connector and its configuration | Not needed: SingleStore is the destination |
| Monitoring for each hop | One 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
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''')
Chapter 4: Oops, replay it
TRUNCATE on the events. Tell me the data isn't gone.
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:
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.')
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 columnsA live return-on-ad-spend scoreboard joining streaming game events with ad campaigns
Replay after an accident with NEW
PIPELINES_SOURCE_OFFSETSand NEWALTER 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.