Complex Data Types in SQL 2 – Benefits of SQL Support

Posted December 19, 2019 by Waqas Dhillon, Product Manager - Machine Learning, Vertica

Cubes with binary ones and zeroes on them clustered together floating in blue space with light shining out

Co-authored by James Clampffer, Deepak Majeti.

In this second post in the Complex Types blog series, let’s look at some of the reasons complex types are used extensively in organized big data formats like ORC and Parquet.
Be sure to read the first post in this series where we discussed the definition of complex types, and explored the various complex types in detail.

In the next post, we’ll dive into how Vertica allows you to import, analyze or export data in complex types.

There are several advantages to complex types:

1. Express Analyses More Naturally
Complex types simplify the expression of analysis logic thereby simplifying the data pipelines. For example, in order to list all the customers with more than 1000 website events, we will write this simple query.

create table SQL

Writing the same code in SQL would look something like the script below which is not very intuitive.

First, we will need to create four tables: customers, phone_numbers, web_events, and http_events.

Create table SQL without complex types

Then the query would be something like:

Query on tables without complex types - a lot more SQL code

We not only have to write additional code but also needed to use multiple joins on the tables to get the desired results. For queries involving more tables, the code can get complex quickly and take the focus away from analysis.  Using complex types, helps avoid this issue by having a natural flow to the analysis.Query on tables without complex types

2. Alternative for Costly Joins

Complex types can be used to replace expensive joins, as shown above. This can have an impact on performance in more than one way. As a complex type value is not distributed across nodes this avoids expensive inter-node data movement. In the above example, if the query had to be run only once, it may not be much of an issue. But if the query needs to be run multiple times, especially on data that is not changing very frequently, doing a join every time you run the query would be overkill.

Complex types like Arrays are very effective at representing a set. Using these data structures allows the users to store the data in a set on a single node thereby avoiding the need to fetch data from different nodes for analysis.

Below you can see how using complex types eliminates the need for three joins.

Diagram of 3 joins necessary in SQL without complex types

Using complex data types avoids all those costly joins:

Diagram showing no need for joins to get same data with complex types

3. Allow Unstructured Data

Maps are unstructured by nature and therefore they allow unstructured data to be represented along with structured data. Maps are also a good choice for sparse data which can be better represented using maps instead of normal data types.

The ways to approximate a map in plain SQL aren’t great.

Two common approaches are:

Approach 1: Add many key1, val1 … keyN, valN columns to store key-value pairs.  Null out unused columns.  Limited by table width.

Approach 2: Create a table where the “map” has a unique ID that stores a single key and value in each row e.g. TABLE(map_id int not null, key<typename>, val <typename>).  Something like map_count would require a groupby on map_id.  You still can’t intuitively represent nested maps

Common use case: Apps that push their data to a centralized server might have optional fields that are named slightly differently over time, perhaps to indicate minor semantic changes.  Then a second ETL pass can be done to normalize similar values to the same thing e.g. maybe the app started with millisecond timings and new versions use microseconds, so everything gets adjusted to use the same units.  This allows the ETL to be done directly inside Vertica.

Vertica has flex tables to help handle semi structured and unstructured data. However, complex data types are expected to provide much better performance for many use cases. The reason is that flex tables are totally unstructured whereas in this case you have a schema associated. For the above use case, we have a map of string to string. Therefore, in this case, complex types are going to be way faster than the flex tables.

Advantage of Map type

4. Supported by Popular Open Source File Formats

Apache Parquet and Apache ORC are two of the most popular columnar file formats used for analytics today. Both these formats support complex data types to varying degree. Apache Parquet supports StructMapList while Apache ORC support StructMapList and Union. These two formats also differ significantly in the internal representation of complex type data.

 

Limitations in Other Analytical Tools

While some analytical tools like Hive, Presto and Impala currently support complex types, these tools have significant limitations for complex types. Some of the major limitations that we have observed are:

  • Materialize very early at the scan step
  • Do not use columnar execution
  • Lack literal support
  • Support limited file formats
  • Lack ability to re-use complex types defined

The support for complex data types is not very mature in most of the tools. An additional shortcoming that’s common across these tools is that they still run out of memory or processing.

In the next post in this series, we’ll discuss what complex types are supported in Vertica, and how they are used.

 
Related posts:
Complex Data Types in SQL 1 – What are They?
Quick Tip: Exporting Data to Parquet
Vertica and Amazon – Better Together, for Years
Announcing Vertica Version 9.3 – Ride the Winds of Change