Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

case sensitive columns

The create table in Snowflake surrounds all columns with double quotes. This makes the column names in the database create as lower case. However Snowflake defaults everything to upper case.

The result of this is a simple select statement will not work without double quotes.

CREATE TABLE TEST1 (
"x1" NUMBER(38,0) NOT NULL)

The result means

select x1 from test1 fails with unknown identifier.

I need to do

select "x1" from test1;

How can I remove the quotes from the resulting create table step in Matillion. (I am having to avoid using the create table step and create all tables manually because of this)

7 Community Answers

Matillion Agent  

Ian Funnell —

Hi Anthony,

Matillion will always use double quotes in its DDL, because Snowflake is case sensitive and distinguishes for example between “col1” and “COL1”.

To remove the need for double quotes, you should use uppercase for the names in the column list of the Create/Replace Table component.

Snowflake identifiers which are not enclosed in double quotes get converted to uppercase. So if you’ve used uppercase names in the Create/Replace Table, you’ll be able to reference them later without double quotes.

Best regards,
Ian


Anthony Schneider —

Hi Ian,

So I'm now hitting the same problem with the MongoDB query. It seems to drop and recreate tables and it surrounds the columns with double quotes. So if the mogodb column is lowercase it recreates the table with lowercase columns.

Is there anyway around this that doesn't involve me having to query my snowflake table with every column surrounded in double quotes?


Matillion Agent  

Laura Malins —

Hi Anthony

If you use Advanced mode in the MongoDB query component you can alias the MongoDB columns with upper case name, so for example your query could be:

SELECT col1 AS COL1
FROM tablename

Thanks
Laura


Anthony Schneider —

Do you feel that the default behavior for columns into the target Snowflake Database to surround them with double quotes is correct?

This means anyone migrating data from source to target database will have to alias all their columns, which seems quite cumbersome.


Matillion Agent  

Kalyan Arangam —

hi Anthony,

Thanks for raising this with us.
I think we need to discuss this internally and review why we ended up doing this in the first place and what could be done to resolve this.

In the meantime, snowflake does support a flag to ignore case-sensitivity with names- QUOTED_IDENTIFIERS_IGNORE_CASE. You may set this parameter to TRUE for the matillion-environment-user and any DDL by matillion will be created in uppercase. Is this something you are able to use to overcome this issue?

https://docs.snowflake.net/manuals/sql-reference/parameters.html#label-quoted-identifiers-ignore-case
https://docs.snowflake.net/manuals/sql-reference/identifiers.html#third-party-tools-and-case-insensitive-identifier-resolution

Please note that this setting applies to new objects only. Any previous objects with lowercase names cannot be queried once this is set to true and may give unexpected results if you have objects which share the same name but differ in case.

Best
Kalyan


Matillion Agent  

Kalyan Arangam —

Hi Anthony,

I had a discussion with the DEV team and (for the tool) using quotes around object-names is recommended. This is especially important from the tool’s perspective as we deal with a whole host of source-systems which behave differently around case-sensitivity, object-naming and various other nuances – using quotes around object-names is the safest option.

In your case, case-sensitivity of source-system is not a concern. We would recommended setting ‘QUOTED_IDENTIFIERS_IGNORE_CASE = true’ on your snowflake-Account or matillion-user. This would ensure snowflake would convert all DDL and SQL from matillion to uppercase and you no longer need to enclose in quotes.

Please note, once you set this parameter to true, you may not be able to query any objects in lowercase via the matillion user. You may need to re-run your etl or find some means of moving data into uppercase tables.

We will be updating our documentation to discuss more about this issue.

Please let us know if you have any further concerns.

Best
Kalyan


Anthony Schneider —

Perfect Kaylan, that solution works for those not wanting to be case-sensitive by default.

Thanks,
Anthony

Post Your Community Answer

To add an answer please login