{"id":92507,"date":"2023-12-29T07:05:42","date_gmt":"2023-12-29T12:35:42","guid":{"rendered":"https:\/\/www.whizlabs.com\/blog\/?p=92507"},"modified":"2023-12-29T07:05:42","modified_gmt":"2023-12-29T12:35:42","slug":"big-query-guided-labs","status":"publish","type":"post","link":"https:\/\/www.whizlabs.com\/blog\/big-query-guided-labs\/","title":{"rendered":"Basic SQL Functions in BigQuery | Guided Labs"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">Google BigQuery, a serverless data warehouse solution, is designed to be both cost-effective and highly scalable, empowering businesses to swiftly and efficiently analyze extensive datasets.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Furthermore, it incorporates a set of SQL functions that prove to be invaluable in simplifying the handling of typical business scenarios.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">In this article, you will be guided through <\/span><a href=\"https:\/\/www.whizlabs.com\/google-cloud-sql-deep-dive\/\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">BigQuery SQL functions<\/span><\/a><span style=\"font-weight: 400;\"> and gain insights on how to create <strong>basic SQL functions<\/strong> in BigQuery in real-time settings.<\/span><\/p>\n<p><strong>Let\u2019s dig in!<\/strong><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_76 ez-toc-wrap-left counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ea7e02;color:#ea7e02\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ea7e02;color:#ea7e02\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.whizlabs.com\/blog\/big-query-guided-labs\/#What_is_BigQuery\" >What is BigQuery?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.whizlabs.com\/blog\/big-query-guided-labs\/#Key_Features_of_BigQuery\" >Key Features of BigQuery<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.whizlabs.com\/blog\/big-query-guided-labs\/#What_are_basic_SQL_functions_in_BigQuery\" >What are basic SQL functions in BigQuery?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.whizlabs.com\/blog\/big-query-guided-labs\/#How_to_access_the_Whizlabs_hands-on_labs_for_doing_BigQuery_Operations\" >How to access the Whizlabs hands-on labs for doing BigQuery Operations?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.whizlabs.com\/blog\/big-query-guided-labs\/#Limitations_of_using_the_BigQuery_functions\" >Limitations of using the BigQuery functions<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.whizlabs.com\/blog\/big-query-guided-labs\/#FAQs\" >FAQs<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.whizlabs.com\/blog\/big-query-guided-labs\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"What_is_BigQuery\"><\/span><strong>What is BigQuery?<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400;\">Google&#8217;s enterprise <a href=\"https:\/\/cloud.google.com\/bigquery?hl=en\" target=\"_blank\" rel=\"nofollow noopener\">data warehouse<\/a>, BigQuery, was created to democratize large-scale data analysis. This platform is specifically designed to <strong>manage extensive datasets, handling tasks<\/strong> such as analyzing log data from numerous retail systems or processing IoT data generated by millions of vehicle sensors globally.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Key_Features_of_BigQuery\"><\/span><strong>Key Features of BigQuery<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-92510\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Key-Features-of-BigQuery-Info-scaled.webp\" alt=\"Key-Features-of-BigQuery\" width=\"2560\" height=\"2240\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Key-Features-of-BigQuery-Info-scaled.webp 2560w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Key-Features-of-BigQuery-Info-300x263.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Key-Features-of-BigQuery-Info-1024x896.webp 1024w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Key-Features-of-BigQuery-Info-768x672.webp 768w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Key-Features-of-BigQuery-Info-1536x1344.webp 1536w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Key-Features-of-BigQuery-Info-2048x1792.webp 2048w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Key-Features-of-BigQuery-Info-150x131.webp 150w\" sizes=\"(max-width: 2560px) 100vw, 2560px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">BigQuery incorporates contemporary features that not only boost productivity but also prioritize security and integrity.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Cost-Effective:<\/b><span style=\"font-weight: 400;\"> BigQuery presents a reasonable pricing model, particularly for beginners who can utilize certain operations within the free tier. Charges apply for specific operations and the use of BigQuery storage APIs, with pricing based on two components: Analysis and Storage.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Accelerated SQL Processing: <\/b><span style=\"font-weight: 400;\">BigQuery supports ANSI SQL techniques and employs a second sub-query to enhance concurrency, resulting in higher throughput. The utilization of ANSI SQL also contributes to faster response times.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Data Encryption &amp; Security: <\/b><span style=\"font-weight: 400;\">Given that BigQuery operates in the cloud, there is inherent concern about data and personal information security. To address this, the platform encrypts data and integrates with Cloud Identity and Access Management for enhanced security.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Efficient Storage:<\/b><span style=\"font-weight: 400;\"> BigQuery excels in data storage efficiency. Once data is loaded into BigQuery, the platform automatically processes it, streamlining the storage process.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>BigQuery Machine Learning:<\/b><span style=\"font-weight: 400;\"> The concept of BigQuery Machine Learning empowers data scientists and analysts to undertake various tasks, including model building, training, and testing. Using SQL query syntax, users can directly retrieve results from BigQuery.<\/span><\/li>\n<\/ul>\n<blockquote><p>Also Read :\u00a0<a href=\"https:\/\/www.whizlabs.com\/blog\/google-bigquery\/\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">Google BigQuery and Its Role in Big Data Analytics<\/span><\/a><\/p><\/blockquote>\n<h3><span class=\"ez-toc-section\" id=\"What_are_basic_SQL_functions_in_BigQuery\"><\/span><strong>What are basic SQL functions in BigQuery?<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400;\">Here are some basic SQL Functions that exist in BigQuery:<\/span><\/p>\n<h4><b>1) Aggregation Functions<\/b><\/h4>\n<p><span style=\"font-weight: 400;\">Aggregation functions are frequently utilized in SQL, combining all rows within a group into a singular value. Here are some commonly used aggregation functions:<\/span><\/p>\n<table>\n<tbody>\n<tr>\n<td><b>Aggregate Functions<\/b><\/td>\n<td><b>Description<\/b><\/td>\n<\/tr>\n<tr>\n<td><b>COUNT()<\/b><\/td>\n<td><span style=\"font-weight: 400;\">Represent a number of rows in a table or view.<\/span><\/td>\n<\/tr>\n<tr>\n<td><b>SUM()<\/b><\/td>\n<td><span style=\"font-weight: 400;\">Calculate the sum of all values.<\/span><\/td>\n<\/tr>\n<tr>\n<td><b>AVG()<\/b><\/td>\n<td><span style=\"font-weight: 400;\">Calculate the average of a set of values.<\/span><\/td>\n<\/tr>\n<tr>\n<td><b>MIN()<\/b><\/td>\n<td><span style=\"font-weight: 400;\">Represent the minimum value.<\/span><\/td>\n<\/tr>\n<tr>\n<td><b>MAX()<\/b><\/td>\n<td><span style=\"font-weight: 400;\">Represent the maximum value.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><b>2) Window (Analytical) Functions<\/b><\/h4>\n<p><span style=\"font-weight: 400;\">Window functions in SQL evaluate values for a specific window or set of rows within a table, as opposed to the entire table. The OVER() clause is used to define the window for these functions. Here are some commonly used window functions:<\/span><\/p>\n<table>\n<tbody>\n<tr>\n<td><b>Window Function<\/b><\/td>\n<td><b>Description<\/b><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">RANK()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Assigns a unique rank to each record based on a specified set of criteria.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">ROW_NUMBER()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Assigns a distinct row number to each record.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">NTILE()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Determines the percentile to which a specific row belongs.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">LAG()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Retrieves data from the preceding row within the same result set without requiring SQL joins.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">LEAD()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Retrieves data from the next row within the same result set without necessitating SQL joins.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><b>3) String Functions<\/b><\/h4>\n<p><span style=\"font-weight: 400;\">String functions in SQL are valuable for manipulating and handling text fields. They are particularly useful when you need to modify text, format it, or concatenate values from different columns.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0Here are some commonly used string functions:<\/span><\/p>\n<table>\n<tbody>\n<tr>\n<td><b>String Function<\/b><\/td>\n<td><b>Description<\/b><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">CONCAT()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Joins two words or strings together.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">CONCAT_WS()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Joins two words or strings together using a specified symbol as a concatenating symbol.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">FORMAT()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Displays a number in a specified format.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">RTRIM()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Separates the given substring from the rest of the string.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">SUBSTR()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Extracts a substring from a string at a certain position.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">REVERSE()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Reverses a string.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><b>4) Date Functions<\/b><\/h4>\n<p><span style=\"font-weight: 400;\">When working with dates in your dataset, date functions become valuable for various operations. Here are some commonly used date functions:<\/span><\/p>\n<table>\n<tbody>\n<tr>\n<td><b>Date Function<\/b><\/td>\n<td><b>Description<\/b><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">DATE()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Creates a DATE field from integer values representing the year, month, and day.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">PARSE_DATE()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Creates a DATE object from a string representation of a date.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">DATE_DIFF()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Counts the number of days, weeks, months, or years that have elapsed between two dates.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">CURRENT_DATE()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Returns the current date in the specified or default timezone.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">FORMAT_DATE()<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Formats the DATE field to the given format string.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><span class=\"ez-toc-section\" id=\"How_to_access_the_Whizlabs_hands-on_labs_for_doing_BigQuery_Operations\"><\/span>How to access the Whizlabs hands-on labs for doing BigQuery Operations?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>In the Whizlabs hands-on labs, click on <a href=\"https:\/\/www.whizlabs.com\/labs\/basic-sql-functions-in-bigquery\" target=\"_blank\" rel=\"noopener\">Basic SQL Functions in BigQuery<\/a> labs. Once you find the desired labs page, follow the steps as outlined in the lab task.<\/p>\n<h4><strong>Start the Lab Environment<\/strong><\/h4>\n<p>Click the &#8220;Start Lab&#8221; button situated in the right sidebar of the labs page. This action will set up a dedicated GCP (Google Cloud Platform) environment, configuring all the necessary resources and permissions specific to the lab&#8217;s requirements.<\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-92654\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/start.webp\" alt=\"\" width=\"1084\" height=\"386\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/start.webp 1628w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/start-300x107.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/start-1024x365.webp 1024w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/start-768x274.webp 768w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/start-1536x547.webp 1536w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/start-150x53.webp 150w\" sizes=\"(max-width: 1084px) 100vw, 1084px\" \/><\/p>\n<h4><strong>Access the Google Sign-In Page<\/strong><\/h4>\n<p>Once the lab setup is finalized, the &#8220;Open Console&#8221; button will become active. Proceed to click the &#8220;Open Console&#8221; button, which will direct you to the Google Sign-In page. <span style=\"font-size: 16px;\">It&#8217;s advisable to use the Incognito or private browsing mode in your web browser to ensure a seamless sign-in process and avoid potential issues.<\/span><\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-92655\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/open-console.webp\" alt=\"open console\" width=\"1068\" height=\"272\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/open-console.webp 1848w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/open-console-300x76.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/open-console-1024x261.webp 1024w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/open-console-768x196.webp 768w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/open-console-1536x391.webp 1536w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/open-console-150x38.webp 150w\" sizes=\"(max-width: 1068px) 100vw, 1068px\" \/><\/p>\n<h4><strong>Retrieve and Utilize Lab Credentials<\/strong><\/h4>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>Return to the Lab Document or instructional guide within Whizlabs.<\/li>\n<li>You&#8217;ll find a section named &#8220;Lab Credentials.&#8221;<\/li>\n<\/ul>\n<\/li>\n<li style=\"list-style-type: none;\"><img decoding=\"async\" class=\"alignnone wp-image-92656\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/credentials.png\" alt=\"credentials\" width=\"1041\" height=\"484\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/credentials.png 1855w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/credentials-300x139.png 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/credentials-1024x476.png 1024w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/credentials-768x357.png 768w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/credentials-1536x714.png 1536w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/credentials-150x70.png 150w\" sizes=\"(max-width: 1041px) 100vw, 1041px\" \/>\n<ul>\n<li>Within this section, there will be a highlighted Email and Password designated for console access.<\/li>\n<li>Use the provided &#8220;Copy&#8221; option next to the credentials to conveniently copy them.<\/li>\n<li>Head back to the Google Sign-In page and paste (or input) the copied Email and Password to securely sign in to the GCP console.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h4><strong>Creating a BigQuery Dataset<\/strong><\/h4>\n<p><span style=\"font-weight: 400;\">To create a BigQuery dataset, start by clicking on the hamburger icon in the top left corner, then navigate to BigQuery under the Analytics section.<\/span><\/p>\n<p><span style=\"font-weight: 400;\"><img decoding=\"async\" class=\"size-full wp-image-92513 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Analytics.webp\" alt=\"Analytics\" width=\"298\" height=\"401\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Analytics.webp 298w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Analytics-223x300.webp 223w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Analytics-150x202.webp 150w\" sizes=\"(max-width: 298px) 100vw, 298px\" \/><\/span><span style=\"font-weight: 400;\"> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Now, select the kebab icon on the Project ID and click Create Dataset.<\/span><span style=\"font-weight: 400;\"><br \/>\n<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92514 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/create-data-set.webp\" alt=\"create-data-set\" width=\"460\" height=\"97\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/create-data-set.webp 460w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/create-data-set-300x63.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/create-data-set-150x32.webp 150w\" sizes=\"(max-width: 460px) 100vw, 460px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Set the Dataset ID as &#8220;whizlabs_dataset&#8221; and choose the location as &#8220;us(multiple regions in the United States).<\/span><span style=\"font-weight: 400;\"><br \/>\n<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92515 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/project-id.webp\" alt=\"project-id\" width=\"607\" height=\"157\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/project-id.webp 607w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/project-id-300x78.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/project-id-150x39.webp 150w\" sizes=\"(max-width: 607px) 100vw, 607px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">After creating the dataset, download a file for future steps.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92516 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/data-location.webp\" alt=\"\" width=\"477\" height=\"65\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/data-location.webp 477w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/data-location-300x41.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/data-location-150x20.webp 150w\" sizes=\"(max-width: 477px) 100vw, 477px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">To create a table within the dataset, click on the kebab icon for the dataset, then select &#8220;Create Table.&#8221; Choose the upload option under Source, browse to upload the downloaded file, and name the table &#8220;whizlabs_table.&#8221;<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92517 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/source.webp\" alt=\"\" width=\"651\" height=\"233\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/source.webp 651w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/source-300x107.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/source-150x54.webp 150w\" sizes=\"(max-width: 651px) 100vw, 651px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Under Schema Click on Add Field.<\/span><span style=\"font-weight: 400;\"><br \/>\n<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92518 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/schema.webp\" alt=\"\" width=\"226\" height=\"192\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/schema.webp 226w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/schema-150x127.webp 150w\" sizes=\"(max-width: 226px) 100vw, 226px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Enter the details: Field name as Symbol, Type as String, and left other options as default.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\"><img decoding=\"async\" class=\"aligncenter wp-image-92519 size-full\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/symbol.webp\" alt=\"\" width=\"513\" height=\"158\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/symbol.webp 513w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/symbol-300x92.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/symbol-150x46.webp 150w\" sizes=\"(max-width: 513px) 100vw, 513px\" \/><span style=\"font-size: 16px; font-weight: 400;\">Similarly, fill in other fields to ensure that the schema looks like the one below, and click on Create Table.<\/span><\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92520 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/schema-auto-detect.webp\" alt=\"\" width=\"656\" height=\"273\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/schema-auto-detect.webp 656w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/schema-auto-detect-300x125.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/schema-auto-detect-150x62.webp 150w\" sizes=\"(max-width: 656px) 100vw, 656px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">After creating the table successfully, click on the table to learn more details about the table.<\/span><span style=\"font-weight: 400;\"><br \/>\n<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92521 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/whizlabs_table.webp\" alt=\"\" width=\"484\" height=\"317\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/whizlabs_table.webp 484w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/whizlabs_table-300x196.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/whizlabs_table-150x98.webp 150w\" sizes=\"(max-width: 484px) 100vw, 484px\" \/><\/p>\n<h4><strong>Querying the dataset<\/strong><\/h4>\n<p><span style=\"font-weight: 400;\">For querying the dataset, go to the Editor section, enter the queries, and click on Run.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92522 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/editor2.webp\" alt=\"\" width=\"617\" height=\"107\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/editor2.webp 617w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/editor2-300x52.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/editor2-150x26.webp 150w\" sizes=\"(max-width: 617px) 100vw, 617px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">BigQuery has advanced capabilities such as an inbuilt validator to validate the syntax and errors in your query.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92523 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/run1.webp\" alt=\"\" width=\"487\" height=\"60\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/run1.webp 487w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/run1-300x37.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/run1-150x18.webp 150w\" sizes=\"(max-width: 487px) 100vw, 487px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Another great function of BigQuery is that it tells about the number of Bytes it has to read to derive the output. Note: In BigQuery you are billed based on bytes read and storage.<\/span><span style=\"font-weight: 400;\"><br \/>\n<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92524 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/run2.webp\" alt=\"\" width=\"613\" height=\"78\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/run2.webp 613w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/run2-300x38.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/run2-150x19.webp 150w\" sizes=\"(max-width: 613px) 100vw, 613px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Let&#8217;s run our first query using the SELECT, FROM, WHERE, and LIMIT clauses, copy and paste the below query and click on Run. Replace &lt;Project-ID&gt; with the Project ID mentioned in the login panel, &lt;Dataset-ID&gt; with the Dataset that you created above, and &lt;Table-ID&gt; with the name of the table that you noted before.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">You will receive output like the one below. Note: It returned only 100 rows as defined in the LIMIT.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92525 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results.webp\" alt=\"\" width=\"548\" height=\"284\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results.webp 548w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results-300x155.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results-150x78.webp 150w\" sizes=\"(max-width: 548px) 100vw, 548px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Run the Query on specific Columns using the SELECT, FROM, and WHERE clauses, copy and paste the below query and click on Run. Replace &lt;Project-ID&gt; with the Project ID mentioned in the login panel, &lt;Dataset-ID&gt; with the Dataset that you created above, and &lt;Table-ID&gt; with the name of the table that you noted before.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">After executing the query, you&#8217;ll observe results where the OrderID could be either 123 or 3b93b000-9214-4159-b6f6-5dba5921e80a. Since there is no OrderID with the value 123, a row with the OrderID as the other one is returned.<\/span><\/p>\n<p><span style=\"font-weight: 400;\"><img decoding=\"async\" class=\"size-full wp-image-92526 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results1.webp\" alt=\"\" width=\"482\" height=\"92\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results1.webp 482w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results1-300x57.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results1-150x29.webp 150w\" sizes=\"(max-width: 482px) 100vw, 482px\" \/><\/span><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Now, let&#8217;s use the AND operator instead of OR. Copy and paste the following query, replacing &lt;Project-ID&gt; with the Project ID from the login panel, &lt;Dataset-ID&gt; with the created Dataset, and &lt;Table-ID&gt; with the noted table name.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Upon running this query, you will receive an output indicating that no rows match both conditions.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92527 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results2.webp\" alt=\"\" width=\"639\" height=\"148\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results2.webp 639w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results2-300x69.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results2-150x35.webp 150w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">To sort the output, use the ORDER BY keyword in the following query. Copy and paste it, replacing &lt;Project-ID&gt;, &lt;Dataset-ID&gt;, and &lt;Table-ID&gt; with the appropriate values.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The result will display sorted data based on the specified column, in this case, TransactTime, in descending order.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92528 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results3.webp\" alt=\"\" width=\"501\" height=\"256\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results3.webp 501w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results3-300x153.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-results3-150x77.webp 150w\" sizes=\"(max-width: 501px) 100vw, 501px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Execute the provided query after replacing the placeholders &lt;Project-ID&gt; with the relevant Project ID from the login panel, &lt;Dataset-ID&gt; with the Dataset created earlier, and &lt;Table-ID&gt; with the noted name of the table. The query groups entries based on similar TradeDate values. It&#8217;s important to note that the name &#8220;f0_&#8221; is assigned by BigQuery unless an alias is specified in our query.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92529 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-final-results.webp\" alt=\"\" width=\"439\" height=\"427\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-final-results.webp 439w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-final-results-300x292.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/query-final-results-150x146.webp 150w\" sizes=\"(max-width: 439px) 100vw, 439px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">To assign a custom name to our output column using the AS keyword, execute the provided query after replacing the placeholders: &lt;Project-ID&gt; with the relevant Project ID from the login panel, &lt;Dataset-ID&gt; with the previously created Dataset, and &lt;Table-ID&gt; with the noted name of the table. Then, click on Run.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The output you receive will look similar to the example below. It&#8217;s worth noting that the column name is now more readable.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">BigQuery is equipped with advanced features, including the ability to save queries. Simply click on the Save icon located in the top panel and select &#8220;Save query.&#8221;<\/span><\/p>\n<p><span style=\"font-weight: 400;\"><img decoding=\"async\" class=\"size-full wp-image-92531 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/save-query.webp\" alt=\"\" width=\"406\" height=\"247\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/save-query.webp 406w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/save-query-300x183.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/save-query-150x91.webp 150w\" sizes=\"(max-width: 406px) 100vw, 406px\" \/><\/span> <span style=\"font-weight: 400;\"> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span><\/p>\n<p><span style=\"font-weight: 400;\">Enter any name for the query and set the visibility to Private for now.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92532 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/visibilty-personal.webp\" alt=\"\" width=\"567\" height=\"231\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/visibilty-personal.webp 567w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/visibilty-personal-300x122.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/visibilty-personal-150x61.webp 150w\" sizes=\"(max-width: 567px) 100vw, 567px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">After saving it, you can share it using a link. Click on the Share icon from the top panel and select Get Link.<\/span><span style=\"font-weight: 400;\"><br \/>\n<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92533 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/get-link-query.webp\" alt=\"\" width=\"608\" height=\"170\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/get-link-query.webp 608w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/get-link-query-300x84.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/get-link-query-150x42.webp 150w\" sizes=\"(max-width: 608px) 100vw, 608px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">A Pop-up will show up, Click on Confirm and you will get a link in return and you can share publicly.<\/span><span style=\"font-weight: 400;\"><br \/>\n<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92534 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/update-query-visibility.webp\" alt=\"\" width=\"619\" height=\"115\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/update-query-visibility.webp 619w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/update-query-visibility-300x56.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/update-query-visibility-150x28.webp 150w\" sizes=\"(max-width: 619px) 100vw, 619px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Execute the provided query after replacing the placeholders: &lt;Project-ID&gt; with the relevant Project ID from the login panel, &lt;Dataset-ID&gt; with the previously created Dataset, and &lt;Table-ID&gt; with the noted name of the table. After running the query, the output will indicate that no count of OrderID can be more than 1.<\/span><\/p>\n<p><span style=\"font-weight: 400;\"><img decoding=\"async\" class=\"size-full wp-image-92535 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/lastrun.webp\" alt=\"\" width=\"569\" height=\"225\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/lastrun.webp 569w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/lastrun-300x119.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/lastrun-150x59.webp 150w\" sizes=\"(max-width: 569px) 100vw, 569px\" \/><\/span><\/p>\n<p><span style=\"font-weight: 400;\">You can examine how the process unfolded in the backend by clicking on &#8220;Execution Details&#8221;<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92536 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/execution-graph.webp\" alt=\"\" width=\"511\" height=\"194\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/execution-graph.webp 511w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/execution-graph-300x114.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/execution-graph-150x57.webp 150w\" sizes=\"(max-width: 511px) 100vw, 511px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">The Execution Details are shown in a diagram under the Execution Graph section.<\/span><span style=\"font-weight: 400;\"><br \/>\n<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-92538 aligncenter\" src=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/output.webp\" alt=\"\" width=\"375\" height=\"296\" srcset=\"https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/output.webp 375w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/output-300x237.webp 300w, https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/output-150x118.webp 150w\" sizes=\"(max-width: 375px) 100vw, 375px\" \/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Limitations_of_using_the_BigQuery_functions\"><\/span><strong>Limitations of using the BigQuery functions<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400;\">Let&#8217;s examine some challenges you may encounter when working with User Defined Functions (UDFs):<\/span><\/p>\n<p><b>Limitations with Document-Oriented Objects<\/b><\/p>\n<p><span style=\"font-weight: 400;\">UDFs that require Document-Oriented objects, such as Windows, Node, and Document User Defined Functions, may face limitations as these objects may not be supported. This can impact the functionality of certain UDFs that rely on these specific object types.<\/span><\/p>\n<p><b>Dependency on Native Code in JavaScript Functions<\/b><\/p>\n<p><span style=\"font-weight: 400;\">JavaScript Functions within UDFs that depend on native code may encounter failures. Dependencies on specific native code components can lead to compatibility issues, affecting the execution and reliability of the UDFs.<\/span><\/p>\n<p><b>Case Sensitivity Constraints<\/b><\/p>\n<p><span style=\"font-weight: 400;\">UDFs operate in a case-sensitive manner. This means that they distinguish between uppercase and lowercase letters. While this can be advantageous in some scenarios, it also imposes limitations on applicability rates and the usage of quotes, requiring careful consideration to avoid potential issues.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Understanding these challenges is crucial for effectively leveraging User Defined Functions and ensuring their seamless integration into your applications.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"FAQs\"><\/span><strong>FAQs<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><b>Is BigQuery and SQL the same?<\/b><\/p>\n<p><span style=\"font-weight: 400;\">No, BigQuery and SQL are not the same. They have some differences: Google BigQuery allocates computing resources automatically when you need them. It auto-scales up and down based on the data loaded. On the other hand, SQL Server doesn&#8217;t have an auto-scalable option, and hence it needs human intervention to scale up and down based upon the data loads.<\/span><\/p>\n<p><b>Difference between Google Bigquery and MySQL.<\/b><\/p>\n<table>\n<tbody>\n<tr>\n<td><b>Aspect<\/b><\/td>\n<td><b>Google BigQuery<\/b><\/td>\n<td><b>MySQL<\/b><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">Scalability and Performance<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Designed for massive datasets, scalable to petabytes<\/span><\/td>\n<td><span style=\"font-weight: 400;\">More suitable for smaller to medium-sized workloads, may face performance challenges with extremely large datasets.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">Data Organization<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Columnar storage model for efficient analysis<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Row-based storage model for fast read and write access, may limit performance for complex analytical queries.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">Query Language<\/span><\/td>\n<td><span style=\"font-weight: 400;\">BigQuery SQL with extensions for complex querying<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Traditional SQL with MySQL-specific extensions<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">Data Processing Paradigm<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Serverless computing, automatic resource management<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Requires manual management of resources and optimization<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">Advanced Analytical Capabilities<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Built-in machine learning, geographic functions, integration with GCP services<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Capable of handling complex queries; may require additional plugins for advanced analytics.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">Cost Structure<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Pay-as-you-go based on data processed and stored<\/span><\/td>\n<td><span style=\"font-weight: 400;\">Typically licensed based on subscription or usage, including server capacity and features utilized.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><b>What type of database is Google BigQuery?<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Google BigQuery is categorized as a business intelligence and online analytical processing (OLAP) system. It is a hybrid solution that incorporates SQL dialects and is built upon Google&#8217;s internal column-based data processing technology known as &#8220;Dremel.&#8221;<\/span><\/p>\n<blockquote><p>Know More :\u00a0<a href=\"https:\/\/www.whizlabs.com\/blog\/what-is-bigquery\/\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">What is BigQuery?<\/span><\/a><\/p><\/blockquote>\n<h3><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span><strong>Conclusion<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400;\">Hope this article provides an introduction to Google BigQuery, offering insights into its features. It comprehensively covers various aspects related to <strong>BigQuery Functions, including their types, components, and limitations<\/strong>.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">It also includes practical examples to demonstrate the usage of User-Defined BigQuery Functions, providing you with a real-time experience.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">To make your hands dirty by exploring any other Google Cloud platforms, try our <\/span><a href=\"https:\/\/www.whizlabs.com\/labs\/library\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">Google hands-on labs<\/span><\/a><span style=\"font-weight: 400;\"> and <\/span><a href=\"https:\/\/www.whizlabs.com\/labs\/sandbox\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">Google Sandboxes<\/span><\/a><span style=\"font-weight: 400;\">.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Google BigQuery, a serverless data warehouse solution, is designed to be both cost-effective and highly scalable, empowering businesses to swiftly and efficiently analyze extensive datasets. Furthermore, it incorporates a set of SQL functions that prove to be invaluable in simplifying the handling of typical business scenarios. In this article, you will be guided through BigQuery SQL functions and gain insights on how to create basic SQL functions in BigQuery in real-time settings. Let\u2019s dig in! What is BigQuery? Google&#8217;s enterprise data warehouse, BigQuery, was created to democratize large-scale data analysis. This platform is specifically designed to manage extensive datasets, handling [&hellip;]<\/p>\n","protected":false},"author":390,"featured_media":92509,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"default","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[5080],"tags":[5081],"class_list":["post-92507","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bigquery","tag-bigquery"],"uagb_featured_image_src":{"full":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI.webp",1280,720,false],"thumbnail":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI-150x150.webp",150,150,true],"medium":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI-300x169.webp",300,169,true],"medium_large":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI-768x432.webp",768,432,true],"large":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI-1024x576.webp",1024,576,true],"1536x1536":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI.webp",1280,720,false],"2048x2048":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI.webp",1280,720,false],"profile_24":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI.webp",24,14,false],"profile_48":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI.webp",48,27,false],"profile_96":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI.webp",96,54,false],"profile_150":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI.webp",150,84,false],"profile_300":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI.webp",300,169,false],"tptn_thumbnail":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI-250x250.webp",250,250,true],"web-stories-poster-portrait":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI-640x720.webp",640,720,true],"web-stories-publisher-logo":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI-96x96.webp",96,96,true],"web-stories-thumbnail":["https:\/\/www.whizlabs.com\/blog\/wp-content\/uploads\/2023\/12\/Basic-SQL-Functions-in-BigQuery-Guided-Labs-FI-150x84.webp",150,84,true]},"uagb_author_info":{"display_name":"Neha Biradar","author_link":"https:\/\/www.whizlabs.com\/blog\/author\/neha-biradar\/"},"uagb_comment_info":5,"uagb_excerpt":"Google BigQuery, a serverless data warehouse solution, is designed to be both cost-effective and highly scalable, empowering businesses to swiftly and efficiently analyze extensive datasets. Furthermore, it incorporates a set of SQL functions that prove to be invaluable in simplifying the handling of typical business scenarios. In this article, you will be guided through BigQuery&hellip;","_links":{"self":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts\/92507","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/users\/390"}],"replies":[{"embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/comments?post=92507"}],"version-history":[{"count":7,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts\/92507\/revisions"}],"predecessor-version":[{"id":92657,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts\/92507\/revisions\/92657"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/media\/92509"}],"wp:attachment":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/media?parent=92507"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/categories?post=92507"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/tags?post=92507"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}