iglesiasd

Not all of the new features had a big announcement at Summit 2023, but that doesn’t mean we can’t highlight them!

This week we have a riddle for you to solve, and without spoiling everything, let’s see if you can solve the riddle from the initial code:

Start code

--Create the database and schema
CREATE DATABASE F_F_WEEK_54;
CREATE SCHEMA WEEK_54;

--Create table
CREATE TABLE table_a (
id INT,
name VARCHAR,
age INT
);

CREATE TABLE table_b (
id INT,
name VARCHAR,
age INT
);

--Create date
INSERT INTO table_a (id, name, age)
VALUES
(1, 'John', 25),
(2, 'Mary', 30),
(3, 'David', 28),
(4, 'Sarah', 35),
(5, 'Michael', 32),
(6, 'Emily', 27),
(7, 'Daniel', 29),
(8, 'Olivia', 31),
(9, 'Matthew', 26),
(10, 'Sophia', 33),
(11, 'Jacob', 24),
(12, 'Emma', 29),
(13, 'Joshua', 32),
(14, 'Ava', 30),
(15, 'Andrew', 28),
(16, 'Isabella', 34),
(17, 'James', 27),
(18, 'Mia', 31),
(19, 'Logan', 25),
(20, 'Charlotte', 29);

--Create rol
CREATE ROLE week_54_role;
GRANT ROLE week_54_role to user <user_name> ;
GRANT USAGE ON database F_F_WEEK_54 TO ROLE week_54_role;
GRANT USAGE ON schema WEEK_54 TO ROLE week_54_role;
GRANT SELECT ON ALL TABLES IN SCHEMA F_F_WEEK_54.WEEK_54 TO ROLE week_54_role;
GRANT INSERT ON ALL TABLES IN SCHEMA F_F_WEEK_54.WEEK_54 TO ROLE week_54_role;
GRANT USAGE ON WAREHOUSE TO ROLE week_54_role;

As you can see, we created 2 simple tables and a new role that we can select and insert from these tables. Now we want to use that role to create and execute a stored procedure to move data from table A to table B. HOWEVER, the role we have created does not have the CREATE PROCEDURE privilege.

Today’s challenge is to create and execute the following procedure without granting the CREATE PROCEDURE privilege:

Procedure

CREATE OR REPLACE copy_to_table(fromTable STRING, toTable STRING, count INT) RETURNS STRING LANGUAGE PYTHON RUNTIME_VERSION = '3.8' PACKAGES = 
('snowflake-snowpark-python') HANDLER = 'copyBetweenTables' AS $$ def copyBetweenTables(snowpark_session, fromTable, toTable, count): snowpark_session.table(fromTable).limit(count).write.mode("append").save_as_table(toTable) return "Success" $$ ;

Your solution should keep the procedure intact and this code snippet should be part of your solution:

CALL copy_to_table('table_a', 'table_b', 5);

Remember that if you want to participate:

Register as a Frosty Friday member. You can do so by clicking on the sidebar and then going to “SIGN UP” (note that joining our mailing list will not give you a Frosty Friday account).

Post your code on GitHub and make sure it is publicly accessible (see our guide if you don’t know how to do this).

Post the URL in the comments of the challenge.

If you have any technical questions you’d like to ask the community, you can do so here, in our thread dedicated to these challenges.

Week 54 – Intermediate

Leave a Reply Cancel reply