Materialize Documentation
s
Join the Community github/materialize

CREATE SOURCE: PubNub

CREATE SOURCE connects Materialize to an external data source and lets you interact with its data as if the data were in a SQL table.

This page details how to connect Materialize to a PubNub channel, which provides a quick way to get up and running with no external dependencies before plugging in your own data sources.

Conceptual framework

Sources represent connections to resources outside Materialize that it can read data from. For more information, see Key Concepts: Sources.

Syntax

CREATE MATERIALIZED SOURCE IF NOT EXISTS src_name ( col_name , ) FROM PUBNUB SUBSCRIBE KEY key CHANNEL channel FORMAT TEXT ENVELOPE NONE

with_options

WITH ( field = val , )
Field Use
MATERIALIZED Materializes the source’s data, which retains all data in memory and makes sources directly selectable. For more information, see Key Concepts: Materialized sources.
src_name The name for the source, which is used as its table name within SQL.
col_name Override default column name with the provided identifier. If used, a col_name must be provided for each column in the created source.
SUBSCRIBE KEY The secret subscription key for your PubNub account.
CHANNEL The name of the channel to subscribe to.
ENVELOPE NONE (Default) Use an append-only envelope. This means that records will only be appended and cannot be updated or deleted.
WITH ( option_list ) Options affecting source creation. For more detail, see WITH options.

Features

Generating sample data

PubNub provides multiple sample data streams that you can connect to and play around with as you ramp up with Materialize. To subscribe to a PubNub channel, you must provide a SUBSCRIBE KEY and a CHANNEL:

CREATE SOURCE twitter_raw
FROM PUBNUB
SUBSCRIBE KEY 'sub-c-78806dd4-42a6-11e4-aed8-02ee2ddab7fe'
CHANNEL 'pubnub-twitter';

The subscribe key provided must have permission to access the channel. For more details and a complete list of sample data streams, see the PubNub documentation.

Examples

Creating a source

Using the market orders PubNub data stream:

CREATE SOURCE market_orders_raw
FROM PUBNUB
SUBSCRIBE KEY 'sub-c-4377ab04-f100-11e3-bffd-02ee2ddab7fe'
CHANNEL 'pubnub-market-orders';

The source will have one column containing JSON (named text by default). To use this data in views, you can extract the message fields using Materialize’s jsonb functions:

CREATE MATERIALIZED VIEW market_orders AS
SELECT
    (text::jsonb)->>'bid_price' AS bid_price,
    (text::jsonb)->>'order_quantity' AS order_quantity,
    (text::jsonb)->>'symbol' AS symbol,
    (text::jsonb)->>'trade_type' AS trade_type,
    to_timestamp(((text::jsonb)->'timestamp')::bigint) AS timestamp
FROM market_orders_raw;
Did this info help?
Yes No