Materialize Documentation
s
Join the Community github/materialize
Home  /  Reference  /  CREATE SOURCE

CREATE SOURCE: PubNub

CREATE SOURCE connects Materialize to an external system you want to read data from, and provides details about how to decode and interpret that data.

This page describes 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.

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;