Python on Snowflake: Create and Populate a Database and Retrieve Data
HTML-код
- Опубликовано: 14 окт 2024
- Python on Snowflake: Create and Populate a Database and Retrieve Data
In this episode we return to our Python on Snowflake playlist for our second installment to show how to create a warehouse, database, schema, and a table for us to populate and retrieve data from, using the Snowflake Connector for Python. Though Snowflake has many different ways to connect, Python is my favorite since it the language I use the most for big data platforms.
If you didn’t see the first video, make sure to go back and review it, as it will show you how to get set up to use the Snowflake Connector for Python. Then you’ll be free to use Python for all of your automation.
Related Videos:
Python on Snowflake - Getting started with the Snowflake Connector for Python
• Python on Snowflake - ...
Python on Snowflake: Create and Populate a Database and Retrieve Data
You are watching this one now!
How to Use write_pandas to Migrate Dataframes into Snowflake Databases
• How to Use write_panda...
How to Query Snowflake Data into Pandas Dataframes
• How to Query Snowflake...
How to Execute Many SQL Statements from a File Using the Python Connector for Snowflake
• How to Execute Many SQ...
Use Parameters on Snowflake Queries with the Python Connector
• Use Parameters on Snow...
How to Use execute_string for Many SQL Statements Using Python on Snowflake
• How to Use execute_str...
Python on Snowflake - How to Use executemany with Lists or Tuples for Parameters
• Python on Snowflake - ...
Follow us on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a RUclips Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
/ seamacke
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Data Project? Contact me today!
how to create and populate data base in snowflake, how to retrieve data from database in snowflake, snowflake python connector, snowflake create database, snowflake python create database, python create warehouse snowflake, python create schema snowflake, snowflake python select data, sean mackenzie data analytics and data engineering
#pythonsnowflake #snowflake #createdatabase
• Python on Snowflake: C...
How many of you are starting a big data project?
Bro, I can not put into words how good are your videos, they are really helpful
Tks from Brazil!
Glad you like them! Thanks for watching!
Dear Sean, your videos are Awesome , !!! Thanks for your contribution!!!
I appreciate that!
Dear Sean, please create a video on how to extract the data from snowflake using queries which is in json file and crate a file for connection and create another file for extraction.
That's a neat idea. There are some companies that make API for this. iirc Dozer does something like that. Check it out 🛠
I like your videos. I was hoping that you would include an example of how to populate a Snowflake table using local data. Maybe running a PUT command to populate a SF stage then updating the table from the @stage. I am not sure if the SF connector can execute a PUT statement or if you need to run a SNOWSQL script from within SF. I was hoping to avoid SNOWSQL. Any thoughts?
Great question - you certainly can populate from local data. If you use Pandas, you can just pull your data into a dataframe, then write it to Snowflake:
ruclips.net/video/JMMp5zvWdxs/видео.html
Copy 1m rows from your SQL server to Snowflake:
ruclips.net/video/FHQyOlcO3RQ/видео.html
Use pd_writer to write to Snowflake:
ruclips.net/video/mJSWAj_D6f4/видео.html
In these cases, you don't need SNOWSQL, and I do talk a bit about the automatic file management (ie. parquet) that is done for you in the background. I do think your idea is a great one for a video though! To push some files from local to remote staging and then load some tables. I'll put that on my list! Thanks!
Thanks for the video. How would you use python to clean/prepare data stored in snowflake and then create a new table in snowflake with the cleaned data?
Would you even recommend cleaning/preparing data like this or rather doing it another way?
With cleaning I mean find and replace, regex extraction, other string operation, flagging, dropping rows/columns etc.
I am a data analyst and not an engineer so the preparation would be more of adhoc nature.
Thanks
This is a great question. You can certainly do everything you mention above. If your Python skills are good, and your SQL skills are good, you'll have no problem. You can do most cleaning activities in Snowflake that you would do using your local database like SQL Server, Access, or PostgreSQL.
"Where should I do this operation?" If it is on huge amounts of data, execute your SQL remotely and let Snowflake do the work, while on smaller datasets it might be faster for you (if you have expert skills in Python) to bring them into Pandas dataframes to slice and dice. Alternatively, you might use some worksheets in the Snowflake browser client to do all your cleaning/wrangling work in SQL and then bring the result sets back using Python (for analytics).
The benefit of both approaches (worksheet in browser and python directly) is that you can reproduce the result if the task becomes recurring. If you saved the sequence of SQL statements from your worksheet in a .SQL file, you can take those statements and save them as a procedure in snowflake or just run it yourself whenever you want from Python using execute_stream as I showed in this video:
ruclips.net/video/dlFNbyqPKQk/видео.html
Good luck on your project!
@@seanmackenziedataengineering Thanks a lot Sean. Ok yes I guess I have to do the cleaning in SQL if the data is very big to utilize the snowflake scalability. I have done most cleaning in python/Dataiku before so I am a bit afraid of doing it in SQL haha.
Will start the project in 2 weeks so we will see how big the data is.
If I am able to work with the data in python what would you recommend doing with the final dataset so I can use it in Tableau? Everything should be in the cloud so I think I will run python an AWS. But I assume it is pretty straight forward to load the data back to a new table on Snowflake via the connector?
@@KasperBirkelund I have a similar view in that many times I prefer to pull data into staging environments where I can slice and dice with Python or other tools. If the dataset is not prohibitively large, and the cost won't be too much, go for it! Or, complete some large set operations on the server side in SQL before pulling down a dataset to work with. Then do your magic in Python and put it back in. You can push data back into your table using something like write_pandas, which I did a video on here:
ruclips.net/video/JMMp5zvWdxs/видео.html
You rock.
Thanks! Glad this was helpful. Cheers
Hi Sean I'm new to python I need some inputs from you regarding my project requirement. I need a python that automates the process creating DB, schema, warehouse, creating users and grants role to the users. certain users use read role and certain uses read write role. all for future grants. can you please help me with this ?
Sounds like a good project! You can get most of this right here in this video. The users and permissions are a good topic for a future video! Thanks!
Your videos are awesome. It will be great if you share your code
Thanks for the suggestion. I'm thinking about the way I want to do this! Stay tuned!
can we do the same thing on jupyter notebook?
No problem! You should be able to use the Snowflake Connector for Python and do all kinds of cool stuff 👍
video starts at 11:50
Thanks!