Achieving Advanced Insights with BigQuery

The third course in this course series is Achieving Advanced Insights with BigQuery. Here we will build on your growing knowledge of SQL as we dive into advanced functions and how to break apart a complex query into manageable steps. We will cover the internal architecture of BigQuery (column-based sharded storage) and advanced SQL topics like nested and repeated fields through the use of Arrays and Structs. Lastly we will dive into optimizing your queries for performance and how you can secure your data through authorized views.

After completing this course, enroll in the Applying Machine Learning to your Data with Google Cloud course.

By enrolling in this specialization you agree to the Qwiklabs Terms of Service as set out in the FAQ and located at: https://qwiklabs.com/terms_of_service «<

• Deepen your knowledge of SQL on BigQuery by learning about more advanced functions

• Walkthrough the evolution of how traditional databases handle dataset scale and compare how BigQuery was developed to address scaling limitations.

• Learn the fundamental pieces of work that impact BigQuery performance and how to optimize your queries for speed.

• Learn what Google Cloud and BigQuery tools are available to you to permission control and share your data.

Syllabus

Syllabus - What you will learn from this course
Week 1
Introduction
Week 2
Advanced Functions and Clauses
Week 3
Schema Design and Nested Data Structures
Week 4
Optimizing for Performance
Week 5
Advanced Insights with Vertex AI Workbench
Week 6
Data Access
Week 7
Summary

FAQ

Can I preview a course before enrolling?

Yes, you can preview the first video and view the syllabus before you enroll. You must purchase the course to access content not included in the preview.

What will I get when I enroll?

Once you enroll and your session begins, you will have access to all videos and other resources, including reading items and the course discussion forum. You’ll be able to view and submit practice assessments, and complete required graded assignments to earn a grade and a Course Certificate.

When will I receive my Course Certificate?

If you complete the course successfully, your electronic Course Certificate will be added to your Accomplishments page - from there, you can print your Course Certificate or add it to your LinkedIn profile.

Why can’t I audit this course?

This course is one of a few offered on Coursera that are currently available only to learners who have paid or received financial aid, when available.

Is financial aid available?

Yes. In select learning programs, you can apply for financial aid or a scholarship if you can’t afford the enrollment fee. If fin aid or scholarship is available for your learning program selection, you’ll find a link to apply on the description page.

Qwiklabs Terms of Service

THIS TERMS OF SERVICE AGREEMENT (THE “AGREEMENT”), ALONG WITH THE PRIVACY POLICY LOCATED AT qwiklab.com/privacy_policy (THE “PRIVACY POLICY”), ESTABLISHES THE TERMS AND CONDITIONS APPLICABLE TO YOUR USE OF THE SERVICE (AS DEFINED BELOW) OFFERED BY CLOUD VLAB INC. (“CLOUD VLAB” OR ‘WE’). BY CLICKING THE "I ACCEPT" BUTTON DISPLAYED AS PART OF THE REGISTRATION PROCESS OR BY USING THE SERVICE OR ANY PORTION THEREOF, YOU ACCEPT AND AGREE TO BE BOUND BY THE TERMS AND CONDITIONS OF THIS AGREEMENT AND THE PRIVACY POLICY, INCLUDING ALL TERMS INCORPORATED HEREIN BY REFERENCE. IF YOU ARE ENTERING INTO THIS AGREEMENT ON BEHALF OF A COMPANY OR OTHER LEGAL ENTITY, YOU REPRESENT THAT YOU HAVE THE AUTHORITY TO BIND SUCH ENTITY TO THIS AGREEMENT, IN WHICH CASE THE TERMS "YOU" OR "YOUR" SHALL REFER TO SUCH ENTITY. IF YOU DO NOT HAVE SUCH AUTHORITY, OR IF YOU DO NOT AGREE WITH THESE TERMS AND CONDITIONS, YOU MUST SELECT THE "I DECLINE" BUTTON AND MAY NOT USE THE SERVICE.

Definitions“Service” means the Lab Service and the Lab Creation Service, collectively, along with the Qwiklab Site.“Lab Service” means the educational, training, and learning services provided to you through the Qwiklabs Site, or any related website provided by Cloud vLab, in concert with your respective Lab Sponsor.“Creator role” means the designation of your account as a creator to access the “Lab Creation Service”. If you have the Creator role, all sections of this agreement apply to you including sections that reference the Lab Service and the Lab Creation Service.“Lab Creation Service” means the services and functionality hosted by Cloud vLab and made available to you only if you have a “Creator role” on or through the Qwiklabs Site through which you may deploy, configure, customize, manage, administer, and control a virtual server for implementing and testing software as a part of your training through the Lab Service.“Lab Sponsor” means the company or other organization with whom you are employed or otherwise associated in connection with the Lab Service."Content" means any content or work of authorship created, owned or licensed by you only if you have a “Creator role’, submitted to the Lab Creation Service, and that is transmitted, rendered, displayed or executed on or through the Service, including without limitation any text, postings, audio, sounds, video, photos, images, messages, software, and materials.“Sponsor Content” means any content or work of authorship created, owned, or licensed by your Lab Sponsor and utilized in the Service.“Qwiklabs Technology” means all of Cloud vLab’s proprietary technology (including, but not limited to, software, hardware, products, processes, algorithms, user interfaces, know-how, techniques, designs, and other tangible or intangible technical material or information) made available to you by Cloud vLab in providing the Service, excluding Sponsor Content.“Qwiklabs Site” means the web site located at Qwiklab.com, and/or any related or successor URLs operated or controlled by Cloud vLab.“Resources” means any virtual or physical infrastructure provided to you by the Service.Use of the ServiceOverview of Rights

This Agreement applies to all use of the Service. Subject to the terms and conditions of this Agreement and your registration with us through the Qwiklabs user registration process, Cloud vLab hereby grants you the right to use the Lab Service under the terms of this Agreement. Furthermore, if you have a Creator role, Cloud vLab hereby grants you the right to use the Lab Creation Service under the terms of this Agreement. Use of Resources may be performed only in accordance with the terms and conditions of this Agreement and such other specifications as may be communicated by Cloud vLab from time to time.

Restrictions and Limitations

(a) You may not access the Service if you are a competitor of Cloud vLab, unless you have our prior written consent. In addition, you may not access and/or use the Service for purposes of monitoring its availability, performance, or functionality, or for any other benchmarking or competitive purposes.

(b) You shall not (i) license, sublicense, sell, resell, transfer, assign, distribute, or otherwise commercially exploit or make available to any third party the Service in any way, except as expressly authorized in this Agreement; (ii) modify (except as permitted through the Lab Creation Service (if you have a Creator role) or make derivative works based upon the Service; (iii) reverse engineer the Service and/or any component thereof; (iv) access the Service in order to build a competitive product or service; (v) build a product using similar ideas, features, functions, or graphics of the Service, or (vi) copy any ideas, features, functions, or graphics of the Service.

(c) You shall not utilize any part of the Service to: (i) send spam or otherwise duplicative or unsolicited messages in violation of applicable Laws (as defined below); (ii) send or store infringing, obscene, threatening, libelous, defamatory, pornographic, online gambling, or otherwise unlawful or tortious material, including material harmful to children or that violates third party privacy rights or inconsistent with the generally accepted practices of the Internet community as reasonably determined by Cloud vLab; (iii) send or store material containing software viruses, worms, Trojan horses or other harmful computer code, files, scripts, agents, or programs; (iv) interfere with or disrupt the integrity or performance of the Service or the data contained therein; (v) attempt to gain unauthorized access to the Service or its related systems or networks; or (vi) enable, further, or participate in any unlawful activity. You may not use any part of the Service in connection with providing any website or service that is aimed at, directed to, or marketed to children under the age of 13. You acknowledge and agree that if Cloud vLab or any Lab Sponsor becomes aware or has reason to believe that you are engaging in any such prohibited activity, both have the right to immediately suspend and/or terminate your use of the Service.

(d) If you have a Creator role any use of the Lab Creation Service and the Resources must be limited to use for the sole purpose of completing or participating in Lab Services provided by your Lab Sponsor. The Resources may not be made available to or accessed by any third party other than your Lab Sponsor and/or any individuals acting on behalf of your Lab Sponsor. All software or other Content stored on the Resources may be deleted at any time by Cloud vLab. Cloud vLab makes no warranties or representations with respect to the performance, reliability, or functionality of the Lab Creation Service. All Content or other data stored on the Resources should be non-confidential and no warranty or representation is made with respect to the confidentiality or security of any Content stored on the Resources.

(e) The Lab Service and if you have a Creator role, Lab Creation Service right to use is non-transferable. Any Lab tokens you buy or any promotional tokens you’re given are for your individual use and cannot be resold or distributed.

(f) All rights not expressly granted to you are reserved by Cloud vLab and its licensors.

Your Responsibilities

You are responsible for all activity occurring through the use of the Service. You represent that you shall abide by all applicable local, state, national, and foreign laws and regulations in connection with your use of the Service, including, without limitation, those related to intellectual property and privacy (collectively, "Laws").

You will not obscure or contravene or attempt to obscure or contravene any notices of or attribution to Cloud vLab displayed within the Service that relate to Cloud vLab’s role as a service provider.

You will select and use a secure user password for your account and you agree not to share your password with any other party.

Commercial Activities Prohibited

The Resources may not be used for commercial advertising purposes or related promotional or commercial activities. If you have a Creator role, use of the Lab Creation Service is limited to the creation and testing of Content and related materials in connection with the Lab Service.

Your Information

You agree to provide Cloud vLab with your complete and accurate contact information, including, without limitation, when completing the account registration process. This information includes your first and last name, legal name or business name, country of residence, e-mail address, and password. You agree to update this information within fifteen (15) days of any change to it. If the contact information you have provided is false or fraudulent, Cloud vLab reserves the right to terminate your access to the Service in addition to any other legal remedies. Our collection, use, and storage of your personal information is subject to the terms and conditions of our Privacy Policy, which is set forth at http://qwiklab.com/privacy_policy.

Content; Ownership

This section is applicable to you only if you have a Creator role on Qwiklabs site or any other site provided by Cloud vLab in concert with your Lab Sponsor.

As between you and Cloud vLab, Content shall be the property of you. By posting, uploading, inputting, providing or submitting Content, y ou hereby grant to Cloud vLab and its affiliated companies, Agents and necessary sublicensees a worldwide, perpetual, royalty-free license to (i) copy, reproduce, edit, translate, reformat, store, display, distribute, and perform Content on or through the Service in order to provide the Service; (ii) use and analyze the Content in furtherance of Cloud vLab’s internal business purposes or otherwise for the purpose of providing the Service; (iii) disclose metrics regarding Content on an aggregated basis for marketing and business development purposes; (iv) publish your name in connection with your Content; and (v) sublicense such rights to any supplier or third party in relation to the operation of the Qwiklabs business including the Service. "Agents" include (i) service providers and related third parties that CloudvLab may hire to perform certain business-related functions and (ii) business partners and related third parties with which CloudvLab may have a contractual relationship with respect to the Service.

You acknowledge and agree that you have sole responsibility for ensuring that all Content submitted on or through the Service by you is compliant with the terms and conditions of this Agreement, all other terms of use agreements, disclaimers, and notices that may be displayed by Cloud vLab on or through the Service, and all Laws ("Applicable Terms"). You, and not Cloud vLab, shall have sole responsibility for the accuracy, quality, integrity, legality, reliability, appropriateness, and intellectual property ownership or right to use of all Content, and Cloud vLab shall not be responsible or liable for the deletion, correction, destruction, damage, loss, or failure to store any Content. Cloud vLab reserves the right to, without notice, withhold, remove, and/or discard Content not in compliance with the Applicable Terms, and to remove or disable any Resources or account not in compliance with the Applicable Terms.

Demonstration Accounts and Use

Cloud vLab may grant to certain persons or entities a limited-time demonstration account (“Demo Account”) to use the Service for the limited purpose of evaluating the Service for purchase. Any such Demo Account granted to you may be used only for the limited time period specified by Cloud vLab (the “Demo Period”) upon provision of the Demo Account login details to you. Any Demo Account may be revoked at any time and for any reason. All Content submitted by any user of a Demo Account will be deleted upon termination of the Demo Period. In addition to the terms and conditions of this Section 7, all terms and conditions of this Agreement shall apply to any use of the Service in connection with a Demo Account.

Intellectual PropertyCloud vLab Intellectual Property

Cloud vLab and its licensors, partners, or affiliates, where applicable, shall own all right, title, and interest, including, without limitation, all intellectual property rights in and to the Cloud vLab Technology. This Agreement is not a sale and does not convey to you any rights of ownership in or related to the Service, the Cloud vLab Technology or the intellectual property rights owned by Cloud vLab. The Cloud vLab name, the Qwiklabs trademark, and the other product names associated with the Service are trademarks of Cloud vLab, and no right or license is granted to use them.

Feedback

You hereby assign and agree to assign to Cloud vLab all right, title, and interest in and to any enhancement requests, recommendations, suggestions, comments, evaluations, ideas, or other information relating to the Service (“Feedback”) provided by you to Cloud vLab, including, but not limited to, all intellectual property rights embodied in such Feedback.

Modification of Terms

Cloud vLab reserves the right to modify this Agreement or its policies relating to the Service and other Applicable Terms, at any time, effective upon posting of an updated version of this Agreement, policies and/or other Applicable Terms on the Service. You are responsible for regularly reviewing this Agreement and such policies, the current version of which shall be made available as set forth herein through the Qwiklabs Site. If any change to this Agreement is not acceptable to you, your sole remedy is to terminate your use of the Service and any other rights under this Agreement. Any use of the Service after such publication shall constitute acceptance by you of such revised Agreement.

Term and TerminationTerm

This Agreement commences upon your acceptance of this Agreement by clicking “I Accept” in the sign-up process for the Service and shall continue until terminated (the “Term”). You acknowledge and agree that Cloud vLab or your Lab Sponsor may terminate and/or suspend your access to any portion of the Service for any reason or for no reason at all, in Cloud vLab’s sole discretion, without prior notice. You may terminate this Agreement at any time by discontinuing your use of the Service. For users of Demo Accounts, this Agreement shall terminate upon the expiration of the corresponding Demo Period. All other user accounts shall terminate upon the conclusion or withdrawal of the Lab Service by the Lab Sponsor.

Effects of Termination

Upon termination or expiration, your right to access or use Content shall immediately cease, and Cloud vLab shall have no obligation to retain copies of any Content or related data. Upon termination or expiration of this Agreement, the following provisions will survive in full force and effect: 6, 8, 10.2, 11, 12, 13 and 15, and any other clause or portion of a clause which, by its nature, is intended to survive termination or expiration of this Agreement.

Indemnification

You shall indemnify and hold Cloud vLab, its licensors, partners and each such party’s parent organizations, subsidiaries, affiliates, officers, directors, employees, attorneys, and agents harmless from and against any and all claims, demands, costs, damages, losses, liabilities, and expenses (including attorneys’ fees and costs) arising out of or in connection with: (i) any Content, including without limitation any claim alleging that use of any Content infringes or misappropriates the rights of, or has caused harm to, a third party; (ii) a breach or violation by you of any responsibilities, representations, covenants, or warranties under this Agreement and/or other Applicable Terms; or (iii) your use of the Resources. You agree that Cloud vLab’s licensors and partners shall be third party beneficiaries of your indemnification obligations hereunder.

Disclaimer of Warranties

You acknowledge and agree that by using the Service, you may be exposed to Sponsor Content that is offensive, indecent, or objectionable. You further acknowledge and agree that the Service and the Sponsor Content may contain errors or omissions. You acknowledge and agree that Cloud vLab does not screen or review published Sponsor Content on the Service to determine whether it contains false or defamatory material or material which is offensive, indecent, objectionable, or which contains errors or omissions. Under no circumstances will Cloud vLab be liable in any way for Sponsor Content, including, but not limited to, for any defamation, falsehoods, errors, or omissions in any such content, or for any loss or damage of any kind incurred as a result of the use or publication of any such Sponsor Content posted, emailed, or otherwise transmitted via the Service. Cloud vLab does not guarantee that any Sponsor Content will be to your satisfaction.

CLOUD VLAB AND ITS LICENSORS MAKE NO REPRESENTATION, WARRANTY, OR GUARANTY AS TO THE RELIABILITY, TIMELINESS, QUALITY, SUITABILITY, TRUTH, AVAILABILITY, ACCURACY, OR COMPLETENESS OF THE SERVICE OR ANY SPONSOR CONTENT. CLOUD VLAB AND ITS LICENSORS DO NOT REPRESENT OR WARRANT THAT (A) THE USE OF THE SERVICE WILL BE SECURE, TIMELY, UNINTERRUPTED, OR ERROR-FREE OR OPERATE IN COMBINATION WITH ANY OTHER HARDWARE, SOFTWARE, SYSTEM, OR DATA, (B) THE SERVICE WILL MEET YOUR REQUIREMENTS OR EXPECTATIONS, (C) ANY STORED DATA WILL BE ACCURATE OR RELIABLE, (D) THE QUALITY OF ANY PRODUCTS, SERVICES, INFORMATION, OR OTHER MATERIAL PURCHASED OR OBTAINED BY YOU THROUGH THE SERVICE WILL MEET YOUR REQUIREMENTS OR EXPECTATIONS, (E) ERRORS OR DEFECTS WILL BE CORRECTED, OR (F) THE SERVICE OR THE SERVER(S) THAT MAKE THE SERVICE AVAILABLE ARE FREE OF VIRUSES OR OTHER HARMFUL COMPONENTS. THE SERVICE AND ALL SPONSOR CONTENT ARE PROVIDED TO YOU STRICTLY ON AN "AS IS" BASIS. CLOUD VLAB AND ITS LICENSORS HEREBY DISCLAIM (TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW) ALL CONDITIONS, REPRESENTATIONS AND WARRANTIES, WHETHER EXPRESS, IMPLIED, STATUTORY OR OTHERWISE, INCLUDING, WITHOUT LIMITATION, ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT OF THIRD PARTY RIGHTS.

CLOUD VLAB’S SERVICES MAY BE SUBJECT TO LIMITATIONS, DELAYS, AND OTHER PROBLEMS INHERENT IN THE USE OF THE INTERNET AND ELECTRONIC COMMUNICATIONS. CLOUD VLAB IS NOT RESPONSIBLE FOR ANY DELAYS, DELIVERY FAILURES, OR OTHER DAMAGE RESULTING FROM SUCH PROBLEMS.

Limitation of Liability

IN NO EVENT SHALL CLOUD VLAB’S AGGREGATE LIABILITY ARISING WITH RESPECT TO OR IN CONNECTION WITH THIS AGREEMENT EXCEED THE AMOUNTS RECEIVED BY CLOUD VLAB AND ATTRIBUTABLE TO YOUR LAB SPONSOR’S RELATIONSHIP WITH US, IF ANY, IN THE THREE (3) MONTH PERIOD IMMEDIATELY PRECEDING THE EVENT UPON WHICH CLAIMS ARE BASED. IN NO EVENT SHALL CLOUD VLAB AND/OR ITS LICENSORS BE LIABLE TO ANYONE FOR ANY INDIRECT, PUNITIVE, SPECIAL, EXEMPLARY, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES OF ANY TYPE OR KIND (INCLUDING LOSS OF DATA, REVENUE, PROFITS, USE, OR OTHER ECONOMIC ADVANTAGE) ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE SERVICE, INCLUDING BUT NOT LIMITED TO THE USE OF OR INABILITY TO USE THE SERVICE, OR FOR ANY CONTENT OBTAINED FROM OR THROUGH THE SERVICE, ANY INTERRUPTION, INACCURACY, ERROR, OR OMISSION, REGARDLESS OF CAUSE, IN THE CONTENT, EVEN IF CLOUD VLAB OR ITS LICENSORS HAVE BEEN PREVIOUSLY ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Force Majeure

In no event shall Cloud vLab incur any liability to you or any End Users on account of any loss or damage resulting from any delay or failure to perform all or any part of this Agreement to the extent such delay or failure is caused by events, occurrences, or causes beyond the control and without negligence of Cloud vLab, including by not limited to acts of God, strikes, riots, acts of war, lockouts, earthquakes, fires, and explosions.

General

This Agreement shall be governed by Massachusetts law and controlling United States federal law, without regard to the choice or conflicts of law provisions of any jurisdiction, and any disputes, actions, claims, or causes of action arising out of or in connection with this Agreement or the Service shall be subject to the exclusive jurisdiction of the state and federal courts located in Massachusetts. If any provision of this Agreement is held by a court of competent jurisdiction to be invalid or unenforceable, then such provision(s) shall be construed, as nearly as possible, to reflect the intentions of the invalid or unenforceable provision(s), with all other provisions remaining in full force and effect. All notices to be delivered under this Agreement shall be sent via postal mail to the applicable party at the address notified by the applicable party in writing. Notice shall be deemed to have been given forty-eight (48) hours after the mailing of the written notice. No joint venture, partnership, employment, or agency relationship exists between you and Cloud vLab as a result of this agreement or use of the Service. The failure of either party to enforce any right or provision in this Agreement shall not constitute a waiver of such right or provision unless acknowledged and agreed to by the waiving party in writing. The section headings herein are included merely for convenience of reference, do not limit or affect any of the contents of this Agreement, and are not to be considered part of, or to be used in interpreting, this Agreement. Cloud vLab may assign this Agreement at any time without prior notice to you. You may not assign this Agreement without the prior written consent of Cloud vLab, which consent may be withheld by Cloud vLab at its sole discretion. Any purported assignment by you in violation of this section shall be void. This Agreement, which incorporates the Cloud vLab Privacy Policy comprises the entire agreement between you and Cloud vLab and governs your use of the Service, superseding all prior or contemporaneous negotiations, discussions or agreements, whether written or oral, between the Parties regarding the subject matter hereof.

Last Updated: September 1, 2015

Reviews

Amazing course . Some lectures should have had more exercises in lab for us to practise.Overall a very good course which provides a hands on experience in GCP

That was a great introduction to BigQuery.

I used it at work on few projects, but I didn't know it's that cool.

Thank you for such an eye opening experinece!

Curso bem legal para se conhecer mais a estrutura do BigQuery, capacidades de processamento e possibilidades de controle de acesso à dados!

I really appreciated all of the labs that went along with this course. They were detailed and thorough and led us through some advanced concepts in a digestible, understandable fashion.