COLLAS support

[minghungcho]

Currently SnowDDL does not accept COLLATE for table column. Can this be allowed by expanding data_type_re = compile(r'^(?P<base_type>[a-z0-9_]+)(((?P\d+)(,(?P\d+))?))?$', IGNORECASE) to accept additional regex group for COLLATE?

Traceback (most recent call last):
File "C:\ProgramData\Anaconda3\envs\esg_data_cloud\lib\site-packages\snowddl\resolver\abc_resolver.py", line 115, in _process_tasks
result = f.result()
File "C:\ProgramData\Anaconda3\envs\esg_data_cloud\lib\concurrent\futures_base.py", line 437, in result
return self.__get_result()
File "C:\ProgramData\Anaconda3\envs\esg_data_cloud\lib\concurrent\futures_base.py", line 389, in __get_result
raise self._exception
File "C:\ProgramData\Anaconda3\envs\esg_data_cloud\lib\concurrent\futures\thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
File "C:\ProgramData\Anaconda3\envs\esg_data_cloud\lib\site-packages\snowddl\resolver\table.py", line 61, in compare_object
snow_cols = self._get_existing_columns(bp)
File "C:\ProgramData\Anaconda3\envs\esg_data_cloud\lib\site-packages\snowddl\resolver\table.py", line 246, in _get_existing_columns
type=DataType(r['type']),
File "C:\ProgramData\Anaconda3\envs\esg_data_cloud\lib\site-packages\snowddl\blueprint\data_type.py", line 91, in init
raise ValueError(f"Could not parse data type string [{data_type_str}]")
ValueError: Could not parse data type string [VARCHAR(12) COLLATE 'en-ci']

[littleK0i]

There are few problems with COLLATE in Snowflake at this moment (as of 2022.06.24):

1. DESC TABLE command does not return COLLATE of existing columns. SnowDDL cannot compare column collation without spinning up an active warehouse.

2. This feature is poorly implemented in general, which is not obvious to many end-users.

Specifically:

• it is bad for performance
• not supported for VARIANT columns
• supported by only a few built-in functions

---

Since this feature is so problematic and unloved by Snowflake, I generally suggest to avoid it and consider alternatives. You may use ILIKE for case-insensitive pattern matching, you may use LOWER(<col>) for case-insensitive grouping, etc.

If collation cannot be avoided due to some legacy reasons, you may set it globally as default for the whole account using parameter: https://docs.snowflake.com/en/sql-reference/parameters.html#label-default-ddl-collation

[minghungcho]

Regarding DESC TABLE, from what I've seen that Snowflake does return something like VARCHAR(12) COLLATE 'en-ci' under type if you chose to use COLLATE.

Regardless the performance implication, the main reason why COLLATE is needed is for database/tables migrated from MS SQL Server, where SQL_Latin1_General_CP1_CI_AS is the default. Hence, string comparison is case-insensitively. This means applications / queries built against MS SQL previously, now need to be modified with UPPER() / LOWER() during comparison.

This is not urgent, but thought it would be a nice thing to add.

[littleK0i]

@minghungcho , you was right about DESC TABLE returning collation in type column. Thank you for pointing this out.

I've implemented an initial support for collations in version 0.7.0. An example of config:

columns:
  id:
    type: NUMBER(38,0)
  name:
    type: VARCHAR(255)
    collate: en-ci
  status:
    type: VARCHAR(255)
    collate: en_US-ci
  gg:
    type: VARCHAR(255)
    collate: fr

Please note, Snowflake cannot change collations on existing columns using ALTER TABLE. SnowDDL will likely try to rewrite tables due to collation mismatch.

Please also note, parameter DEFAULT_DDL_COLLATION is not fully supported right now. Collations has to be specified on a column level and using full config form.