Links

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 System
Top-Level Columns
Variant / 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 Field
Snowflake
Athena
Other
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 modified 6mo ago