Once upon a time in the digital realm, databases decided to get into the New Year spirit. Leading the charge, Postgres had a brilliant idea - to send his friend Snowflake some delicious homemade cookies. After all, what's a New Year without some sweet treats?
Postgres bakes some cookies as presents
So, Postgres set to work in his virtual kitchen, crafting tree-shaped cookies of various sizes. But where would you place a cookie in a database world? On a table, of course! Postgres ended up with a table that was overflowing with cookies. It was a sight to behold, rows upon rows of chocolate-chip, oatmeal raisin, and many more flavors!
Here goes the magic recipe that Postgres used to bake cookies:
-- Table to place cookies
CREATE TABLE cookies (
id SERIAL PRIMARY KEY,
cookie TEXT
);
-- Function to bake cookies
CREATE OR REPLACE FUNCTION generate_cookie(width INT, height INT) RETURNS TEXT AS $$
DECLARE
tree TEXT := '';
i INT;
BEGIN
IF width < 3 OR height < 3 THEN
RETURN 'Invalid dimensions';
END IF;
FOR i IN 1..height LOOP
tree := tree || lpad(repeat('*', 2 * i - 1), i + width - 1) || E'\n';
END LOOP;
tree := tree || rpad('|', width, ' ');
RETURN tree;
END;
$$ LANGUAGE plpgsql;
-- Baking cookies and placing them on the table
INSERT INTO cookies(cookie) SELECT generate_series(10,10);
INSERT INTO cookies(cookie) SELECT generate_series(6,6);
INSERT INTO cookies(cookie) SELECT generate_series(12,7);
...
...
...
Here is how some of the cookies looked, aren’t they delicious!
Postgres was proud of his cookie table, and he couldn't wait to send them to Snowflake, his database buddy. But how do you send cookies to a friend who speaks SQL?
Now, here comes the fun part. If humans send holiday cards to their friends, it's usually written in English. But databases have their own language, SQL. That's where the adventure really began.
Presents In Transit
Postgres knew he needed the help of a magical tool called PeerDB, which, as it turns out, also spoke SQL. First, he had to register the two peer databases in PeerDB. Here's what he did:
CREATE PEER good_friend_pg WITH POSTGRES (
host = '<postgres_host>',
port = '<port>',
-- Add other connection details here
);
CREATE PEER good_friend_sf WITH SNOWFLAKE (
account_id = '<account_id>',
private_key = '<private_key>,
-- Add Snowflake connection details here
);
Now that Postgres had introduced friends to PeerDB, he was ready to send the cookies using PeerDB's Mirror feature. He created a mirror named holiday_gift from good_friend_pg to good_friend_sf:
CREATE MIRROR holiday_gift
FROM good_friend_pg TO good_friend_sf
WITH TABLE MAPPING (public.cookie:public.cookie)
WITH (
do_initial_copy = true
);
-- for fun lets ingest more cookies into the table
INSERT INTO cookies(cookie)
SELECT generate_cookie(i, i) FROM generate_series(5, 100) i;
-- continue to execute the above query once every 2 seconds
\watch 2
Snowflake Gets Presents
The cookies were on their way to Snowflake, and Postgres couldn't contain his excitement. He imagined Snowflake's reaction when he received rows and rows of data that turned out to be delicious cookies. Let’s take a peek on Snowflake!
And so, in the world of databases, where holiday gifts could be just as sweet as SQL queries, the celebration continued with a heartwarming twist.
Happy New Year 🎉, everyone, from the PeerDB team! Hope you enjoyed reading this blog. If you also wanted to send some presents from Postgres to Snowflake using PeerDB: 🎁 🥳