Looking for BQ compatible metadata table corresponding to Netezza metadata

[ManigandanS]

Hi Folks,

We are in the process of migrating our customer from Netezza to BigQuery. In this phase, we are refactoring their existing shell script which has NZ SQL to BQ compatible SQL commands. In one of the script, there were using NZ metadata table and we are finding to replace the same with BQ compatible metadata table or any customer table that we can generate based on BQ metadata tables.

Below is the query which is querying the netezza metadata table,

   SELECT case when conseq > 1 then ',' else '' end || ' ' || attname

      FROM _v_relation_keydata

     WHERE relation = 'dba_raw_tblrefill'

       AND constraintname = 'dba_raw_tblrefill_etlpk'

       AND contype = 'u'

     ORDER BY conseq;

Can anyone help us on finding the bq metadata table for "_v_relation_keydata" or construct one from the available metadata table.?

Thanks,
S.Manigandan

[ms4446]

Finding a direct one-to-one equivalent to the Netezza _v_relation_keydata table within BigQuery's native metadata can be challenging. BigQuery structures its system-level metadata primarily within the INFORMATION_SCHEMA views.

Using BigQuery INFORMATION_SCHEMA

You can explore these INFORMATION_SCHEMA views to extract relevant information about your tables and columns in BigQuery:

• INFORMATION_SCHEMA.COLUMNS: Provides details about columns in each table (column name, data type, ordinal position, etc.)

• INFORMATION_SCHEMA.TABLE_CONSTRAINTS: Provides details about constraints on tables (constraint name, constraint type like 'PRIMARY KEY', 'UNIQUE', etc.)

• INFORMATION_SCHEMA.KEY_COLUMN_USAGE: Offers detailed information about the columns used in table constraints.

Constructing Metadata from INFORMATION_SCHEMA

To recreate a view similar to Netezza's _v_relation_keydata, you might need to combine information from multiple INFORMATION_SCHEMA views and filter for the specific table, dataset, and constraint you are interested in.

Example Query (Illustrative)

The following BigQuery SQL query illustrates how you could approach extracting information related to unique key columns, similar to your Netezza use case:

SELECT 
   TC.constraint_name, 
   STRING_AGG(CC.column_name, ', ' ORDER BY CC.ordinal_position) AS key_columns 
FROM 
   `your_dataset`.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
JOIN
   `your_dataset`.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
ON 
   TC.table_schema = CC.table_schema 
   AND TC.table_name = CC.table_name 
   AND TC.constraint_name = CC.constraint_name 
WHERE 
   TC.table_name = 'dba_raw_tblrefill' 
   AND TC.constraint_type = 'UNIQUE' 
GROUP BY 
   TC.constraint_name;

Note:

• This query assumes columns forming a unique key have a constraint_type of 'UNIQUE'.
• You may need to refine this query with additional filtering or joins to match the exact information captured in your Netezza _v_relation_keydata usage.

Limitations and Considerations:

• BigQuery's INFORMATION_SCHEMA structure might differ from Netezza's metadata organization. You may need to explore and understand the INFORMATION_SCHEMA views thoroughly.
• Metadata in BigQuery's INFORMATION_SCHEMAmight not store all the specific attributes that were present in the Netezza _v_relation_keydata table.
• For more fine-grained or historical metadata tracking, you may consider implementing custom solutions (e.g., using BigQuery logs, or creating your own tables to log metadata changes)