{"id":110697,"date":"2024-01-09T09:36:12","date_gmt":"2024-01-09T17:36:12","guid":{"rendered":"https:\/\/www.backblaze.com\/blog\/?p=110697"},"modified":"2024-08-14T11:31:03","modified_gmt":"2024-08-14T18:31:03","slug":"data-driven-decisions-wwith-snowflake-and-backblaze-b2","status":"publish","type":"post","link":"https:\/\/www.backblaze.com\/blog\/data-driven-decisions-wwith-snowflake-and-backblaze-b2\/","title":{"rendered":"Data-Driven Decisions With Snowflake and Backblaze B2"},"content":{"rendered":"\n
\"A<\/figure>\n\n\n\n
<\/div>\n\n\n\n

Since its launch in 2014 as a cloud-based data warehouse, Snowflake<\/a> has evolved into a broad data-as-a-service platform addressing a wide variety of use cases, including artificial intelligence (AI), machine learning (ML), collaboration across organizations, and data lakes. Last year, Snowflake introduced support for S3 compatible cloud object stores<\/a>, such as Backblaze B2 Cloud Storage<\/a>. Now, Snowflake customers can access unstructured data such as images and videos, as well as structured and semi-structured data such as CSV, JSON, Parquet, and XML files, directly in the Snowflake Platform, served up from Backblaze B2.<\/p>\n\n\n\n

Why access external data from Snowflake, when Snowflake is itself a data as a service (DaaS) platform with a cloud-based relational database at its core? To put it simply, not all data belongs in Snowflake. Organizations use cloud object storage solutions such as Backblaze B2 as a cost-effective way to maintain both master and archive data, with multiple applications reading and writing that data. In this situation, Snowflake is just another consumer of the data. Besides, data storage in Snowflake is much more expensive than in Backblaze B2, raising the possibility of significant cost savings as a result of optimizing your data\u2019s storage location.<\/p>\n\n\n\n

Snowflake Basics<\/h2>\n\n\n\n

At Snowflake\u2019s core is a cloud-based relational database. You can create tables, load data into them, and run SQL queries just as you can with a traditional on-premises database. Given Snowflake\u2019s origin as a data warehouse, it is currently better suited to running analytical queries against large datasets than as an operational database serving a high volume of transactions, but Snowflake Unistore<\/a>\u2019s hybrid tables feature (currently in private preview) aims to bridge the gap between transactional and analytical workloads.<\/p>\n\n\n\n

As a DaaS platform, Snowflake runs on your choice of public cloud\u2014currently Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform\u2014but insulates you from the details of managing storage, compute, and networking infrastructure. Having said that, sometimes you need to step outside the Snowflake box to access data that you are managing in your own cloud object storage account. I\u2019ll explain exactly how that works in this blog post, but, first, let\u2019s take a quick look at how we classify data according to its degree of structure, as this can have a big impact on your decision of where to store it.<\/p>\n\n\n\n

Structured and Semi-Structured Data<\/h2>\n\n\n\n

Structured data conforms to a rigid data model. Relational database tables are the most familiar example\u2014a table\u2019s schema describes required and optional fields and their data types, and it is not possible to insert rows into the table that contain additional fields not listed in the schema. Aside from relational databases, file formats such as Apache Parquet<\/a>, Optimized Row Columnar<\/a> (ORC), and Avro<\/a> can all store structured data; each file format specifies a schema that fully describes the data stored within a file. Here\u2019s an example of a schema for a Parquet file:<\/p>\n\n\n\n

% parquet meta customer.parquet\n\nFile path:  \/data\/customer.parquet\n...\nSchema:\nmessage hive_schema {\n  required int64 custkey;\n  required binary name (STRING);\n  required binary address (STRING);\n  required int64 nationkey;\n  required binary phone (STRING);\n  required int64 acctbal;\n  optional binary mktsegment (STRING);\n  optional binary comment (STRING);\n}\n<\/pre>\n\n\n\n
Semi-structured data, as its name suggests, is more flexible. File formats such as CSV, XML and JSON need not use a formal schema, since they can be self-describing. That is, an application can infer the structure of the data as it reads the file, a mechanism often termed \u201cschema-on-read.\u201d <\/p>\n\n\n\n
This simple JSON example illustrates the principle. You can see how it\u2019s possible for an application to build the schema of a product record as it reads the file:<\/p>\n\n\n\n
{\n  \"products\" : [\n    {\n      \"name\" : \"Paper Shredder\",\n      \"description\" : \"Crosscut shredder with auto-feed\"\n    },\n    {\n      \"name\" : \"Stapler\",\n      \"color\" : \"Red\"\n    },\n    {\n      \"name\" : \"Sneakers\",\n      \"size\" : \"11\"\n    }\n  ]\n}\n<\/pre>\n\n\n\n
Accessing Structured and Semi-Structured Data Stored in Backblaze B2 from Snowflake<\/h2>\n\n\n\n
You can access data located in cloud object storage external to Snowflake, such as Backblaze B2, by creating an external stage<\/a>. The external stage is a Snowflake database object that holds a URL for the external location, as well as configuration (e.g., credentials) required to access the data. For example:<\/p>\n\n\n\n
CREATE STAGE b2_stage\n  URL = 's3compat:\/\/your-b2-bucket-name\/'\n  ENDPOINT = 's3.your-region.backblazeb2.com'\n  REGION = 'your-region'\n  CREDENTIALS = (\n    AWS_KEY_ID = 'your-application-key-id'\n    AWS_SECRET_KEY = 'your-application-key'\n  );\n<\/pre>\n\n\n\n
You can create an external table<\/a> to query data stored in an external stage as if the data were inside a table in Snowflake, specifying the table\u2019s columns as well as filenames, file formats, and data partitioning. Just like the external stage, the external table is a database object, located in a Snowflake schema, that stores the metadata required to access data stored externally to Snowflake, rather than the data itself.<\/p>\n\n\n\n
Every external table automatically contains a single VARIANT<\/a> type column, named value<\/code>, that can hold arbitrary collections of fields. An external table definition for semi-structured data needs no further column definitions, only metadata such as the location of the data. For example:<\/p>\n\n\n\n
CREATE EXTERNAL TABLE product\n  LOCATION = @b2_stage\/data\/\n  FILE_FORMAT = (TYPE = JSON)\n  AUTO_REFRESH = false;\n<\/pre>\n\n\n\n
When you query the external table, you can reference elements within the value column, like this:<\/p>\n\n\n\n
SELECT value:name\n  FROM product\n  WHERE value:color = \u2018Red\u2019;\n+------------+\n| VALUE:NAME |\n|------------|\n| \"Stapler\"  |\n+------------+\n<\/pre>\n\n\n\n
Since structured data has a more rigid layout, you must define table columns (technically, in Snowflake, these are referred to as \u201cpseudocolumns\u201d), corresponding to the fields in the data files, in terms of the value column. For example:<\/p>\n\n\n\n
CREATE EXTERNAL TABLE customer (\n    custkey number AS (value:custkey::number),\n    name varchar AS (value:name::varchar),\n    address varchar AS (value:address::varchar),\n    nationkey number AS (value:nationkey::number),\n    phone varchar AS (value:phone::varchar),\n    acctbal number AS (value:acctbal::number),\n    mktsegment varchar AS (value:mktsegment::varchar),\n    comment varchar AS (value:comment::varchar)\n  )\n  LOCATION = @b2_stage\/data\/\n  FILE_FORMAT = (TYPE = PARQUET)\n  AUTO_REFRESH = false;\n<\/pre>\n\n\n\n
Once you\u2019ve created the external table, you can write SQL statements to query the data stored externally, just as if it were inside a table in Snowflake:<\/p>\n\n\n\n
SELECT phone\n  FROM customer\n  WHERE name = \u2018Acme, Inc.\u2019;\n+----------------+\n| PHONE          |\n|----------------|\n| \"111-222-3333\" |\n+----------------+\n<\/pre>\n\n\n\n
The Backblaze B2 documentation includes a pair of technical articles that go further into the details, describing how to export data from Snowflake to an external table stored in Backblaze B2<\/a>, and how to create an external table definition for existing structured data stored in Backblaze B2<\/a>.<\/p>\n\n\n\n
Accessing Unstructured Data Stored in Backblaze B2 from Snowflake<\/h2>\n\n\n\n
The term \u201cunstructured\u201d, in this context, refers to data such as images, audio, and video, that cannot be defined in terms of a data model. You still need to create an external stage to access unstructured data located outside of Snowflake, but, rather than creating external tables and writing SQL queries, you typically access unstructured data from custom code running in Snowflake\u2019s Snowpark<\/a> environment.<\/p>\n\n\n\n
Here\u2019s an excerpt from a Snowflake user-defined function, written in Python, that loads an image file from an external stage:<\/p>\n\n\n\n
from snowflake.snowpark.files import SnowflakeFile\n\n# The file_path argument is a scoped Snowflake file URL to a file in the \n# external stage, created with the BUILD_SCOPED_FILE_URL function. \n# It has the form\n# https:\/\/abc12345.snowflakecomputing.com\/api\/files\/01b1690e-0001-f66c-...\ndef generate_image_label(file_path):\n\n  # Read the image file \n  with SnowflakeFile.open(file_path, 'rb') as f:\n    image_bytes = f.readall()\n\n  ...\n<\/pre>\n\n\n\n
In this example, the user-defined function reads an image file from an external stage, then runs an ML model on the image data to generate a label for the image according to its content. A Snowflake task using this user-defined function can insert rows into a table of image names and labels as image files are uploaded into a Backblaze B2 Bucket. You can learn more about this use case in particular, and loading unstructured data from Backblaze B2 into Snowflake in general, from the Backblaze Tech Day \u201823 session that I co-presented with Snowflake Product Manager Saurin Shah:<\/p>\n\n\n\n
\n