{"id":4841,"date":"2020-04-19T19:18:50","date_gmt":"2020-04-19T19:18:50","guid":{"rendered":"https:\/\/www.testpreptraining.com\/tutorial\/?page_id=4841"},"modified":"2020-04-19T19:18:50","modified_gmt":"2020-04-19T19:18:50","slug":"queries-google-professional-data-engineer-gcp-2","status":"publish","type":"page","link":"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/","title":{"rendered":"Queries Google Professional Data Engineer GCP"},"content":{"rendered":"<ul>\n<li>BigQuery supports two types of queries:\n<ul>\n<li>Interactive queries<\/li>\n<li>Batch queries<\/li>\n<\/ul>\n<\/li>\n<li>By default, BigQuery runs interactive queries, or query is executed as soon as possible.<\/li>\n<li>BigQuery queues each batch query on behalf and starts the query as soon as idle resources are available<\/li>\n<li>Run queries by\n<ul>\n<li>Query editor in the Cloud Console<\/li>\n<li>Compose Query option in the BigQuery web UI<\/li>\n<li>BigQuery command-line tool&#8217;s bq query command<\/li>\n<li>BigQuery REST API to programmatically call the jobs.query or query-type jobs.insert methods<\/li>\n<li>BigQuery client libraries<\/li>\n<\/ul>\n<\/li>\n<li>Query execution involves root servers which interpret the query and send it to intermediate servers, which orchestrate the execution to many leaf servers. Leaf servers read from disk and execute.<\/li>\n<li>Analytical window functions:\n<ul>\n<li>Aggregate \u2013 sum, count<\/li>\n<li>Navigation \u2013 lead, lag<\/li>\n<li>Ranking, numbering \u2013 rank, cume_dist<\/li>\n<li>\u201cPartition by\u201d is similar to \u201cgroup by\u201d but doesnt aggregate. This is different from bq partitions how data is stored.<\/li>\n<\/ul>\n<\/li>\n<li>Data Types \u2013 struct, array, timestamp, int64, float64, string<\/li>\n<li>Inner table can be using WITH<\/li>\n<li>ARRAY_AGG \u2013 creates array. UNNEST \u2013 break array.<\/li>\n<li>STRUCT \u2013 creates struct<\/li>\n<li>User defined functions \u2013 sql udf as well as javascript udfs is possible<\/li>\n<li>Udf has constraints \u2013 size of udf output is limited, native javascript not supported<\/li>\n<li>Unnest \u2013 takes an array and returns table<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>Query jobs<\/strong><\/p>\n<ul>\n<li>Jobs are actions that BigQuery runs on behalf to\n<ul>\n<li>load data<\/li>\n<li>export data<\/li>\n<li>query data<\/li>\n<li>copy data.<\/li>\n<\/ul>\n<\/li>\n<li>With Cloud Console, the classic BigQuery web UI, or the CLI for above jobs, a job resource is automatically created, scheduled, and run.<\/li>\n<li>If create a job programmatically, BigQuery schedules and runs the job for you.<\/li>\n<li>jobs execute asynchronously<\/li>\n<li>jobs can be polled for their status.<\/li>\n<\/ul>\n<p><strong>Saving and sharing queries<\/strong><\/p>\n<ul>\n<li>If save a query, it can be\n<ul>\n<li>private (visible only to you)<\/li>\n<li>shared at the project level (visible to project members)<\/li>\n<li>public (anyone can view it).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>BigQuery supports two types of queries: Interactive queries Batch queries By default, BigQuery runs interactive queries, or query is executed as soon as possible. BigQuery queues each batch query on behalf and starts the query as soon as idle resources are available Run queries by Query editor in the Cloud Console Compose Query option in&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"footnotes":""},"categories":[617],"tags":[706,712,619,623,622,618,621],"class_list":["post-4841","page","type-page","status-publish","hentry","category-google-gcp","tag-bigquery","tag-bigquery-queries","tag-data-engineer","tag-gcp","tag-google-certification","tag-google-cloud","tag-professional-data-engineer"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Queries Google Professional Data Engineer GCP - Testprep Training Tutorials<\/title>\n<meta name=\"description\" content=\"Google Cloud Certified Professional Data Engineer Tutorial, dumps, brief notes on Queries\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Queries Google Professional Data Engineer GCP - Testprep Training Tutorials\" \/>\n<meta property=\"og:description\" content=\"Google Cloud Certified Professional Data Engineer Tutorial, dumps, brief notes on Queries\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Testprep Training Tutorials\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/\",\"name\":\"Queries Google Professional Data Engineer GCP - Testprep Training Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\"},\"datePublished\":\"2020-04-19T19:18:50+00:00\",\"dateModified\":\"2020-04-19T19:18:50+00:00\",\"description\":\"Google Cloud Certified Professional Data Engineer Tutorial, dumps, brief notes on Queries\",\"breadcrumb\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Queries Google Professional Data Engineer GCP\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"name\":\"Testprep Training Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\",\"name\":\"Testprep Training\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"contentUrl\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"width\":583,\"height\":153,\"caption\":\"Testprep Training\"},\"image\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Queries Google Professional Data Engineer GCP - Testprep Training Tutorials","description":"Google Cloud Certified Professional Data Engineer Tutorial, dumps, brief notes on Queries","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:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/","og_locale":"en_US","og_type":"article","og_title":"Queries Google Professional Data Engineer GCP - Testprep Training Tutorials","og_description":"Google Cloud Certified Professional Data Engineer Tutorial, dumps, brief notes on Queries","og_url":"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/","og_site_name":"Testprep Training Tutorials","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/","url":"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/","name":"Queries Google Professional Data Engineer GCP - Testprep Training Tutorials","isPartOf":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website"},"datePublished":"2020-04-19T19:18:50+00:00","dateModified":"2020-04-19T19:18:50+00:00","description":"Google Cloud Certified Professional Data Engineer Tutorial, dumps, brief notes on Queries","breadcrumb":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/queries-google-professional-data-engineer-gcp-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.testpreptraining.ai\/tutorial\/"},{"@type":"ListItem","position":2,"name":"Queries Google Professional Data Engineer GCP"}]},{"@type":"WebSite","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","name":"Testprep Training Tutorials","description":"","publisher":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization","name":"Testprep Training","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/","url":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","contentUrl":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","width":583,"height":153,"caption":"Testprep Training"},"image":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/4841","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/comments?post=4841"}],"version-history":[{"count":1,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/4841\/revisions"}],"predecessor-version":[{"id":4851,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/4841\/revisions\/4851"}],"wp:attachment":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/media?parent=4841"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/categories?post=4841"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/tags?post=4841"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}