Ingest knowledge from Snowflake to Amazon S3 utilizing AWS Glue Market Connectors

In as we speak’s advanced enterprise panorama, organizations are challenged to eat from number of sources and sustain with knowledge that pours in all by means of the day. There’s a demand to design functions that allows knowledge to be moveable throughout cloud platforms and provides them the flexibility to derive insights from a number of knowledge sources to stay aggressive. On this put up, we exhibit how AWS Glue integration with Snowflake has simplified the method of connecting to Snowflake and making use of knowledge transformations with out writing a single line of code. With AWS Glue Studio, now you can use a easy visible interface to compose jobs for migrations that transfer and combine knowledge. It lets you subscribe to a Snowflake connector in AWS Market, question Snowflake tables and save the information in Amazon Easy Storage Service (Amazon S3) as Parquet format.

When you select to carry your personal customized connector or want a special connector from AWS Market, observe the steps on this weblog Performing knowledge transformations utilizing Snowflake and AWS Glue. On this put up, we use the brand new AWS Glue Connector for Snowflake to seamlessly join with Snowflake with out the necessity to set up JDBC drivers. To validate the information ingested, we use Amazon Redshift Spectrum to create an exterior desk and question the information in Amazon S3. With Amazon Redshift Spectrum, you’ll be able to effectively question and retrieve knowledge from information in Amazon S3 with out having to load the information into Amazon Redshift tables.

Answer Overview

Let’s check out the structure diagram on how AWS Glue connects to Snowflake for knowledge ingestion.

Stipulations

Earlier than you begin, be sure to have the next:

1. An account in Snowflake, particularly a service account that has permissions to tables to be queried.
2. AWS Identification and Entry Administration (IAM) permissions in place to create AWS Glue and Amazon Redshift service roles and insurance policies. To configure, observe the directions in Establishing IAM Permissions for AWS Glue and Create an IAM function for Amazon Redshift.
3. Amazon Redshift Serverless endpoint. When you should not have it configured, observe the directions in Amazon Redshift Serverless Analytics.

Configure the Amazon S3 VPC Endpoint

As a primary step, we configure an Amazon S3 VPC Endpoint to allow AWS Glue to make use of a non-public IP handle to entry Amazon S3 with no publicity to the general public web. Full the next steps.

1. Open the Amazon VPC console.
2. Within the left navigation pane, select Endpoints.
3. Select Create Endpoint, and observe the steps to create an Amazon S3 VPC endpoint of kind Gateway.

Subsequent, we create a secret utilizing AWS Secrets and techniques Supervisor

1. On AWS Secrets and techniques Supervisor console, select Retailer a brand new secret.
2. For Secret kind, choose Different kind of secret.
3. Enter a key as sfUser and the worth as your Snowflake consumer identify.
4. Enter a key as sfPassword and the worth as your Snowflake consumer password.
5. Select Subsequent.
6. Title the key snowflake_credentials and observe by means of the remainder of the steps to retailer the key.

Subscribe to AWS Market Snowflake Connector

To subscribe to the connector, observe the steps and activate Snowflake Connector for AWS Glue. This native connector simplifies the method of connecting AWS Glue jobs to extract knowledge from Snowflake

1. Navigate to the Snowflake Connector for AWS Glue in AWS Market.
2. Select Proceed to Subscribe.
3. Overview the phrases and situations, pricing, and different particulars.
4. Select Proceed to Configuration.
5. For Supply Methodology, select your supply technique.
6. For Software program model, select your software program model
7. Select Proceed to Launch.
8. Underneath Utilization directions, select Activate the Glue connector in AWS Glue Studio. You’re redirected to AWS Glue Studio.
9. For Title, enter a reputation on your connection (for instance, snowflake_s3_glue_connection).
10. Optionally, select a VPC, subnet, and safety group.
11. For AWS Secret, select snowflake_credentials.
12. Select Create connection.

A message seems that the connection was efficiently created, and the connection is now seen on the AWS Glue Studio console.

Configure AWS Glue for Snowflake JDBC connectivity

Subsequent, we configure a AWS Glue job by following the steps under to extract knowledge.

1. On the AWS Glue console, select AWS Glue Studio on the left navigation pane.
2. On the AWS Glue Studio console, select Jobs on the left navigation pane.
3. Create a job with “Visible with supply and goal” and select the Snowflake connector for AWS Glue 3.0 because the supply and Amazon S3 because the goal.
4. Enter a reputation for the job.
5. Underneath job particulars, choose an IAM function.
6. Create a brand new IAM function in the event you don’t have already with required AWS Glue and AWS Secrets and techniques Supervisor insurance policies.
7. Underneath Visible, Select the Information supply – Connection node and select the connection you created.
8. In connection choices, create a key worth pair with question as proven under. Notice that CUSTOMER desk in SNOWFLAKE_SAMPLE_DATA database is taken into account for this migration. This desk will get preloaded (1.5M rows) whenever you set up Snowflake Schema.
   
   

   key	worth
   question	SELECT C_CUSTKEY, C_NAME, C_ADDRESS, C_NATIONKEY, C_PHONE, C_ACCTBAL, C_MKTSEGMENT, C_COMMENT FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
   sfUrl	MXA94638.us-east-1.snowflakecomputing.com
   sfDatabase	SNOWFLAKE_SAMPLE_DATA
   sfWarehouse	COMPUTE_WH

9. Within the Output schema part, specify the supply schema as key-value pairs as proven under.
   
10. Select the Rework-ApplyMapping node to view the next rework particulars.
11. Select the Information goal properties – S3 node and enter S3 bucket particulars as proven under.
12. Select Save.
    

After you save the job, the next script is generated. It assumes the account info and credentials are saved in AWS Secrets and techniques Supervisor as described earlier.

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

args = getResolvedOptions(sys.argv, ["JOB_NAME"])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args["JOB_NAME"], args)

# Script generated for node Snowflake Connector for AWS Glue 3.0
SnowflakeConnectorforAWSGlue30_node1 = glueContext.create_dynamic_frame.from_options(
    connection_type="market.spark",
    connection_options={
        "question": "SELECT C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_MKTSEGMENT,C_COMMENT FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER",
        "sfUrl": "MXA94638.us-east-1.snowflakecomputing.com",
        "sfDatabase": "SNOWFLAKE_SAMPLE_DATA",
        "sfWarehouse": "COMPUTE_WH",
        "connectionName": "snowflake_s3_glue_connection",
    },
    transformation_ctx="SnowflakeConnectorforAWSGlue30_node1",
)

# Script generated for node ApplyMapping
ApplyMapping_node2 = ApplyMapping.apply(
    body=SnowflakeConnectorforAWSGlue30_node1,
    mappings=[
        ("C_CUSTKEY", "decimal", "C_CUSTKEY", "decimal"),
        ("C_NAME", "string", "C_NAME", "string"),
        ("C_ADDRESS", "string", "C_ADDRESS", "string"),
        ("C_NATIONKEY", "decimal", "C_NATIONKEY", "decimal"),
        ("C_PHONE", "string", "C_PHONE", "string"),
        ("C_ACCTBAL", "decimal", "C_ACCTBAL", "decimal"),
        ("C_MKTSEGMENT", "string", "C_MKTSEGMENT", "string"),
        ("C_COMMENT", "string", "C_COMMENT", "string"),
    ],
    transformation_ctx="ApplyMapping_node2",
)

# Script generated for node S3 bucket
S3bucket_node3 = glueContext.write_dynamic_frame.from_options(
    body=ApplyMapping_node2,
    connection_type="s3",
    format="glueparquet",
    connection_options={"path": "s3://sf-redshift-po/check/", "partitionKeys": []},
    format_options={"compression": "snappy"},
    transformation_ctx="S3bucket_node3",
)

job.commit()

13. Select Run to run the job.

After the job completes efficiently, the run standing ought to change to Succeeded.

The next screenshot reveals that the information was written to Amazon S3.

Question Amazon S3 knowledge utilizing Amazon Redshift Spectrum

Let’s question the information in Amazon Redshift Spectrum

1. On the Amazon Redshift console, select the AWS Area.
2. Within the left navigation pane, select Question Editor.
3. Run the create exterior desk DDL given under.

   CREATE EXTERNAL TABLE "spectrum_schema"."buyer"
   (c_custkey   decimal(10,2),
   c_name       varchar(256),
   c_address    varchar(256),
   c_nationkey  decimal(10,2),
   c_phone      varchar(256),
   c_acctbal    decimal(10,2),
   c_mktsegment varchar(256),
   c_comment    varchar(256))
   saved as parquet
   location 's3://sf-redshift-po/check'; 

   

4. Run the choose question on the CUSTOMER desk.

SELECT depend(c_custkey), c_nationkey FROM "dev"."spectrum_schema"."buyer" group by c_nationkey

Issues

The AWS Glue crawler doesn’t work instantly with Snowflake. It is a native functionality that you need to use for different AWS knowledge sources which might be joined or linked within the AWS Glue ETL job. As an alternative, you’ll be able to outline connections within the script as proven earlier on this put up.

The Snowflake supply tables coated on this put up solely give attention to structured knowledge varieties and subsequently semi-structured or unstructured knowledge varieties in Snowflake (binary, varbinary, and variant) are out of scope. Nevertheless, you can use AWS Glue features similar to relationalize to flatten nested schema knowledge into semi-normalized constructions, or you can use Amazon Redshift Spectrum to assist these knowledge varieties.

Conclusion

On this put up, we learnt methods to outline Snowflake connection parameters in AWS Glue, hook up with Snowflake from AWS Glue utilizing the AWS native connector for Snowflake, migrate to Amazon S3 and use Redshift Spectrum to question knowledge in Amazon S3 to satisfy your online business wants.

We welcome any ideas or questions within the feedback part under

In regards to the Authors

Sindhu Achuthan is a Information Architect with International Monetary Providers at Amazon Net Providers. She works with prospects to supply architectural steering for analytics options on Amazon Glue, Amazon Redshift, AWS Lambda, and different providers. Exterior work, she is a DIYer, likes to go on lengthy trails, and do yoga.

Shayon Sanyal is a Sr. Options Architect specializing in databases at AWS. His day job permits him to assist AWS prospects design scalable, safe, performant and strong database architectures on the cloud. Exterior work, you will discover him mountain climbing, touring or coaching for the following half-marathon.