[HIVE-12274] Increase width of columns used for general configuration in the metastore.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.0
• Fix Version/s: 2.3.0, 3.0.0
• Component/s: Metastore
• Labels: metastore pull-request-available
• Release Note: Increased width of metastore text columns for general configuration storage.
• Assignee: Naveen Gangam
• Reporter: Elliot West
• Created: 27/Oct/15 16:26
• Updated: 24/Jun/20 00:26
• Resolved: 26/Mar/17 21:11

Description

Overview

This issue is very similar in principle to HIVE-1364. We are hitting a limit when processing JSON data that has a large nested schema. The struct definition is truncated when inserted into the metastore database column COLUMNS_V2.TYPE_NAME as it is greater than 4000 characters in length.

Given that the purpose of these columns is to hold very loosely defined configuration values it seems rather limiting to impose such a relatively low length bound. One can imagine that valid use cases will arise where reasonable parameter/property values exceed the current limit.

Context

These limitations were in by the patch attributed to HIVE-1364 which mentions the "max length on Oracle 9i/10g/11g" as the reason. However, nowadays the limit can be increased because:

• Oracle DB's varchar2 supports 32767 bytes now, by setting the configuration parameter MAX_STRING_SIZE to EXTENDED. (source)
• Postgres supports a max of 1GB for character datatype. (source)
• MySQL can support upto 65535 bytes for the entire row. So long as the PARAM_KEY value + PARAM_VALUE is less than 65535, we should be good. (source)
• SQL Server's varchar max length is 8000 and can go beyond using "varchar(max)" with the same limitation as MySQL being 65535 bytes for the entire row. (source)
• Derby's varchar can be upto 32672 bytes. (source)

Proposal

Can these columns not use CLOB-like types as for example as used by TBLS.VIEW_EXPANDED_TEXT? It would seem that suitable type equivalents exist for all targeted database platforms:

• MySQL: mediumtext
• Postgres: text
• Oracle: CLOB
• Derby: LONG VARCHAR

I'd suggest that the candidates for type change are:

• COLUMNS_V2.TYPE_NAME
• TABLE_PARAMS.PARAM_VALUE
• SERDE_PARAMS.PARAM_VALUE
• SD_PARAMS.PARAM_VALUE

After updating the maximum length the metastore database needs to be configured and restarted with the new settings. Altering MAX_STRING_SIZE will update database objects and possibly invalidate them, as follows:

• Tables with virtual columns will be updated with new data type metadata for virtual columns of VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) type.
• Functional indexes will become unusable if a change to their associated virtual columns causes the index key to exceed index key length limits. Attempts to rebuild such indexes will fail with ORA-01450: maximum key length exceeded.
• Views will be invalidated if they contain VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) typed expression columns.
• Materialized views will be updated with new metadata VARCHAR2(4000), 4000-byte NVARCHAR2, and RAW(2000) typed expression columns
• So the limitation could be raised to 32672 bytes, with the caveat that MySQL and SQL Server limit the row length to 65535 bytes, so that should also be validated to provide consistency.

Finally, will this limitation persist in the work resulting from HIVE-9452?

Issue Links

• breaks: HIVE-21614 Derby/Oracle does not support CLOB comparisons
• breaks: HIVE-16667 PostgreSQL metastore handling of CLOB types for COLUMNS_V2.TYPE_NAME and other field is incorrect
• causes: HIVE-19230 Schema column width inconsistency in Oracle
• contains: HIVE-15249 HIve 2.1.0 is throwing InvalidObjectException(message:Invalid column type name is too long
• contains: HIVE-14145 Too small length of column 'PARAM_VALUE' in table 'SERDE_PARAMS'
• is related to: HIVE-16710 Make MAX_MS_TYPENAME_LENGTH configurable
• is related to: HIVE-9815 Metastore column"SERDE_PARAMS"."PARAM_VALUE" limited to 4000 bytes
• is related to: HIVE-11985 don't store type names in metastore when metastore type names are not used
• is related to: HIVE-12299 Hive Column Data Type definition in schema limited to 4000 characters - too small
• links to: GitHub Pull Request #232
• links to: GitHub Pull Request #232
• links to: ReviewBoard