CODEWITHSUNDEEP
rdbisnowflake-cloud-data-platform
speleo
speleo

Reputation: 123

R - dbWriteTable makes capital letters in column names

I am trying to create a table (in Snowflake db) with exactly the same column names as I keep in the R data.frame object:

    'data.frame':   1 obs. of  26 variables:
     $ Ship_To                 : chr "0002061948"
     $ Del_Coll_Indicator      : chr "D"
     $ Currency                : chr "GBP"
     $ Total_Volume            : num 0
     $ Total_Quantity          : num 0
    ...

There is no problem with the table creation:

dbWriteTable(con = my_db$con, name = "test5", value = df)

but all column names in the database are converted to upper cases:

'data.frame':   1 obs. of  26 variables:
 $ SHIP_TO                 : chr "0002061948"
 $ DEL_COLL_INDICATOR      : chr "D"
 $ CURRENCY                : chr "GBP"
...

Is there any way to keep in the table original names from R's data frame?

Upvotes: 2

Views: 702

Answers (1)

Harsh J
Harsh J

Reputation: 696

As covered by Snowflake's SQL reference docs, when identifiers (such as column names) are unquoted at creation, Snowflake will upper case them, and treat them as case-insensitive. Any quoted identifiers will be kept as-is and treated as a case-sensitive identifier.

Alter the data frame column names (colnames(df)) to use a quoted identifier format via the dbQuoteIdentifier(my_db$con, each_column_name) DBI function. This should help preserve the casing.

Upvotes: 3

Related Questions