Table and Column Name Compression

Some of the popular Destination systems impose the following limits on the length of table and column names:

Destination	Table Name Character Limit	Column Name Character Limit
MySQL	64	64
PostgreSQL	63	63
Redshift	127	127
BigQuery	1024	128
Snowflake	255	255
MSSQL Server	128	128

This means that if the field or object name exceeds the limit set by the Destination, the extra characters are truncated during replication. This can lead to duplicate Destination tables and column names getting created, finally resulting in a replication error.

For example, consider the following nested JSON objects ingested from a MongoDB Source with parsing methods as Flatten structs and split arrays to new events:

• organization_vertical_geography_zone_country_department_contact_home_phone (74 chr)

• organization_vertical_geography_zone_country_department_contact_home_address (76 chr)

For a Postgres Destination, the tables created for these objects would end up having the same name due to the 63 character limit:

• organization_vertical_geography_zone_country_department_contact, and

• organization_vertical_geography_zone_country_department_contact.

To prevent such situations, Hevo uses name compression strategies to derive unique names for the tables and columns.

---

Name Compression Strategies

The following strategies take into account the different ways in which the Source fields and objects may have been named and try to retain the most relevant information within the specified character limit.

For the sample values below, a limit of 40 characters is assumed to derive the compressed names.

Name Compression Strategy (in order of priority)	Original Name	Compressed Name	Notes
Keep the last n characters	this_table_name_is_too_long_andTheLastWordIsLongerThanTheLimitByItself	dTheLastWordIsLongerThanTheLimitByItself	Useful when words are not separated and the name is a continuous string. This method counts only characters and does not recognize words
Abbreviate using first 2 letters only until necessary	this_table_name_has_so_many_words_that_abbreviating_might_help_shorten_it	th_ta_na_ha_so_ma_wo_th_ab_mi_he_sh_it	Useful when words are separated by an _ or a delimiter.
Remove words from the beginning	this_table_name_is_too_long_and_has_too_many_words_that_abbreviating_would_still_not_ensure_it_is_within_the_limit	still_not_ensure_it_is_within_the_limit	Whole words are removed till necessary.

---

Scenarios that Trigger Name Compression

• The Destination prefix is long. Hevo imposes a limit of 32 characters on the prefix length. As the Destination prefix is not compressed while creating the Destination table, it can lead to the Truncate strategy being applied on the table name.

• You have not enabled Merge Tables option. As a result, the Event Type name = database_name.table_name. This may lead to the character limit getting exceeded.

• The Source has JSON data, and the strategy selected for parsing of JSON Events results in generating long table and column names.

• The Source supports longer table and column names than the Destination. For example, the Source is Amazon Redshift (127 char) and the Destination is Postgres (63 char).

Note: Your selection of the Name Sanitization feature may affect the compression strategy being applied to the table name.

---

Example: Destination Schema with Table Compression

Let us consider:

• Source Type: Amazon Redshift
• Source Database Name: table_name_compression
  
• Destination Prefix: south_west_zone_retail_sales_div

• Source schema:
  

  

• Destination Type: Postgres
• Destination Database Name: table_name_compression_dest
• Sanitize Tables: True
  

In this scenario, the Destination schema created after table name compression is as follows: