--- title: 'Google BigQuery Docs Digest' url: 'https://johnlinhart.com/blog/google-bigquery-notes' markdown: 'https://johnlinhart.com/blog/google-bigquery-notes.md' date: '2016-11-28' description: "I'm writing down BigQuery specifics which I find different and interesting from my MySql knowledge. Hopefully, this digest will help someone else trying to learn and use BigQuery in the future. Web vi" taxonomy: category: - Development - Google - Database tag: - developer - technology archives_month: - nov_2016 archives_year: - '2016' --- # Google BigQuery Docs Digest [ Blog ](https://johnlinhart.com/) Google BigQuery Docs Digest 28 *Nov* #### [Google BigQuery Docs Digest](https://johnlinhart.com/blog/google-bigquery-notes) [developer](https://johnlinhart.com/tag:developer) [technology](https://johnlinhart.com/tag:technology) ![](https://johnlinhart.com/images/9/3/e/c/2/93ec2a3b74aa1f710bc65f664811c8d6aa9d75c2-bigquery.png) I'm writing down BigQuery specifics which I find different and interesting from my MySql knowledge. Hopefully, this digest will help someone else trying to learn and use BigQuery in the future. ## Web view Something similart to PhpMyAdmin for BigQuery where we can view the schema, data and test queries: [https://bigquery.cloud.google.com/dataset](https://bigquery.cloud.google.com/dataset "This link will take you to an external web site. We are not responsible for their content.") ## Partitioned tables Partitioned tables should make the queries faster and cheaper. > A partitioned table is a special table that is divided into segments, called partitions, that make it easier to manage and query your data. BigQuery offers date-partitioned tables, which means that the table is divided into a separate partition for each date. To create a date-partitioned table. *[source](https://cloud.google.com/bigquery/docs/partitioned-tables "This link will take you to an external web site. We are not responsible for their content.")* > If you create a date-partitioned table, BigQuery automatically loads data into a date-based partition of the table. You can then reduce the number of bytes processed by restricting your queries to specific partitions in the table. *[source](https://cloud.google.com/bigquery/docs/creating-partitioned-tables "This link will take you to an external web site. We are not responsible for their content.")* ## Wildcard tables Queries can be done through several tables with the wildcard: `FROM bigquery-public-data.noaa_gsod.gsod*` > Wildcard tables are useful when a dataset contains multiple, similarly named tables that have compatible schemas. Typically, such datasets contain tables that each represent data from a single day, month, or year. *[source](https://cloud.google.com/bigquery/docs/wildcard-tables "This link will take you to an external web site. We are not responsible for their content.")* ## Query modes *Synchronous* - wait for the result *Asynchronous* - returns without any result, then check periodically if the result is available *Interactive* - query is executed ASAP *Batch* - starts the query as soon as idle resources are available. Batch queries don't count towards your concurrent rate limit, which can make it easier to start many queries at once. *[More info](https://cloud.google.com/bigquery/batch "This link will take you to an external web site. We are not responsible for their content.")* ### Query caching > BigQuery caches query results on a best-effort basis for increased performance. Results are cached for approximately 24 hours and cache lifetimes are extended when a query returns a cached result. ### Query limits - Maximum tables per query: 1,000 - Maximum query length: 256 KB *[source](https://cloud.google.com/bigquery/querying-data "This link will take you to an external web site. We are not responsible for their content.")* ## Table Decorators > Normally, BigQuery performs a full column scan when running a query. You can use table decorators to perform a more cost-effective query of a subset of your data. ### To get a snapshot of the table at one hour ago `SELECT COUNT(*) FROM [data-sensing-lab:gartner.seattle@-3600000]` ### To get table data added between one hour and half an hour ago `SELECT COUNT(*) FROM [data-sensing-lab:gartner.seattle@-3600000--1800000]` *[source](https://cloud.google.com/bigquery/table-decorators "This link will take you to an external web site. We are not responsible for their content.")* ## Special SQL query keywords There are some special BigQuery SQL functions. ### FLATTEN > When you query nested data, BigQuery automatically flattens the table data for you. For example `citiesLived.place` in the SQL query will be returned in the flat table as `citiesLived_place`. > Although BigQuery can automatically flatten nested fields, you may need to explicitly call FLATTEN when dealing with more than one repeated field. ### WITHIN > The WITHIN keyword specifically works with aggregate functions to aggregate across children and repeated fields within records and nested fields. When you specify the WITHIN keyword, you need to specify the scope over which you want to aggregate *[source](https://cloud.google.com/bigquery/docs/data#bigquery-paging-php "This link will take you to an external web site. We are not responsible for their content.")* ## Modifying or deleting rows or data > BigQuery tables are append-only. The query language does not currently support either updating or deleting data. In order to update or delete data, you must delete the table, then recreate the table with new data. Alternatively, you could write a query that modifies the data and specify a new results table. *[source](https://cloud.google.com/bigquery/docs/tables "This link will take you to an external web site. We are not responsible for their content.")* ## Jobs > Jobs are used to start all potentially long-running actions, for instance: queries, table import, and export requests. Shorter actions, such as list or get requests, are not managed by a job resource. *[source](https://cloud.google.com/bigquery/docs/managing_jobs_datasets_projects "This link will take you to an external web site. We are not responsible for their content.")* ## Datasets > A dataset is a grouping mechanism that holds zero or more tables. Datasets are the lowest level unit of access control; you cannot control access at the table level. *[source](https://cloud.google.com/bigquery/docs/managing_jobs_datasets_projects "This link will take you to an external web site. We are not responsible for their content.")* ## Projects > A project holds a group of datasets. Projects are created and managed in the APIs console. Jobs are billed to the project to which they are assigned. *[source](https://cloud.google.com/bigquery/docs/managing_jobs_datasets_projects "This link will take you to an external web site. We are not responsible for their content.")* ## RECORD field type All I could find in the Google docs about this field is: > A collection of one or more other fields. *[source](https://cloud.google.com/bigquery/data-types "This link will take you to an external web site. We are not responsible for their content.")* If the Record field should contain multiple rows, it must be REPEATED *[source](http://stackoverflow.com/questions/32020714/what-does-repeated-field-in-google-bigquery-mean "This link will take you to an external web site. We are not responsible for their content.")* ## Denormalization > In BigQuery, you typically should denormalize the data structure in order to enable super-fast querying. While JOINs on small datasets are possible with BigQuery, they're not as performant as a denormalized structure. *[source](https://cloud.google.com/bigquery/preparing-data-for-loading "This link will take you to an external web site. We are not responsible for their content.")* [ Next Post](https://johnlinhart.com/blog/my-mautic-3-wishlist) [Previous Post ](https://johnlinhart.com/blog/uh-oh-mautic-upgrade-was-not-successful) #### Projects I'm part of **[Mautic](http://mautic.org)** Open source marketing automation software. **[Calculoid.com](http://calculoid.com)** Web app which lets you create a calculator. **[eScope.cz](http://escope.cz/en)** My Joomla! articles and extensions. **[CRMery](http://crmery.com)** CRM as Joomla component. **[Constimator.com](http://constimator.com)** Web app which lets you calculate cost of building. #### For stalkers [](https://github.com/escopecz "GitHub") [](https://twitter.com/jan_linhart "Twitter") [](http://stackoverflow.com/users/2421302/escopecz "Stack Overflow") [](https://cz.linkedin.com/pub/jan-linhart/52/a03/80a "LinkedIn") [](http://magazine.joomla.org/component/k2/itemlist/user/5517-janlinhart "Joomla Magazine") #### Popular Tags [mautic](https://johnlinhart.com/tag:mautic) [developer](https://johnlinhart.com/tag:developer) [technology](https://johnlinhart.com/tag:technology) [web development](https://johnlinhart.com/tag:web%20development) [notes](https://johnlinhart.com/tag:notes) [composer](https://johnlinhart.com/tag:composer) [marketing](https://johnlinhart.com/tag:marketing) [personal](https://johnlinhart.com/tag:personal) [cronfig](https://johnlinhart.com/tag:cronfig) [hobby](https://johnlinhart.com/tag:hobby) [webexpo](https://johnlinhart.com/tag:webexpo) [freelance](https://johnlinhart.com/tag:freelance) [cost](https://johnlinhart.com/tag:cost) [calculator](https://johnlinhart.com/tag:calculator) [rate](https://johnlinhart.com/tag:rate) [calculoid](https://johnlinhart.com/tag:calculoid) [wordpress](https://johnlinhart.com/tag:wordpress) [grav](https://johnlinhart.com/tag:grav) [pagekit](https://johnlinhart.com/tag:pagekit) [cms](https://johnlinhart.com/tag:cms) [john](https://johnlinhart.com/tag:john) [name](https://johnlinhart.com/tag:name) #### Archives - [ 1 November 2024 ](https://johnlinhart.com/blog/archives_month:nov_2024) - [ 1 October 2024 ](https://johnlinhart.com/blog/archives_month:oct_2024) - [ 5 March 2023 ](https://johnlinhart.com/blog/archives_month:mar_2023) - [ 1 January 2020 ](https://johnlinhart.com/blog/archives_month:jan_2020) - [ 2 December 2019 ](https://johnlinhart.com/blog/archives_month:dec_2019) - [ 1 September 2019 ](https://johnlinhart.com/blog/archives_month:sep_2019) - [ 1 August 2018 ](https://johnlinhart.com/blog/archives_month:aug_2018) - [ 1 November 2016 ](https://johnlinhart.com/blog/archives_month:nov_2016) - [ 1 September 2015 ](https://johnlinhart.com/blog/archives_month:sep_2015) - [ 1 June 2015 ](https://johnlinhart.com/blog/archives_month:jun_2015) - [ 1 May 2015 ](https://johnlinhart.com/blog/archives_month:may_2015) - [ 1 April 2015 ](https://johnlinhart.com/blog/archives_month:apr_2015) #### Syndicate [ Atom 1.0](https://johnlinhart.com/blog.atom) [ RSS](https://johnlinhart.com/blog.rss) --- ## Navigation - Parent: [Blog](https://johnlinhart.com/index.md) - Previous: [My Mautic 3 wish list](https://johnlinhart.com/blog/my-mautic-3-wishlist.md) - Next: [Uh Oh, Mautic upgrade was not successful](https://johnlinhart.com/blog/uh-oh-mautic-upgrade-was-not-successful.md)