Streaming to Materialize

​

What’s Materialize?

Materialize is a streaming database that enables real-time analytics using SQL. Materialize ingests data from a variety of sources, including databases, event streams, and message queues. You create materialized views on top of this data, and Materialize keeps them up-to-date with the latest data. This means you can write complex queries that are executed with low-latency.

Common use cases are dashboards, fraud detection, and personalized recommendations.

Sequin makes it easy to stream data from APIs like Salesforce and ServiceNow into Materialize. This opens up a wide range of use cases, such as managing IT services in real-time with ServiceNow, tracking issues with Jira, and monitoring deployments and infrastructure with AWS.

To stream data to Materialize via Sequin, you will:

• Setup a webhook source in Materialize
• Connect Sequin to an API source
• Create a Sequin webhook consumer that streams to Materialize
• Create materialized views over your data in Materialize

The easiest way to stream data from Sequin to Materialize is via webhooks. Sequin will first send all historical data in the API via webhooks, then will keep Materialize up-to-date by sending changes as they appear in the API. This means Materialize will always have a complete view of API data.

Sequin’s webhooks are designed to guarantee at least once delivery, making them safe to count on.

​

Setup a webhook source in Materialize

After signing up for Materialize, visit the console to setup your webhook endpoint.

You’ll want to ensure your endpoint is protected so that only Sequin can POST data to it. For example, you can have Sequin provide a bearer token in its requests. To do so, first create a secret in Materialize. This will be a shared secret with Sequin:

CREATE SECRET sequin_secret AS 'your-secret-here';

Now, you can create a webhook source that validates incoming requests using that secret:

CREATE SOURCE sequin_hooks IN CLUSTER my_cluster FROM WEBHOOK
  BODY FORMAT JSON
  CHECK (
    WITH (HEADERS, SECRET sequin_secret)
      headers->'Authorization' = 'Bearer ' || sequin_secret
  );

The CHECK clause is how we tell Materialize to validate incoming webhooks. Here, we do a simple equality match between the Authorization header and Bearer {sequin_secret}.

Materialize will return the URL of the webhook endpoint:

CREATE SOURCE

NOTICE: URL to POST data is 'https://UKQMM3J9S26GvUXsTL5xALfj0c.us-east-1.aws.materialize.cloud/api/webhook/materialize/public/sequin_hooks'

Returned in 431.5ms

To make sure everything is working properly, you can make an API request to the URL:

curl https://UKQMM3J9S26GvUXsTL5xALfj0c.us-east-1.aws.materialize.cloud/api/webhook/materialize/public/sequin_hooks \
  -H 'content-type: application/json'
  -H 'authorization: Bearer {token}'
  -d '{ "hello": "world" }'

And you’ll see it over in Materialize:

select * from sequin_hooks;

Results
-------
{ "hello": "world" }

​

Connect Sequin to an API source

You can connect Sequin to an upstream API using either the Sequin console or API.

Here’s a curl request that connects Sequin to Salesforce:

curl --request POST \
  --url https://api.sequin.io/v1/syncs \
  --header 'Authorization: Bearer {your-api-token}' \
  --header 'Content-Type: application/json' \
  --data '{
    "provider": "salesforce",
    "name": "Choam Corp - Salesforce Production",
    "collection_ids": ["salesforce:contact", "salesforce:opportunity"],
    "credential": {
      "properties": {
        "kind": "salesforce_oauth2",
        "secret": "dBeAXpLjPaYKN67uJLsh8ZEKZYQz1O52bQ",
        "name": "Salesforce prod"
      },
      "oauth_app_id": "8ff58ef0-d376-4ae8-b2e2-9f0206aa65b8",
    }
  }'

This request provides Sequin with a refresh token it will use to make requests to Salesforce. The request specifies that Sequin should sync the Contact and Opportunity objects from Salesforce.

​

Create a webhook consumer

Next, you’ll create a webhook consumer to stream data from the Salesforce account to Materialize. Consumers are how you stream data from Sequin’s syncs to destinations.

You can create consumers in Sequin’s console or API. Here’s an example curl request to create a webhook consumer:

curl --request POST \
  --url https://api.sequin.io/v1/webhook_consumers \
  --header 'Authorization: Bearer {your-api-token}' \
  --header 'Content-Type: application/json' \
  --data '{
    "sync_id": "e39875af-cd83-4406-8d46-3b30e0b5b624",
    "name": "Materialize - sequin_hooks",
    "http_endpoint": {
      "base_url": "https://UKQMM3J9S26GvUXsTL5xALfj0c.us-east-1.aws.materialize.cloud/api/webhook/materialize/public/sequin_hooks",
      "encrypted_headers": {
        "authorization": "Bearer {sequin-secret}"
      }
    }
  }'

For http_endpoint, specify the base_url of the Materialize webhook source you just created. In encrypted_headers, instruct Sequin to send the secret you created earlier as a bearer token.

You can specify a sync_id or a provider (e.g. salesforce), and Sequin will only send data from that sync or provider to the destination. Or, you can omit both, and all Sequin data will be sent to the destination.

At this point, your API data should be flowing into Materialize! Run some queries in the Materialize console to verify the connection has been established:

select count(*) from sequin_hooks;

Results
-------
5251

To see what an individual record looks like in Materialize, grab one:

select * from sequin_hooks limit 1;

Results
-------
{"data":{"collection_id":"salesforce:opportunity","data": ... }

As you can see, webhooks are stored at rest as JSONB inside a single column. All webhooks from Sequin flow into the sequin_hooks table.

From here, you can create views to extract your API data into tables.

​

Create materialized views over your data in Materialize

Materialize has a handy tool you can use to construct a view given a JSON object:

Paste in an example JSON object from the sequin_hooks table. Set the source to sequin_hooks and the JSON column to body. You can use the collection_id on a webhook payload to discriminate the object type. For example, here’s what creating a materialized view might look like for a Salesforce Opportunity:

CREATE MATERIALIZED VIEW salesforce_opportunity AS SELECT
    body->'data'->>'collection_id' AS collection_id,
    body->'data'->'data'->>'AccountId' AS AccountId,
    (body->'data'->'data'->>'Amount')::numeric AS Amount,
    body->'data'->'data'->>'CampaignId' AS CampaignId,
    body->'data'->'data'->>'CloseDate' AS CloseDate,
    body->'data'->'data'->>'ContactId' AS ContactId,
    body->'data'->'data'->>'CreatedDate' AS CreatedDate,
    body->'data'->'data'->>'DeliveryInstallationStatus__c' AS DeliveryInstallationStatus__c,
    body->'data'->'data'->>'Description' AS Description,
    (body->'data'->'data'->>'ExpectedRevenue')::numeric AS ExpectedRevenue,
    body->'data'->'data'->>'Fiscal' AS Fiscal,
    (body->'data'->'data'->>'FiscalQuarter')::numeric AS FiscalQuarter,
    (body->'data'->'data'->>'FiscalYear')::numeric AS FiscalYear,
    (body->'data'->'data'->>'IsWon')::bool AS IsWon,
    body->'data'->>'sync_id' AS sync_id,
    body->'data'->>'upstream_created_at' AS upstream_created_at,
    body->'data'->>'upstream_id' AS upstream_id,
    body->'data'->>'upstream_updated_at' AS upstream_updated_at
FROM sequin_hooks
where body->'data'->>'collection_id' = 'salesforce:opportunity';

This materialized view flattens out the nested JSON structure into a columnar view that’s easy to work with. Repeat with the other collections that you’re syncing.

The beautiful part is the speed of data propagating through the system. Make an update to a Salesforce Opportunity. In just a few seconds, it will have flowed through Sequin to Materialize, and Materialize will have updated your materialized view with the latest record!

​

Where to next?

If you weren’t following along already, now’s a good time to head over to Materialize to sign-up for a free trial.

To learn more about what makes Materialize unique, check out the intro to their docs.