Populating column-oriented databases

Introduction to NoSQL

Jake Roach

Data Engineer

Populating row-oriented vs. column-oriented databases

Row-oriented:

  • Optimized for transactional use-cases
  • Best performance when inserting, updating or deleting individual records

Column-oriented:

  • Use for analytics workflows
  • Perform well when loading, updating, or deleting data in-bulk

Analytical database being updated daily.

Introduction to NoSQL

CREATE TABLE

Image of a Snowflake data warehouse, and the nested storage objects.

CREATE TABLE books (
    title VARCHAR(100),
    author VARCHAR(100),
    price FLOAT
);
Introduction to NoSQL

COPY INTO

COPY INTO books
FROM 'file://data_science_books.csv'
FILE_FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    SKIP_HEADER = 1
);

COPY INTO

FROM

  • Cloud storage location
  • URL
  • Staged files

FILE_FORMAT

  • Type of file, delimiter, other metadata information
1 https://docs.snowflake.com/en/sql-reference/sql/copy-into-table
Introduction to NoSQL

CREATE TABLE ... AS

CREATE TABLE premium_books AS
SELECT *
FROM books
WHERE price > 50.00;

CREATE OR REPLACE TABLE premium_books AS
SELECT *
FROM books
WHERE price > 50.00;

CREATE TABLE ... AS

  • Provide a table name
  • Creates table in the current schema

SELECT ...

  • Populates table with data returned by query

OR REPLACE

  • If there is an existing table, it is replaced by new table
1 https://docs.snowflake.com/en/sql-reference/sql/create-table
Introduction to NoSQL

Let's practice!

Introduction to NoSQL

Preparing Video For Download...