Working with JSON Files In Snowflake Using Datameer

Ndz Anthony
December 30, 2022

This article will explore how to extract and explore your JSON Snowflake data within Datameer.

JSON outputs from Server APIs can vary from plain to highly nested.

Without the right expertise and tools, working with JSON can seem non-intuitive and complicated, especially for a non-data-savvy user.

And That’s why as part of our low/no-code approach, we decided to introduce a product feature to cater to your user’s JSON modeling needs.

With Datameer’s robust JSON handling features, performing manipulations on your JSON data can happen in a matter of seconds -literally!

We will cover the following:

Loading JSON Data Into Snowflake
JSON Representation in Datameer
Exploring JSON data using Datameer’s SQL Editor
Working with JSON in the Formula Builder

Let’s dive in!

WHAT IS JSON?

According to Snowflake, JavaScript Object Notation (JSON) is an open-standard data format or interchange for semi-structured data.

It is text-based and can be read by humans and machines.

STRUCTURE OF JSON

The format of JSON data is key/value pairs. The value is on the right, and the key is on the left, separated by a colon (:) in the middle.

Each key/value pair is separated from the next by a comma (,). A string enclosed in double quotation marks, such as “name,” is the key.

JSON FORMAT AND DATA TYPES

The following value types are permissible:

Number
String- Double quotation marks are used to separate a group of Unicode characters.
Boolean: True or False
Array- square-bracketed list of values, such as { “Datameer,” “Sun,” or “Bike” }
Object – For instance, a group of key/value pairs enclosed in curly braces

LOADING JSON DATA INTO SNOWFLAKE

Datameer supports JSON from sources such as CRM systems like Hubspot, Google Analytics, etc. As long as it’s within your snowflake environment, Datameer can work with it.

Speaking of data within the snowflake environment, let’s quickly discuss how to load your JSON data into a snowflake.

Depending on your use case, there are a dozen ways to push data into Snowflake.

To load JSON data:

1. Within Snowflake’s Web UI, select the data loading tab.

2. Select the proper warehouse and press “Next.”

3. Pick the JSON file

4. Create JSON file format

A file format describes the staged data files that will be loaded into Snowflake.

5. Please check to make sure there are no negative records. To load data, click ‘Load.’

Additional Considerations-

Suppose you’re not using the classic console. You can view the resources below for additional help getting your data into a snowflake.

JSON REPRESENTATION IN DATAMEER

When working with sub-structured data in Datameer, you can use the representation dialog to preview the contents of your JSON column.

With the representation dialog and data Grid dialog, users can explore, navigate and understand the path expressions within their JSON data set.

See video below

WORKING WITH JSON IN THE SQL EDITOR

JSON is the preferred format whenever data from one server needs to be transferred to a web page because front-end applications like Android, iOS, React or Angular, etc., can parse the JSON contents and display them as needed.

With the SQL Editor, Datameer can extract the following:

JSON attribute(s) that has/ have any predefined data type in the JSON expression without using a specific function
JSON object(s)
VARIANT data by using the TRY_PARSE_JSON function

Using JSON in the Formula Builder

You can create a wide variety of formulas using Workbench’s Formula Builder. The editor field is above the source column preview when you first open the Formula Builder. There are two tabs on the right where the functions can be set up:

The ‘Column’ tab – allows you to change the columns’ visibility so that only the necessary columns are displayed for the function you want to use.
The ‘Function’ tab – allows you to use a search bar or a category to browse all available functions.

JSON attributes can also be extracted using the Formula Builder’s function “JSON EXTRACT PATH TEXT.”

Wrap-Up / Summary

Without needing specialized technical knowledge or subject matter expertise, Datameer will let you work with JSON data and other data types to create insights using data from various sources.

Business teams can self-serve and create their datasets inside of Snowflake using Datameer’s no-code visual interface, even if they have no experience with SQL coding.

Data engineers can use a more conventional SQL editor to extract JSON attributes from JSON objects.

This helps to create a democratized approach to collaborative data modeling and transformation.

What are you waiting for?

Jump right in and explore Datameer for yourself.

References;

https://docs.snowflake.com/en/sql-reference/functions/parse_json.html

https://docs.fileformat.com/web/json/

https://datameer.atlassian.net/wiki/spaces/DAS70/pages/31963974149/Using+the+Formula+Builder

https://documentation.datameer.com/datameer/exploring_JSON