Converting Slack JSON data to Snowflake

Uli Bethke Flexter, JSON, Snowflake

In this blog post we will show you how fast and easy it is to obfuscate Slack JSON data and convert it to Snowflake. We will use Sonra’s obfuscation tool Paranoid (open source) to mask the JSON documents we have retrieved from the Slack API. We will use Flexter to convert the data to a relational format in a Snowflake database.

In this post we will using Flexter as a Service (FaaS). FaaS follows a subscription model. Flexter is also available as a free edition and an enterprise edition.

Slack

Slack is a cloud-based collaboration chat software, used in organizations to help teams communicate in a more effective manner.

Slack does this by segregating a team into channels which can be specialized for different uses as needed. This reduces the noise and allows people to stay focused on topic.

Snowflake

Snowflake is a data warehouse platform designed and built from scratch for the cloud. It follows a pay per use model. You are billed by the second and can scale the platform elastically based on workloads and number of concurrent users.

We have written extensively about Snowflake in other parts of the blog:

We provide Snowflake expert services to get you started on the platform.

Exporting data from Slack

Exporting data from Slack is available to Workspace owners and admins of any slack plan. It will export all public channel content from a workspace. We will do it in couple of simple steps.

  1. From your desktop, click your workspace name in the top left.
  2. Select Administration, then Workspace settings from the menu.
  3. Choose Import/Export Data in the top right.
  4. Select the Export tab.
  5. Click Start Export. We'll send you an email once it's ready.
  6. Open the email and click Visit your workspace's export page.
  7. Click Ready for download to access the .zip file.

Masking Slack JSON

In a first step we will be masking our Slack data with Paranoid. We show how you can install install Paranoid in our Masking Sabre XML post.

This will mask all of the values in theJSON document. Optionally Paranoid has the feature to mask individual elements inside of a document.

Let’s have a look at our file after masking

Processing masked XML with FaaS API

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to Snowflake can be done in a few simple steps. For more details please refer to the FaaS API documentation.

Step 1 - Authenticate

Step 2 - Define Source Connection (Upload or S3) for Source Data (JSON/XML)

Step 3 - Optionally define Source Connection (Upload or S3) for Source Schema (XSD)

Step 4 - Define your Target Connection, e.g. Snowflake, Redshift, SQL Server, Oracle etc.

Step 5 - Convert your XML/JSON from Source to Target Connection

Let’s through these steps for the Slack JSON data and convert it to a relational format in Snowflake.

Step 1 - Authenticate

To get an access_token you need to make a call to /oauth/token with Authorization header and 3 form parameters:

  • username=YOUR_EMAIL
  • password=YOUR_PASSWORD
  • grant_type=password

You will get your username and password from Sonra when you sign up for the service.

Example of output

Step 2 - Define Source Connection (Upload) for Source Data (JSON from Slack)

In this step we upload our Slack JSON Source Data

Example of output

Step 4 - Define Target Connection (Snowflake)

As we don’t have a Source Schema we skip the optional step of defining a Source Schema.

Instead we define our Target connection. In this example we convert our JSON data to a relational format in Snowflake.

We give the Target Connection a name and supply various connection parameters to the Snowflake database.

Example of output

Convert XML/JSON automatically to a Database, Text, or Hadoop

No manual coding
Cut cost by up to 80%

Find out more

Step 5 - Convert JSON data from Source Connection (Upload) to Target Connection (Snowflake)

In the next step we will convert our JSON data. Data will be written directly to the Snowflake Target Connection.

Example of output

Example of ER Diagram (Target Data Model)

You can download ER Diagram of our Slack JSON file here.

Next we will run SQL Query where we will select messages with emojis 🙂

Conclusion

We have masked Slack JSON data and converted it to Snowflake tables in a few simple steps. We did in a matter of minutes what would normally take a few hours or even days.

You can try out the free version of Flexter online.

Our enterprise edition can be installed on a single node or for very large volumes of XML on a cluster of servers.

If you have any questions please refer to the Flexter FAQ section. You can also request a demo of Flexter or reach out to us directly.

 

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.