Data type during transferring data with ADF to Databricks tables converts into string

Question

Big Picture: Netezza Tables (with integer values, with datatime values) -----> Databricks table with columns in string

• I am transferring large tables from Netezza to ADLS and then from ADLS to Databricks (again using ADF = Azure Data Factory)
• Loading data directly data from Netezza to Databricks is put aside due to different architectural decisions.
• In Netezza there are tables with integer, datatime values in them, which I want to make them available as is to the Databricks

Details:

• ADF has a generic pipeline with parameters which makes it able to read all Netezza tables
• ADF has lets say a daily pipeline where generical pipeline is duplicated to cover all netezza tables
• The above architecture for ADF will cause an issue which down the line everything is ingested as string. and I am not able to change via map tabs in ADF
• ADF is writing data in ADLS as CSV
• Down the line of ADF pipline, I am running a databricks notebook which load data into databricks table, and I can see all columns in string

I have an idea as following but it is not working

dataSchema = StructType([
                         StructField("col1", IntegerType()),
                         StructField("col2", LongType()),
                         StructField("col3", FloatType()),
                         StructField("col4", DoubleType()),
                         StructField("col5", StringType()),
                         StructField("col6", DateType()),
                         StructField("col7", TimeType()),
                         StructField("col8", ArrayType()),
                         StructField("col9", MapType()),
                        ])
df.write \
  .option("schema",dataSchema)
  ......
  .save()

Please help with you experience, on how I can enforce these table columns to desire data type

Answer 1

You can use parquet format instead of CSV in the sink for the ADF pipeline. It will retain the datatype as in source, rather than string for all columns like in CSV. Also, parquet is good for you in couple of ways:

1. You can also use some form of compression like snappy, to save some space in ADLS
2. Easy with spark/databricks/Hive integration, as you mentioned in your qn.

A small comparison for you to understand. I tried with parquet and csv, and you can see the difference here. ADF pipeline sink:

CSV sink (All columns as string)

Parquet: ( Columns with equivalent format)