View Categories

Snowflake

Table of Contents

How to Connect PeaSoup S3 to Snowflake

Follow these steps to configure Snowflake to access and store data on PeaSoup S3. Since PeaSoup is S3-compatible, you can integrate it as an external stage or cloud storage backend for Snowflake.

Prerequisites

  • PeaSoup S3 Access: Make sure you have the following information:
    • Access key
    • Secret key
    • Bucket name
    • PeaSoup S3 endpoint URL https://s3.eu-west-1.peasoup.cloud
  • Snowflake Account with access to create external stages and run SQL queries.

Steps to Connect PeaSoup S3 to Snowflake

  1. Log into Snowflake:
  2. Create an External Stage for PeaSoup S3:
    • In Snowflake, you can define an external stage to reference data stored in PeaSoup S3.
    • Run the following SQL query to create the stage using PeaSoup S3:CREATE OR REPLACE STAGE pea_soup_stage URL=’s3://your-bucket-name/’ CREDENTIALS = (AWS_KEY_ID=’your-access-key’ AWS_SECRET_KEY=’your-secret-key’) ENDPOINT=’https://s3.eu-west-1.peasoup.cloud‘;
    • This will set up an external stage that points to your PeaSoup S3 bucket.
  3. Copy Data from PeaSoup S3 to Snowflake:
    • Once the stage is set up, you can load data from PeaSoup S3 into Snowflake tables. Use the following query to copy data from the external stage:COPY INTO your_table FROM @pea_soup_stage/path-to-your-file FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = ‘”‘);
    • Ensure the file format matches your data (CSV, JSON, Parquet, etc.).
  4. Query Data from External Stage (Optional):
    • You can also query data directly from PeaSoup S3 without loading it into Snowflake. For example, to query data in CSV format:SELECT * FROM @pea_soup_stage/path-to-your-file (FILE_FORMAT => (TYPE = ‘CSV’ FIELD_OPTIONALLY_ENCLOSED_BY = ‘”‘));
    • This method allows you to work with the data in PeaSoup S3 without loading it into a Snowflake table.
  5. Secure PeaSoup S3 Access (Optional):
    • For additional security, you can configure Snowflake to use external authentication mechanisms like AWS IAM roles or policies (if PeaSoup supports similar authentication features).
    • Modify the credentials section in the stage creation to use a more secure access control method, such as temporary credentials or tokens.
  6. Test the Configuration:
    • After setting up the stage, test loading data from PeaSoup S3 to ensure the integration is working smoothly.
    • Monitor the data loading and querying process to ensure performance and accuracy.

Optional: Manage File Formats

  • You can create specific file formats for different types of data stored in PeaSoup S3. For example, if your data is in JSON format:CREATE OR REPLACE FILE FORMAT json_format TYPE = ‘JSON’;
  • Then, use this format in your data loading queries:COPY INTO your_table FROM @pea_soup_stage/path-to-your-json-file FILE_FORMAT = json_format;

Notes

  • Ensure that PeaSoup S3 is accessible over the internet, and configure the correct permissions for Snowflake to access the bucket.
  • PeaSoup’s S3-compatible API allows seamless integration with Snowflake’s external stages, enabling scalable cloud-based data processing and storage.