Case (in)sensitive string comparison in Snowflake

Introduction

In my previous post, I already talked about case sensitive and insensitive object identifiers in Snowflake. In this blogpost I'll go one step further and research how Snowflake compare strings in queries. In this blogpost I'll answer the question on how Snowflake handles comparisons of strings in case the setting DEFAULT_DDL_COLLATION is set and when it is unset. We start first with the setting when a database is created in a standard manner. 

Setup

For this experiment I've set up a database that is created in a standard way. An example is shown here.

CREATE DATABASE IF NOT EXISTS "DB_CASING_TEST";

Here an example of the table for the experiments and some values that are used for the experiments.

CREATE TABLE IF NOT EXISTS dbo."MiXed tAbLe"(
	ID integer,  
    Name varchar(50)
); 

INSERT INTO dbo."MiXed tAbLe" VALUES (1, 'TeST'), (2, 'TEST'), (3, 'test'), (4, 'Other value');

Experiments

And when I query the table with a case sensitive expression in the WHERE clause (= 'TEST') it will return a case sensitive answer. Only the record with 'TEST'  is returned.

Now, an experiment with the UPPER function in the WHERE clause! This will return 3 values.

The next experiment is with the LIKE operator. This will return one value and thus case sensitive.

Now there is also a case insensitive comparison operator : ILIKE. What will return this operator? Three values and therefore it is a case insensitive result.

It is also possible to use the COLLATE function in the WHERE Clause (and other places). This is also a way to do a case insensitive comparison. Offcourse you have to use the CI option.

The next experiment is about creating the table with columns that are case insensitive. You can specify the collation per column. Here is an example of the CREATE TABLE statement with columns with a collation

CREATE OR REPLACE TABLE dbo."MiXed_tAbLe_Collate"(
 	ID integer,  
    Name varchar(50) COLLATE 'en-ci'
);

I inserted the same values again and did the experiments again and you can see what happened. I've included some of the screenshot below. Here is the first one. This result is now 3 values instead of one as what have happened in the previous experiments.

I can show you all of the queries with the LIKE, ILIKE and UPPER en the COLLATE function. You have to believe me; they all returned 3 values back.

The next experiment is investigating on how the joining works with a case sensitive setting and here are the results and as you might expect, all results are based on case sensitive joining. First setup the base for the experiments.

CREATE OR REPLACE TABLE dbo."MiXed_tAbLe_Join"(
 	ID integer,  
    Name varchar(50) 
);

INSERT INTO dbo."MiXed_tAbLe_Join" VALUES (1, 'TeST'), (2, 'TEST'), (3, 'TESt'), (4, 'Other valuessss');

Here is one experiment with plain and simple joining. I changed the data a bit and you can see here that two values are returned : case sensitive

It is also possible to use the COLLATE function here resulting in all values are joined case insensitive with the other values.

DEFAULT_DDL_COLLATION

There is also another option : DEFAULT_DDL_COLLATION. This setting can be set during the creation of the database. This option is also available on account level.

CREATE DATABASE IF NOT EXISTS "DB_DEFAULT_DDL_COLLATION_EN_CI" DEFAULT_DDL_COLLATION = 'en-ci';

And now if we query the table with the same query we used before it will return 3 values. 

With this setting the comparisons, joining and other operations are now case insensitive.

Final thoughts

With DEFAULT_DDL_COLLATION it is possible to set the database in another collation and one of the options is case insensitive. I urge you to look into the options available.

Hennie

The case with mixed case in Snowflake

Introduction

It seems that Snowflake has a different way handling naming of objects than SQL Server or Azure SQL Database. I want to show you some examples that I tested so far in snowflake. 

The bottomline is : When you create a mixed case object (eg. table) with "" you can only reference this table with the same casing (case sensitive) and when you use no ""  OR use "" and put the name in uppercase, the object is created in uppercase and you can reference this object case insensitive.  

I've experimented with upper-, lower, mixed case variations in the naming of object and you can see what happens when I try to query them.

Experiment #1.1

In this experiment I have created a table with mixed case and double quotes. The double quotes are important here. The double quotes makes sure that the table is created in mixed case and not in uppercase. This happens when the quotes are omitted.

CREATE TABLE IF NOT EXISTS dbo."MiXed tAbLe"(
	ID integer  CONSTRAINT PK_Product_ID PRIMARY KEY ENFORCED
);

This results in the table in the following screenshot of Snowflake. The table is created as mixed case.

Experiment #1.2

Can we select the table with the same mixed case naming and the quotes? Yes we can!

Experiment #1.3

Can we use other casing in the SELECT statement? No, we can not. When the table is referenced with another casing in the name an error happens.

Experiment #1.4

Can we reference the table without ""? I have created the table with a space and therefore it will give an error.

Therefore, I've created another experiment. You can read this in the following experiment.

Experiment #2.1

In this experiment I created a table with mixed case, but without a space in the name and with double quotes. Now when I tried to reference the the table with upper case of the name it resulted in the following error.

Experiment #2.2

Another experiment shows that when I reference the table with the same casing but without the "", still an error occurs.

So the same casing and without the double quotes results in an error.

Experiment #2.3

Referencing the table with double quotes and the same casing will return the table.

This also implies that when double quotes have been used for creating the table you have to use the double quotes in the SELECT statement. As we see later in this blogpost, when the table is created in uppercase and with "" we can reference the table in uppercase, too.

Experiment #2.4

Now an experiment with the double quotes and with other casing than the casing when the table was created. An error happens.

Experiment #3.1

A new experiment with the mixed casing and without double quotes. What happens when we create a table in mixed casing and without the double quotes? The table is upper cased.

You can see that here : UPPERTABLE

Experiment #3.2

When the same casing is used in the SELECT statement the table is succesfully queried by the SELECT statement.

Experiment #3.3

So, what happens when the table is queried with other casing (mixed case)? The query returns successfully results.

Experiment #4.1

A new experiment with double quotes and uppercasing. I created a table with upper casing and with quotes and queried the table with the same casing. 

So the query with the exact same casing returns a result successfully.

Experiment #4.2

And now query the table with the upper case name but without double quotes? Yes this gives also a successful result.

Experiment #4.3

Can we query the table with mixed case and with double quotes? No that is not possible.

Experiment #4.4

And now the final test: can we query the table with different casing, without quotes and with a table that is created with upper case and double quotes? Yes, we can..

Experiment #5

I also tried the setting "DEFAULT_DDL_COLLATION" during the creation of the database.

-- Create the Database
CREATE DATABASE IF NOT EXISTS "MiXedCaseCollation" DEFAULT_DDL_COLLATION = 'en-ci';

But that didn't change the outcome of the experiments that I have conducted. 

Experiment #6

I was notified by someone of Snowflake about the setting QUOTED_IDENTIFIERS_IGNORE_CASE. 

If the setting is changed to TRUE, Snowflake will automatically uppercase all the objects, and it makes no difference whether a "" is used or not.

alter session set quoted_identifiers_ignore_case = true;

All objects are now created as uppercase. 

And all of the queries I've experimented are now runnable on the tables. No errors. 

Conclusion

Let's see whether we can draw some conclusions from these experiments:

• When a table is created without double quotes, the table can be referenced by mixed case.
• When the table is created with double quotes, and with mixed case, the table can only be referenced with the same casing and with double quotes.
• When the table is created with double quotes and the name is in upper case, the table can be queried with mixed case.

Changing the collation of the database to case insensitive collation didn't do anything for the naming of objects. 

References

• Collation on database level
• CREATE DATABASE
• Moved from sql server to snowflake finding case sensitive collation issue
• Snowflake for SQL Server Users – Part 8 – Case Sensitivity
• Snowflake documentation : Collation Support
• Identifier Requirements

Creating AdventureWorks tables in Snowflake

Introduction

I was wondering whether how easy it is to recreate the Adventureworks tables in Snowflake and load the csv files into Snowflake. So I grabbed the CREATE TABLE scripts from the AdventureWorks example (Github) and tried to execute them in Snowflake. In the beginning I ran into a couple of errors. The next step was uploading the csv files, that are accompanied with the AdventureWorks Database, to a Blob storage in Azure. I created a SAS token and started loading the data into the tables. I encountered some errors but I managed to solve them mostly all of them. I had one issue with DimProduct.csv; it seems there is something wrong with the file. 

My first table

I started with a simple table, the DatabaseLog table and I was curieus what would and what would not convert into Snowflake SQL language. Below an example of the table. 

CREATE TABLE dbo.DatabaseLog (
    DatabaseLogID int IDENTITY (1, 1) NOT NULL,
    PostTime datetime NOT NULL, 
    DatabaseUser sysname NOT NULL, 
    Event sysname NOT NULL, 
    Schema sysname NULL, 
    Object sysname NULL, 
    TSQL nvarchar(max) NOT NULL, 
    XmlEvent xml NOT NULL
)

This resulted in the following snowflake table :

--DROP TABLE IF EXISTS dbo.DatabaseLog;

CREATE TABLE IF NOT EXISTS dbo.DatabaseLog (
    DatabaseLogID int IDENTITY (1, 1) NOT NULL,
    PostTime datetime NOT NULL, 
    DatabaseUser varchar(128) NOT NULL , 
    Event varchar(128) NOT NULL,  
    Schema varchar(128) NOT NULL, 
    Object varchar(128) NOT NULL, 
    TSQL varchar NOT NULL, 
    XmlEvent variant NOT NULL
);

Then I subsequently started converting the other CREATE TABLE scripts into Snowflake SQL language. The following issues I had to solve in order to create the tables in Snowflake :

• The [ and ] was not recognized by Snowflake.
• Sysname is not supported in Snowflake.
• Nvarchar is not recognized as a valid datatype.
• Varchar(Max) is not supported. For this purpose I used the standard varchar and this would create the largest varchar possible.
• Unsupported data type 'XML'. I used the variant datatype for storing the XML.
• I had to remove the ON PRIMARY;
• Bit was unsupported. I replaced this with the Boolean datatype.
• I removed all of the GO's but that was not really necessary.
• I replaced nchar with char.
• Money is not a supported datatype. I haven't found a similar datatype in Snowflake. I replaced it with a float datatype.
• varbinary(max) was not recognized and I replaced that with varbinary without a max length.

After I solved these issues it was very easy to create the tables in snowflake.

Loading the data into Snowflake

The next step was loading the tables in Snowflake. The first error I ran into was because there was something wrong with the file. When I executed the following statement :

Copy into dbo.DimDate
  from @my_azure_stage
  pattern='DimDate.csv'
  file_format = (type = csv field_delimiter = '|');

The following error message happened :

Numeric value '0xFF0xFE20x0000x0000x0050x0000x0010x0000x0010x00' is not recognized File 'DimDate.csv', line 1, character 1 Row 1, column "DIMDATE"["DATEKEY":1] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

I found out that the encoding was UCS-2 LE BOM and Snowflake didn't recognize this format.

Next step was changing the definition of the file in the COPY INTO statement. I added ENCODING='UTF-16LE' to the statement as you can see below.

copy into dbo.DimDate
  from @my_azure_stage
  pattern='DimDate.csv'
  file_format = (type = csv field_delimiter = '|' ENCODING='UTF-16LE');

When I added the extra parameter and added this to the other COPY INTO statements for the other files it was easy to import the csv files. Except for one file DimProduct.csv it worked fine. The specific error that happened during loading the file is the following :

NULL result in a non-nullable column File 'DimProduct.csv', line 1, character 2 Row 1, column "DIMPRODUCT"["SPANISHPRODUCTNAME":7] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

Examining the file in Excel with Text to Columns shows a mixed corrupted file. I didn't want to waste to much of time looking in an issue with the file. The origin of the error seems to be the image column in the file. Google also didn't led me to a solution. So it's a pity I could solve this one. Because Excel also mixed up the file I decided to forget about it. I concluded that it was not an Snowflake issue.

Final Thoughts

Transferring the code from SQL Server to Snowflake is fairly easy. There are some issues but these are  easy to overcome. I took me a couple of hours to convert the table scripts into Snowflake SQL language, creating the load scripts and a bit of debugging. 

Hennie

Azure Series : Log analytics introduction

Introduction

Log analytics is part of Azure Monitor and it is a log collection of different services. You need to set up a workspace (You can setup multiple workspaces). In this blogpost I'll setup diagnostic settings for a VM and see if we can use log analytics for some analysis.

There are a couple of resources available for monitoring and newly are added continuously as you can see below.

Setting up diagnostic settings of a VM

At first, we cannot use diagnostics because the VM is not turned on. So, we need to turn the VM on.

Now, first start the VM in order to enable diagnostic settings.

And now we can enable diagnostic settings in the VM and we can use it in Azure monitor.

This will take some time. I had to refresh the window because it kept on saying "busy"..These are the standard settings of the diagnostic settings.

 What to log more? These are some of the other options.

Some more tabs are available but will not go into detail for now. But let's move on to the Azure Monitor Log analytics. Now, we can see that the VM is monitored and used in the Azure Monitor

Some of the Storage accounts are being monitored too.

Now let's try some log analytics queries.  First setup the scope for log analytics.

And now we can write some log analytics queries. Here are some simple log analytics query to retrieve the perf counters of the Computer "azhennievm".

Here is another example where I've gather the logs of 2 minutes ago until now.

I tried some examples of the Microsoft docs.

Resources

Some great resources I found so far about log analytics:

• Marc Kean
• github

Final thoughts

This tool can be used for monitoring your Azure resources and it is mostly used in administrative tasks in Azure.

Hennie

Azure Series : Synchronization between Azure Databases

Introduction

I have to copy a couple of tables of about 200 million rows between a couple Azure SQL databases, just once or perhaps twice. I don't know it exactly. Now, in SQL Server (on-prem) you have some options and it's fairly easy copying data but in Azure it's a different ballgame. If the data is not very much you can use the "Generate scripts" and choose the option "data only" resulting in scripted data in INSERT statements. I tried the bacpac option in SSMS but I received a lot of validation errors because (perhaps) the database was not in a consistent state. I didn't investigate this much further. One another trick I tried was using SSIS, my old favorite ETL Tool. Although I enjoy the tool a lot, it seems that the integration and alignment with Azure should be better. Although, It is a good backup for my problem, I would like to know if there is somethnig better, easier or faster to use. So my options were starting to get smaller and smaller. Elastic queries could be an option but I have been there, done that before and so I ended up with experimenting with synchronization groups in Azure SQL Databases. This blogpost is a description of the process and investigation on how to setup synchronization between Azure SQL Database. I hope you find it useful and leave me note when you have remarks or questions.

The setup

Firstly, what are synchronization groups in Azure SQL Database? Well, it is a synchronization tool for data synchronization between Azure SQL Databases and on-premise SQL Servers (you have to install an agent). For this blogpost, I am only interested in synchronization between Azure SQL databases.

There are two types of databases: Hubs and Members.  

The configuration

First, create a sync group in the Azure Portal, Navigate to the database and search for "Sync to other databases" and click on that.

Create a new sync group with "New Sync Group"

Enter the Sync group name and I choose to use existing database and all of the databases are shown in the drop down box and I choose the Hub database. The next step is choosing the member database that is used for the synchronization.

The member database is used for the sync member.

And the next step is choosing the tables (and clumns if you wish) to sync from, but in my case it seems like saying for hours: "loading tables"....hmpf...

After a couple of tries and clicking around the following error message appears and now things were getting more and more clearer. The service has nog access to the Azure SQL Database Server.

So I set this option : Allow Azure services to access the server!

And now I recieved another error message, something about a bad login.

After correcting the password, all of a sudden I received a list of tables. I'm not sure but I took some while to manage this, but may be it's just me.

In the next step I can even choose columns for synchronization. There are some not supported columns over there. I leave that for later to investigate.

The execution

All ready and I pressed on the Sync button and some magic happened. The table is synchronized to the other database!

Some logging appears and it seems that the synchronization is succeeded.

Let's take a look in the database, but hey there are some tables in the database I didn't expect and seems a bit awkward. These tables are needed for the synchronization between the databases.

Also in the member database a lot of synchronization (meta) tables were created.

Final thoughts

I expected/hoped that the synchronization of databases is a kind of replacement of the import/export of data of the on premise SQL Server version, a one stop copy and paste method, but it's more like a synchronization tool, as off course the name implies. So for a simple copy action it's usable but you will get a lot of tables in your database unless you use a meta data database. 

Hennie