CREATE SOURCE: JSON over 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 document details how to connect Materialize to a JSON–formatted PubNub channel.
Sources represent connections to resources outside Materialize that it can read data from. For more information, see API Components: Sources.
|MATERIALIZED||Materializes the source’s data, which retains all data in memory and makes sources directly selectable. For more information, see API Components: 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.|
|WITH ( option_list )||Options affecting source creation. For more detail, see
|TEXT||Format the source’s data as ASCII-encoded text.|
|FORMAT BYTES||Leave data received from the source as unformatted bytes, and store them in a column named
|ENVELOPE NONE||(Default) Use an append-only envelope. This means that records will only be appended and cannot be updated or deleted.|
The following options are valid within the
|(No options supported.)|
PubNub source details
A PubNub source represents a subscription to a single PubNub channel. The subscribe key you provide must have permission to access the channel. For more details, see Permission Management in the PubNub documentation.
Text format details
Text-formatted sources reads lines from a file.
- Data from text-formatted sources is treated as newline-delimited.
- Data is assumed to be UTF-8 encoded, and discarded if it cannot be converted to UTF-8.
- Text-formatted sources have one column, which, by default, is named
Raw byte format details
Raw byte-formatted sources provide Materialize the raw bytes received from the source without applying any formatting or decoding.
Raw byte-formatted sources have one column, which, by default, is named
Extracting JSON data from bytes
Materialize cannot receive JSON data directly from a source. Instead, you must
create a source that stores the data it receives as raw bytes (FORMAT
BYTES), and then construct views that provides access to your JSON data by
casting the source’s
bytea column (named
text, and then to
CREATE MATERIALIZED VIEW jsonified_bytes AS SELECT CAST(data AS JSONB) AS data FROM ( SELECT CONVERT_FROM(data, 'utf8') AS data FROM bytea_source )
Append-only envelope means that all records received by the source is treated as an insert. This is Materialize’s default envelope (i.e. if no envelope is specified), and can be specified with ENVELOPE NONE.
PubNub market data
CREATE SOURCE market_orders_raw FROM PUBNUB SUBSCRIBE KEY 'sub-c-4377ab04-f100-11e3-bffd-02ee2ddab7fe' CHANNEL 'pubnub-market-orders';
This creates a source that…
- Connects to the PubNub demo market orders channel.
- Treats message values as text.
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;