{"id":328,"date":"2021-07-15T17:11:43","date_gmt":"2021-07-15T15:11:43","guid":{"rendered":"https:\/\/stage.usercentrics.com\/?post_type=knowledge&p=10990"},"modified":"2024-09-03T19:39:22","modified_gmt":"2024-09-03T17:39:22","slug":"optimizing-your-bigquery-tables-using-clustering","status":"publish","type":"knowledge","link":"https:\/\/usercentrics.com\/us\/knowledge-hub\/optimizing-your-bigquery-tables-using-clustering\/","title":{"rendered":"Optimizing your BigQuery tables using clustering"},"content":{"rendered":"\n\n

Besides the table partitioning to improve performance and costs in BigQuery, there is also another technique available called clustering<\/em>.<\/p>\n

A little heads-up:<\/strong> Clustering and partitioning don’t preempt each other. And in most cases, it makes sense to use both in conjunction.<\/p>\n\n\n

What are clustered tables?<\/h2>\n

Using clustered tables, BigQuery automatically organizes and colocates related data based on one or multiple selected columns. Important to note here is that the order of columns you are specifying for clustering is essential. But more on this later.<\/span><\/p>\n

Since BigQuery does almost everything automatically \u2013\u2013 in regards to clustering \u2013\u2013 the only thing you have to do is to tell BigQuery on which columns and in which order you want BigQuery to cluster your data.<\/span><\/p>\n

BigQuery then stores the data in multiple blocks inside the internal BigQuery storage. Storing the data in pre-sorted blocks means that BigQuery can \u2013\u2013 based on your query filter clause \u2013\u2013 use these blocks to prevent scans of unnecessary data.<\/span><\/p>\n

According to the official docs using clustering will significantly improve performance when the scanned table \u2013\u2013 or table partition \u2013\u2013 exceeds 1 GB.<\/span><\/p>\n

BigQuery offers automatic re-clustering, which means that even when you add new data to the tables, BigQuery will automatically sort them into the existing blocks.<\/span><\/p>\n

A small example of a clustered table compared to a non-clustered one<\/b><\/p>\n

You can see a table with randomly inserted data on the left side and an on the <\/span>name<\/span><\/em> and <\/span>event<\/span><\/em> column clustered table on the right side in the following image.<\/span><\/p>\n

As you can see, rows with the same <\/span>name<\/span><\/em> are located right beneath each other. And in the range of the same <\/span>name’s<\/span><\/em> the<\/span><\/i> rows with the same <\/span>event<\/span><\/em> are also colocated.<\/span><\/p>\n

\"IMAGE_CLUSTERED_TABLE\"<\/p>\n

Taking this example, you can already imagine the possible improvement when querying data. As an example, let’s assume that you want to query all values where the <\/span>name<\/span><\/em> equals <\/span>“1”<\/span><\/em>.<\/span><\/p>\n

Without clustering, BigQuery would have to scan the whole table because each row could contain the value <\/span>“1”<\/span><\/em> in its <\/span>name<\/span><\/em> column.<\/span><\/p>\n

With clustering, BigQuery knows that only the first three columns will match this filter condition. That results in 50% fewer scanned columns since all the possible resulting rows are right beneath each other, and BigQuery can skip the other 50%. Also, 50% fewer checked rows also means 50% fewer costs since BigQuery charges you on the bytes read. Even though this might not be 100% accurate, as we will see later, it’s accurate enough to get the point of clustering data in tables.<\/span><\/p>\n

The process of skipping specific blocks on scanning is also called <\/span>block pruning<\/span><\/em>. Even though BigQuery doesn’t necessarily create one block per one distinct value in the clustered column. The amount of blocks BigQuery creates depends highly on the amount of data stored. For 1 MB of data, even though it might contain 100 different values in the clustered column, BigQuery won’t create 100 blocks and save 99% of bytes read when filtering for one specific value. But the more (distinct) data is available inside the table; the more effective clustering will become. As already mentioned above, Google suggested a minimum table size of 1 GB to see significant improvements using clustering. But we will see in the next section that improvements will start way earlier.<\/span><\/p>\n\n\n

Hands-On<\/h2>\n

Now it’s time for some actual hands-on coding and seeing some real-world examples. I split the hands-on part into four small portions.<\/span><\/p>\n

    \n
  1. Easy script to create some test data we then can import and query.<\/span><\/li>\n
  2. Import the test data into BigQuery as a normal and as a clustered table.<\/span><\/li>\n
  3. Query both tables with the same query and compare results.<\/span><\/li>\n
  4. Making an assumption on Google’s clustering block size based on a real example.<\/span><\/li>\n<\/ol>\n

    Create test data<\/b><\/p>\n

    We will orient ourselves by the first image in this article to create test data, where we showed how BigQuery organizes a clustered table compared to a normal one.<\/span><\/p>\n

    That means that we will have two columns. One column is representing a <\/span>name<\/span><\/em>, the other one representing an <\/span>event<\/span><\/em>.<\/span><\/p>\n

    Unlike the last article<\/a><\/span>, we will dynamically create the data via a JavaScript script and write it into a CSV file. We need a minimum amount of data \u2013\u2013 we will create 5,000,000 rows resulting in a 28 MB CSV file \u2013\u2013 so we can see some effect of the clustering.<\/span><\/p>\n

    So, let’s check out the actual code for generating our test data.<\/span><\/p>\n

    \"Code_csv#4\"

    Code_csv#4<\/p><\/div>\n

    As you can see, we are using the <\/span>csv-writer<\/span><\/em> NPM module for writing the CSV file. So make sure you install it via <\/span>npm i –save csv-writer<\/span><\/em> before running the script.<\/span><\/p>\n

    The script itself should be easy to understand. First, we define some constants that determine the number of rows, amount of different names, and events that the script will generate randomly. Due to simplicity’s sake, I used plain numbers to represent the names.<\/span><\/p>\n

    When you use my default values, the script will produce a 28 MB CSV file containing exactly 5,000,000 rows of data \u2013 plus one additional for the column header \u2013 with 1,000 different names and ten different events. The script defines a name as a value between 0 and 999 and an event as a value between 0 and 9.<\/span><\/p>\n

    Of course, the produced CSV file will contain a little bit of different data each time you run it. But due to the high number of occurrences, you can expect each name to occur approximately 5.000 times.<\/span><\/p>\n

    Now that the test data is ready, it’s time to ship it to BigQuery.<\/span><\/p>\n

    Create a new table with data via CSV import<\/b><\/p>\n

    If you look at the following snippet, you will see many similarities to the last article’s CSV import script<\/a><\/span>. And true, most of it is the same code. The main difference is the fourth parameter of the <\/span>importCSV()<\/span><\/em> method. Before we used <\/span>timePartitioning<\/span><\/em>,<\/span><\/i> we now use <\/span>clustering<\/span><\/em> to define the fields that BigQuery should cluster.<\/span><\/p>\n

    \"Code_Import-clustered_csv\"

    Code_Import-clustered_csv<\/p><\/div>\n

    In the script, we define the clustering fields as <\/span>[“name”, “event”]<\/span><\/em>, which means BigQuery first clusters for the <\/span>name<\/span><\/em> and then for the <\/span>event<\/span><\/em> column.<\/span><\/p>\n

    After you run the script successfully \u2013\u2013 it might take a bit to import the ~28 MB CSV file to BigQuery and wait for the clustering to finish \u2013\u2013 we can verify via the CLI if BigQuery added clustering correctly.<\/span><\/p>\n

    bq show –format=prettyjson\u00a0<\/span><\/em><\/p>\n

    YOUR_GCP_PROJECT:YOUR_DATASET_ID.my_clustered_table<\/span><\/em><\/p>\n

    This command will print the metadata of your BigQuery table to the CLI. There you should find some entry for <\/span>clustering<\/span><\/i> looking like the following:<\/span><\/p>\n

    “clustering”: {<\/span><\/em><\/p>\n

    \u00a0 \u00a0 \u00a0“fields”: [<\/span><\/em><\/p>\n

    \u00a0 \u00a0 \u00a0 \u00a0“name”,<\/span><\/em><\/p>\n

    \u00a0 \u00a0 \u00a0 \u00a0“event”<\/span><\/em><\/p>\n

    \u00a0 \u00a0 \u00a0]<\/span><\/em><\/p>\n

    },<\/span><\/em><\/p>\n

    If you run the same command for the <\/span>non_clustered_table<\/span><\/em>, you should not see any entry for <\/span>clustering<\/span><\/em>.<\/span><\/p>\n

    Run queries on the imported data<\/b><\/p>\n

    Now that we imported all the data to the normal and the clustered table and verified that BigQuery enabled the clustering correctly on our clustered table, it’s time to run some queries against it to compare their statistics.<\/span><\/p>\n

    \"\" \"Code<\/p>\n

    Again, we are using quite a lot of code from the previous article<\/a> <\/span>because most of it is independent of any clustering or partitioning.<\/span><\/p>\n

    Compared to before, we added some <\/span>printConfig<\/span><\/em> to our <\/span>runQuery<\/span><\/em> method to define what it should print \u2014 either the query result or the job statistics, or both.<\/span><\/p>\n

    In our standard search query \u2013 <\/span>createQuery<\/span><\/em> \u2013 we are filtering the rows by a specific <\/span>name<\/span><\/i> with the value <\/span>“1”<\/span><\/em>. In theory, this should affect around 5,000 rows because we have created 5,000.000 rows with 1,000 different names resulting in approximately 5,000 entries per name.<\/span><\/p>\n

    We added one additional query \u2013 <\/span>createCountQuery <\/span><\/em>\u2013 that we will run and print its result. We run this query to verify our assumption that around 5.000 rows should be affected by our filer condition.<\/span><\/p>\n

    Query results<\/b><\/p>\n

    When you run the above script, you should get a similar result to the following:<\/span><\/p>\n

    \"index<\/p>\n

    Because we don’t have a lot of data here, the <\/span>Time<\/span> taken<\/span><\/em> is similar in both queries. As explained in the beginning, significant performance improvements will be visible when approaching the table size of 1 GB and more.<\/span><\/p>\n

    But what you can see is that the <\/span>Rows<\/span> read<\/span><\/em> and the <\/span>Bytes processed<\/span><\/em> \u2013 which Google charges you for \u2013 has significantly decreased.<\/span><\/p>\n

    From 5,000,000 rows read and 64,450,259 bytes processed, we decreased down to 384,853 rows read and 4,947,899 bytes processed. That is a theoretically cost saving of <\/span>92.3%<\/b>! Just because we used clustering.<\/span><\/p>\n

    You can also see this big difference in the following graph:<\/span><\/p>\n

    \"Rows-Read-and-Bytes-Processed\"

    Rows-Read-and-Bytes-Processed<\/p><\/div>\n

    The attentive audience will also notice that the <\/span>Bytes billed<\/span><\/em> are not 92.3% smaller, even though one could expect this. And yes, absolutely right. But that is because Google charges a minimum of 10 MB per query regardless of the actual bytes processed. When the <\/span>Bytes processed<\/span><\/em> are higher than 10 MB, the <\/span>Bytes billed<\/span><\/em> will be almost always the same as the <\/span>Bytes processed<\/span>.<\/span><\/em><\/p>\n

    Google’s clustering block size, an assumption<\/b><\/p>\n

    Besides this, you might also notice that the <\/span>Rows read<\/span><\/em> count is way higher than the 5,000 rows we expect to only scan for our query. That is due to some Google internal configuration of BigQuery where a cluster range’s minimum size exists. Even though Google’s “magic” is not only about the plain size of the colocated data.<\/span><\/p>\n

    You could assume that when you increase the amount of data up to a certain point, the <\/span>Rows read<\/span><\/em> should have the same value as the affected rows. Since we saw above that even though only 5,000 rows should be affected \u2013 the query read 384,853 rows \u2013 we could think that somewhere around this value, there could be some “magic number”.\u00a0<\/span><\/p>\n

    As a small example \u2013 I don’t want to go too deep into this topic yet \u2013 I changed our above test data set like the following:<\/span><\/p>\n