{"id":3780,"date":"2022-02-13T23:24:36","date_gmt":"2022-02-13T23:24:36","guid":{"rendered":"https:\/\/wiki.thomasandsofia.com\/?p=3780"},"modified":"2022-02-13T23:52:15","modified_gmt":"2022-02-13T23:52:15","slug":"bqfbd-views-in-bigquery","status":"publish","type":"post","link":"https:\/\/wiki.thomasandsofia.com\/?p=3780","title":{"rendered":"BQFBD &#8211; Views in BigQuery"},"content":{"rendered":"<p><a href=\"\/bigquery-for-big-data\/\">Main Menu<\/a><\/p>\n<h1>Section 11: Views in BigQuery<\/h1>\n<h2>47. Introduction to Views &amp; their Advantages<\/h2>\n<p>https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22757841#questions<\/p>\n<h3>View Overview<\/h3>\n<ul>\n<li>Views do not contain any data of its own<\/li>\n<li>Can be created by selecting any number of rows or columns of it base table\/tables\n<ul>\n<li>Views can reflect the results of a JOIN query on any number of tables.<\/li>\n<\/ul>\n<\/li>\n<li>Once created, views become independent of the base table.\n<ul>\n<li>The View schema is frozen and will not reflect changes made to the base table schema<\/li>\n<li>To change a View schema, you have to change the underlying view definition query<\/li>\n<li>Changing a View schema will not change it&#8217;s base table&#8217;s schema<\/li>\n<\/ul>\n<\/li>\n<li>Views are read only<\/li>\n<li>Drop the base table and you cannot run queries against the view, since Views do not contain their own underlying data.<\/li>\n<\/ul>\n<h3>Advantages of Views<\/h3>\n<ul>\n<li>Protect base table from being accidentally dropped or altered.<\/li>\n<li>Can help turn lengthy and complicated query into a one-liner.<\/li>\n<li>No additional storage costs\n<ul>\n<li>Again, no data exists in the view.<\/li>\n<li>Queries are still charged<\/li>\n<\/ul>\n<\/li>\n<li>Views prevent direct access to tables for security reasons.\n<ul>\n<li>Users are restricted to getting data from Views only<\/li>\n<li>Different users should be provided different data accesses\n<ul>\n<li>Higher users would have access to to all columns in our tables data,<\/li>\n<li>Others are only provided limited access to limited columns<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h4>View Access Example<\/h4>\n<p><a href=\"https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/views.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3781\" src=\"https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/views.png\" alt=\"\" width=\"1122\" height=\"331\" srcset=\"https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/views.png 1122w, https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/views-300x89.png 300w, https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/views-1024x302.png 1024w, https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/views-768x227.png 768w, https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/views-150x44.png 150w\" sizes=\"auto, (max-width: 1122px) 100vw, 1122px\" \/><\/a><\/p>\n<ul>\n<li>Access to this table required by users in various depts.\n<ul>\n<li>HR\n<ul>\n<li>Requires access to all columns<\/li>\n<\/ul>\n<\/li>\n<li>Support\n<ul>\n<li>Only need Emp ID, Name, Position, Mgr and Dept Id<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><strong>Support View<\/strong><\/p>\n<p><a href=\"https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/supportview.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3782\" src=\"https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/supportview.png\" alt=\"\" width=\"901\" height=\"296\" srcset=\"https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/supportview.png 901w, https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/supportview-300x99.png 300w, https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/supportview-768x252.png 768w, https:\/\/wiki.thomasandsofia.com\/wp-content\/uploads\/2022\/02\/supportview-150x49.png 150w\" sizes=\"auto, (max-width: 901px) 100vw, 901px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2>48. Create Views in BQ<\/h2>\n<p>https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22757847#questions<\/p>\n<ul>\n<li>Create a view with 2 columns and 100 rows\n<ul>\n<li>select names, gender from `proj.ds.table` limit 100<\/li>\n<li>Click [ Save View ]\n<ul>\n<li>Views must be stored in the same region as the table data<\/li>\n<\/ul>\n<\/li>\n<li>Provide a name for the view<\/li>\n<li>You cannot add a Description, Expiration or Labels at creation time using the UI\n<ul>\n<li>Possible with CLI or API<\/li>\n<li>Possible after creations<\/li>\n<\/ul>\n<\/li>\n<li>Expiration dates are default to the Table&#8217;s, but can be set shorter.<\/li>\n<\/ul>\n<\/li>\n<li><\/li>\n<\/ul>\n<h3>Limit Access to Views<\/h3>\n<ul>\n<li>To limit access to a few columns, create a view with only those columns<\/li>\n<li>To limit access to a View\n<ul>\n<li>Views are treated as Table resources in BQ<\/li>\n<li>There is no option to assign access controls directly to Tables or Views<\/li>\n<li>The lowest level of BQ that you are able to grant access is the dataset level\n<ul>\n<li>Therefore, you cannot granularly grant access to specific tables and views in that dataset.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li>To grant access to a Dataset\n<ul>\n<li>Create a Cloud IAM role to an entity at the dataset level or higher (Project?)<\/li>\n<li>Users with that role will only be able to perform the set of operations on all tables and views present in that dataset.<\/li>\n<\/ul>\n<\/li>\n<li>Limiting access to only Views\n<ul>\n<li>Create the View in a different Dataset than the source tables!<\/li>\n<li>Creating the View in the same dataset would not make sense&#8230; the user would have access to the tables also!<\/li>\n<li><strong>Always create Views in a different dataset than the source tables!<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>Copying and Renaming Views<\/h3>\n<ul>\n<li>Copy\n<ul>\n<li>Currently only available via UI\n<ul>\n<li>Not available via API, CLI or client libraries<\/li>\n<li>You will need to recreate the view if using these<\/li>\n<\/ul>\n<\/li>\n<li>View &gt; [ Copy View ]<\/li>\n<\/ul>\n<\/li>\n<li>Rename\n<ul>\n<li>This option is not available.<\/li>\n<li>Copy the view to a new name, then delete the current view.\n<ul>\n<li>Deleting a view cannot be undone.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>49. Restrict rows at User level in Views<\/h2>\n<p>https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22758315#overview<\/p>\n<h3>Restricting Rows<\/h3>\n<p>session_user() = email address of current user<\/p>\n<ul>\n<li>Create a new table with user email address assigned to groups\n<ul>\n<li>group_id, email<\/li>\n<\/ul>\n<\/li>\n<li>Create a new column on the source table that include the access group ID<\/li>\n<li>Create a view that joins both tables\n<ul>\n<li><code>select data.* from `proj.ds.data` as data JOIN `proj.ds.groups` as group ON data.group_id=group.group_id where group.email=\"john.doe@johndoe.com\"<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>50. View Limitations and Quotas<\/h2>\n<p>https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22758305#overview<\/p>\n<h3>Limitations<\/h3>\n<ul>\n<li>Datasets containing the View and Table must be in the same location<\/li>\n<li>Data from a View cannot be exported<\/li>\n<li>A standard SQL query cannot reference a view defined using legacy SQL syntax<\/li>\n<li>You cannot reference query parameters in Views\n<ul>\n<li>To be discussed later<\/li>\n<\/ul>\n<\/li>\n<li>You cannot include a User-defined function in View definition<\/li>\n<li>You cannot reference a view in a wildcard table query<\/li>\n<\/ul>\n<h3>Quotas<\/h3>\n<ul>\n<li>Maximum nested views = 16\n<ul>\n<li>A view created from another view\n<ul>\n<li>select * from `proj.ds.view`<\/li>\n<\/ul>\n<\/li>\n<li>If you delete a parent view, the child views will not work<\/li>\n<\/ul>\n<\/li>\n<li>Maximum number of views in a dataset = 2500<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Main Menu Section 11: Views in BigQuery 47. Introduction to Views &amp; their Advantages https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22757841#questions View Overview Views do not contain any data of its own Can be created by selecting any number of rows or columns of it base table\/tables Views can reflect the results of a JOIN query on any number of tables. ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/wiki.thomasandsofia.com\/?p=3780\" title=\"read more...\">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":[77,78],"tags":[],"class_list":["post-3780","post","type-post","status-publish","format-standard","hentry","category-bigquery","category-bigquery-for-big-data-engineers"],"_links":{"self":[{"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts\/3780","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3780"}],"version-history":[{"count":3,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts\/3780\/revisions"}],"predecessor-version":[{"id":3786,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts\/3780\/revisions\/3786"}],"wp:attachment":[{"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3780"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3780"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3780"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}