CODEWITHSUNDEEP
binarysnowflake-cloud-data-platform
Nagendra
Nagendra

Reputation: 211

Store Photos(JPG) in snowflake Binary data Type

what are the steps to store the photos (size <1MB) in snowflake binary data type?

Upvotes: 1

Views: 1843

Answers (1)

Nat Taylor
Nat Taylor

Reputation: 1108

You will need to encode your images into HEX or BASE64 and then load them into a BINARY column. See https://docs.snowflake.com/en/user-guide/binary-input-output.html for more details. As others have mentioned, this isn't a great fit for Snowflake and you may want to consider just storing references to the images (paths/urls) and storing them elsewhere.

Here is one way to store an image using the Python connector.

import snowflake.connector
import base64
import requests


# Assumes create or replace TABLE IMAGE (C1 BINARY);

conn = snowflake.connector.connect(**your_params)

sql = f"""insert into image values (to_binary('{base64.b64encode(requests.get('https://www.google.com/s2/favicons?domain=google.com').content).decode('ascii')}', 'BASE64'));"""
conn.cursor().execute(sql)

# Optionally, to display the image
import io
from PIL import Image
im = conn.cursor().execute("select * from image").fetchall()
with io.BytesIO(im[0][0]) as f:
    display(Image.open(f))

Upvotes: 3

Related Questions