Column Case Sensitivity

This article describes how Upsolver handles case sensitivity in column names

Upsolver offers a consistent methodology for managing case sensitivity in column names during SQL query execution. Understanding its approach is crucial for precise and efficient data operations. This guide details Upsolver's handling of case sensitivity and how column names are mapped across various target systems in SELECT queries.

Column Case Insensitivity in Upsolver

Upsolver views column names without considering their case. In other words, it doesn't matter if column names are in upper, lower, or mixed case; Upsolver sees them all as the same when executing queries.

Retaining Original Column Case in SELECT Queries

While Upsolver strives to maintain the original case of column names, its behavior might differ based on the target system in question. Below is a breakdown of how Upsolver retains the original case in a SELECT query across different systems:

Mapping of Column Names by Target System

Target SystemTop-Level ColumnsVariant / Struct fields

Snowflake

Uppercase

Original Case

Athena

Lowercase

Lowercase

Other

Original Case

Original Case

Examples of Mapping Column Names for SELECT *

Let's illustrate how Upsolver maps column names when performing a SELECT * query for various target systems:

Suppose we have the following table structure with original case column names: CustomerID, OrderDate, BillingAmount, and a nested column named CustomerAddress with two sub-fields.

| CustomerID | OrderDate   | BillingAmount | CustomerAddress             |
|------------|-------------|---------------|-----------------------------|
| 101        | 2023-01-15  | 150.00        | { "Line1": "123 Main St",   |
|            |             |               |   "Line2": "Apt 4B" }       |
| 102        | 2023-02-10  | 200.00        | { "Line1": "456 Elm Rd",    |
|            |             |               |   "Line2": "Suite 200" }    |

Here's how the column names map for different source fields and target systems:

Source FieldSnowflakeAthenaOther

CustomerID

CUSTOMERID

customerid

CustomerID

OrderDate

ORDERDATE

orderdate

OrderDate

BillingAmount

BILLINGAMOUNT

billingamount

BillingAmount

CustomerAddress

CUSTOMERADDRESS

customeraddress

CustomerAddress

CustomerAddress.Line1

CUSTOMERADDRESS.Line1

customeraddress.line1

CustomerAddress.Line1

CustomerAddress.Line2

CUSTOMERADDRESS.Line2

customeraddress.line2

CustomerAddress.Line2

Known Limitations

Jobs created prior to Upsolver version 2023.05.15-02.23 will not adhere to the rules outlined above. In these cases, all columns will be converted to lowercase for all target systems except Snowflake, where the column names will be converted to uppercase.

Last updated