Introduction
Snowflake’s definition on their website is as follows: “A single, fully managed platform that powers the AI Data Cloud. Snowflake securely connects businesses globally across any type or scale of data to productize AI, applications, and more in the enterprise.”
For a more in-depth introduction, you can read this blog by my fellow colleague.
Structured and unstructured (via link fetching) data is supported in Snowflake, although there exists data that lies in neither category – hence, we introduce the semi-structured data category.
We use semi-structured data when we:
- Do not know what read patterns to expect
- When we have metadata, we do not search for into but rather present it as a whole
- When we do not want to enforce a schema upon a user
Snowflake supports two semi-structured data types: variant and array. Let’s get to know them.
A VARIANT value can hold a value of any type. Ideal for storing JSON, Avro, ORC, or others since we do not need to define the hierarchical structure of the data.
Due to performance implications, we should avoid using the VARIANT if our data has numbers in the strings or dates.
The ARRAY data type compliments VARIANTs since it holds only these types. Snowflake’s array concept is the same as that of other programming languages, holding multiple data points in one place.
This type is helpful when we have variants that have similar or identical structures. And we would process the array elements in the same way.
An important thing to know when using the VARIANT or ARRAY data type is the maximum storage size of 16 MB. In the case we know the ARRAY or VARIANT will exceed the maximum size it is best to convert them into columns and connect them using a relational link.
A natural question that arises is why shouldn’t we simply store our JSON as serialized strings? The main point lies in query optimization; if we are querying and/or filtering our data based on keys stored in the VARIANT type, we will have nearly identical performance as if we are querying based upon a column. Furthermore, it allows us to directly manipulate the data stored inside it.
We can create a Snowflake table with an autoincrement ID, a field metadata of VARIANT data types, and an array field that describes the metadata further.
CREATE OR REPLACE TABLE GENERIC (
ID NUMBER AUTOINCREMENT START 1 INCREMENT 1,
METADATA VARIANT
METADATA_TAGS ARRAY
)
We will use Spring Boot to demonstrate how to store data in our newly created table. Suppose we receive the data through a REST client, and the object we receive can be represented as this:
public record Generic(
@NotBlank long id,
@NotBlank String metadata,
@NotBlank String metadataTags,
) {
@JsonCreator
public Generic(
@JsonProperty("id") final long id,
@JsonProperty("metadata") final String metadata,
@JsonProperty("metadataTags") final String metadataTags
) {
this.id = id;
this.metadata = metadata;
this.metadataTags = metadataTags;
}
}
JDBI is a higher-level SQL convenience layer built on top of JDBC. It aims to abstract much of the boilerplate present in JDBC by automating connection management and automatic object mapping query results for Java Objects. More can be seen in a Five Minute Introduction on the official site.
To separate concerns, we will create a GenericEntity, which will be used by the JDBI. Furthermore, the toDomain function is handy when transforming the entity into the model.
public class GenericEntity {
private final long id;
private final String metadata;
private final String metadataTags;
@JdbiConstructor
public GenericEntity(
final long id,
final String metadata,
final String metadataTags
) {
this.id = id;
this.metadata = metadata;
this.metadataTags = metadataTags;
}
public Generic toDomain() {
return new Generic(
id,
metadata,
metadataTags
);
// getters omitted
}
}
Now, we will need to create a repository that will have a JDBI repository injected into it. The JDBI repository will hold the actual Snowflake SQL statements, which will be called from the parent repository.
@Component
public class GenericRepository {
@Autowired
private JdbiGenericRepository jdbiGenericRepository;
@Override
public void saveGenerics(final List<Generic> generics) {
final List<GenericEntity> genericEntities = generics.stream()
.map(GenericEntity::new)
.collect(Collectors.toList());
jdbiGenericRepository.insertGenerics(genericEntities);
}
}
With the stream() function, we map from Generic to GenericEntity. Afterward, we call the JDBI Generic Repository to perform the actual inserts.
@RegisterConstructorMapper(GenericEntity.class)
public interface JdbiGenericRepository {
@SqlBatch("""
INSERT INTO GENERIC
(
id,
METADATA,
METADATA_TAGS,
)
SELECT
:id,
parse_json(:metadata),
parse_json(:metadataTags),
""")
void insertGenerics(@BindBean List<GenericEntity> generics);
}
The `parse_json` function indicates to Snowflake that the string we are passing in is of JSON format, so it knows to process like so.
This concludes the basic setup of inserting semi-structured data, and the reader can follow the outline in this Tech Bite to extend the functionality.
References:
Semi-structured data types | Snowflake Documentation
Considerations for Semi-structured Data Stored in VARIANT | Snowflake Documentation
“Storing semi-structured data in Snowflake” Tech Bite was brought to you by Muhamed Hamzić, Software Engineer at Atlantbh.
Tech Bites are tips, tricks, snippets or explanations about various programming technologies and paradigms, which can help engineers with their everyday job.
