topic Re: How to Pull Cached SQL Table into Python Dictionary? in Data Engineering

How to Pull Cached SQL Table into Python Dictionary?

drewtoby — Tue, 16 May 2023 18:50:10 GMT

Hello,

I have been working on this issue as a proof of concept - it would be extremely helpful to iterate through tables via loops in a few scenarios. I have a simple three column dimension that I added to a cached table.

cache lazy table hedis_cache select * from hofhc.hedis_dim

I then tried the following two methods. The first is coming up empty, whereas the second is returning data as a dataframe, not a python dictionary.

Any advice? Thanks in advance for all the help!

Re: How to Pull Cached SQL Table into Python Dictionary?

daniel_sahal — Wed, 17 May 2023 05:35:44 GMT

@Andrew Begg

First of all, cache table creates a view, not table, so you won't be able to use pd.read_table (https://docs.databricks.com/sql/language-manual/sql-ref-syntax-aux-cache-cache-table.html).

About the second method - .select() still gives a dataframe as an output. You need to find a way to convert DF to dict.

Re: How to Pull Cached SQL Table into Python Dictionary?

drewtoby — Wed, 17 May 2023 14:34:16 GMT

Got it to work, thank you for the tip! I needed to convert the dataframe over to a pandas dataframe

https://www.geeksforgeeks.org/convert-pyspark-dataframe-to-dictionary-in-python/