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.
|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
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
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.
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
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;