The destination table has only one column that will store the JSON object with the VARIANT type.Snowflake auto-detect files compressed with gzip.Snowflake natively understands json objects, and stores them in a semi-columnar format.What’s interesting about this table on Snowflake: json.gz files from S3 into a Snowflake native table: create or replace table reddit_comments_sample(v variant) copy into reddit_comments_sample from file_format = (type=json) Then Snowflake can see these files by following the docs to create a storage integration: use role accountadmin create or replace storage integration s3_int_reddit type = external_stage storage_provider = s3 enabled = true storage_aws_role_arn = 'arn:aws:iam::4815162342:role/my_role' storage_allowed_locations = ('s3://my-s3/') desc integration s3_int_reddit grant create stage on schema public to role sysadmin grant usage on integration s3_int_reddit to role sysadmin use role sysadmin create or replace stage my_ext_stage_reddit url='s3://my-s3/' storage_integration = s3_int_reddit list we can ingest these. Unzstd+split+gzip at work Push to S3 and Snowflakeīecause I chose a Graviton2 VM, the easiest way to make the files available to Snowflake was to move them to S3 ( snowsql is not yet available on Arm): aws s3 cp -recursive.

These are the archives that I’ll load into Snowflake, but first I need to download them.įor example, all the reddit comments from December 2020 live in a 19GB file:

Jason Baumgartner shares through PushShift.io billions of reddit comments and submissions.

Snowflake supports not only JavaScript UDFs, but also Java UDFs and external functions - which opens up a world of possibilities within the database.

Snowflake supports advanced SQL syntax for recursive queries - which is awesome to analyze threaded comments.

Snowflake stores data compressed - in this case with a ratio better than 1:10 compared with the original files.

Snowflake understands JSON objects on load, and optimizes the syntax and storage for them - without the need to predefine a schema.

Working with semi-structured data in Snowflake is fast, easy, and fun.

These are some of the Snowflake features that delighted me, that I didn’t get with BigQuery: Loading and analyzing reddit comments in Snowflake is fun, and a great way to show its powers. Querying for the subreddits with the most comments

Max for live download reddit