{"id":3748,"date":"2022-02-08T11:34:36","date_gmt":"2022-02-08T11:34:36","guid":{"rendered":"https:\/\/wiki.thomasandsofia.com\/?p=3748"},"modified":"2022-02-09T10:38:05","modified_gmt":"2022-02-09T10:38:05","slug":"bqfbd-operations-on-datasets-tables","status":"publish","type":"post","link":"https:\/\/wiki.thomasandsofia.com\/?p=3748","title":{"rendered":"BQFBD &#8211; Operations on Datasets &#038; Tables"},"content":{"rendered":"<p><a href=\"\/bigquery-for-big-data\/\">Main Menu<\/a><\/p>\n<h1>Section 6: BQFBD &#8211; Operations on Datasets &amp; Tables<\/h1>\n<h2>27. Copying Datasets<\/h2>\n<p>https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22736223#questions<\/p>\n<h3>Access<\/h3>\n<ul>\n<li>You cannot grant permissions on:\n<ul>\n<li>Tables<\/li>\n<li>views<\/li>\n<li>row level<\/li>\n<\/ul>\n<\/li>\n<li>You can grant permissions on\n<ul>\n<li>dataset level<\/li>\n<li>columns\n<ul>\n<li>BQ Column-level Security<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li>Granting Access\n<ul>\n<li>Dataset &gt; Share dataset<\/li>\n<li>Enter the user or group and select the role<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>Copying Datasets<\/h3>\n<ul>\n<li>Once created, you cannot rename nor move a dataset\n<ul>\n<li>To do these, you need to copy it.<\/li>\n<li>Requires BQ Data Transfer Service\n<ul>\n<li>API and Services &gt; Enable API &amp; Services<\/li>\n<li>Search BigQuery Data Transfer API\n<ul>\n<li>This needs to be enabled on a Per-Project basis???<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li>Create a new dataset with a new name and new location<\/li>\n<li>Click the Source Dataset &gt; Copy Dataset\n<ul>\n<li>Select the Project and dataset name<\/li>\n<li>Check [X] Overwrite destination tables if there is existing data that needs to be overwritten.<\/li>\n<li>[ Copy ]<\/li>\n<\/ul>\n<\/li>\n<li>This was scheduled for me in UTC-6 time.\u00a0 Not sure when it will complete.\n<ul>\n<li>Note: was completed next time I logged in.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>28. Transfer Service for scheduling Copy Jobs<\/h2>\n<p>https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22738771?start=0#questions<\/p>\n<ul>\n<li>You can use Transfer Service as an automated backup service.<\/li>\n<li>GCP compresses the data before sending, so you should be billed for less than the total size of your dataset.<\/li>\n<li>Limitations\n<ul>\n<li>In Region\n<ul>\n<li>1000 copy jobs per destination table per day<\/li>\n<li>100,000 per project per day (including failures)<\/li>\n<li>Source dataset can have max 20,000 tables\n<ul>\n<li>All can be copied in parallel<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li>Cross Region\n<ul>\n<li>100 per dest. table<\/li>\n<li>2,000 per project \/ day (including failures)<\/li>\n<li>Source dataset can have max 20,000 tables\n<ul>\n<li>1000 can be copied in parallel<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>29. Native operations on Table for Schema change<\/h2>\n<p>https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22738893#questions<\/p>\n<p>There are 2 types of Schema changes.\u00a0 Native and Manual<\/p>\n<h3>Table metadata changes<\/h3>\n<ul>\n<li>Table &gt; Details\n<ul>\n<li>Description<\/li>\n<li>Labels (Tags?)<\/li>\n<li>Expiration date\/time<\/li>\n<\/ul>\n<\/li>\n<li>Copy \/ Delete<\/li>\n<\/ul>\n<h3>Table Schema changes<\/h3>\n<ul>\n<li>Native Schema\n<ul>\n<li>Only 2 Native schema modifications\n<ol>\n<li>Adding new columns to a schema definition<\/li>\n<li>Relaxing a columns mode from Required to Nullable\n<ul>\n<li>Changing to Nullable cannot be undone.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<\/li>\n<li>Changing column names and column types are unsupported<\/li>\n<li>Table &gt; Schema &gt; [Edit Schema]\n<ul>\n<li>You cannot add REQUIRED columns.\n<ul>\n<li>Only Nullable and Repeated<\/li>\n<\/ul>\n<\/li>\n<li>Add data using a DML statement or load new data into the table.\n<ul>\n<li>Create a new table with the same table name and upload your data.<\/li>\n<\/ul>\n<\/li>\n<li>If you appending to an existing table, you&#8217;ll need to manually recreate the schema<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>30. Manual operations on Tables<\/h2>\n<p>https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22738927#questions<\/p>\n<h3>Changing column names<\/h3>\n<ul>\n<li>Write a query to copy the data to an alias<\/li>\n<\/ul>\n<pre>select * EXCEPT(SOURCE_COLUMN), SOURCE_COLUMN as NEW_NAME from `proj.dataset.table`<\/pre>\n<ul>\n<li>Using query settings, when you run the query, set the destination as the name of the source table.\n<ul>\n<li>More &gt; Query settings<\/li>\n<\/ul>\n<\/li>\n<li>This is expensive, but preserves all the existing data.\n<ul>\n<li>Conserve costs by recreating the whole table with the desired column names.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>Changing data types<\/h3>\n<ul>\n<li>Use a query to cast the data type while overwriting the same table.<\/li>\n<\/ul>\n<pre>select * EXCEPT(SOURCE_COLUMN), CAST(SOURCE_COLUMN as NEW_TYPE) from `proj.ds.table`<\/pre>\n<ul>\n<li>You cannot convert strings to Boolean.<\/li>\n<li>use the same method for overwriting the table<\/li>\n<\/ul>\n<h3>Deleting a column<\/h3>\n<pre>select * EXCEPT(COLUMN_TO_DELETE) from `proj.ds.table`<\/pre>\n<h3>Changing a columns mode (Nullable, Required&#8230;)<\/h3>\n<ol>\n<li>Export the table to Cloud Storage<\/li>\n<li>Create a new table with the correct mode (schema)<\/li>\n<li>Load the data to the new table.<\/li>\n<\/ol>\n<ul>\n<li>You will not be charged for the Export Job nor the Load job!\n<ul>\n<li>Export and Load jobs are free<\/li>\n<li>You will be charged for storage, but this should be minimal.\n<ul>\n<li>Don&#8217;t forget to delete the export after the load completes.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>Restoring Deleted Data<\/h3>\n<ul>\n<li>You can restore deleted data of a table up to 7 days, at any point in time\n<ul>\n<li>Table decorators<\/li>\n<li>SYSTEM_TIME AS OF in the FROM clause<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h4>Example<\/h4>\n<p><strong>Delete all rows from a table<\/strong><\/p>\n<pre>delete from `proj.ds.table` where 1=1<\/pre>\n<p><strong>Restore the data<\/strong><\/p>\n<ul>\n<li>Make sure you store the results in a table!\n<ul>\n<li>It&#8217;s OK to use the same table using overwrite<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<pre>select * from `proj.ds.table` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Main Menu Section 6: BQFBD &#8211; Operations on Datasets &amp; Tables 27. Copying Datasets https:\/\/www.udemy.com\/course\/bigquery\/learn\/lecture\/22736223#questions Access You cannot grant permissions on: Tables views row level You can grant permissions on dataset level columns BQ Column-level Security Granting Access Dataset &gt; Share dataset Enter the user or group and select the role Copying Datasets Once created, ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/wiki.thomasandsofia.com\/?p=3748\" 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-3748","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\/3748","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=3748"}],"version-history":[{"count":3,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts\/3748\/revisions"}],"predecessor-version":[{"id":3752,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts\/3748\/revisions\/3752"}],"wp:attachment":[{"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3748"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3748"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3748"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}