How do I specify column's data type with spark dataframes?

Raie
New Contributor III

‎03-18-2022 10:24 AM

What I am doing:

spark_df = spark.createDataFrame(dfnew)

spark_df.write.saveAsTable("default.test_table", index=False, header=True)

This automatically detects the datatypes and is working right now. BUT, what if the datatype cannot be detected or detects wrong? Mostly concerned about doubles, ints, bigints.

I tested casting but it doesnt work on databricks:

spark_df = spark.createDataFrame(dfnew.select(dfnew("Year").cast(IntegerType).as("Year")))

Is there a way to feed a DDL to spark dataframe for databricks? Should I not use spark to create the table?

Labels:

RKNutalapati
Valued Contributor

‎03-20-2022 07:45 AM

Hi @Raie A​  : It will create data frame with wider dataTypes for example Long for (Int/BigInt) etc.. It depends on the use case. If you have created a table and want to overwrite/append data to that then you need to explicitly cast as per your column DataType.

one option is by creating a data class and convert DF as DS

case class person(id: Int, name: String)

dfnew.as[person]

0 Kudos

Hubert-Dudek
Databricks MVP

‎03-20-2022 07:47 AM

  • just create table earlier and set column types (CREATE TABLE ... LOCATION ( path path)
  • in dataframe you need to have corresponding data types which you can make using cast syntax, just your syntax is incorrect, here is example of correct syntax:

from pyspark.sql.types import IntegerType
from pyspark.sql.functions import *
 
dfnew = spark.createDataFrame([("2022",), ("2021",), ("2020",)], ["Year"])
dfnew = dfnew.withColumn("Year", col("Year").cast(IntegerType()))


My blog: https://databrickster.medium.com/

View solution in original post

Raie
New Contributor III
In response to Hubert-Dudek

‎03-21-2022 10:23 AM

Thanks @Hubert Dudek​  I changed the syntax and imported all the data types and casting is working!