Icy IO: loading local files with Snowflake

blog
snowflake
io
productivity
Author

Phillip Cloud

Published

August 31, 2023

Loading files without Ibis

It can be challenging to load local files into Snowflake from Python.

Here’s how to load a CSV file into Snowflake without Ibis.

CREATE TEMP STAGE load_csv_stage;

CREATE TEMP FILE FORMAT load_csv_format
TYPE = CSV PARSE_HEADER = TRUE;

PUT 'file:///path/to/my.csv' @load_csv_stage;

CREATE TEMP TABLE my_table
USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(
        INFER_SCHEMA(
            LOCATION => '@load_csv_stage',
            FILE_FORMAT => 'load_csv_format'
        )
    )
);

COPY INTO my_table
FROM @load_csv_stage
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);
1
Creates a temporary stage in Snowflake. Stages are locations in Snowflake that hold files. They can be used to store raw files to load into tables. TEMP stages are only accessible to the current session and will be dropped when the session ends.
2
A file format is a set of instructions for how to interpret a file. File formats are where you specify parsing and some loading options for your files.
3
PUT copies a file or glob pattern matching one or more files to a stage.
4
Creates a temporary table with schema inferred using Snowflake’s INFER_SCHEMA table function.
5
USING TEMPLATE is a Snowflake-specific syntax that allows you to specify a set of column definitions computed from staged files.
6
INFER_SCHEMA is a powerful feature of Snowflake that allows you to load files without having to compute the schema in client code.
7
COPY INTO loads the staged data into the created temporary table.

Snowflake provides the full set of primitives required to achieve this, but composing them together can be challenging. Some users struggle to remember the sequence of steps.

Loading files with Ibis

Let’s take a look at how ibis turns the above process into a single line of Python.

First, we connect to snowflake:

import os
from ibis.interactive import *

con = ibis.connect(os.environ["SNOWFLAKE_URL"])

read_csv

Loading CSV files is now a single line of familiar Python code:

diamonds = con.read_csv("diamonds.csv")
diamonds
┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ carat         ┃ cut       ┃ color  ┃ clarity ┃ depth         ┃ table         ┃ price ┃ x             ┃ y             ┃ z             ┃
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ decimal(3, 2) │ string    │ string │ string  │ decimal(3, 1) │ decimal(3, 1) │ int64 │ decimal(4, 2) │ decimal(4, 2) │ decimal(4, 2) │
├───────────────┼───────────┼────────┼─────────┼───────────────┼───────────────┼───────┼───────────────┼───────────────┼───────────────┤
│          0.23 │ Ideal     │ E      │ SI2     │          61.5 │          55.0 │   326 │          3.95 │          3.98 │          2.43 │
│          0.21 │ Premium   │ E      │ SI1     │          59.8 │          61.0 │   326 │          3.89 │          3.84 │          2.31 │
│          0.23 │ Good      │ E      │ VS1     │          56.9 │          65.0 │   327 │          4.05 │          4.07 │          2.31 │
│          0.29 │ Premium   │ I      │ VS2     │          62.4 │          58.0 │   334 │          4.20 │          4.23 │          2.63 │
│          0.31 │ Good      │ J      │ SI2     │          63.3 │          58.0 │   335 │          4.34 │          4.35 │          2.75 │
│          0.24 │ Very Good │ J      │ VVS2    │          62.8 │          57.0 │   336 │          3.94 │          3.96 │          2.48 │
│          0.24 │ Very Good │ I      │ VVS1    │          62.3 │          57.0 │   336 │          3.95 │          3.98 │          2.47 │
│          0.26 │ Very Good │ H      │ SI1     │          61.9 │          55.0 │   337 │          4.07 │          4.11 │          2.53 │
│          0.22 │ Fair      │ E      │ VS2     │          65.1 │          61.0 │   337 │          3.87 │          3.78 │          2.49 │
│          0.23 │ Very Good │ H      │ VS1     │          59.4 │          61.0 │   338 │          4.00 │          4.05 │          2.39 │
│             … │ …         │ …      │ …       │             … │             … │     … │             … │             … │             … │
└───────────────┴───────────┴────────┴─────────┴───────────────┴───────────────┴───────┴───────────────┴───────────────┴───────────────┘

read_parquet

Similarly, loading Parquet files is now a single line of code:

diamonds = con.read_parquet("diamonds.parquet")
diamonds
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┓
┃ carat   ┃ cut       ┃ color  ┃ clarity ┃ depth   ┃ table   ┃ price ┃ x       ┃ y       ┃ z       ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━┩
│ float64 │ string    │ string │ string  │ float64 │ float64 │ int64 │ float64 │ float64 │ float64 │
├─────────┼───────────┼────────┼─────────┼─────────┼─────────┼───────┼─────────┼─────────┼─────────┤
│    0.23 │ Ideal     │ E      │ SI2     │    61.5 │    55.0 │   326 │    3.95 │    3.98 │    2.43 │
│    0.21 │ Premium   │ E      │ SI1     │    59.8 │    61.0 │   326 │    3.89 │    3.84 │    2.31 │
│    0.23 │ Good      │ E      │ VS1     │    56.9 │    65.0 │   327 │    4.05 │    4.07 │    2.31 │
│    0.29 │ Premium   │ I      │ VS2     │    62.4 │    58.0 │   334 │    4.20 │    4.23 │    2.63 │
│    0.31 │ Good      │ J      │ SI2     │    63.3 │    58.0 │   335 │    4.34 │    4.35 │    2.75 │
│    0.24 │ Very Good │ J      │ VVS2    │    62.8 │    57.0 │   336 │    3.94 │    3.96 │    2.48 │
│    0.24 │ Very Good │ I      │ VVS1    │    62.3 │    57.0 │   336 │    3.95 │    3.98 │    2.47 │
│    0.26 │ Very Good │ H      │ SI1     │    61.9 │    55.0 │   337 │    4.07 │    4.11 │    2.53 │
│    0.22 │ Fair      │ E      │ VS2     │    65.1 │    61.0 │   337 │    3.87 │    3.78 │    2.49 │
│    0.23 │ Very Good │ H      │ VS1     │    59.4 │    61.0 │   338 │    4.00 │    4.05 │    2.39 │
│       … │ …         │ …      │ …       │       … │       … │     … │       … │       … │       … │
└─────────┴───────────┴────────┴─────────┴─────────┴─────────┴───────┴─────────┴─────────┴─────────┘

read_json

Lastly, loading JSON files is now – surprise 🥳 – a single line of code!

Line delimited JSON is supported:

diamonds = con.read_json("diamonds.ndjson")
diamonds
┏━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ carat         ┃ clarity ┃ color  ┃ cut       ┃ depth         ┃ price ┃ table         ┃ x             ┃ y             ┃ z             ┃
┡━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ decimal(3, 2) │ string  │ string │ string    │ decimal(3, 1) │ int64 │ decimal(3, 1) │ decimal(4, 2) │ decimal(4, 2) │ decimal(4, 2) │
├───────────────┼─────────┼────────┼───────────┼───────────────┼───────┼───────────────┼───────────────┼───────────────┼───────────────┤
│          0.23 │ SI2     │ E      │ Ideal     │          61.5 │   326 │          55.0 │          3.95 │          3.98 │          2.43 │
│          0.21 │ SI1     │ E      │ Premium   │          59.8 │   326 │          61.0 │          3.89 │          3.84 │          2.31 │
│          0.23 │ VS1     │ E      │ Good      │          56.9 │   327 │          65.0 │          4.05 │          4.07 │          2.31 │
│          0.29 │ VS2     │ I      │ Premium   │          62.4 │   334 │          58.0 │          4.20 │          4.23 │          2.63 │
│          0.31 │ SI2     │ J      │ Good      │          63.3 │   335 │          58.0 │          4.34 │          4.35 │          2.75 │
│          0.24 │ VVS2    │ J      │ Very Good │          62.8 │   336 │          57.0 │          3.94 │          3.96 │          2.48 │
│          0.24 │ VVS1    │ I      │ Very Good │          62.3 │   336 │          57.0 │          3.95 │          3.98 │          2.47 │
│          0.26 │ SI1     │ H      │ Very Good │          61.9 │   337 │          55.0 │          4.07 │          4.11 │          2.53 │
│          0.22 │ VS2     │ E      │ Fair      │          65.1 │   337 │          61.0 │          3.87 │          3.78 │          2.49 │
│          0.23 │ VS1     │ H      │ Very Good │          59.4 │   338 │          61.0 │          4.00 │          4.05 │          2.39 │
│             … │ …       │ …      │ …         │             … │     … │             … │             … │             … │             … │
└───────────────┴─────────┴────────┴───────────┴───────────────┴───────┴───────────────┴───────────────┴───────────────┴───────────────┘

As well as strict JSON arrays of objects:

diamonds = con.read_json("diamonds.json")
diamonds
┏━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ carat         ┃ clarity ┃ color  ┃ cut       ┃ depth         ┃ price ┃ table         ┃ x             ┃ y             ┃ z             ┃
┡━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ decimal(3, 2) │ string  │ string │ string    │ decimal(3, 1) │ int64 │ decimal(3, 1) │ decimal(4, 2) │ decimal(4, 2) │ decimal(4, 2) │
├───────────────┼─────────┼────────┼───────────┼───────────────┼───────┼───────────────┼───────────────┼───────────────┼───────────────┤
│          0.23 │ SI2     │ E      │ Ideal     │          61.5 │   326 │          55.0 │          3.95 │          3.98 │          2.43 │
│          0.21 │ SI1     │ E      │ Premium   │          59.8 │   326 │          61.0 │          3.89 │          3.84 │          2.31 │
│          0.23 │ VS1     │ E      │ Good      │          56.9 │   327 │          65.0 │          4.05 │          4.07 │          2.31 │
│          0.29 │ VS2     │ I      │ Premium   │          62.4 │   334 │          58.0 │          4.20 │          4.23 │          2.63 │
│          0.31 │ SI2     │ J      │ Good      │          63.3 │   335 │          58.0 │          4.34 │          4.35 │          2.75 │
│          0.24 │ VVS2    │ J      │ Very Good │          62.8 │   336 │          57.0 │          3.94 │          3.96 │          2.48 │
│          0.24 │ VVS1    │ I      │ Very Good │          62.3 │   336 │          57.0 │          3.95 │          3.98 │          2.47 │
│          0.26 │ SI1     │ H      │ Very Good │          61.9 │   337 │          55.0 │          4.07 │          4.11 │          2.53 │
│          0.22 │ VS2     │ E      │ Fair      │          65.1 │   337 │          61.0 │          3.87 │          3.78 │          2.49 │
│          0.23 │ VS1     │ H      │ Very Good │          59.4 │   338 │          61.0 │          4.00 │          4.05 │          2.39 │
│             … │ …       │ …      │ …         │             … │     … │             … │             … │             … │             … │
└───────────────┴─────────┴────────┴───────────┴───────────────┴───────┴───────────────┴───────────────┴───────────────┴───────────────┘

Conclusion

Ibis 7.0.0 adds support for read_csv, read_parquet and read_json to the Snowflake backend.

We think you’ll enjoy the increase in productivity these new features bring to the Snowflake backend and we’d love to hear your feedback!

Back to top