Skip to content
Integrations

Snowflake

Add or upsert the results of each successful run as rows in your own Snowflake table.

Adds the dataset items of each successful run as rows to a table in your own Snowflake account. Scrape.ly signs in with key-pair authentication: you create a Snowflake user for Scrape.ly, give it a public key, and paste the matching private key. The private key is stored encrypted and never shown again. There is no app to install and nothing to approve.

Prepare Snowflake

Scrape.ly writes into a table that already exists; it never creates or changes tables. Create one with a column for each result field you want:

.sql
CREATE TABLE ANALYTICS.PUBLIC.RESULTS (
  URL        STRING,
  TITLE      STRING,
  PRICE      NUMBER(10,2),
  TAGS       VARIANT,
  SCRAPED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

Make a key pair on your computer. The private key stays with you until you paste it into Scrape.ly:

.bash
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out scrapely_key.p8 -nocrypt
openssl rsa -in scrapely_key.p8 -pubout -out scrapely_key.pub

Leave out -nocrypt to protect the key with a passphrase; Scrape.ly then asks for it once, to read the key, and does not store it.

Then create a role that may only use this table, and a user for Scrape.ly with the public key (the contents of scrapely_key.pub without the -----BEGIN/END PUBLIC KEY----- lines):

.sql
CREATE ROLE SCRAPELY_WRITER;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE SCRAPELY_WRITER;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE SCRAPELY_WRITER;
GRANT USAGE ON SCHEMA ANALYTICS.PUBLIC TO ROLE SCRAPELY_WRITER;
GRANT SELECT, INSERT, UPDATE ON TABLE ANALYTICS.PUBLIC.RESULTS TO ROLE SCRAPELY_WRITER;

CREATE USER SCRAPELY
  TYPE = SERVICE
  DEFAULT_ROLE = SCRAPELY_WRITER
  DEFAULT_WAREHOUSE = COMPUTE_WH
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...';
GRANT ROLE SCRAPELY_WRITER TO USER SCRAPELY;

UPDATE is only needed for Update existing rows. Run DESC USER SCRAPELY to see RSA_PUBLIC_KEY_FP; it matches the fingerprint Scrape.ly shows after you save the key.

Send results to a table

  1. Click New automation (in Automations, or on an Actor's Automations tab) and pick Send results to Snowflake, or click Or create manually and, under Send, click Snowflake.
  2. Enter the Account identifier from your account URL, such as xy12345.us-east-2.aws or myorg-myaccount (pasting the whole account URL works too), and the User (SCRAPELY).
  3. Paste the Private key: the whole contents of scrapely_key.p8, including the -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY----- lines.
  4. Optionally enter a Warehouse and Role. Empty uses the user's DEFAULT_WAREHOUSE and DEFAULT_ROLE.
  5. Enter the Table as DATABASE.SCHEMA.TABLE, such as ANALYTICS.PUBLIC.RESULTS.
  6. Choose the Write mode: Add new rows, or Update existing rows (upsert) with a Key column.
  7. Optionally set Max rows per run (1 to 100,000, default 1,000).
  8. Use Save and send test. The test signs in, checks that the table exists and that the role may write to it, and writes nothing.

Connection: Scrape.ly uses Snowflake's SQL API over HTTPS, and only ever sends requests to <account>.snowflakecomputing.com. Accounts that are only reachable through Private Link are not supported. If the user has a network policy, it must allow Scrape.ly's requests from the internet.

Columns: a result field is written to the column with the same name (upper or lower case doesn't matter). A nested field whose parent has no column of its own goes into the column named parent and child joined with _, so address.city goes into ADDRESS_CITY. Objects and lists go into VARIANT, OBJECT, or ARRAY columns as they are, and into text columns as JSON. Fields without a column are skipped, and the delivery history lists them. Columns without a field get their default value. Snowflake converts each value to the column's type; a value it can't convert, such as abc for a NUMBER column, skips that row.

Upsert: with Update existing rows, a row whose key column already holds the item's value is updated instead of added (MERGE). Snowflake has no unique indexes, so the key column needs none. Only the columns the item has a field for are updated; the others keep their values. Items without a value for the key column are skipped and counted. When one run has several items with the same key, the last one wins.

When it writes: only for succeeded runs, and only when the run returned items. Rows go in up to 1,000 at a time, so a failure partway leaves the earlier rows written, and the delivery history says how many rows are in the table. A row Snowflake refuses is skipped and counted in the delivery history; most other rows are still written.

Cost: the rows are written by your warehouse, so each run uses a few seconds of its compute, billed by Snowflake to your account. A suspended warehouse resumes for the write (when AUTO_RESUME is on) and Snowflake bills at least a minute each time it resumes.

Limits: each Snowflake automation of this kind writes at most 30 times an hour, and an account at most 500 exports a day (Google Sheets, S3, GitHub, Airtable, HubSpot, Salesforce, Notion, Dropbox, OneDrive, Postgres, Azure Blob, BigQuery, OpenAI Vector Store, and Snowflake together). A statement that takes longer than 60 seconds is stopped, and an export stops after 8 minutes. If the key sign-in is refused, there is no warehouse, the table doesn't exist, the role may not write to it, or none of the fields match a column, the delivery history on the automation's page says so.

API

A webhook with a snowflake notification. Only ACTOR.RUN.SUCCEEDED is allowed.

.bash
curl -X POST https://api.scrape.ly/v2/webhooks \
  -H "Authorization: Bearer $SCRAPELY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "eventTypes": ["ACTOR.RUN.SUCCEEDED"],
    "condition": { "actorId": "YOUR_ACTOR_ID" },
    "description": "Results to Snowflake",
    "notification": {
      "channel": "snowflake",
      "account": "xy12345.us-east-2.aws",
      "username": "SCRAPELY",
      "privateKey": "-----BEGIN PRIVATE KEY-----\nMIIEvQ...\n-----END PRIVATE KEY-----",
      "warehouse": "COMPUTE_WH",
      "table": "ANALYTICS.PUBLIC.RESULTS",
      "mode": "upsert",
      "keyColumn": "URL",
      "maxRows": 1000
    }
  }'

mode is insert (default) or upsert; keyColumn is only used with upsert. Add passphrase when the key is encrypted, and role to use a role other than the user's default. Responses carry notification.snowflakeSettings with account, username, warehouse, role, table, mode, keyColumn, maxRows, and the keyFingerprint of the public key; the private key is never returned. On PUT, leaving out privateKey keeps the saved one. In an automation, use a delivery with "channel": "snowflake" and the same fields.