Hive: Allows Single and Double Quotes Interchangeably

In standard SQL, the character used for quoting string literals is the single quote. In Hive you can use single quotes or double quotes. Great flexibility! Flexibility is not what we want. We want consistency; we want precision; we want no ambiguity. We want our code to be On Rails.

Adding the option to use two kinds of quotation marks does not make anyone's life easier. It is an entirely negative feature.

If you do ever try to migrate some Hive SQL to a proper relational database you are in for a lot of pain. I would recommend that you simply start again. Managers in this business being what they often are, I can see that there will be migration efforts that attempt to run Hive SQL on other databases. The use of double quotes with string literals will be one practice out of many that will extend the effort needed for the migration exercise and give your manager high blood-pressure.

There is a simple workaround for this problem. Never use double quotes to delimit strings in Hive scripts. If you are copying examples from the Hive documentation, change the double quotes to single quotes.