Tabular NoSQL data stores

Introduction to NoSQL

Jake Roach

Data Engineer

Tabular data stores

Empty row-oriented table.

Empty column-oriented table.

Column-oriented databases:

Store data in columns, rather than rows
Allow for selective column read and retrieval
Easier schema changes
Better data compression, query performance

Querying a column-oriented database

Books table in a Snowflake database.

Books table highlighted with filtered values.

SELECT
    title,
    price
FROM books
WHERE price < 50.00;

SQL-like syntax
Column-elimination and selective reads/retrieval
Automatic data clustering

Query execution in column-oriented data stores

SELECT
    title,
    price
FROM books
WHERE price < 50.00;

This query executes by:

Accessing price column, identify records with price < 50.00
Retrieving corresponding values from title column

Execution of query against books table in a Snowflake database.

Later, we'll look at:

Optimizing data loads and deletes
Creating performant JOINs
Working with semi-structure data

Connecting to a Snowflake database

import snowflake.connector

conn = snowflake.connector.connect(
  user="<user>",
  password="<password>",
  account="<account_identifier>",
  database="<database_name>",
  schema="<schema_name>",
  warehouse="<warehouse_name>"
)

The conn variable will be created for you, pre-exercise

Writing and executing Snowflake queries

# Build a query in a string (or multi-line string)
query = """
SELECT
    title,
    price
FROM books
WHERE price < 50.00;
"""

# Execute the query, print the results
results = conn.cursor().execute(query).fetch_pandas_all()
print(results)

Let's practice!

Introduction to NoSQL