Converting Slack JSON data to a database (Snowflake)

August 6, 2019

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.

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 Paranoid in our Masking Sabre XML post.


This will mask all of the values in the JSON 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 Flexter

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to Snowflake can be done in a few simple steps.
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.
[flexter_button]

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

[blogBannerFlexter]

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.
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.
Enjoyed this post? Have a look at the other posts on our blog.
Contact us for Snowflake professional services.
We created the content in partnership with Snowflake.

In this video, we use Flexter to automatically convert very complex FpML XML to Snowflake tables. Book a demo to see the power of Flexter in action!