Select partitioning and clustering keys for BigQuery tables when migrating from Netezza

Published in

Google Cloud - Community

9 min readJun 5, 2020

When migrating a data warehouse to BigQuery one of the key parts is to select partitioning and clustering strategy for your tables. It’s important to come up with the strategy at the very beginning when your tables are still small because changing partitioning or clustering schema requires rebuilding the tables.

To add partitioning to a table you will need to recreate it. Although adding the clustering doesn’t require rebuilding the table you may still consider this option. In BigQuery, automatic re-clustering works only for new data which is inserted after the clustering has been applied to the table. So, the data that initially existed in the table before it was clustered would stay intact by the re-clustering auto process.

Since Netezza doesn’t have such concepts as partitioning, clustering, or indexing, it’s not possible to automatically determine a corresponding partitioning and clustering schema in BigQuery.

Note that Netezza distribution columns are not good candidates for partitioning or clustering keys in BQ because they serve different purposes. The main purpose of Netezza distribution is to uniformly spread data across nodes to maximize the parallel query execution. It doesn’t work for data pruning. In contrast, in BQ partitioning and clustering helps to reduce the query cost by reducing the number of scanned bytes.

So, good key candidates should have the following characteristics:

They should be often used in WHERE/HAVING clauses to filter the rows.
They should not be frequently updated.
A partitioning key should be a DATE/TIMESTAMP or INTEGER column. A clustering key can also have one of BOOL, NUMERIC, CHAR, VARCHAR, NCHAR, or NVARCHAR data types.
They should have at least two unique values, otherwise there would be nothing to prune. Note that it’s possible to combine multiple unique values into a single partition by using the integer partition key that defines a range of values or by truncating a date key to month/year using DATE_TRUNC(). In BigQuery, the maximum number of partitions per partitioned table cannot exceed 4000.
BigQuery stores data in files that have a fixed minimum size. This size may change with a new release. A suitable estimate would be 512 MB. So, each BQ partition/cluster should be at least 512 MB.

You can get partitioning and clustering candidates by analysing Netezza statistics and query history tables.

List large tables

You can start from a simple query which lists large tables in your database. Later, you will add more to this query to identify the best key candidates. In Netezza, you can list the large tables in YOUR_DATABASE ADMIN schema using the following query:

SELECT 
  _V_TABLE.database as "Database Name",
  _V_TABLE.schema as "Schema Name",
  _V_TABLE.tablename as "Table Name",  
  used_bytes/pow(1024, 3) as "Used GB"
FROM _v_table_storage_stat stat
INNER JOIN _V_TABLE on (stat.objid = _V_TABLE.objid)
WHERE 
  "Used GB" > 1
  and _V_TABLE.objtype='TABLE'
  and _V_TABLE.database = 'YOUR_DATABASE'
  and _V_TABLE.schema = 'ADMIN'
ORDER BY "Used GB" desc, "Table Name";

The results will look like:

Use Netezza statistic to get information about table columns

Next, you can enhance the above query and use Netezza statistics to get information about columns in your large tables. Your goal is to identify good partitioning and clustering keys, so select columns that:

Have DATE/TIMESTAMP, INTEGER, BOOL, NUMERIC, CHAR, VARCHAR, NCHAR, or NVARCHAR data types. This is done by adding a filter on _v_relation_column.atttypid.
Have at least two unique values. This is implemented by a filter on _v_relation_column.atttdisperson where disperson measures the number of distinct values in a column.

Before running the next query, make sure that table statistics is up-to-date. Adding the column statistics to the first query, you’ll get:

SELECT          
  -- Tables:
  _V_TABLE.DATABASE as "Database Name",
  _V_TABLE.schema as "Schema Name",
  _V_TABLE.TABLENAME as "Table Name",
  _V_TABLE.reltuples as "# of Rows",
  round(stat.used_bytes/pow(1024, 3), 3) as "Used GB",  -- Columns:
  _v_relation_column.attname as "Column Name",
  case _v_relation_column.atttypid
    WHEN 20 THEN 'BIGINT'
    WHEN 21 THEN 'SMALLINT'
    WHEN 23 THEN 'INTEGER'
    WHEN 1082 THEN 'DATE'
    WHEN 1184 THEN 'TIMESTAMP'
    WHEN 2500 THEN 'BYTEINT'
    WHEN 16 THEN 'BOOLEAN'
    WHEN 1700 THEN 'NUMERIC'
    WHEN 18 THEN 'CHAR'
    WHEN 1042 THEN 'CHARACTER(' || _v_relation_column.atttypmod -12 || ')'
    WHEN 1043 THEN 'VARCHAR(' || _v_relation_column.atttypmod -12 || ')'
    WHEN 2522 THEN 'NCHAR(' || _v_relation_column.atttypmod -12 || ')'
    WHEN 2530 THEN 'NVARCHAR(' || _v_relation_column.atttypmod -12 || ')'
  end as "Column Type",  -- Statistics Details
  CASE WHEN       _v_statistic.recent IS NULL THEN 'not maintained      '
       ELSE
       CASE
            WHEN  _v_statistic.recent =    85 THEN         'Full              '
            WHEN  _v_statistic.recent =   341 THEN         'Express           '
            WHEN  _v_statistic.recent =   149 THEN         'Basic             '
            WHEN  _v_statistic.recent =     1 THEN         'Full Min/Max OK   '
            WHEN  _v_statistic.recent =   257 THEN         'Express Min/Max OK'
            WHEN  _v_statistic.recent =   129 THEN         'Basic Min/Max OK  '
            WHEN  _v_statistic.recent =     0 THEN         'Full Outdated     '
            WHEN  _v_statistic.recent =   256 THEN         'Express Outdated  '
            WHEN  _v_statistic.recent =   128 THEN         'Basic Outdated    '
            WHEN  _v_statistic.recent =   169 THEN         'Min/Max Only      '
            WHEN  _v_statistic.recent =   170 THEN         'Unavailable       '
            WHEN  _v_statistic.recent = 16554 THEN         'Unavailable       '
            ELSE  SUBSTRING ('flags=' || _v_statistic.recent || '                    ', 1, 20)
       END END as "Statistics",  -- Column stats:
  SUBSTRING(NVL(_v_statistic.loval,'') || '               ', 1, 15)   || ' ' as "Minimum Value",  SUBSTRING(NVL(_v_statistic.hival,'') || '               ', 1, 15)         as "Maximum Value",  CASE WHEN     _v_relation_column.attdispersion =  0
       THEN    '                      '
       WHEN     _v_relation_column.attdispersion = -1
       THEN    ' 100% Unique          '
       ELSE    TO_CHAR(  (CAST((1.0/_v_relation_column.attdispersion) AS BIGINT)), ' 999,999,999,999,999 ' )
  END as "# of Unique Values",  CASE WHEN    _v_statistic.nullfrac = 0
       THEN    '                    '
       ELSE    TO_CHAR(  (CAST((RELTUPLES * _v_statistic.nullfrac) AS BIGINT)), '999,999,999,999,999' )
  END as "# of NULLs",  CASE WHEN    _v_relation_column.attdispersion = -1
       THEN    1
       ELSE    round(_V_TABLE.reltuples * (1 - _v_statistic.nullfrac) * _v_relation_column.attdispersion, 3)
  END as "# of Rows in Cluster",round((stat.used_bytes / pow(1024, 3)) / CASE _v_relation_column.attdispersion WHEN -1 THEN _V_TABLE.reltuples ELSE 1.0 / _v_relation_column.attdispersion END, 3) as "Cluster Size GB",round(stat.used_bytes * _v_statistic.nullfrac / pow(1024, 3), 3) as "NULL-Cluster Size GB"
         
FROM _v_relation_column
inner join _V_TABLE on 
  _v_relation_column.objid = _V_TABLE.OBJID
inner join _v_table_storage_stat stat on
  stat.objid = _V_TABLE.OBJID
left join _v_statistic on 
  _v_relation_column.objid  = _v_statistic.objid    AND
  _v_relation_column.attnum = _v_statistic.attnum
WHERE   
  "Used GB" > 1
  and _V_TABLE.objtype='TABLE' 
  and _V_TABLE.DATABASE = 'YOUR_DATABASE'
  and _V_TABLE.schema = 'ADMIN'
  and _v_relation_column.atttypid IN (20, 21, 23, 1082, 1184, 2500, 16, 1700, 18, 1042, 1043, 2522, 2530)
  and _v_relation_column.attdispersion <= 0.5
ORDER BY   
  "Used GB" desc,
  "Table Name",
  _v_relation_column.attnum;

Here are the results:

The above query needs to include some additional information in order to identify good partitioning/clustering key candidates. In particular you need to identify columns that are referenced the most in your production workload.

Gather historical information about column references in SQL statements

You can enable Netezza history to gather information about column references in SQL statements. You can get this information from $v_hist_column_access_stats (NUM_WHERE andNUM_HAVING columns). Additionally, the column should not be frequently updated, that is NUM_UPDATED should be small. You can compute the number of filter and update references as a fraction of all column references.

In BigQuery you can partition a table by DATE/TIMESTAMP and additionally specify four clustering columns. Only a partitioned table can be clustered. So, add column reference statistics to the previous query and select the top five columns ordered by the number of references. First list partitioning key candidates, then clustering key candidates. If your history database is called histdb.histown you’ll get:

SELECT          
  -- Tables:
  _V_TABLE.DATABASE as "Database Name",
  _V_TABLE.schema as "Schema Name",
  _V_TABLE.TABLENAME as "Table Name",
  _V_TABLE.reltuples as "# of Rows",
  round(stat.used_bytes/pow(1024, 3), 3) as "Used GB",  -- Columns:
  _v_relation_column.attname as "Column Name",
  case _v_relation_column.atttypid
    WHEN 20 THEN 'BIGINT'
    WHEN 21 THEN 'SMALLINT'
    WHEN 23 THEN 'INTEGER'
    WHEN 1082 THEN 'DATE'
    WHEN 1184 THEN 'TIMESTAMP'
    WHEN 2500 THEN 'BYTEINT'
    WHEN 16 THEN 'BOOLEAN'
    WHEN 1700 THEN 'NUMERIC'
    WHEN 18 THEN 'CHAR'
    WHEN 1042 THEN 'CHARACTER(' || _v_relation_column.atttypmod -12 || ')'
    WHEN 1043 THEN 'VARCHAR(' || _v_relation_column.atttypmod -12 || ')'
    WHEN 2522 THEN 'NCHAR(' || _v_relation_column.atttypmod -12 || ')'
    WHEN 2530 THEN 'NVARCHAR(' || _v_relation_column.atttypmod -12 || ')'
  end as "Column Type",  -- Statistics Details
  CASE WHEN       _v_statistic.recent IS NULL THEN 'not maintained      '
       ELSE
       CASE
            WHEN  _v_statistic.recent =    85 THEN         'Full              '
            WHEN  _v_statistic.recent =   341 THEN         'Express           '
            WHEN  _v_statistic.recent =   149 THEN         'Basic             '
            WHEN  _v_statistic.recent =     1 THEN         'Full Min/Max OK   '
            WHEN  _v_statistic.recent =   257 THEN         'Express Min/Max OK'
            WHEN  _v_statistic.recent =   129 THEN         'Basic Min/Max OK  '
            WHEN  _v_statistic.recent =     0 THEN         'Full Outdated     '
            WHEN  _v_statistic.recent =   256 THEN         'Express Outdated  '
            WHEN  _v_statistic.recent =   128 THEN         'Basic Outdated    '
            WHEN  _v_statistic.recent =   169 THEN         'Min/Max Only      '
            WHEN  _v_statistic.recent =   170 THEN         'Unavailable       '
            WHEN  _v_statistic.recent = 16554 THEN         'Unavailable       '
            ELSE  SUBSTRING ('flags=' || _v_statistic.recent || '                    ', 1, 20)
       END END as "Statistics",  -- Column stats:
  SUBSTRING(NVL(_v_statistic.loval,'') || '               ', 1, 15)   || ' ' as "Minimum Value",  SUBSTRING(NVL(_v_statistic.hival,'') || '               ', 1, 15)         as "Maximum Value",  CASE WHEN     _v_relation_column.attdispersion =  0
       THEN    '                      '
       WHEN     _v_relation_column.attdispersion = -1
       THEN    ' 100% Unique          '
       ELSE    TO_CHAR(  (CAST((1.0/_v_relation_column.attdispersion) AS BIGINT)), ' 999,999,999,999,999 ' )
  END as "# of Unique Values",  CASE WHEN    _v_statistic.nullfrac = 0
       THEN    '                    '
       ELSE    TO_CHAR(  (CAST((RELTUPLES * _v_statistic.nullfrac) AS BIGINT)), '999,999,999,999,999' )
  END as "# of NULLs",  CASE WHEN    _v_relation_column.attdispersion = -1
       THEN    1
       ELSE    round(_V_TABLE.reltuples * (1 - _v_statistic.nullfrac) * _v_relation_column.attdispersion, 3)
  END as "# of Rows in Cluster",round((stat.used_bytes / pow(1024, 3)) / CASE _v_relation_column.attdispersion WHEN -1 THEN _V_TABLE.reltuples ELSE 1.0 / _v_relation_column.attdispersion END, 3) as "Cluster Size GB",round(stat.used_bytes * _v_statistic.nullfrac / pow(1024, 3), 3) as "NULL-Cluster Size GB"--Reference counts:
round((hist.NUM_WHERE + hist.NUM_HAVING) / (hist.refs - NUM_GENSTATS), 3) as "Fraction of WHERE/HAVING Refs",round(hist.NUM_UPDATED / (hist.refs - NUM_GENSTATS), 3) as "Fraction of UPDATE Refs"
         
FROM _v_relation_column
inner join _V_TABLE on 
  _v_relation_column.objid = _V_TABLE.OBJID
inner join _v_table_storage_stat stat on
  stat.objid = _V_TABLE.OBJID
left join _v_statistic on 
  _v_relation_column.objid  = _v_statistic.objid    AND
  _v_relation_column.attnum = _v_statistic.attnum
left join histdb.histown."$v_hist_column_access_stats" hist on
  _V_TABLE.DATABASE = hist.DBNAME AND
  _V_TABLE.schema = hist.schemaname AND
  _V_TABLE.TABLENAME = hist.TABLENAME AND
  _v_relation_column.attname = hist.COLUMNNAME
WHERE   
  "Used GB" > 1
  and _V_TABLE.objtype = 'TABLE' 
  and _V_TABLE.DATABASE = 'YOUR_DATABASE'
  and _V_TABLE.schema = 'ADMIN'
  and _v_relation_column.atttypid IN (20, 21, 23, 1082, 1184, 2500, 16, 1700, 18, 1042, 1043, 2522, 2530)
  and _v_relation_column.attdispersion <= 0.5
ORDER BY   
  "Used GB" desc,
  "Table Name",
  "Fraction of WHERE/HAVING Refs" desc,
  "Fraction of UPDATE Refs",
  _v_relation_column.attnum;

Here are the results:

First, for each table select a partitioning key. That is the top DATE/TIMESTAMP/INTEGER column. Consider the rest of the columns and select the clustering keys.

Below is the definition of the last three columns in the result set. Assuming that the data is distributed uniformly, if you use “Column Name” as the top level partitioning/clustering key each cluster would have:

# of Rows in Cluster — the approximate number of rows in each cluster,
Cluster Size GB — the approximate size of each cluster,
NULL-Cluster Size GB — the approximate size of the cluster for which Column Name is NULL.

Note that NULL values do not contribute to the column dispersion statistics computed by Netezza. So, the result of # of Rows in Cluster was adjusted to account for that. Additionally, column NULL-Cluster Size GB computes how much disk space rows with NULL partition key consume.

Note, that the query doesn’t limit the number of listed columns. If you have wide tables and the query returns an unmanageable set of columns you can always limit the results using RANK() as shown below and selecting the top 6 columns for each table.

RANK() OVER (
  PARTITION BY _V_TABLE.OBJID 
  ORDER BY 
    round((hist.NUM_WHERE + hist.NUM_HAVING) / (hist.refs - NUM_GENSTATS), 3) desc,
    round(hist.NUM_UPDATED / (hist.refs - NUM_GENSTATS), 3)
)

Summarizing the steps

Review the last query results and select the best partitioning key and then clustering key candidates from the suggested columns.

Also, you can partition and cluster on the same column which can be particularly useful for time series data whose granularity is less than a day. Note, that if you don’t want to use partitions but still want to use clustering, you can create a table with only one partition.

If needed, merge several key values to a single partition if a default split produces partitiones less than 512 MB or the number of partitions exceeds 4000. Note, that if a partition spans several cluster values, then data for each clustering key value will be stored in a separate file. Thus, the best way to select a partition size would be that each cluster is larger than 512 MB.

Select partitioning and clustering keys for BigQuery tables when migrating from Netezza

List large tables

Use Netezza statistic to get information about table columns

Gather historical information about column references in SQL statements

Summarizing the steps

Written by Anna Epishova