topic Re: Manipulating Data - using Notebooks in Data Engineering

Manipulating Data - using Notebooks

StevenW — Thu, 09 Feb 2023 13:25:07 GMT

I need to read/query table A, manipulate/modify the data and insert the new data into Table A again.

I considered using :

Cur_Actual = spark.sql("Select * from Table A")

currAct_Rows = Cur_Actual.rdd.collect()

for row in currAct_Rows:

do_somthing(row)

But that doesn't allow me to change the data, for example:

row.DATE = date_add(row.DATE, 1)

And then I don't understand how I would insert the new data into TABLE A.

Andy advice would be appreciated.

Re: Manipulating Data - using Notebooks

-werners- — Thu, 09 Feb 2023 13:29:48 GMT

Hard to tell without some context. I suppose Table A is a hive table based on delta or parquet?

If so, this can easily be achieved with a withColumn statement and overwrite of the data (or write a merge statement, or even a update for delta lake).

Re: Manipulating Data - using Notebooks

StevenW — Thu, 09 Feb 2023 13:40:27 GMT

Table A is a Delta table. I get this:

Cur_Actual.write.format('delta').mode('append').save('/location/Table A')

But as I understand it, one cannot loop over a DF, and hence the data is changed with the .collect() function to a collection.

This data needs to be modified and written back - but how,,?

Re: Manipulating Data - using Notebooks

-werners- — Thu, 09 Feb 2023 13:44:04 GMT

OK.

Basically you should never loop over a dataframe because that renders the distributed capacity of Spark useless.

what you should do is:

read the delta table into a dataframe with spark.read.table(table)
then do your transformations. Updating a column is done with the withColumn() statement. There are tons of other functions of course.
finally write the data. This can be either in append (as you did), merge (upsert) or overwrite (replace all).

There are some interesting tutorials on the databricks website which give an introduction to spark/databricks.

Re: Manipulating Data - using Notebooks

Manoj12421 — Thu, 09 Feb 2023 19:06:52 GMT

You can use withColumn() for the transformations and then write data this can be append, overwrite, merge .