Reply to thread

Message: <blockquote data-quote="csharp" data-source="post: 26718383" data-attributes="member: 560481"><h3>How to create Tables inside Tables using Nested Data Structure in Google BigQuery</h3><h4>In this article, I’ll guide you through the steps of creating a table inside a table using the Columnar Storage feature with Nested Data Structures in Google Big Query.</h4>The first thing to keep in mind is, In order to create a nested table, the two source tables must contain a defined relationship so that the items in one table can be related to the other table.A structure field can contain another structure or even an array of structures. Once you have created a structure, you can use the STRUCT function (Explained at the end of the article) or direct assignment statements to nest structures within existing structure fields.<img src="https://cdn-images-1.medium.com/max/720/1*aFHgqMKV3Ftlh7_yGBQYyA.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />Google BigQueryColumnar Format AKA the Capacitor in BigQueryMany databases store their data by rows, which look similar to a spreadsheet. All the data about a record is stored in one row. By contrast, a columnar database stores its data by columns, with the data for each column stored together.Take this data for example:<img src="https://cdn-images-1.medium.com/max/720/1*GGUDrgOBJAZniuRAr3G6ew.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />An example data table with three columns (<a href="https://blog.twitter.com/engineering/en_us/a/2013/dremel-made-simple-with-parquet.html" target="_blank">Source</a>)In a SQL or standard flat file database the data would be stored like this:<img src="https://cdn-images-1.medium.com/max/720/1*uU7C6NP505BH3ytLd7QNuw.png" alt="" class="fr-fic fr-dii fr-draggable " style="" /><a href="https://blog.twitter.com/engineering/en_us/a/2013/dremel-made-simple-with-parquet.html" target="_blank">Source</a>In a columnar database, the data would be stored like this:<img src="https://cdn-images-1.medium.com/max/720/1*bjuYsfh-rgtzn6C4Vwi0jg.png" alt="" class="fr-fic fr-dii fr-draggable " style="" /><a href="https://blog.twitter.com/engineering/en_us/a/2013/dremel-made-simple-with-parquet.html" target="_blank">Source</a>Let’s use some real time example with some dummy values for the below scenario:I want to store client ID, Name, Address and Mobile Number in a table called Clients. Take these data for an instance:100, Dylan Jay, 137 Cairns Street, 555-1212112, Tara Jay, 137 Dairns Road, 444-2222In a columnar database, those data will store like this:ID Number: {100,112}Name: {Dylan Jay,Tara Jay}Address: {137 Cairns Street, 137 Dairns Road}Mobile: {555-1212,444-2222}The main idea behind using columnar storage is to handle large amount of data for analysis, Such as business Intelligence Systems. By storing all the records for one field together, columnar databases can query and perform analysis on that similar data far quicker than row-based databases.In BigQuery, We called this columnar format as Capacitor.To store in a columnar format we first need to describe the data structures using a schema.There is no need for any other complex types like Maps, List or Sets as they all can be mapped to a combination of repeated fields and groups.Let’s say we have three tables as A,B and C<img src="https://cdn-images-1.medium.com/max/720/1*_hERUI9K0J2RHBi-9Q5PnQ.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />Table A Schema<img src="https://cdn-images-1.medium.com/max/720/1*e5Hzreu-3BPM5sl-czBMzw.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />Table B Schema (B is a child of A)<img src="https://cdn-images-1.medium.com/max/720/1*Jlkdi7D4aU-XDIhHDz6Vgg.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />Table C Schema (C is a child of A)OK, so both these kid tables want to be grouped with the parent A, And the combination of the table should look like this:<img src="https://cdn-images-1.medium.com/max/720/1*fnl7NxAPGDSEONqpdWFGsQ.png" alt="" class="fr-fic fr-dii fr-draggable " style="" /><a href="https://www.jayasekara.blog/2021/07/how-to-create-tables-inside-tables-bigquery-columnar-format-capacitors-gcp.html" target="_blank">Read full article here</a></blockquote>

Verification: Haya warak paha keeyada? (haya wadi kireema paha)

Top Bottom