How to Pull Cached SQL Table into Python Dictionary?

[drewtoby]

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!

[daniel_sahal · Accepted solution]

@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.

[drewtoby]

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/