{"id":203,"date":"2020-01-12T02:46:31","date_gmt":"2020-01-12T02:46:31","guid":{"rendered":"https:\/\/snowflake.pavlik.us\/?p=203"},"modified":"2020-01-13T14:22:10","modified_gmt":"2020-01-13T14:22:10","slug":"snowflake-relationships-java-utilities","status":"publish","type":"post","link":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/","title":{"rendered":"Snowflake Relationships &#8211; Java Utilities"},"content":{"rendered":"\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img decoding=\"async\" src=\"https:\/\/cdn.pixabay.com\/photo\/2016\/12\/09\/18\/30\/database-schema-1895779__480.png\" alt=\"\"\/><\/figure><\/div>\n\n\n\n<p>Administrators usually disable parent-child relational constraint enforcement, especially in OLAP databases. Snowflake allows definition of parent-child relationships, but currently does not enable enforcement. This approach enables documentation at the table and view level. It also allows integration with Entity Relationship Diagram (ERD) solutions or custom data dictionaries.<\/p>\n\n\n\n<p>Snowflake stores the relationship information in the table Data Definition Language (DDL) representation of each table or view. Since there appears to be no centralized location to read the relationships, I wrote a Java project to capture them automatically. <\/p>\n\n\n\n<p>In its present state it has some limitations. Chief among them is that I have tested it using only single-column primary and foreign keys. I think, though I have not yet confirmed, that it should work with multi-column keys. It could run into parsing issues due to differences in how Snowflake stores DDL lines for multi-column primary and foreign keys. This should be a simple problem to address, but I&#8217;ve not yet tested it.<\/p>\n\n\n\n<p>The attached Java project, Snowflake_Utilities, has a class named SchemaInfo. The SchemaInfo class will collect more schema information in future updates. The initial preview focuses on collecting relationship information. It can:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Return a primary key for a table or view<\/li><li>Return all foreign keys defined for a table or view, along with the name and key on the parent<\/li><li>Get all primary keys for every table and view across an entire Snowflake account<\/li><li>Get all foreign keys for every table and view across an entire Snowflake account<\/li><\/ul>\n\n\n\n<p>The included Java source, exported from Eclipse, should be easy to configure. The main thing to add to the project build path is the latest Snowflake JDBC driver. <\/p>\n\n\n\n<p><a href=\"https:\/\/snowflake.pavlik.us\/wp-content\/uploads\/2020\/01\/SnowflakeConstraints.zip\">https:\/\/snowflake.pavlik.us\/wp-content\/uploads\/2020\/01\/SnowflakeConstraints.zip<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Administrators usually disable parent-child relational constraint enforcement, especially in OLAP databases. Snowflake allows definition of parent-child relationships, but currently does not enable enforcement. This approach enables documentation at the table and view level. It also allows integration with Entity Relationship Diagram (ERD) solutions or custom data dictionaries. Snowflake stores the relationship information in the table [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25,24],"tags":[],"class_list":["post-203","post","type-post","status-publish","format-standard","hentry","category-java","category-utilities"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\r\n<title>Snowflake Relationships - Java Utilities - Snowflake in the Carolinas<\/title>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Snowflake Relationships - Java Utilities - Snowflake in the Carolinas\" \/>\r\n<meta property=\"og:description\" content=\"Administrators usually disable parent-child relational constraint enforcement, especially in OLAP databases. Snowflake allows definition of parent-child relationships, but currently does not enable enforcement. This approach enables documentation at the table and view level. It also allows integration with Entity Relationship Diagram (ERD) solutions or custom data dictionaries. Snowflake stores the relationship information in the table [&hellip;]\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/\" \/>\r\n<meta property=\"og:site_name\" content=\"Snowflake in the Carolinas\" \/>\r\n<meta property=\"article:published_time\" content=\"2020-01-12T02:46:31+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2020-01-13T14:22:10+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/cdn.pixabay.com\/photo\/2016\/12\/09\/18\/30\/database-schema-1895779__480.png\" \/>\r\n<meta name=\"author\" content=\"Greg Pavlik\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Greg Pavlik\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/\"},\"author\":{\"name\":\"Greg Pavlik\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#\\\/schema\\\/person\\\/019455f4675665b6cf5edea31ec44d7b\"},\"headline\":\"Snowflake Relationships &#8211; Java Utilities\",\"datePublished\":\"2020-01-12T02:46:31+00:00\",\"dateModified\":\"2020-01-13T14:22:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/\"},\"wordCount\":290,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/cdn.pixabay.com\\\/photo\\\/2016\\\/12\\\/09\\\/18\\\/30\\\/database-schema-1895779__480.png\",\"articleSection\":[\"Java\",\"Utilities\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/\",\"url\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/\",\"name\":\"Snowflake Relationships - Java Utilities - Snowflake in the Carolinas\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/cdn.pixabay.com\\\/photo\\\/2016\\\/12\\\/09\\\/18\\\/30\\\/database-schema-1895779__480.png\",\"datePublished\":\"2020-01-12T02:46:31+00:00\",\"dateModified\":\"2020-01-13T14:22:10+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#\\\/schema\\\/person\\\/019455f4675665b6cf5edea31ec44d7b\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/#primaryimage\",\"url\":\"https:\\\/\\\/cdn.pixabay.com\\\/photo\\\/2016\\\/12\\\/09\\\/18\\\/30\\\/database-schema-1895779__480.png\",\"contentUrl\":\"https:\\\/\\\/cdn.pixabay.com\\\/photo\\\/2016\\\/12\\\/09\\\/18\\\/30\\\/database-schema-1895779__480.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2020\\\/01\\\/12\\\/snowflake-relationships-java-utilities\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/snowflake.pavlik.us\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Snowflake Relationships &#8211; Java Utilities\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#website\",\"url\":\"https:\\\/\\\/snowflake.pavlik.us\\\/\",\"name\":\"Snowflake in the Carolinas\",\"description\":\"Random thoughts on all things Snowflake in the Carolinas\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/snowflake.pavlik.us\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#\\\/schema\\\/person\\\/019455f4675665b6cf5edea31ec44d7b\",\"name\":\"Greg Pavlik\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g\",\"caption\":\"Greg Pavlik\"},\"description\":\"Greg is a Senior Sales Engineer at Snowflake Computing, in the Raleigh-Durham area. He's been in data management and security for the twenty years.\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Snowflake Relationships - Java Utilities - Snowflake in the Carolinas","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/","og_locale":"en_US","og_type":"article","og_title":"Snowflake Relationships - Java Utilities - Snowflake in the Carolinas","og_description":"Administrators usually disable parent-child relational constraint enforcement, especially in OLAP databases. Snowflake allows definition of parent-child relationships, but currently does not enable enforcement. This approach enables documentation at the table and view level. It also allows integration with Entity Relationship Diagram (ERD) solutions or custom data dictionaries. Snowflake stores the relationship information in the table [&hellip;]","og_url":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/","og_site_name":"Snowflake in the Carolinas","article_published_time":"2020-01-12T02:46:31+00:00","article_modified_time":"2020-01-13T14:22:10+00:00","og_image":[{"url":"https:\/\/cdn.pixabay.com\/photo\/2016\/12\/09\/18\/30\/database-schema-1895779__480.png","type":"","width":"","height":""}],"author":"Greg Pavlik","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Greg Pavlik","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/#article","isPartOf":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/"},"author":{"name":"Greg Pavlik","@id":"https:\/\/snowflake.pavlik.us\/#\/schema\/person\/019455f4675665b6cf5edea31ec44d7b"},"headline":"Snowflake Relationships &#8211; Java Utilities","datePublished":"2020-01-12T02:46:31+00:00","dateModified":"2020-01-13T14:22:10+00:00","mainEntityOfPage":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/"},"wordCount":290,"commentCount":0,"image":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/#primaryimage"},"thumbnailUrl":"https:\/\/cdn.pixabay.com\/photo\/2016\/12\/09\/18\/30\/database-schema-1895779__480.png","articleSection":["Java","Utilities"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/","url":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/","name":"Snowflake Relationships - Java Utilities - Snowflake in the Carolinas","isPartOf":{"@id":"https:\/\/snowflake.pavlik.us\/#website"},"primaryImageOfPage":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/#primaryimage"},"image":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/#primaryimage"},"thumbnailUrl":"https:\/\/cdn.pixabay.com\/photo\/2016\/12\/09\/18\/30\/database-schema-1895779__480.png","datePublished":"2020-01-12T02:46:31+00:00","dateModified":"2020-01-13T14:22:10+00:00","author":{"@id":"https:\/\/snowflake.pavlik.us\/#\/schema\/person\/019455f4675665b6cf5edea31ec44d7b"},"breadcrumb":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/#primaryimage","url":"https:\/\/cdn.pixabay.com\/photo\/2016\/12\/09\/18\/30\/database-schema-1895779__480.png","contentUrl":"https:\/\/cdn.pixabay.com\/photo\/2016\/12\/09\/18\/30\/database-schema-1895779__480.png"},{"@type":"BreadcrumbList","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2020\/01\/12\/snowflake-relationships-java-utilities\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/snowflake.pavlik.us\/"},{"@type":"ListItem","position":2,"name":"Snowflake Relationships &#8211; Java Utilities"}]},{"@type":"WebSite","@id":"https:\/\/snowflake.pavlik.us\/#website","url":"https:\/\/snowflake.pavlik.us\/","name":"Snowflake in the Carolinas","description":"Random thoughts on all things Snowflake in the Carolinas","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/snowflake.pavlik.us\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/snowflake.pavlik.us\/#\/schema\/person\/019455f4675665b6cf5edea31ec44d7b","name":"Greg Pavlik","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g","caption":"Greg Pavlik"},"description":"Greg is a Senior Sales Engineer at Snowflake Computing, in the Raleigh-Durham area. He's been in data management and security for the twenty years."}]}},"_links":{"self":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts\/203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/comments?post=203"}],"version-history":[{"count":4,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts\/203\/revisions"}],"predecessor-version":[{"id":216,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts\/203\/revisions\/216"}],"wp:attachment":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/media?parent=203"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/categories?post=203"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/tags?post=203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}