Skip to main content

Integrate a Snowflake database

Connect your Snowflake database and take advantage of Squid's real-time streaming, robust querying, and AI capabilities.​

TL;DR​

In this tutorial you learn how to connect a new Snowflake database to your Squid project. This includes:

This tutorial uses React and Squid to connect to sample data in a Snowflake table. A basic understanding of database terminology, React, and the Squid platform is beneficial, but there are no prerequisites for this guide.

Environment setup​

  1. In the Squid Console, switch to the dev environment.

switch environment

  1. Download the snowflake code sample using the following command. Replace the placeholders with the values from your Squid application as shown in the console.
    2. squid init-sample snowflake --template snowflake --appId YOUR_SQUID_APP_ID --apiKey YOUR_SQUID_API_KEY --environmentId dev --squidDeveloperId YOUR_SQUID_DEVELOPER_ID --region YOUR_REGION
  1. Open the project in the IDE of your choice.
  2. Start the app locally by runnning the following command in the project folder:
npm run start
  1. To view the app, navigate to localhost:PORT where PORT is logged in the terminal. The address will likely be http://localhost:5173.

Create a new Snowflake database​

Snowflake provides several shared databases upon account creation. Since these databases are read-only, we're going to make a copy that we can edit.

In the Snowflake console, navigate to and open a Snowflake Worksheet. In the Worksheet, use the Run all option to execute the following commands. Replace <your_wh> with the name of your data warehouse:

USE WAREHOUSE <your_wh>;

CREATE DATABASE SQUID_TUTORIAL;

CREATE TABLE SQUID_TUTORIAL.PUBLIC.ORDERS LIKE "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS";

INSERT INTO SQUID_TUTORIAL.PUBLIC.ORDERS SELECT * FROM "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS";

These commands create a new database and import data from one of the sample tables to the new database.

Connect Snowflake to Squid​

  1. Click the Integrations tab, and then click Available integrations to view all integrations.
  2. Select the Snowflake integration.
  3. Provide the following configuration information:
Note

Fields are case-sensitive. Ensure you are using uppercase when needed.

  • Integration ID: snowflake-shop

  • Snowflake username

  • Snowflake password - to keep it private, store your password in Squid Secrets.

  • Snowflake account ID - Open the menu in the corner showing your username in the Snowflake console, and then click the Account option from the menu. Hovering over your account brings up a modal with the option to view your full account information. You can use the Copy account identifier button in Snowflake to copy the ID. In the Squid Console, paste this ID and replace the . with a - so the result will be the following: Before: AAAAAAA.AAA00000 After: AAAAAAA-AAA00000

  • Snowflake database name: SQUID_TUTORIAL

  • Snowflake schema: PUBLIC

  • Snowflake warehouse - Use the same warehouse in which you created the database in the previous step.

  • Snowflake role - The name of the role in which the Snowflake database is located. You can find the Snowflake role in the Snowflake console under your Snowflake username.

Once you've entered the connection information, click Test connection to verify that the connection is successful.

  1. Click Add integration to add the integration.

  2. The schema is automatically synced to Squid from Snowflake. Each collection requires a primary key that will serve to identify individual documents (rows) in the collection (table).

  3. In the ORDERS collection in the Squid Console schema preview, click the ... button in the O_ORDERKEY row and select Edit field.

Choose the C_CUSTKEY row

  1. Under Primary key, select Yes from the dropdown menu.

Select primary key

  1. Click Save schema to save the discovered schema.

Security rules​

Security rules are required for all database integrations. This allows you to create custom logic for authorizing access to specific collections or actions, as well as the database as a whole. Since authentication and authorization are outside the scope of this tutorial, our backend code includes a security rule which allows for public access to the snowflake integration.

Tip

To learn more about securing your data, read about Squid's backend security rules.

Security rules are located in the file backend/src/service/example-service.ts. The security rules can be expanded by adding a decorator to the SquidService class. To create a security rule for a database integration, use the @secureDatabase decorator, which takes the database action type and integration_id as a parameter. You can view the current security rule in the ExampleService class:

backend/src/service/example-service.ts
export class ExampleService extends SquidService {
  @secureDatabase('all', 'snowflake-shop')
  allowAllAccessToSnowflakeDb(): boolean {
    return true;
  }
}

Run the app locally​

  1. Start the Squid backend locally using the following command in the terminal opened to your app's backend:
squid start
  1. Start the frontend using the following command in the terminal opened to your app's frontend:
npm run dev
  1. To view the app, navigate to localhost:PORT where PORT is logged in the terminal. The address will likely be http://localhost:5173.

View the app's functionality​

  1. You can view all the documents that match the query in a table displayed in the frontend. The following Squid hooks read the data, which is then displayed by the DisplayOrders component:
App.tsx
  const ordersCollection = useCollection<Order>('ORDERS','snowflake-shop');
  const { data } = useQuery(ordersCollection.query().eq('O_CUSTKEY',121361).dereference());

  ...

    return (
    <div>
      <h1>Orders</h1>
      {data && <DisplayOrders orders={data} />}
      <AddOrder />
    </div>
  );
  1. Click Add order to randomly generate a new order and add it to the database. Notice that this order then appears in the view in real-time. Squid makes real-time syncing with your Snowflake database possible.

And that's it!​

Congratulations! You just connected a Snowflake database to Squid and managed reads and writes from a client application using Squid security functionality. There is so much more that Squid can do with Snowflake and with other integrations, so check out the Squid Client SDK documentation to learn about other database features like mutations and queries, or try out another tutorial like this one that showcases querying your data using Squid AI.