{"id":449,"date":"2024-08-09T09:34:11","date_gmt":"2024-08-09T09:34:11","guid":{"rendered":"https:\/\/datadandies.nl\/?p=449"},"modified":"2024-08-09T09:34:11","modified_gmt":"2024-08-09T09:34:11","slug":"find-each-occurrence-of-a-combination-of-words-in-a-text-and-include-the-subsequent-word-using-python-and-regex","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2024\/08\/09\/find-each-occurrence-of-a-combination-of-words-in-a-text-and-include-the-subsequent-word-using-python-and-regex\/","title":{"rendered":"Find each occurrence of a combination of words in a text and include the subsequent word using Python and Regex"},"content":{"rendered":"\n

\u201cTHE DATABASE MOST USED IN YOUR QUERIES WILL BE DELETED!\u201d<\/p>\n\n\n\n

\u201cBut don\u2019t you worry pal, we are gonna migrate the tables and views you use to another database!\u201d<\/p>\n\n\n\n

\u201cIf you could just give me a list of the objects you use in the soon-to-be-history-database, that would be great.\u201d<\/p>\n\n\n\n

Migrations are a common animal in any data ecosystem. So chances are that you have received the question above. And you are more than willing to oblige. However, you dread having to look through hundreds of CREATE-statements.<\/p>\n\n\n\n

Fortunately, you are very savvy and laz\u2026 efficient! Efficient is the word.<\/p>\n\n\n\n

First, you will get all the CREATE-statements of all the objects that you created in your database in Snowflake.<\/p>\n\n\n\n

Then you will you use Python and regex to scoop up all the mentions of the to-be-deleted database including the attached objects.<\/p>\n\n\n\n

And because you are extremely \u2026 efficient! You grab this script from the internet \u2026 From this post even! Here we go:<\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
--In Snowflake<\/span><\/span>\nselect<\/span> get_ddl(<\/span>'<\/span>database<\/span>'<\/span>, <\/span>'<\/span>CERTAIN_DATABASE<\/span>'<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
# In VS Code<\/span><\/span>\n<\/span>\nimport<\/span> re<\/span><\/span>\n<\/span>\n# Path.<\/span><\/span>\npath <\/span>=<\/span> <\/span>r<\/span>'<\/span>C:\\Users\\highlyefficientuser\\result_of_snowflake_ddl.txt<\/span>'<\/span><\/span>\n<\/span>\n# Open file.<\/span><\/span>\nfile <\/span>=<\/span> <\/span>open<\/span>(<\/span>path<\/span>)<\/span><\/span>\n<\/span>\n# Turn it into a string.<\/span><\/span>\nstring <\/span>=<\/span> <\/span>str<\/span>(<\/span>file<\/span>.<\/span>read<\/span>())<\/span><\/span>\n<\/span>\n# Create regex object for database CERTAIN_DATABASE and the subsequent table untill a space. Ignore casing.<\/span><\/span>\nDBregex <\/span>=<\/span> re<\/span>.<\/span>compile<\/span>(<\/span>r<\/span>'<\/span>CERTAIN_DATABASE.\\w<\/span>*<\/span>'<\/span>,<\/span> re<\/span>.<\/span>IGNORECASE<\/span>)<\/span><\/span>\n<\/span>\n# Save all matching occurrences in a list.<\/span><\/span>\nalleVoorkomens <\/span>=<\/span> DBregex<\/span>.<\/span>findall<\/span>(<\/span>string<\/span>)<\/span><\/span>\n<\/span>\n# Lowercase all occurrences. <\/span><\/span>\nfor<\/span> i <\/span>in<\/span> <\/span>range<\/span>(<\/span>len<\/span>(<\/span>alleVoorkomens<\/span>)):<\/span><\/span>\n    alleVoorkomens<\/span>[<\/span>i<\/span>]<\/span> <\/span>=<\/span> alleVoorkomens<\/span>[<\/span>i<\/span>].<\/span>lower<\/span>()<\/span><\/span>\n<\/span>\n# Remove any duplicates using set().<\/span><\/span>\nuniekeVoorkomens <\/span>=<\/span> <\/span>list<\/span>(<\/span>set<\/span>(<\/span>alleVoorkomens<\/span>))<\/span><\/span>\n<\/span>\n# Sort the list alphabetically.<\/span><\/span>\nuniekeVoorkomens<\/span>.<\/span>sort<\/span>()<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
A word of warning before I wish you good luck: this only works for fully qualified object names, meaning DATABASE_NAME.SCHEMA_NAME.OBJECT_NAME. When all you\u2019re using in your CREATE-statements are single object names without the database, then (depending on the tool you are using) this will not work.<\/p>\n\n\n\n
Good luck!<\/p>\n","protected":false},"excerpt":{"rendered":"
\u201cTHE DATABASE MOST USED IN YOUR QUERIES WILL BE DELETED!\u201d \u201cBut don\u2019t you worry pal, we are gonna migrate the tables and views you use to another database!\u201d \u201cIf you could just give me a list of the objects you use in the soon-to-be-history-database, that would be great.\u201d Migrations are a common animal in any…<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[37,39,4],"class_list":["post-449","post","type-post","status-publish","format-standard","hentry","category-blog","tag-python","tag-regex","tag-sql"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/449","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=449"}],"version-history":[{"count":1,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/449\/revisions"}],"predecessor-version":[{"id":450,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/449\/revisions\/450"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=449"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=449"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=449"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}