topic Re: How do I specify column's data type with spark dataframes? in Data Engineering

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

Raie — Fri, 18 Mar 2022 17:24:34 GMT

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?

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

RKNutalapati — Sun, 20 Mar 2022 14:45:23 GMT

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]

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

Hubert-Dudek — Sun, 20 Mar 2022 14:47:29 GMT

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()))

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

Raie — Mon, 21 Mar 2022 17:23:55 GMT

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